On Tue, Sep 10, 2019 at 9:49 PM Tom Lane <t...@sss.pgh.pa.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
