Re: WIP Join Removal - Mailing list pgsql-patches

On Mon, 2008-09-01 at 22:23 +0300, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > Patch works, but there's a bit I haven't finished yet - checking unique
> > indexes.
>
> Did plan invalidation make it safe to rely on the presence of a unique
> index for planning decisions?

My understanding was "Yes" and this case was the specific reason I
originally wanted to pursue plan invalidation back in 2006.

> 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.

Hmm, will think about that, its certainly not an easy addition, for me.
I'll concentrate on getting this patch finished and committed first.

> > +     /*
> > +      * We can now remove join by pulling up child plan from the keeprel.
> > +      * This needs to be done considering costs, since its possible for
> > +      * a nested inner indexscan plan to be cheaper. So it isn't
> > +      * always desirable to remove the join.
>
> Can you elaborate that a bit? I can't imagine a case where we wouldn't
> want to remove a join, when we know we can.

Neither could I when I first looked at this.

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.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support