Re: WIP Join Removal

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