X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9981EDAC33 for ; Tue, 25 Oct 2005 11:12:00 -0300 (ADT) 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 42050-09 for ; Tue, 25 Oct 2005 14:11:56 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9576DDA0DE for ; Tue, 25 Oct 2005 11:11:58 -0300 (ADT) 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 j9PEC2ER013680; Tue, 25 Oct 2005 10:12:02 -0400 (EDT) To: Rich Doughty Cc: pgsql-performance@postgresql.org Subject: Re: Outer join query plans and performance In-reply-to: <435E186A.3060003@opusvl.com> References: <435E186A.3060003@opusvl.com> Comments: In-reply-to Rich Doughty message dated "Tue, 25 Oct 2005 12:35:06 +0100" Date: Tue, 25 Oct 2005 10:12:02 -0400 Message-ID: <13679.1130249522@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200510/361 X-Sequence-Number: 15129 Rich Doughty writes: > EXPLAIN SELECT * > FROM > tokens.ta_tokens t LEFT JOIN > tokens.ta_tokenhist h1 ON t.token_id = h1.token_id LEFT JOIN > tokens.ta_tokenhist h2 ON t.token_id = h2.token_id > WHERE > h1.histdate = 'now'; > EXPLAIN SELECT * > FROM > tokens.ta_tokens t LEFT JOIN > tokens.ta_tokenhist h1 ON t.token_id = h1.token_id LEFT JOIN > tokens.ta_tokenhist h2 ON t.token_id = h2.token_id > WHERE > h2.histdate = 'now'; The reason these are different is that the second case constrains only the last-to-be-joined table, so the full cartesian product of t and h1 has to be formed. If this wasn't what you had in mind, you might be able to rearrange the order of the LEFT JOINs, but bear in mind that in general, changing outer-join ordering changes the results. (This is why the planner won't fix it for you.) regards, tom lane