Changing one number from 32 to 34 made a Postgres query 6,000 faster. Here's exactly why.

Payyavula Balanagaraju — Sun, 01 Nov 2026 21:58:23 +0000

Who this is for: anyone who has seen a Postgres query that should be fast but isn't, and EXPLAIN shows a bad plan without saying why. You don't need to know planner internals. Everything is explained from zero, with the real code lines.

The short version: in Postgres 15 and every version released so far, the planner splits your joins into groups before it removes the joins it doesn't need. If the two tables that need an index lookup land in different groups, the fast plan can't be built at all. join_collapse_limit sets the group size, and the "safe" values are not a range: they have holes. Postgres 16+ gets a change that helps, but doesn't remove the problem completely.

1. The problem

A report on the platform I work on started timing out. One query, run with a few filters, took 137,779 ms: more than two minutes.

The query reads from a view. That view is built on other views, which are built on more views (we call them layered views). It looks like one simple SELECT ... FROM report_view WHERE .... Underneath, it is not simple at all.

EXPLAIN ANALYZE showed the slow part clearly:

Nested Loop  (rows=1) (actual time=1112..139689 rows=1005)
  Join Filter: (enrollment.course_id = course.id)
  Rows Removed by Join Filter: 8,944,739
  ->  Bitmap Heap Scan on course             (estimated rows=1, actual rows=181)
  ->  Hash Left Join ...                     (loops=181)
        ->  Hash (rows=895194) (actual time=538.801) loops=181     ← 538.8 ms × 181 = 97.5 s

Two things were wrong at once:

The plan shape. Postgres built both sides of the join completely, then compared them row by row (Join Filter) and threw away 8.9 million rows. The fast way would have been: take each of the 181 courses and look up its enrollments through an index. In a plan that looks like Index Cond: (course_id = course.id).
The row estimate. Postgres expected 1 course and got 181. It planned an expensive inner step thinking it would run once. It ran 181 times.

Fixing the plan shape alone was enough: the inner step drops to ~0.06 ms, so running it 181 times stops mattering. The rest of this article is about the plan shape, because that's the part EXPLAIN can't explain.

2. The first fix worked, and that was the problem

The first suggestion I got (from an AI assistant) was to raise one planner setting, join_collapse_limit. It worked. The report became fast.

But I didn't know why it worked, so I tested every value from 8 to 140, three times each, on a copy of real data:

 8 10 12 14 16 18 20 22 24 26 28 30 32 34 ............ 140
 ✗  ✗  ✓  ✗  ✓  ✓  ✓  ✓  ✓  ✗  ✗  ✓  ✗  ✓ ✓ ✓ ✓ ✓ ✓ ✓ ✓

	Values
Slow (bad plan)	8, 10, 14, 26, 28, 32
Fast (index lookup)	12, 16–24, 30, 34 and up

At 32 the query took 140,611 ms. At 34 it took 23.9 ms. That's almost 6,000× from changing one number by two.

This is not a threshold. It's a range with holes in it. If the setting were simply "bigger is better", 14 couldn't be slow when 12 is fast. Nobody could tell me why, so I went to the Postgres source code.

3. Background: what the planner does with your joins (two minutes)

3.1 Views disappear

A view is saved SQL. When you query a view, Postgres pastes the view's SQL into your query before planning. Views built on views get pasted recursively. Our one-line query became about 435 table references: one inner view alone contributed 121, and it appeared three times.

3.2 Postgres can throw away useless LEFT JOINs ("join removal")

Most of those 435 tables aren't needed for this report. Postgres can remove a LEFT JOIN if all of these are true (join_is_removable, src/backend/optimizer/plan/analyzejoins.c:159 in 15.8):

It's a LEFT join.
The right side is one single table.
That table can match at most one row, proven by a primary key or unique index on the join columns.
No column of that table is used anywhere above the join: SELECT, WHERE, ORDER BY, or another join's ON.

If all four hold, removing the table can't change the result, so Postgres drops it. In our query, removal cut ~435 references down to about 11.

Rule 4 is why the columns you select matter. With 2 columns selected, only 3 tables survived and the query was fast at the default settings. With the report's 27 columns, 10–11 survived and it was slow. Each extra column can bring back a table that would otherwise be removed.

3.3 Postgres limits how hard it searches

To join N tables, the planner tries different orders. The number of possible orders grows like N!:

Tables	Possible orders (≈ N!)
4	24
8	40,320
11	~40 million

