Re: Reduce LEFT/FULL JOIN to ANTI JOIN in more cases

On Thu, 4 Jun 2026 at 22:55, Richard Guo <guofenglinux(at)gmail(dot)com> wrote:

> reduce_outer_joins() already recognizes that a LEFT JOIN is really an
> anti-join when an upper qual forces a nullable-side Var to be NULL
> while that Var is actually non-null in every matching row. In that
> case only the null-extended (unmatched) rows can satisfy the upper
> qual, which is exactly anti-join semantics, so we switch JOIN_LEFT to
> JOIN_ANTI. This is worth detecting because an anti-join is usually
> much cheaper than computing the outer join and then filtering the
> result with the IS NULL clause.

While you’re at it, any chance of changing it so that "a LEFT JOIN b WHERE
b IS NULL" is guaranteed not to have worse performance than " a LEFT JOIN b
WHERE b.f IS NULL"? I've had this and asked about it here and it was
suggested that I should specify the primary key fields of b; but surely if
I want an antijoin it's clearer and better implicit documentation if I
don't zero in on specific fields. I think somebody else is suggesting "a
LEFT ANTI JOIN b" which would be even better.