How does an MPP planner choose between broadcasting one join input and hash-redistributing both?

## The same goal, two ways to reach it A distributed join needs rows with equal join keys on the same machine. There are exactly two general ways to achieve that with an exchange: 1. **Replicate one side.** Send every row of the smaller input to every node. Each node now holds the whole small side plus its own share of the large side, and can join locally. 2. **Repartition both sides.** Hash both inputs on the join key and send each row to the node owning its bucket. Each node then holds matching subsets of both sides. A third, cheapest case exists when the data is already placed correctly: if both inputs are distributed on the join key, no movement is required at all, and if only one is, only the other side needs to move (a single-sided redistribution). ## The cost model Let `N` be the node count, `S` the bytes of the smaller input after filtering and projection, and `L` the bytes of the larger input. - **Broadcast** moves roughly `S × N` bytes in total (or `S × (N-1)` from each node's perspective, depending on how the engine counts). It is *independent of `L`* — the big table never moves. Every node must materialize a hash table over the whole of `S`, so per-node memory is `S`, not `S / N`. - **Hash redistribution** moves roughly `S + L` bytes, each row exactly once. Per-node memory for the build side is about `S / N`, because the small side is also split. Broadcast therefore wins when `S × N < S + L`, i.e. roughly when `S` is small relative to `L / N`. Concretely: with 20 nodes, broadcasting a 2 GB input moves 40 GB, while redistributing a 2 GB and a 4 TB input moves about 4 TB — broadcast is dramatically cheaper. Broadcasting a 200 GB input across the same 20 nodes moves 4 TB and needs 200 GB of hash table on every node, which is usually fatal. Note the asymmetry with cluster size: **broadcast cost grows with node count, redistribution cost does not.** A broadcast plan that is comfortable on a 4-node cluster can become the dominant cost on a 64-node one, which is one reason the "same query, bigger cluster, no faster" complaint appears. ## What the planner actually uses The decision is made from estimated cardinalities and average row widths, after predicate and projection pushdown — what matters is the size of the input *as it arrives at the join*, not the table's size on disk. A highly selective filter can turn a large table into a broadcastable input. Most engines also apply a hard ceiling: if the estimated broadcast side exceeds a memory or size threshold, redistribution is chosen regardless. Because the choice is estimate-driven, its failure mode is estimate-driven too. If statistics are stale, or the input is the result of an earlier join or an aggregation whose cardinality is hard to predict, the planner may believe a side is 50 MB when it is 50 GB. The query then broadcasts 50 GB to every node, memory pressure spikes on all of them at once, the join spills or the query is killed for exceeding its memory limit. The tell in a profile is a broadcast exchange whose actual row count dwarfs its estimate. ## Row width matters as much as row count Shuffle cost is bytes, not rows. Selecting fifteen columns where three are needed multiplies the broadcast volume accordingly, and wide string columns dominate quickly. Projecting only the join key and the columns actually consumed above the join — and filtering before the exchange rather than after — often changes the decision from redistribution to broadcast and shrinks the moved data at the same time. ## Skew interacts with the choice Redistribution is vulnerable to skew: if one join key value accounts for a large share of rows, one node receives that whole share and becomes the query's runtime. Broadcast is immune to key skew on the probe side, because the large input never moves — each node simply probes its own local rows against the full copy. That makes broadcast attractive when the join key is known to be lopsided and the other input is genuinely small. Conversely, broadcast has its own skew-like hazard: it multiplies memory pressure uniformly, so it fails on *all* nodes at once rather than on one, and it competes with every other query running concurrently on the same cluster. ## Practical levers An engineer influences this in four ways: keep statistics current so the estimate is right; filter and project before the join so the candidate side really is small; align physical placement with the hottest join key so neither side has to move; and, where the engine exposes it, review the chosen strategy in the plan and treat an unexpected broadcast of a large input as a statistics bug rather than a tuning knob.