So Postgres protects itself with two settings:

join_collapse_limit (default 8): tables written with explicit JOIN syntax are grouped into lists of at most this many items. The planner searches join orders inside each list, and treats each finished list as one fixed piece.
geqo_threshold (default 12): if a single list has 12 or more items, Postgres stops searching exhaustively and uses GEQO, a genetic (randomised) optimiser. Plans can then change from run to run.

4. The real cause: the order of two steps

Here is the order inside the planner's main function, query_planner(), in Postgres 15.8 (src/backend/optimizer/plan/planmain.c):

Line	Call	What it does
184	`joinlist = deconstruct_jointree(root);`	Walks the whole join tree once and does two jobs in that walk: (A) collects the facts the planner needs (which columns are used where, what each join condition looks like), and (B) builds the join list, applying `join_collapse_limit`
221	`joinlist = remove_useless_joins(root, joinlist);`	Then removes the useless LEFT JOINs

The join list is built at line 184, counting all ~435 tables. The useless ones are removed at line 221, and the join list is never rebuilt. That's the whole problem.

4.1 How the join list is built

deconstruct_recurse visits the join tree bottom-up. At every JOIN it has the list from the left side and the list from the right side, and applies one rule (src/backend/optimizer/plan/initsplan.c:1037-1058):

else if (list_length(leftjoinlist) + list_length(rightjoinlist) <=
         join_collapse_limit)
{
    /* OK to combine subproblems */
    joinlist = list_concat(leftjoinlist, rightjoinlist);
}
else
{
    /* can't combine, but needn't force join order above here */
    ...
    joinlist = list_make2(leftpart, rightpart);
}

In plain words:

left items + right items ≤ limit → merge them into one flat list (all orders will be tried across them);
otherwise → don't merge; return exactly two items, the left side as a sub-list and the right side.

A tiny example. A JOIN B JOIN C with a limit of 2 (Postgres reads it as (A JOIN B) JOIN C):

JOIN node	Left list	Right list	Total	≤ 2?	Result
`A JOIN B`	`[A]`	`[B]`	2	yes	`[A, B]`
`(…) JOIN C`	`[A, B]`	`[C]`	3	no	`[[A, B], C]`

The result [[A, B], C] means: plan A+B together first, on their own; then join that finished piece with C. The order "A with C first" is never even considered.

4.2 Why a sub-list blocks the fast plan

When the planner meets a sub-list, it plans it completely and alone first (make_rel_from_joinlist, src/backend/optimizer/path/allpaths.c:3325). The level above gets one finished result and can't look inside it.

The fast plan we wanted is "for each course, use an index to find its enrollments". Postgres calls this a parameterized nested loop. It only works when both tables are in the same list. If enrollment sits inside a sub-list and course sits outside it, that lookup can't be built. Postgres isn't choosing the slow plan because it thinks it's cheaper. The fast plan doesn't exist as an option.

4.3 Removal doesn't fix the shape

When remove_useless_joins removes a table, it deletes that table's entry from the join list (remove_rel_from_joinlist, analyzejoins.c:462). A sub-list stays a sub-list:

sublist = remove_rel_from_joinlist((List *) jlnode, relid, nremoved);
/* Avoid including empty sub-lists in the result */
if (sublist)
    result = lappend(result, sublist);

So the borders drawn while ~435 tables were counted survive, even though only ~11 tables remain. At the default limit of 8, course and enrollment ended up on different sides of a border.

Postgres's own code admits the order is a compromise. This comment sits right above the removal call:

/*
 * Remove any useless outer joins.  Ideally this would be done during
 * jointree preprocessing, but the necessary information isn't available
 * until we've built baserel data structures and classified qual clauses.
 */

In other words: the facts needed to decide "this join is useless" are collected in the same walk that builds the join list. So by the time Postgres knows what's useless, the groups are already drawn.

5. Seeing it happen: I added logging inside the planner

Reading the code told me what could happen. To see what did happen, I compiled PostgreSQL 15.8 from the official source and added log lines inside deconstruct_recurse (every JOIN decision) and make_rel_from_joinlist (every final list):

elog(NOTICE, "JOIN|right=%s|L=%d|R=%d|sum=%d|jcl=%d|act=%s", ...);

I loaded a schema-only copy of the database (tables, views, and all 270 primary keys and unique constraints, no rows) and ran the report query.

Tip: the constraints matter. My first copy had no primary keys, so Postgres couldn't prove any join was useless, nothing was removed, and the trace showed 766 join-list entries instead of 45. An experiment without unique constraints measures the wrong tree.

