Join Strategies
The three ways a database combines two row sets — nested loop, hash join and merge join — and the conditions under which each is correct.
Nested loop. For each row on the outer side, look up matches on the inner side. Excellent when the outer side is small and the inner side has a selective index; quadratic and catastrophic when the outer side turns out to be large. This is the plan you find behind a query that was fast in development and is unusable in production, because the row estimate was wrong.
Hash join. Build a hash table from the smaller side, probe it with the larger. The right choice for joining two large sets on equality with no useful index. Cost is memory — if the hash table does not fit in working memory it spills to disk, which is a common and fixable slowdown.
Merge join. Both sides sorted on the join key, then walked in parallel. Efficient for very large joins and free when the inputs are already sorted (from an index scan); otherwise it pays for two sorts.
What this is for: reading a plan and knowing whether the strategy is reasonable given the real row counts. A nested loop over five rows is ideal; the same plan over five million means the estimate was wrong, and the fix is statistics or a predicate rather than a hint.