Join Order and Collapse Limits in PostgreSQL #

The planner decides not only how to join two relations but in which order to join all of them, and for queries with many tables that ordering decision is bounded by three settings that silently change the search from exhaustive to partial to randomised.

The join algorithms get most of the attention, yet a hash join in the wrong position can be slower than a nested loop in the right one. A five-table query has 1,680 possible join trees; a ten-table query has more than 17 billion. This page explains how PostgreSQL searches that space, the points where from_collapse_limit, join_collapse_limit and geqo_threshold narrow it, how to read the chosen order out of an EXPLAIN tree, and how to intervene without hints. It belongs to execution plan fundamentals because every other join decision is made inside the order this search produces.

When the Planner Searches Join Orders #

For each query level the planner builds a join list: the relations that can be joined in any order. Explicit JOIN … ON syntax and subqueries in FROM form nested lists; the planner collapses them into the parent list only when the result stays small enough to search.

Within a collapsed list of n relations, the standard planner uses dynamic programming. It computes the best path for every single relation, then every pair that shares a join clause, then every triple, and so on, keeping the cheapest path for each subset along with any path that provides a useful sort order. The number of subsets grows as 2ⁿ, which is why the limits exist: planning time for a 12-way join with many join clauses can reach hundreds of milliseconds.

Two constraints further restrict the legal orders:

The practical consequence: under the limits, the order you write in FROM does not matter. Over them, it does. The CTE and subquery planning rules decide which subqueries even become part of the list.

Three regimes of join-order search A horizontal scale of relations per join list from 2 to 16. Up to 8, explicit joins and subqueries are flattened and all orders are searched with dynamic programming. Above 8, the written join nesting is kept and each nested group is searched separately. At 12 or more items in one list, the genetic optimizer samples orders instead of enumerating them. 258 1216 flatten + exhaustive search written order irrelevant nesting preserved groups searched separately GEQO sampled orders join_collapse_limit / from_collapse_limit = 8 geqo_threshold = 12 relations in one join list (after collapsing) → which search the planner runs GEQO applies per list, so a query can mix exhaustive and genetic search at different levels

Reading Join Order Out of EXPLAIN #

A plan tree encodes join order structurally. Each join node has an outer (first) and inner (second) child, and the deepest join in the tree was performed first. Reading bottom-up tells you the sequence; the estimate on each join tells you why the planner liked it.

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, sum(li.amount)
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN line_items li  ON li.order_id = o.id
JOIN products p     ON p.id = li.product_id
WHERE p.category = 'refurbished'
  AND o.placed_at >= '2026-08-01'
GROUP BY c.name;
HashAggregate  (actual rows=812 loops=1)
  Group Key: c.name
  ->  Hash Join  (cost=… rows=46 …) (actual rows=91840 loops=1)          -- ③ join customers last
        Hash Cond: (o.customer_id = c.id)
        ->  Nested Loop  (cost=… rows=46 …) (actual rows=91840 loops=1)  -- ② then orders
              ->  Nested Loop  (cost=… rows=51 …) (actual rows=102210 loops=1)  -- ① products ⋈ line_items first
                    ->  Seq Scan on products p  (rows=3) (actual rows=1204 loops=1)
                          Filter: (category = 'refurbished'::text)
                    ->  Index Scan using line_items_product_id_idx on line_items li
                          (rows=17) (actual rows=85 loops=1204)
              ->  Index Scan using orders_pkey on orders o
                    (rows=1) (actual rows=1 loops=102210)
                    Filter: (placed_at >= '2026-08-01'::date)
                    Rows Removed by Filter: 0
        ->  Hash  (actual rows=180000 loops=1)
              ->  Seq Scan on customers c  (actual rows=180000 loops=1)
Execution Time: 6204.8 ms

Breakdown:

The order itself was a correct response to wrong numbers. That is the most common case, and it is why the first step for any join-order problem is to fix the lowest misestimate — using the tools in statistics and selectivity estimation — rather than to override the order.

Reading the join sequence bottom-up A left-deep tree. At the bottom, products and line_items join first as step one. Their result joins orders as step two. That result joins customers as step three at the top. An annotation marks the products scan estimate of 3 rows against 1204 actual as the error that drove the whole order. ③ Hash Join ② Nested Loop customers (hash) ① Nested Loop orders (pkey) products rows=3 / 1204 line_items (index) deepest join runs first; the dashed leaf's 400× error chose the whole sequence

