EXPLAIN ANALYZE is your debugger for SQL. Seq Scan on a large table = problem. Index Scan = solution.
Reading EXPLAIN¶
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE user_id = 123 AND status = ‘active’; – Bad: – Seq Scan on orders (cost=0..10000 rows=100 width=200) – Filter: (user_id = 123 AND status = ‘active’) – Rows Removed by Filter: 99900 – Good: – Index Scan using idx_orders_user_status (cost=0..10 rows=100 width=200) – Index Cond: (user_id = 123 AND status = ‘active’)
Optimization Techniques¶
- Add missing indexes (see EXPLAIN)
- Rewrite subqueries as JOINs
- Materialized views for repeated aggregations
- LIMIT for top-N queries
- Partition large tables
Materialized View¶
CREATE MATERIALIZED VIEW monthly_stats AS SELECT DATE_TRUNC(‘month’, created_at) as month, COUNT(*) as orders, SUM(amount) as revenue FROM orders GROUP BY 1; – Refresh (manually or via cron) REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_stats;
Key Takeaway¶
Always use EXPLAIN ANALYZE. Seq Scan = add an index. Materialized views for aggregations. Monitor slow queries.