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 C183E52A3A for ; Thu, 31 Mar 2005 08:03:22 +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 25888-10 for ; Thu, 31 Mar 2005 07:03:17 +0000 (GMT) Received: from ylpvm29.prodigy.net (ylpvm29-ext.prodigy.net [207.115.57.60]) by svr1.postgresql.org (Postfix) with ESMTP id CA42E52A39 for ; Thu, 31 Mar 2005 08:03:15 +0100 (BST) Received: from one (adsl-68-124-182-149.dsl.pltn13.pacbell.net [68.124.182.149]) by ylpvm29.prodigy.net (8.12.10 outbound/8.12.10) with ESMTP id j2V72jnr031656; Thu, 31 Mar 2005 02:02:45 -0500 Received: from [192.168.5.103] (unknown [192.168.5.103]) by one (Postfix) with ESMTP id 79F39F1A5C; Wed, 30 Mar 2005 23:03:04 -0800 (PST) Message-ID: <424BA115.8050606@cheapcomplexdevices.com> Date: Wed, 30 Mar 2005 23:04:53 -0800 From: Ron Mayer User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: rm_pg@cheapcomplexdevices.com, pgsql-performance@postgresql.org Subject: Re: Left Outer Join much faster than non-outer Join? References: <1755.1112242072@sss.pgh.pa.us> In-Reply-To: <1755.1112242072@sss.pgh.pa.us> 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.044 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/675 X-Sequence-Number: 11380 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. > why don't you have any join condition between > streetname_lookup and city_lookup? Those two tables shared no data. They merely get the "id"s for looking things up in the much larger central table. Unique indexes on the city_lookup and street_lookup make the cartesian join harmless (they each return only 1 value); and the huge fact table has a multi-column index that takes both of the ids from those lookups. With the tables I have (shown below), how else could one efficiently fetch the data for "Main St" "San Francisco"? streetname_lookup (for every street name used in the country) streetid | name | type ----------+--------+------ 1 | Main | St 2 | 1st | St city_lookup (for every city name used in the country) cityid | name | state --------+---------+------ 1 | Boston | MA 2 | Alameda| CA tlid_smaller (containing a record for every city block in the country) city_id | street_id | addresses | demographics, etc. --------+------------+-----------+---------------------- 1 | 1 | 100 block | [lots of columns] 1 | 1 | 200 block | [lots of columns] 1 | 1 | 300 block | [lots of columns] 1 | 2 | 100 block | [lots of columns] 1 | 2 | 100 block | [lots of columns] > The planner won't consider Cartesian joins unless forced to, which is > why it fails to consider the join order "((sl join cl) join ts)" unless > you have an outer join in the mix. I think that's generally a good > heuristic, and am disinclined to remove it ... IMHO it's a shame it doesn't even consider it when the estimated results are very small. I think often joins that merely look up IDs would be useful to consider for the purpose of making potential multi-column indexes (as shown in the previous email's explain analyze result where the cartesian join was 30X faster than the other approach since it could use the multi-column index on the very large table). Ron