---
title: Multi-Stage Hash Join Techniques
url: https://www.emergentmind.com/topics/multi-stage-hash-join
type: topic
---

# Multi-Stage Hash Join Techniques

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 [1905.13376], [2209.08475], [2411.04042]. 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" [1905.13376], 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" [2209.08475], 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" [2411.04042], 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 [1905.13376]. The distributed formulation emphasizes skew tolerance, shared-nothing scalability, and outer-join support without record deduplication or custom table partitioning [2209.08475]. 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 [2411.04042].

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 $Q=|T|/M$ partitions; $P$ is chosen so that $|R_i|\le M$, where $M$ is on-chip capacity [1905.13376]. Stage 1 loads one $R_i$ from DRAM onto the chip and hashes each tuple $r(a,b)$ to one of $U$ on-chip buckets via a second hash $h(b)$, building per-PMU hash tables keyed on $b$. Stage 2 iterates over $T$-side buckets: the corresponding $S_i$ tuples with $g(c)=j$ are streamed into the same PMU tables, then $T_j$ is streamed and broadcast to every PMU, where each PMU performs a small three-way match of its local $R$- and $S$-rows with the broadcast tuple and emits or aggregates the result on the fly [1905.13376].

AM-Join begins with hot-key identification. Let $\kappa_R$ and $\kappa_S$ be the sets of hot keys in $R$ and $S$. The result is decomposed as
$$
Q=
(R_{HH}\bowtie S_{HH})
\ \cup\
(R_{HC}\bowtie S_{CH})
\ \cup\
(R_{CH}\bowtie S_{HC})
\ \cup\
(R_{CC}\bowtie S_{CC}),
$$
where each quadrant is disjoint by construction [2209.08475]. 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 [2209.08475].

In the NSA-based formulation, a binary join such as $R(x,y)\bowtie S(y,z)$ is decomposed into dictionary construction, a nested semijoin Lookup, and a later Expand:
$$
\mu_{\{z\}}\bigl(R\ \ltimes_y\ \Gamma_{y\to\{z\}}(S)\bigr).
$$
The dictionary $D=\Gamma_{y\to\{z\}}(S)$ maps each $y$ to the bag of matching $z$ values, Lookup attaches the corresponding nested bag to each tuple of $R$, and Expand unnests it into flat output tuples [2411.04042]. 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 [2411.04042].

## 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) [1905.13376]. The described configuration has $U=64$ PMUs and $64$ PCUs arranged, for example, in an $8\times 8$ layout, an on-chip total scratchpad of $M=16\,\text{MB}$, SIMD PCUs with $L=16$ lanes and peak $12.3$ TFLOPS, a static high-bandwidth network supporting unicast, multicast, and broadcast, and off-chip DDR3 DRAM at $49$ GB/s read/write [1905.13376]. The memory hierarchy is
$$
\text{DRAM} \leftrightarrow \text{Scratch-pad (PMU)} \leftrightarrow \text{PCU registers/SIMD lanes}.
$$

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 [1905.13376]. During build, tuples of $R_i$ are streamed into $U$ disjoint per-PMU hash tables using $h(b)$. During probe, the $S_j$ tuples stream in interleaved with build-table writes, collision handling in hardware is parallelized across PCUs, and each broadcast $T_j$ tuple simultaneously probes the build tables for both $R$ and $S$ matches through a 2-level hash lookup [1905.13376]. 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 $R$ partition, while the inner probe loops run freely [1905.13376].

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 [1905.13376]. 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 [2209.08475]. 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, $\Theta(\ell^2)$, and the cost of one Tree-Join splitter stage followed by processing of the resulting sublists. If a key has joined-list sizes $\ell_R$ and $\ell_S$, with effective size $\ell=\sqrt{\ell_R\ell_S}$, then in the worst case a key is classified hot when
$$
\ell^2 > (1+\lambda + \ell^{2/3}/n)\cdot \ell^{4/3},
$$
where $\lambda\ge 0$ is the ratio of network cost to local I/O cost and $n$ is the number of executors [2209.08475]. A simple sufficient threshold independent of $n$ is
$$
\ell > (1+\lambda)^{3/2}.
$$

