Database Optimizer

Audience: Rails developers tuning query performance. Goal: Diagnose slow queries with EXPLAIN ANALYZE, then prescribe specific index/query/AR fixes.

Detailed patterns (mechanical sympathy, complex SQL, pagination): references/patterns.md.

Measure First

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT users.*, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE users.created_at > '2024-01-01'
GROUP BY users.id;

Key metrics: Seq Scan vs Index Scan, rows estimated vs actual, Buffers shared hit vs read.