Diagnosing slow ClickHouse queries before changing the schema
When a dashboard or ingestion job slows on a self-managed ClickHouse cluster, the first decision is not which schema change to make but which query and which resource is actually slow. Changing an ordering key, adding a projection or rewriting a join without that evidence often costs a rebuild and still leaves the original problem in place. The sound order is to find the slow statements, measure what they read and consume, isolate the bottleneck, and only then choose between a schema, index or query fix.
ClickHouse publishes a query optimization guide that walks through diagnosing slow queries, isolating bottlenecks, choosing an optimization approach and working a complete example. Teams should use it as the reference for the method, then apply it to their own workload. ClickHouse server is licensed under the Apache License 2.0 (Apache-2.0), so the guide's techniques apply directly to the cluster you run, whether it sits on bare metal, virtual machines or Kubernetes in your own infrastructure. The tuning work described here is therefore your team's responsibility on that cluster, and it needs the same change control you apply to any other production database.
Before any change, confirm three things with your own team: which dashboards and ingestion jobs are affected, whether the slowdown started after a data volume, query pattern or version change, and whether a pre-production copy with representative data exists to test against. The baseline timings and rows read you capture now are what will tell you later whether a fix worked, so record them before touching the table definition.
How to find the slow queries in system.query_log
This sequence turns the raw query history in system.query_log into a short, ranked list of queries worth investigating, separated by workload and backed by a log retention window that covers the incident. It sets up the evidence the rest of the diagnosis depends on.
Confirm query logging and retention
Before searching, check that query logging is enabled on every node that serves queries and that the log keeps rows long enough to cover the incident window. Ask the platform owner to confirm the query_log settings in the server configuration and the TTL or retention applied to the table, then run a count of rows grouped by day against system.query_log for the last several days. The step is done when the earliest retained day covers the period you need to investigate. If it does not, record that gap now, because older slow queries cannot be recovered from this table.
Scope the search to the incident window
Limit the search to the time range of the complaint, using the event_time column and a margin on each side so that queries started just before the reported symptom are included. Filter on type = 'QueryFinish' to examine completed queries, and add a separate check for QueryExceptionBeforeStart and ExceptionWhileProcessing to capture failed work. Note the time range and the cluster or replica names in your working notes. The step is complete when you have one bounded result set that you can reproduce.
Rank queries by duration and read volume
Sort the window by query_duration_ms and then compare read_rows, read_bytes, memory_usage and result_rows for the slowest entries. A query that is slow with a small read volume usually points to waiting, contention or a heavy final stage, while a query that reads many rows points to scan or key issues. Record the top candidates with their query_id, user, and the values for each measure. The output is a short list of queries whose costs are clearly above the rest of the window.
Filter by memory use and error status
Run a second pass that ranks queries by peak memory_usage and lists any entries with a non-empty exception column or an error-type type value. Queries that fail on memory limits may never show a long duration, so they can hide in the error set. Keep the errors in the same working list and mark which ones were retried by the application. The step is done when every failed or memory-heavy query in the window is either on the candidate list or marked as examined and excluded.
Group by normalized query text
Group the candidates by normalized_query_hash or a normalized form of the query text, so that many executions of the same statement with different literal values appear as a single pattern. Count the executions and sum the duration and read volume for each group. A pattern that runs often and costs a moderate amount each time can matter more than one expensive ad hoc query. Save the top patterns with their execution counts and total cost, since these become the targets for schema and query changes.
Separate dashboard traffic from ad hoc queries
Split each pattern by user, interface and client name, using the user, http_user_agent or client_name columns, and by any application-specific tagging your teams apply, such as query settings or comments. Dashboards usually run on fixed schedules from service accounts, while ad hoc analysis comes from people or notebooks. Label each group as dashboard, ingestion-adjacent, scheduled job or ad hoc. The output is a list where each pattern has an owner and a workload type, which decides how it is tuned and who must be consulted before a change.
Shortlist queries for bottleneck analysis
Select a small set of patterns that carry the most total cost or the worst single-execution cost and record why each was chosen. Include the query text, the table names, the time range, the user and the measured values. Confirm that the shortlist reproduces the original complaint, either by rerunning a representative query on a pre-production copy with representative data or by matching the timing in the log. The step is complete when each shortlisted query has a written record that the next stage can analyze with EXPLAIN and profiling.
Isolating the bottleneck: scan, join, memory or concurrency
A scan bottleneck means the query reads far more data than it needs to answer. It shows up as a large gap between rows read and rows returned, with the time spent mostly in reading and decompressing data rather than in aggregation or joining. The fix usually sits in the table design or the filter, not in the query engine settings.
- Rows read versus rows returned: Compare the read counters in
system.query_logwith the result row count. A query that reads most of a large table to return a few rows is a scan problem. - Filter columns outside the ordering key: If the predicates do not match the leading columns of the
ORDER BYkey, ClickHouse cannot skip granules efficiently. Check the key choices against the query filters. - Partition pruning misses: Filters that wrap the partition column in functions or use non-matching types may prevent pruning. Confirm in the
EXPLAINoutput which parts and partitions are selected. - Wide reads: Queries that select many columns, or use
SELECT *, read more column data than the answer needs. - Fix family: Schema fixes such as ordering keys, partitions and skip indexes, or query fixes such as narrower projections and sargable filters.
Confirm the exact counter names and the EXPLAIN options available in your ClickHouse version before you build a checklist around them.
A join bottleneck appears when the cost sits in combining tables rather than reading one. The large side of a join, the right-hand table held in memory, or a join executed in an unexpected order can all dominate the runtime. The EXPLAIN output and the query log usually show that most of the time is spent in the join stage, and that intermediate row counts grow after the join.
- Large intermediate results: A join that produces many more rows than either input, or a result that expands before a filter applies, points to join order or a missing early filter.
- Right-side size: Joins that load a large table into memory on the right side can use a lot of memory and time. Check which table is on each side in your query and in the plan.
- Repeated lookups of small reference data: Dimension lookups repeated across millions of rows often cost more than they should. These may be better served by denormalized columns or dictionaries.
- Filters applied after the join: If conditions that could limit one table are only applied to the joined result, the engine does more work than necessary.
- Fix family: Query fixes such as filter pushdown and join order, or schema fixes such as denormalizing attributes, dictionaries and materialized views that precompute the join result.
Confirm which join algorithm and settings your version uses and how it reports them in the plan before you change the join strategy.
A memory bottleneck shows up as high peak memory for one query, spilling to disk, or an out-of-memory error. It is often caused by large aggregation states, big sorts, or a join or aggregation with high cardinality keys. Memory pressure can also slow a query down well before it fails, because the engine starts writing intermediate data to disk.
- Memory peaks in the query log: Look at the peak memory recorded for each query and compare it with the failing or slow runs. Queries that hit a limit or spill are candidates for this family.
- High-cardinality group keys: Grouping by columns with very many distinct values forces large hash tables. Check whether the grouping can use a coarser key or a precomputed aggregate.
- Large sorts without limits: Sorting an entire large result when only the top rows are needed creates unnecessary memory use.
- Spill events: External aggregation and sorting leave traces in the query log and in the settings applied. Confirm the spill-related counters and thresholds in your version.
- Fix family: Query fixes such as adding limits, reducing selected columns or using approximate functions where they are acceptable, plus schema fixes such as materialized aggregates. Server or per-query memory limits are a safety net, not a substitute for a better query.
Confirm the exact memory and spill fields in your version and the defaults that apply to your deployment.
A concurrency bottleneck means the query is not slow on its own. It waits because other work is using the same CPU, memory, or I/O, or because too many queries run at once. Runtime that varies widely between identical runs, or that depends on the time of day, often points here. Fixing the query alone will not help if the server is saturated.
- Queueing under load: Compare query start times with the time spent before execution. Long waits before the query runs, or rising concurrent query counts, indicate queueing.
- Variable runtime for identical queries: If the same query runs quickly at night and slowly during dashboard peaks, look at shared resource use during those windows.
- Ingestion competing with reads: Large background merges and heavy inserts can slow queries that read the same tables. Check merge activity and insert volume at the same times.
- Dashboard fan-out: Many panels refreshing together can produce a burst of similar queries that compete with each other.
- Fix family: Resource controls such as workload scheduling and concurrency limits, plus scheduling changes for heavy jobs. Query fixes help only after the shared load is understood.
Confirm the workload and concurrency settings available in your version and how your monitoring exposes queue and merge activity.
Which fix fits which finding
This table compares the five fix families for slow ClickHouse queries, showing the symptom each addresses, what it costs in inserts or storage, and when it is the wrong choice. Read it after the query log and EXPLAIN output point to a specific bottleneck, then pick the cheapest family that removes that bottleneck.
| Fix family | Symptom it addresses | Cost in inserts or storage | When it is the wrong choice |
|---|---|---|---|
| Ordering key or schema change | High rows read for filters on columns that are not leading the sort order, with most granules scanned | Requires a new table and data copy, plus a rewrite of ingestion paths; storage may shift with compression | Filters are already on leading key columns, or the real cost is aggregation or joins. See Choosing a ClickHouse primary key and ordering key |
| Data skipping index | Selective filters on columns outside the ordering key where EXPLAIN shows few granules pruned | Adds index storage and some insert overhead; index only helps when data is clustered for the filter value | Values are spread across all granules, so the index skips nothing. See Using data skipping indexes in ClickHouse |
| Materialized view or projection | Repeated aggregations or alternate sort orders that rescan the same large base table on every dashboard refresh | Extra work on every insert and extra storage for the target table or projection; backfill is needed for history | Results must be fresh only on a schedule, or the query shape changes often. See Speeding up ClickHouse queries with materialized views and ClickHouse projections versus materialized views |
| Query rewrite or join change | Heavy joins, unnecessary columns, SELECT star or late filtering that the query log shows reading far more data than the result needs | Usually no storage or insert cost; some effort to change application or dashboard SQL | The rows read are already minimal and the time goes to memory or concurrency. See Optimizing JOINs in ClickHouse and Using dictionaries for fast lookups in ClickHouse |
| Workload or memory limits | Slow or failing queries caused by contention from ingestion, dashboards or ad hoc users, or by queries that spill or exhaust memory | No data cost; configuration and monitoring effort, and possible throughput loss for lower-priority work | Rows read are high because of a poor key or missing index, so limits only hide the problem. See Isolating workloads with ClickHouse workload scheduling and Controlling ClickHouse memory use and spilling to disk |
How to read EXPLAIN output and rows read without guessing
Run EXPLAIN indexes = 1 on the slow query to see which parts of the table ClickHouse expects to read before it touches any data. The output lists the primary key condition and any skip indexes, along with how many granules or parts each step keeps. If the primary key line shows no condition, the filter is not matching the leading columns of the ORDER BY key, and the query scans far more of the table than it needs. Skip indexes deserve the same check: a Skip step that drops few granules means the index is not helping this filter, and the ordering key or index definition needs work before anything else. Use EXPLAIN PIPELINE only when you want to see how the work is parallelized across threads, since that view answers a different question.
Compare the plan with what actually happened. The system.query_log table records read_rows and read_bytes for each execution, alongside result_rows, so a large ratio of rows read to rows returned is the clearest sign of wasted scanning. Pull these figures for the same query text and the same time window, because a cold cache or a concurrent merge can change them between runs. Confirm the table and filter values match production, since a plan built against a small test copy can look fine while the live query reads far more.
EXPLAIN shows what ClickHouse plans to do, not how long anything took. A plan that looks efficient can still run slowly because of memory pressure, network hops, or contention with other workloads, and a plan that looks wasteful can be fast on a warm cache. Treat the plan as a hypothesis, then check it against measured query_duration_ms and memory figures from system.query_log for real executions. Only change the schema or query once the plan and the measured numbers point to the same cause.
Common mistakes that send teams after the wrong fix
Most wasted tuning effort starts with a measurement that was taken in the wrong conditions or read the wrong way. The cards below cover the errors we see most often when teams diagnose slow ClickHouse queries, and the check that catches each one before a schema or index change goes to production.
Tuning on a cold cache
The first run of a query after a restart or a long idle period reads data from disk and builds caches, so its duration can look far worse than normal use. Teams then add indexes or change keys to fix a problem that only existed on the first run. Run the query several times, record the runs separately, and compare the warm runs with the cold one before deciding anything. Check whether the number of rows read changes between runs, since that points to a real access pattern problem.
Testing on unrepresentative data
A test table with a few million rows, uniform values and no skew can behave nothing like production, where one tenant or one day holds most of the events. Plans, granule pruning and memory use all depend on data distribution. Test on a copy with production-like row counts, the same ORDER BY key, and the real filter values your dashboards use. Confirm the copy matches production on partition layout and value cardinality before trusting the timings.
Adding indexes before checking the ordering key
A skip index is often added because a filter is slow, but the real cause may be that the filter column is not early in the ORDER BY key, so ClickHouse cannot prune granules at all. A skip index layered on top of an unsuited key adds insert cost and rarely fixes the query. Run EXPLAIN with indexes on and check whether the primary key is used and how many granules are selected. Only consider a skip index after the key has been ruled out.
Reading only average duration
An average query duration hides the slow tail, which is usually what users and dashboards feel. A query that runs in a fraction of a second most of the time but times out once a day can have an acceptable average and still be the problem. Look at percentiles and the worst executions in system.query_log, grouped by normalized query text, and check rows read and memory for those outliers. Fix the queries that dominate the tail first.
Ignoring concurrent load from ingestion
A query measured on an idle cluster may slow sharply when inserts, merges and other dashboards run at the same time. Merges compete for disk and CPU, and background work can delay the query you are investigating. Measure the query during the same hours it runs in production, and compare its timings with merge activity and insert volume from system tables. If contention is the cause, the fix may be workload isolation or insert batching rather than a query rewrite.
Changing several things at once
Changing the ORDER BY key, adding a skip index and rewriting the query in one release makes it impossible to know which change helped or hurt. The gain may come from one change while another regresses inserts or memory use. Change one variable at a time, record the rows read, duration and memory for each run against the same copy, and keep a baseline you can return to. Only combine fixes after each one has shown its own effect.
Questions teams ask before diagnosing ClickHouse performance
Can our team diagnose slow ClickHouse queries in house?
Yes, many teams can, provided someone can read query logs, interpret EXPLAIN output and reason about how the ordering key matches filters. The skills are mostly SQL and MergeTree fundamentals. What usually stalls in-house efforts is time to work through queries across the cluster and the lack of a safe place to test fixes. A review is most useful when your engineers already know ClickHouse basics but have no repeatable method, or when the slow queries touch business logic that only your team can explain. The ClickHouse query optimization guide describes the method, which you can follow internally before deciding whether outside help adds value.
What drives the effort of a ClickHouse performance review?
The effort depends on a few measurable inputs that should be stated in the written scope:
- Cluster size: the number of shards, replicas and nodes, which determines how much telemetry must be collected and compared.
- Query count: how many distinct slow query shapes need analysis, and how many dashboards or applications issue them.
- Data volume: the row counts and part layout of the affected tables, which affect how long each EXPLAIN and test run takes.
- Access to representative data: whether testing can use a pre-production copy with realistic volumes and schema.
Ask the provider to list these inputs, state which ones they assumed, and name what changes the quote if the assumptions prove wrong.
How long does diagnosis take for a given scope?
Duration follows scope. A review limited to a small set of named queries on one table is far shorter than a review covering every dashboard across several clusters. Timing also depends on how quickly the team can export query logs, grant read access and provide a copy for testing, since waiting on those steps often takes longer than the analysis itself. No general duration applies, so ask for a timeline tied to specific deliverables, such as a ranked list of queries, a root-cause note per query and a tested fix for each, with the dependencies each step needs from your side written into the plan.
What do we need to provide for a ClickHouse performance review?
Plan to provide the following before work starts:
- Query log access: exports from
system.query_logfor the period when the problem appears, including the user and client identifiers needed to group queries. - Schema definitions:
SHOW CREATE TABLEoutput for affected tables and any materialized views, projections or dictionaries they depend on. - Cluster layout: shard and replica topology, server versions and relevant settings profiles.
- A pre-production copy: a non-production environment with representative data and schema, so fixes can be measured without changing production.
- A named contact who can approve tests and explain business meaning of the dashboards and jobs involved.
Confirm data handling rules for query logs and sample rows, since these can contain personal or sensitive values.
When should we choose a retainer instead of a fixed-scope review?
Choose a fixed-scope review when you have a defined set of slow queries, a known cluster and a clear goal such as a ranked list of fixes and a tested change plan. Choose a retainer when performance needs continued attention, for example when new dashboards, ingestion sources or schema changes arrive every month, or when your team wants a provider available for repeated investigations. Fixed scope is agreed in writing before work starts, so the deliverables and boundaries are explicit. Retainers suit ongoing needs, and their scope and recurring effort should be written down the same way, with a clear list of what is included each period.
Does a review include applying fixes to our production clusters?
Whether production changes are included should be stated in the written scope. A typical review covers diagnosis, isolating bottlenecks and recommending schema, index or query changes, with each proposed change tested on a copy first. Applying changes to production, and the change window, rollback steps and monitoring afterward, are separate decisions. Ask the provider to specify who executes production changes, how changes are reviewed, and how results are verified against the same queries before and after the change.
Which ClickHouse version should the diagnosis be run against?
Run the diagnosis against the version your production clusters actually use, because query planning and settings behavior can differ between releases. The ClickHouse security policy lists versions 26.9, 26.8, 26.7 and 26.3 as currently supported with security updates, so confirm your running version appears on that list before drawing conclusions about planner behavior. Ask the provider to record the exact version in the scope and to flag any fix that depends on a newer release, since upgrades carry their own planning and compatibility checks.
How DeployOpen scopes and delivers a ClickHouse performance review
DeployOpen treats a slow-query investigation as a bounded piece of work: we agree the workload, the systems and the evidence in writing, then trace each slow query to a cause before recommending a fix. Engagements are fixed-scope, so the questions to answer and the deliverables are settled before any analysis begins.
How we scope
- Agree which queries, tables, users and time windows are in the written scope before any analysis starts.
- Confirm access to system.query_log, the cluster topology and the ClickHouse version running in your environment.
- Define the pre-production copy with representative data and queries where candidate fixes will be tested.
How we build
- Collect baseline timings, rows read and memory use for each in-scope query from system.query_log.
- Run EXPLAIN on the slow queries and record where the bottleneck sits: scan, join, aggregation or sort.
- Test each candidate change, whether schema, skip index or query rewrite, on the copy and compare the same measurements.
How we hand over
- Deliver a written report with each finding, the evidence behind it and the change recommended for it.
- Document the query checks and log queries so your team can repeat the diagnosis on future slow queries.
- Walk your engineers through the results and list the changes that need production review and a changelog check.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer after the review closes.
- Ask for a quote that separates the diagnosis work, the test environment setup and any follow-up changes.
- Confirm in writing who owns production changes, rollback steps and the monitoring that tracks the fixes.
