Which Join Algorithm the Planner Picks, and the Input Shape Each One Wants
The three join algorithms are not ranked; each wants a different input shape. A nested loop wants a small outer input and an indexed inner side; a hash join wants one side small enough to fit in memory and no useful index; a merge join wants both sides already sorted on the join key. The planner chooses by estimated row counts, so when the estimate is wrong it does not just get the size wrong, it picks the wrong algorithm entirely. Reading which join you got, and asking whether your real data matches the shape that join wants, is a fast way to explain a slow join.
Questions this Concept answers
- Why is no single join algorithm simply the best one?
When the Optimizer Picks Nested Loop, Merge, or Hash Joins in Oracle Database
A relational optimizer implements the join operator by one of three physical algorithms — nested loops (probe the inner table once per outer row, O(n·m) unless an index reduces the inner probe), sort…