join_collapse_limit and Explicit Join Order #

You have a reporting query whose best join order you know — drive from the small filtered campaigns set, then clicks, then everything else — and the planner keeps starting from clicks because an estimate it cannot get right says the campaign filter is unselective. You have already tried statistics. The remaining lever, short of an extension, is to make the written order binding.

The search this overrides is described in join order and collapse limits. This page covers doing it safely for one statement.

The Planner Condition #

join_collapse_limit controls whether explicit JOIN constructs are flattened into one list the planner can reorder. The default is 8. At 1, no flattening happens at all: each JOIN is planned as written, left to right, so A JOIN B JOIN C JOIN D becomes ((A ⋈ B) ⋈ C) ⋈ D.

What stays free:

What it does not constrain:

Explicit joins are also how you control parenthesised bushy shapes: (a JOIN b ON …) JOIN (c JOIN d ON …) ON … under join_collapse_limit = 1 produces exactly that bushy tree.

Flattened list versus preserved nesting Left panel: with join_collapse_limit 8, four explicitly joined tables become one set that the planner may join in any order. Right panel: with join_collapse_limit 1, the same SQL is planned strictly as campaigns joined to clicks, then sessions, then users, in the written sequence. join_collapse_limit = 8 { campaigns, clicks, sessions, users } any order the cost model prefers written order ignored join_collapse_limit = 1 campaigns clicks sessions users written nesting kept; algorithms still chosen

Annotated EXPLAIN Evidence #

The planner’s own order, driven by an estimate that says campaigns filters little:

Hash Join  (actual time=8210.4..9402.1 rows=14022 loops=1)
  Hash Cond: (cl.campaign_id = ca.id)
  ->  Hash Join  (actual rows=41802210 loops=1)            -- clicks ⋈ sessions first: 41.8M rows
        ->  Seq Scan on clicks cl  (actual rows=41802210 loops=1)
        ->  Hash  ->  Seq Scan on sessions s
  ->  Hash  (actual rows=12 loops=1)
        ->  Seq Scan on campaigns ca  (rows=4100) (actual rows=12 loops=1)
              Filter: (owner_team = 'emea-growth' AND starts_on >= '2026-09-01')
Execution Time: 9480.2 ms
-- campaigns estimated 4100, actual 12 → the filter looked useless, so the planner deferred it

The same statement with the order pinned:

Nested Loop  (actual time=0.9..96.3 rows=14022 loops=1)
  ->  Nested Loop  (actual rows=14022 loops=1)
        ->  Seq Scan on campaigns ca  (rows=4100) (actual rows=12 loops=1)
        ->  Index Scan using clicks_campaign_id_idx on clicks cl  (actual rows=1168 loops=12)
  ->  Index Scan using sessions_pkey on sessions s  (actual rows=1 loops=14022)
Execution Time: 101.7 ms
-- the estimate is still wrong (4100), but the order no longer depends on it
Evidence that the order is pinned Three plan lines are annotated. The deepest join now reads campaigns first. The campaigns estimate is still 4100 rows, showing the estimate was not fixed. The inner clicks scan runs twelve loops, one per real campaign. Seq Scan on campaigns (rows=4100) (actual rows=12) estimate still wrong — order fixed instead Index Scan … on clicks cl (loops=12) one probe per actual campaign Index Scan using sessions_pkey (loops=14022) later joins follow the written sequence check the deepest join first: it must be the relation you wrote first

Step-by-Step Resolution #

  1. Exhaust estimate repairs first. Test the filter alone, check pg_stats, and try extended statistics for correlated predicates such as owner_team and starts_on. A fixed estimate adapts to data changes; a pinned order does not.

  2. Find the good order empirically. Test candidate orders in a rolled-back transaction:

    BEGIN;
SET LOCAL join_collapse_limit = 1;
EXPLAIN (ANALYZE, BUFFERS)
SELECT …
FROM campaigns ca
JOIN clicks cl   ON cl.campaign_id = ca.id
JOIN sessions s  ON s.id = cl.session_id
JOIN users u     ON u.id = s.user_id
WHERE ca.owner_team = 'emea-growth' AND ca.starts_on >= '2026-09-01';
ROLLBACK;

  3. Commit the order to the SQL text. Write the explicit JOIN sequence in the query itself, with a comment explaining that order is significant. Convert any comma-joined relations into explicit joins, or they will stay freely reorderable.

  4. Scope the setting to the transaction.

    BEGIN;
