ClickHouse Partition Pruning vs Primary Key: 3 Proven Gates

ClickHouse partition pruning and primary-key pruning are two different mechanisms that happen to show up on the same summary line of the server log, which is why so many people treat them as one thing. They are not. Partition pruning drops whole parts by evaluating the partition expression value stored in partition.dat. The primary key drops mark ranges inside a part by walking primary.cidx.

There is a third gate sitting between them, the per-part min-max index on the source columns of the partition expression, and that third gate is the reason a WHERE event_date BETWEEN ... filter can prune parts inside a month even when the table is partitioned by toYYYYMM(event_date) and event_date is nowhere in the sort key.

Everything below was measured on ClickHouse 26.7.2.1 with a single MergeTree table, merges stopped so the part layout stays deterministic. The DDL, the load script and the queries are at the end, so you can rerun it on your own version and check whether the trace lines match.

Table of Contents

The three gates a query passes before ClickHouse reads a single mark

When ReadFromMergeTree plans a read, it walks the list of active parts and applies three independent filters in order. Each one reads a different file, operates on a different key, and prunes at a different granularity. The first two work at part level: a part either survives or is discarded without opening any column data. The third works inside the surviving parts, at the level of index granules.

Figure 1. ClickHouse partition pruning, min-max pruning and primary-key pruning as three gates, each backed by its own file in the part directory.

The distinction that matters most in practice is what each gate is keyed on. The partition gate sees only the value of the partition expression, so under PARTITION BY toYYYYMM(event_date) it can reason about months and nothing finer. The min-max gate is keyed on the raw columns that feed that expression, so it holds the true minimum and maximum event_date for every part. The primary-key gate is keyed on the ORDER BY prefix and knows nothing about either. Three files on disk, three sections in EXPLAIN, three lines in the log. Most of what gets described as ClickHouse partition pruning in incident write-ups is actually a mix of the first two gates, and the primary key is a separate conversation.

Lab table: monthly partitions, tenant-first sort key, nine parts

The table is deliberately shaped so that the partition expression and the sort key share no columns. That is the configuration where ClickHouse partition pruning and primary-key pruning are easiest to tell apart, and it is also a very common production shape for multi-tenant event data.

CREATE TABLE lab.events
(
    event_date  Date,
    event_time  DateTime,
    tenant_id   UInt32,
    user_id     UInt64,
    event_type  LowCardinality(String),
    payload     String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_time)
SETTINGS index_granularity = 8192;

-- ClickHouse partition pruning lab: keep the part layout stable for the experiment (lab only; never leave this on in production)
SYSTEM STOP MERGES lab.events;

Three months of data, three inserts per month, one million rows each. Each insert covers a distinct ten-day window so every part has a non-overlapping event_date range inside its month. With merges stopped, that gives exactly nine parts.

-- repeated for month IN ('2026-06','2026-07','2026-08') and (d0,d1) IN ((1,10),(11,20),(21,28))
INSERT INTO lab.events
SELECT
    toDate('{month}-01') + intDiv(number, 100000) % ({d1}-{d0}+1) + {d0}-1 AS event_date,
    toDateTime(event_date) + (number % 86400)                             AS event_time,
    number % 200                                                          AS tenant_id,
    cityHash64(number)                                                    AS user_id,
    ['view','click','purchase'][number % 3 + 1]                           AS event_type,
    randomPrintableASCII(32)                                              AS payload
FROM numbers(1000000);

SELECT partition, name, rows, min_date, max_date, marks
FROM system.parts
WHERE database = 'lab' AND table = 'events' AND active
ORDER BY name;

   ┌─partition─┬─name─────────┬────rows─┬───min_date─┬───max_date─┬─marks─┐
