Comparison
Hash joinvsSort-merge join
Hash join
one side is built into an in-memory hash table and the other is streamed past it, and it is fast right up until the build side does not fit.
Joining by building a hash table from one input and probing it with the other. It is the fastest option when the build side fits in memory, and its failure mode is spilling, which turns it into something much slower than the sort-based alternative. Which join an engine picks is a planner decision driven by size estimates, so an unexpectedly slow join is usually a statistics problem rather than a SQL one.
Full entry →Sort-merge join
both sides are too big to broadcast, so each is shuffled and sorted by the key and then walked through in step.
Joining two large datasets by partitioning both on the key, sorting each partition, and merging. It scales to inputs of any size and it is the expensive default, because it pays for both a shuffle and a sort. It is also the join most affected by skew, since a single heavy key produces one partition that dominates the runtime.
Full entry →