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

The Following is the query in question.

SELECT, z.lon,, z.state, q.date_time, c.make, c.model, c.year
   FROM quotes AS q, zips AS z, cars AS c
  = 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

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)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to