1. │ 202606    │ 202606_1_1_0 │ 1000000 │ 2026-06-01 │ 2026-06-10 │   124 │
2. │ 202606    │ 202606_2_2_0 │ 1000000 │ 2026-06-11 │ 2026-06-20 │   124 │
3. │ 202606    │ 202606_3_3_0 │ 1000000 │ 2026-06-21 │ 2026-06-28 │   124 │
4. │ 202607    │ 202607_4_4_0 │ 1000000 │ 2026-07-01 │ 2026-07-10 │   124 │
5. │ 202607    │ 202607_5_5_0 │ 1000000 │ 2026-07-11 │ 2026-07-20 │   124 │
6. │ 202607    │ 202607_6_6_0 │ 1000000 │ 2026-07-21 │ 2026-07-28 │   124 │
7. │ 202608    │ 202608_7_7_0 │ 1000000 │ 2026-08-01 │ 2026-08-10 │   124 │
8. │ 202608    │ 202608_8_8_0 │ 1000000 │ 2026-08-11 │ 2026-08-20 │   124 │
9. │ 202608    │ 202608_9_9_0 │ 1000000 │ 2026-08-21 │ 2026-08-28 │   124 │
   └───────────┴──────────────┴─────────┴────────────┴────────────┴───────┘

Note the min_date / max_date columns, the part of ClickHouse partition pruning nobody looks at. system.parts is showing you the contents of the min-max index, and it is already at day precision inside a monthly partition. That is the whole story of this post in one catalog query; the rest is proving it with the planner and the log.

ClickHouse partition pruning in EXPLAIN indexes = 1: three sections, not one

Two queries, each designed to trigger exactly one mechanism. Query A filters on a whole month of event_date. Query B filters on tenant_id, the first sort-key column. Output trimmed to the ReadFromMergeTree node.

EXPLAIN indexes = 1
SELECT count() FROM lab.events
WHERE event_date >= '2026-08-01' AND event_date < '2026-09-01';

ReadFromMergeTree (lab.events)
  Read type: Default
  Parts: 3 | Granules: 369
  Indexes:
    Min-Max
      Keys:
        event_date
      Condition: and((event_date in (-Inf, 20696]), (event_date in [20666, +Inf)))
      Parts: 3/9
      Granules: 369/1107
    Partition
      Keys:
        toYYYYMM(event_date)
      Condition: and((toYYYYMM(event_date) in (-Inf, 202609]), (toYYYYMM(event_date) in [202608, +Inf)))
      Parts: 3/3
      Granules: 369/369
    PrimaryKey
      Condition: true          -- the sort key contributed nothing
      Parts: 3/3
      Granules: 369/369
    Ranges: 3

Three things are worth pausing on. The planner prints Min-Max before Partition, and it is the min-max gate that did the pruning (9 parts down to 3); by the time the partition gate runs there is nothing left for it to remove, so it reports 3/3. The PrimaryKey condition is literally true, which is ClickHouse telling you the WHERE clause could not be expressed against (tenant_id, event_time) at all. And the numbers on the date bounds (20666, 20696) are days since epoch, the internal representation of Date, while the partition condition is expressed in YYYYMM integers. Different keys, different value spaces. That alone should settle whether ClickHouse partition pruning and the primary key are the same feature.

-- no ClickHouse partition pruning possible here: tenant_id is not a partition source column
EXPLAIN indexes = 1
SELECT count() FROM lab.events WHERE tenant_id = 42;

ReadFromMergeTree (lab.events)
  Read type: Default
  Parts: 9 | Granules: 18
  Indexes:
    Min-Max
      Condition: true
      Parts: 9/9
      Granules: 1107/1107
    Partition
      Condition: true
      Parts: 9/9
      Granules: 1107/1107
    PrimaryKey
      Keys:
        tenant_id
      Condition: (tenant_id in [42, 42])
      Parts: 9/9
      Granules: 18/1107        -- 2 granules per part, every part still open
      Search Algorithm: binary search
    Ranges: 9

