Merge joins look elegant on paper. In production, correlating spans with logs almost always means hash join wins — here's the cost model that decides.

When a HelixQL query joins traces to logs on trace_id, the planner has two strategies available: sorted merge join and hash join. In more than 90% of production correlation queries, it picks hash join. The reason is not academic — it's about how trace_id values actually arrive on disk.

A merge join is only cheap when both sides are already sorted on the join key. Ours almost never are.

Sort order is a lie at query time

Spans and log lines are both stored primarily sorted by ingest time, not by trace_id. A merge join needs both inputs ordered on trace_id, which means an explicit sort step — O(n log n) — before the merge even starts. For a 10-minute window with 2.4M spans and 8.1M log lines, that sort alone costs more than the join.

The hash join path

Instead, the planner builds a hash table on the smaller side (usually the span set, since spans are lower cardinality than log lines in most services) keyed by trace_id, then streams the larger side through probes. Build cost is O(n) and probe cost is O(m), with no sort required on either side.

from traces
| where service == 'checkout-api' and duration > 800ms
| join kind=inner (from logs | where level == 'error') on trace_id
| project trace_id, span.name, log.message, duration

When merge join wins anyway

There is one path where the planner flips: if a query already filters traces down to a pre-sorted exemplar set (for example, joining against the 200 trace_ids attached to a metric bucket's exemplar list), the build side is small enough — typically under 500 rows — that a merge join against an index-ordered log scan avoids materializing a hash table at all. We measured this crossover at roughly 1,200 rows on the build side; below that, merge join's lower constant factor wins by 8-15ms.

What we measured

Across our internal benchmark suite (47 representative correlation queries against a 30-day retention cluster), hash join averaged 340ms versus 890ms for a forced merge join on the same queries — largely the cost of that upfront sort. The planner's cost model weighs estimated build-side cardinality, existing sort order metadata on each chunk, and available memory budget per query (capped at 256MB by default) before choosing.

  • Hash join wins when inputs aren't pre-sorted on the join key, which is the common case.
  • Merge join wins only when one side is already small and ordered, roughly under 1,200 rows.
  • The planner's choice is cost-based, not fixed, and re-evaluated per query.