Re: Pulling up direct-correlated ANY_SUBLINK

On Tue, Sep 10, 2019 at 9:49 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> > Can we try to pull up direct-correlated ANY SubLink with the help of
> > LATERAL?
>
> Perhaps. But what's the argument that you'd end up with a better
> plan? LATERAL pretty much constrains things to use a nestloop,
> so I'm not sure there's anything fundamentally different.

Sorry for the noise on replying such an old thread, but recently I
realized that pulling up direct-correlated ANY SubLink with LATERAL may
cause another problem that we cannot find any legal join order due to
the constraints imposed by LATERAL references. Below is an example:

select * from A where exists
(select * from B where A.i in (select C.i from C where C.j = B.j));

For this query, after we converting the ANY SubLink to a LATERAL
subquery, the subquery cannot be pulled up further into the parent query
because its qual contains lateral reference to 'B', which is outside a
higher semi join. When considering the join of 'A' and the 'subquery',
we decide it's not legal due to the LATERAL reference. As a result, we
end up with not finding any legal join order for level 2.

Thanks
Richard