Inspect query work

Optimization starts with evidence. Read how many rows and bytes the query processed, which parts and granules it skipped, its memory use, and where time went. Guessing leads to cargo-cult schema changes; the numbers below tell you exactly what a 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

Use EXPLAIN and relevant system tables to inspect queries and storage. With indexes = 1, EXPLAIN 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 a different one. A faster machine cannot repair an ordering key that forces every query to scan all events.

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 entirely. The ProfileEvents column on the same row breaks the work down further when you need it.

Baseline before you change anything

Run one selective and one unselective query. Save their read rows, bytes, duration, and plan before changing anything.

That baseline is the whole discipline. Schema changes, projections, and materialized views all have costs, 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.