Model
Broadcast or shuffle a join
The shuffle's cost was relocated into memory, not removed.
- Large side
- the large input
- Small side
- the small input (copied to every node)
- Parallelism
- nodes
How it behaves
One input on the horizontal axis, the others held fixed.
broadcastshuffle
Data table
| Parallelism P | series | Bytes moved |
|---|---|---|
| 10 | broadcast | 4.66 GB |
| 10.8 | broadcast | 5.03 GB |
| 11.7 | broadcast | 5.44 GB |
| 12.6 | broadcast | 5.88 GB |
| 13.6 | broadcast | 6.35 GB |
| 14.7 | broadcast | 6.87 GB |
| 15.9 | broadcast | 7.42 GB |
| 17.2 | broadcast | 8.02 GB |
| 18.6 | broadcast | 8.67 GB |
| 20.1 | broadcast | 9.37 GB |
| 21.7 | broadcast | 10.1 GB |
| 23.5 | broadcast | 10.9 GB |
| 25.4 | broadcast | 11.8 GB |
| 27.5 | broadcast | 12.8 GB |
| 29.7 | broadcast | 13.8 GB |
| 32.1 | broadcast | 14.9 GB |
| 34.7 | broadcast | 16.1 GB |
| 37.5 | broadcast | 17.4 GB |
| 40.5 | broadcast | 18.9 GB |
| 43.8 | broadcast | 20.4 GB |
| 47.3 | broadcast | 22 GB |
| 51.1 | broadcast | 23.8 GB |
| 55.2 | broadcast | 25.7 GB |
| 59.7 | broadcast | 27.8 GB |
| 64.5 | broadcast | 30 GB |
| 69.7 | broadcast | 32.5 GB |
| 75.4 | broadcast | 35.1 GB |
| 81.5 | broadcast | 37.9 GB |
| 88 | broadcast | 41 GB |
| 95.1 | broadcast | 44.3 GB |
| 103 | broadcast | 47.9 GB |
| 111 | broadcast | 51.8 GB |
| 120 | broadcast | 55.9 GB |
| 130 | broadcast | 60.4 GB |
| 140 | broadcast | 65.3 GB |
| 152 | broadcast | 70.6 GB |
| 164 | broadcast | 76.3 GB |
| 177 | broadcast | 82.5 GB |
| 191 | broadcast | 89.1 GB |
| 207 | broadcast | 96.3 GB |
| 224 | broadcast | 104 GB |
| 242 | broadcast | 113 GB |
| 261 | broadcast | 122 GB |
| 282 | broadcast | 131 GB |
| 305 | broadcast | 142 GB |
| 330 | broadcast | 154 GB |
| 356 | broadcast | 166 GB |
| 385 | broadcast | 179 GB |
| 416 | broadcast | 194 GB |
| 450 | broadcast | 210 GB |
| 486 | broadcast | 226 GB |
| 526 | broadcast | 245 GB |
| 568 | broadcast | 264 GB |
| 614 | broadcast | 286 GB |
| 663 | broadcast | 309 GB |
| 717 | broadcast | 334 GB |
| 775 | broadcast | 361 GB |
| 838 | broadcast | 390 GB |
| 905 | broadcast | 422 GB |
| 978 | broadcast | 456 GB |
| 1.06K | broadcast | 492 GB |
| 1.14K | broadcast | 532 GB |
| 1.24K | broadcast | 575 GB |
| 1.33K | broadcast | 622 GB |
| 1.44K | broadcast | 672 GB |
| 1.56K | broadcast | 726 GB |
| 1.69K | broadcast | 785 GB |
| 1.82K | broadcast | 848 GB |
| 1.97K | broadcast | 917 GB |
| 2.13K | broadcast | 991 GB |
| 2.3K | broadcast | 1.05 TB |
| 2.49K | broadcast | 1.13 TB |
| 2.69K | broadcast | 1.22 TB |
| 2.9K | broadcast | 1.32 TB |
| 3.14K | broadcast | 1.43 TB |
| 3.39K | broadcast | 1.54 TB |
| 3.66K | broadcast | 1.67 TB |
| 3.96K | broadcast | 1.8 TB |
| 4.28K | broadcast | 1.95 TB |
| 4.63K | broadcast | 2.1 TB |
| 5K | broadcast | 2.27 TB |
| 10 | shuffle | 373 GB |
| 10.8 | shuffle | 373 GB |
| 11.7 | shuffle | 373 GB |
| 12.6 | shuffle | 373 GB |
| 13.6 | shuffle | 373 GB |
| 14.7 | shuffle | 373 GB |
| 15.9 | shuffle | 373 GB |
| 17.2 | shuffle | 373 GB |
| 18.6 | shuffle | 373 GB |
| 20.1 | shuffle | 373 GB |
| 21.7 | shuffle | 373 GB |
| 23.5 | shuffle | 373 GB |
| 25.4 | shuffle | 373 GB |
| 27.5 | shuffle | 373 GB |
| 29.7 | shuffle | 373 GB |
| 32.1 | shuffle | 373 GB |
| 34.7 | shuffle | 373 GB |
| 37.5 | shuffle | 373 GB |
| 40.5 | shuffle | 373 GB |
| 43.8 | shuffle | 373 GB |
| 47.3 | shuffle | 373 GB |
| 51.1 | shuffle | 373 GB |
| 55.2 | shuffle | 373 GB |
| 59.7 | shuffle | 373 GB |
| 64.5 | shuffle | 373 GB |
| 69.7 | shuffle | 373 GB |
| 75.4 | shuffle | 373 GB |
| 81.5 | shuffle | 373 GB |
| 88 | shuffle | 373 GB |
| 95.1 | shuffle | 373 GB |
| 103 | shuffle | 373 GB |
| 111 | shuffle | 373 GB |
| 120 | shuffle | 373 GB |
| 130 | shuffle | 373 GB |
| 140 | shuffle | 373 GB |
| 152 | shuffle | 373 GB |
| 164 | shuffle | 373 GB |
| 177 | shuffle | 373 GB |
| 191 | shuffle | 373 GB |
| 207 | shuffle | 373 GB |
| 224 | shuffle | 373 GB |
| 242 | shuffle | 373 GB |
| 261 | shuffle | 373 GB |
| 282 | shuffle | 373 GB |
| 305 | shuffle | 373 GB |
| 330 | shuffle | 373 GB |
| 356 | shuffle | 373 GB |
| 385 | shuffle | 373 GB |
| 416 | shuffle | 373 GB |
| 450 | shuffle | 373 GB |
| 486 | shuffle | 373 GB |
| 526 | shuffle | 373 GB |
| 568 | shuffle | 373 GB |
| 614 | shuffle | 373 GB |
| 663 | shuffle | 373 GB |
| 717 | shuffle | 373 GB |
| 775 | shuffle | 373 GB |
| 838 | shuffle | 373 GB |
| 905 | shuffle | 373 GB |
| 978 | shuffle | 373 GB |
| 1.06K | shuffle | 373 GB |
| 1.14K | shuffle | 373 GB |
| 1.24K | shuffle | 373 GB |
| 1.33K | shuffle | 373 GB |
| 1.44K | shuffle | 373 GB |
| 1.56K | shuffle | 373 GB |
| 1.69K | shuffle | 373 GB |
| 1.82K | shuffle | 373 GB |
| 1.97K | shuffle | 373 GB |
| 2.13K | shuffle | 373 GB |
| 2.3K | shuffle | 373 GB |
| 2.49K | shuffle | 373 GB |
| 2.69K | shuffle | 373 GB |
| 2.9K | shuffle | 373 GB |
| 3.14K | shuffle | 373 GB |
| 3.39K | shuffle | 373 GB |
| 3.66K | shuffle | 373 GB |
| 3.96K | shuffle | 373 GB |
| 4.28K | shuffle | 373 GB |
| 4.63K | shuffle | 373 GB |
| 5K | shuffle | 373 GB |
Worked examples, recomputed
Every number the mechanisms quote for this relationship is recomputed from it, so none of them disagrees with it.
- 500 MB × 200 = 100 GB broadcast
- 400 GB ÷ 0.5 GB = 800 > 200: broadcast wins on network
- a 1 MB table to 10,000 consumers → 10 GB
Where it appears
Each mechanism states it in its own names. The general symbol each name stands for is shown with it.
- Query Processing
Why can one question be answered by plans whose costs differ by a factor of ten thousand, and what does each operator force the engine to do?