Re: WIP Join Removal

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
Cc: List pgsql-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: WIP Join Removal
Date: 2008-09-02 11:38:18
Message-ID: 1220355498.4371.372.camel@ebony.2ndQuadrant
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-patches

On Tue, 2008-09-02 at 10:41 +0100, Simon Riggs wrote:
> On Mon, 2008-09-01 at 22:23 +0300, Heikki Linnakangas wrote:

> > Couldn't we also do join removal for inner joins, when there's a foreign
> > key reference that enforces that there's one and only one matching tuple
> > in the removed table:
> >
> > SELECT FROM child, parent WHERE child.fkey = parent.pkey
> Hmm, I had thought this was the same case, but the inner join
> possibility wasn't something I'd seen. Guess that flaw shows this is all
> original thought - I'll go back and read that optimizer blog again...
> I agree it will work.
> We would need to replace the join condition with an alteration of the
> original quals on child so that we add "AND child.fkey is not null".
> Which would mean we would need to re-plan the access to that base
> relation so we picked up the new qual and potentially used an index for
> it as well. That would be possible only if the join condition exactly
> matches the FK constraint.

Also, note that when we do an inner join we must not have any
qualification of the rows on the checkrel. Any qualification that
removes rows will alter the answer.

This is a direct contrast to the left outer join case where the presence
or absence of a single table qualification on the checkrel has *no
effect* on the results of the query (as long as the qual is immutable).

Simon Riggs
PostgreSQL Training, Services and Support

In response to

Browse pgsql-patches by date

  From Date Subject
Next Message Simon Riggs 2008-09-02 11:53:50 Re: WIP Join Removal
Previous Message Heikki Linnakangas 2008-09-02 11:20:41 Re: WIP Join Removal