Skip site navigation (1) Skip section navigation (2)

Re: WIP Join Removal

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Simon Riggs <simon(at)2ndQuadrant(dot)com>
Cc: List pgsql-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: WIP Join Removal
Date: 2008-09-02 11:03:24
Message-ID: 48BD1D7C.6090002@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-patches
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

In response to

Responses

pgsql-patches by date

Next:From: Gregory StarkDate: 2008-09-02 11:05:38
Subject: Re: WIP Join Removal
Previous:From: Simon RiggsDate: 2008-09-02 10:56:35
Subject: Re: rmgr hooks and contrib/rmgr_hook

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group