ClickHouse® 26.3 introduced Materialized Common Table Expressions (CTEs), a feature designed to reduce redundant computations by executing a CTE once and allowing multiple parts of a query to reuse the same intermediate result. While this sounds like an automatic performance win, the reality is more nuanced. Like many query optimizations, the effectiveness of Materialized CTEs depends heavily on the workload.
In this article, we'll explore how Materialized CTEs work, where they provide measurable improvements, and the scenarios where they may not offer any noticeable benefit.
What Are Materialized CTEs?
A Common Table Expression (CTE) is a temporary named result set created using the WITH clause. Traditionally, ClickHouse treats CTEs as inline query expressions, meaning the optimizer may re-evaluate them when referenced multiple times.
Materialized CTEs change this behavior. By using the MATERIALIZED keyword, ClickHouse computes the CTE once, stores the intermediate result temporarily, and reuses it wherever it is referenced within the query.
This can reduce duplicate computation, especially for expensive aggregations or joins that would otherwise execute multiple times.
Enabling Materialized CTEs
Materialized CTEs are available in ClickHouse® 26.3 but require enabling the new query analyzer.
SET enable_materialized_cte = 1;
SET enable_analyzer = 1;
Without these settings, ClickHouse falls back to traditional CTE behavior, making performance comparisons inaccurate.
Benchmark Environment
To understand the practical impact of Materialized CTEs, the feature was evaluated using a consistent benchmark environment:
- ClickHouse® 26.3 LTS
- Docker Compose deployment
- UK Property Price dataset containing millions of records
-
clickhouse-clientfor query execution - Multiple benchmark iterations with identical hardware and configuration
Each query was executed several times after an initial warm-up run, and the average execution time was used for comparison.
Scenario 1: Simple Aggregation
The first test evaluated a straightforward aggregation grouped by year.
Because the CTE was referenced only once, there was no repeated computation to eliminate. Both the traditional and materialized versions performed almost identically, with only minor timing differences that fall within normal benchmark variation.
Key takeaway: Materializing a CTE provides little value when the intermediate result is consumed only once.
Scenario 2: Reusing a Small CTE
The second workload referenced the same CTE multiple times.
Although this appears to be an ideal use case for materialization, the intermediate result contained only a small number of rows. The cost of storing and managing the temporary result outweighed the cost of recomputing such a lightweight dataset.
In this scenario, the materialized version offered no measurable advantage.
Key takeaway: Multiple references alone do not guarantee better performance. The complexity and size of the intermediate result also matter.
Scenario 3: Self-JOIN on an Expensive Intermediate Result
The most significant improvement appeared in a JOIN workload where the same aggregated dataset was referenced from both sides of a self-join.
With a traditional CTE, ClickHouse had to compute the aggregation separately for each side of the join. Materializing the CTE eliminated this duplicate work by computing the aggregation once and reusing the result.
This reduced data scanned, lowered CPU usage, and significantly improved query execution time.
Key takeaway: Materialized CTEs are particularly effective when expensive intermediate results are reused across multiple JOIN operations.
Scenario 4: Complex Expressions
The final benchmark focused on expensive string manipulation, regular expressions, and date formatting.
Even though each row required substantial processing, the CTE was referenced only once. As a result, the materialized version showed only marginal differences compared to the traditional approach.
This demonstrates that computational complexity alone does not justify materialization. Reuse remains the deciding factor.
What We Learned
The benchmark shows that Materialized CTEs are not a universal optimization.
They perform best when:
- Expensive computations are reused multiple times.
- Intermediate results participate in multiple JOIN operations.
- Avoiding repeated scans significantly reduces query cost.
On the other hand, they provide little or no benefit when:
- The CTE is referenced only once.
- The intermediate result is very small.
- The overhead of materialization exceeds the cost of recomputation.
Best Practices
If you're considering using Materialized CTEs in production, keep these recommendations in mind:
- Benchmark your own queries before adopting the feature widely.
- Measure execution time alongside rows read, bytes scanned, and memory usage.
- Focus on workloads that repeatedly consume the same expensive intermediate result.
- Avoid assuming that every repeated CTE should be materialized.
- Keep ClickHouse updated, as optimizer improvements continue across releases.
Final Thoughts
Materialized CTEs are a valuable addition to ClickHouse® 26.3, but they should be viewed as a targeted optimization rather than a universal performance switch.
The biggest gains occur when duplicate computation is genuinely expensive, particularly in complex JOIN workloads. For simpler queries or single-use CTEs, the optimizer already does an excellent job, and materialization may offer little benefit.
As with any database optimization, the best approach is to benchmark your own workloads, understand your query patterns, and apply Materialized CTEs where they provide measurable improvements rather than assuming they will accelerate every query.
Read more... https://www.ch-ops.io/blog/materialized-ctes-in-clickhouse-263-a-performance-benchmark
Top comments (0)