Re: Pulling up direct-correlated ANY_SUBLINK

Hi Antonin,

On Tue, Sep 10, 2019 at 4:31 PM Antonin Houska <ah(at)cybertec(dot)at> wrote:

> Richard Guo <riguo(at)pivotal(dot)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

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

Thanks
Richard