Comparison
Broadcast joinvsSort-merge join
Broadcast join
the small dimension table is copied to every worker so the big table never has to move.
Joining by sending a copy of the smaller side to every node, avoiding a shuffle of the large side entirely. It is dramatically faster when the small side genuinely is small, and it fails hard when the planner's size estimate is wrong — broadcasting something large runs every executor out of memory. The threshold is configurable and the estimate comes from statistics, which is why stale statistics show up as a query that used to be fast.
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 →