What came out, per run:

	limit = 8	limit = 64
JOIN decisions evaluated	739	739
merges	656	730
refusals (cuts)	83	9
separate join problems	75	45
the `enrollment` table ends up in a group of	1 (alone)	5
execution time (copy of real data)	137,779 ms	91 ms

Same 739 decisions either way. The limit turned 83 cuts into 9, and that's the whole difference.

At limit 8, the top of the tree showed three cuts, each just over the limit:

  L +  R  =  sum   vs 8   ->  decision
  7 +  4  =  11    vs 8   ->  refuse    <-- cut
  8 +  2  =  10    vs 8   ->  refuse    <-- cut
  6 +  3  =   9    vs 8   ->  refuse    <-- cut

At limit 64, the decisive JOIN merged two halves of the query, one of 34 table references and one of 30:

 34 + 30  =  64    vs 64  ->  MERGE     <-- the merge that puts course and enrollment together

34 + 30 = 64, against a limit of exactly 64. It passes by a margin of zero.

5.1 Why the safe values have holes

Look at what each decision returns:

a merge returns a list of length L + R (it gets longer);
a refusal returns a list of length 2 (it gets shorter).

So a merge that succeeds low in the tree makes a list longer, and that longer list can then fail a check higher up. A refusal low in the tree makes a list shorter, which can then pass higher up. Change the limit and some decisions flip at the bottom, which flips other decisions further up. Whether course and enrollment end up together jumps around as you change the limit. That's the holes: 12 fast, 14 slow, 16 fast.

6. What we shipped, and two traps

6.1 The fix

BEGIN;
SET LOCAL join_collapse_limit = 64;
-- the report query (and its count query)
COMMIT;

137,779 ms → 91 ms.

6.2 Trap 1: bigger is not safer

My own first recommendation was 100. The trace showed that at 100 the tree collapses so far that the biggest single list reaches 12 tables, exactly geqo_threshold, so Postgres switches to the genetic optimiser and plans stop being deterministic. Confirmed on the copy of real data by changing geqo_seed (if the plan changes when the seed changes, GEQO is running):

jcl=48  : no GEQO     jcl=64  : no GEQO     jcl=72 : no GEQO
jcl=80  : no GEQO     jcl=100 : GEQO FIRES

The usable window for this query was 48–80: fast plan, deterministic. 64 sits in the middle of it.

Raising from_collapse_limit as well (the sibling setting for comma-joins and subqueries) didn't help either. It made several values flip from fast to slow. It's a second axis with its own holes.

6.3 Trap 2: never set it globally

A global 64 is dangerous for other queries. In a separate test with a 12-table join where nothing can be removed, join_collapse_limit = 64 made Postgres switch that query to GEQO, while the default of 8 didn't. So:

set it with SET LOCAL, only in the transaction that runs this query;
SET LOCAL outside a transaction does nothing: Postgres prints a warning and keeps the old value. Make sure your code really opens a transaction;
SET LOCAL also resets at COMMIT, so the setting doesn't leak to the next request on a pooled connection.

6.4 Fixes that don't need the setting at all

The setting is a workaround. These fix the cause:

Select fewer columns in the hot query. Fetching 25 IDs first and then the details for just those 25 ran in 5.5 ms at the default limit, with no planner setting at all.
Add the unique index that join removal needs. No setting can remove a join Postgres can't prove is unique.
Fewer view layers in hot paths.

One more honest number: planning alone took about 440 ms for this query, because of view expansion. That's a separate cost, and the setting doesn't touch it.

7. The twist: Postgres changed this in August 2026

On 28 August 2026, Tom Lane committed 2ebf25e7d7, "Perform join removal by editing the query's jointree".

7.1 What it does

Before (15.8 up to the latest released versions):

build facts + join list (all tables counted);
remove useless joins, patching the planner's notes by hand;
plan with the old join list.

After:

build facts + join list;
remove useless joins by cutting them out of the query tree itself (the LEFT JOIN node is replaced by its left side, analyzejoins.c:399 in master: return j->larg;);
throw away all notes and the join list, and start again (goto restart, planmain.c:286 in master);
repeat until nothing more can be removed, then plan.

The final join list is built only from the tables that survived. In effect: remove first, then group.

7.2 Why he did it: a bug that returned wrong rows

He didn't do it for speed. He did it because patching the notes by hand kept causing bugs. Bug #19560 is a good example (reported on 19beta2, a regression starting in 16; 15.x is not affected):

