Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 419819FA339 for ; Sun, 3 Dec 2006 12:30:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 30120-05 for ; Sun, 3 Dec 2006 12:30:29 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id 85BD29FA19A for ; Sun, 3 Dec 2006 12:30:29 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so2491590ugf for ; Sun, 03 Dec 2006 08:30:28 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=RJDtOt2M5Z/u/ApqKA7Gkkzkf7p2S8Cxq4Sf88f4K9aJyG9oaM7XXeqd6zLTi+UEtJDvWkzasl8jDwOUVz+1hZGJvnUaX58vuQK6Cs9FDlZA9WwUymz9eB68CjFXuJlsaGCC7ouN+3LGzNAo+/UAfGRHDM9+2O8cmKs28hdNnrc= Received: by 10.78.201.10 with SMTP id y10mr6868368huf.1165163428194; Sun, 03 Dec 2006 08:30:28 -0800 (PST) Received: by 10.78.204.8 with HTTP; Sun, 3 Dec 2006 08:30:28 -0800 (PST) Message-ID: <19ab0ccd0612030830t68d9a7ddoba02f060fd9cdea4@mail.gmail.com> Date: Sun, 3 Dec 2006 11:30:28 -0500 From: "Aaron Birkland" To: "Jonathan Blitz" Subject: Re: Propagating outer join conditions Cc: pgsql-performance@postgresql.org In-Reply-To: <021f01c716ef$3f5701d0$647ba8c0@jonathanlaptop> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <19ab0ccd0612030711s70a3e095hceb69509cbd41597@mail.gmail.com> <021f01c716ef$3f5701d0$647ba8c0@jonathanlaptop> X-Google-Sender-Auth: c7482b89abc8c0b9 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/30 X-Sequence-Number: 22086 First, I forgot to mention - this is 8.2 RC1 I was trying on The suggested change produces an identical 'bad' query plan. The main issue (I think) is that the query node that processes "t1 JOIN t11 ON ..' is not aware of the join condition 't28.s = t1.s'.. even though the value of t28.s (as determined by the inner index scan where t28.o = 'spec') could(should?) theoretically be known to it. If it did, then I imagine it would realize that a nested loop join starting with t1.s = t28.s (which is very selective) would be much cheaper than doing the big merge join. -Aaron On 12/3/06, Jonathan Blitz wrote: > How about trying: > > Select * > From > (Select * from t28 where t28.0='spec') t28a > Left out join (t1 JOIN t11 ON > > (t11.o = '' AND t11.s = t1.o)) ON t28a.s = t1.s > > In this way, I think, the where clause on t28 would be performed before the > join rather than after.