Predicate Pushdown and Primary Key Skipping in MergeTree Queries

Predicate pushdown in columnar storage engine queries is a sequence of tests. Sparse primary indexes and skip indexes can rule out data before decompression, but granule boundaries set a floor on what remains. Understanding how these layers work together explains the gap between a query that reads all of the data and one that reads only a small fraction.

What MergeTree actually promises when you add a filter

A filter tells the engine which rows to return, but it does not guarantee that the engine can avoid reading the others. Before opening data blocks, the engine must prove that no row in them can match. If an index cannot give that proof, the data stays a candidate even if a match seems unlikely. Both the number of rows satisfying the filter and the amount of data the engine can rule out without reading it matter.

A granule is the smallest unit the engine can skip, so even a filter matching one row can require a full chunk to be read. Keys and indexes matter because they help discard those chunks.

How the sparse primary index eliminates granules before anything else runs

The primary index is the first and cheapest opportunity to discard data. Stored in primary.idx, it records one entry per granule. Each entry holds the minimum sort-key value at the start of the granule, which lets the engine find ranges that cannot match. When a range is provably outside the predicate, the engine can skip it without opening a data file. This is what makes the index sparse, and also what limits its precision.

Consider rows sorted by (tenant_id, event_date). Filtering on tenant_id can remove long, contiguous runs of granules belonging to other tenants. A filter on event_date alone does not cover one unbroken stretch of that ordering, because dates start over within each tenant’s run. The second column can help eliminate data within runs where the first column is constant, but it does not behave like the leading column. A column absent from the sort key gets no help from this layer at all.

Lower-cardinality columns first create broad runs, while later columns allow finer elimination within them. A table can also declare a PRIMARY KEY shorter than its ORDER BY, trading coarser elimination for a smaller index and faster key lookup. That can suit a table whose full sort key is needed for merge correctness but whose filters use only the leading columns. In either case, the index can discard only ranges that its recorded key values prove irrelevant.

What skip indexes add, and what each type can and cannot skip

Skip indexes give columns outside the primary key another chance to eliminate data. Each stores a summary over a block of granules, but the summary must answer the question the predicate asks. An index that cannot resolve that predicate adds lookup work without avoiding data reads. The important distinction is not whether an index exists, but whether its contents can prove that a block contains no match.

minmax records the smallest and largest values in a block. It serves range predicates such as <, > and BETWEEN on numeric and date columns, and is the simplest and cheapest option, with little write cost. By contrast, set(N) records up to N distinct values and serves equality and IN predicates on low-cardinality columns. If a block contains more than N distinct values, that summary degrades to “cannot skip”.

bloom_filter serves equality checks on higher-cardinality strings through a probabilistic membership summary with a tunable false-positive rate. It cannot eliminate blocks for range predicates or negation predicates such as NOT IN and NOT LIKE. tokenbf_v1 provides a token-based bloom filter for substring and full-text search, but negation is a trap there too. Equality and IN work across the index types, while range operators are useful with minmax and the primary index.

The block size introduces a separate trade-off. GRANULARITY N groups N primary-index granules into one skip-index block, so larger values mean fewer lookups but coarser elimination. IBM Instana used tokenbf_v1(10240, 3, 0) GRANULARITY 4 on lowerUTF8(http_url) for case-insensitive search. Its GRANULARITY 1 lookups could take up to a few seconds, while GRANULARITY 4 brought them within a second. Finer summaries were not automatically faster.

How a predicate actually travels through all three layers at query time

Consider a filter that restricts a tenant and searches for a URL token. The engine first reads primary.idx, which fits in memory, then evaluates suitable skip indexes for the surviving blocks, then reads the remaining granules. Later layers cannot send that work back to help an earlier layer rule out more data. Failure to eliminate data at the primary index leaves the most work for everything that follows.

The tenant condition can first discard key ranges belonging to other tenants. For what remains, a compatible token index can rule out blocks whose summary proves that the requested token is absent. IBM Instana’s index on lowerUTF8(http_url) illustrates this second step, removing blocks before decompression and retaining those that plausibly contain the URL. A surviving block is one the index could not reject.

The engine then decompresses and reads each surviving granule in full, evaluates the predicate in-process, and returns matching rows. Large reductions depend on the layers having useful evidence to act on, with the primary index removing ranges and skip indexes narrowing the remainder. If the primary index cannot use a filter and no suitable skip index exists, even a filter with high logical selectivity can lead to a full scan.

