Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 45E389FB280 for ; Mon, 30 Oct 2006 11:09:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 67553-05 for ; Mon, 30 Oct 2006 15:09:33 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 17ED19FB1F0 for ; Mon, 30 Oct 2006 11:09:32 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k9UF9T8q016060; Mon, 30 Oct 2006 10:09:30 -0500 (EST) To: Mattias Kregert cc: pgsql-performance@postgresql.org Subject: Re: Strange plan in pg 8.1.0 In-reply-to: <200610301205.08087.mattias@kregert.se> References: <200610301205.08087.mattias@kregert.se> Comments: In-reply-to Mattias Kregert message dated "Mon, 30 Oct 2006 13:05:07 +0200" Date: Mon, 30 Oct 2006 10:09:29 -0500 Message-ID: <16059.1162220969@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/611 X-Sequence-Number: 21740 Mattias Kregert writes: > Why does it come up with this strange plan? Because 8.1 can't reorder outer joins. To devise the plan you want, the planner has to be able to prove that it's OK to perform the IN join before the LEFT join, something that isn't always the case. 8.2 can prove this, but no existing release can. The only workaround I can think of is to do the IN in a sub-select. regards, tom lane