Re: Performance improvement for joins where outer side is unique

David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> I can make this change, but before I do I just want to point that I
> don't think what you've said here is entirely accurate.

> Let's assume unique joins are very common place, and join removals are
> not so common. If a query has 5 left joins, and only one of which can
> be removed, then the new code will most likely perform 5 unique join
> checks, whereas the old code would perform 9, as those unique checks
> are performed again once the 1 relation is removed for the remaining
> 4.

I'm not following. If the join removal code had reached the stage of
making a uniqueness check, and that check had succeeded, the join would be
gone and there would be no repeat check later. If it didn't reach the
stage of making a uniqueness check, then again there's no duplication.
There will be some advantage in making a negative cache entry if join
removal performs a uniqueness check that fails, but I don't really see
why that's hard. It does not seem like removal of a relation could
cause another rel to become unique that wasn't before, so keeping
negative cache entries across join removals ought to be safe.

regards, tom lane