Multi-Stage Hash Join Techniques

• Multi-stage hash join is a family of techniques that decomposes join processing into discrete stages to control intermediate-result growth, balance load, and minimize data movement.
• It employs varied execution models, such as three-stage pipelines on accelerators, hot-key based splits in distributed systems, and Lookup/Expand in column stores.
• These methods optimize performance by tailoring join operations to hardware capacities, network conditions, and workload skew, reducing DRAM and network costs.

Searching arXiv for the cited works and closely related terminology. Multi-stage hash join is a family of join-processing strategies that decomposes hash-based join evaluation into multiple explicit phases rather than treating a join as a single monolithic build-and-probe operator. In the literature, this decomposition appears in several distinct but related forms: a three-stage streaming pipeline for multiway joins on coarse-grain reconfigurable hardware; a multistage distributed architecture that isolates hot and cold keys and assigns each sub-join to a different execution method; and a logical decomposition of hash join into Lookup and Expand operators for acyclic joins in column stores (Olukotun et al., 2019, Metwally, 2022, Bekkers et al., 2024). Across these settings, the common objective is to control intermediate-result growth, reduce data movement, and improve load balance by making the stages of join evaluation explicit.

1. Scope and conceptual variants

The term encompasses several execution models rather than a single algorithmic template. In "Efficient Multiway Hash Join on Reconfigurable Hardware" (Olukotun et al., 2019), a classic three-way join

$R(A,B)\ \bowtie\ S(B,C)\ \bowtie\ T(C,D)$

is implemented as a three-stage streaming pipeline consisting of global partitioning, per-partition build, and nested probing. In "Scaling and Load-Balancing Equi-Joins" (Metwally, 2022), Adaptive-Multistage-Join (AM-Join) decomposes an equi-join $R\bowtie S$ into four disjoint sub-joins according to whether a key is hot or cold in each input, and then assigns those sub-joins to Tree-Join, Index-Broadcast-Join, or a standard shuffle join. In "Instance-Optimal Acyclic Join Processing Without Regret: Engineering the Yannakakis Algorithm in Column Stores" (Bekkers et al., 2024), hash join is decomposed into Lookup and Expand in Nested Semijoin Algebra (NSA), then executed through shredding in a column-store engine.

These formulations differ in substrate, data model, and optimization target. The hardware-oriented formulation emphasizes on-chip scratchpad capacity, static on-chip communication, and DRAM traffic (Olukotun et al., 2019). The distributed formulation emphasizes skew tolerance, shared-nothing scalability, and outer-join support without record deduplication or custom table partitioning (Metwally, 2022). The column-store formulation emphasizes robustness against large intermediates for acyclic queries and a no-regret transformation from a binary join plan to a 2-phase NSA plan (Bekkers et al., 2024).

A plausible implication is that "multi-stage hash join" is best treated as an execution principle: the operator is factored into stages that expose structure relevant to the target system, such as partition locality, skew class, or output reconstruction.

2. Stage decompositions in representative systems

The three works instantiate staging in sharply different ways.

Setting	Stages	Primary purpose
Plasticine multiway join	Partitioning, build, probe	Avoid large intermediates and exploit CGRA locality
AM-Join	Hot-key discovery, HH/HC/CH/CC split, specialized sub-joins	Balance skew and minimize network movement
SYA / NSA	Lookup, then Expand / Flatten	Avoid large intermediates in acyclic joins

In the Plasticine design, Stage 0 performs global partitioning. $R$ and $S$ are hashed on $B$ using a high-level hash $H(B)$ into $P=|R|/M$ top-level partitions, while $T$ is hashed on $C$ using $G(C)$ into $R\bowtie S$0 partitions; $R\bowtie S$1 is chosen so that $R\bowtie S$2, where $R\bowtie S$3 is on-chip capacity (Olukotun et al., 2019). Stage 1 loads one $R\bowtie S$4 from DRAM onto the chip and hashes each tuple $R\bowtie S$5 to one of $R\bowtie S$6 on-chip buckets via a second hash $R\bowtie S$7, building per-PMU hash tables keyed on $R\bowtie S$8. Stage 2 iterates over $R\bowtie S$9-side buckets: the corresponding $R$0 tuples with $R$1 are streamed into the same PMU tables, then $R$2 is streamed and broadcast to every PMU, where each PMU performs a small three-way match of its local $R$3- and $R$4-rows with the broadcast tuple and emits or aggregates the result on the fly (Olukotun et al., 2019).

