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 -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
