Parameter · Query Tuning / Other Planner Options
from_collapse_limit
Sets the FROM-list size beyond which subqueries are not collapsed. The planner will merge subqueries into upper queries if the resulting FROM list would have no more than this many items.
- Default:
8 - Type: integer
- Range: 1 … 2147483647
- Applies: Session (any user)
In plain words
A subquery in FROM can often be "pulled up" so its tables are joined together with the outer ones, in any order. But more tables means much more planning work. If the combined list would exceed this limit, the subquery is planned on its own as a single unit.
For DBAs and operators
After subqueries are pulled up, the planner flattens nested FROM lists into one join list as long as the resulting list has no more than from_collapse_limit items. Beyond that, the subquery's tables stay a separate sub-problem: the best plan for them is found first, then it is joined to the rest as one item.
In the animation, the outer query has 3 tables and the subquery 6: with the default 8 they are planned as two lists (6 items, then 4 items); with a limit of 9 or more they merge into one 9-item search, which may find a better order but costs more planning time.
It is usually set to the same value as join_collapse_limit, and kept below geqo_threshold so merged lists do not suddenly switch to the genetic optimizer. Raising both to 12–16 can fix bad plans for queries over views that join many tables, at the price of planning time: measure Planning Time with EXPLAIN (SUMMARY).
How to change it
Any user can change it for their session with SET.