Changing one number from 32 to 34 made a Postgres query 6,000 faster. Here's exactly why.
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…
This story concerns a Postgres query that took over two minutes to execute - more than six thousand times longer than it should have. The query was built on layered views, with a nested loop step that joined together 435 tables. Postgres built the entire join, then tried to filter out all rows that didn't match. That step took 1100 ms, and the planner estimated it would only take one row. So instead of looking up the matching enrollments for each of the 181 courses through an index, it did the full join 181 times.
There were two fixes. The first was to raise a planner setting called join_collapse_limit. This value determines how many tables Postgres will try to join together at once before grouping the rest of the steps together. The default is 8, and many settings in between were slow. At 32, the query still took 140 seconds. But at 34, it was only 0.04 seconds - that's 6,000 times faster.
The second fix was not to select as many columns, since each extra column could bring back a table that would otherwise have been removed by the planner.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
