PostgreSQL famously does not implement query hints. This is about 95% true, and join_collapse_limit is the other 5%: set it to 1, and the planner joins your tables in exactly the order you wrote them. This is not an exploit or an accident of implementation; it is documented, and according to Tom Lane on pgsql-hackers, it is essentially why the parameter still exists. The project had spent years telling people they could force a join order by writing an explicit JOIN nest, and removing the escape hatch seemed like a bad idea. So PostgreSQL has exactly one join-order hint. It just isn’t spelled like one.
Default 8, context user, valid from 1 to 2147483647, no restart. It arrived in PostgreSQL 7.4, in the same commit as from_collapse_limit, and the two defaults are deliberately equal.
The mechanics are the mirror image of its sibling. from_collapse_limit governs whether a subquery in the FROM list is dissolved into its parent’s join problem; join_collapse_limit governs whether an explicit JOIN construct is. An explicit inner join (JOIN, INNER JOIN, CROSS JOIN) is semantically identical to listing the tables in FROM and putting the condition in WHERE, so the planner would very much like to flatten the JOIN tree into a plain list of relations it can reorder freely. It flattens from the bottom up, and stops when going further would produce a FROM list of more than join_collapse_limit items. Whatever stays nested is planned as written: each surviving JOIN node becomes its own smaller join problem, solved independently, with the results combined in the order the query specifies. Join order is chosen within a single join problem and never across two (the from_collapse_limit post covers why that matters), so every point where the flattening stopped is a seam in the plan.
Two exceptions, cutting in opposite directions. FULL JOIN is never flattened, at any setting; its semantics genuinely fix the structure. And outer joins constrain ordering whether or not they are flattened: the planner knows the algebraic identities that make some LEFT JOIN rearrangements valid and others not, and flattening only grants freedom the semantics allow.
The symptom that sends people looking for this parameter is a query with more than eight explicit joins whose plan is sensible up to a point and then follows the written join order off a cliff. The classic report is “we added one more join and the query fell over”; the one they added was the ninth. The usual habitat is ORM-generated SQL and views stacked on views, where the join count crept up without anyone counting. Raising join_collapse_limit per session to cover the join count fixes it, at a price in planning time; EXPLAIN reports Planning Time, and that is the number to watch as you raise it. Mind the tripwire while you do: geqo_threshold defaults to 12, and pushing join_collapse_limit past it hands the query to GEQO, trading a complete search of a small space for a random sample of a large one. If you raise the collapse limits, move geqo_threshold above them in the same edit.
The more interesting direction is down. Issue SET LOCAL join_collapse_limit = 1 inside a transaction, and the query you wrap gets exactly the join order you wrote. You do not have to pin everything: a JOIN inside an otherwise comma-separated FROM list pins only that construct. The documentation’s own example is FROM a CROSS JOIN b, c, d, e; at 1, a is joined to b first, and the planner stays free to arrange everything else. (The other entries in the folk-hint repertoire, OFFSET 0 and AS MATERIALIZED CTEs, are optimization fences rather than order constraints; related tool, blunter instrument.)
Be honest about what it means when this works. If forcing the join order produces a better plan, the planner’s estimates were wrong, and you have papered over that fact rather than repaired it. The durable fixes are statistical: raise the statistics target on the columns being misjudged, add extended statistics with CREATE STATISTICS for the correlated columns that fooled the estimator, run ANALYZE. The hint is a tourniquet, and tourniquets are for emergencies.
Leave it at 8 in postgresql.conf. The value that earns this parameter its keep is 1, per query, in a SET LOCAL, on the day you genuinely know better than the statistics. If that day comes often, the statistics are the problem.