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 ... .β¦