Re: Left Outer Join much faster than non-outer Join?

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(at)cheapcomplexdevices(dot)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)
>