Indexes, Sort Keys & Read Amplification

The most common “why is this slow, it returns almost nothing?” answer is: the query isn’t using the index, so it reads the whole table. This page is about that gap — read amplification — and how indexes (and, in ClickHouse, sort keys) close it.

Read amplification: the gap that is the bug#

Read amplification = rows read ÷ rows returned. A healthy query reads roughly what it returns. A sick one reads millions to hand back twenty. From the debugging loop, this ratio is the smell — you spot it before you know the cause:

returns 20 rows · reads 15,500,000 rows  →  amplification ≈ 775,000×

That number says “I am scanning far more than I need.” An index is usually how you fix it.

What an index actually does#

An index lets the database skip data instead of reading it. Without one, “find rows where status = 'error'” means reading every row and checking — a full scan. With an index on status, it jumps straight to the matching rows.

The catch that surprises people: an index only helps if your filter matches the columns it’s built on — in order. An index on (a, b, c) helps a query filtering on a (or a, b), but not a query filtering only on c. Filter on the wrong column and the index sits there unused while you scan everything.

ClickHouse: the sort key is the index#

ClickHouse doesn’t have per-column indexes like a typical OLTP database. Its primary index is the table’s sort key (ORDER BY (...)). Data is physically stored sorted by those columns, and the index lets it skip whole blocks (“granules”) that can’t match.

The rule is the same, and sharper: filter on the leading column(s) of the sort key, or you get no pruning.

A real example. The trace table’s sort key led with ts_bucket_start:

ORDER BY (ts_bucket_start, resource_fingerprint, ...)

A hand-written dashboard query filtered on timestamp — a different column, not part of the leading key:

-- slow: filters on timestamp, but the sort key leads with ts_bucket_start → near-full scan
WHERE timestamp >= now() - INTERVAL 7 DAY

ClickHouse couldn’t prune, so it read almost the whole table. Adding a filter on the leading column let it skip:

-- fast: prunes by the leading sort-key column
WHERE ts_bucket_start >= toUnixTimestamp(now() - INTERVAL 7 DAY)
  AND timestamp >= now() - INTERVAL 7 DAY   -- keep for exactness

Same result, a fraction of the rows read.

How to see it#

EXPLAIN indexes = 1 shows whether the index pruned the scan — how many parts and granules it will read (see finding slow queries):

EXPLAIN indexes = 1
SELECT count() FROM signoz_traces.signoz_index_v3
WHERE ts_bucket_start >= toUnixTimestamp(now() - INTERVAL 1 DAY);

If the plan shows it selecting nearly all granules for a query that should touch few, your filter isn’t hitting the index.

Why not just index everything?#

Indexes aren’t free — every index is extra storage and extra write cost (each insert must maintain it). In ClickHouse there’s exactly one primary sort key per table, chosen for the dominant query pattern; you can’t reorder it per query. So the skill is:

1. Know your table’s sort key before writing a query against it.
2. Filter on its leading column(s) so the query can prune.
3. When you can’t, expect a scan — and reach for a smaller (pre-aggregated) table instead, like a rollup.

The mental model#

A query is only as fast as the data it can skip. Indexes and sort keys are skip mechanisms, and they only engage when you filter on their leading columns. When “rows read” dwarfs “rows returned,” you’re skipping nothing — check whether your filter matches the index.