ClickHouse JOINs Aren't Slow Anymore (You're Reading 2020's Docs)

Aman Puri — Tue, 12 May 2026 18:41:27 +0000

ClickHouse JOIN performance was rebuilt between 2022 and 2026. The "avoid JOINs in ClickHouse" advice from 2020 is still circulating:

"ClickHouse can't do JOINs."

"Denormalize everything."

"Only hash join is supported."

"JOINs OOM on anything bigger than RAM."

All four were accurate in 2020. None of them are accurate today.

In 2020, ClickHouse had one join algorithm, no disk spilling, no cost-based optimizer, and join order followed query syntax. If the right table did not fit in memory, the query crashed.

Between 2022 and early 2026, the join subsystem was rebuilt. Six algorithms ship by default. The optimizer reorders joins automatically using column statistics. Runtime bloom filters prune fact tables at the storage scan. Grace hash spills to disk instead of OOMing. None of it is behind feature flags.

Here is what shipped.

How Many JOIN Algorithms Does ClickHouse Support?

By the end of 2022 ClickHouse had five join algorithms. Today it has six, and the engine selects automatically:

Hash join. The classic.
Parallel hash join. Default since v24.12 (PR #70788). Near-linear scaling across CPU cores. Zero configuration.
Grace hash join. Disk-spilling for joins that exceed memory (PR #38191). GA in v24.3. Closes the most upvoted join issue in ClickHouse history, open since June 2020.
Full sorting merge. Memory-bounded sort-merge (PR #35796). Faster than hash on pre-sorted data.
Direct join. O(1) memory lookups against dictionaries and EmbeddedRocksDB. No hash table construction.
Paste join. Positional joins.

"ClickHouse only has hash join" has been false since November 2022.

ClickHouse JOIN OOM Crashes: Grace Hash and Disk Spilling

Grace hash join partitions both inputs into buckets via a secondary hash, processes one bucket pair at a time, and spills inactive buckets to disk. Full sorting merge bounds memory by the sort buffer rather than right-side cardinality.

Before PR #38191, a join where the right table exceeded memory crashed with OOM. After it, the join completes. It just takes longer.

Does ClickHouse Have a Query Optimizer for JOINs?

"ClickHouse has no query optimizer" was true. It has not been since 2024.

Equivalence-Set Predicate Pushdown (180× Speedup)

Equivalence-set predicate pushdown (PR #61216) is the highest-impact join optimization of the four-year arc. When tables join on t1.id = t2.id, a filter WHERE t1.id = 5 is logically equivalent to t2.id = 5. The optimizer recognizes this and pushes the filter to both sides before the join executes.

Benchmarks: up to 180× speedup.

Extended in 2026 (PR #96596) to propagate across chains of INNER JOINs using a Disjoint Set Union to track transitive equalities. Filter on t1.id, get it pushed to t2, t3, and beyond.

Automatic OUTER to INNER Conversion

Automatic OUTER to INNER conversion (PR #62907) recognizes when post-join filters make outer semantics redundant. A LEFT JOIN ... WHERE right_col IS NOT NULL becomes an INNER JOIN. One benchmarked query went from 32s to 0.006s.

Converting to INNER first unlocks predicate pushdown and reordering that are structurally impossible for OUTER JOINs.

Right-Side and OR-Condition Pushdown

Two further pushdown extensions:

Right-side predicate pushdown (PR #50532) with 27× improvement on applicable workloads.
OR-condition pushdown (PR #84735). Previously only AND conditions could be pushed.

No query hints required.

Cost-Based JOIN Reordering in ClickHouse (v25.9+)

Automatic join order optimization landed in v25.9.

Greedy JOIN Reordering with Column Statistics

PR #86822 introduced global join reordering using a greedy algorithm with column statistics. For queries joining three or more tables, the optimizer evaluates estimated cardinalities and picks the order that minimizes intermediate result sizes.

TPC-H SF100: 1,450× speedup, 25× memory reduction versus syntax-order execution.

DPsize Dynamic Programming JOIN Reordering

v25.12 added a DPsize dynamic programming algorithm (PR #91002) for exhaustive search on complex queries, with greedy fallback above the complexity threshold. PostgreSQL and Oracle planners work the same way.

Automatic Statistics Collection

Statistics collection went automatic in v25.10 (PR #89332). Runtime hash table sizes from previous executions feed back into future plans (PR #93912).

Hand-tuned join order is obsolete.

Runtime Bloom Filters for ClickHouse Star Schema JOINs

Star and snowflake schemas were historically where ClickHouse joins hurt. The fact table is large. The dimensions are small. The fact table got scanned in full, then most rows were discarded.

PR #89314, default since v25.10, builds bloom filters from build-side join keys during hash table construction and pushes them to the probe-side scan. Non-matching rows are discarded at the storage layer before reaching the join.

v25.10 release blog: 2.1× overall query speedup, 7× memory reduction on star-schema workloads. Filters can be pushed into PREWHERE (PR #95838) for further efficiency. An adaptive mechanism disables them at runtime when they stop filtering enough rows.

Does ClickHouse Support Correlated Subqueries?

True until April 2025. PR #76078 shipped correlated subquery decorrelation. EXISTS, scalar, and projection-list subqueries auto-convert into joins.

Beta with default enablement landed in August 2025 (PR #85107), closing issue #6697, open since 2019.

Migration from PostgreSQL or Snowflake no longer requires manual rewriting of correlated subqueries.

ClickHouse JOIN Limitations and Trade-offs in 2026

Four caveats remain:

Flat tables are still faster for sub-10ms p99 dashboards. Denormalization wins on extreme latency targets.
The optimizer depends on statistics. Stale or missing stats degrade plan quality. Monitor system.statistics.
Correlated subqueries are beta. Common patterns work. Edge cases need explicit join rewrites.
Grace hash trades speed for completion. Constant spilling means the data model needs adjustment, not the join algorithm.

When Should You Use JOINs in ClickHouse?

Workload	Verdict
Star/snowflake analytics	Yes. Runtime bloom filters target this exact pattern.
Multi-table reporting	Yes. The optimizer reorders.
Joins larger than RAM	Yes. Grace hash and full sorting merge guarantee completion.
Real-time dimension enrichment	Yes. Direct join is O(1) memory.
ASOF time-series joins	Yes. Full sorting merge variant is 2× faster, 2× less memory.
Sub-10ms p99 on complex joins	Conditional. Denormalize if the storage budget allows.
10+ table joins with no stats	Enable `allow_statistics_optimize` (default in v25.10+).

Responding to "avoid JOINs in ClickHouse" in 2026

Check the version.

Anyone benchmarking against ClickHouse 23.x, or quoting blog posts written before parallel hash join became default, is not evaluating ClickHouse 2026. They are evaluating a system that no longer exists.

The commit history is public. 50+ pull requests. Six algorithms. Cost-based optimization. Runtime filtering. Automatic algorithm selection, build-side selection, join reordering, predicate pushdown. None behind flags. All default.

Test it on the workload. That is the only benchmark that matters.

ClickHouse JOIN FAQ

Are ClickHouse JOINs production-ready in 2026?

Yes. Six algorithms ship by default, the optimizer reorders joins using column statistics, runtime bloom filters prune fact tables at the storage scan, and grace hash spills to disk instead of OOMing. None of it requires configuration.

How many JOIN algorithms does ClickHouse support?

Six: hash, parallel hash (default), grace hash (disk-spilling), full sorting merge, direct (O(1) key-value), and paste. The engine selects automatically.

Does ClickHouse still crash with OOM on large JOINs?

No. Grace hash join (GA in v24.3) partitions inputs and spills to disk. Full sorting merge bounds memory by the sort buffer. Both algorithms guarantee completion regardless of right-side size.

Should I denormalize tables in ClickHouse instead of using JOINs?

Conditional. For sub-10ms p99 dashboard queries, flat tables remain faster. For analytical workloads where query readability and storage efficiency matter, normalized star and snowflake schemas with JOINs perform well after the 2024 to 2026 optimizer work.

DEV Community: Aman Puri