Re: Convert ALL SubLinks to ANY SubLinks

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