Finding Slow Queries in ClickHouse

You have EXPLAIN in a normal database. ClickHouse has that too — plus something even better for “what’s slow right now”: a complete log of every query it has run. This is the Locate and why-so-much-work half of the debugging loop, for the store under SigNoz.

Our telemetry lives in ClickHouse (metrics in signoz_metrics, traces in signoz_traces, logs in signoz_logs). When a dashboard or an alert feels slow, this is where you look.

Where to run these#

clickhouse-client on the box that runs ClickHouse:

# on the observability host
docker exec -it $(docker ps -qf name=clickhouse) clickhouse-client

(You can also run read-only SQL through the SigNoz API with a clickhouse_sql query, but for debugging, the client on the host is the most direct.)

1. system.query_log — the flight recorder#

Every finished query, with duration and — crucially — how much it read:

SELECT
    query_duration_ms,
    read_rows,
    formatReadableSize(read_bytes) AS read,
    result_rows,
    substring(query, 1, 120) AS q
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 30 MINUTE
ORDER BY query_duration_ms DESC
LIMIT 10;

This one query is how you find every slow thing. “The dashboard is slow” → run this while it loads → you see the exact query and its cost. No guessing.

2. The golden signal: read_rows vs result_rows#

The single most useful number in query debugging. A query that reads 15.5 million rows to return 20 is doing ~750,000× too much work — and that ratio is the bug before you know the cause:

SELECT
    read_rows,
    result_rows,
    intDiv(read_rows, greatest(result_rows, 1)) AS amplification,
    substring(query, 1, 100) AS q
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 30 MINUTE
ORDER BY amplification DESC
LIMIT 10;

High amplification → ask why. The answer is almost always one of: reading the wrong (too-fine) table, not using the index, or high cardinality.

3. Which table did it actually read?#

system.query_log records the tables each query touched. This is how we caught SigNoz reading the fine 5-minute rollup for 7-day queries instead of the coarse 30-minute one:

SELECT query_duration_ms, read_rows,
       arrayStringConcat(tables, ', ') AS tables
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 15 MINUTE
  AND has(tables, 'signoz_metrics.samples_v4')  -- or whatever you suspect
ORDER BY query_duration_ms DESC
LIMIT 10;

Rollups matter here: SigNoz keeps raw samples plus pre-aggregated _agg_5m and _agg_30m tables, and picks one by the query’s time range. If a query is reading samples_v4 (raw) for a 30-day window, it’s scanning far more than it needs — reading the wrong table is a top cause of a slow panel.

4. EXPLAIN — is my index actually working?#

The literal EXPLAIN. The useful variant shows whether the primary index pruned the scan:

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

It prints how many parts / granules it will scan and whether the index narrowed them. If you’re filtering on a column that isn’t the leading part of the table’s sort key, you’ll see it scan everything — a classic, invisible slowness. (Why that happens and how to fix it: indexes and read amplification.)

5. The physical picture: parts, partitions, merges#

Sometimes the query is fine and the table is the problem — too many small parts, or an over-partitioned table where every query pays to open hundreds of partitions:

-- parts & partitions per table (part explosion / over-partitioning)
SELECT table, count() AS parts, uniqExact(partition) AS partitions, sum(rows)
FROM system.parts
WHERE active AND database LIKE 'signoz%'
GROUP BY table
ORDER BY parts DESC;

-- what's merging right now (background CPU)
SELECT database, table, elapsed, progress, num_parts FROM system.merges;

We once found a 13,000-row table split into 176 daily partitions — every alert evaluation opened all 176 and took ~850ms. The fix wasn’t the query; it was the table shape.

The cheat sheet#

Everything here is the DB half of the same loop: locate the query, look at how much it read versus returned, ask why. For the tool-side view (services, traces, dashboards), see debugging with SigNoz.