Why granule boundaries are the ceiling on how much data you can avoid reading

What happens when the indexes have eliminated everything they possibly can? Every surviving granule still has to be read and decompressed, including rows that do not match. The remaining waste can be the unavoidable cost of reading in blocks.

Smaller granules offer finer skipping and are worth considering for highly selective point lookups. However, they also enlarge the primary index and give skip indexes more entries to evaluate, trading read precision against index size and write overhead. Adaptive granularity, enabled by default, adjusts boundaries according to uncompressed byte size. That helps when row widths vary widely, while preserving the granule floor.

An ORDER BY that groups useful rows for one query pattern can work poorly for another, and secondary indexes cannot make up for all of that. The unfavored query still pays for the granules that survive. Changing the production table’s ordering takes a full data rewrite, so this trade-off needs to be weighed when the table is designed and will be costly to change later.

Designing ORDER BY and skip indexes so the engine eliminates the most data

Start with the filters that should remove the largest runs of data. Place the lowest-cardinality column first, then higher-cardinality columns for finer elimination within those runs. For a multi-tenant event table where most queries filter by tenant first, (tenant_id, event_type, event_date) is the example shape. Because tenant_id has low cardinality across rows, it forms long runs that the engine can skip at once.

For secondary access patterns beyond ORDER BY, add an index that answers the actual predicate. A frequently filtered high-cardinality column, or one that is not the primary filter, can call for bloom_filter or minmax, depending on whether the query asks about membership or ranges. Adding that column to the ordering can disrupt the cardinality sequence and weaken primary-index effectiveness. Every index is maintained on inserts and merges, so an unused index charges for work without delivering a read benefit.

Tune skip-index GRANULARITY against the lookup overhead the workload can tolerate. IBM Instana’s result with 4 supports that workload-specific trade-off reasoning. Once an index is added to a table with existing data, run ALTER TABLE … MATERIALIZE INDEX explicitly. Declaring the index does not automatically backfill it. Existing parts remain unindexed until materialization runs or background merges eventually cover them, which makes a correct definition an incomplete deployment.

The join pushdown regression that broke predicate elimination across query boundaries

Even with a correct schema, the engine can fail to rule out data if the predicate never reaches the table scan. That failure sits before the index tests, at the boundary between query planning and data access. A join pushdown regression provides a concrete example. The indexes remained available, but the planner stopped delivering the filters they needed. Index design alone could not protect the query.

PR #114313 set both right- and left-stream filter-pushdown columns to unavailable for the affected ANY INNER JOIN path. Only predicates over equi-join keys still reached the table sides, while other filters fell through to full granule reads. A synthetic reproduction measured a meaningful slowdown, and a production query on the same cluster showed a severe regression. PR #120413 / #120765 resolved the issue, was merged into master and targeted the current development line. The evidence was limited to this specific join failure.

The documented mitigation was to rewrite the left filter into a subquery. That made the predicate apply before the join rather than requiring it to cross the join boundary, restoring granule elimination on the inner table. Pushdown depends on an unbroken path from the query to the indexes. When that path breaks, query time and disk reads can rise without a visible error. SHOW CREATE TABLE can still show an apparently correct set of indexes.

Operational signals that tell you whether your indexes are actually working

The decisive evidence is how many granules a query reads and how many it skips. Those query-level statistics turn schema choices into observable behavior. If a leading-key filter leaves most granules in play, the expected primary-index elimination is not materializing. If a secondary filter skips nothing, check whether an index covers it, whether that index works with the predicate and whether the parts have been populated. Latency alone cannot distinguish those mechanisms.

Zero compressed bytes are a warning that an index was never built or is not doing useful work. After ALTER TABLE … MATERIALIZE INDEX, verify that existing parts show the index populated before assuming it is active. Also check query logs for NOT IN and NOT LIKE on bloom-filtered columns. An evaluated index with zero granules skipped exposes a predicate form that defeats elimination, while an index never consulted brings write and merge overhead without a read benefit.

These signals matter especially for agentic and high-frequency query workloads. Agents probing data in parallel with many distinct filters can exercise index types very differently from predictable human queries. An index tuned for familiar query shapes may thus do little to help with agent-generated SQL. Isolated, read-only compute with full query logging lets platform teams see which generated predicates lack index coverage before the problem is visible only as degraded latency. The final test of predicate pushdown is not whether a filter or index is present, but whether the engine can use it to avoid reading granules.