TopolearnThe sort key is the single most important performance decision for a ClickHouse table.
A MergeTree table's data is sorted by its ORDER BY key and cut into granules of 8,192 rows (index_granularity). The primary index stores the key values of only the first row of each granule. That makes it sparse: a billion-row table needs about 122,000 index entries, small enough to sit in memory.
When a query filters on key columns, ClickHouse binary-searches this index to find which granules can contain matching rows and reads only those. Filtering on a column that is not a prefix of the key gives far less pruning, because those values are scattered across all granules.
Designing the key:
- Lead with the columns that appear in most
WHEREclauses, often a tenant or category, then time:ORDER BY (tenant_id, event_type, ts). - Order the key columns roughly by ascending cardinality. Low-cardinality first gives long runs of equal values, which helps pruning on later columns and compresses much better.
- Avoid leading with a near-unique column like a UUID unless point lookups on it are the main use.
- A query can only use the key efficiently from the left: with
(a, b, c), filters onaora, bprune well; a filter oncalone barely does.
EXPLAIN indexes = 1 shows how many granules each query selects, which is the quickest way to check a key design.
Example
CREATE TABLE logs (tenant UInt16, level LowCardinality(String), ts DateTime, msg String)
ENGINE = MergeTree ORDER BY (tenant, level, ts);
INSERT INTO logs SELECT number % 100, ['info','warn','error'][number % 3 + 1],
now() - number, 'm' FROM numbers(2000000);
-- Filter on the key prefix: only a few granules are read
EXPLAIN indexes = 1 SELECT count() FROM logs WHERE tenant = 7 AND level = 'error';
-- PrimaryKey Keys: tenant, level ... Granules: 3/245
-- Filter on ts alone (third key column): no pruning
EXPLAIN indexes = 1 SELECT count() FROM logs WHERE ts > now() - 60;
-- PrimaryKey Keys: ts ... Granules: 245/245Remember
- The primary index has one entry per 8,192-row granule
- Filters prune well only on a left prefix of ORDER BY
- Order key columns from low to high cardinality, frequent filters first
Watch out for
- Putting a timestamp or UUID first in ORDER BY, which makes filters on the other key columns scan everything.