The reading room: what an answer is measured in

You Have the Answer, but What Did It Cost? Measuring Without a Stopwatch

18 min

What you'll learn

measure the price of an answer with three numbers: how many parts, rows, and marks had to be read
compare two queries that return the same answer at different prices, and explain why
not rely on a stopwatch when it measures database creation and data loading along with the query
read the columns and types of someone else’s table even when its full definition cannot be shown

10:15. Three numbers for March

March 27, 2184. The Academy reading room on Vault-9: a tall, bright tier, long tables, the issue window.

Three days ago, you finished your shift at the intake level. The night-shift pipeline runs without you now: the data arrives, and rerunning the load breaks nothing. Up here, the questions are different.

The curator places a sheet in front of you with three questions for March: how many visits were there, how many distinct visitors, and how many times did a title actually reach someone’s hands? The questions themselves are simple. You’ll find the answers quickly.

The problem is elsewhere. Two queries can produce the same answer, while one charges the reading room roughly twenty times the price of the other. So getting the right number is not enough. You also need to know the price the engine paid for it.

A stopwatch won’t help here. The cell timer starts BEFORE the database even exists: those milliseconds include creating the database and schema, as well as loading all seven tables. Using it to measure one query is like weighing a title together with the cart that delivered it.

So from this shift onward, you are responsible not only for the answer, but also for how much data had to be read to get it.

QUERY: Welcome back, archivist. Downstairs they asked whether the data arrived. Up here they ask how much — and then immediately: how much did you read to find out? The second question is mine. No number, no credit.

Above the table, the desk panel unfolds: eight empty cells, one for each chapter, and a three-column log: question → method → price. Both are empty for now.

By the end of this lesson, the first price will appear on the panel. By the end of the chapter, the first answer will.

The dark reading tier of the station: the archivist seen from behind at the issue window, before him a panel of eight empty answer cells and an open ledger ruled into three columns, the warden holding out a sheet with three questions; the planet stands beyond the bulkhead and there is no clock in the frame. — Three questions, no answers — and they will not be measured in time: there is not one clock in the hall.

One question — “how many distinct visitors were in the reading room on March 5” — and two ways to ask it. The answer should be the same. We’ll compare the last three numbers: they show how much data had to be read in each case. For now, focus on the result; we’ll unpack the course wrapper immediately after the cell.

The twenty-four March parts under two filters: the day column lights up a single part, a computation over the timestamp lights up all twenty-four. Both rows answer 174.

Three numbers instead of milliseconds

Both queries returned 174. But their prices differed: the first read 1172 rows, while the second read 23 565. The same question cost the engine roughly twenty times as much.

Why? Not because of the result, but because ClickHouse could discard irrelevant data up front in one case and had to read it in the other.

rr_events is split into daily partitions: each day’s events live in a separate part on disk. A filter on event_date matches the partitioning key. So the engine reads one part out of twenty-four and never even opens the rest.

A filter on toDate32(event_ts) asks the same question differently — through a calculation over a column. To decide whether a row qualifies, the engine first has to read it. So it opens twenty-four parts, touches twenty-four marks, and reads all 23 565 rows.

The price difference appears before the answer is even computed: the first query helps the engine narrow the read immediately; the second does not.

Four familiar terms. You already saw them in the chapter on columnar storage, and the rest of the course uses them in the same sense:

part — a separate portion of table data on disk;
granule — the smallest batch of rows inside a part that the engine can read (for most reading-room tables it is 8192 rows, for the dm_vydacha mart it is 1024);
mark — a marker at a granule boundary; marks let the engine jump inside a part without reading it from the beginning;
sorting key — the order in which rows are stored inside a part; marks follow that order, so a question aligned with the key is cheap, while one that cuts across it is expensive.

That gives us the measure we’ll use from here on: the price of an answer is three numbers: parts / rows / marks.

