You've written a query that works. It returns the right numbers, passes QA, and gets deployed to production. Six months later, a business analyst notices the dashboard is taking 45 seconds to load. You open the query and find it: the same 200-line subquery computing rolling 30-day revenue figures, pasted three times across different parts of the WHERE clause, the SELECT list, and a HAVING filter. Each copy is evaluated independently by the database engine. You've inadvertently written a query that does three times as much work as it needs to.
This isn't a contrived scenario. It happens constantly in production SQL at organizations of every size, and the pattern has a name in compiler theory: common subexpression elimination (CSE). Compilers for general-purpose programming languages have done this automatically for decades. SQL query optimizers sometimes do it, but the guarantees are weak, inconsistent across database engines, and often sabotaged by the very way we structure our queries. The good news is that you don't have to wait for the optimizer — you can do it yourself, explicitly, using CTEs, derived tables, and a precise understanding of how your execution engine handles repeated logic.
By the end of this lesson, you'll understand not just how to factor repeated SQL logic, but why different approaches produce different execution plans, when the optimizer helps you and when it actively works against you, and how to use optimizer hints as a precision instrument rather than a blunt force tool.
What you'll learn:
MATERIALIZE, NO_MERGE, WITH (NOEXPAND), and engine-specific directives) to enforce materialization when the optimizer makes the wrong callThis lesson assumes you're comfortable with:
If you've never looked at an EXPLAIN plan before, spend an hour on that first. Everything in this lesson becomes much more concrete once you can see what the engine is actually doing.
Let's build a concrete example. You're working for a SaaS company. You have an orders table with about 50 million rows, a customers table, and a products table. A business analyst needs a report that flags customers who:
Here's the naive implementation a lot of people write first:
SELECT
c.customer_id,
c.email,
c.signup_date,
-- Compute lifetime value inline
(
SELECT SUM(o.order_total)
FROM orders o
WHERE o.customer_id = c.customer_id
) AS lifetime_value,
-- Flag for 90th percentile
CASE WHEN (
SELECT SUM(o.order_total)
FROM orders o
WHERE o.customer_id = c.customer_id
) >= (
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY ltv)
FROM (
SELECT customer_id, SUM(order_total) AS ltv
FROM orders
GROUP BY customer_id
) ltv_calc
) THEN 1 ELSE 0 END AS is_high_value
FROM customers c
WHERE
-- Recent purchase check
EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
)
AND
-- Lifetime value above 90th percentile (again)
(
SELECT SUM(o.order_total)
FROM orders o
WHERE o.customer_id = c.customer_id
) >= (
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY ltv)
FROM (
SELECT customer_id, SUM(order_total) AS ltv
FROM orders
GROUP BY customer_id
) ltv_calc
);
Count the redundant computations:
SUM(order_total) appears three times — once in the SELECT list, once in the CASE, and once in the WHERE clauseorders to build the ltv_calc subquery happens twice for the percentile calculation aloneOn a 50-million-row orders table, you're potentially doing 5–7 full or large partial scans where 2 would suffice. That's not an optimization opportunity — it's a correctness and efficiency emergency.
Why doesn't the optimizer fix this automatically? Modern query optimizers can recognize some repeated subexpressions, but their ability to do so is highly constrained. Correlated subqueries (like the per-customer SUM above) are particularly hard for optimizers to memoize because each invocation potentially produces different results. Scalar subqueries in both
SELECTandWHEREpositions may be executed separately even if they're textually identical. Never assume the optimizer will handle this.
Before we fix the query, we need to understand what's actually happening under the hood — because this knowledge will inform every technique we use.
SQL is a declarative language. You specify what you want, not how to compute it. The optimizer's job is to translate your logical request into an efficient physical execution plan. The optimizer does this by applying transformation rules — algebraic equivalences that preserve result correctness while changing execution strategy.
Common subexpression elimination is one such transformation. In compiler theory, it means: "if you compute the same value twice, compute it once and reuse the result." For SQL, this requires the optimizer to:
Step 1 is harder than it sounds. SUM(order_total) WHERE customer_id = c.customer_id appears in three places but the optimizer must prove they reference the same outer binding. Step 2 involves cost estimation, which depends on statistics. Step 3 involves memory and I/O trade-offs.
This is the most important thing to understand before using CTEs for CSE, because CTE behavior differs dramatically between database engines:
PostgreSQL (before version 12): CTEs were always optimization fences. The planner would materialize every CTE result into a temporary structure and never push predicates into them. This was actually useful for CSE — materialization meant the subquery ran exactly once.
PostgreSQL 12+: The planner can now "inline" CTEs (treat them like view definitions and push them into the larger query). By default, a CTE is inlined if it's referenced once; if referenced multiple times, PostgreSQL may still materialize it. You can force materialization with MATERIALIZED keyword or prevent it with NOT MATERIALIZED.
SQL Server: SQL Server generally treats CTEs as syntactic sugar — it expands them inline during query compilation. A CTE referenced three times may result in the underlying computation executing three times. To force single-execution, you often need #temp tables.
MySQL 8+: Similar to SQL Server — CTEs are typically inlined. Multiple references can mean multiple executions.
BigQuery: CTEs are expanded unless the query planner decides to materialize them. The WITH clause in BigQuery is better thought of as a readability tool than a materialization guarantee.
Oracle: Oracle's behavior is similar to SQL Server — CTEs are generally inlined, though the optimizer may cache results in some circumstances.
The critical insight: Never assume that writing something once as a CTE means it executes once. You must understand your engine's specific behavior and verify with execution plans.
The cleanest approach to CSE in SQL is factoring the repeated expression into a CTE and, where necessary, forcing materialization. Let's rewrite our problem query.
Before writing a single line of SQL, annotate what's repeated:
SUM(order_total) GROUP BY customer_id — referenced 3 timesWITH
-- Computation A: Per-customer lifetime value (runs once)
customer_ltv AS MATERIALIZED (
SELECT
customer_id,
SUM(order_total) AS lifetime_value,
COUNT(*) AS order_count,
AVG(order_total) AS avg_order_value,
MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id
),
-- Computation B: Portfolio-level percentile thresholds (runs once)
ltv_thresholds AS MATERIALIZED (
SELECT
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY lifetime_value) AS p90_ltv,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY lifetime_value) AS p75_ltv
FROM customer_ltv -- References the already-computed CTE
),
-- Computation C: Customers active in last 30 days
recently_active AS MATERIALIZED (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
),
-- Computation D: Category average order values
category_avg AS MATERIALIZED (
SELECT
p.category,
AVG(o.order_total) AS category_avg_order_value
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.category
)
SELECT
c.customer_id,
c.email,
c.signup_date,
ltv.lifetime_value,
ltv.order_count,
ltv.avg_order_value,
CASE
WHEN ltv.lifetime_value >= thresh.p90_ltv THEN 'Tier 1'
WHEN ltv.lifetime_value >= thresh.p75_ltv THEN 'Tier 2'
ELSE 'Standard'
END AS value_tier
FROM customers c
JOIN customer_ltv ltv ON c.customer_id = ltv.customer_id
JOIN ltv_thresholds thresh ON TRUE -- cross join to scalar result
JOIN recently_active ra ON c.customer_id = ra.customer_id
JOIN category_avg ca
ON ca.category = c.primary_category -- assuming customers have a primary category
WHERE
ltv.lifetime_value >= thresh.p90_ltv
AND ltv.avg_order_value > ca.category_avg_order_value;
The MATERIALIZED keyword (PostgreSQL 12+ syntax) tells the planner: compute this CTE once, store the result, and reuse it for all references. The result is:
orders for customer_ltv (instead of three)customer_ltv for ltv_thresholds (instead of two)orders for recently_activeorders + products for category_avgWe've gone from 7+ scans of large tables to 3 scans and some joins. On a 50-million-row table, this is a qualitative difference, not just a marginal improvement.
Sometimes you want the opposite — you want the optimizer to push predicates into a CTE rather than materializing it. Consider:
WITH all_orders AS NOT MATERIALIZED (
SELECT *
FROM orders
JOIN order_line_items oli ON orders.order_id = oli.order_id
)
SELECT * FROM all_orders WHERE customer_id = 12345;
With NOT MATERIALIZED, the planner can push the WHERE customer_id = 12345 predicate down into the CTE definition, potentially using an index on customer_id. If you force materialization here, you'd scan and store the entire join result before filtering.
Rule of thumb: Use
MATERIALIZEDwhen the CTE is referenced multiple times or when its computation is expensive and selectivity happens after it. UseNOT MATERIALIZEDwhen the CTE is referenced once and you want predicate pushdown.
Before CTEs existed (or in engines where CTE behavior is unreliable), derived tables — subqueries in the FROM clause — were the standard tool for CSE. They're still valuable, particularly when you need precise control over join order or when you're working in MySQL pre-8.0.
The key property of a derived table is that it's evaluated in place, and — critically — each reference to the same derived table definition creates a separate evaluation. You can't reference a derived table by name twice (unlike a CTE). However, you can nest derived tables to achieve multi-level CSE.
-- Instead of referencing customer_ltv twice,
-- we structure the query so the derived table is used once
-- and the result flows through to all uses
SELECT
c.customer_id,
c.email,
ltv_with_tier.lifetime_value,
ltv_with_tier.value_tier
FROM customers c
JOIN (
-- This derived table encapsulates LTV computation AND tier assignment
-- so the downstream query doesn't need to re-reference LTV multiple times
SELECT
ltv.customer_id,
ltv.lifetime_value,
CASE
WHEN ltv.lifetime_value >= thresh.p90_ltv THEN 'Tier 1'
WHEN ltv.lifetime_value >= thresh.p75_ltv THEN 'Tier 2'
ELSE 'Standard'
END AS value_tier
FROM (
-- Inner derived table: customer LTV
SELECT customer_id, SUM(order_total) AS lifetime_value
FROM orders
GROUP BY customer_id
) ltv
CROSS JOIN (
-- Inner derived table: thresholds
-- References a *separate* evaluation of the LTV query
-- This is the weakness of derived tables vs. CTEs
SELECT
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_ltv) AS p90_ltv,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_ltv) AS p75_ltv
FROM (
SELECT customer_id, SUM(order_total) AS total_ltv
FROM orders
GROUP BY customer_id
) ltv_for_percentiles
) thresh
) ltv_with_tier ON c.customer_id = ltv_with_tier.customer_id
WHERE ltv_with_tier.value_tier = 'Tier 1';
Notice the problem: even with derived tables, we still scan orders twice because derived tables can't share computation between sibling subqueries. This is exactly where CTEs with materialization win.
Derived tables genuinely outperform CTEs in specific situations:
When predicate pushdown is critical: If a derived table wraps a large table and you're filtering heavily, the planner can often push the outer WHERE into the derived table definition. Most planners will not push predicates into a materialized CTE.
When you're on an engine that materializes CTEs poorly: In SQL Server, a derived table joined once is often cleaner than a CTE that might get expanded and re-evaluated.
When you need to control join order explicitly: Some planners treat derived tables as atomic units in join ordering; you can use this to pin an expensive small-result computation before it's joined to a large table.
Sometimes the repeated computation isn't within a single query — it's across multiple queries in a session, a stored procedure, or a batch job. CTEs are scoped to a single statement; derived tables obviously can't escape their enclosing query. Temporary tables are the tool for this level of CSE.
-- In a stored procedure or ETL script:
-- Step 1: Materialize the expensive computation once
CREATE TEMPORARY TABLE tmp_customer_ltv AS
SELECT
customer_id,
SUM(order_total) AS lifetime_value,
COUNT(*) AS order_count,
AVG(order_total) AS avg_order_value,
MAX(order_date) AS last_order_date,
MIN(order_date) AS first_order_date
FROM orders
GROUP BY customer_id;
-- Create an index for subsequent joins
CREATE INDEX idx_tmp_ltv_customer ON tmp_customer_ltv(customer_id);
CREATE INDEX idx_tmp_ltv_value ON tmp_customer_ltv(lifetime_value);
-- Step 2: Use the temp table across multiple subsequent queries
-- Query A: High-value customer report
SELECT c.*, t.lifetime_value, t.order_count
FROM customers c
JOIN tmp_customer_ltv t ON c.customer_id = t.customer_id
WHERE t.lifetime_value >= 10000;
-- Query B: Churn risk analysis (using same temp table)
SELECT c.*, t.last_order_date,
CURRENT_DATE - t.last_order_date AS days_since_last_order
FROM customers c
JOIN tmp_customer_ltv t ON c.customer_id = t.customer_id
WHERE t.last_order_date < CURRENT_DATE - INTERVAL '90 days'
AND t.lifetime_value >= 500; -- Not just any churner — valuable ones
-- Query C: Cohort analysis
SELECT
DATE_TRUNC('month', t.first_order_date) AS cohort_month,
COUNT(*) AS customers,
AVG(t.lifetime_value) AS avg_ltv,
AVG(t.order_count) AS avg_orders
FROM tmp_customer_ltv t
GROUP BY 1
ORDER BY 1;
-- Cleanup
DROP TEMPORARY TABLE tmp_customer_ltv;
The temp table approach has three significant advantages over CTEs for multi-query scenarios:
SQL Server-specific note: In SQL Server,
#temptables are almost always preferable to CTEs for repeated computation in stored procedures. The SQL Server optimizer regularly makes poor decisions with complex CTEs — it often expands them and produces bad cardinality estimates. A#temptable withUPDATE STATISTICSforces good estimation.
For truly expensive computations that are needed repeatedly — not just within a session, but across many sessions and users — the right answer is to pre-materialize the subexpression at the schema level using indexed views (SQL Server) or materialized views (PostgreSQL, Oracle, MySQL 8+, BigQuery).
-- PostgreSQL: Create a materialized view for customer LTV
-- This is the "ultimate" CSE — compute once, refresh on schedule
CREATE MATERIALIZED VIEW mv_customer_ltv AS
SELECT
o.customer_id,
SUM(o.order_total) AS lifetime_value,
COUNT(*) AS order_count,
AVG(o.order_total) AS avg_order_value,
MAX(o.order_date) AS last_order_date,
MIN(o.order_date) AS first_order_date,
COUNT(DISTINCT DATE_TRUNC('month', o.order_date)) AS active_months
FROM orders o
GROUP BY o.customer_id
WITH DATA;
CREATE UNIQUE INDEX ON mv_customer_ltv(customer_id);
CREATE INDEX ON mv_customer_ltv(lifetime_value);
CREATE INDEX ON mv_customer_ltv(last_order_date);
-- Now any query can reference this view without recomputing
SELECT c.*, m.lifetime_value, m.last_order_date
FROM customers c
JOIN mv_customer_ltv m ON c.customer_id = m.customer_id
WHERE m.lifetime_value >= 5000;
The materialized view is refreshed on a schedule:
-- Refresh nightly (or incrementally if supported)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_ltv;
SQL Server's indexed views go even further — with the NOEXPAND hint, the optimizer can automatically substitute the indexed view for matching subexpressions in queries, even when the query doesn't explicitly reference the view:
-- SQL Server: Force optimizer to use indexed view
SELECT c.customer_id, c.email, v.lifetime_value
FROM customers c
JOIN dbo.vw_customer_ltv v WITH (NOEXPAND) ON c.customer_id = v.customer_id
WHERE v.lifetime_value >= 10000;
WITH (NOEXPAND) tells SQL Server: don't expand this view into its definition — read the pre-computed index directly. Without this hint, even when an indexed view exists, the optimizer may choose to recompute from base tables.
Writing the refactored query is only half the job. You need to prove it reduced work. Here's how to read plans for CSE evidence.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
WITH customer_ltv AS MATERIALIZED (
SELECT customer_id, SUM(order_total) AS lifetime_value
FROM orders
GROUP BY customer_id
)
SELECT c.customer_id, ltv.lifetime_value
FROM customers c
JOIN customer_ltv ltv ON c.customer_id = ltv.customer_id
WHERE ltv.lifetime_value > 5000;
In the output, look for:
CTE Scan on customer_ltv — this means the CTE was materialized and is being read from the in-memory/on-disk result, not recomputedBuffers: shared hit=X — lower numbers confirm fewer table pages were readactual rows=X loops=1 on the CTE node — if loops > 1, the CTE was re-evaluatedIf you see Seq Scan on orders appear multiple times, your CTE was not materialized and is being re-evaluated. Add MATERIALIZED keyword.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Your query here
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
Look for the logical reads number for each table. If orders shows up multiple times in the IO stats with significant reads, a subexpression involving it is being evaluated multiple times.
In the visual execution plan (available in SSMS), look for Spool operators — these indicate the optimizer has chosen to cache an intermediate result. A Table Spool or Index Spool is SQL Server's version of CSE. If you don't see one where you expect it, the optimizer isn't caching.
BigQuery doesn't have EXPLAIN in the traditional sense, but you can use the Query Execution Details in the BigQuery console after running a query. Look at:
Optimizer hints should be your last resort after understanding why the optimizer is making the wrong call, not your first response to a slow query. That said, there are legitimate scenarios where hints are the right tool.
PostgreSQL doesn't have inline hints the way Oracle or SQL Server do, but you can use session-level settings to guide planning:
-- Force the planner to prefer hash joins over nested loops
-- (useful when you know a join will produce many rows)
SET enable_nestloop = off;
SET enable_hashjoin = on;
WITH customer_ltv AS MATERIALIZED ( ... )
SELECT ...;
-- Reset after your query
RESET enable_nestloop;
RESET enable_hashjoin;
For CSE specifically, the MATERIALIZED keyword in CTE definitions is your primary control surface.
-- Force a fresh plan compilation (useful when parameter sniffing
-- causes the optimizer to use a plan based on unrepresentative parameters)
SELECT *
FROM customers c
JOIN #tmp_customer_ltv t ON c.customer_id = t.customer_id
WHERE t.lifetime_value > @threshold
OPTION (RECOMPILE);
-- SQL Server 2022+: Query Store hints allow you to attach hints
-- to a query without modifying its text
EXEC sys.sp_query_store_set_hints
@query_id = 1234,
@query_hints = N'OPTION(RECOMPILE, MAXDOP 4)';
The OPTION(USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING')) hint can force the optimizer to consider alternative plans when it's stuck in a suboptimal cached plan.
Oracle has explicit inline hints for controlling CTE and view behavior:
WITH customer_ltv AS (
SELECT /*+ MATERIALIZE */
customer_id,
SUM(order_total) AS lifetime_value
FROM orders
GROUP BY customer_id
)
SELECT c.customer_id, ltv.lifetime_value
FROM customers c
JOIN customer_ltv ltv ON c.customer_id = ltv.customer_id;
The /*+ MATERIALIZE */ hint inside the CTE definition tells Oracle's optimizer to treat this as a global temporary table result. The complementary hint /*+ INLINE */ forces expansion instead.
For views and derived tables in Oracle, NO_MERGE prevents the optimizer from merging a view/subquery into the parent query:
SELECT c.customer_id, v.lifetime_value
FROM customers c
JOIN (
SELECT /*+ NO_MERGE */
customer_id,
SUM(order_total) AS lifetime_value
FROM orders
GROUP BY customer_id
) v ON c.customer_id = v.customer_id;
Without NO_MERGE, Oracle might decide to collapse the derived table into the outer query and apply a different join strategy that re-evaluates the aggregation multiple times.
Warning about hints: Hints create maintenance debt. If your data distribution changes, your schema evolves, or you upgrade your database version, hints that once improved performance may now hurt it. Document every hint with a comment explaining why it was added and what you observed without it. Include the execution plan statistics at the time of adding the hint.
Real-world analytical queries often have multiple levels of repeated subexpressions — not just one. CTEs can chain, with each CTE building on the previous ones. This is the SQL equivalent of naming intermediate variables in a program.
WITH
-- Level 1: Raw aggregates
order_metrics AS MATERIALIZED (
SELECT
o.customer_id,
o.product_id,
p.category,
o.order_date,
o.order_total,
SUM(o.order_total) OVER (
PARTITION BY o.customer_id
ORDER BY o.order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_customer_ltv,
AVG(o.order_total) OVER (
PARTITION BY p.category
ORDER BY o.order_date
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) AS rolling_30d_category_avg
FROM orders o
JOIN products p ON o.product_id = p.product_id
),
-- Level 2: Customer-level summaries (built from Level 1)
customer_summary AS MATERIALIZED (
SELECT
customer_id,
MAX(running_customer_ltv) AS lifetime_value,
MAX(order_date) AS last_order_date,
AVG(order_total) AS avg_order_value,
COUNT(*) AS total_orders,
-- How often did this customer beat the category average?
SUM(CASE WHEN order_total > rolling_30d_category_avg THEN 1 ELSE 0 END)
AS orders_above_category_avg
FROM order_metrics
GROUP BY customer_id
),
-- Level 3: Percentile thresholds (built from Level 2)
thresholds AS MATERIALIZED (
SELECT
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY lifetime_value) AS p90_ltv,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY lifetime_value) AS p75_ltv,
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY avg_order_value) AS p90_aov,
AVG(lifetime_value) AS mean_ltv,
STDDEV(lifetime_value) AS stddev_ltv
FROM customer_summary
),
-- Level 4: Scored customers (built from Levels 2 and 3)
customer_scores AS MATERIALIZED (
SELECT
cs.customer_id,
cs.lifetime_value,
cs.last_order_date,
cs.avg_order_value,
cs.orders_above_category_avg,
-- Normalized LTV score (z-score style)
(cs.lifetime_value - t.mean_ltv) / NULLIF(t.stddev_ltv, 0) AS ltv_zscore,
CASE
WHEN cs.lifetime_value >= t.p90_ltv AND cs.avg_order_value >= t.p90_aov
THEN 'Elite'
WHEN cs.lifetime_value >= t.p90_ltv
THEN 'High Value'
WHEN cs.lifetime_value >= t.p75_ltv
THEN 'Mid-High Value'
ELSE 'Standard'
END AS customer_tier
FROM customer_summary cs
CROSS JOIN thresholds t
)
-- Final output: join back to customer table for metadata
SELECT
c.customer_id,
c.email,
c.signup_date,
c.sales_rep_id,
cs.lifetime_value,
cs.customer_tier,
cs.ltv_zscore,
cs.last_order_date,
cs.orders_above_category_avg,
CURRENT_DATE - cs.last_order_date AS days_since_last_order
FROM customers c
JOIN customer_scores cs ON c.customer_id = cs.customer_id
ORDER BY cs.lifetime_value DESC;
This query scans orders and products exactly once in the order_metrics CTE. Every subsequent CTE reads from the materialized result of the previous one. The total data touched is minimized.
Note the CROSS JOIN thresholds t — thresholds is a single-row result (scalar-like), so the cross join is an efficient way to apply those scalar values across every row in customer_summary without a subquery per row.
CSE isn't always the right move. Here are situations where "factoring" a subexpression actually hurts performance.
-- BAD: Materializes ALL orders before filtering
WITH all_orders AS MATERIALIZED (
SELECT * FROM orders
JOIN order_line_items oli ON orders.order_id = oli.order_id
JOIN products p ON oli.product_id = p.product_id
)
SELECT * FROM all_orders WHERE customer_id = 12345 AND order_date > '2024-01-01';
-- BETTER: Let the planner push the filter down
WITH all_orders AS NOT MATERIALIZED (
SELECT * FROM orders
JOIN order_line_items oli ON orders.order_id = oli.order_id
JOIN products p ON oli.product_id = p.product_id
)
SELECT * FROM all_orders WHERE customer_id = 12345 AND order_date > '2024-01-01';
-- OR: just write the query directly without the CTE
If a CTE result is 10 million rows but you're filtering it down to 500, materializing the 10 million rows wastes memory and time. Let the predicate push down.
-- This CTE prevents the optimizer from using an index skip scan
-- that would have been available if the subquery were inlined
WITH customer_categories AS MATERIALIZED (
SELECT DISTINCT primary_category FROM customers
)
SELECT * FROM products p
WHERE p.category IN (SELECT primary_category FROM customer_categories);
-- Without the CTE, the optimizer might choose a semi-join strategy
-- that avoids materializing the customer_categories set entirely
SELECT * FROM products p
WHERE p.category IN (SELECT DISTINCT primary_category FROM customers);
Sometimes the optimizer knows better. Force materialization only when you have evidence (from execution plans) that the default behavior is wrong.
CTEs are excellent for readability, but don't let the desire for clean code override execution efficiency. A 12-level CTE chain where each level is referenced exactly once is just bureaucracy — the planner has to wade through 12 logical transformations, each of which may degrade cardinality estimates.
The cardinality estimation problem: Every time the optimizer estimates how many rows a CTE will return, it introduces potential error. In a long CTE chain, these estimation errors compound. If
order_metricsreturns an estimated 5 million rows but the planner thinks it'll return 500,000, every downstream CTE will make join and memory decisions based on the wrong number. Sometimes one well-structured query with a few key derived tables produces better estimates than 10 CTEs with compounding errors.
You have the following schema:
events(event_id, user_id, event_type, event_date, session_id, revenue)users(user_id, email, signup_date, country, plan_type)sessions(session_id, user_id, start_time, end_time, channel)Task: Write a query that returns, for each user:
Constraints:
EXPLAIN ANALYZE (or your engine's equivalent) to verify execution plan behaviorStarter structure:
WITH
user_revenue AS MATERIALIZED ( ... ),
country_thresholds AS MATERIALIZED ( ... ),
session_stats AS MATERIALIZED ( ... ),
channel_ranked AS MATERIALIZED ( ... ),
primary_channel AS MATERIALIZED ( ... )
SELECT ...
FROM users u
JOIN ...
Fill in each CTE, verify the final query touches each base table exactly once, and check the execution plan to confirm materialization.
On PostgreSQL versions before 12, the MATERIALIZED keyword doesn't exist — CTEs were always materialized. If you're on PostgreSQL 12+ and your CTE isn't materializing despite the keyword, check the PostgreSQL version (the keyword requires 12+) and ensure there's no syntax error causing fallback to default behavior.
This usually happens when:
work_mem (PostgreSQL) or tempdb allocation (SQL Server) is too smallANALYZE (PostgreSQL) or UPDATE STATISTICS (SQL Server) after populating the temp tableIn PostgreSQL, this appears as multiple CTE Scan nodes with the same CTE name. This can happen when a CTE is used inside a nested loop where the outer side produces many rows — the CTE is read once per outer row. Add MATERIALIZED and the CTE result is stored; or restructure the query so the CTE is joined at the top level rather than used as a correlated subquery.
SQL Server doesn't support MATERIALIZED keyword in CTE definitions. Your options are:
#temp tables (most reliable)SELECT INTO #tmp FROM (...) CTE patternOPTION (MAXRECURSION 0) or other query-level hints can influence plan shapes, but this is engine-version-dependentThis is a correctness issue, not a performance issue. Common causes:
JOIN vs. correlated subquery contexts (EXISTS vs. IN vs. JOIN handle NULLs differently)DISTINCT in a derived table changes row counts in unexpected waysAlways validate refactored queries against the original results on a sample dataset before deploying.
Query rewriting for common subexpression elimination is one of the highest-leverage optimization skills you can develop. The core principles are:
MATERIALIZED for within-query CSE on PostgreSQL; temp tables for SQL Server and multi-statement scenarios; materialized views for cross-session repeated accessThe most important mindset shift is treating SQL like you'd treat application code: name your intermediate computations, define them once, and reference the named result everywhere it's needed. CTEs are not just a readability tool — when used correctly, they're a precision instrument for controlling what the database actually computes.
Next steps in your learning path:
The query that took 45 seconds and got you into this lesson? With the techniques here, you should be able to get it under 5 seconds on the same hardware. Go verify it with EXPLAIN (ANALYZE, BUFFERS).
Learning Path: Advanced SQL Queries