excessive performance difference

Поиск
Список
Период
Сортировка
От Luis Amigo
Тема excessive performance difference
Дата
Msg-id 3C18A084.BD9E93B8@atc.unican.es
обсуждение исходный текст
Список pgsql-novice
We're running a query on psql7.2b3, in which planner is unable to find
correct way to solve.
This is only informative message
If we run query19.old it takes more than 7 hours with this plan

Aggregate  (cost=17310310.04..17310310.04 rows=1 width=116)
  ->  Nested Loop  (cost=0.00..17310310.00 rows=15 width=116)
        ->  Seq Scan on part  (cost=0.00..140.00 rows=2000 width=36)
        ->  Seq Scan on lineitem  (cost=0.00..2325.79 rows=60279
width=80)

If we use query19.sql it takes 6 secs and plan is

Nested Loop  (cost=9770.00..9770.02 rows=1 width=96) (actual
time=6538.94..6539.09 rows=1 loops=1)
  ->  Nested Loop  (cost=6513.40..6513.41 rows=1 width=64) (actual
time=4472.32..4472.41 rows=1 loops=1)
        ->  Subquery Scan resultado  (cost=3256.82..3256.82 rows=1
width=30) (actual time=2283.08..2283.11 rows=1 loops=1)
              ->  Aggregate  (cost=3256.82..3256.82 rows=1 width=30)
(actual time=2283.03..2283.04 rows=1 loops=1)
                    ->  Hash Join  (cost=175.00..3256.81 rows=1
width=30) (actual time=688.18..2282.70 rows=1 loops=1)
                          ->  Seq Scan on lineitem  (cost=0.00..3079.28
rows=506 width=26) (actual time=6.29..2217.74 rows=492 loops=1)
                          ->  Hash  (cost=175.00..175.00 rows=1 width=4)
(actual time=55.20..55.20 rows=0 loops=1)
                                ->  Seq Scan on part  (cost=0.00..175.00
rows=1 width=4) (actual time=42.12..55.11 rows=2 loops=1)
        ->  Subquery Scan resultado2  (cost=3256.59..3256.59 rows=1
width=30) (actual time=2189.15..2189.18 rows=1 loops=1)
              ->  Aggregate  (cost=3256.59..3256.59 rows=1 width=30)
(actual time=2189.10..2189.11 rows=1 loops=1)
                    ->  Hash Join  (cost=175.00..3256.58 rows=1
width=30) (actual time=2188.99..2188.99 rows=0 loops=1)
                          ->  Seq Scan on lineitem  (cost=0.00..3079.28
rows=460 width=26) (actual time=2.53..2124.05 rows=519 loops=1)
                          ->  Hash  (cost=175.00..175.00 rows=1 width=4)
(actual time=54.97..54.97 rows=0 loops=1)
                                ->  Seq Scan on part  (cost=0.00..175.00
rows=1 width=4) (actual time=21.05..54.85 rows=3 loops=1)
  ->  Subquery Scan resultado3  (cost=3256.59..3256.59 rows=1 width=30)
(actual time=2066.41..2066.44 rows=1 loops=1)
        ->  Aggregate  (cost=3256.59..3256.59 rows=1 width=30) (actual
time=2066.36..2066.37 rows=1 loops=1)
              ->  Hash Join  (cost=175.01..3256.59 rows=1 width=30)
(actual time=2066.24..2066.24 rows=0 loops=1)
                    ->  Seq Scan on lineitem  (cost=0.00..3079.28
rows=460 width=26) (actual time=2.25..2001.36 rows=483 loops=1)
                    ->  Hash  (cost=175.00..175.00 rows=2 width=4)
(actual time=55.48..55.48 rows=0 loops=1)
                          ->  Seq Scan on part  (cost=0.00..175.00
rows=2 width=4) (actual time=17.21..55.34 rows=3 loops=1)


