CTE performance depends on three factors: (1) Materialization - does the CTE execute once or multiple times? (2) Filtering - does the CTE scan the entire table or just needed rows? (3) Indexing - can the database use indexes or must it do full table scans? A slow CTE query usually has one or more of these issues. The fix: check execution plan with EXPLAIN ANALYZE, identify the bottleneck, apply the right optimization. Understanding these three factors prevents 90% of CTE performance problems.

**

**1. Materialization: Execute Once or Multiple Times?**

PostgreSQL 12+ defaults to NOT MATERIALIZED (inlining). If CTE is referenced multiple times, it executes multiple times.

```sql

-- Problem: CTE executes 3 times (45 seconds)

WITH monthly_sales AS (

SELECT month, SUM(amount) as total

FROM sales -- 50M rows

GROUP BY month

)

SELECT * FROM monthly_sales WHERE total > 100000

UNION ALL

SELECT * FROM monthly_sales WHERE total <= 100000

UNION ALL

SELECT * FROM monthly_sales WHERE month = '2024-01';

-- Scans 50M rows 3 times = 150M rows

-- Solution: Add MATERIALIZED hint (8 seconds)

WITH monthly_sales AS MATERIALIZED (

SELECT month, SUM(amount) as total

FROM sales -- 50M rows

GROUP BY month

)

SELECT * FROM monthly_sales WHERE total > 100000

UNION ALL

SELECT * FROM monthly_sales WHERE total <= 100000

UNION ALL

SELECT * FROM monthly_sales WHERE month = '2024-01';

-- Scans 50M rows once, reuses results

```

**How to check:** Use EXPLAIN ANALYZE

```sql

EXPLAIN ANALYZE

WITH cte AS (SELECT ...)

SELECT * FROM cte;

-- Look for:

-- "CTE Scan on cte" = Materialized (good if reused)

-- Inlined subquery = Not materialized (bad if reused)

```

**2. Filtering: Scan Entire Table or Just Needed Rows?**