join_collapse_limit

Definition PG 18 manual

The planner will rewrite explicit JOIN constructs (except FULL JOINs) into lists of FROM items whenever a list of no more than this many items would result. Smaller values reduce planning time but might yield inferior query plans.

By default, this variable is set the same as from_collapse_limit, which is appropriate for most uses. Setting it to 1 prevents any reordering of explicit JOINs. Thus, the explicit join order specified in the query will be the actual order in which the relations are joined. Because the query planner does not always choose the optimal join order, advanced users can elect to temporarily set this variable to 1, and then specify the join order they desire explicitly. 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

join_collapse_limit controls when explicit JOIN constructs, except FULL JOIN, are flattened into a reorderable FROM list. A value of 1 preserves the written explicit join order.

Flattening enlarges the set of join orders the planner can explore, improving opportunities at the cost of planning CPU and memory. Outer-join semantics still constrain legal reorderings.

The exposed item count interacts with from_collapse_limit and geqo_threshold. Using 1 as a manual join-order tool transfers responsibility to SQL authors and is not a general plan-stability guarantee. Its user context permits session- or transaction-local changes; newly performed or newly planned work sees the value.

Operational considerations

Treating join_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 join_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 join_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 join_collapse_limit without a measured benefit. Prefer query-local structure or a scoped role setting over a cluster-wide override.