**Performance Optimization Checklist:**

1. **Always check execution plan first**

- EXPLAIN ANALYZE shows actual performance

- Don't guess, measure

2. **Materialize reused CTEs**

- If CTE referenced 2+ times, add MATERIALIZED

- Check execution plan to verify

3. **Filter as early as possible**

- Push WHERE conditions into CTE definition

- Reduces data volume early

4. **Index columns used in:**

- WHERE clauses

- JOIN conditions

- ORDER BY clauses

- GROUP BY columns

5. **Consider alternatives:**

- Window functions for running totals, rankings

- JOINs for simple combinations

- Temp tables for very large intermediate results

6. **Monitor memory usage:**

- Materialized CTEs use work_mem (PostgreSQL)

- Large CTEs can spill to disk (slow)

- Increase work_mem if needed

7. **Update statistics:**

- ANALYZE table ensures accurate row estimates

- Bad estimates lead to bad execution plans

8. **Avoid over-nesting:**

- Too many nested CTEs hurt readability

- Consider breaking into temp tables

**Benchmarks (50M row table):**

| Issue | Before | After | Improvement |

|-------|--------|-------|-------------|

| No filtering in CTE | 30s | 2s | 15x faster |

| CTE executed 3 times | 45s | 8s | 5.6x faster |

| Missing index | 30s | 0.5s | 60x faster |

| All three issues | 120s | 1s | 120x faster |

Filter early: Push WHERE into CTE definition (100x improvement possible)

Materialize reused CTEs: Add MATERIALIZED hint (3-5x improvement)

Index join columns: CREATE INDEX on foreign keys (60x improvement)

Use window functions: Replace correlated subqueries (100x improvement)