In this lesson, you will learn how to determine whether to inline Common Table Expressions (CTEs) or force their materialization to optimize query execution plans and resource usage.
While CTEs provide excellent readability, the Cost-Based Optimizer (CBO) treats them differently depending on the database engine. Generally, an "inlined" CTE is expanded into the main query, allowing the optimizer to push predicates deeper, apply indexes, and prune partitions across the entire query tree. However, if a CTE is referenced multiple times, some engines default to "materializing" it—writing the results to a temporary table—which can lead to excessive I/O and prevent the optimizer from seeing the global query structure.
When to Inline
Inlining is preferred when the CTE acts as a simple filter or a small lookup table. By allowing the optimizer to flatten the query, you enable better join reordering and predicate pushdown. If you see a query plan where a CTE causes a full table scan that could have been avoided by an index, the optimizer is likely struggling to "see through" the CTE boundary.
When to Materialize
Materialization is beneficial for complex, resource-heavy transformations that are referenced multiple times in a single query. By forcing materialization (using hints like MATERIALIZED in PostgreSQL or WITH ... AS MATERIALIZED), you ensure the expensive calculation happens exactly once, rather than re-computing the result set for every join.
Example: Consider a CTE calculating daily revenue aggregates:
-- Potentially slow if inlined multiple times
WITH DailySales AS (
SELECT date, SUM(amount) as total FROM sales GROUP BY 1
)
SELECT * FROM DailySales a JOIN DailySales b ON a.date = b.date - 1;
If this query runs slowly, the engine might be recalculating the sum twice. By forcing materialization, you store the result in a temp structure, reducing CPU cycles at the cost of transient disk I/O. As a data engineer, your goal is to examine the EXPLAIN plan: if you see redundant processing nodes for the same CTE, it is time to intervene.
Key Takeaway: Inline CTEs to enable filter pushdown and index usage, but force materialization when a complex result set is referenced multiple times to avoid redundant compute.