Search Internals: Dynamic Programming, Collapsing and GEQO #

Dynamic programming. For a collapsed list of relations {A, B, C, D}, the planner computes level 1 (each relation alone, with every useful scan path), level 2 (every joinable pair), level 3 (every joinable triple built from a level-2 result plus one relation), and level 4 (the full set). At each level and subset it keeps the cheapest total-cost path, the cheapest startup-cost path when a LIMIT makes that relevant, and paths with useful sort orders that a later merge join or ORDER BY could exploit. By default it builds left-deep and bushy trees alike, so the final tree may join (A ⋈ B) with (C ⋈ D).

Collapsing. With the default limits of 8, a query written as three nested explicit joins of four tables collapses into one list of four and is searched in full. The same pattern with twelve tables stops collapsing at eight items: the remaining explicitly nested joins are kept as separate subproblems, each searched on its own, and then combined. The planner can no longer discover that the best plan interleaves relations from different groups.

-- Before and after raising the limits for one query
SET LOCAL join_collapse_limit = 12;
SET LOCAL from_collapse_limit = 12;
EXPLAIN (ANALYZE, SUMMARY) <the 11-table query>;
-- Planning Time: 3.1 ms  → 38.6 ms
-- Execution Time: 9120.4 ms → 412.8 ms   (an interleaved order was found)

GEQO. At geqo_threshold items in a single list, the planner switches to a genetic algorithm: it generates a population of random join orders, evaluates their costs, and recombines the cheaper ones over a number of generations controlled by geqo_effort (1–10, default 5). The result depends on the random seed geqo_seed, which defaults to 0, so repeated planning of the same query with the same statistics yields the same plan — but a statistics change can move it to a very different order. The details, including when to prefer raising the threshold, are in GEQO for queries joining many tables.

Semi-joins and anti-joins. EXISTS, IN (subquery) and NOT EXISTS are pulled into the join list as semi- and anti-joins, which gives them the same ordering freedom as ordinary joins with extra restrictions on which side can be unique-ified or hashed. NOT IN is not converted, which is one of the most consequential plan differences in everyday SQL; see semi-join and anti-join plan shapes.

Memory, I/O and Planning-Time Behaviour #

Join order determines the size of every intermediate result. The same four joins, performed in a different sequence, can move a hash table from 2 MB to 2 GB, turn a single-batch hash into a 64-batch spill, or turn 50 index probes into 5 million. work_mem is allocated per node, so a bad order multiplies memory use across every hash and sort built on an oversized intermediate.

It also determines I/O shape. A nested loop driven from a small, selective relation produces a trickle of random index reads; one driven from a large relation produces a flood. Buffers: shared read= on the inner side of a nested loop, divided by loops, gives the per-probe cost — multiply by the true outer row count to see what the order costs. The method in using BUFFERS to find I/O hotspots applies directly.

And it determines planning time. Planning time appears in EXPLAIN (ANALYZE) as Planning Time, and for large join lists it can exceed execution time. Two things multiply it: the number of relations in one list, and the number of join clauses connecting them, since every connected subset produces candidate paths. Partitioned tables add their own multiplier when partitionwise joins are enabled.

-- Planning memory and time for a large join (PostgreSQL 17+)
EXPLAIN (SUMMARY, MEMORY) <query>;
-- Planning:
--   Memory: used=18204kB  allocated=18432kB
-- Planning Time: 142.6 ms

Step-by-Step Tuning Workflow #

  1. Count relations per join list. Count base tables, including those inside views and pulled-up subqueries. If any list exceeds 8, collapsing stopped; if any reaches 12, GEQO ran.

  2. Read the tree bottom-up and find the first join with a large estimate error. Divergence on a join usually starts with divergence on one of its inputs; follow it to the lowest scan.

  3. Repair the estimate first. Stale or insufficient statistics, correlated predicates and function-wrapped columns cause most bad orders.

    ANALYZE products;
CREATE STATISTICS products_cat_cond (dependencies, mcv) ON category, condition FROM products;
ANALYZE products;

  4. If the query exceeds the limits, raise them for that transaction and measure both times.

    BEGIN;
