PostgreSQL can reorder many joins instead of treating SQL text order as a fixed execution sequence. That freedom gives the planner more candidate plans, but the search space grows rapidly as a query brings more relations into one join problem. Two planner settings, join_collapse_limit and from_collapse_limit, place boundaries on how aggressively PostgreSQL flattens query structure before it searches for a plan.
These limits are not execution-time row caps. They shape planner search. Changing them can alter planning time and can also alter the set of join orders available for consideration.
Explicit joins can become one planner problem
An inner join written with explicit JOIN syntax does not normally force PostgreSQL to preserve the textual join order. When the resulting list stays within join_collapse_limit, the planner can flatten compatible explicit joins into a relation list and consider alternative join orders.
Consider this shape:
SELECT o.id, c.region, p.sku
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN order_items AS i ON i.order_id = o.id
JOIN products AS p ON p.id = i.product_id
WHERE c.region = 'west'
AND p.active;The SQL text places orders beside customers first, but that does not imply those relations must form the first execution join. If planner rules permit flattening, PostgreSQL can consider a different order when its cardinality and cost estimates favor one.
This matters because early selectivity can change the amount of data reaching later joins. A selective predicate on products, for example, can make a plan that reaches products early cheaper than one that follows the written order.
join_collapse_limit can preserve explicit structure
Reducing join_collapse_limit restricts flattening of explicit joins. The special value 1 makes explicit JOIN structure constrain join order rather than exposing those joins to normal reordering.
That setting changes the role of SQL syntax. With the normal planner freedom, parentheses and explicit inner-join grouping often describe logical structure without fixing a physical sequence. With join_collapse_limit = 1, explicit join nesting becomes a planner boundary.
A partial boundary is also possible. A query can combine an explicitly grouped join with other items in a plain FROM list. The grouped part narrows the search while leaving PostgreSQL freedom around the remaining relations. This can reduce planning work without prescribing every join in a large statement.
Restricting the search is not automatically an execution improvement. A smaller search space takes less planner effort, but it can exclude a cheaper join order. The setting therefore affects both planning cost and the planner’s opportunity to find a low-cost execution plan.
Subquery flattening has a separate limit
from_collapse_limit applies to a related transformation: merging eligible subqueries into an upper query. Flattening can expose relations inside a subquery to predicates and join orders from the outer query.
A simplified shape looks like this:
SELECT x.id, s.total
FROM accounts AS x
JOIN (
SELECT p.account_id, sum(p.amount) AS total
FROM payments AS p
JOIN payment_state AS ps ON ps.id = p.state_id
GROUP BY p.account_id
) AS s ON s.account_id = x.id
WHERE x.status = 'open';Not every subquery is eligible for flattening; query semantics can require a separate planning boundary. For eligible cases, from_collapse_limit controls whether merging the subquery would create a FROM list beyond the configured size.
The distinction from join_collapse_limit is structural. One governs flattening of explicit joins, while the other governs collapsing eligible subqueries into their parent query. Both influence the size of the join problem presented to the planner.
Larger search spaces consume planner time
Join planning is combinatorial. Adding relations can create many more candidate join trees and access-path combinations than the relation count alone suggests. PostgreSQL has additional mechanisms for large join problems, including the genetic query optimizer once the configured threshold is reached, but collapse limits act earlier by controlling the structure exposed to join search.
This makes planning time a legitimate part of query latency for statements with many relations. A plan that executes quickly can still be expensive to produce if the planner explores a broad search space on every parse-and-plan cycle.
Prepared statements, plan caching behavior, query shape, and parameter handling can change how often that cost is paid. Collapse settings do not remove those factors; they only control one dimension of planner search.
A lower limit can also change plan quality
Planner freedom has value only when estimates and costing provide useful signals. Allowing more join orders gives PostgreSQL more opportunities to find a cheap candidate, but it also asks the planner to spend more time evaluating alternatives.
Conversely, preserving explicit structure can be useful when a query author intentionally supplies a join grouping that sharply reduces the search problem. That choice carries a maintenance cost: schema growth, data distribution changes, new indexes, or changed predicates can make a previously suitable fixed order less attractive.
For that reason, a collapse setting is better treated as a search-space control than as a generic query-speed switch. Its effect is visible in planning behavior, and any execution benefit depends on the plans that remain reachable.
EXPLAIN separates planning from execution
EXPLAIN (ANALYZE) reports planning and execution time separately, which makes it possible to distinguish search overhead from runtime work. Comparing plans under different collapse settings should account for both values rather than focusing only on the chosen join nodes.
The join tree itself also shows whether a structural restriction changed the order in which relations are combined. If planning time falls while execution time rises, the smaller search space may have excluded a better plan. If both remain stable, the broader search may have been unnecessary for that query shape.
Such comparisons need representative statistics. A join-order decision based on stale or weak cardinality estimates can obscure the effect being measured, because the planner may rank candidate plans incorrectly regardless of how many candidates it considers.
PostgreSQL’s join collapse controls sit at the boundary between declarative SQL and bounded plan search. They do not change what an eligible query means; they change how much structural freedom the planner receives before costing alternatives. For large join graphs, that boundary can matter as much to planning latency as indexes and row estimates matter to execution.