from_collapse_limit

Definition PG 18 manual

The planner will merge sub-queries into upper queries if the resulting FROM list would have no more than this many items. Smaller values reduce planning time but might yield inferior query plans. The default is eight. For more information see Section 14.3.

Setting this value to geqo_threshold or more may trigger use of the GEQO planner, resulting in non-optimal plans. See Section 19.7.3.

How it works

from_collapse_limit bounds how far the planner merges eligible subqueries into the parent FROM list. Flattening exposes more join orders and predicate movement opportunities but enlarges the search space.

If flattening would produce more FROM items than the limit, the subquery boundary is retained. Smaller values can reduce planning time while hiding a better global join order.

The resulting relation count interacts with join_collapse_limit and geqo_threshold; raising this limit can unexpectedly move the query from exhaustive planning into GEQO. Its user context permits session- or transaction-local changes; newly performed or newly planned work sees the value.

Operational considerations

Treating from_collapse_limit as an executor resource limit rather than a planning assumption or policy.

Testing only one parameter set or one data distribution.

Expecting an already cached plan to be rewritten automatically.

Using a global override to hide stale statistics or fragile SQL structure.

Workload guidance

OLAP: Analytical SQL can make from_collapse_limit more visible because joins, cursors, or recursion are larger. Benchmark the full statement family and inspect estimates rather than copying a single successful value.

OLTP: Keep from_collapse_limit at its upstream default unless representative plans show a repeatable, workload-wide problem. Test a local override first and include planning latency as well as execution latency.

SMALL: On a small host, avoid increasing planning search or memory pressure through from_collapse_limit without a measured benefit. Prefer query-local structure or a scoped role setting over a cluster-wide override.