Looking inside granules before building an index

A skipping index is a bet on how values spread across granules: if every granule holds every value, a set index skips nothing and still costs insert time. You normally find out after building it, which on a large table is a long mutation. I asked for a _granule virtual column so one could measure first, for example the 99th percentile of distinct values per granule, and size max_size of a set index from it.

The issue sat for nine months and closed on 2025-06-26. The fix was not written for me: PR #82341 by amosbird added _part_granule_offset, the zero-based granule index within a part, and shipped in 25.7. It is per part, so group by _part too:

CREATE TABLE e (a UInt32, country String, url String) ENGINE = MergeTree ORDER BY a
SETTINGS index_granularity = 1000;
INSERT INTO e SELECT number, ['TR','DE','US'][number % 3 + 1], concat('/p/', toString(number % 300))
FROM numbers(100000);

SELECT count() AS granules, quantile(0.99)(c) AS p99_countries, quantile(0.99)(u) AS p99_urls
FROM (SELECT _part, _part_granule_offset AS g, uniq(country) AS c, uniq(url) AS u
      FROM e GROUP BY _part, g);

100   3   300

Every granule holds all 3 countries and up to 300 URLs, so on this data a set index would be exact but skip nothing. Use intDiv(_part_granule_offset, 10) to simulate GRANULARITY 10. See Data skipping indexes and EXPLAIN which indexes a query uses.