Doubling an MPP cluster's nodes barely speeds up a shuffle-heavy query — how do you diagnose it and what do you change?

## What scale-out actually buys Adding compute nodes to a shared-nothing cluster divides the *local* portion of the work: each node scans fewer bytes, filters fewer rows, and builds smaller local hash tables. If a query's time is dominated by scanning and local computation, doubling the nodes genuinely approaches halving the runtime. Everything that is not local behaves differently, and a shuffle-heavy query is mostly not local. ## The four things that do not improve **Hash redistribution volume is roughly invariant.** Redistributing an input moves each row exactly once regardless of how many nodes exist. Doubling `N` does not halve the bytes on the wire; it splits the same bytes into twice as many streams. Per-node received volume does halve, which helps the receiving operator, but aggregate network work does not fall, and the all-to-all connection count grows quadratically, so per-message efficiency drops as messages get smaller. Beyond some point the coordination overhead eats the parallelism gain. **Broadcast volume gets worse.** Replicating a side to every node costs `size × N`. Double the cluster and that traffic doubles, while the same hash table is built redundantly on twice as many machines. A plan that was comfortably broadcast-based on a small cluster can become network- and memory-bound on a large one — the query genuinely gets *slower* per unit of hardware. **Skew is invariant.** All rows sharing a key value are indivisible under hashing. If one value owns 30% of the rows, one worker owns 30% of the work at any cluster size. The critical path is unchanged and the extra nodes idle. **Serial tails stay serial.** The coordinator's final merge for a global sort with a large result, single-threaded result serialization to the client, planning and metadata operations, and any per-query fixed startup cost are all outside the parallel region. This is Amdahl's law with a cloud invoice attached: as the parallel part shrinks, the serial remainder becomes the whole runtime. ## The diagnosis, in order 1. **Attribute the runtime by stage.** From the query profile, get wall time (or CPU time) per stage, and within each stage separate scan/local work from exchange transfer from the accumulating operator above it. If scan is 80% of the time, scale-out is the right lever and you are done. 2. **Check per-worker balance in the dominant stage.** Rows and bytes received per worker. A max-to-median ratio far above one is skew, and no amount of hardware fixes it. 3. **Check the exchange types and their estimated-versus-actual rows.** A broadcast whose actual cardinality is orders of magnitude above the estimate is a statistics problem masquerading as a capacity problem. 4. **Check the tail.** How long after the last worker finishes does the query take to return? A large gap points at the gather/merge or at result serialization, not at the cluster. 5. **Check what else was running.** On a shared cluster, added nodes also add concurrency capacity; if admission changed at the same time, per-query memory and network share changed too. Only after this does a capacity conclusion mean anything. ## The structural changes worth making **Co-locate the hot join.** If one join key drives most of the workload, distributing both tables on it removes the exchange entirely for those queries. This is the single largest available win and also the most expensive to change later, which is why it belongs in schema design rather than in tuning. **Remove the join.** Pre-joining or denormalizing the recurring join into a wide table trades storage and load complexity for zero shuffle at query time. In columnar storage the extra columns are cheap to skip when unused, which makes wide tables far more viable than transactional instincts suggest. **Materialize the recurring aggregate.** If the same expensive shuffle runs on a schedule to serve dashboards, computing it once into a summary table converts a repeated large shuffle into a cheap read. **Narrow the wire.** Project only the needed columns, push filters below the exchange, ensure partial aggregation is happening. Often worth a larger factor than any hardware change and it applies to every future run. **Fix skew at the source.** Sentinel defaults from upstream loaders and grain choices that concentrate rows are data-model defects; salting is a workaround, correcting the model is the fix. ## Scale up versus scale out For shuffle-bound work, fewer larger nodes can beat more smaller ones. More cores per machine means more of the join partners are already in the same address space, less traffic crosses the interconnect, and broadcast replication is paid fewer times. For scan-bound work the opposite holds — many small nodes maximize aggregate I/O bandwidth. Knowing which regime a workload is in is the point of the attribution exercise, and a mixed fleet may reasonably run different workloads on differently shaped compute. ## The judgment call Capacity is the fastest lever and the one with a recurring bill; layout and modelling changes are slow, risky and permanent. The defensible position is to spend on capacity when attribution shows scan-bound work, sustained demand growth or a deadline that cannot wait for a model change — and to refuse it when attribution shows skew, an estimate-driven bad plan choice, or a shuffle that a layout change would delete outright. Buying nodes to hide those simply raises the floor of every future bill.