These numbers are more useful than time for another reason: they do not depend on how busy Vault-9 is or who else is reading from the room at that moment. Two runs of the same query give the same numbers. They do not necessarily take the same amount of time.

Required form

The price comes from EXPLAIN ESTIMATE, but we do not call it directly in the lesson.

The reason is technical: the first column of the result contains the name of the database where the cell ran. Every run creates a new database with a new name, so the raw result would change from run to run.

To keep only the stable part of the result, we always measure the price through a wrapper and explicitly select the columns we need:

SELECT parts, rows, marks FROM viewExplain('EXPLAIN ESTIMATE', '', ( ... ваш запрос ... ))

The result contains exactly the three numbers we need to compare queries: parts, rows, and marks.

There is one more trap. Sometimes the engine does not need to read any data at all — for example, if you ask only for the row count for one day and the table is partitioned by day. In that case, the wrapper returns nothing.

That is not an error. The answer came from about the parts, so the rows themselves did not need to be read. If you want to see the price of a read, include at least one actual column in the question.

One object at three scales: 24 parts, one per day; granules of 8192 rows inside each part; marks at their boundaries. The mart uses smaller granules of 1024 rows, so its marks are closer together.

We’ll need the reading-room mart in the final lesson. For now, let’s inspect its columns and types. The sandbox will not allow SHOW CREATE TABLE — that is a sandbox rule, not a ClickHouse limitation. So we’ll use an available but incomplete substitute.

What the sandbox blocks, and what replaces it

In this course, you work with a single server through a browser and without administrator privileges. That means some familiar tools are unavailable here.

This does not stop you from studying how ClickHouse works. Every blocked capability has a practical substitute, and the course uses that substitute all the way to the final lesson.

blocked	replacement	what you lose
`SHOW CREATE TABLE`	`DESCRIBE TABLE` — 12 rows, one per column, and 7 description columns, including compression and TTL	you cannot see the table engine, sorting key, or settings; you have to ask whoever created the table
system database with part	`uniqExact(_part)` and an array of parts directly in the query	you cannot see on-disk sizes or merge history
multiple machines	two local tables and `UNION ALL`	there is no network between machines, so you cannot observe its price — the most expensive part of a real distributed answer

It is important to understand the boundary of this substitution. It changes how you observe the system, not the engine itself.

Everything else is real: the same engine, the same version, the same errors word for word. Whenever the lesson uses a substitute, it says so explicitly — as it does here.

Interview question

How this comes up in interviews

A typical question is: “How would you tell that a query is reading more data than necessary?”

“Measure the execution time” is usually not enough. Time depends on cache state, other workloads on the server, and even which run of the query this is. It tells you how long one particular run took, but does a poor job of explaining why one query reads more than another.

A strong answer is based on read volume: name the three numbers from the plan — how many parts, rows, and marks had to be read — and explain how they change after filtering on the partitioning key.

In job descriptions and documentation, you’ll see the standard English terms for these concepts: part — part, mark — mark, granule — granule. Those are almost certainly the words an interviewer will use.

Check yourself

The lesson's cell shows an execution time. Why does the course not measure the price of an answer with it?

Key takeaways

question	method	price
How many distinct visitors were there on March 5?	filter on `event_date` — the table’s partitioning key	1 part / 1172 rows / 1 mark
Same question	filter on a calculation over the timestamp	24 parts / 23 565 rows / 24 marks

The answer is the same in both cases — 174 visitors. But the price differs by a factor of about twenty because the first filter matches the table’s partitioning key, while the second forces the engine to read every part.

That is the main point of the lesson: a correct answer is not enough. You need to understand how much data ClickHouse read on the way to it.

The first cell in the desk panel is no longer empty. It contains not an answer, but a measure that the course will use to evaluate the other seven.

QUERY: Accepted. Not because the number looks nice, but because you wrote down its price. It gets harder from here: the answers get longer, and the price learns how to hide.