Query analysis

Query analysis shows how a query does its work: an execution analysis of one real run, and what-if estimates for hypothetical indexes on the tables a chart reads. Both appear on the cost card that Model AI returns for a chart's query, and a Model Builder runs the same checks while it tunes a model. Query cost covers the card and its four modes; this page goes through the analysis and what-if results themselves.

Read an execution analysis

Ask the Model AI chat for an analysis of the query behind a chart. While it works, the chat shows Analyzing one run of a query. The run executes the chart's query once, with the result cache off, and reads the plan the database produced. Its timing is one run, not a median, and the card carries the note "EXPLAIN ANALYZE ran the chart once with the result cache off; its timing is one run, not the median of a measure."

• The tiles come first: Ran in with a one run badge, Rows read, Bytes read and Peak memory. The figures are read from the rounded plan text, so Rows read carries approx.
• A short findings list, up to eight items, names what deserves a look, from a slow step to a join whose output was misestimated, so the expensive parts surface without reading the whole plan.
• The plan follows, one row per step, indented by depth. A row reads Rows in → out (what entered and what left, with the share it filtered away); the time and share beside it belong to the step's Slowest stage. Expanding a step shows its stages with their own times, shares and parallelism, the runtime filters that ran on it, and, for a join, the join facts.
• A join's facts compare planning with reality: Left and Right list each side's rows and the count the planner expected, Matched gives what each side matched, Hash table the join's memory use, and Output rows the estimate, the actual count and the q-error between them. Spill flags a hash table that spilled to disk.
• The joins charts build are LEFT ANY, and the database does not count matched rows for those, so Matched, match rate and fan-out read not collected. In the plan text, a table without statistics shows estimated no stats where the estimate belongs, and q-error not collected.
• When the card holds back steps to stay readable, a line under the table counts them, {n} faster steps not shown.

Read the plan text

Show EXPLAIN ANALYZE opens the card's copy of the plan. When the text is cut, the card says "Only the first part of the plan text is shown."

• The text opens with a Query summary: block: the total time with its split into planning and execution, the rows and bytes read, and peak memory. When the server reports it, a further line splits the time into three parts: in steps, outside steps, and idle.
• Each step follows as an indented tree. Its I/O line gives rows and bytes before and after, its time, and its parallelism; stage lines such as Stage (build) and Stage (probe) carry their own times and shares, and the runtime filters that ran are named on the step they helped.

Read a what-if estimate

Ask the chat to price indexes you are considering on the tables a chart reads; while it works, the chat shows Estimating candidate indexes. The candidates are hypothetical: nothing is built, and they live only for the estimate.

• Each table's part starts from its baseline, {n} marks kept in {m} parts, and counts how many of the chart's filters the estimate modelled; the filters the estimate left out are counted under it.
• A candidate row carries its label, its type (minmax, set(100), bloom_filter(0.01)), the columns it covers and its granularity {n}, then what it would save: skips {n}%, with {baseline} → {marks} marks · {bytes} left to read.
• The badge beside the saving says how the figure was obtained: measured when the database measured the kept data, measured on a sample when only part of the kept data was read, estimated for a statistical figure, and applicability only when it answered only whether the index applies.
• The best candidate carries Suggested; when the candidates would skip more together than alone, a Together row prices them as one group, up to everything the chart's filters kept.
• A candidate that cannot be priced reads Not applicable with the reason; a table that ended early reads Timed out, Failed or Skipped. Show probe SQL opens the statement the estimate ran.
• Read the notes under the card before trusting a figure; they list what the estimate could not see or promise.
• On a server that is not new enough, the card answers Analyzed and What if with a Needs ClickHouse {version} notice instead; Query cost shows that notice and the modes that keep working.

Questions, answered

How do I ask for a query analysis or a what-if?

Open the Model AI chat beside a semantic model and ask in your own words: for an analysis of the query behind a chart, or for a price on an index you are considering on the tables the chart reads. The chat shows Analyzing one run of a query or Estimating candidate indexes while it works.

What does q-error tell me?

It measures how far the planner's estimate was from what actually came out, on a join's output. q-error 1.00 means the estimate was exact; the higher it goes, the further off the estimate was, and a misestimated join becomes a finding when it drifts far enough.

Why do some join figures say not collected?

The database does not count matched rows for ANY and ASOF joins, and the joins charts build are LEFT ANY. For those, Matched, match rate and fan-out read not collected; the rest of the join facts still work.

What do "Rows in → out" and "Slowest stage" on a step mean?

Rows in → out is what entered the step and what left it, with the share the step filtered away. Slowest stage gives the time and share of the step's slowest stage, so you can see where the step's time went.

Why does the card say some steps are not shown?

It keeps the most expensive steps so the table stays readable, and a line under it counts the rest as faster steps not shown.

What do the labels on a what-if candidate mean?

They say how the saving was obtained: measured when the database measured the kept data, measured on a sample when only part of the kept data was read, estimated for a statistical figure, and applicability only when the check answered only whether the index applies.

Why is a what-if candidate marked "Not applicable"?

The index could not be priced on that table, and the reason follows the label, for example that the chart has no filter on the table that an index could use. A whole table can also end Not applicable, Timed out, Failed or Skipped.

Why can a table read with FINAL not be estimated?

A table with a replacing column or an upsert load is read with FINAL, and how a skip index prunes under FINAL cannot be modelled accurately, so an estimate would be a guess. A real index still prunes there, so choose it from the plan and measure the chart after it is written; Skip indexes and Refreshing views walks through writing one.

Can I run query analysis on SQL from the Editor?

No. Query analysis does not run on the SQL you type in a console; it runs on a chart's query through the Model AI chat, as described above. While you write SQL, the Editor's own help is its syntax check and formatter: see Write and check SQL.