Diagnosing slow queries

A query that scans billions of rows is almost never a hardware problem — it is reading data it does not need. This playbook shows how to find your worst queries, prove why they are slow, and rewrite them so ClickHouse reads less.

Symptoms

• Dashboards or BI tools that used to be instant now take seconds.
• A handful of queries dominate CPU and disk reads in system.query_log.
• read_rows is far larger than the number of rows the query actually returns.
• Latency spikes whenever a specific report or user runs.

What to look at first

Everything starts in `system.query_log`. It records every executed query with the bytes and rows it read, its duration and its memory. Rank by the metric that hurts — usually read_bytes or query_duration_ms.

-- Top 20 heaviest queries in the last 24h
SELECT
    query_duration_ms,
    read_rows,
    formatReadableSize(read_bytes)    AS read,
    formatReadableSize(memory_usage)  AS mem,
    normalizeQuery(query)             AS pattern
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 1 DAY
ORDER BY read_bytes DESC
LIMIT 20;

Cause 1 — Partition pruning is broken

ClickHouse can skip whole partitions only if the WHERE filter touches the partition key directly. Wrapping the key in a function — toYYYYMM(ts), toDate(created_at) — forces ClickHouse to evaluate that function on every row before it can filter, so pruning never happens.

-- ❌ Function on the partition key — scans every partition
SELECT count() FROM events
WHERE toYYYYMM(ts) = 202606;

-- ✅ Direct range on the key — prunes to one month
SELECT count() FROM events
WHERE ts >= '2026-06-01' AND ts < '2026-07-01';

Confirm pruning with EXPLAIN — look at the number of parts and granules the planner selects.

EXPLAIN indexes = 1
SELECT count() FROM events
WHERE ts >= '2026-06-01' AND ts < '2026-07-01';

Cause 2 — No PREWHERE on a heavy read

PREWHERE reads the filter columns first, drops the rows that fail, and only then reads the wide columns for the survivors. On a selective filter over a wide table this is the single biggest win. ClickHouse auto-moves some predicates, but explicit PREWHERE on the most selective column is reliable.

-- Read 'status' first, fetch the wide 'payload' only for matches
SELECT user_id, payload
FROM events
PREWHERE status = 500
WHERE ts >= '2026-06-01';

Cause 3 — FINAL on every read

FROM ... FINAL forces a merge-on-read across all parts so a ReplacingMergeTree returns deduplicated rows. It is correct but expensive on hot paths. Where you only need the latest row per key, argMax or LIMIT 1 BY does the same job without the full FINAL merge.

-- ❌ Full merge-on-read on every query
SELECT * FROM orders FINAL WHERE user_id = 42;

-- ✅ Latest version per key, no FINAL
SELECT argMax(status, _version) AS status, argMax(total, _version) AS total
FROM orders
WHERE user_id = 42
GROUP BY user_id;

Cause 4 — Memory-heavy JOINs

The default hash join builds the entire right table in memory. When the right side is large you get spills, OOM kills, or slow runs. Switching the join algorithm lets ClickHouse stream instead.

-- Let ClickHouse partition the build side to disk
SET join_algorithm = 'grace_hash';   -- or 'parallel_hash', 'full_sorting_merge'

SELECT e.user_id, u.plan
FROM events AS e
JOIN users AS u ON u.id = e.user_id;

How OptiHouse automates this

Instead of hand-writing the queries above, OptiHouse runs them across your whole query_log on every scan and ranks the findings by dollar impact: