title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/query-condition-cache.md)

Note

The query condition cache only works when [enable\_analyzer](https://clickhouse.com/docs/operations/settings/settings#enable_analyzer) is set to true, which is the default value.

Many real-world workloads involve repeated queries against the same or almost the same data (for instance, previously existing data plus new data).
ClickHouse provides various optimization techniques to optimize for such query patterns.
One possibility is to tune the physical data layout using index structures (e.g., primary key indexes, skipping indexes, projections) or pre-calculation (materialized views).
Another possibility is to use ClickHouse's [query cache](/docs/operations/query-cache) to avoid repeated query evaluation.
The downside of the first approach is that that it requires manual intervention and monitoring by a database administrator.
The second approach may return stale results (as the query cache is transactionally not consistent) which may or may not be acceptable, depending on the use case.

The query condition cache provides an elegant solution for both problems.
It is based on the idea that evaluating a filter condition (e.g., `WHERE col = 'xyz'`) on the same data will always return the same results.
More specifically, the query condition cache remembers for each evaluated filter and each granule (= a block of 8192 rows by default) if no row in the granule satisfy the filter condition.
The information is recorded as a single bit: a 0 bit represents that no row matches the filter whereas a 1 bit means that at least one matching row exists.
In the former case, ClickHouse may skip the corresponding granule during filter evaluation, in the latter case, the granule must be loaded and evaluated.

The query condition cache is effective if three prerequisites are fulfilled:

- First, the workload must evaluate the same filter conditions repeatedly. This happens naturally if a query is repeated multiple times but it can also happen if two queries share the same filters, e.g. `SELECT product FROM products WHERE quality > 3` and `SELECT vendor, count() FROM products WHERE quality > 3`.
- Second, the majority of the data is immutable, i.e., does not change between queries. This is generally the case in ClickHouse as parts are immutable and created only by INSERTs.
- Third, filters are selective, i.e. only relatively few rows satisfy the filter condition. The fewer rows match the filter condition, the more granules will be recorded with bit 0 (no matching rows), and the more data can be "pruned" from subsequent filter evaluations.

## Memory consumption[​](#memory-consumption "Direct link to Memory consumption")

Since the query condition cache stores only a single bit per filter condition and granule, it consumes only little memory.
The maximum size of the query condition cache can be configured using server settings [`query_condition_cache_size`](/docs/operations/server-configuration-parameters/settings#query_condition_cache_size) (default: 100 MB).
A cache size of 100 MB corresponds to 100 \* 1024 \* 1024 \* 8 = 838,860,800 entries.
Since each entry represents a mark (8192 rows by default), the cache can cover up to 6,871,947,673,600 (6.8 trillion) rows of a single column.
In practice, filter are evaluated on more than one column, so that number needs to be divided by the number of filtered columns.

## Configuration settings and usage[​](#configuration-settings-and-usage "Direct link to Configuration settings and usage")

Setting [use\_query\_condition\_cache](/docs/operations/settings/settings#use_query_condition_cache) controls whether a specific query or all queries of the current session should utilize the query condition cache.

For example, the first execution of query

```
SELECT col1, col2
FROM table
WHERE col1 = 'x'
SETTINGS use_query_condition_cache = true;
```

will store ranges of the table which do not satisfy the predicate.
Subsequent executions of the same query, also with parameter `use_query_condition_cache = true`, will utilize the query condition cache to scan less data.

## Administration[​](#administration "Direct link to Administration")

The query condition cache is not retained between restarts of ClickHouse.

To clear the query condition cache, run [`SYSTEM CLEAR QUERY CONDITION CACHE`](/docs/sql-reference/statements/system#drop-query-condition-cache).

The content of the cache is displayed in system table [system.query\_condition\_cache](/docs/operations/system-tables/query_condition_cache).
To calculate the current size of the query condition cache in MB, run `SELECT formatReadableSize(sum(entry_size)) FROM system.query_condition_cache`.
If you like to investigate individual filter conditions, you can check field `condition` in `system.query_condition_cache`. Note that this field is only available in debug builds.

The number of query condition cache hits and misses since database start are shown as events "QueryConditionCacheHits" and "QueryConditionCacheMisses" in system table [system.events](/docs/operations/system-tables/events).
Both counters are only updated for `SELECT` queries which run with setting `use_query_condition_cache = true`, other queries do not affect "QueryCacheMisses".

## Related content[​](#related-content "Direct link to Related content")

- Blog: [Introducing the Query Condition Cache](https://clickhouse.com/blog/introducing-the-clickhouse-query-condition-cache)
- [Predicate Caching: Query-Driven Secondary Indexing for Cloud Data Warehouses (Schmidt et. al., 2024)](https://doi.org/10.1145/3626246.3653395)

- [Memory consumption](#memory-consumption)- [Configuration settings and usage](#configuration-settings-and-usage)- [Administration](#administration)- [Related content](#related-content)

Was this page helpful?