Almost nobody writes the query shape this parameter governs:
1 SELECT * FROM x, y, (SELECT * FROM a, b, c WHERE something) AS ss 2 WHERE somethingelse;
But you produce it constantly without meaning to. Reference a view that contains a join and the view’s definition is inserted in place of the reference, leaving the planner looking at exactly that: a subquery sitting in your FROM list. Nested views, ORM-generated SQL, and anything built out of reusable query fragments all arrive at the planner in this form. from_collapse_limit decides what the planner does with it.
Default 8, context user, valid from 1 up to 2147483647. The rule is short: the planner will merge a subquery into the query above it if the resulting FROM list would have no more than this many items. Merging means flattening, so that the tables inside the subquery and the tables outside it become a single join problem rather than two.
That distinction is the whole point, because join order is chosen within one join problem and never across two. Flatten the example above and the planner is free to join x to a first if the outer WHERE eliminates most of a that way, and it may never build the subquery’s full output at all. Leave it unflattened and the subquery is planned on its own, its result handed upward afterward. The second plan is frequently worse. It is also much cheaper to find, which is the tension the parameter exists to manage.
The reason for a limit at all is that the number of possible join orders grows factorially with the number of relations. Two separate three-way joins are nothing to plan; one six-way join is a considerably larger search, and the cost keeps compounding from there. So this is a budget on how large a single join problem the planner will consent to build. Setting it to 1 stops subquery flattening outright.
Before you reach for the value, know what it does not govern. Only a simple subquery is eligible for flattening in the first place. A subquery containing aggregation, DISTINCT, LIMIT or OFFSET, window functions, or a set operation is never pulled up no matter what this parameter says, because those constructs make it a fence by their own semantics. That is the mechanism behind the old OFFSET 0 trick for deliberately fencing a subquery off from the planner. If the subquery you are worried about has a GROUP BY in it, raising from_collapse_limit will accomplish precisely nothing.
And when you do raise it, mind the number 12. That is the default for geqo_threshold, and when a join problem reaches that many FROM items PostgreSQL stops searching exhaustively and hands the query to the genetic query optimizer, which samples the space of join orders rather than covering it. The documentation is blunt about the consequence: setting a collapse limit at or above geqo_threshold may trigger GEQO and produce non-optimal plans. The default of 8 sits below 12 deliberately. Raise from_collapse_limit to 16 in pursuit of better plans and you can get worse ones, chosen by a different algorithm than the one you thought you were tuning. If you raise the collapse limit, move geqo_threshold above it in the same edit.
Its sibling join_collapse_limit does the same job for explicit JOIN syntax and takes its default from this parameter, and it gets its own entry when the alphabet reaches it. For this one: leave it at 8 in postgresql.conf, and treat any increase as a per-query experiment, which the user context makes easy. Set it higher for one session, compare the plan and the planning time, and remember which side of the trade you are paying. On a query that runs a few times an hour, several milliseconds of extra planning to find a better join order is a bargain. On one that runs a thousand times a second, it is not.