ClickHouse skipping indexes: bloom, set, minmax

Secondary (Skipping) Indexes in ClickHouse: When ORDER BY Index Is Not Enough

1. How Skipping Indexes Work — Skipping Data Blocks

In traditional databases (PostgreSQL, MySQL), an index is a structure that points exactly to the rows satisfying a condition. A B-tree says: "Value user_id = 123 is in row #45678".

In ClickHouse, the primary index (sparse index by ORDER BY) works differently. It stores values only for every 8192nd row (granule) and can efficiently prune entire blocks of data for columns that appear early in the ORDER BY.

But what if you need to search by a column that is not in ORDER BY? For example, you want to find all bets with a specific IP address, but your ORDER BY is (user_id, created_at). ClickHouse will have to read all granules and filter IP after reading. This is called a full scan.

Secondary (skipping) indexes solve this problem. They do not point to specific rows but say: "In this block of N granules, there is definitely no such value — you can skip it." If the index says "maybe" — ClickHouse still reads the block.

Real-world analogy: Imagine you are looking for a book with a green cover in a library. The main index (catalog by author's last name) does not help. But you walk past shelves and quickly glance: "All books on this shelf are blue — skip. This shelf has green ones — I'll check." A skipping index is like color-coded shelves, not a precise pointer.

Why are they called skipping? Because the main job of the index is to skip blocks that are definitely not needed. The more blocks skipped, the faster the query.

Important limitation: Skipping indexes work only at the granule level. They cannot find the exact row position within a granule. Therefore, the index is useful when the desired value is rare (low selectivity). If 80% of rows match the condition — you'll still have to read everything.

2. INDEX ... TYPE minmax — For Range Queries

The simplest skipping index is minmax. It stores the minimum and maximum value of a column for each group of granules.

CREATE TABLE player_events
(
    user_id     UInt64,
    event_time  DateTime,
    amount      Decimal(18,2),
    outcome     String      -- 'win', 'loss', 'push'
)
ENGINE = MergeTree()
ORDER BY (user_id, event_time)              -- primary order
INDEX idx_outcome_minmax outcome TYPE minmax GRANULARITY 4;

Breaking down the parameters:

• INDEX idx_outcome_minmax — index name (choose your own, but make it meaningful).
• outcome — column on which the index is built.
• TYPE minmax — index type: stores min and max values in a group of granules.
• GRANULARITY 4 — how many granules (each 8192 rows) are combined into one group for the index. Here 4 × 8192 = 32768 rows per index entry.

How it works in a query:

-- Find events with a specific outcome
SELECT * FROM player_events 
WHERE outcome = 'win' AND event_time >= '2025-06-01';

ClickHouse reads the idx_outcome_minmax index:

• Group 1: min='loss', max='push' → no 'win' → skip 32768 rows.
• Group 2: min='loss', max='win' → contains 'win' → read this group.
• Group 3: min='win', max='win' → only 'win' → read.

When minmax is effective:

• Columns with monotonic changes (time, ID, temperature).
• Columns with few unique values but unevenly distributed.
• Range queries (BETWEEN, >=, <=).

When it is useless:

• Random values (e.g., hash, UUID). Min and max will cover the entire range, the index will not skip anything.

3. INDEX ... TYPE set — For Equality on Low-Cardinality Columns

A set index stores unique values for a group of granules. If the searched value is not in this set — the group is skipped.

CREATE TABLE bets
(
    user_id     UInt64,
    sport_id    UInt8,      -- only 20 sports
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, user_id)
INDEX idx_sport sport_id TYPE set(10) GRANULARITY 2;

Parameters:

• set(10) — the maximum number of unique values the index will store for a group. If a group contains more than 10 unique sport_id values, the index will only remember 10 (and may cause a false positive). Choose a number slightly larger than the expected column cardinality.

How it works:

-- Query for a specific sport
SELECT sum(amount) FROM bets WHERE sport_id = 1;

The idx_sport index knows for each group of granules which sport_id values occur. If a group does not contain sport_id=1 — skip the entire group. If it does — read it.

When set is effective:

• Column cardinality is low (up to hundreds of values).
• Equality queries (=, IN).
• Data is well grouped within granules (e.g., all football bets for an hour are stored compactly).

Gambling example: A bets table with ORDER BY (created_at, user_id). Column sport_id (20 values) is not in ORDER BY. A set index on sport_id allows quickly finding all hockey bets without scanning everything.

4. bloom_filter Index — For High-Cardinality String Columns

Bloom filter is a probabilistic data structure. It can say "the value is definitely not in the group" or "the value might be present." It never says "definitely present" — it can only err on the side of false positives.

CREATE TABLE player_events
(
    user_id     UInt64,
    ip_address  String,          -- millions of unique IPs
    event_type  String,
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
INDEX idx_ip ip_address TYPE bloom_filter(0.01) GRANULARITY 3;

Parameters:

• bloom_filter(0.01) — false positive rate of 1%. The smaller the number, the more accurate the index, but it takes more space. Typically 0.01 (1%) or 0.001 (0.1%) is used.
• GRANULARITY 3 — 3 granules (3 × 8192 = 24576 rows) per index entry.

How it works:

-- Find all events from a suspicious IP
SELECT * FROM player_events WHERE ip_address = '192.168.1.100';

The index for each group of granules checks via bloom filter: "Could this group contain IP=192.168.1.100?" If "no" — the group is skipped. If "yes" (including false positives) — the group is read.

When bloom filter is effective:

• Columns with high cardinality (IP addresses, email, user_agent).
• Exact match queries.
• Searched values are rare (e.g., a specific IP out of 10 million).

Why minmax is not suitable for IP: Due to random distribution, min and max IP in a group will cover almost the entire range, so pruning won't work.

Real-world example — multi-account detection (one IP, many user_ids):

-- Find all users from a given IP
SELECT DISTINCT user_id FROM player_events 
WHERE ip_address = '192.168.1.100';

Without an index — full scan. With bloom_filter on ip_address — fast, even if the IP appears in 0.1% of rows.

5. ngrambf_v1 — For LIKE/ILIKE Search on Strings

Sometimes you need to search by a substring: WHERE player_name LIKE '%John%'. Regular indexes do not help because % at the beginning prevents B-tree usage.

ngrambf_v1 splits the string into n-grams — substrings of length N. For example, for N=3, 'Johny' → 'Joh', 'ohn', 'hny'. The index builds a bloom filter on these n-grams.

CREATE TABLE players
(
    player_id   UInt64,
    player_name String,
    country     String
)
ENGINE = MergeTree()
ORDER BY player_id
INDEX idx_name player_name TYPE ngrambf_v1(3, 500000, 2, 0.01) GRANULARITY 4;

Parameters of ngrambf_v1:

• 3 — n-gram length (usually 2–4). Larger means more accurate but more memory.
• 500000 — bloom filter size in bytes per index entry.
• 2 — number of hash functions (usually 2–4).
• 0.01 — false positive probability.

How to use in a query:

-- Find players with a name containing 'Alex'
SELECT * FROM players WHERE player_name LIKE '%Alex%';

The index splits 'Alex' into n-grams ('Ale', 'lex') and checks if these n-grams exist in groups. If a group has none of these n-grams — the group is skipped.

Limitations:

• Works only with LIKE and ILIKE (case-insensitive).
• Requires the search string to be longer than the n-gram (at least 3 characters).
• Not suitable for short strings (e.g., 'a').

When to use: Searching by player nicknames, partial email, addresses. In gambling — finding a player by part of their name for customer support.

6. tokenbf_v1 — For Token (Word) Search

tokenbf_v1 is similar to ngrambf_v1, but splits the string not into overlapping pieces, but into tokens — words separated by spaces, punctuation, digits.

CREATE TABLE logs
(
    log_time    DateTime,
    message     String,
    user_agent  String
)
ENGINE = MergeTree()
ORDER BY log_time
INDEX idx_msg message TYPE tokenbf_v1(500000, 2, 0.01) GRANULARITY 2;

Parameters of tokenbf_v1:

• 500000 — bloom filter size in bytes.
• 2 — number of hash functions.
• 0.01 — false positive probability.

How it works:

For the string "User 123 logged in from Ukraine" tokens: 'User', '123', 'logged', 'in', 'from', 'Ukraine'.

-- Find all logs mentioning an error
SELECT * FROM logs WHERE message LIKE '%error%';

The index splits 'error' into tokens (just 'error') and checks for the presence of this token in groups.

When tokenbf_v1 is better than ngrambf_v1:

• Searching for whole words (not parts).
• English texts, logs, user_agent.
• Fewer false positives than ngrambf_v1.

Gambling example: Searching bet logs for messages containing 'fraud' or 'suspicious'.

7. How to Check That an Index Is Used — EXPLAIN indexes=1

You created an index, but does it work? ClickHouse provides the EXPLAIN indexes = 1 command.

-- Enable index usage analysis
EXPLAIN indexes = 1
SELECT user_id, amount FROM bets 
WHERE sport_id = 1 AND created_at >= '2025-06-01';

Example output:

Expression
  ...
  ReadFromMergeTree
    Indexes:
      PrimaryKey
        Condition: (created_at >= '2025-06-01')
        Used keys: (created_at)
        Granules: 150 / 12000
      Skip
        Name: idx_sport
        Type: set
        Condition: sport_id = 1
        Granules: 80 / 12000

What the numbers mean:

• Granules: 150 / 12000 — primary key pruned 11850 granules, 150 remain.
• Skip ... Granules: 80 / 150 — skipping index further pruned 70 granules, 80 remain.
• Final gain: 12000 → 80 granules read.

If the index is not used:

• Not shown in the Skip section → either not created, or the query does not match the index type.
• Granules: 12000 / 12000 — reading everything, index did not help.

Why an index might not be used:

• Index type does not match the operator (minmax for = is ineffective).
• Granularity is too large (coarse index).
• The searched value appears almost everywhere (index cannot skip blocks).

8. When Skipping Indexes Do NOT Help

Scenario 1: High cardinality + random distribution

If the column user_id (millions of values) and ORDER BY does not start with user_id, a skipping index (even bloom_filter) will poorly prune blocks. Because value user_id=123 may be scattered across the entire table.

Scenario 2: Query without filtering on "good" columns

Indexes on sport_id will not help if WHERE has only amount > 1000 and there is no index on amount.

Scenario 3: Too large GRANULARITY

If GRANULARITY = 64 (524k rows per group), and your table has 10 million rows, there will be only ~20 groups. You can skip only 20 blocks, which is negligible.

Scenario 4: The searched value appears in 50%+ of rows

Skipping indexes are good for rare values. If half the rows match the condition, indexes will say "maybe" for almost all blocks, and you will read everything.

Scenario 5: Index is too small

-- Bad: too small bloom filter (10000 bytes)
INDEX idx_ip ip_address TYPE bloom_filter(0.01) GRANULARITY 4;

A small bloom filter gives many false positives (often says "maybe" when it is not). The index stops skipping blocks.

9. Cost of Skipping Indexes — Memory and Insert Speed

Every index has a cost. Do not create indexes "just in case."

Cost #1: Additional disk space

• minmax — very cheap (8 bytes per group per column).
• set(100) — more expensive, but within thousands of bytes per group.
• bloom_filter — expensive: at 500k bytes and GRANULARITY=1, for a table with 10k groups = 5 GB just for the index.

Cost #2: Slower INSERT

On each insert, ClickHouse updates all indexes for each granule. 5 indexes on a table can slow down inserts by 2-3 times.

Rule of thumb:

• No more than 2-3 skipping indexes on a large table (billions of rows).
• Indexes only on columns that are frequently filtered.
• For test workloads — experiment. For production — measure.

How to estimate index cost:

-- Check index sizes in a table
SELECT 
    table,
    index_name,
    formatReadableSize(index_size) AS size
FROM system.indexes
WHERE table = 'bets';

If the index size is close to the data size — you might have overdone it.

10. Real Example: Fraud Detection by IP Address

Imagine in your casino a group of players uses one IP address for multi-accounting (against the rules). You need to find everyone who logged in from a suspicious IP.

Events table:

• 500 million rows.
• ORDER BY = (user_id, event_time) — fast queries by user.
• Frequent query: SELECT user_id FROM events WHERE ip_address = 'x.x.x.x'.

Solution — bloom_filter index:

CREATE TABLE player_events
(
    user_id     UInt64,
    event_time  DateTime,
    ip_address  String,
    event_type  String,   -- 'login', 'bet', 'withdraw'
    amount      Decimal(18,2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, event_time)
INDEX idx_ip ip_address TYPE bloom_filter(0.01) GRANULARITY 4;

Performance comparison:

Scenario	Without index	With bloom_filter (0.01)
Query time for rare IP (0.001% rows)	60 seconds (full scan 500M)	0.3 seconds
Query time for frequent IP (5% rows)	60 seconds	45 seconds (index helps little)
Table size (compressed)	100 GB	108 GB (+8%)
INSERT time (10k rows/sec)	0.5 ms per batch	0.7 ms per batch (+40%)

How to write an anti-fraud query:

-- Find all users who ever used a suspicious IP
SELECT DISTINCT user_id 
FROM player_events 
WHERE ip_address = '192.168.1.100'   -- bloom_filter helps
AND event_time >= today() - 30;      -- partitions prune old data

-- Then check how many different accounts use this IP
SELECT count(DISTINCT user_id) AS suspicious_accounts
FROM player_events 
WHERE ip_address = '192.168.1.100';

Why bloom_filter, not minmax:

• IP addresses are randomly distributed; min/max in a group will almost always cover the entire range.
• Bloom filter is ideal for set membership checks.

What's Next

Now you know all types of ClickHouse secondary indexes. Next topics:

• Combining indexes — how multiple skipping indexes work together.
• Tuning granularity — how to choose the optimal granule size for different data types.
• Indexes in distributed tables — how skipping indexes work in a cluster.

Bottom line: Skipping indexes in ClickHouse are not a silver bullet. They do not work like B-trees in PostgreSQL. But for the right scenarios (rare values, bloom filter, n-grams) they turn full scans into lightning-fast queries. Key rules:

• Do not create indexes until you see a problem (full scan).
• Start with bloom_filter for high-cardinality columns, set for low-cardinality ones.
• Always check with EXPLAIN indexes = 1.
• Remember the cost: disk space + INSERT slowdown.

---

← Previous: Materialized Views in ClickHouse: The Power of Incremental Processing

— Editorial Team