The mirror image, and the cleanest demonstration that primary-key pruning is not partition pruning. Both part-level gates pass everything through (Condition: true, 9/9), and the primary key then discards 98.4% of the granules inside each of the nine parts. No part was pruned; every part was opened and then mostly skipped. If you are watching system.query_log, the difference shows up as ProfileEvents['SelectedParts'] staying at 9 while ProfileEvents['SelectedMarks'] collapses to 18. Partition pruning moves the first number; primary-key pruning moves the second.

ClickHouse partition pruning in the server log: the same thing in three lines

The log is where most people first meet these mechanisms, usually while tailing clickhouse-server.log during an incident. Set send_logs_level = 'trace' in clickhouse-client and the SelectExecutor emits a Key condition line, a MinMax index condition line, and the familiar Selected N/M parts by partition key ... summary. Here are the same two queries; server prefixes trimmed.

-- Query A: ClickHouse partition pruning path. WHERE event_date >= '2026-08-01' AND event_date < '2026-09-01'
<Debug> lab.events (SelectExecutor): Key condition: unknown, unknown, and
<Debug> lab.events (SelectExecutor): MinMax index condition: (column 0 in [20666, +Inf)), (column 0 in (-Inf, 20696]), and
<Debug> lab.events (SelectExecutor): Selected 3/9 parts by partition key, 3 parts by primary key, 369/369 marks by primary key, 369 marks to read from 3 ranges

-- Query B: primary key only, no ClickHouse partition pruning. WHERE tenant_id = 42
<Debug> lab.events (SelectExecutor): Key condition: (column 0 in [42, 42])
<Debug> lab.events (SelectExecutor): MinMax index condition: unknown
<Debug> lab.events (SelectExecutor): Selected 9/9 parts by partition key, 9 parts by primary key, 18/1107 marks by primary key, 18 marks to read from 9 ranges

Read Key condition as the primary-key gate’s view of your WHERE clause and MinMax index condition as the part-level gate’s view. The word unknown is the tell: it means that atom of the condition tree references a column the gate does not index, so the atom is treated as always-true for pruning purposes. In Query A the primary-key gate sees two unknowns joined by and, which reduces to “no pruning possible”. In Query B the min-max gate sees a single unknown for the same reason.

The summary line is where ClickHouse partition pruning and primary-key pruning get conflated. Selected 3/9 parts by partition key is what most people quote as evidence of ClickHouse partition pruning, and it folds the partition gate and the min-max gate into one number; the log does not separate them. Then 3 parts by primary key is a second, independent part count (a part is dropped here only if the key condition excludes every one of its mark ranges), and 369/369 marks by primary key is the granule-level result. Four numbers, two mechanisms. When someone says “the query pruned to 3 parts”, ask them which of the two counts they are reading.

Partition pruning below partition granularity: min-max on a non-partition column

Now the ClickHouse partition pruning case that people get wrong in schema reviews. The partition key is toYYYYMM(event_date). A ten-day filter on event_date cannot be answered by the partition gate, because a month is the finest thing it can distinguish. The sort key is (tenant_id, event_time), so the primary key cannot help either. The intuitive prediction is “all three parts of August get read”, because partition pruning cannot go below a month. The measured result is two.

EXPLAIN indexes = 1
SELECT count() FROM lab.events
WHERE event_date >= '2026-08-15' AND event_date < '2026-08-25';

ReadFromMergeTree (lab.events)
  Parts: 2 | Granules: 246
  Indexes:
    Min-Max
      Keys:
        event_date
      Condition: and((event_date in (-Inf, 20689]), (event_date in [20680, +Inf)))
      Parts: 2/9               -- 202608_7_7_0 (08-01..08-10) dropped here
      Granules: 246/1107
    Partition
      Keys:
        toYYYYMM(event_date)
      Condition: and((toYYYYMM(event_date) in (-Inf, 202608]), (toYYYYMM(event_date) in [202608, +Inf)))
      Parts: 2/2
      Granules: 246/246
    PrimaryKey
      Condition: true
      Parts: 2/2
      Granules: 246/246
    Ranges: 2

