Choose a join algorithm
Two inputs must be joined: orders to customers, events to sessions, readings to time windows. Every join algorithm is a decision about where the lookup structure comes from: an index that already exists, an order the data already has, or a hash table built for this one query. Which algorithm fits this join?
You will end with a join algorithm and placement, the memory threshold it works within, and the distribution checks that can override it.
Decide
Is the join condition an equality?
a.customer_id = b.customer_id is; a.ts BETWEEN b.start AND b.end is not.
Why
Hashing only finds exact matches, so a hash join cannot serve a range or overlap condition.
Does the data already have a property the join can use?
An index on the join key with a small other side; both sides already sorted by the key; both sides already partitioned by the key.
Index, small sideIndex nested-loop join
SortedMerge join
Co-partitionedLocal join, no network
NoneGo to 3
Why
Properties the data already has were paid for when it was written. Using them is the only source of free wins, and the step skipped more often than any other.
Is the large side more than P times the small side, and does the small side fit on every machine?
Each machine also builds its own hash table from the small side, so check that CPU too.
Why
Broadcasting copies the small side to every machine: P × small side moves. Shuffling repartitions both sides by key: about large + small moves. Broadcast wins when the size ratio exceeds P.
Where you can land
A non-equality join
Use it when the condition is a range, a band or an overlap.
Use a band merge (both sides sorted, a sliding window of matches), an interval structure, or a block nested loop.
Before you build it: check whether it is secretly an as-of join ("the most recent price at or before each trade"). If so, use the engine's as-of operator, which is far cheaper.
Index nested-loop join
Use it when one side is small and the other has an index on the join key.
What it costs: one index lookup per row of the small side; it degrades quickly as that side grows.
Merge join
Use it when both sides are already sorted by the join key.
Constant memory, and it cannot run out. What it costs: if either side is not sorted, a sort, with a log factor.
Local join, no network
Use it when both sides are already partitioned by the join key.
What it costs: nothing at query time; the partitioning was paid for when the data was written.
Broadcast join
Use it when the small side is tiny relative to the large side and fits everywhere.
What it costs: P copies of the small side, and P hash-table builds.
Shuffle join
Use it when neither side is small enough to broadcast.
Both sides are repartitioned by key, then each partition is joined locally as below.
What it costs: moving both sides across the network, and exposure to hot keys, which land on one machine.
Hash join
Use it when nothing is sorted, indexed or co-partitioned, on one machine or within a partition.
Before you build it
Size the build side against memory
Build side under memory M: an in-memory hash table. Between M and M²/B, where B is the size of one I/O buffer: a hybrid hash join, which degrades gradually as a ramp. Above M²/B: partition recursively, or switch to a merge join for its constant memory. With 16 GB of memory and 8 MB buffers, M²/B is about 32 TB.
Whichever you land on
Decide whether a runtime filter pays
A runtime filter is a compact summary (a Bloom filter, about 9.6 bits per key at 1% false positives) built from one side's keys and used to skip rows on the other side before the join. Its leverage ≈ (row size in bits ÷ 9.6) × (1 − fraction of rows that match). Wide rows joined selectively: use it, and accept that the scan waits for the filter. Narrow rows, or most rows match: it is overhead.
Check the key distribution: it overrides everything above
Check the most frequent key's share, the fraction of NULL keys, and for range joins the spread of interval lengths. A plan chosen from table sizes and run against skewed keys is the standard failure: one hot key lands entirely on one machine or one hash bucket.