Re: Do not scan index in right table if condition for left join evaluates to false using columns in left table

Andres Freund <andres(at)anarazel(dot)de> writes:
> ISTM that it shouldn't be expensive to recognize this type of join clause and
> pushes them down. While it could be done by the query's author, it seems worth
> handling this on our side. But maybe I'm missing something here?

No, that condition *can't* be pushed down to the LHS scan, because its
failure should not remove LHS rows from the output; it can only cause
them to have nulls in the RHS columns.

One could imagine that we split up the join filter conditions into
"depends on RHS" and "doesn't depend on RHS" subsets, and make the
nestloop plan node evaluate the latter set only once per LHS row,
and then skip the inner-side scan when that condition fails.
But this would be a bunch of new mechanism that's only useful for
outer joins, only for rather hokey outer join conditions, and only
for nestloop-type joins. I'm pretty dubious that it's worth the
trouble -- not least because I don't recall anybody complaining
about this before.

regards, tom lane