Tom Lane wrote:[EMAIL PROTECTED] writes:select *That's a fairly odd query;
from streetname_lookup as sl
join city_lookup as cl on (true)
left outer 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'
;
I think it's a very common type of query in data warehousing.
It's reasonably typical of a traditional star schema where "streetname_lookup" and "city_lookup" are dimension tables and "tlid_smaller" is the central fact table.
Although looking again I must admit the query was written unconventionally. Perhaps those queries are remnants dating back to a version when you could force join orders this way?
Perhaps a more common way of writing it would have been:
select * from tlid_smaller
where geo_streetname_id in (select geo_streetname_id from streetname_lookup
where str_name='$str_name')
and geo_city_id in (select geo_city_id from city_lookup where
city='$city' and state='$state');However this query also fails to use the multi-column index on (geo_streetname_id,geo_city_id). Explain analyze shown below.
In cases where I can be sure only one result will come from each of the lookup queries I guess I can do this:
select * from tlid_smaller
where geo_streetname_id = (select geo_streetname_id from streetname_lookup
where str_name='$str_name')
and geo_city_id = (select geo_city_id from city_lookup where
city='$city' and state='$state');which has the nicest plan of them all (explain analyze also shown below).
> With the tables I have (shown below), how else could one > efficiently fetch the data for "Main St" "San Francisco"?
I guess I just answered that question myself. Where possible, I'll write my queries this way.
Thanks,
Ron
fli=# fli=# explain analyze select * from tlid_smaller where geo_streetname_id in (select geo_streetname_id from streetname_lookup where str_name='alamo') and geo_city_id in (select geo_city_id from city_lookup where city='san antonio' and state='TX'); fli-# fli-# QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash IN Join (cost=9.03..29209.16 rows=1 width=32) (actual time=76.576..96.605 rows=78 loops=1) Hash Cond: ("outer".geo_city_id = "inner".geo_city_id) -> Nested Loop (cost=3.02..29202.88 rows=52 width=32) (actual time=65.877..91.789 rows=4151 loops=1) -> HashAggregate (cost=3.02..3.02 rows=1 width=4) (actual time=0.039..0.042 rows=1 loops=1) -> Index Scan using streetname_lookup__str_name on streetname_lookup (cost=0.00..3.01 rows=1 width=4) (actual time=0.025..0.028 rows=1 loops=1) Index Cond: (str_name = 'alamo'::text) -> Index Scan using tlid_smaller__street_zipint on tlid_smaller (cost=0.00..28994.70 rows=16413 width=32) (actual time=65.820..81.309 rows=4151 loops=1) Index Cond: (tlid_smaller.geo_streetname_id = "outer".geo_streetname_id) -> Hash (cost=6.01..6.01 rows=1 width=4) (actual time=0.054..0.054 rows=0 loops=1) -> Index Scan using city_lookup__name on city_lookup (cost=0.00..6.01 rows=1 width=4) (actual time=0.039..0.041 rows=1 loops=1) Index Cond: ((city = 'san antonio'::text) AND (state = 'TX'::text)) Total runtime: 97.577 ms (12 rows)
fli=#
fli=# explain analyze select * from tlid_smaller
where geo_streetname_id = (select geo_streetname_id from
streetname_lookup where str_name='alamo')
and geo_city_id = (select geo_city_id from city_lookup where
city='san antonio' and state='TX');fli-# fli-#
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tlid_smaller__street_city on tlid_smaller (cost=9.02..16.88
rows=3 width=32) (actual time=0.115..0.255 rows=78 loops=1)
Index Cond: ((geo_streetname_id = $0) AND (geo_city_id = $1))
InitPlan
-> Index Scan using streetname_lookup__str_name on streetname_lookup
(cost=0.00..3.01 rows=1 width=4) (actual time=0.044..0.047 rows=1 loops=1)
Index Cond: (str_name = 'alamo'::text)
-> Index Scan using city_lookup__name on city_lookup (cost=0.00..6.01
rows=1 width=4) (actual time=0.028..0.030 rows=1 loops=1)
Index Cond: ((city = 'san antonio'::text) AND (state = 'TX'::text))
Total runtime: 0.474 ms
(8 rows)---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