When a hot key is split, Tree-Join chooses a chunking exponent $p$ such that the splitter cost $\Theta(\ell^{2-p})$ matches the downstream executor cost $\Theta(\ell^{2p})$, giving $p=2/3$. The resulting number of sub-lists per side is
$$
\delta(\ell)=\ell^{1/3},
$$
and each sub-list has size
$$
\ell'=\ell^{2/3}.
$$
After $t$ split rounds, each inherited sublist has size $\ell_{\max}^{(2/3)^t}$, and splitting stops once the hot threshold is no longer exceeded; the number of iterations satisfies
$$
t < O(\log\log \ell_{\max}).
$$
These are the formal basis for the claim that only a doubly logarithmic number of split rounds is needed [2209.08475].

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 $\kappa_R\cap\kappa_S$, then "unravels" the hot keys into an augmented-key index of sub-list pairs before Tree-JoinBasic proceeds [2209.08475]. 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 [2209.08475].

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 [2209.08475].

## 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
$$
C_{\sf multiway}=|R|+|S|+\frac{|R|\times |T|}{M}.
$$
The interpretation given is that each $R$ and $S$ tuple is loaded exactly once, while each $T$ tuple is reread once per top-level $R$ partition, namely $P=|R|/M$ times [1905.13376]. For a cascaded binary plan that first computes $I=R\bowtie S$ with $|I|\le |R||S|/d$, where $d$ is the join-key distinct count, the approximate cost is
$$
C_{\sf binary}\approx
\left(|R|+|S|+\frac{|R||S|}{d}\right)
+
\left(|T|+\frac{|R||S|}{d}\times \frac{|T|}{M}\right).
$$
The paper notes that when $I\gg M$, the second term incurs $I/M$ passes over DRAM, so the cost balloons [1905.13376].

For cyclic three-way joins where keys are pairwise shared, the cost is
$$
C_{\sf cyclic}=|R|+H|S|+G|T|,\qquad HG=\frac{|R|}{M},
$$
with optimal choice
$$
H=\sqrt{\frac{|R||T|}{M|S|}},
$$
yielding
$$
C_{\sf cyclic}^{*}=|R|+2\sqrt{\frac{|R||S||T|}{M}}.
$$
These formulas formalize the dependence of multistage join cost on top-level partitioning and on-chip capacity [1905.13376].

AM-Join presents a different cost comparison. For a hot key processed on a single executor, the local I/O cost is $\Theta(\ell^2)$. In Tree-Join, the splitter emits $\delta^2$ sub-pairs at cost $\Theta(\ell^{4/3})$, network cost is $\lambda\ell^{4/3}$, and each downstream executor also performs $\Theta(\ell^{4/3})$ local I/O, so the bottleneck becomes $\Theta((1+\lambda)\ell^{4/3})$ rather than $\Theta(\ell^2)$ [2209.08475]. 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 $S_{\small}$:
  $$
  \Theta(|S_{CH}|\cdot m_S \cdot (1+\lambda\log_{\lambda+1}n))
  $$
- shuffle time for $R_{\large}$:
  $$
  \Theta(|R_{HC}|\cdot m_R\cdot (1+\lambda)).
  $$
The hot-key collection phase itself has cost
$$
\Delta_{\sf getHotKeys}
=
\frac{|R|\cdot m_R}{n}
+
\kappa_{\max}\cdot m_b\cdot \lambda\log n
$$
[2209.08475].

