Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 35 ยท Performance and Indexing

The Sparse Primary Index

Learn how the ClickHouse sparse primary index works: granules, marks, ORDER BY key choice, why column order matters and how to check pruning with EXPLAIN.

ClickHouse is fast on huge tables mostly because of what it does not read. For every query it tries to skip as many rows as possible, using the table's primary index. That index looks nothing like the B-tree on a row-store database, and understanding it explains most ClickHouse performance advice: how to choose ORDER BY, why column order matters and why point lookups are not its strength.

The ClickHouse snippets here are read-only because the server cannot run on this site. The runnable parts model granule skipping with plain SQL, so you can count what would be read.

Sorted data in parts

A MergeTree table stores each insert as a part, with rows sorted by the table's ORDER BY key (the sorting key). By default the primary key equals the sorting key.

SQL
CREATE TABLE hits
(
    site     LowCardinality(String),
    ts       DateTime,
    user_id  UInt64,
    url      String
)
ENGINE = MergeTree
ORDER BY (site, ts);

Inside a part the data is divided into granules: consecutive blocks of rows, 8192 by default (index_granularity). A granule is the smallest unit ClickHouse ever reads from disk.

The primary index does not contain one entry per row. It holds one entry per granule, the key value of the granule's first row. That is why it is called sparse: a table with a billion rows has only about 122,000 index entries (a billion divided by 8192), small enough to sit in memory always.

flowchart TB
    I["Primary index in memory: one key per granule"] --> G1["Granule 0: rows 0-8191"]
    I --> G2["Granule 1: rows 8192-16383"]
    I --> G3["Granule 2: rows 16384-24575"]
    I --> G4["..."]

The index points at granules ("marks"); the engine reads only the granules whose key range can match.

How a query uses it

For a condition on the primary key columns, ClickHouse binary-searches the in-memory index, finds the ranges of granules that might contain matching rows, and reads only those granules from the column files. Everything else is skipped without touching the disk.

SQL
-- Prunes well: a prefix of the sorting key is constrained
SELECT count() FROM hits WHERE site = 'shop.example' AND ts >= '2025-03-01';

Because the index stores one key per 8192 rows, it cannot find a single row exactly. It finds a granule, then ClickHouse scans the 8192 rows inside it. That is cheap for analytics and a poor fit for "fetch one row by id" workloads. Those are better served by a transactional store or by accepting a small granule scan.

Column order is everything

Sorting is lexicographic: first by site, then by ts inside each site. This has big consequences:

Query filter Pruning
site = 'a' excellent: one contiguous range
site = 'a' AND ts > ... excellent: a narrower range
ts > ... only weak: the time values repeat in every site's block, so ranges are scattered and most granules may match
url = '...' no help: url is not in the key

Guidelines for choosing ORDER BY:

  1. Put columns you filter by most often first.
  2. Put low-cardinality columns before high-cardinality ones. A low-cardinality prefix creates long contiguous runs, improving both pruning and compression, because equal values sit next to each other.
  3. Use a time column late or truncated when queries filter by site first (for example toStartOfDay(ts)) to keep key prefixes useful.
  4. Do not stuff too many columns into the key. Each added column increases index memory and slows inserts' sorting a little, with diminishing returns.

If you must search by a column in the second position without the first, ClickHouse can still use a binary-search-like "generic exclusion" scan, but it is far less effective when the first column has high cardinality.

PRIMARY KEY can be shorter than ORDER BY

You may declare a primary key that is a prefix of the sorting key. The index then holds only the prefix columns (less memory), while rows are still fully sorted by the long key (good for compression and for engines that collapse rows by key):

SQL
ENGINE = MergeTree
PRIMARY KEY (site, toStartOfHour(ts))
ORDER BY (site, toStartOfHour(ts), user_id);

Checking whether the index is used

EXPLAIN indexes = 1 shows how many parts and granules survive pruning:

SQL
EXPLAIN indexes = 1
SELECT count() FROM hits WHERE site = 'shop.example' AND ts >= '2025-03-01';

Look for the PrimaryKey section with lines like Granules: 12/118000. The first number is how many granules will be read; the second is the total. A ratio near 1 means the key does not help this query.

Stand-in: counting granules to read

Below, a table is sorted by (site, ts) and divided into pretend granules of 100 rows. The query counts how many granules hold at least one matching row for two different filters. This mirrors the pruning above (the stand-in finds exact matches, whereas ClickHouse prunes by key ranges, which is slightly coarser).

SQL Stand-in: filtering by the first key column vs the second

Press Run (or Ctrl + Enter) to see the result here.

Filtering by the first key column reads a small contiguous slice; filtering only by the second key column touches granules spread across every site.

SQL Stand-in: swap the key order and compare

Press Run (or Ctrl + Enter) to see the result here.

Which filter you run most often should decide the first key column.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How many entries does the sparse primary index hold?

    The index stores the key of the first row of each granule, so it stays small enough to live in memory.
  2. 2. A table has ORDER BY (site, ts). Which filter prunes worst?

    Time values repeat inside each site's block, so a condition on ts alone cannot narrow to a contiguous range.
  3. 3. Which rule helps choose the order of ORDER BY columns?

    Filtered, low-cardinality columns first give long contiguous runs, which improves both pruning and compression.
  4. 4. What does EXPLAIN indexes = 1 help you see?

    The PrimaryKey section reports selected versus total granules and parts, showing how effective pruning is.

Summary

  • Data is stored sorted by the key and split into granules of 8192 rows.
  • The primary index is sparse: one entry per granule, always in memory.
  • Queries read only granules whose key range can match; column order decides how well that works.
  • Put filtered, low-cardinality columns first; a shorter PRIMARY KEY saves index memory.
  • Use EXPLAIN indexes = 1 to verify pruning.

Next: Data Skipping Indexes