Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 56C239FB29A for ; Mon, 30 Oct 2006 11:40:14 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 51308-01 for ; Mon, 30 Oct 2006 11:40:01 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mxfep03.bredband.com (mxfep03.bredband.com [195.54.107.76]) by postgresql.org (Postfix) with ESMTP id 0A83C9FB280 for ; Mon, 30 Oct 2006 11:39:59 -0400 (AST) Received: from ironport.bredband.com ([195.54.107.82] [195.54.107.82]) by mxfep03.bredband.com with ESMTP id <20061030153958.HPGD25623.mxfep03.bredband.com@ironport.bredband.com> for ; Mon, 30 Oct 2006 16:39:58 +0100 Received: from c-a009e155.242-5-64736c11.cust.bredbandsbolaget.se (HELO server.kregert.se) ([85.225.9.160]) by ironport.bredband.com with ESMTP/TLS/AES256-SHA; 30 Oct 2006 16:39:57 +0100 Received: from knatte (knatte.kregert.se [10.10.0.9]) by server.kregert.se (8.13.6/8.13.4) with SMTP id k9UFZunS007243; Mon, 30 Oct 2006 16:35:56 +0100 Message-ID: <003e01c6fc39$6970bf90$09000a0a@kregert.se> From: "Mattias Kregert" To: "Tom Lane" Cc: References: <200610301205.08087.mattias@kregert.se> <16059.1162220969@sss.pgh.pa.us> Subject: Re: Strange plan in pg 8.1.0 Date: Mon, 30 Oct 2006 16:38:13 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/613 X-Sequence-Number: 21742 From: "Tom Lane" > 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 > Thanks! I'll try some subselect solution for now, and make a note to change it when 8.2 is out. /* m */