Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2EC1664FFF8 for ; Tue, 2 Sep 2008 07:21:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 23101-04 for ; Tue, 2 Sep 2008 07:20:56 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 77E54650316 for ; Tue, 2 Sep 2008 07:20:43 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id 29so1713939ugo.7 for ; Tue, 02 Sep 2008 03:20:40 -0700 (PDT) Received: by 10.67.90.10 with SMTP id s10mr3673665ugl.38.1220350839692; Tue, 02 Sep 2008 03:20:39 -0700 (PDT) Received: from ?80.222.42.69? ( [80.222.42.69]) by mx.google.com with ESMTPS id 33sm5886465nfu.7.2008.09.02.03.20.38 (version=TLSv1/SSLv3 cipher=RC4-MD5); Tue, 02 Sep 2008 03:20:39 -0700 (PDT) Message-ID: <48BD1374.4030504@enterprisedb.com> Date: Tue, 02 Sep 2008 13:20:36 +0300 Organization: EnterpriseDB User-Agent: Mozilla-Thunderbird 2.0.0.14 (X11/20080509) MIME-Version: 1.0 To: Simon Riggs CC: List pgsql-patches Subject: Re: WIP Join Removal References: <1220176372.4371.118.camel@ebony.2ndQuadrant> <48BC4127.30706@enterprisedb.com> <1220348487.4371.307.camel@ebony.2ndQuadrant> In-Reply-To: <1220348487.4371.307.camel@ebony.2ndQuadrant> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit From: Heikki Linnakangas X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200809/6 X-Sequence-Number: 7021 Simon Riggs wrote: > It turns out that a join like this > > select a.col2 > from a left outer join b on a.col1 = b.col1 > where b.col2 = 1; > > can be cheaper if we don't remove the join, when there is an index on > a.col1 and b.col2, because the presence of b allows the values returned > from b to be used for an index scan on a. Umm, you *can't* remove that join. Because of the condition "b.col2 = 1", which implies that "b.col1 IS NOT NULL", that's actually equal to: select a.col2 from a inner join b on a.col1 = b.col1 where b.col2 = 1; -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com