Inspect query work

Optimization starts with evidence. Before you touch the schema, read how many rows and bytes a query processed, which parts and granules it skipped, how much memory it used, and where the time went. Guessing leads to cargo-cult schema changes. The numbers tell you what the query cost.

The quickest evidence is free. clickhouse-client prints a summary after every query:

1 row in set. Elapsed: 0.018 sec. Processed 8.19 thousand rows, 65.54 KB

Processed ... rows is the headline number. A query that returns 20 rows but processes 500 million is doing enormous work to find its answer. That’s your signal, long before anyone complains about latency.

Ask the planner what it will skip

EXPLAIN with indexes = 1 shows how much data the primary index eliminates:

EXPLAIN indexes = 1
SELECT count() FROM events WHERE service = 'api';

PrimaryKey
  Keys: service
  Condition: (service in ['api', 'api'])
  Parts: 2/6
  Granules: 41/482

Read the fractions. Granules: 41/482 means the index narrowed the scan to 41 blocks out of 482. The filter is doing its job.

A filter on a column the ordering key can’t help with reads 482/482, a full scan. That’s the signature of the classic failure: an ordering key chosen for one query shape while the dashboards ask another. A faster machine cannot fix an ordering key that forces every query to read every event.

The query log remembers

Every finished query lands in system.query_log with its full cost:

SELECT query_duration_ms, read_rows,
  formatReadableSize(read_bytes) AS data,
  formatReadableSize(memory_usage) AS mem
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY event_time DESC
LIMIT 5;

This is how you audit yesterday’s slow dashboard without reproducing it. Find the query, read read_rows and memory_usage, and you know whether the problem is scanning, aggregation memory, or something else. The ProfileEvents column on the same row breaks the work down further when you need it.

Baseline before you change anything

Run one selective query and one unselective query. Save their read rows, bytes, duration, and plan somewhere you’ll find them again.

That baseline is the whole discipline. Schema changes, projections, and materialized views all have a cost, and the only way to know a change paid off is comparing the same numbers before and after. “It feels faster” has fooled everyone at least once. read_rows dropping from 500 million to 2 million has never fooled anyone.

Try this on a table you already have: pick the slowest dashboard query, run it through EXPLAIN indexes = 1, and write down the granule fraction. If the fraction is close to full, the fix is the ordering key or a precomputed table, not more hardware.