ClickHouse Query Performance Validation

Query Performance Validation

Turn "this query looks expensive" into numbers a reviewer can act on. The output is a verdict per clause — change, keep, or caller's call — each attached to a measurement, plus the negative results so nobody retries them.

Ground rules

1. Measure, never infer. Every claim needs a number that would differ if the claim were false.
2. Equivalence gate first. No variant's cost is quotable until you have ensured it returns the same result as the query it varies, on every shape you measure. Where the query defines an order — anything with ORDER BY, and anything paginated, where order decides which rows land on the page — the comparison must include that order; compare order-independently only when the result genuinely has no defined order. A faster query that answers a different question is not an optimization. (Equivalence holds between the candidate and its variants. A candidate is often meant to change results versus main; main is the cost reference, not a result reference.)
3. Collect the whole picture per run: latency, peak memory, CPU time, and what was scanned (parts, granules, marks, rows read). The first three are what the system pays; the scan numbers are the evidence that explains why they moved. A variant can read fewer rows and still cost more memory, more CPU and the same wall time — so no single number decides anything on its own.
4. Two data shapes minimum, since rankings flip with density and skew. A win on one shape is a hypothesis.
5. One variable per variant, including deleting a clause outright to see if it earns its keep.
6. ≥5 runs; report p50, p90, p95 and min. Differences smaller than the spread are not differences, and the tail is where a polled endpoint hurts. Tail quantiles are only as good as the run count — if p90/p95 is what you are deciding on, run more than five.
7. Write down what you could not measure — quotas, unreachable shapes, cache state. Those caveats bound the finding.

Assumptions to verify rather than trust

The failure mode of this work is a confident mechanism the engine does not implement.

• A CTE referenced N times may be evaluated N times. Named CTEs are not materialized. Probe it: same query, one reference vs two, compare marks and peak memory.
• Plan node count is not evaluation count. Sets for IN (SELECT …) are often built eagerly and appear only as a literal (x in 35297-element set) with no read node — so counting ReadFromMergeTree nodes undercounts work, and a dominant subquery can be invisible.
• EXPLAIN executes scalar and set subqueries to resolve index conditions: it is neither free nor a timing proxy.
• Fewer rows read is not faster, and prune sets themselves cost memory.
• An index existing is not an index used — read granule counts, not the schema.
• A prune that pays on one shape can be dead weight on another, including one justified by a benchmark on synthetic data.
• Container data is not production shape (parts, versions per key, cardinality, array density).
• A read-only role can change the experiment: row-read caps abort long queries, and a restricted profile may refuse the settings your measurement needs.

The loop

1. Frame it. Which endpoint, which call sites (one constant serving a list page and a by-id lookup behaves differently), how often called, what it is constrained on.
2. Render the SQL for each call site, from the candidate and from main — see rendering.md.
3. Inventory the query before measuring it. Read it and write down: the entity that drives it; every CTE and subquery with how many times each is referenced; which clauses are prunes (a clause you could delete without changing results) versus load-bearing; every dedup, set build, aggregation and join. This inventory is what tells you which probes are worth running — without it you will measure the query you assumed rather than the one you have.
4. Get an environment: read-only access to a real one, else revive Testcontainers and extrapolate — see environments.md.
5. Baseline both: the candidate and main, ≥5 runs each, plan captured per call site, shape recorded alongside. The delta between them is the cost of the change, and it is a finding in its own right — often the one that matters most.
6. Locate the cost — see instrumentation.md. Isolate the suspect subquery and measure end-to-end; a gap between them is itself a finding.
7. Work the inventory, one variable at a time. Take each clause from step 3 and ask what it costs and whether it earns its keep: prunes get ablated, repeated references get a reference-count probe, dedup and aggregation steps get an alternative formulation, expensive predicates get a cheaper one. For each: state the metric you expect to move, gate it, measure on both shapes and every call site, then keep or discard fast — and keep the number either way, including for what failed.