Why ClickHouse is so fast: the question a practitioner actually asks

A team migrating an OLAP workload off Redshift, BigQuery, Elasticsearch or a row store sees ClickHouse return a GROUP BY over a few billion rows in sub-second time and asks the obvious question: what is it doing that our previous engine was not? The short answer to why ClickHouse is so fast is that it does dramatically less work per query. The long answer — and the useful one, because it tells you how to design tables that keep it fast — is below.

This article follows the query’s own path: first the I/O phase (which parts, granules and columns are read, and how many bytes come off disk), then the CPU phase (how those bytes are turned into a result). Each design choice is tied to the catalog table or EXPLAIN variant that lets you measure it, because a claim you cannot measure is not an engineering claim.

Every MergeTree part stores each column in its own compressed stream. In Wide parts that is a <column>.bin file plus a <column>.cmrk2 (or .mrk2) mark file per column; in Compact parts (small parts, below min_bytes_for_wide_part, default 10 MiB since 22.x) all columns share a single data.bin but remain separately addressable via marks. A query that references 4 of 300 columns opens 4 streams. The other 296 are never touched — no I/O, no decompression, no cache pollution.

The consequence for a row store is not subtle. PostgreSQL, MySQL/InnoDB and SQL Server keep a row’s columns together in a page; reading one column means reading the page that holds all of them. On a 300-column, 100-billion-row table the row store reads ~75× more bytes than ClickHouse for the same 4-column aggregate before any index or compression effect is counted. Redshift and Snowflake are also columnar, so this particular advantage is against OLTP engines and Elasticsearch/Lucene doc-values rather than against other warehouses — the differences against those come from choices 2–7.

Two properties make ClickHouse’s column layout more efficient than “columnar” alone implies: values in a column stream are fixed-width for numeric and date types with no per-value headers, and Nullable, LowCardinality, Array and Tuple types are decomposed into separate sub-streams (null-map, dictionary, offsets, elements) so that even a nested column is read only to the depth the query needs.

Skip indexes are not row-level indexes. Each one stores a small summary per block of GRANULARITY n granules — a min/max pair, a set of distinct values, or a Bloom filter — and at query time ClickHouse uses that summary to prove that a block cannot contain matching rows and drops it.

They never produce false negatives; they simply cannot prune when the summary is inconclusive, which is why the value of a skip index is entirely a function of how the column’s values are physically clustered along the sort order. Used well, they extend why ClickHouse is so fast from the sort key to the next two or three most-filtered columns.

Partition pruning sits one level above: the PARTITION BY expression’s min/max is stored per part (minmax_<col>.idx), so a predicate on the partition key eliminates whole parts before any granule-level work. Monthly partitions on event_date plus event_date in the sort key is the common pattern; do not partition finer than needed — thousands of small parts hurt merges and the “too many parts” guard far more than pruning helps.

Add a skip index and materialize it for existing parts

-- Bloom filter for point lookups on a high-cardinality column
-- that is NOT a prefix of ORDER BY.
ALTER TABLE events
    ADD INDEX idx_customer_bf customer_id
    TYPE bloom_filter(0.01)
    GRANULARITY 4;

-- Builds the index for parts that already exist. This is a
-- mutation: it rewrites index files, not data. Test on staging;
-- watch system.mutations for is_done = 1 before relying on it.
ALTER TABLE events MATERIALIZE INDEX idx_customer_bf;

Verify it: size and pruning

SELECT
    name,
    type_full,
    granularity,
    formatReadableSize(data_compressed_bytes) AS on_disk,
    marks
FROM system.data_skipping_indices
WHERE database = currentDatabase()
  AND table = 'events';

-- Then confirm the Skip entry in:
EXPLAIN indexes = 1
SELECT count() FROM events WHERE customer_id = 8812734;

If Granules after the Skip line are not materially lower than before it, the index is costing write amplification for nothing — drop it. A useful production rule: a skip index that prunes less than ~70% of granules for its target queries is usually the wrong tool; a projection with a different ORDER BY (choice 7) is the right one.

Related reading: how data-skipping indexes are implemented, parts, partitions and primary-index design, and the official skip-index guide.

