Fix Slow Dashboard Query: Filter Early + Materialize

advanced

Dashboard query takes 45 seconds. Three issues: (1) CTE scans entire table without filtering, (2) CTE referenced 3 times but not materialized, (3) No indexes.

-- BEFORE: 45 seconds (multiple issues)

WITH all_sales AS (

SELECT *

FROM sales -- 50M rows, no filtering!

)

SELECT 'High Value' as segment, *

FROM all_sales

WHERE sale_date >= '2024-01-01' AND amount > 1000 -- Filter AFTER CTE

UNION ALL

SELECT 'Medium Value', *

FROM all_sales

WHERE sale_date >= '2024-01-01' AND amount BETWEEN 100 AND 1000

UNION ALL

SELECT 'Low Value', *

FROM all_sales

WHERE sale_date >= '2024-01-01' AND amount < 100;

-- Problems:

-- 1. CTE scans 50M rows (no filter)

-- 2. CTE executes 3 times (not materialized)

-- 3. No index on sale_date or amount

-- Total: 150M rows scanned

-- AFTER: 0.8 seconds (all issues fixed)

-- Step 1: Create indexes

CREATE INDEX idx_sales_date_amount ON sales(sale_date, amount);

-- Step 2: Filter in CTE + materialize

WITH recent_sales AS MATERIALIZED (

SELECT *

FROM sales

WHERE sale_date >= '2024-01-01' -- Filter IN CTE

)

SELECT 'High Value' as segment, *

FROM recent_sales

WHERE amount > 1000

UNION ALL

SELECT 'Medium Value', *

FROM recent_sales

WHERE amount BETWEEN 100 AND 1000