AM-Join begins with hot-key identification. Let $R$5 and $R$6 be the sets of hot keys in $R$7 and $R$8. The result is decomposed as

$R$9

where each quadrant is disjoint by construction (Metwally, 2022). Hot-hot keys are handled by Tree-Join, a multi-round split-and-shuffle join; hot-cold and cold-hot cases are handled by Index-Broadcast-Join (IB-Join), which builds a hash index on the small side, broadcasts it, and probes from the large side in one pass; cold-cold keys are handled by a standard single-round shuffle join (Metwally, 2022).

In the NSA-based formulation, a binary join such as $S$0 is decomposed into dictionary construction, a nested semijoin Lookup, and a later Expand:

$S$1

The dictionary $S$2 maps each $S$3 to the bag of matching $S$4 values, Lookup attaches the corresponding nested bag to each tuple of $S$5, and Expand unnests it into flat output tuples (Bekkers et al., 2024). Shredded Yannakakis (SYA) transforms a binary join plan for an acyclic query into a 2-phase NSA plan in which lookup-like operators precede the final flattening phase (Bekkers et al., 2024).

3. Hardware realization: partition, build, and probe on a CGRA

The Plasticine-style accelerator is a tile-based CGRA organized as a 2D checkerboard of Pattern Memory Units (PMUs) and Pattern Compute Units (PCUs) (Olukotun et al., 2019). The described configuration has $S$6 PMUs and $S$7 PCUs arranged, for example, in an $S$8 layout, an on-chip total scratchpad of $S$9, SIMD PCUs with $B$0 lanes and peak $B$1 TFLOPS, a static high-bandwidth network supporting unicast, multicast, and broadcast, and off-chip DDR3 DRAM at $B$2 GB/s read/write (Olukotun et al., 2019). The memory hierarchy is

$B$3

The execution mapping is explicitly stage-specific. Partitioning and join stages reconfigure the routing and PCU microcode on the fly, and all hash functions are synthesized as bit-shifts and xors in PCUs (Olukotun et al., 2019). During build, tuples of $B$4 are streamed into $B$5 disjoint per-PMU hash tables using $B$6. During probe, the $B$7 tuples stream in interleaved with build-table writes, collision handling in hardware is parallelized across PCUs, and each broadcast $B$8 tuple simultaneously probes the build tables for both $B$9 and $H(B)$0 matches through a 2-level hash lookup (Olukotun et al., 2019). Double-buffered PMU banks overlap DRAM prefetch of the next partition with current join computation, and the entire chip is reconfigured once per top-level $H(B)$1 partition, while the inner probe loops run freely (Olukotun et al., 2019).

This architecture is specifically motivated by the case where joining three or more relations in a single step is preferable because the join of any two relations would yield an excessively large intermediate relation (Olukotun et al., 2019). The implementation therefore treats staging not merely as a software pipeline but as a mapping of partition-local state, communication mode, and compute microcode onto a reconfigurable substrate.

4. Distributed multistage joins under skew

AM-Join addresses the case in which equi-joins become difficult because hot keys appear in both relations at large scale (Metwally, 2022). Its central design decision is to classify keys by hotness in each input and dispatch each quadrant of the decomposition to an appropriate operator.

The hot-key threshold is derived from a comparison between the cost of a single-round shuffle join, $H(B)$2, and the cost of one Tree-Join splitter stage followed by processing of the resulting sublists. If a key has joined-list sizes $H(B)$3 and $H(B)$4, with effective size $H(B)$5, then in the worst case a key is classified hot when

$H(B)$6

where $H(B)$7 is the ratio of network cost to local I/O cost and $H(B)$8 is the number of executors (Metwally, 2022). A simple sufficient threshold independent of $H(B)$9 is

$P=|R|/M$0

When a hot key is split, Tree-Join chooses a chunking exponent $P=|R|/M$1 such that the splitter cost $P=|R|/M$2 matches the downstream executor cost $P=|R|/M$3, giving $P=|R|/M$4. The resulting number of sub-lists per side is

$P=|R|/M$5

and each sub-list has size

$P=|R|/M$6

After $P=|R|/M$7 split rounds, each inherited sublist has size $P=|R|/M$8, and splitting stops once the hot threshold is no longer exceeded; the number of iterations satisfies

