Обсуждение: Subquery pull-up increases jointree search space

Andrei Lepikhov <lepihov@gmail.com> writes:
>  From time to time, I see user complaints on performance degradation
> caused by newly introduced transformation - it is usually related to
> correlated subplan transformation as well as trivial initplan → join
> transformation.
> The original issue (according to my case analysis) is usually that, by
> adding such a pull-up optimiser excess join collapse limit. As a result,
> the query tree tail, which was previously ordered according to the cost
> model, is now determined mechanically, sometimes causing severe
> degradation in execution time.

Certainly that's possible, but I doubt it's common enough to justify
putting a lot of work into specialized mechanisms to deal with such
scenarios.

What I'm wondering about is that join_collapse_limit and
from_collapse_limit were invented more than two decades ago, but
we've not touched their default values since then.  Machines are a
lot faster since 2004, and we've probably achieved some net speedups
in the planner logic as well.  Could we alleviate this concern by
raising those defaults, and if so, what are reasonable values in 2026?

(geqo_threshold should probably be looked at too.)

            regards, tom lane

On 9/2/26 21:16, Tom Lane wrote:
> Andrei Lepikhov <lepihov@gmail.com> writes:
>>   From time to time, I see user complaints on performance degradation
>> caused by newly introduced transformation - it is usually related to
>> correlated subplan transformation as well as trivial initplan → join
>> transformation.
>> The original issue (according to my case analysis) is usually that, by
>> adding such a pull-up optimiser excess join collapse limit. As a result,
>> the query tree tail, which was previously ordered according to the cost
>> model, is now determined mechanically, sometimes causing severe
>> degradation in execution time.
> 
> Certainly that's possible, but I doubt it's common enough to justify
> putting a lot of work into specialized mechanisms to deal with such
> scenarios.

I feel the same. But what is also true is that people complain about 
that when migrating from other DBMSes. And it is a common question: why 
does the alternative one perform better? What technology lies under the 
hood?

That's why I think about some tiny changes to let extension developers 
discover the problem and find a workaround. Or we can put pulled-up 
relations at the end of the join list to minimise impact on the query 
plan during an upgrade.

What's more, where more flaws exist (a typical example is the 
limitations of the partition pruning state machine), they may also be 
fixed by replanning with alternative settings or other extension tricks, 
if the problem can be detected and memorised.

> 
> What I'm wondering about is that join_collapse_limit and
> from_collapse_limit were invented more than two decades ago, but
> we've not touched their default values since then.  Machines are a
> lot faster since 2004, and we've probably achieved some net speedups
> in the planner logic as well.  Could we alleviate this concern by
> raising those defaults, and if so, what are reasonable values in 2026?

As I see, people never use the default settings now. The case that 
triggered this topic could work well with a join collapse limit around 
40 joins (GEQO started at 14). But a specific setting always depends on 
how much time people want to spend on planning. So, I don't think a 
change of default settings is needed.

-- 
regards, Andrei Lepikhov,
pgEdge

On Mon, Feb 9, 2026 at 3:17 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> What I'm wondering about is that join_collapse_limit and
> from_collapse_limit were invented more than two decades ago, but
> we've not touched their default values since then.  Machines are a
> lot faster since 2004, and we've probably achieved some net speedups
> in the planner logic as well.  Could we alleviate this concern by
> raising those defaults, and if so, what are reasonable values in 2026?

The problem as I see it is that the planning time growth is
exponential, and so faster hardware doesn't necessarily buy you very
much, especially given that we've added new planner techniques that
add to the number of paths considered. But I also think that the
degenerate cases are much worse than the typical cases. For example, I
seem to remember things like A LEFT JOIN (B1 INNER JOIN B2 INNER JOIN
B3...) LEFT JOIN (C1 INNER JOIN C2 INNER JOIN C3...) [repeat with D,
E, etc.] being a problem, maybe for GEQO, because a
randomly-determined join order isn't likely to be valid. I think there
are similar issues with join_collapse_limit etc, for example because
we prefer joins that have joinclauses over those that don't, so the
actual planner work can be wildly different with the same number of
joins in the query. I suspect the thing that we need in order to be
able to safely raise these thresholds is for somebody to spend some
time figuring out what the pathologically bad cases are and designing
some sort of mitigations specifically for those. Or, alternatively, we
could decide that we've been too pessimistic and set slightly riskier
values by default, expecting that they'll work out most of the time
and that users can lower the setting if there's an issue.

--
Robert Haas
EDB: http://www.enterprisedb.com