In the NSA formulation, the cost model is stated in terms of three convex, monotone unit-cost functions: $c_{\text{build}}(n)$ for building a hash map on $n$ keys, $c_{\text{probe}}(m,n)$ for $m$ probes into a map of size $n$, and $c_{\text{gen}}(r)$ for emitting a column of $r$ output tuples [2411.04042]. A traditional flat hash join costs
$$
c_{\text{build}}(|S|)
+
c_{\text{probe}}(|R|,|\text{distinct}(S.\text{joinKeys})|)
+
|R\bowtie S|\cdot c_{\text{gen}}(|\text{distinct}(R\bowtie S)|).
$$
In a 2-phase NSA plan, each Lookup costs $c_{\text{probe}}(|in|,|map|)$, each build costs $c_{\text{build}}(\cdot)$, and the final Flatten costs $\text{outputSize}\cdot c_{\text{gen}}(\cdot)$ [2411.04042]. The paper’s two central guarantees are robustness, meaning asymptotic $O(\text{inputSize}+\text{outputSize})$ time for a 2-phase NSA plan on an acyclic join, and no regret, meaning that for every well-behaved binary plan $P$, its SYA rewriting incurs no more build, probe, or generation cost than $P$, pointwise on every database [2411.04042].

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 $64$ PMUs/PCUs, DRAM bandwidth of $49$ GB/s, and on-chip memory of $16$ MB, the CPU baseline is single-threaded Postgres on Xeon E5-2697 [1905.13376]. For a cascaded binary self-join over three copies of a "friends" relation $F$ of size $N$ and distinct count $d$, the accelerator achieves $200\times$ to $600\times$ speedup over the CPU, with the best results when $d\ll N$; if the intermediate spills, the bottleneck shifts from DRAM streaming to SSD at $700$ MB/s [1905.13376]. For a single linear three-way self-join, the implementation achieves up to $45\times$ speedup relative to cascaded binary joins on the same hardware for $N\approx 2\times 10^8$ and $d\approx 7\times 10^5$, with the largest speedups when $d$ is small or DRAM bandwidth is limited [1905.13376]. In a star-join where the large fact table $S$ has size $N$ and two dimension tables $R$ and $T$ each have size $K\ll N$ with $2K\le M$, $R$ and $T$ fit entirely on chip and the single 3-way join yields up to $11\times$ speedup over two binary joins [1905.13376].

AM-Join reports that on two relations with more than $10^9$ and $10^7$ records, record size $10^2$ or $10^3$ bytes, and Zipf skew parameter $\alpha\in[0,1]$, AM-Join and Tree-Join scale nearly linearly in $1/(\#\text{executors})$ up to $n=1000$ and tolerate $\alpha$ up to $0.9$--$1.0$, whereas single-round Hash-Join and Spark3-Join break down above $\alpha\approx 0.5$ [2209.08475]. The paper also reports a same-attribute self-join speedup of approximately $40\%$ from pruning duplicate pairs in Tree-JoinBasic, and on real Uber trip data totaling $17$ TB, only AM-Join and Tree-Join complete a same-attribute self-join in $2$ Hrs while others time out at $48$ Hrs [2209.08475].

SYA is implemented in Apache DataFusion using Rust and Arrow, with binary-plan input orders taken from DuckDB’s optimizer, and evaluated on $1{,}849$ acyclic queries drawn from JOB, STATS-CEB, and a CE graph workload [2411.04042]. The paper states that all runs exclude planning, I/O, aggregation, and focus only on join time. On JOB, SYA improves $92\%$ of queries with speedups up to $35\times$; on STATS-CEB, $96.5\%$ with speedups up to $33\times$; on CE, $87.8\%$ with speedups up to $188\times$ [2411.04042]. The abstract states that across the full suite, SYA improves performance for $85.3\%$ of the queries with speedups up to $62.5\times$, while the detailed summary later states that SYA outperforms traditional binary hash join on $88.7\%$ of $1{,}849$ queries [2411.04042]. 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 [1905.13376], [2411.04042]. 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 [1905.13376], [2209.08475], [2411.04042]. 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 [2209.08475], [2411.04042].

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.

Source: https://www.emergentmind.com/topics/multi-stage-hash-join