Message-ID: <48BD1D7C.6090002@enterprisedb.com>
Date: Tue, 02 Sep 2008 14:03:24 +0300
Organization: EnterpriseDB
User-Agent: Mozilla-Thunderbird 2.0.0.14 (X11/20080509)
MIME-Version: 1.0
To: Simon Riggs <simon@2ndQuadrant.com>
CC: List pgsql-patches <pgsql-patches@postgresql.org>
Subject: Re: WIP Join Removal
References: <1220176372.4371.118.camel@ebony.2ndQuadrant>	
	<48BC4127.30706@enterprisedb.com>	
	<1220348487.4371.307.camel@ebony.2ndQuadrant>	
	<48BD1374.4030504@enterprisedb.com>	
	<1220352112.4371.349.camel@ebony.2ndQuadrant>	
	<48BD1852.1070904@enterprisedb.com>
	<1220352898.4371.355.camel@ebony.2ndQuadrant>
In-Reply-To: <1220352898.4371.355.camel@ebony.2ndQuadrant>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>

Simon Riggs wrote:
> On Tue, 2008-09-02 at 13:41 +0300, Heikki Linnakangas wrote:
>> Simon Riggs wrote:
>>> On Tue, 2008-09-02 at 13:20 +0300, Heikki Linnakangas wrote:
>>>> 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. 
>>> Yes, you can. The presence or absence of rows in b is not important to
>>> the result of the query because of the "left outer join".
>>>
>>> I spent nearly a whole day going down that deadend also.
>> Oh. How does the query look like after removing the join, then?
> 
> Same answer, just slower. Removing the join makes the access to a into a
> SeqScan, whereas it was a two-table index plan when both tables present.
> The two table plan is added by the immediately preceding call add_... -
> i.e. that plan is only added during join time not during planning of
> base relations.

I mean, can you how me an SQL query of what's left after removing the 
join? Certainly just removing the join and the WHERE clause doesn't give 
the same answer. Or is it something that can't be expressed with SQL? 
What's the filter in the SeqScan?

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com