Planner join selection: nested loop vs hash vs merge, the cost crossover, and disable_cost
Simple terms
Three ways to join, and Postgres has no favourite. A nested loop shines when one side is tiny: grab a row, go look it up, repeat. A hash join is for big unsorted sets, build a hash of one side, stream the other past it. A merge join wants both inputs already in order, then walks them in step. None of that is a rule. The planner prices all three against your actual data and keeps whichever comes out cheapest, which is why the same query can quietly switch strategy as a table grows.
You might be asked
PostgreSQL has three join executors, nested loop, hash join, and merge join. Walk me through how the planner chooses between them, what the cost crossover looks like, and what it means when EXPLAIN shows a join cost like 10000000006.12.
Pro concept
Full answer and evidence depth sit behind Pro
You have the question and a plain-English lead. Pro unlocks the short answer, what the docs say, what the code does, the labeled lab evidence or reproduction protocol, how to use it under pressure, and the references.
Unlock the full breakdown for Planner join selection: nested loop vs hash vs merge, the cost crossover, and disable_cost
- How you'd answer it, the full senior-level short answer
- What the docs say, the manual's actual wording, with the citations
- What the code does, the mechanism decoded from PostgreSQL's own source at a pinned tag
- Proof from a real run, labeled as raw output, a captured run summary, or a run-it-yourself protocol
- Using it under pressure, the situation, the call you'd make, what goes wrong, and what people get wrong
- Version notes, what changed across PostgreSQL releases
Card required. Cancel before day 7 and you are not charged.
How this was verified
The open teaser is the question and a plain-English lead. The short answer, the manual walkthrough, the source decode, the lab evidence, and how to use it under pressure unlock with Pro. Evidence is labeled as raw output, a captured run summary, or a run-it-yourself protocol.
Connected
Where this concept connects
How this concept links across the library, the interview questions that test it, its plain-English glossary definition, and the guided pathways it belongs to. Open the full map to explore further.