A materialized projection silently disables the query condition cache — repeated selective queries get slower than with no projection at all

[PedroTadim]

Summary

When a query is served from a materialized projection, the query condition cache is consulted
but never populated. Every repetition of the same selective query re-reads the same marks
instead of pruning them, so the cache never takes effect for that query.

The effect is worse than "no speedup": because the projection opts the query out of the cache,
a repeated query on a table with a projection can be slower than the same query on the same
table without one.

Adding the projection is enough — no delete, no mutation, no restart.

Reproduction

Self-contained; the final query reports the ProfileEvents for all six runs.

DROP TABLE IF EXISTS t_qcc_proj;

CREATE TABLE t_qcc_proj (pk UInt64, a UInt32, b UInt32)
ENGINE = MergeTree ORDER BY pk
SETTINGS index_granularity = 64;

-- `b` holds only even values, so the odd needle below sits inside [min, max] but is absent:
-- neither the base table's primary key nor the projection's can prune it away.
INSERT INTO t_qcc_proj
SELECT number, toUInt32(number % 1000), toUInt32(number % 100000) FROM numbers(400000);

-- three identical runs WITHOUT a projection
SYSTEM DROP QUERY CONDITION CACHE;
SELECT count() FROM t_qcc_proj WHERE b = 99999 SETTINGS use_query_condition_cache = 1, log_comment = 'qcc_1_no_projection';
SELECT count() FROM t_qcc_proj WHERE b = 99999 SETTINGS use_query_condition_cache = 1, log_comment = 'qcc_1_no_projection';
SELECT count() FROM t_qcc_proj WHERE b = 99999 SETTINGS use_query_condition_cache = 1, log_comment = 'qcc_1_no_projection';

-- `a` is only here to make `b` a NON-LEADING key of the projection's sort order, which is the
-- realistic shape: a projection built for one access pattern, filtered on a column that is not
-- its leading key. The projection's own primary key then cannot prune `b = 99999`, so what is
-- left over is exactly what the condition cache should have eliminated. With `GROUP BY b` alone
-- the residual is 1 mark instead of 1562 - the same defect, just less of it.
ALTER TABLE t_qcc_proj ADD PROJECTION p (SELECT a, b, count() GROUP BY a, b);
ALTER TABLE t_qcc_proj MATERIALIZE PROJECTION p SETTINGS mutations_sync = 2;

-- the same three runs WITH the materialized projection
SYSTEM DROP QUERY CONDITION CACHE;
SELECT count() FROM t_qcc_proj WHERE b = 99999 SETTINGS use_query_condition_cache = 1, log_comment = 'qcc_2_with_projection';
SELECT count() FROM t_qcc_proj WHERE b = 99999 SETTINGS use_query_condition_cache = 1, log_comment = 'qcc_2_with_projection';
SELECT count() FROM t_qcc_proj WHERE b = 99999 SETTINGS use_query_condition_cache = 1, log_comment = 'qcc_2_with_projection';

SYSTEM FLUSH LOGS query_log;

SELECT
    log_comment,
    groupArray(marks)  AS marks_per_run,
    groupArray(hits)   AS cache_hits_per_run,
    groupArray(misses) AS cache_misses_per_run
FROM
(
    SELECT
        log_comment,
        ProfileEvents['SelectedMarks']             AS marks,
        ProfileEvents['QueryConditionCacheHits']   AS hits,
        ProfileEvents['QueryConditionCacheMisses'] AS misses
    FROM system.query_log
    WHERE type = 'QueryFinish'
      AND current_database = currentDatabase()
      AND log_comment IN ('qcc_1_no_projection', 'qcc_2_with_projection')
    ORDER BY event_time_microseconds
)
GROUP BY log_comment
ORDER BY log_comment
FORMAT Vertical;

Result on 26.8.1.657:

Row 1:
──────
log_comment:          qcc_1_no_projection
marks_per_run:        [6250,0,0]
cache_hits_per_run:   [0,1,1]
cache_misses_per_run: [1,0,0]

