From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
To: Simon Riggs <simon@2ndQuadrant.com>
Cc: List pgsql-patches <pgsql-patches@postgresql.org>
Subject: Re: WIP Join Removal
Date: Tue, 02 Sep 2008 14:03:24 +0300
Message-ID: <48BD1D7C.6090002@enterprisedb.com> (raw)
In-Reply-To: <1220352898.4371.355.camel@ebony.2ndQuadrant>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-patches@postgresql.org
Cc: heikki.linnakangas@enterprisedb.com, simon@2ndQuadrant.com
Subject: Re: WIP Join Removal
In-Reply-To: <48BD1D7C.6090002@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox