Raising join_collapse_limit from 32 to 34 made a Postgres query 6,000x faster

A single PostgreSQL planner setting took a report query from 140,611 ms down to 23.9 ms — nearly a 6,000x speedup — and the reason is the order in which the planner performs two internal steps, not a missing index or stale statistics. According to a detailed dev.to write-up by Payyavula Balanagaraju, the behavior is present in Postgres 15 and every version released so far; Postgres 16 and later include a change that helps but does not eliminate the problem.

Two minutes behind a one-line query

The report selected from a view, and that view sat on a stack of further views. Postgres pastes a view's saved SQL into the query before planning, recursively, so the one-line SELECT expanded into roughly 435 table references; one inner view alone contributed 121 references and appeared three times.

EXPLAIN ANALYZE showed a nested loop that built both sides of a join and then discarded 8,944,739 rows through its join filter. A bitmap heap scan on the course table was estimated at one row but returned 181, so an inner hash step costing about 538.8 ms ran once per course — roughly 97.5 s in total. The fast alternative would have been an index lookup of enrollments for each course, a plan shape the optimizer never produced.

A setting that works, with holes

The first fix, suggested by an AI assistant, was to raise join_collapse_limit. It worked, but without an explanation of why, so the author benchmarked a sweep of values from 8 up to 140, three runs each, on a copy of real data. The results were not a threshold. The values 8, 10, 14, 26, 28 and 32 produced the slow plan, while 12, 16 through 24, 30 and everything from 34 upward produced the fast one. At 32 the query took 140,611 ms; at 34 it took 23.9 ms. If the setting were simply "bigger is better," 14 could not be slow while 12 is fast.

Grouping happens before pruning

Two planner mechanisms interact here. First, Postgres can remove a LEFT JOIN outright when the right side is a single table, uniqueness on the join columns is proven by a primary key or unique index, and no column of that table is used anywhere above the join — the join_is_removable check in analyzejoins.c in the 15.8 source. In this query, removal cut the ~435 references down to about 11. Second, because possible join orders grow factorially (11 tables imply about 40 million orders), the planner caps its search: join_collapse_limit, default 8, bounds how many items may sit in one group, and geqo_threshold, default 12, switches larger groups to the randomized GEQO optimizer.

The problem is sequencing. In query_planner() in Postgres 15.8, deconstruct_jointree (planmain.c, line 184) builds the join list and applies join_collapse_limit while all ~435 tables are still being counted; remove_useless_joins (line 221) prunes afterwards, and the list is never rebuilt. When the builder encounters a JOIN whose two sides together exceed the limit, it returns them as two separate sub-lists, and each sub-list is then planned in isolation — the level above only ever sees a finished result. A parameterized nested loop, the "look up enrollments per course" shape, can only be considered when both tables live in the same list. If one lands inside a sub-list and the other outside it, the fast plan is not a rejected option; it does not exist as a candidate at all. The planner is not misjudging costs.

Why the SELECT list matters

Join survival depends on column usage, so the report's column list decided the outcome. Selecting two columns left only three tables after pruning, and the query was fast at default settings. Selecting the report's 27 columns left 10–11 tables in play and triggered the slow plan. Any extra column can pull a table back into the query that would otherwise have been dropped.

Why it matters

The post offers diagnostics anyone with EXPLAIN access can apply. Millions of rows in "Rows Removed by Join Filter," paired with a large gap between estimated and actual rows, points to a plan-shape problem rather than an indexing one. Layered views can quietly expand a one-line query into hundreds of table references, and the column list controls how many survive pruning. And join_collapse_limit is not monotonic — the safe values have holes — so a setting that works today can regress after someone adds a column to a report, while raising it far enough increases planning cost and can trip GEQO's randomized search. Testing candidate values against a copy of real data, as the author did, remains the only reliable check.