ORDER BY, PARTITION BY and how queries skip data

Two clauses in CREATE TABLE decide most of a MergeTree table’s query performance, and they are often confused:

• ORDER BY defines the sorting key (and, unless you say otherwise, the primary key). Data inside each part is sorted by it, and a sparse index over it lets queries skip granules.
• PARTITION BY splits the table into partitions: separate sets of parts, one per partition value. Its main purposes are data management (drop or move a month in one cheap operation, TTL) and, as a side effect, skipping whole partitions.

The demo table:

CREATE TABLE metrics (ts DateTime, sensor_id UInt32, metric LowCardinality(String), value Float64)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (metric, sensor_id, ts);

INSERT INTO metrics
SELECT toDateTime('2026-01-01 00:00:00') + number * 20, number % 100,
       ['temp','humidity','pressure','co2'][1 + intDiv(number, 7) % 4], rand() % 1000 / 10
FROM numbers(1000000);

A million rows, 20 seconds apart, land in eight monthly partitions:

SELECT partition, name, rows FROM system.parts WHERE table = 'metrics' AND active ORDER BY partition;

┌─partition─┬─name─────────┬───rows─┐
│ 202601    │ 202601_1_1_0 │ 133920 │
│ 202602    │ 202602_2_2_0 │ 120960 │
│ 202603    │ 202603_3_3_0 │ 133920 │
│ 202604    │ 202604_4_4_0 │ 129600 │
│ 202605    │ 202605_5_5_0 │ 133920 │
│ 202606    │ 202606_6_6_0 │ 129600 │
│ 202607    │ 202607_7_7_0 │ 133920 │
│ 202608    │ 202608_8_8_0 │  84160 │
└───────────┴──────────────┴────────┘

How a query narrows down the data

EXPLAIN indexes = 1 shows three pruning stages, in this order:

1. Partition Min-Max: each part keeps the min and max of the partition key’s source columns; parts outside the filter range are dropped.
2. Partition: the filter is evaluated against the partition key expression itself.
3. PrimaryKey: the sparse index selects granules (blocks of 8192 rows) inside the surviving parts.

To avoid the count-from-metadata shortcut, use sum(value) in the examples.

Filter on the partition column

EXPLAIN indexes = 1 SELECT sum(value) FROM metrics
WHERE ts >= '2026-02-01' AND ts < '2026-03-01';

Partition Min-Max   Parts: 1/8    Granules: 15/121
Partition           Parts: 1/1    Granules: 15/15
PrimaryKey          Parts: 1/1    Granules: 15/15

Seven of eight parts are dismissed without opening them. (The EXPLAIN output in this article is condensed to the lines of interest.)

Filter on the key columns

EXPLAIN indexes = 1 SELECT sum(value) FROM metrics WHERE metric = 'temp' AND sensor_id = 7;

Partition Min-Max   Parts: 8/8    Granules: 121/121
Partition           Parts: 8/8    Granules: 121/121
PrimaryKey          Parts: 8/8    Granules: 8/121   (binary search)

No partition filter, so all eight parts are visited, but within each the primary index narrows the read to 8 of 121 granules. Now the same without the leading key column:

EXPLAIN indexes = 1 SELECT sum(value) FROM metrics WHERE sensor_id = 7;

PrimaryKey          Parts: 8/8    Granules: 38/121   (generic exclusion search)

Filtering on the second key column alone still helps, but far less (38 of 121 granules), and the algorithm changes from binary search to “generic exclusion search”. The rule of thumb behind ORDER BY design follows directly:

• Put the columns you filter by most often first, and generally low-cardinality columns before high-cardinality ones. The index is most effective on a prefix of the key, and a low-cardinality prefix makes the later columns sort well within each prefix value, which also helps compression.
• A time column typically goes last in the key when you also filter on other columns (as here), or first when you almost always filter by time range and have no leading dimension.

Combining both

EXPLAIN indexes = 1 SELECT sum(value) FROM metrics
WHERE metric = 'co2' AND ts >= '2026-02-01' AND ts < '2026-03-01';

Partition Min-Max   Parts: 1/8    Granules: 15/121
Partition           Parts: 1/1    Granules: 15/15
PrimaryKey          Parts: 1/1    Granules: 4/15

Partition pruning removes seven months, then the primary key finds the co2 block inside February.

What the pruner understands

The partition pruner is smarter than a literal comparison. It evaluates the filter against the partition key and can see through monotonic functions. All of these prune to a single month on the demo table:

WHERE ts >= '2026-02-01' AND ts < '2026-03-01'     -- direct range
WHERE toStartOfMonth(ts) = '2026-02-01'            -- monotonic function of the column
WHERE toDate(ts) = '2026-02-14'                    -- finer function still maps into one month
WHERE toYYYYMM(ts) IN (202601, 202602)             -- same expression as the partition key: 2 of 8 parts
WHERE _partition_id = '202601'                     -- the virtual column (1 partition)

A function that is not monotonic over the column cannot prune anything:

WHERE toDayOfWeek(ts) = 1                          -- Parts: 8/8, nothing pruned

Even a chain of conversions works when each step is monotonic. For a partition key like toYYYYMM(toDate(intDiv(d, 1000))) on an integer millisecond column, a filter on toDateTime(intDiv(d, 1000), 'UTC') < toDateTime('2020-10-01 00:00:00', 'UTC') still prunes partitions before the query runs. The ClickHouse test suite for partition pruning is a good place to see how far this goes.

Two virtual columns are available for inspecting this: _partition_id (the partition’s identifier string) and _partition_value (the partition expression value as a tuple), alongside _part.

Choosing a partition key

• Partition by something coarse, like a month or a day, so that you get tens to hundreds of partitions, not thousands.
• The partition key is not a substitute for the primary key. Prefer the primary key for fast filtering; use partitions for lifecycle: DROP PARTITION, DETACH, TTL moves, backups of a period.
• Too many partitions hurt: every insert creates a part per partition it touches, merges happen only within a partition, and startup gets slower. ClickHouse guards against it:

CREATE TABLE bad (ts DateTime, sensor_id UInt32, value Float64)
ENGINE = MergeTree PARTITION BY sensor_id ORDER BY ts;
INSERT INTO bad SELECT now(), number, 1 FROM numbers(1000);
-- Code: 252. Too many partitions for single INSERT block (more than 100).
-- The limit is controlled by 'max_partitions_per_insert_block' setting.

• If you only ever query recent data and never drop old data in bulk, you may not need PARTITION BY at all. A good ORDER BY plus a TTL is simpler.
• Partition key expressions should be cheap and deterministic over a column that is always filtered if you want pruning to happen.

Checklist

1. Run EXPLAIN indexes = 1 on your real queries, not guesses.
2. Look at the Granules: x/y of the PrimaryKey stage. If it is close to y/y, your filter is not served by the sort key.
3. Look at Parts: x/y of the Partition Min-Max stage. If it is y/y while you expected pruning, the filter is probably not a monotonic function of the partition column.
4. When the filter column is not in the key, consider a projection or a skip index.