Richard Guo <ri...@pivotal.io> wrote: > 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: https://www.postgresql.org/message-id/1386716782.5203.YahooMailNeo%40web162905.mail.bf1.yahoo.com 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. -- Antonin Houska Web: https://www.cybertec-postgresql.com
