Your First Surprise: Why the "Wrong" Index Is Fine Here

The B-tree instinct, and why it doesn't transfer

In a row-oriented OLTP database, a B-tree index maps a column value to the exact row (or row pointer) that holds it. That's what makes a lookup on an indexed column fast: the database walks a small tree and lands on precisely the row it needs, touching almost nothing else. Filter on a column with no such index, and there's no shortcut — the engine has to walk every row, checking each one, and for large tables that's slow enough to notice immediately.

The loading-data lesson earlier in this module already showed you the mechanism: every INSERT sorts its block by the table's ORDER BY expression before writing a part. That same ORDER BY expression doubles as the table's primary key — unless a table specifies a separate PRIMARY KEY, ClickHouse uses the sorting key as the primary key directly. It's natural to assume that primary key behaves like the B-tree you already know: an exact structure for finding rows. It doesn't. ClickHouse's primary index is built on a completely different idea, and that difference is exactly why a "wrong" filter doesn't blow up the way a missing B-tree index does.

An index over granules, not rows

Instead of indexing individual rows, ClickHouse divides each part into granules — fixed-size blocks of consecutive rows in the part's sort order, 8,192 rows by default. The primary index stores exactly one entry per granule: the primary key value of that granule's first row. Not one entry per row. One entry for every 8,192 rows.

That's what makes it a sparse primary index: it's deliberately coarse. It was never built to answer "which row has this value" — it can't, on its own; a sparse index only ever tells you which row a granule starts at. What it's built to answer is a cheaper, coarser question: "which granules could possibly contain a match, so I can skip the rest?" And because it's one small entry per 8,192 rows rather than one per row, the whole index for a table with hundreds of millions of rows stays small enough to sit comfortably in memory — the tree you'd need to index every individual row at that scale would not.

This coarseness has a direct, measurable consequence even in the best case, when your filter matches the ORDER BY prefix perfectly: the index can only ever narrow the search down to whole granules, never to a single row within one. Reading a matched range of the primary key can pull in up to twice a granule's worth of extra rows that don't actually satisfy the filter, simply because the index doesn't have the resolution to draw the line more precisely than "somewhere in this granule." ClickHouse's primary index was never a precision instrument. It's a coarse pre-filter, and once you see that, the "wrong index" case stops looking like a special failure — it's just the same coarse tool doing less filtering than usual.

Why "every granule read" still isn't a disaster

Here's the part that doesn't transfer from the OLTP world. When a row-oriented database gives up on an index, it falls back to scanning every row, in full — every column of every row, because that's the physical unit the storage format hands back. That's genuinely expensive, and it's why the "missing index" rule is so unforgiving there.

When ClickHouse's primary index can't narrow anything — because your filter has nothing to do with the ORDER BY prefix — it still isn't scanning full rows. It's still a columnar engine underneath: reading user_id for every granule means reading only the user_id column file (plus whatever other columns the query actually selects), not the other twenty columns sitting untouched in their own files. And it still processes that column in vectorized blocks, the same block-at-a-time execution style from Module 1. "The primary index found nothing to skip" and "the query has to inspect every row's full contents" are two different failure modes — ClickHouse only ever has the first one. That's the whole reason a "wrong" filter here tends to land on "slower, but fine" instead of "catastrophically slow."

-- events is ORDER BY (event_type, event_time), 5,000 granules total
EXPLAIN indexes = 1
SELECT count()
FROM events
WHERE event_type = 'purchase' AND event_time > '2024-06-01';

"Indexes": [
  {
    "Type": "PrimaryKey",
    "Keys": ["event_type", "event_time"],
    "Condition": "and((event_type in ['purchase', 'purchase']), (event_time in (2024-06-01, +inf)))",
    "Parts": "3/12",
    "Granules": "40/5000"
  }
]

EXPLAIN indexes = 1
SELECT count()
FROM events
WHERE user_id = 42;

"Indexes": [
  {
    "Type": "PrimaryKey",
    "Keys": ["event_type", "event_time"],
    "Parts": "12/12",
    "Granules": "5000/5000"
  }
]

Reading these two side by side is the actual diagnostic skill this lesson is building: Granules: "40/5000" means the primary index ruled out 4,960 of 5,000 granules before ClickHouse read a single value. Granules: "5000/5000" means it ruled out nothing — every granule gets read — but, per the mechanic above, "read" here still means "read the user_id column only," not "read every column of every row." EXPLAIN is how you stop guessing which case you're in and just look.