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