Parameter · Query Tuning / Other Planner Options
join_collapse_limit
Sets the FROM-list size beyond which JOIN constructs are not flattened. The planner will flatten explicit JOIN constructs into lists of FROM items whenever a list of no more than this many items would result.
- Default:
8 - Type: integer
- Range: 1 … 2147483647
- Applies: Session (any user)
In plain words
When you write a JOIN b JOIN c …, the planner may reorder those joins freely, but only up to this many tables in one group. Beyond that, it keeps your written order between groups. Setting it to 1 makes PostgreSQL join tables in exactly the order you wrote them.
For DBAs and operators
Explicit JOINs (all except FULL JOIN) are flattened into one join list while the list stays within this limit. A left-deep chain of 10 joins with the default 8 becomes a list of the first 8 tables (searched freely), which is then one item in a second list together with the 9th and 10th tables. FULL JOINs are never flattened, and outer joins in a flattened list are still only reordered where that gives the same result.
join_collapse_limit = 1 is a well-known way to force a join order: write the joins in the order you want, and the planner keeps it (it can still choose the join method and which side is inner). This is useful when the planner keeps picking a bad order due to estimates you cannot fix, or to cut planning time for huge generated queries.
Raising it (with from_collapse_limit, below geqo_threshold) lets the planner find better orders for queries with many joins, which helps ORM-generated SQL whose written order is arbitrary, at the cost of planning time that grows exponentially with the list size.
How to change it
Any user can change it for their session with SET.