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 49E4153B6E for ; Thu, 31 Mar 2005 05:08:04 +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 84831-03 for ; Thu, 31 Mar 2005 04:07:54 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 603C453A5E for ; Thu, 31 Mar 2005 05:07:53 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2V47qt9001756; Wed, 30 Mar 2005 23:07:52 -0500 (EST) To: rm_pg@cheapcomplexdevices.com Cc: pgsql-performance@postgresql.org Subject: Re: Left Outer Join much faster than non-outer Join? In-reply-to: References: Comments: In-reply-to rm_pg@cheapcomplexdevices.com message dated "Wed, 30 Mar 2005 12:37:08 -0800" Date: Wed, 30 Mar 2005 23:07:52 -0500 Message-ID: <1755.1112242072@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/674 X-Sequence-Number: 11379 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; why don't you have any join condition between streetname_lookup and city_lookup? 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 ... regards, tom lane