All Your GUCs in a Row: from_collapse_limit

PostgreSQL's from_collapse_limit GUC controls whether subqueries in FROM clauses get flattened into the outer query for joint planning. The default value of 8 means the planner merges subqueries only when the resulting FROM list stays at or below 8 items. Flattening allows the planner to consider cross-subquery join orders, often producing better plans, but the search space grows factorially with more relations. Subqueries with aggregation, DISTINCT, LIMIT, OFFSET, window functions, or set operations are never flattened regardless of this setting — which is the basis of the OFFSET 0 fence trick. Raising the limit above 12 risks triggering GEQO (genetic query optimizer), which samples rather than exhaustively searches join orders and can produce worse plans. If you raise from_collapse_limit, also raise geqo_threshold above it. Best practice: keep it at 8 in postgresql.conf and experiment per-session using the user context.

Source: https://postgr.es/p/9qj. 8sync News only summarizes and links out; content copyright belongs to the authors and original sources.