Once bytes are in memory, ClickHouse never reconstructs rows. The execution engine follows the MonetDB/X100 model: every operator (FilterTransform, ExpressionTransform, AggregatingTransform, MergeSortingTransform …) consumes and produces a chunk — a set of columns holding up to max_block_size rows (default 65,409). A filter evaluates region = 'eu-west-1' over 65 K values in a tight loop on a contiguous array, producing a UInt8 mask; the mask is then applied to the other columns with a single pass. This is the CPU-side half of why ClickHouse is so fast.

Three CPU-level effects follow. Instruction cache: the loop body is a few hundred bytes executed tens of thousands of times, so branch prediction and the µop cache work as designed. SIMD: hot kernels (arithmetic, comparisons, hashing, memcpy-like moves, aggregate state updates) are compiled in multiple variants and selected at runtime by CPU dispatch — SSE 4.2, AVX2, AVX-512 — without a separate build. Virtual-call amortisation: the interpreter overhead of choosing which typed implementation to run is paid once per chunk, not once per row.

Since 22.x ClickHouse can additionally JIT-compile chains of scalar expressions and aggregate-state updates with LLVM (compile_expressions, compile_aggregate_expressions, on by default after a query shape has been seen min_count_to_compile_expression times), fusing adjacent operators into one native loop. Its benefit is real on expression-heavy queries and negligible on I/O-bound ones — measure with ProfileEvents['CompileFunction'] before crediting it.

See the processors: EXPLAIN PIPELINE

EXPLAIN PIPELINE graph = 0
SELECT customer_id, sum(amount)
FROM events
WHERE event_date BETWEEN '2026-08-01' AND '2026-08-07'
GROUP BY customer_id
SETTINGS max_threads = 8;

Read it bottom-up: MergeTreeSelect × 8 (one stream per thread over disjoint mark ranges) → ExpressionTransform × 8 → AggregatingTransform × 8 (each thread builds its own hash table) → Resize → a merging step that combines the eight partial states. The × N multipliers are the intra-node parallelism of choice 6 made visible. Our step-by-step guide to reading EXPLAIN PIPELINE covers what each processor means.

Aggregating materialized view pattern

CREATE TABLE events_daily_by_customer
(
    event_date   Date,
    customer_id  UInt64,
    total_amount AggregateFunction(sum, Decimal(18, 4)),
    p95_latency  AggregateFunction(quantile(0.95), UInt32),
    event_count  AggregateFunction(count)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, customer_id);

CREATE MATERIALIZED VIEW mv_events_daily_by_customer
TO events_daily_by_customer
AS
SELECT
    event_date,
    customer_id,
    sumState(amount)                 AS total_amount,
    quantileState(0.95)(latency_ms)  AS p95_latency,
    countState()                     AS event_count
FROM events
GROUP BY event_date, customer_id;

-- Query with -Merge combinators; GROUP BY must match target ORDER BY.
SELECT
    customer_id,
    sumMerge(total_amount)          AS total_amount,
    quantileMerge(0.95)(p95_latency) AS p95_latency
FROM events_daily_by_customer
WHERE event_date BETWEEN '2026-08-01' AND '2026-08-07'
GROUP BY customer_id
ORDER BY total_amount DESC
LIMIT 100;

Projection for a non-key filter column

ALTER TABLE events
    ADD PROJECTION prj_events_by_customer
    (
        SELECT *
        ORDER BY (customer_id, event_date)
    );

-- Builds the projection for existing parts (a mutation; stage it).
ALTER TABLE events MATERIALIZE PROJECTION prj_events_by_customer;

-- Confirm the optimiser chose it: look for the projection
-- name in the ReadFromMergeTree description.
EXPLAIN indexes = 1
SELECT count() FROM events WHERE customer_id = 8812734;

-- Or after the fact, from the query log:
SELECT query_id, projections, read_rows, read_bytes
FROM system.query_log
WHERE type = 'QueryFinish'
  AND has(tables, concat(currentDatabase(), '.events'))
ORDER BY event_time DESC
LIMIT 20;

Projections are incompatible with lightweight deletes/updates and cannot carry a WHERE or JOIN in their definition (26.3). Where those constraints bite, a materialized view into a second table is the fallback. Our projections guide and chained materialized views post go further; the official reference is the incremental materialized view and projections documentation.