Re: excessive performance difference

On Thu, 13 Dec 2001 13:35:16 +0100
Luis Amigo <lamigo(at)atc(dot)unican(dot)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