Re: A question on the query planner - Mailing list pgsql-performance

Robert Treat wrote:

>On Mon, 2003-12-01 at 16:44, Jared Carr wrote:
>
>
>>I am currently working on optimizing some fairly time consuming queries
>>on a decently large
>>dataset.
>>
>>The Following is the query in question.
>>
>>SELECT z.lat, z.lon, z.city, z.state, q.date_time, c.make, c.model, c.year
>>    FROM quotes AS q, zips AS z, cars AS c
>>        WHERE
>>            z.zip = q.zip AND
>>            c.car_id = q.car_id AND
>>            z.state != 'AA' AND
>>            z.state != 'AE' AND
>>            z.state != 'AP' AND
>>            z.state = 'WA'
>>     ORDER BY date_time;
>>
>>
>>
>
>This wont completely solve your problem, but z.state = 'WA' would seem
>to be mutually exclusive of the != AA|AE|AP.  While it's not much, it is
>extra overhead there doesn't seem to be any need for...
>
>Robert Treat
>
>
That is an excellent point, unfortunately it doesn't change the query
plan at all.

Furthermore noticed that in the following query plan it is doing the
sequential scan on quotes first, and
then doing the sequential on zips.  IMHO this should be the other way
around, since the result set for
zips is considerably smaller especially give that we are using a where
clause to limit the number of items
returned from zips, so it would seem that it would be faster to scan
zips then join onto quotes, but perhaps
it needs to do the sequential scan on both regardless.

Of course still there is the holy grail of getting it to actually use
the indexes. :P

                                                                QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=57812.71..57822.86 rows=4058 width=80) (actual
time=2522.826..2529.237 rows=4581 loops=1)
   Sort Key: q.date_time
   ->  Merge Join  (cost=57473.20..57569.50 rows=4058 width=80) (actual
time=2360.656..2451.987 rows=4581 loops=1)
         Merge Cond: ("outer"."?column7?" = "inner"."?column5?")
         ->  Sort  (cost=56625.79..56635.93 rows=4058 width=62) (actual
time=2077.209..2085.095 rows=4735 loops=1)
               Sort Key: (q.car_id)::text
               ->  Hash Join  (cost=1088.19..56382.58 rows=4058
width=62) (actual time=86.111..1834.682 rows=10193 loops=1)
                     Hash Cond: (("outer".zip)::text = ("inner".zip)::text)
                     ->  Seq Scan on quotes q  (cost=0.00..10664.25
rows=336525 width=27) (actual time=0.098..658.905 rows=336963 loops=1)
                     ->  Hash  (cost=1086.90..1086.90 rows=516 width=52)
(actual time=85.798..85.798 rows=0 loops=1)
                           ->  Seq Scan on zips z  (cost=0.00..1086.90
rows=516 width=52) (actual time=79.532..84.151 rows=718 loops=1)
                                 Filter: ((state)::text = 'WA'::text)
         ->  Sort  (cost=847.41..870.91 rows=9401 width=37) (actual
time=282.896..300.082 rows=11950 loops=1)
               Sort Key: (c.car_id)::text
               ->  Seq Scan on cars c  (cost=0.00..227.01 rows=9401
width=37) (actual time=0.102..43.516 rows=9401 loops=1)