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;