The Join Collapse Limit: When the Planner Stops Searching Join Orders
The number of possible join orders explodes with the number of tables, so above join_collapse_limit (default 8) PostgreSQL stops exhaustively searching and either keeps the order you wrote or switches to a genetic search. That is why a twelve-table report can suddenly get a terrible plan while an eleven-table one is fine, and why the same query is sensitive to how the JOINs are written. The fixes are raising the limit for that statement, accepting a longer planning time, or breaking the query into smaller pieces.
Questions this Concept answers
Why does exhaustive join-order search have to stop somewhere?
J
jeremy
Video
Forcing Join Order with Optimization Barriers in PostgreSQL
Because SQL is declarative, a cost-based planner is free to reorder joins and to flatten nested query expressions (subquery pull-up), so the written form of a query does not constrain the executed jo…