Re: WIP Join Removal

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 child.data 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 www.2ndQuadrant.com
PostgreSQL Training, Services and Support