SET LOCAL join_collapse_limit = 16;
SET LOCAL from_collapse_limit = 16;
SET LOCAL geqo_threshold = 18;
EXPLAIN (ANALYZE, SUMMARY) <query>;
ROLLBACK;

    Accept the change only when execution time saved clearly exceeds planning time added, taking into account how often the statement is planned.

  5. Pin an order explicitly only when estimates cannot be fixed. Rewrite the query with JOIN in the desired order and disable reordering for that transaction — the method in join_collapse_limit and explicit join order:

    BEGIN;
SET LOCAL join_collapse_limit = 1;
SELECT … FROM orders o
  JOIN line_items li ON li.order_id = o.id
  JOIN products p    ON p.id = li.product_id
  JOIN customers c   ON c.id = o.customer_id
WHERE …;
COMMIT;

  6. Verify under the real parameter distribution. A pinned order that suits today’s hot category may not suit next month’s. Re-check the plan with representative values, not a single example.

Escalate in this order Four steps rising left to right. First, fix row estimates with ANALYZE and statistics. Second, raise collapse limits per transaction when the query exceeds them. Third, raise geqo_threshold so a large list is searched exhaustively. Fourth, pin the written order with join_collapse_limit set to 1. Each step is more invasive and more brittle than the last. 1 · fix estimates ANALYZE, statistics 2 · raise collapse SET LOCAL limits 3 · avoid GEQO raise geqo_threshold 4 · pin order join_collapse_limit = 1 more invasive, more brittle →

Common Pitfalls #

Rearranging FROM order and expecting a change. Under the collapse limits the planner ignores written order. Diagnostic signal: identical plans before and after reordering the tables. Fix: change the estimates, or set join_collapse_limit = 1 if you genuinely intend the written order to be binding.

Setting join_collapse_limit = 1 globally. Every query on the server then plans joins in exactly the order the application wrote them, including ORM-generated SQL with arbitrary order. Diagnostic signal: widespread regressions right after a configuration change, all with deep nested-loop chains. Fix: use SET LOCAL inside the transaction that needs it.

Views hiding the true relation count. A query joining four views may join twenty base tables. Diagnostic signal: planning time far above what the visible SQL suggests, or GEQO-dependent plan changes. Fix: count base relations in the plan output, not in the query text, and raise limits or restructure the views.

Diagnosing GEQO variance as data drift. A plan that flips after an innocuous ANALYZE on a 14-table query may be the genetic search landing on a different order. Diagnostic signal: large plan differences with small statistics changes, only on queries above geqo_threshold. Fix: raise geqo_threshold above the relation count for that workload if planning time allows.

Outer joins blocking a good order. A LEFT JOIN placed early can force a large intermediate result that an inner join would have filtered first. Diagnostic signal: a big null-extended join feeding a highly selective inner join above it. Fix: move selective inner joins inside the outer join’s scope where semantics allow, or turn a LEFT JOIN whose nulls are filtered out by a WHERE into an inner join.

Missing join clauses creating Cartesian steps. When two relations are related only through a third, a clause-less pair forces a nested loop without an index. Diagnostic signal: a Nested Loop with no Join Filter or Index Cond and a row count equal to the product of its inputs. Fix: add the transitive condition explicitly or restructure the join graph.

Frequently Asked Questions #

Does the order of tables in my FROM clause matter? #

Usually not. As long as the number of relations in a join list is within join_collapse_limit and from_collapse_limit, the planner flattens the list and searches join orders itself. Written order starts to matter once a query exceeds those limits, because the planner then keeps parts of the syntactic structure, or when join_collapse_limit is set to 1 deliberately.

What happens when a query joins more than twelve tables? #

At geqo_threshold relations (12 by default) the exhaustive dynamic-programming search is replaced by the genetic query optimizer, which samples join orders rather than enumerating them. Plans become faster to produce but may miss the best order, especially when row estimates are also poor.

Can I force a specific join order without hints? #

Yes. Write the joins with explicit JOIN syntax in the order you want and set join_collapse_limit to 1 for the transaction with SET LOCAL. The planner then keeps the written nesting while still choosing the join algorithm and access path for each step.

Why does an outer join restrict the join order? #

Moving a LEFT JOIN relative to an inner join can change which rows are null-extended and therefore the result. The planner only considers orders that are provably equivalent, so queries with many outer joins have fewer legal orders and depend more on how the SQL was written.

Up: Execution Plan Fundamentals