Re: Poor performance on 9.4.4 when matching multiple columns from left side of LATERAL join

Steven Grimm <sgrimm(at)thesegovia(dot)com> writes:
> It is puzzling that if, as suggested by someone else in the thread, I
> expand IN(a,b,c) to (x = a OR x = b OR x = c) it gets substantially
> faster, though still obviously falls afoul of the problem you describe
> above (~4 seconds instead of ~6 seconds). Should those two be equivalent?

The parser actually will do that expansion for you, when the IN-list items
contain variables ... but its definition of "variable" for this purpose is
"contain_vars_of_level(expr, 0)" so the outer-level Vars you've got in
this LATERAL subquery formulation don't trigger that behavior. I seem to
remember writing it that way intentionally, but this example makes me
think maybe excluding outer-level Vars wasn't such a hot idea. It will
remain a ScalarArrayOpExpr even if the query later gets flattened to the
point where the Vars aren't outer-level anymore, which is probably not
what we want it to be.

regards, tom lane