pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
To: rm_pg@cheapcomplexdevices.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Left Outer Join much faster than non-outer Join?
Date: Wed, 30 Mar 2005 12:50:23 -0800 (PST)
Message-ID: <Pine.LNX.4.58.0503301241541.9713@greenie.cheapcomplexdevices.com> (raw)
In-Reply-To: <Pine.LNX.4.58.0503301219540.9713@greenie.cheapcomplexdevices.com>
References: <Pine.LNX.4.58.0503301219540.9713@greenie.cheapcomplexdevices.com>


Setting join_collapse_limit=1 improves my performance dramatically.

Even on a query with only 3 tables.

This surprised me, since there are only 3 tables being joined, I would
have assumed that the optimizer would have done the exhaustive search
and not used geqo stuff - and that this exhaustive search would have
found the good plan.

Any reason it didn't?   Explain analyze results shown below.



On Wed, 30 Mar 2005 rm_pg@cheapcomplexdevices.com wrote:
> 
> Can anyone please help me make my JOIN find the right index to use?
>

fli=# set join_collapse_limit=1;
SET
fli=# explain analyze
 select *
     from streetname_lookup as sl
     join city_lookup as cl on (true)
     join tlid_smaller as ts on (sl.geo_streetname_id = ts.geo_streetname_id and cl.geo_city_id=ts.geo_city_id)
     where  str_name='alamo' and  city='san antonio' and state='TX'
;
fli-# fli-# fli-# fli-# fli-# fli-#                                                                           QUERY PLAN                                                                      \

---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..16.94 rows=1 width=74) (actual time=0.116..0.528 rows=78 loops=1)
   ->  Nested Loop  (cost=0.00..9.03 rows=1 width=42) (actual time=0.079..0.086 rows=1 loops=1)
         ->  Index Scan using streetname_lookup__str_name on streetname_lookup sl  (cost=0.00..3.01 rows=1 width=19) (actual time=0.042..0.044 rows=1 loops=1)
               Index Cond: (str_name = 'alamo'::text)
         ->  Index Scan using city_lookup__name on city_lookup cl  (cost=0.00..6.01 rows=1 width=23) (actual time=0.026..0.028 rows=1 loops=1)
               Index Cond: ((city = 'san antonio'::text) AND (state = 'TX'::text))
   ->  Index Scan using tlid_smaller__street_city on tlid_smaller ts  (cost=0.00..7.86 rows=3 width=32) (actual time=0.031..0.181 rows=78 loops=1)
         Index Cond: (("outer".geo_streetname_id = ts.geo_streetname_id) AND ("outer".geo_city_id = ts.geo_city_id))
 Total runtime: 0.709 ms
(9 rows)


--------[with the default join_collapse_limit]-----------
> fli=# explain analyze
>  select *
>      from streetname_lookup as sl
>      join city_lookup as cl on (true)
>      join tlid_smaller as ts on (sl.geo_streetname_id = ts.geo_streetname_id and cl.geo_city_id=ts.geo_city_id)
>      where  str_name='alamo' and  city='san antonio' and state='TX'
> ;
> fli-# fli-# fli-# fli-# fli-# fli-#                                                                              QUERY PLAN                                                                   \
> 
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Hash Join  (cost=6.01..29209.16 rows=1 width=74) (actual time=9.421..28.154 rows=78 loops=1)
>    Hash Cond: ("outer".geo_city_id = "inner".geo_city_id)
>    ->  Nested Loop  (cost=0.00..29202.88 rows=52 width=51) (actual time=0.064..23.296 rows=4151 loops=1)
>          ->  Index Scan using streetname_lookup__str_name on streetname_lookup sl  (cost=0.00..3.01 rows=1 width=19) (actual time=0.025..0.032 rows=1 loops=1)
>                Index Cond: (str_name = 'alamo'::text)
>          ->  Index Scan using tlid_smaller__street_zipint on tlid_smaller ts  (cost=0.00..28994.70 rows=16413 width=32) (actual time=0.028..8.153 rows=4151 loops=1)
>                Index Cond: ("outer".geo_streetname_id = ts.geo_streetname_id)
>    ->  Hash  (cost=6.01..6.01 rows=1 width=23) (actual time=0.073..0.073 rows=0 loops=1)
>          ->  Index Scan using city_lookup__name on city_lookup cl  (cost=0.00..6.01 rows=1 width=23) (actual time=0.065..0.067 rows=1 loops=1)
>                Index Cond: ((city = 'san antonio'::text) AND (state = 'TX'::text))
>  Total runtime: 28.367 ms
> (11 rows)
> 



view thread (7+ messages)  latest in thread

Message-ID: <Pine.LNX.4.58.0503301241541.9713@greenie.cheapcomplexdevices.com>
Permalink:  ../Pine.LNX.4.58.0503301241541.9713@greenie.cheapcomplexdevices.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.58.0503301241541.9713@greenie.cheapcomplexdevices.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: rm_pg@cheapcomplexdevices.com
  Subject: Re: Left Outer Join much faster than non-outer Join?
  In-Reply-To: <Pine.LNX.4.58.0503301241541.9713@greenie.cheapcomplexdevices.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