Re: excessive performance difference - Mailing list pgsql-general

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