-- Not checking execution plan (flying blind)

WITH sales_summary AS (

SELECT region, SUM(amount) as total

FROM sales

GROUP BY region

)

SELECT * FROM sales_summary;

-- Assumption: Query is fast

-- Reality: May be slow, but you don't know why

-- Always check execution plan first

EXPLAIN ANALYZE

WITH sales_summary AS (

SELECT region, SUM(amount) as total

FROM sales

GROUP BY region

)

SELECT * FROM sales_summary;

-- Shows:

-- - Execution time

-- - Rows scanned

-- - Index usage

-- - Materialization status

-- Now you know what to optimize

Never optimize without checking the execution plan first. EXPLAIN ANALYZE shows actual performance: execution time, rows scanned, whether indexes are used, whether CTE is materialized. Without this data, you're guessing. The execution plan tells you exactly what's slow: full table scan? CTE executed multiple times? Missing index? Always measure before optimizing.

Use EXPLAIN ANALYZE before every optimization. Measure, don't guess.

Critical

Query is slow but you don't know why. Can't optimize without data.

-- Filtering after CTE (scans entire table)

WITH all_orders AS (

SELECT *

FROM orders -- 50M rows

)

SELECT *

FROM all_orders

WHERE order_date >= '2024-01-01' -- Filter AFTER

AND status = 'completed';

-- CTE scans 50M rows, then filters to 500K

-- Execution time: 30 seconds

-- Filter inside CTE (scans only needed rows)

WITH recent_completed_orders AS (