explain
SELECT
    sum(lineitem.extendedprice*(1-lineitem.discount))AS revenue
FROM
    part,
    lineitem
WHERE
    (
        part.partkey=lineitem.partkey
        AND part.brand='Brand#12'
        AND part.container IN('SM CASE','SM BOX','SM PACK','SM PKG')
        AND lineitem.quantity>=1 AND lineitem.quantity<=1+10
        AND part.size BETWEEN 1 AND 5
        AND lineitem.shipmode IN ('AIR','AIR REG')
        AND lineitem.shipinstruct='DELIVER IN PERSON'
    )
    OR
    (
        part.partkey=lineitem.partkey
        AND part.brand='Brand#23'
        AND part.container IN('MED BAG','MED BOX','MED PACK','MED PKG')
        AND lineitem.quantity>=10 AND lineitem.quantity<=10+10
        AND part.size BETWEEN 1 AND 10
        AND lineitem.shipmode IN ('AIR','AIR REG')
        AND lineitem.shipinstruct='DELIVER IN PERSON'
    )
    OR
    (
        part.partkey=lineitem.partkey
        AND part.brand='Brand#34'
        AND part.container IN('LG CASE','LG BOX','LG PACK','LG PKG')
        AND lineitem.quantity>=20 AND lineitem.quantity<=20+10
        AND part.size BETWEEN 1 AND 15
        AND lineitem.shipmode IN ('AIR','AIR REG')
        AND lineitem.shipinstruct='DELIVER IN PERSON'
    );
SELECT
    (suma+suma1+suma2)AS revenue

FROM
    (SELECT
    CASE
    WHEN sum(extendedprice*(1-discount))>0 THEN sum(extendedprice*(1-discount))
    ELSE 0
    END AS suma
    FROM
        part,
        lineitem
    WHERE
    (
        part.partkey=lineitem.partkey
        AND part.brand='Brand#12'
        AND part.container IN('SM CASE','SM BOX','SM PACK','SM PKG')
        AND lineitem.quantity>=1 AND lineitem.quantity<=1+10
        AND part.size BETWEEN 1 AND 5
        AND lineitem.shipmode IN ('AIR','AIR REG')
        AND lineitem.shipinstruct='DELIVER IN PERSON'
    ))AS resultado,
    (SELECT
    CASE
    WHEN sum(extendedprice*(1-discount))>0 THEN sum(extendedprice*(1-discount))
    ELSE 0
    END AS suma1
    FROM
        part,
        lineitem
    WHERE
    (
        part.partkey=lineitem.partkey
        AND part.brand='Brand#23'
        AND part.container IN('MED BAG','MED BOX','MED PACK','MED PKG')
        AND lineitem.quantity>=10 AND lineitem.quantity<=20
        AND part.size BETWEEN 1 AND 10
        AND lineitem.shipmode IN ('AIR','AIR REG')
        AND lineitem.shipinstruct='DELIVER IN PERSON'
    ))AS resultado2,
    (SELECT
    CASE
    WHEN sum(extendedprice*(1-discount))>0 THEN sum(extendedprice*(1-discount))
    ELSE 0
    END AS suma2
    FROM
        part,
        lineitem
    WHERE
    (
        part.partkey=lineitem.partkey
        AND part.brand='Brand#34'
        AND part.container IN('LG CASE','LG BOX','LG PACK','LG PKG')
        AND lineitem.quantity>=20 AND lineitem.quantity<=30
        AND part.size BETWEEN 1 AND 15
        AND lineitem.shipmode IN ('AIR','AIR REG')
        AND lineitem.shipinstruct='DELIVER IN PERSON'
    ))AS resultado3;

В списке pgsql-novice по дате отправления:

Предыдущее
От: "Josh Berkus"
Дата:
Сообщение: Re: Storing number '001' ?
Следующее
От: S P Arif Sahari Wibowo
Дата:
Сообщение: Varying bit field: to set and query a particular bit