[RFC] Query plan caching

[novikd]

Company or project name

ClickHouse

Use case

ClickHouse commonly executes the same queries repeatedly. Currently, query analysis and planning are performed from scratch each time a query is received, even for identical queries. This overhead also affects VIEWs:

• Ordinary VIEWs: The underlying query is re-analyzed on every SELECT from the view.
• Materialized VIEWs: The underlying query is re-analyzed on every INSERT into the view.

In certain scenarios, query analysis and planning overhead becomes significant relative to total query duration, particularly for:

• Queries with sub-second execution times.
• Queries that extensively use aliases (see #86019, #72721). Aliases can significantly increase query size and, therefore, increase analysis cost.

While alias analysis performance can be improved through caching identifier resolution results (see #88043), this approach has inherent limitations. Identifier resolution caching cannot always be applied and must sometimes be invalidated—for example, when the group_by_use_nulls setting is enabled. These constraints motivate a more comprehensive query plan caching solution.

Describe the solution you'd like

Similar to Query Cache (#34011), ClickHouse could cache SELECT query plans using query AST hash as a key.

Possible implementation

Caching a universal query plan

Once the SELECT query is analyzed and planned, the resulting query plan can be cloned using IQueryPlan::clone() and stored in the query plan cache.

However, the same query may read different data on each execution. Additionally, reading steps may hold resources (e.g., preventing removal of already-merged data parts). To address these issues:

1. All reading query plan steps should be replaced with a generic ReadFromTableStep in the cache entry.
2. All filters from PrewhereInfo in the reading step should be copied into the ReadFromTableStep.

This ensures the cached plan remains universal and resource-independent.

Executing cached SELECT queries

When a matching cache entry exists for a query:

1. Extract and copy the cached query plan.
2. Replace all ReadFromTableStep instances with correct physical reading steps using the QueryPlan::resolveStorages method.
3. Check if row-policy should be used.
4. Apply query plan level optimizations to the extracted plan.

Query plan optimizations must be reapplied because the optimal plan may differ between executions. Possible reasons:

• Changed statistics: New data in tables can affect statistics, altering the optimal join order.
• Schema changes: New vector indexes or projections may have been added to target tables.
• Configuration changes: Query plan optimizations may have been enabled or disabled

Updating query cache entries

It may be beneficial to update cached query plans to reduce the number of optimizations applied on each query execution. This could be triggered when the number of modified query plan steps during optimization exceeds a configurable threshold.

Views

Materialized Views

SELECT queries from MATERIALIZED VIEWs can be "compiled" into query plans during CREATE or ATTACH operations and subsequently reused. The QueryPlan::resolveStorages method would need modification to properly handle view logic (replacing the storage).

Ordinary Views

Ordinary views present a more complex challenge because ClickHouse must be able to optimize the cached query plan when only a subset of view columns is read. Two potential approaches:

1. (Optimal) Enhance the unused column removal optimization introduced in #76487 to fully cover distributed cases (as described in #81718).
2. (Simple) Use the tuple (query_hash, columns_to_read) as the cache key.

Describe alternatives you've considered

No response

Additional context

Query plans cannot be cached in certain cases:

• Non-correlated scalar subqueries: These subqueries are evaluated during query analysis, and their result values may differ between executions. A dedicated setting could optionally allow caching by assuming the subquery result is static and unchanging across runs.

[KochetovNicolai]

ClickHouse could cache SELECT query plans using query AST hash as a key.

I think we also should append settings to the cache (there could be session-level settings we must apply).

All filters from PrewhereInfo in the reading step should be copied into the ReadFromTableStep.

If we apply optimizations, prewhere info may not be needed (except it's manually specified for storage).
Overall, we can apply first-stage plan optimizations before putting the plan into the cache, but we cannot apply those that depend on the storage snapshot.

Also, should we mix the table metadata into the key? E.g., optimize_functions_to_subcolumns depend on table metadata.
The table schema overall can change, and there could be a situation when re-resolved storage returns a reading step with different types. Maybe, in this case, we should just drop the plan and rebuild.

Should we support distributed query processing as well? Technically, we can put a required stage together with the reading step. However, it seems like even more complexity to invalidate the cache properly, so I would rather not.

[maxjustus]

Nice. Even with this I do think it's still worth having alias expression caching as well in the limited scopes where it's safe to do so because without it query analysis becomes catastrophically slow when using deeply nested expressions containing multiple aliased expressions, meaning that the initial query might take so long to analyze that it runs afoul of query timeouts before its query plan can be cached.

[novikd]

I think we also should append settings to the cache (there could be session-level settings we must apply).

Makes sense.

If we apply optimizations, prewhere info may not be needed (except it's manually specified for storage).
Overall, we can apply first-stage plan optimizations before putting the plan into the cache, but we cannot apply those that depend on the storage snapshot.

The filter could have been moved into PREWHERE. It is important not to lose it in such a case, so it should be extracted from the reading step.

Also, should we mix the table metadata into the key? E.g., optimize_functions_to_subcolumns depend on table metadata.
The table schema overall can change, and there could be a situation when re-resolved storage returns a reading step with different types. Maybe, in this case, we should just drop the plan and rebuild.

Makes sense. It looks like cache entry should have a structure like:

struct CacheEntry
{
    bool isValid() const;

    QueryPlan plan;
    SettingsSnapshot settings;
    UsedStorageMetadataVersions metadata_versions;
}

Before using the cached query plan, it is essential to verify that the cache entry is still valid and can be used.

Should we support distributed query processing as well? Technically, we can put a required stage together with the reading step. However, it seems like even more complexity to invalidate the cache properly, so I would rather not.

I guess simple cases can be covered, but the initial implementation can ignore distributed execution.

[qhsong]

Once the SELECT query is analyzed and planned, the resulting query plan can be cloned using IQueryPlan::clone() and stored in the query plan cache.

For complex QueryPlan, clone may cost too much time.May be we can try seperate IQueryStep into two parts, immutable and mutable. CacheEntry save immutable part, IQueryPlanStep construct a mutable part and pass mutable part to updatePipiline function . Also we can make updatePipeline as a const function.
In this way, maybe we can save optimized QueryPlan? Optimized also cost a lot of time.

Let's think from SQL, What's the mutable part for a SQL? Most Part is Expression. It's ActionDAG and ExpressionAction in CK.
Maybe we can build a const map and ActionDAG save the const index. So we can save ActionDAG and ExpressionAction, just change const map. This may save a lot of time.

But I still have no idea to handle with const fold expression.