Row 2:
──────
log_comment:          qcc_2_with_projection
marks_per_run:        [1562,1562,1562]
cache_hits_per_run:   [0,0,0]
cache_misses_per_run: [2,2,2]

All six queries return the same (correct) result of 0 rows: this is purely a performance defect.

The cache is consulted, but never filled

Read the two cache_*_per_run arrays side by side.

Without the projection the cache behaves normally: run 1 misses and populates, runs 2 and 3 hit,
and SelectedMarks collapses from 6250 to 0.

With the projection every run reports 0 hits and 2 misses — the read side probes the cache
each time and finds nothing, so SelectedMarks stays pinned at 1562. Three identical queries
producing three misses and zero hits is not "the cache does not apply here"; it means the consult
path runs and the write path does not, so nothing is ever recorded for a read served from a
projection part.

Wall time follows directly: query_duration_ms is [9, 3, 3] without the projection and
[13, 12, 11] with it, so each repeat is roughly 4× slower with the projection than without
one.

What narrows it down

Each of these was checked on the same table, in isolation:

• ALTER TABLE ... ADD PROJECTION alone — cache still works (0 failures in 182 fuzzer trials).
  Only MATERIALIZE PROJECTION breaks it.
• ALTER TABLE ... DROP PROJECTION afterwards — cache works again (0 failures in 170 trials).
• SETTINGS optimize_use_projections = 0 on the query — cache works again, marks return to
  [6250, 0, 0]. This pins the cause to the projection read path rather than to the mutation that
  materializing performs.

The condition is simply that the query is actually served from the projection. With a projection
the optimizer declines to use, the read falls back to the base table and the cache works normally.

Note on intent

This does not look like a deliberate exclusion. The codebase has an explicit idiom for turning the
cache off on read paths that cannot support it — ReadFromMergeTree::disableQueryConditionCache(),
called for vector search (useVectorSearch.cpp), lazy final (optimizeLazyFinal.cpp,
LazyFinalKeyAnalysisTransform.cpp) and UNIQUE KEY reads. The projection optimizer does not call
it, and filterPartsByQueryConditionCache has no projection guard, so the read side genuinely
intends to use the cache here.

I have not established whether this is a regression. It may be a case that was never
implemented rather than something that broke; both binaries available to me are within days of
each other, so I could not bisect.

Impact

The cost is whatever the projection's own primary key cannot already eliminate, re-paid on every
repetition. That varies with the shape of the projection:

predicate vs. projection sort key	marks per repeat
on the leading key (GROUP BY b, WHERE b = ...)	1
on a non-leading key (GROUP BY a, b, WHERE b = ...)	1562

The reproduction above uses the second shape, which is the common one: a projection built for some
other access pattern, with a selective filter on a column that is not its leading key. That is
precisely the case the condition cache exists to accelerate, and precisely where it is lost.

Related

• Do not disable the query condition cache for materialized lightweight deletes #112947 — same symptom class (cache silently stops pruning) caused by a lightweight delete;
  different cause. Reproduced independently while validating the oracle used to find this.
• Perf regression: QueryConditionCache ineffective in self-joins (since #94062) #104985 (closed) — QueryConditionCache ineffective in self-joins.
• Query condition cache poisoned by a query_plan_join_shard_by_pk_ranges + full_sorting_merge multi-threaded join read — later default-settings queries silently lose rows #111897, Query condition cache poisoned by a parallel-replicas read of Merge over a VIEW — later plain queries silently return 0 rows #111363, makeQueryConditionCacheKey still keys the Query Condition Cache on the raw etag, so HDFS's new weak (mtime_sec,size) token can serve stale row-group skip marks #112016 — other query-condition-cache defects, all unrelated causes.

No existing issue mentions projections.

Version: 26.8.1.657 (master, built 2026-08-03)

[PedroTadim]

cc @rschu1ze this shouldn't be difficult to add?

[rschu1ze]

I had only a brief and superfluous look.

We can ask GroeneAI, however experience tells it will produce 2k LOC spaghetti code for such a feature and it will take lots of effort to cleanup.

I asked Claude to fix this locally, and it produced a 2 liner ... which looked incorrect.

Let's better fix in the classical way.