CREATE TABLE items (id text, owner text);
CREATE TABLE follows (item_id text, user_id text, UNIQUE (user_id, item_id));
INSERT INTO items VALUES ('item1', 'alice');

WITH viewer AS (SELECT 'bob' AS id)
SELECT count(*) FROM items
LEFT JOIN follows ON follows.item_id = items.id AND follows.user_id = 'bob'
LEFT JOIN viewer ON TRUE
WHERE items.owner = viewer.id;

The only item belongs to alice and the viewer is bob, so the answer is 0. Postgres returned 1. The plan became just Aggregate -> Seq Scan on items: the WHERE condition vanished. follows was removable (a unique key, no columns used), and removing it changed the planner's internal notes so that a new filter should have been created. The filters had already been created earlier, and nothing re-checked them.

Tom's commit message: the old code "implemented the removal by updating those structures in-place … nothing keeps it in step with the rest of the planner", and "We have seen many related bugs over the years too, so it's time to do something drastic."

7.3 Which versions have it (as of 1 November 2026)

Version	Has the fix?
15.x	Never. It was back-patched only to 16
16.15, 17.11, 18.6 (latest releases)	Not yet: the fix is on those branches, but in no release tag yet
Next minor releases, 12 Nov 2026	Expected (fixes on a stable branch normally ship in the next minor)
19 (RC1; final release planned 19 Nov 2026)	Yes

7.4 What it does NOT fix

More survivors than the limit. Our query keeps about 11 tables. 11 > 8, so even on the new code the survivors are split again. In a synthetic test, 6 survivors at limit 8 went from 213–270 ms to 33–36 ms; 11 survivors at limit 8 kept the same bad plan. On 16+ we'd still need the limit at 11 or more.
Chains cost planning time. Each restart repeats the planning setup. Independent useless joins are all removed in one pass, but a join that only becomes removable after another one is gone waits for the next pass. Views built on views create exactly those chains. Measured by Richard Guo in the discussion of the change:

Shape	Before	After
64 independent useless joins	1.11 ms	0.57 ms
a chain of 64 dependent removals	1.98 ms	20.9 ms

How long the chains are in real layered-view schemas, and what that costs, hasn't been measured publicly as far as I know.

8. Check your own query in 15 minutes

Run EXPLAIN (ANALYZE, BUFFERS). Look for Join Filter on a join you expected to use an index, with a big Rows Removed by Join Filter. Compare with what you'd expect: Index Cond: (child.parent_id = parent.id).
Count tables before and after removal. If your query reads from views, count the tables the view expands into (or look at the plan). If far more tables exist in the views than appear in the plan, removal is happening, and the grouping was done on the bigger count.
Try a few limits, inside a transaction:

   BEGIN; SET LOCAL join_collapse_limit = 12; EXPLAIN ANALYZE <query>; ROLLBACK;

Test several values, not just one. Expect holes.

Check GEQO. Run the plan with two different geqo_seed values. If the plan changes, the genetic optimiser is in play at that limit.
Ship it scoped. SET LOCAL inside the transaction of that one query, never globally. Re-test when the views change: our winning merge had a margin of zero.
Fix the cause when you can: fewer columns in the hot query, the missing unique index, fewer view layers.

What I'd tell my team

When a tool shows you what is slow but not why, the source code can. Postgres is open, and the comments in the planner are honest about its own compromises. A fix solves one query. Understanding why it works solves the next ten.

Do you build views on top of views in Postgres? How many levels deep did you go before the planner started fighting you?

References (public)

Postgres commit 2ebf25e7d7: https://github.com/postgres/postgres/commit/2ebf25e7d70a8fce31ace78d723fa9271ab8af72
Bug #19560 report: https://www.postgresql.org/message-id/19560-54cd7ede78d5e355%40postgresql.org
Discussion thread: https://www.postgresql.org/message-id/flat/1186816.1784573544%40sss.pgh.pa.us
PostgreSQL docs, explicit JOIN control and join_collapse_limit: https://www.postgresql.org/docs/current/explicit-joins.html
PostgreSQL roadmap (release dates): https://www.postgresql.org/developer/roadmap/
Source files: src/backend/optimizer/plan/planmain.c, initsplan.c, analyzejoins.c, src/backend/optimizer/path/allpaths.c (line numbers from REL_15_8 and master as of 2026-11)

DEV Community: Payyavula Balanagaraju