Count skipped granules while changing sort-key order

Goal

You put the same 2 million rows into several tables with different sorting keys and confirm with the granule counts of EXPLAIN indexes = 1 and with rows_read what the sparse primary index skips. At the end you choose for yourself the sorting key that fits a given query.

Why it matters

The sorting key of a ClickHouse table is hard to change after creation, and if you choose it wrongly, it reads everything every time even though there is an index. Which column goes first is decided not by feel but by "how many granules does this query pick". This lab's grader does not trust the numbers you wrote — it attaches EXPLAIN to the SELECTs you saved and re-runs them read-only, and runs the same SELECT again and measures the rows read to compare.

Steps

1. Create the database sparse and the table sparse.hits — columns site LowCardinality(String), user_id UInt32, ts DateTime, dur_ms UInt32 (in this order), engine MergeTree, sorting key ORDER BY (site, user_id).
2. Run /opt/lab/fixtures/sparse/hits.sql once to insert 2 million rows, and merge the parts into one with OPTIMIZE TABLE sparse.hits FINAL.
3. Create /root/ch/sparse/q_site.sql, which sums dur_ms for rows with site = 'docs.example', save the output of that SELECT with EXPLAIN indexes = 1 attached to /root/ch/sparse/explain_site.txt, and write the chosen/total granules and the search method of the PrimaryKey step to /root/ch/sparse/granules.json as selected, total and search.
4. Create /root/ch/sparse/q_user.sql, which sums dur_ms for rows with user_id = 4242, and write the chosen granules and search method from EXPLAIN, together with this query's rows_read, to /root/ch/sparse/user.json as selected, search and rows_read.
5. Create sparse.hits_us with the same columns but only the sorting key as ORDER BY (user_id, site), move the rows of sparse.hits into it and merge the parts into one. On that table, create /root/ch/sparse/q_user_us.sql, which sums for user_id = 4242, and /root/ch/sparse/q_site_us.sql, which sums for site = 'docs.example', and write the rows_read of the two queries to /root/ch/sparse/order.json as user_rows_read and site_rows_read.
6. Create sparse.hits_g1k with the same columns and sorting key as sparse.hits and SETTINGS index_granularity = 1024, move the rows and merge. Write the marks and primary_key_size of that table (system.parts) and the rows_read of /root/ch/sparse/q_user_g1k.sql, which sums for user_id = 4242, to /root/ch/sparse/granularity.json as marks, primary_key_size and user_rows_read.
7. Create sparse.hits_pk, with the sorting key ORDER BY (site, user_id, ts) and the primary key PRIMARY KEY (site, user_id) set separately, and sparse.hits_full, with the same sorting key and no PRIMARY KEY written, move the rows and merge. Write the primary_key_size of the two tables to /root/ch/sparse/pk.json as hits_pk and hits_full.
8. Create /root/ch/sparse/q_day_hits.sql, which sums the dur_ms of the one-day site report WHERE site = 'api.example' AND ts >= '2026-09-10 00:00:00' AND ts < '2026-09-11 00:00:00' on sparse.hits, and /root/ch/sparse/q_day.sql, which does so on sparse.hits_day (same columns, same rows, one part) that you create with a sorting key of your choice. The rows_read of q_day.sql must be a quarter or less of that of q_day_hits.sql. Write the two values to /root/ch/sparse/day.json as hits_rows_read and day_rows_read.

Notes

• The server is already running when the Pod starts (127.0.0.1:9000). If it has stopped, run ch-up.
• Saving EXPLAIN: clickhouse-client -q "EXPLAIN indexes = 1 $(cat q_site.sql)" > explain_site.txt. Look at Granules: 고른/전체 inside the PrimaryKey block (selected/total; do not confuse it with the number in the ReadFromMergeTree line above).
• Measuring rows read: clickhouse-client --use_query_condition_cache 0 --queries-file q_user.sql --format JSON | jq .statistics.rows_read. Be sure to turn off the query condition cache — 26.8 has it on by default, so when you run the same condition a second time, you get a value with fewer rows read. The grader measures with it off too.
• You can copy just the columns and give a new sorting key with CREATE TABLE 새표 AS sparse.hits ENGINE = MergeTree ORDER BY (...) (the placeholder stands for the new table name). For the rows, INSERT INTO 새표 SELECT * FROM sparse.hits.
• Common mistake: measuring without merging (no OPTIMIZE FINAL) — with several parts, granules are counted per part and the numbers differ. If you write in PRIMARY KEY a column that is not a prefix of the sorting key, the table is not created.
• Official docs: A practical introduction to primary indexes · Primary indexes · Choosing a primary key · MergeTree · EXPLAIN · system.parts

A table with the low-cardinality column first

Create the database sparse and the table sparse.hits. The columns are, in order, site LowCardinality(String), user_id UInt32, ts DateTime, dur_ms UInt32, the engine is MergeTree, and the sorting key is ORDER BY (site, user_id).

