When does a ClickHouse data-skipping index such as minmax or bloom_filter actually help?

## What a skipping index is ClickHouse has exactly one ordering index — the sparse primary index over the sorting key. Data-skipping indexes are the secondary mechanism: small per-block summaries that let the engine discard granules a predicate cannot match, *after* partition pruning and primary-index analysis have already run. They are declared inside the table definition: ```sql ALTER TABLE events ADD INDEX idx_status status TYPE set(20) GRANULARITY 4, ADD INDEX idx_uid user_id TYPE bloom_filter(0.01) GRANULARITY 1; ``` `GRANULARITY n` is the part everyone gets wrong on first contact: it is **not** rows. It is the number of index granules (blocks of `index_granularity` rows, 8192 by default) covered by one skipping-index entry. `GRANULARITY 4` at the default row granularity means one summary per 32,768 rows. A smaller value gives finer pruning and a larger index; a larger value gives a tiny index that prunes coarsely. Adding an index to an existing table only affects newly written parts. Existing parts need `ALTER TABLE ... MATERIALIZE INDEX idx_name`, which rewrites index files in the background. ## The types and what they fit - **`minmax`** — stores the minimum and maximum of the expression per block. Cheapest of all, and the right choice for anything correlated with the sort order: an ingest timestamp when the table is sorted by date, a monotonic sequence id, a derived coarse bucket. Also handles range predicates, which the other types cannot. - **`set(max_rows)`** — stores up to `max_rows` distinct values per block; if a block exceeds that, the index gives up for that block and it is always read. Good for low-cardinality categorical columns — status, country, event type — used with `=` or `IN`. - **`bloom_filter([false_positive_rate])`** — a probabilistic membership filter per block. Handles equality and `IN` on higher-cardinality columns where a `set` would overflow. False positives cost you an unnecessary block read; false negatives are impossible, so results stay correct. - **`tokenbf_v1(...)` and `ngrambf_v1(...)`** — Bloom filters over tokens or n-grams of a string, for `LIKE '%needle%'` and `hasToken` style predicates on text columns. ## The condition that decides everything: clustering A per-block summary can only exclude a block if the block's values are *narrow* relative to the predicate. That is a property of the physical layout, not of the index type. If `user_id` values are shuffled uniformly across the table because the sorting key is `(tenant_id, event_date)` and users interleave freely, then almost every 32,768-row block contains at least one row for almost any popular user id — a Bloom filter over that block says "maybe" every time, and you read the whole table plus the index. The same index on the same column becomes powerful the moment the data is clustered: if events for a user arrive in bursts, or the sorting key ends with something correlated with `user_id`, blocks become homogeneous and the filter starts excluding most of them. So the honest checklist before adding one is: 1. Is the predicate column outside the sorting-key prefix (otherwise the primary index already handles it)? 2. Are its values physically clustered under the current sort order? 3. Is the predicate shape supported by the type you're choosing — ranges need `minmax`, equality can use `set` or `bloom_filter`? If the answer to 2 is no, the correct fix is a different sorting key or a second sorted copy of the data, not an index. ## Measuring instead of guessing `EXPLAIN indexes = 1` prints each index-analysis step with the parts and granules surviving it, so you can see directly whether the skipping index removed anything beyond what the primary index already removed. `system.data_skipping_indices` reports each declared index with its type, expression, granularity and on-disk size, which is how you find indexes that cost storage and contribute nothing. During investigation, the setting `force_data_skipping_indices` makes a query fail unless the named index is usable, which is a blunt but effective way to catch a query that silently stopped using one. ## Costs Skipping indexes are not free. Every insert computes and writes their summaries, merges rewrite them, they occupy disk, and index analysis itself takes time proportional to the number of index blocks. A Bloom filter with a very low false-positive rate over a high-cardinality column can grow large. Several unused indexes on a hot ingest table are pure write amplification. The default assumption should be zero skipping indexes, each one added because a specific query shape measurably improved. ## Interview traps The two answers that end the topic are "it's like a secondary index, so filters on that column become fast" and "add a Bloom filter on any column people filter by". Both miss the clustering condition. The strong answer says: summaries per block of granules, pruning only when values are clustered under the sort order, `GRANULARITY` counted in granules not rows, and measurement with `EXPLAIN indexes = 1` before and after.