X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 51EF95365F for ; Thu, 31 Mar 2005 09:14:18 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 46677-03 for ; Thu, 31 Mar 2005 08:14:09 +0000 (GMT) Received: from ylpvm01.prodigy.net (ylpvm01-ext.prodigy.net [207.115.57.32]) by svr1.postgresql.org (Postfix) with ESMTP id 3BA0D533EF for ; Thu, 31 Mar 2005 09:14:08 +0100 (BST) Received: from one (adsl-68-124-182-149.dsl.pltn13.pacbell.net [68.124.182.149]) by ylpvm01.prodigy.net (8.12.10 outbound/8.12.10) with ESMTP id j2V8E84H003168; Thu, 31 Mar 2005 03:14:08 -0500 Received: from [192.168.5.103] (unknown [192.168.5.103]) by one (Postfix) with ESMTP id 112CFF1A5C; Thu, 31 Mar 2005 00:14:06 -0800 (PST) Message-ID: <424BB1BB.90304@cheapcomplexdevices.com> Date: Thu, 31 Mar 2005 00:15:55 -0800 From: Ron Mayer User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Ron Mayer Cc: Tom Lane , pgsql-performance@postgresql.org Subject: Re: Left Outer Join much faster than non-outer Join? References: <1755.1112242072@sss.pgh.pa.us> <424BA115.8050606@cheapcomplexdevices.com> In-Reply-To: <424BA115.8050606@cheapcomplexdevices.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.03 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/676 X-Sequence-Number: 11381 Ron Mayer wrote: > Tom Lane wrote: >> rm_pg@cheapcomplexdevices.com writes: >>> select * >>> 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' >>> ; >> That's a fairly odd query; > > > 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)