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

Hi,

On 2024-12-07 17:06:52 -0500, Tom Lane wrote:
> 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.

Yea, just sent an email saying so after realizing the same.

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

As I wrote in my other email, I'm also somewhat dubious it's worth having
explicit code for this in nodeNestloop.c.

Not convinced that such queries are that hokey though, it's not that rare a
thing to want to left join to some other table only if the outer side matches
some attribute. We ourselves do have a few cases like that in our queries,
e.g. psql's describeOneTableDetails(), pg_dump's getTables() and several
others. ...

Greetings,

Andres Freund