Re: Subquery pull-up increases jointree search space

On Mon, Feb 9, 2026 at 3:17 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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