Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 35 ยท Performance and Indexing

Query Profiling and Optimization

Find and fix slow ClickHouse queries with EXPLAIN, system.query_log, read_rows, PREWHERE, FINAL costs, join order and settings, plus a SQL stand-in to compare plans.

ClickHouse queries are usually fast, but a slow one is typically slow for a small number of reasons: it reads too many rows, too many columns, uses too much memory in a join or aggregation, or runs too many tiny inserts and merges behind it. This lesson gives you a measuring routine and a checklist of fixes, ordered from most to least effective.

The ClickHouse statements are read-only snippets (no server on this site). The runnable parts use SQLite's EXPLAIN QUERY PLAN as a stand-in, to practice the habit of reading a plan.

Step 1: measure what a query really does

Run the query, then ask the server what happened. The system.query_log table records every finished query with counters:

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

The most useful ratio is read_rows versus result_rows and versus the table size. Reading 2 billion rows to return 12 means the filter did not prune. clickhouse-client also prints rows read, bytes and elapsed time after each query, and SET send_logs_level = 'trace' shows the server's reasoning as it runs.

To find the worst offenders over time, group by normalized_query_hash and sort by total duration or total bytes read.

Step 2: read the plan

SQL
EXPLAIN indexes = 1
SELECT url, count() FROM hits WHERE site = 'shop.example' AND ts >= today() - 7 GROUP BY url;

EXPLAIN variants worth knowing:

Statement Shows
EXPLAIN or EXPLAIN PLAN logical steps (read, filter, aggregate, sort)
EXPLAIN indexes = 1 parts and granules selected by the primary key and skipping indexes
EXPLAIN PIPELINE the parallel execution pipeline, number of threads
EXPLAIN SYNTAX the query after the optimizer rewrote it
EXPLAIN ESTIMATE estimated rows, marks and parts to read

If Granules: 118000/118000 appears in the PrimaryKey section, the sorting key is not helping this filter. Fix the filter, the key, or add a projection or skipping index.

Step 3: apply fixes in this order

  1. Read fewer rows. Filter on the sorting-key prefix, filter by partition or date range, and avoid wrapping key columns in functions that block pruning (WHERE toDate(ts) = ... can still prune, but WHERE lower(site) = 'a' cannot use an index on site).
  2. Read fewer columns. ClickHouse is columnar: SELECT * reads every column file. Name only what you need. This is the biggest free win.
  3. Let PREWHERE filter early. PREWHERE reads only the filter columns first, finds matching rows, and then reads the other columns just for those rows. ClickHouse moves cheap conditions from WHERE to PREWHERE automatically (optimize_move_to_prewhere = 1), but you can force it for a selective condition on a small column.
  4. Pick the right data types. Smaller types and LowCardinality mean fewer bytes.
  5. Pre-aggregate. If the same rollup runs constantly, store it with a materialized view.
  6. Tune joins. Put the smaller table on the right: the right table is loaded into memory as a hash table. Filter both sides before joining, and use a dictionary or IN subquery for lookups when you can.
  7. Avoid FINAL on huge tables unless needed. SELECT ... FINAL on a ReplacingMergeTree applies the merge logic at query time, which is costly (though it parallelizes better in recent versions). Alternatives: argMax, LIMIT 1 BY key, or a deduplicating materialized view.
  8. Control memory. For big GROUP BY and ORDER BY, settings such as max_bytes_before_external_group_by and max_bytes_before_external_sort allow spilling to disk, and max_memory_usage bounds a query.
SQL
-- Narrow columns and a selective PREWHERE
SELECT user_id, url
FROM hits
PREWHERE status = 503
WHERE site = 'shop.example' AND ts >= today() - 1;

-- Put the smaller table on the right of the join
SELECT h.url, d.title
FROM hits AS h
INNER JOIN pages AS d ON h.url = d.url;

Insert-side problems that slow queries

Too many small inserts create a pile of tiny parts. Each query then opens many files, and the background merges fall behind (Too many parts error). Insert in batches of at least 10,000 to 100,000 rows, at most about one insert per second per table, or enable asynchronous inserts so the server batches for you (see the loading data lesson). Watch system.parts for the count of active parts per partition and system.merges for running merges.

Over-partitioning causes the same issue. Partition by month or day, not by user or hour, and keep total partitions in the low hundreds.

A quick checklist

Slow query? Check in order

Rows read Compare read_rows to result
Key and filters EXPLAIN indexes = 1
Columns No SELECT *
Parts Batch your inserts

Measure first, then change one thing.

1 / 2

Practice the habit: reading a plan

The server is not available, but SQLite's plan output has the same purpose: did the engine use an index or scan everything? This stand-in compares a filter without and with an index.

SQL Stand-in: scan versus index search

Press Run (or Ctrl + Enter) to see the result here.

Read the detail column: a line starting with SCAN means a full pass over the table. Now add an index (the stand-in for a good sorting key) and compare:

SQL Stand-in: the same query after adding an index

Press Run (or Ctrl + Enter) to see the result here.

The plan now says SEARCH ... USING COVERING INDEX. In ClickHouse the equivalent evidence is a small Granules: selected/total ratio. Remember that ClickHouse reads whole granules, so even a good plan reads thousands of rows, which is fine.

Common mistakes

  • Optimizing without measuring: always compare read_rows and duration before and after.
  • Using SELECT * or ORDER BY on huge result sets without LIMIT.
  • Joining a big table on the right side of a join.
  • Creating thousands of partitions or inserting row by row.
  • Adding FINAL everywhere to hide duplicates instead of fixing the model.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which pair of numbers in system.query_log most directly shows a query is not pruning well?

    Reading billions of rows to return a few means the filter did not narrow the data read.
  2. 2. In a ClickHouse join, which table is loaded into memory as a hash table by default?

    The right-hand table is built into a hash table, so it should be the smaller one.
  3. 3. What does PREWHERE do?

    This saves I/O when the condition is selective and the other columns are wide.
  4. 4. Why are many tiny INSERT statements harmful?

    Each insert creates a part. Too many parts causes 'Too many parts' errors and slow reads; batch rows or use async inserts.

Summary

  • Measure with system.query_log, comparing read_rows, bytes and memory.
  • Read plans with EXPLAIN indexes = 1 and EXPLAIN PIPELINE.
  • Fix in order: fewer rows, fewer columns, PREWHERE, types, pre-aggregation, joins, FINAL.
  • Batch inserts and avoid over-partitioning to keep part counts low.

Next: Replication and ClickHouse Keeper