It is two statements, CREATE DATABASE and CREATE TABLE. site has five values and user_id has 50,000 — an order with the column with fewer distinct values first. If the column names, types or order differ, the source script in the next step will not load.

Insert 2 million rows and make one part

Run /opt/lab/fixtures/sparse/hits.sql once to insert 2,000,000 rows into sparse.hits, and make the active part one with OPTIMIZE TABLE sparse.hits FINAL.

A single INSERT can create several parts depending on block size. If there are several parts, EXPLAIN counts granules per part, so merge them into one before comparing. If there are 4 million rows, you inserted twice — TRUNCATE and again.

Filtering by the first key column — binary search

Create /root/ch/sparse/q_site.sql, which sums dur_ms for rows with site = 'docs.example', and save the output of that SELECT with EXPLAIN indexes = 1 attached to /root/ch/sparse/explain_site.txt. Write Granules: 고른/전체 (selected/total) and Search Algorithm of the PrimaryKey step to /root/ch/sparse/granules.json as selected, total and search.

Granules appears twice in the EXPLAIN output — the line right under ReadFromMergeTree is the final result, and what this step asks for is the number in the PrimaryKey block under Indexes. Copy the search method string exactly, including spaces. The first key column is sorted, so it can find the start and end of the range directly.

Filtering only by the second key column

Create /root/ch/sparse/q_user.sql, which sums dur_ms for rows with user_id = 4242. Write the number of granules chosen and the search method from the PrimaryKey step of EXPLAIN, together with this query's statistics.rows_read run with --use_query_condition_cache 0, to /root/ch/sparse/user.json as selected, search and rows_read.

user_id is sorted again from the start in each site lump, so as a whole it is not sorted. So instead of binary search, it uses a method that rules out only the intervals in which the earlier column's value does not change between neighboring marks. Connect the fact that there are five site lumps with the number of granules chosen. rows_read comes out near chosen granules × 8192.

A table with the key order reversed

Create sparse.hits_us with the same columns but only the sorting key as ORDER BY (user_id, site), move the rows of sparse.hits into it and merge with OPTIMIZE ... FINAL. On that table, create /root/ch/sparse/q_user_us.sql, which sums dur_ms for user_id = 4242, and /root/ch/sparse/q_site_us.sql, which sums for site = 'docs.example', and write the rows_read of the two queries to /root/ch/sparse/order.json as user_rows_read and site_rows_read.

With CREATE TABLE ... AS sparse.hits ENGINE = MergeTree ORDER BY (...), you can copy the columns and give only the sorting key anew. If about 200 users sit inside one granule of 8192 rows, can the first column user_id be the same between two neighboring marks? If not, you cannot rule out any interval with the later column site.

If you cut granules down to 1024 rows

Create sparse.hits_g1k with the same columns and sorting key as sparse.hits and SETTINGS index_granularity = 1024, move the rows and merge. Write the marks and primary_key_size from system.parts, and the rows_read of /root/ch/sparse/q_user_g1k.sql, which sums for user_id = 4242, to /root/ch/sparse/granularity.json as marks, primary_key_size and user_rows_read.

The SETTINGS clause goes after ORDER BY. When granules get smaller, even if the number of granules the same condition picks is similar, the rows read per granule shrink. In exchange, marks increase and the index file (primary_key_size) grows — since the design keeps the index in memory, this is the cost. marks is one more than the number of granules.

Make PRIMARY KEY a prefix of the sorting key

Create sparse.hits_pk, with the sorting key ORDER BY (site, user_id, ts) and PRIMARY KEY (site, user_id) written separately, and sparse.hits_full, with the same sorting key and no PRIMARY KEY written, move the rows into each and merge. Write the system.parts primary_key_size of the two tables to /root/ch/sparse/pk.json as hits_pk and hits_full.

If you do not write a PRIMARY KEY, the whole sorting key becomes the primary key. When you write one separately, it must be a prefix of the sorting key — if you write something that is not a prefix, like (user_id), the table is not created. The sort order is the same, so the difference in file size comes from the number of columns written in the index.

Choose the sorting key that fits the query

Make two queries that sum dur_ms under the one-day site report condition site = 'api.example' AND ts >= '2026-09-10 00:00:00' AND ts < '2026-09-11 00:00:00' — /root/ch/sparse/q_day_hits.sql, which reads sparse.hits, and /root/ch/sparse/q_day.sql, which reads sparse.hits_day (same columns, all the rows of sparse.hits, one part) that you create with a sorting key you choose. Choose the key so that the rows_read of q_day.sql is a quarter or less of that of q_day_hits.sql, and write the two values to /root/ch/sparse/day.json as hits_rows_read and day_rows_read.

sparse.hits narrows by site, but inside that it is in user_id order, so the ts range does not hit the index. Recall which column in the condition comes with an equals sign and which with a range, and what the earlier column must be like for the second column to hit the index (step 4). After choosing, check with EXPLAIN first.