Choosing Broadcast Hash Join vs Sort-Merge Join in Apache Spark 3.x

The decision

When Spark joins two tables the optimizer chooses between a broadcast hash join and a sort-merge join. The choice changes shuffle, memory use and latency. The decision hinges on the size of the smaller side, available executor memory, data skew and whether statistics are available for Adaptive Query Execution.

Constraints and options

Constraint	Broadcast hash join	Sort-merge join
Size requirement	One side must fit in executor memory. The common default threshold is spark.sql.autoBroadcastJoinThreshold, often 10 MB, configurable.	No size limit; default for larger inputs.
Shuffle	No shuffle. The small side is broadcast to all executors.	Requires shuffle and sort of both sides.
Memory pressure	Replicates the small side to every executor and driver. Increases memory use.	Can spill to disk if memory is insufficient.
Skew	Unaffected by skew on the large side. The broadcast side itself must fit in memory.	Degraded by skew. AQE can split large partitions in Spark 3.x.
Explicit control	/*+ BROADCAST(table) */ hint can request broadcast.	Sort-merge can be requested with a join hint where supported. Hint syntax varies by version and distribution.
AQE interaction	AQE can pick broadcast at runtime if statistics show a small side.	AQE is the default fallback; may switch to broadcast at runtime.

Version note as of 2026-10-20: AQE exists in Spark 3.x. Enabled-by-default status and skew handling defaults vary by distribution and patch version. Verify in your environment.

Trade-offs in practice

When broadcast is attractive

• Small dimension joining a large fact. Broadcast avoids shuffling the large side.
• Repeated joins to the same small table in a pipeline.
• Low latency where shuffle stages dominate.

Risk: The default threshold is often too low for wide rows. Estimate serialized size and keep the threshold well below executor memory. If the broadcast side exceeds memory, the job can fail with out-of-memory. No automatic fallback to sort-merge.

When sort-merge is safer

• Both sides are large or the small side exceeds the threshold.
• Memory is constrained and spill is preferable to replication.
• Skew is present and AQE skew handling is available.

Risk: Shuffle read/write dominates runtime. High spill indicates insufficient memory for sort buffers.

Concrete validation workflow

Use EXPLAIN and Spark UI on representative data. Do not rely on defaults alone.

Step 1: Inspect planned strategy

spark.sql("""
  EXPLAIN
  SELECT *
  FROM large_table l
  JOIN small_table s ON l.key = s.key
""").show(truncate=False)

Run on driver with read access. Look for BroadcastHashJoin or SortMergeJoin in the physical plan. BroadcastExchange before the join indicates broadcast is planned.

Step 2: Test a broadcast hint

spark.sql("""
  EXPLAIN
  SELECT /*+ BROADCAST(s) */ *
  FROM large_table l
  JOIN small_table s ON l.key = s.key
""").show(truncate=False)

Hints are not validated at parse time. A misspelled alias is silently ignored. Always verify in EXPLAIN.

Step 3: Execute and check runtime

result = spark.sql("""
  SELECT /*+ BROADCAST(s) */ l.*, s.dim_attr
  FROM large_table l
  JOIN small_table s ON l.key = s.key
""")
result.count()

Open Spark UI SQL tab for the query id. Check actual join type, shuffle read/write, memory spill, and broadcast time. AQE may change the plan after EXPLAIN, so runtime is authoritative.

Step 4: Compare strategies

Run the same join with broadcast hint and without hint, or with a sort-merge hint where supported. Record from Spark UI:

Metric	Broadcast	Sort-merge
Total execution time	_____	_____
Shuffle Read	_____	_____
Shuffle Spill Disk	_____	_____
Peak Executor Memory	_____	_____

Choose the strategy with acceptable memory and spill for your workload.

Configuration to review

• spark.sql.autoBroadcastJoinThreshold: controls automatic broadcast. Raise for wide rows; -1 disables broadcast.
• spark.sql.adaptive.enabled: enables AQE runtime re-optimization. Verify default in your distribution.
• spark.sql.adaptive.skewJoin.enabled: enables skew handling for sort-merge.
• spark.sql.statistics.fallBackToHdfs.enabled: can help optimizer for external tables without metastore stats.
• spark.driver.maxResultSize: must accommodate broadcast collect size.

Limitations

• Statistics freshness matters for AQE. Run ANALYZE TABLE for managed tables or enable fallback stats for external tables.
• Broadcast size estimation is approximate. Test with real data.
• Hint syntax and defaults vary by Spark version and distribution. Check documentation for your release.
• Spark < 3.0 has no AQE; join strategy is fixed at planning time.

Bottom line

With AQE available, start with optimizer defaults and validate with EXPLAIN plus Spark UI metrics on real data. Use broadcast when the small side truly fits in executor memory with headroom. Use sort-merge when size, skew or memory constraints make broadcast risky.