Re: Left Outer Join much faster than non-outer Join? - Mailing list pgsql-performance

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)
(actualtime=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.028rows=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.181rows=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-#
QUERYPLAN                                                                   \ 
>
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  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)
(actualtime=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.067rows=1 loops=1) 
>                Index Cond: ((city = 'san antonio'::text) AND (state = 'TX'::text))
>  Total runtime: 28.367 ms
> (11 rows)
>