Re: [POC] Allow flattening of subquery with a link to upper query

On 9/5/22 12:22, Richard Guo wrote:
> 
> On Fri, Sep 2, 2022 at 7:09 PM Andrey Lepikhov 
>     Yeah, it's not easy-to-solve problem. If I correctly understand the
>     code, to fix this problem we must implement the same logic, as
>     pull_up_subqueries (lowest_outer_join/safe_upper_varnos). 
> 
> Yeah, I think we'd have to consider the restrictions from lateral
> references to guarantee correctness when we pull up subqueries. We need
> to avoid the situation where quals need to be postponed past outer join.
> 
> However, even if we have taken care of that, there may be other issues
> with flattening direct-correlated ANY SubLink. The constraints imposed
> by LATERAL references may make it impossible for us to find any legal
> join orders, as discussed in [1].
> 
> [1] 
> https://www.postgresql.org/message-id/CAMbWs49cvkF9akbomz_fCCKS=D5TY=4KGHEQcfHPZCXS1GVhkA@mail.gmail.com
<https://www.postgresql.org/message-id/CAMbWs49cvkF9akbomz_fCCKS=D5TY=4KGHEQcfHPZCXS1GVhkA@mail.gmail.com>

The problem you mentioned under this link is about ineffective query 
plan - as I understand it.
This is a problem, especially if we would think about more complex 
pull-ups of subqueries - with aggregate functions in the target list.
I think about that problem as about next step - we already have an 
example - machinery of alternative plans. This problem may be solved in 
this way, or by a GUC, as usual.

-- 
Regards
Andrey Lepikhov
Postgres Professional