---
title: "How ClickHouse became fast at joins"
description: "Over two years, ClickHouse focused on making joins faster for analytical SQL workloads, moving them from a performance gap to a first-class strength. The first year established the foundation with parallel hash joins, local automatic join reordering, filter pushdown, and execution improvements, producing a 4.4× speedup on the TPC-H SF100 join-heavy workload by version 25.4, compared with 22.4. The second year made optimizations effective by default, adding correlated-subquery support, lazy column replication, runtime filters, and statistics-based join reordering. Correlated queries can now run directly, while compact representations avoid copying repeated values and runtime filtering skips rows before hash-table lookups. Across the same benchmark and hardware, these changes delivered another 6× speedup, for a total 26× improvement; one six-table query fell from 3903.7 seconds to 2.7 while peak memory dropped from 99.1 GiB to 3.9 GiB."
---

# How ClickHouse became fast at joins

[Clickhouse](https://yomu.fyi/company/clickhouse) · Jun 3, 2026

**Type:** Explainer

## Summary

Over two years, ClickHouse focused on making joins faster for analytical SQL workloads, moving them from a performance gap to a first-class strength. The first year established the foundation with parallel hash joins, local automatic join reordering, filter pushdown, and execution improvements, producing a 4.4× speedup on the TPC-H SF100 join-heavy workload by version 25.4, compared with 22.4. The second year made optimizations effective by default, adding correlated-subquery support, lazy column replication, runtime filters, and statistics-based join reordering. Correlated queries can now run directly, while compact representations avoid copying repeated values and runtime filtering skips rows before hash-table lookups. Across the same benchmark and hardware, these changes delivered another 6× speedup, for a total 26× improvement; one six-table query fell from 3903.7 seconds to 2.7 while peak memory dropped from 99.1 GiB to 3.9 GiB.

## Context

ClickHouse had been improving join performance for two years because join-heavy analytical SQL was slower than desired and some important queries using correlated subqueries could not run directly. The stated goal was for users to avoid rewriting queries, manually tuning join orders, or enabling internal optimizations themselves.

## Approach / What changed

The work combined foundational execution changes with default-enabled optimizer and execution improvements: parallel hash joins, local and statistics-based join reordering, filter pushdown, correlated-subquery decorrelation, lazy column replication, and runtime filters. The post compares these changes on TPC-H SF100 using the same AWS EC2 m6i.8xlarge hardware.

## Takeaways

- Correlated subquery support lets ClickHouse run queries across SELECT, WHERE, and HAVING, including TPC-H queries that previously required manual rewrites or exclusion.
- Lazy column replication represents repeated joined values through a compact index and dictionary structure, avoiding physical copying when later operations can consume the compact form.
- Statistics-based join reordering reduced one six-table TPC-H query from 3903.7 seconds to 2.7 seconds and lowered peak memory from 99.1 GiB to 3.9 GiB.

**Tags:** [Performance](https://yomu.fyi/topic/performance)

- Source: [Clickhouse](https://clickhouse.com/blog/clickhouse-fast-joins)
- Source URL: https://clickhouse.com/blog/clickhouse-fast-joins
- Ingested by Yomu: 2026-08-29T01:34:07.736Z

[Read original post](https://clickhouse.com/blog/clickhouse-fast-joins)