<Debug> lab.events (SelectExecutor): Key condition: unknown, unknown, and
<Debug> lab.events (SelectExecutor): MinMax index condition: (column 0 in [20680, +Inf)), (column 0 in (-Inf, 20689]), and
<Debug> lab.events (SelectExecutor): Selected 2/9 parts by partition key, 2 parts by primary key, 246/246 marks by primary key, 246 marks to read from 2 ranges

Figure 2. ClickHouse partition pruning stops at the month; the min-max index on the source column drops the part that lies entirely before August 15.

The mechanism is simple once you know where to look. When a part is written, MergeTree records the minimum and maximum of every column that appears in the partition expression, and writes them to minmax_<column>.idx in the part directory. It is the raw column, not the expression result. So even though the partition key collapses event_date to a month, the part still carries the exact day range it contains, and the planner uses it.

The gate is per-part, so its effectiveness depends entirely on how narrow each part’s date range is, which is a function of ingestion order and merge history rather than of anything in the DDL. Here is the part directory, showing all three index files side by side:

$ # the three files behind ClickHouse partition pruning, min-max and the primary key
$ ls store/15b/15b2beca-.../202608_7_7_0/
checksums.txt  columns.txt  count.txt  event_date.bin  event_date.cmrk2  ...
minmax_event_date.idx      -- gate 2: raw min/max of the source column
partition.dat              -- gate 1: the value 202608
primary.cidx               -- gate 3: sparse index over (tenant_id, event_time)
...

This is also why a composite partition key such as PARTITION BY (toYYYYMM(event_date), tenant_id % 16) gives you a free tenant_id min-max per part without adding it to the sort key: the min-max index tracks every source column of the expression, not just the first. Whether that is a good idea for your part count is a separate question, and the answer is usually no beyond a handful of buckets.

The mirror case: a sort-key column that does not prune

To close the loop on ClickHouse partition pruning versus the sort key, filter on event_time, which is in the sort key but is not the partition source column. The min-max gate is blind to it (unknown), and because event_time is the second key column with an unconstrained high-cardinality prefix in front of it, the primary key falls back to generic exclusion search and ends up excluding nothing.

EXPLAIN indexes = 1
SELECT count() FROM lab.events
WHERE event_time >= '2026-08-15 00:00:00' AND event_time < '2026-08-25 00:00:00';

ReadFromMergeTree (lab.events)
  Parts: 9 | Granules: 1107
  Indexes:
    Min-Max
      Condition: true
      Parts: 9/9
    Partition
      Condition: true
      Parts: 9/9
    PrimaryKey
      Keys:
        event_time
      Condition: and((event_time in (-Inf, 1787615999]), (event_time in [1786752000, +Inf)))
      Parts: 9/9
      Granules: 1107/1107      -- every granule survives
      Search Algorithm: generic exclusion search
    Ranges: 9

<Debug> lab.events (SelectExecutor): Key condition: (column 1 in [1786752000, +Inf)), (column 1 in (-Inf, 1787615999]), and
<Debug> lab.events (SelectExecutor): MinMax index condition: unknown, unknown, and
<Debug> lab.events (SelectExecutor): Selected 9/9 parts by partition key, 9 parts by primary key, 1107/1107 marks by primary key, 1107 marks to read from 9 ranges

Same ten-day window as the previous query, but expressed against the wrong column for ClickHouse partition pruning: a full scan of 9 million rows instead of 1.2 million. The two columns are semantically identical (event_date is just toDate(event_time)), and the difference is purely which gate each one is wired into. If your application layer generates time filters, this is the thing to check first. A time filter on the partition source column gets part-level pruning for free; the same filter on a sort-key column that is not a prefix gets nothing.

Make the engine tell you which gate fired