$P=|R|/M$9

These are the formal basis for the claim that only a doubly logarithmic number of split rounds is needed (Metwally, 2022).

AM-Join also modifies the initial round to preserve balance. Tree-JoinBasic would hash all occurrences of a hot key onto one partition in the first iteration, so the load-balanced version first runs a distributed heavy-hitters procedure using Space-Saving plus treeAggregate to produce $T$0, then "unravels" the hot keys into an augmented-key index of sub-list pairs before Tree-JoinBasic proceeds (Metwally, 2022). The paper states that this avoids building a huge initial joined index for very hot keys and preserves randomness and balance even in round 1 (Metwally, 2022).

The same staging framework extends to outer joins. Table 2 of the paper states that AM-Join supports all outer-join kinds by selecting for each of the four sub-joins either Tree-Join, Index-Broadcast-Left-Outer-Join, or a shuffle variant of left, right, or full outer join, and no extra deduplication is needed because the sub-joins are disjoint and use standard outer-join semantics (Metwally, 2022).

5. Cost models and stage-selection criteria

The literature treats multistage hash join not only as an execution pattern but also as a planning problem governed by explicit cost models.

For the linear three-way join on Plasticine, the total number of DRAM tuples read onto the chip is

$T$1

The interpretation given is that each $T$2 and $T$3 tuple is loaded exactly once, while each $T$4 tuple is reread once per top-level $T$5 partition, namely $T$6 times (Olukotun et al., 2019). For a cascaded binary plan that first computes $T$7 with $T$8, where $T$9 is the join-key distinct count, the approximate cost is

$C$0

The paper notes that when $C$1, the second term incurs $C$2 passes over DRAM, so the cost balloons (Olukotun et al., 2019).

For cyclic three-way joins where keys are pairwise shared, the cost is

$C$3

with optimal choice

$C$4

yielding

$C$5

These formulas formalize the dependence of multistage join cost on top-level partitioning and on-chip capacity (Olukotun et al., 2019).

AM-Join presents a different cost comparison. For a hot key processed on a single executor, the local I/O cost is $C$6. In Tree-Join, the splitter emits $C$7 sub-pairs at cost $C$8, network cost is $C$9, and each downstream executor also performs $G(C)$0 local I/O, so the bottleneck becomes $G(C)$1 rather than $G(C)$2 (Metwally, 2022). For the hot-cold case, broadcast is chosen when the cost of broadcasting the small side does not exceed the shuffle cost of the large side:

• broadcast time of $G(C)$3:

$G(C)$4

• shuffle time for $G(C)$5:

$G(C)$6

The hot-key collection phase itself has cost

$G(C)$7

(Metwally, 2022).

In the NSA formulation, the cost model is stated in terms of three convex, monotone unit-cost functions: $G(C)$8 for building a hash map on $G(C)$9 keys, $R\bowtie S$00 for $R\bowtie S$01 probes into a map of size $R\bowtie S$02, and $R\bowtie S$03 for emitting a column of $R\bowtie S$04 output tuples (Bekkers et al., 2024). A traditional flat hash join costs

$R\bowtie S$05

In a 2-phase NSA plan, each Lookup costs $R\bowtie S$06, each build costs $R\bowtie S$07, and the final Flatten costs $R\bowtie S$08 (Bekkers et al., 2024). The paper’s two central guarantees are robustness, meaning asymptotic $R\bowtie S$09 time for a 2-phase NSA plan on an acyclic join, and no regret, meaning that for every well-behaved binary plan $R\bowtie S$10, its SYA rewriting incurs no more build, probe, or generation cost than $R\bowtie S$11, pointwise on every database (Bekkers et al., 2024).

These cost models support a consistent planning principle: choose a staged plan when it lowers DRAM traffic, network movement, or intermediate construction relative to a simpler binary or single-round alternative.

6. Empirical results, misconceptions, and practical interpretation

The reported empirical outcomes differ by setting but consistently show that staging is most valuable when a conventional hash join would suffer from large intermediates or severe skew.

