Comparison
Broadcast joinvsShuffle
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 →Shuffle
the join needs matching keys on the same machine, so the engine moves the whole dataset across the network before it can start.
Redistributing data across workers so that rows sharing a key end up together, which joins, group-bys and window functions all require. It is the expensive step in any distributed query, because it is bounded by network and disk rather than by CPU. Most distributed query tuning is really about avoiding shuffles, reducing what they carry, or making sure the data was already partitioned the way the join needs.
Full entry →