ClickHouse ships two settings that turn “did it prune?” into a hard error, and the error text is the clearest statement in the whole system that these are separate mechanisms. force_index_by_date demands that either the partition expression or the min-max index be usable; force_primary_key demands that the sort key be usable. They are independent, and you can combine them in a CI query-lint step.

-- assert ClickHouse partition pruning (or min-max) was used
SELECT count() FROM lab.events
WHERE event_time >= '2026-08-15 00:00:00'
SETTINGS force_index_by_date = 1;

Code: 277. DB::Exception: Neither MinMax index by columns (event_date) nor partition expr is used
and setting 'force_index_by_date' is set. (INDEX_NOT_USED)

SELECT count() FROM lab.events
WHERE event_date >= '2026-08-15'
SETTINGS force_primary_key = 1;

Code: 277. DB::Exception: Primary key (tenant_id, event_time) is not used
and setting 'force_primary_key' is set. (INDEX_NOT_USED)

Note the wording of the first error: “neither MinMax index by columns (event_date) nor partition expr”. The engine itself names two things and lists the min-max source column explicitly. The day-range query on event_date passes force_index_by_date = 1 without complaint, even though it never used the partition expression, because the min-max gate satisfied it.

Partition pruning vs primary-key pruning: which filter hits which gate

WHERE clause (ClickHouse partition pruning lab)	Min-Max	Partition	PrimaryKey	Parts / marks read
`event_date` whole month	3/9	3/3	true	3 parts, 369 marks
`tenant_id = 42`	true	true	binary search	9 parts, 18 marks
`event_date` 10-day range	2/9	2/2	true	2 parts, 246 marks
`event_time` same 10 days	true	true	generic exclusion, 0 excluded	9 parts, 1107 marks
month AND `tenant_id = 42`	3/9	3/3	binary search	3 parts, 6 marks

The last row is the shape you want most queries to take, partition pruning and primary-key pruning stacked: part-level pruning from the date, granule-level pruning from the tenant, and 6 marks read out of 1107. Neither mechanism alone gets there.

What this changes in schema and query design

The first consequence is about where time belongs, because partition pruning and primary-key pruning are fed by different columns. The habit of putting event_time or event_date late in a tenant-first sort key “so that time-range queries are fast” mostly does not do what people think, as the generic-exclusion case shows. Time-range pruning on a multi-tenant table comes from the partition source column and its min-max index, and from a leading-prefix match on the sort key, not from a trailing sort-key position. Filter on the partition source column, pass the tenant as the sort-key prefix, and let the gates compose.

The second consequence is that ClickHouse partition pruning quality at the min-max level is an operational property, not a DDL property. It depends on how tight each part’s date range is. Parts written by a well-ordered streaming pipeline have narrow ranges and prune beautifully; parts produced by a large backfill, or by merges that combined a month’s worth of small parts, have wide ranges and prune poorly even though system.parts looks healthy on row counts. Watch min_date / max_date spread per part (or min_time / max_time for DateTime partition sources) as a health signal, not just part count.

The third is about how you read ClickHouse partition pruning diagnostics. When triaging a slow query against system.query_log, compare ProfileEvents['SelectedParts'] against the active part count and ProfileEvents['SelectedMarks'] against the total marks of the selected parts. The first ratio is the combined part-level gates; the second is the primary key.

A query that reads 9/9 parts but 18 marks is fine. A query that reads 2/9 parts but every mark in them is fine for a different reason. A query that reads 9/9 parts and every mark has been failed by both mechanisms, and EXPLAIN indexes = 1 will show you three Condition: true lines to prove it.

The broader ClickHouse partition pruning and part-selection discussion is in our ClickHouse partitioning pitfalls and custom partitioning keys posts; the MergeTree storage and indexing piece covers the sparse primary index itself in more depth.

Version boundaries and what was not tested

