Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Why is my primary key not used? How can I check?

View as MarkdownEdit this pageReport an issueSet up docs MCP

Checking your Primary Key

Users may see cases where their query is slower than expected, in the belief they are ordering or filtering by a primary key. In this article we show how users can confirm the key is used, highlighting common reasons its not.

Create table

Consider the following simple table:

CREATE TABLE logs
(
    `code` LowCardinality(String),
    `timestamp` DateTime64(3)
)
ENGINE = MergeTree
ORDER BY (code, toUnixTimestamp(timestamp))

Note how our ordering key includes toUnixTimestamp(timestamp) as the second entry.

Populate data

Populate this table with 100m rows:

INSERT INTO logs SELECT
 ['200', '404', '502', '403'][toInt32(randBinomial(4, 0.1)) + 1] AS code,
    now() + toIntervalMinute(number) AS timestamp
FROM numbers(100000000)

0 rows in set. Elapsed: 15.845 sec. Processed 100.00 million rows, 800.00 MB (6.31 million rows/s., 50.49 MB/s.)

SELECT count()
FROM logs

┌

Basic filtering

If we filter by code we can see the number of rows scanned in the output. - 49.15 thousand. Notice how this is a subset of the total 100m rows.

SELECT count() AS c
FROM logs
WHERE code = '200'

┌

Furthermore, we can confirm the use of the index with the EXPLAIN indexes=1 clause:

EXPLAIN indexes = 1
SELECT count() AS c
FROM logs
WHERE code = '200'

┌

Notice how the number of granules scanned 8012 is a fraction of the total 12209. The section higlighted below, confirms use of the primary key code.

PrimaryKey
  Keys: 
   code

Granules are the unit of data processing in ClickHouse, with each typically holding 8192 rows. For further details on granules and how they are filtered we recommend reading this guide.

Multi-key filtering

Suppose we filter, by code and timestamp:

SELECT count()
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')

┌

In this case both ordering keys are used to filter rows, resulting in the need to only read 87 granules.

Using keys in sorting

ClickHouse can also exploit ordering keys for efficient sorting. Specifically,

When the optimize_read_in_order setting is enabled (by default), the ClickHouse server uses the table index and reads the data in order of the ORDER BY key. This allows us to avoid reading all data in case of specified LIMIT. So, queries on big data with small limits are processed faster. See here and here for further details.

This, however, requires alignment of the keys used.

For example, consider this query:

SELECT *
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')
ORDER BY timestamp ASC
LIMIT 10

┌

We can confirm that the optimization has not been exploited here by using EXPLAIN pipeline:

EXPLAIN PIPELINE
SELECT *
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')
ORDER BY timestamp ASC
LIMIT 10

┌

The line MergeTreeSelect(pool: ReadPool, algorithm: Thread) here does not indicate the use of the optimization but rather a standard read. This is caused by our table ordering key using toUnixTimestamp(Timestamp) NOT timestamp. Rectifying this mismatch addresses the issue:

EXPLAIN PIPELINE
SELECT *
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')
ORDER BY toUnixTimestamp(timestamp) ASC
LIMIT 10

┌
Navigation