Re: Pulling up direct-correlated ANY_SUBLINK

Richard Guo <riguo@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.