On the Plasticine performance model with $R\bowtie S$12 PMUs/PCUs, DRAM bandwidth of $R\bowtie S$13 GB/s, and on-chip memory of $R\bowtie S$14 MB, the CPU baseline is single-threaded Postgres on Xeon E5-2697 (Olukotun et al., 2019). For a cascaded binary self-join over three copies of a "friends" relation $R\bowtie S$15 of size $R\bowtie S$16 and distinct count $R\bowtie S$17, the accelerator achieves $R\bowtie S$18 to $R\bowtie S$19 speedup over the CPU, with the best results when $R\bowtie S$20; if the intermediate spills, the bottleneck shifts from DRAM streaming to SSD at $R\bowtie S$21 MB/s (Olukotun et al., 2019). For a single linear three-way self-join, the implementation achieves up to $R\bowtie S$22 speedup relative to cascaded binary joins on the same hardware for $R\bowtie S$23 and $R\bowtie S$24, with the largest speedups when $R\bowtie S$25 is small or DRAM bandwidth is limited (Olukotun et al., 2019). In a star-join where the large fact table $R\bowtie S$26 has size $R\bowtie S$27 and two dimension tables $R\bowtie S$28 and $R\bowtie S$29 each have size $R\bowtie S$30 with $R\bowtie S$31, $R\bowtie S$32 and $R\bowtie S$33 fit entirely on chip and the single 3-way join yields up to $R\bowtie S$34 speedup over two binary joins (Olukotun et al., 2019).

AM-Join reports that on two relations with more than $R\bowtie S$35 and $R\bowtie S$36 records, record size $R\bowtie S$37 or $R\bowtie S$38 bytes, and Zipf skew parameter $R\bowtie S$39, AM-Join and Tree-Join scale nearly linearly in $R\bowtie S$40 up to $R\bowtie S$41 and tolerate $R\bowtie S$42 up to $R\bowtie S$43--$R\bowtie S$44, whereas single-round Hash-Join and Spark3-Join break down above $R\bowtie S$45 (Metwally, 2022). The paper also reports a same-attribute self-join speedup of approximately $R\bowtie S$46 from pruning duplicate pairs in Tree-JoinBasic, and on real Uber trip data totaling $R\bowtie S$47 TB, only AM-Join and Tree-Join complete a same-attribute self-join in $R\bowtie S$48 Hrs while others time out at $R\bowtie S$49 Hrs (Metwally, 2022).

SYA is implemented in Apache DataFusion using Rust and Arrow, with binary-plan input orders taken from DuckDB’s optimizer, and evaluated on $R\bowtie S$50 acyclic queries drawn from JOB, STATS-CEB, and a CE graph workload (Bekkers et al., 2024). The paper states that all runs exclude planning, I/O, aggregation, and focus only on join time. On JOB, SYA improves $R\bowtie S$51 of queries with speedups up to $R\bowtie S$52; on STATS-CEB, $R\bowtie S$53 with speedups up to $R\bowtie S$54; on CE, $R\bowtie S$55 with speedups up to $R\bowtie S$56 (Bekkers et al., 2024). The abstract states that across the full suite, SYA improves performance for $R\bowtie S$57 of the queries with speedups up to $R\bowtie S$58, while the detailed summary later states that SYA outperforms traditional binary hash join on $R\bowtie S$59 of $R\bowtie S$60 queries (Bekkers et al., 2024). This difference reflects distinct summary points within the same source and should not be conflated into a single percentage without qualification.

Several misconceptions are corrected by these results. One is that multistage execution is synonymous with repeated repartitioning in distributed systems; the hardware and column-store formulations show staging through on-chip partition/build/probe and Lookup/Expand decomposition instead (Olukotun et al., 2019, Bekkers et al., 2024). Another is that multistage methods are only beneficial for pathological cases. The SYA results emphasize competitiveness even on common acyclic workloads, while the Plasticine and AM-Join results show that the largest gains arise precisely when intermediate-result blowup or skew would otherwise dominate execution (Olukotun et al., 2019, Metwally, 2022, Bekkers et al., 2024). A further misconception is that staging necessarily requires custom data placement or deduplication logic: AM-Join explicitly states support for all outer-join variants without record deduplication or custom table partitioning, and SYA states that no extra per-tuple callbacks or code generation is needed once the NSA plan is shredded (Metwally, 2022, Bekkers et al., 2024).

Taken together, these works present multi-stage hash join as a systems-level technique for restructuring join execution around the actual sources of cost: memory capacity and broadcast locality on accelerators, key skew and network movement in shared-nothing clusters, and intermediate-result avoidance plus vectorized reconstruction in column stores.