On 9/2/26 21:16, Tom Lane wrote:
Andrei Lepikhov <[email protected]> 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
