pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jared Carr <jared@89glass.com>
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: A question on the query planner
Date: Tue, 02 Dec 2003 09:55:49 -0800
Message-ID: <3FCCD225.8030609@89glass.com> (raw)
In-Reply-To: <1070385381.24915.8650.camel@camel>
References: <3FCBB639.10409@89glass.com>
	<1070385381.24915.8650.camel@camel>

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)





view thread (19+ messages)  latest in thread

Message-ID: <3FCCD225.8030609@89glass.com>
Permalink:  ../3FCCD225.8030609@89glass.com/
Also on:    postgresql.org/message-id/3FCCD225.8030609@89glass.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: jared@89glass.com, xzilla@users.sourceforge.net
  Subject: Re: A question on the query planner
  In-Reply-To: <3FCCD225.8030609@89glass.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox