Re: Convert ALL SubLinks to ANY SubLinks

HI
> If pulling up a subquery results in a suboptimal join order due to the
> number of relations, the root cause lies in the limitations of the
> join search algorithm itself. It doesn't seem reasonable to penalize
> the general subquery flattening mechanism for that limitation, and it
> makes even less sense to fault this specific transformation, which
> simply converts ALL_SUBLINK to ANY_SUBLINK so it can benefit from the
> same hashed SubPlan and flattening mechanics that other subqueries
>already enjoy.
Agree +1

Thanks

On Fri, Feb 27, 2026 at 9:42 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:

> On Thu, Feb 26, 2026 at 8:37 PM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
> > I want to correct your statement on the 'no regression' phrase. In
> > practice, users often report issues after each new sublink
> > transformation goes live.
> >
> > This happens because the transformation changes the 'join problem'
> > order. Before, the subplan's join list was handled independently, but
> > now its relations are mixed with those from higher levels. If the join
> > collapse limit is exceeded, this can sometimes cause much worse
> > performance than in earlier Postgres versions.
>
> I'm not convinced this is a regression. In scenarios where the join
> tree becomes too large, wouldn't the standard solution be for the user
> to tune join_collapse_limit (and maybe also geqo_threshold)?
>
> If pulling up a subquery results in a suboptimal join order due to the
> number of relations, the root cause lies in the limitations of the
> join search algorithm itself. It doesn't seem reasonable to penalize
> the general subquery flattening mechanism for that limitation, and it
> makes even less sense to fault this specific transformation, which
> simply converts ALL_SUBLINK to ANY_SUBLINK so it can benefit from the
> same hashed SubPlan and flattening mechanics that other subqueries
> already enjoy.
>
> - Richard
>
>
>