ClickHouse Query Performance Tuning: Sort Keys, Indexes and Projections

Last updated: October 2026

ClickHouse is fast enough that a slow query usually means it is reading too much data, and the fix is rarely a bigger server. A query that scans every granule of a billion-row table is slow on any hardware, while the same query restricted by the primary index can return in milliseconds.

This guide follows the order we use on client systems: measure what each query reads, fix the sort key where possible, then consider skip indexes and projections, and finally check how data is inserted. It reflects ClickHouse 26.x as of October 2026, including optimisations that are now on by default. For the wider platform choice, see our ClickHouse vs Snowflake comparison.

Measure what each query reads

Every finished query is recorded in system.query_log with its duration, the rows and bytes it read, and its memory use. The normalized_query_hash column is the same for queries that differ only in their literal values, so a dashboard's repeated queries group together. Ranking by bytes read finds the queries that cost the most I/O, which is usually where tuning pays off first.

-- Queries from the last day, grouped by shape, most bytes read first
SELECT normalized_query_hash,
       any(query)                          AS sample_query,
       count()                             AS runs,
       round(avg(query_duration_ms))       AS avg_ms,
       formatReadableSize(sum(read_bytes)) AS total_read,
       sum(read_rows)                      AS rows_read,
       formatReadableSize(max(memory_usage)) AS peak_memory
FROM system.query_log
WHERE type = 'QueryFinish'
  AND is_initial_query = 1
  AND event_time > now() - INTERVAL 1 DAY
GROUP BY normalized_query_hash
ORDER BY sum(read_bytes) DESC
LIMIT 20;

Then run EXPLAIN indexes = 1 on the worst query. For the primary key, the output shows the key columns the condition could use and how many parts and granules were selected out of the total. A small fraction of granules means the index did its job. A ratio close to the total means a near-full scan, and a table created with ORDER BY tuple() shows no index section at all.

EXPLAIN indexes = 1
SELECT count()
FROM events
WHERE event_type = 'checkout' AND tenant_id = 42;

-- Relevant part of the output (illustrative figures)
--   PrimaryKey
--     Keys:
--       event_type
--       tenant_id
--     Parts: 6/40
--     Granules: 118/24310

The sort key does most of the work

ClickHouse stores each part sorted by the table's ORDER BY and keeps a sparse primary index with one entry per granule, which is 8,192 rows by default. A query that filters on the leading sort-key columns can skip most granules. A query that filters only on other columns reads them all, however selective its filter is.

ClickHouse's guidance for choosing the key is practical. Put the columns your queries filter on most often into the key, where four or five columns are usually enough, and when those columns differ greatly in cardinality, order them from lowest to highest. A separately declared PRIMARY KEY must be a prefix of the ORDER BY. The ordering key has to be defined when the table is created and cannot be added afterwards, so a wrong choice means creating a new table and copying the data across.

CREATE TABLE events
(
    event_time  DateTime,
    event_type  LowCardinality(String),
    tenant_id   UInt32,
    user_id     UInt64,
    error_code  UInt16,
    amount      Decimal(18, 2),
    payload     String
)
ENGINE = MergeTree
PARTITION BY toStartOfMonth(event_time)
ORDER BY (event_type, tenant_id, toDate(event_time), user_id);

In this example most dashboard queries filter on event type and tenant, so those come first, with event type ahead because it has far fewer distinct values. The date expression serves time-range filters within a tenant, and user_id at the end keeps each user's events together within a day, which helps compression.

Partitions are for managing data

ClickHouse's documentation describes partitioning as primarily a data management technique and not a query optimisation tool. Partitions let you drop, move or archive data in a single metadata operation, and they work with TTL and tiered storage. A monthly partition, as in the example above, is a common choice, and ClickHouse recommends a partition key with fewer than 100 to 1,000 distinct values.

Partitioning by day or by customer creates many small parts, and parts in different partitions are never merged with each other. When a single partition holds more than 1,000 active parts, ClickHouse slows inserts down, and above 3,000 it rejects them with a Too many parts error. Those thresholds are the defaults of parts_to_delay_insert and parts_to_throw_insert.

Skip indexes and the text index

A data skipping index stores a small summary for each block of granules, and its GRANULARITY sets the block size: with the default 8,192-row granules, GRANULARITY 4 gives one summary per 32,768 rows. The main types are minmax for loosely sorted values, set for columns with few distinct values per block, bloom_filter for equality checks on high-cardinality values, and text for full-text search. The older ngrambf_v1 and tokenbf_v1 types are now marked as deprecated.

