Thread: excessive performance difference
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;
On Thu, 13 Dec 2001 13:35:16 +0100
Luis Amigo <lamigo@atc.unican.es> wrote:
> 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)
I found that the query, query19.old, has no hints to run the planner
effectively. I would think you need to change a bit like this:
explain
SELECT
sum(lineitem.extendedprice*(1-lineitem.discount))AS revenue
FROM
part,
lineitem
WHERE
part.partkey=lineitem.partkey -- change
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.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.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'
));
NOTICE: QUERY PLAN:
Aggregate (cost=344.66..344.66 rows=1 width=72)
-> Merge Join (cost=139.66..344.66 rows=1 width=72)
-> Sort (cost=69.83..69.83 rows=1000 width=32)
-> Seq Scan on part (cost=0.00..20.00 rows=1000 width=32)
-> Sort (cost=69.83..69.83 rows=1000 width=40)
-> Seq Scan on lineitem (cost=0.00..20.00 rows=1000 width=40)
AFAICT, you probably might use indices to remove the two sorts
from the previous result of the query plan.
create index idx_part_partkey on part (partkey);
create index idx_lineitem_partkey on lineitem (partkey);
NOTICE: QUERY PLAN:
Aggregate (cost=323.01..323.01 rows=1 width=72)
-> Merge Join (cost=0.00..323.01 rows=1 width=72)
-> Index Scan using idx_part_partkey on part
(cost=0.00..59.00 rows=1000 width=32)
-> Index Scan using idx_lineitem_partkey on lineitem
(cost=0.00..59.00 rows=1000 width=40)
Regards,
Masaru Sugawara
Masaru Sugawara wrote:
>
> I found that the query, query19.old, has no hints to run the planner
> effectively. I would think you need to change a bit like this:
Thank you for your answer Masaru, the only thing I wanted to communicate is that
query19.old works perfectly with pay bases, and someone could get stucked with the
same problem. What you are getting outside is same condition that is inside of
all, partkey is primary key, so it is not neccesary to create index.
query19.sql gives better performance than yours, it is pseudo intra-paralellism,
if anyone is interested here are the results
tpch=# \e /disco3/lamigo/tpch/consultas/query19.old
NOTICE: QUERY PLAN:
Aggregate (cost=6073.03..6073.03 rows=1 width=116) (actual time=6865.81..6865.81
rows=1 loops=1)
-> Hash Join (cost=145.00..6073.03 rows=1 width=116) (actual
time=2057.06..6865.58 rows=1 loops=1)
-> Seq Scan on lineitem (cost=0.00..2326.05 rows=60305 width=80) (actual
time=0.38..3244.77 rows=60279 loops=1)
-> Hash (cost=140.00..140.00 rows=2000 width=36) (actual
time=132.55..132.55 rows=0 loops=1)
-> Seq Scan on part (cost=0.00..140.00 rows=2000 width=36) (actual
time=13.66..98.65 rows=2000 loops=1)
Total runtime: 6867.08 msec
EXPLAIN
tpch=# \e /disco3/lamigo/tpch/consultas/query19.sql
NOTICE: QUERY PLAN:
Nested Loop (cost=9771.17..9771.19 rows=1 width=96) (actual time=6539.58..6539.74
rows=1 loops=1)
-> Nested Loop (cost=6513.97..6513.98 rows=1 width=64) (actual
time=4254.88..4254.98 rows=1 loops=1)
-> Subquery Scan resultado2 (cost=3257.16..3257.16 rows=1 width=30)
(actual time=2185.23..2185.27 rows=1 loops=1)
-> Aggregate (cost=3257.16..3257.16 rows=1 width=30) (actual
time=2185.19..2185.20 rows=1 loops=1)
-> Hash Join (cost=175.00..3257.16 rows=1 width=30) (actual
time=2185.07..2185.07 rows=0 loops=1)
-> Seq Scan on lineitem (cost=0.00..3079.86 rows=458
width=26) (actual time=2.50..2119.31 rows=519 loops=1)
-> Hash (cost=175.00..175.00 rows=1 width=4) (actual
time=55.40..55.40 rows=0 loops=1)
-> Seq Scan on part (cost=0.00..175.00 rows=1
width=4) (actual time=21.11..55.27 rows=3 loops=1)
-> Subquery Scan resultado3 (cost=3256.81..3256.81 rows=1 width=30)
(actual time=2069.55..2069.59 rows=1 loops=1)
-> Aggregate (cost=3256.81..3256.81 rows=1 width=30) (actual
time=2069.51..2069.51 rows=1 loops=1)
-> Hash Join (cost=175.01..3256.81 rows=1 width=30) (actual
time=2069.39..2069.39 rows=0 loops=1)
-> Seq Scan on lineitem (cost=0.00..3079.86 rows=387
width=26) (actual time=2.24..2004.59 rows=483 loops=1)
-> Hash (cost=175.00..175.00 rows=2 width=4) (actual
time=55.00..55.00 rows=0 loops=1)
-> Seq Scan on part (cost=0.00..175.00 rows=2
width=4) (actual time=17.20..54.87 rows=3 loops=1)
-> Subquery Scan resultado (cost=3257.19..3257.19 rows=1 width=30) (actual
time=2284.52..2284.55 rows=1 loops=1)
-> Aggregate (cost=3257.19..3257.19 rows=1 width=30) (actual
time=2284.47..2284.48 rows=1 loops=1)
-> Hash Join (cost=175.00..3257.19 rows=1 width=30) (actual
time=688.98..2284.13 rows=1 loops=1)
-> Seq Scan on lineitem (cost=0.00..3079.86 rows=464
width=26) (actual time=6.31..2219.45 rows=492 loops=1)
-> Hash (cost=175.00..175.00 rows=1 width=4) (actual
time=54.99..54.99 rows=0 loops=1)
-> Seq Scan on part (cost=0.00..175.00 rows=1 width=4)
(actual time=42.02..54.89 rows=2 loops=1)
Total runtime: 6541.95 msec
EXPLAIN
tpch=# create index index_partkey on lineitem (partkey int4_ops);
CREATE
tpch=# analyze;
ANALYZE
tpch=# \e /disco3/lamigo/tpch/consultas/query19.old
ANALYZE
tpch=# \i /disco3/lamigo/tpch/consultas/query19.old
psql:/disco3/lamigo/tpch/consultas/query19.old:35: NOTICE: QUERY PLAN:
Aggregate (cost=6183.50..6183.50 rows=1 width=116) (actual time=6816.22..6816.22
rows=1 loops=1)
-> Hash Join (cost=145.00..6183.50 rows=1 width=116) (actual
time=2013.52..6815.98 rows=1 loops=1)
-> Seq Scan on lineitem (cost=0.00..2326.05 rows=60305 width=80) (actual
time=0.38..3220.05 rows=60279 loops=1)
-> Hash (cost=140.00..140.00 rows=2000 width=36) (actual
time=108.44..108.44 rows=0 loops=1)
-> Seq Scan on part (cost=0.00..140.00 rows=2000 width=36) (actual
time=1.02..76.31 rows=2000 loops=1)
Total runtime: 6817.15 msec
Thank you and regards.
Luis Amigo
Universidad de Cantabria