SET LOCAL join_collapse_limit = 1;
-- the reporting query
COMMIT;

    For a query that lives in a SQL function, attach the setting to the function instead: CREATE FUNCTION … SET join_collapse_limit = 1 AS $$ … $$.

  5. Validate across parameter values. Run the pinned plan with a campaign filter that matches 12 rows and one that matches 4,000. If the large case becomes unacceptable, the pin is too blunt — split the two cases or return to fixing the estimate.

A pinned order wins only where it was measured Two pairs of bars. For a filter matching 12 campaigns, the planner's order takes 9480 milliseconds and the pinned order takes 102. For a filter matching 4000 campaigns, the planner's order takes 9900 and the pinned order takes 14200, because the nested loops now run far more probes. 12 campaigns planner: 9,480 ms pinned: 102 ms 4,000 campaigns planner: 9,900 ms pinned: 14,200 ms bars scaled within each pair; the pin reverses the ranking on the large case

Before and After #

-- BEFORE: join_collapse_limit = 8 (default)
Hash Join … clicks ⋈ sessions first (actual rows=41802210)          Execution Time: 9480.2 ms

-- AFTER: explicit JOIN order + SET LOCAL join_collapse_limit = 1
Nested Loop … campaigns ⋈ clicks first (actual rows=14022)           Execution Time: 101.7 ms

What the pin does not protect you from #

A pinned order still leaves the planner free to choose join algorithms from estimates, and those estimates are still wrong. In the example the campaigns scan is still estimated at 4,100 rows. The planner chose nested loops anyway because, with campaigns fixed as the driving relation, an index probe into clicks was cheaper than hashing 41 million click rows for any plausible campaign count. But on a slightly different schema — say, without an index on clicks.campaign_id — the same wrong estimate could lead the planner to hash clicks in the pinned position and lose most of the benefit. After pinning, read the algorithms at each step, not only the order.

The pin also fixes the order of evaluation for outer joins and semi-joins written in the statement. Rewriting EXISTS subqueries as explicit joins to make them part of the pinned sequence changes semantics if the joined relation can produce duplicates; keep them as EXISTS and accept that the planner places them.

Alternatives that keep the planner adaptive #

Two approaches achieve a similar effect without freezing the order. A MATERIALIZED CTE computing the selective campaign set is planned and executed first, and the outer query then sees its real size when it is rescanned — though the outer query is still planned with an estimate for the CTE’s row count, not the actual one. Splitting the work into two statements — fetch campaign ids, then query clicks with campaign_id = ANY($1) — lets the second statement be planned with the actual list in hand, which gives an accurate estimate at the cost of one extra round trip. Both remain correct if next month the filter matches 4,000 campaigns, which is exactly the case where a pinned nested-loop order becomes the slowest option.

Common Pitfalls #

Setting the limit at session scope behind a pooler. A plain SET join_collapse_limit = 1 persists on the server connection and changes every later query that reuses it. Diagnostic signal: unrelated statements developing deep nested-loop chains intermittently. Fix: use SET LOCAL, and see session reset and DISCARD ALL effects for how leaked settings persist.

Leaving a comma join in the pinned statement. FROM campaigns ca JOIN clicks cl ON …, users u still lets the planner move users. Diagnostic signal: part of the plan follows the written order and part does not. Fix: express every relation as an explicit JOIN.

Pinning an order through a view. Joins inside a view are expanded into the query and follow the same rules, so the visible SQL may not be the order that is pinned. Diagnostic signal: the plan’s leaf order does not match the statement text. Fix: inline the view’s joins into the statement or pin the order inside the view definition.

Forgetting the pin exists. Data grows and the right order changes. Diagnostic signal: a query that was fixed a year ago is slow again with a plan that exactly matches its written order. Fix: record the reason and the measured parameter range next to the query, and review it when the tables change by an order of magnitude.

Frequently Asked Questions #

Does join_collapse_limit = 1 affect comma-separated FROM lists? No. It only controls flattening of explicit JOIN constructs. Comma-separated relations form a single list that the planner reorders freely, subject to from_collapse_limit and geqo_threshold.

Does pinning the join order also pin the join algorithm? No. The planner keeps the nesting you wrote but still chooses hash, merge or nested loop and the scan paths, and may swap the two inputs of an individual join.

How do I apply the setting to just one query from an application? Wrap the statement in a transaction and issue SET LOCAL join_collapse_limit = 1 before it. The value reverts at commit or rollback, so it cannot leak across pooled connections.

Up: Join Order and Collapse Limits