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 [EMAIL PROTECTED] 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)
>
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