All output above is from ClickHouse 26.7.2.1, and the partition pruning behaviour described here is community MergeTree; SharedMergeTree in ClickHouse Cloud shares the same part-selection code but a different part lifecycle, so re-measure there. The three-section EXPLAIN indexes = 1 layout with Min-Max, Partition and PrimaryKey has been stable for several major versions.

The exact section names, though, and the presence of the Search Algorithm line have shifted across the 23.x–25.x line; if your output differs, check the EXPLAIN reference for your release. The Selected N/M parts by partition key ... summary line has kept its shape for years and is the safest thing to grep for across versions.

Behaviour of the generic exclusion search on trailing key columns is described in ClickHouse’s own primary index guide, and the partition-key semantics in the custom partitioning key page.

What this lab does not cover: ReplicatedMergeTree and distributed tables (the part-selection logic is identical per replica, but parallel-replica reading adds a coordination layer on top), projections (which get their own part selection), data-skipping indexes (a fourth gate, applied after the primary key, and out of scope here), and the query condition cache introduced in 25.x, which can short-circuit mark selection on repeated predicates and make SelectedMarks look better than the index alone would justify.

Merges were stopped for determinism; on a live table the min-max pruning ratio will drift as parts merge, so re-measure on your own part layout rather than assuming the 2/9 result transfers.

Reproduce it

The full lab runs in under a minute on a laptop. It was executed here through chDB (the embedded ClickHouse engine, same code as the server), but every statement is plain ClickHouse SQL and works unchanged in clickhouse-client; the log lines require SET send_logs_level = 'trace' in the client session.

-- ClickHouse partition pruning vs primary-key pruning: reproduction script (ClickHouse 26.7)
-- 1. table and nine deterministic parts (DDL and INSERT loop as shown above)
-- 2. confirm layout
SELECT partition, name, rows, min_date, max_date, marks
FROM system.parts
WHERE database = 'lab' AND table = 'events' AND active
ORDER BY name;

-- 3. planner view, one query per gate (ClickHouse partition pruning vs primary key)
EXPLAIN indexes = 1 SELECT count() FROM lab.events WHERE event_date >= '2026-08-01' AND event_date < '2026-09-01';
EXPLAIN indexes = 1 SELECT count() FROM lab.events WHERE tenant_id = 42;
EXPLAIN indexes = 1 SELECT count() FROM lab.events WHERE event_date >= '2026-08-15' AND event_date < '2026-08-25';
EXPLAIN indexes = 1 SELECT count() FROM lab.events WHERE event_time >= '2026-08-15 00:00:00' AND event_time < '2026-08-25 00:00:00';
EXPLAIN indexes = 1 SELECT count() FROM lab.events WHERE toYYYYMM(event_date) = 202608 AND tenant_id = 42;

-- 4. log view
SET send_logs_level = 'trace';
-- rerun the five SELECTs without EXPLAIN and read the SelectExecutor lines

-- 5. assertion mode: ClickHouse partition pruning vs primary key, as hard errors
SELECT count() FROM lab.events WHERE event_time >= '2026-08-15 00:00:00' SETTINGS force_index_by_date = 1;  -- expect INDEX_NOT_USED
SELECT count() FROM lab.events WHERE event_date >= '2026-08-15'            SETTINGS force_primary_key = 1;    -- expect INDEX_NOT_USED

-- 6. clean up: SYSTEM START MERGES lab.events; then DROP the lab database when done

Test ClickHouse partition pruning on your own version and your own part layout before changing any production schema; a sort-key or partition-key change on a large MergeTree table is a full rewrite, and the right answer depends on the actual query mix in your system.query_log. If you want a second pair of eyes on a partitioning and sort-key design, or a query workload that is reading far more marks than it should, that is exactly the kind of review ChistaDATA does under our ClickHouse consulting and support engagements.

ChistaDATA Inc.

Enterprise-class 24*7 ClickHouse Consultative Support and Managed Services