Skip indexes help less often than people expect. If a value appears even once in a block, the whole block has to be read, so an index on a column with no relationship to the sort key skips very little, and every insert pays to build it. ClickHouse's documentation suggests trying the sort key, projections and materialized views first. Good candidates are rare values that matter, such as error codes that appear in a tiny fraction of rows.

-- Rare error codes: a set index lets ClickHouse skip blocks without the value
ALTER TABLE events ADD INDEX idx_error_code error_code TYPE set(100) GRANULARITY 4;
ALTER TABLE events MATERIALIZE INDEX idx_error_code;

-- Full-text search on log text (text index, GA from ClickHouse 26.2)
ALTER TABLE events ADD INDEX idx_payload payload TYPE text(tokenizer = splitByNonAlpha);
ALTER TABLE events MATERIALIZE INDEX idx_payload;

SELECT count() FROM events WHERE hasToken(payload, 'timeout');

A new index applies only to data inserted after it is created, so MATERIALIZE INDEX is needed to build it for existing parts. To confirm that an index is used, run the query with send_logs_level set to 'trace' and look for the line reporting how many granules it dropped. The text index has been generally available since ClickHouse 26.2, and ClickHouse strongly recommends 26.2 or later for using it in production. Note that hasToken matches whole tokens only, so a search for 'timeout' does not match 'timeouts'.

Projections for a second sort order

A projection is a hidden copy of the table, or of an aggregate of it, stored with a different sort order and kept in sync automatically. When a query would read less data from a projection, ClickHouse uses it without the query changing. An ordering projection serves queries that filter on columns outside the main key, and an aggregating projection, defined with GROUP BY, pre-computes totals.

-- Since 25.5 a projection can store only the sort key and the row position,
-- so it works like an index on user_id instead of a full second copy
ALTER TABLE events ADD PROJECTION prj_user_lookup
(
    SELECT _part_offset ORDER BY user_id
);
ALTER TABLE events MATERIALIZE PROJECTION prj_user_lookup;

Projections have costs and limits. Every insert writes the data twice, a full ordering projection stores a second copy of the table, and a projection definition cannot contain joins or a WHERE clause. Lightweight updates and deletes are not supported on tables with projections, and by default a lightweight DELETE on such a table throws an error, controlled by the lightweight_mutation_projection_mode setting. EXPLAIN projections = 1 and the projections column in system.query_log show whether a query used one.

ClickHouse describes materialized views as more effective for maintaining aggregates, and they suit totals that several dashboards share. Our guide to ClickHouse materialized views for rollups covers that pattern.

PREWHERE and the optimisations already on

ClickHouse moves suitable WHERE conditions into PREWHERE automatically, because optimize_move_to_prewhere is enabled by default. PREWHERE reads the filter columns first and loads the other columns only for granules that still contain matching rows, which reduces the data read without changing the result. Writing PREWHERE by hand is rarely needed, and EXPLAIN PLAN actions = 1 shows which conditions were moved.

Two newer optimisations are worth knowing about, because they affect what a timing comparison means. Lazy materialization, introduced in 25.4 and on by default, delays reading columns until the query needs them, which helps Top N queries most. The query condition cache, introduced in 25.3, remembers for each filter and granule whether any row matched, so a repeated filter can skip granules without evaluating them again. It was enabled on the 26.9 server we checked, and since it works best on data that rarely changes, we recommend confirming the use_query_condition_cache setting on your version before comparing a cold run with a warm one.

Inserts and part counts

Each insert creates a new part on disk, and background merges combine parts later. ClickHouse recommends inserting in batches of at least 1,000 rows, ideally 10,000 to 100,000, and roughly one insert query per second. Where the client cannot batch, asynchronous inserts buffer rows on the server, and ClickHouse strongly recommends async_insert=1 with wait_for_async_insert=1, describing wait_for_async_insert=0 as very risky.

INSERT INTO events (event_time, event_type, tenant_id, user_id, error_code, amount, payload)
SETTINGS async_insert = 1, wait_for_async_insert = 1
VALUES (now(), 'checkout', 42, 1001, 0, 59.90, '');

Asynchronous inserts do not fix a partition key with too many values, which still produces too many parts. If system.parts shows partitions with hundreds of active parts, the partition key and the insert batch size are the first things to check.