> Can we try to pull up direct-correlated ANY SubLink with the help of > LATERAL?
> By this way, we can convert the query: > > select * from a where a.i = ANY(select i from b where a.j > b.j); > > To: > > select * from a SEMI JOIN lateral(select * from b where a.j > b.j) > sub on a.i = sub.i; >
I tried this a few years ago. This is where the problems started:
Thank you for this link. Good to know the discussions years ago.
I'm not sure I remember enough, but the problem has something to do with one possible strategy to plan SEMI JOIN: unique-ify the inner path and then perform plain INNER JOIN instead.
I think the problemm was that the WHERE clause of the subquery didn't participate in the SEMI JOIN evaluation and was used as filter instead. Thus the clause's Vars were not used in unique keys of the inner path and so the SEMI JOIN didn't work well.
This used to be a problem until it was fixed by commit 043f6ff0, which includes the postponed qual from a LATERAL subquery into the quals seen by make_outerjoininfo().