A slow SQL query can kill an entire application. EXPLAIN ANALYZE is your best friend for optimization.
EXPLAIN ANALYZE¶
EXPLAIN ANALYZE SELECT u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at > ‘2024-01-01’ GROUP BY u.name ORDER BY order_count DESC LIMIT 10; – Seq Scan = bad (full table scan) – Index Scan = good – Bitmap Index Scan = OK for larger result sets
Indexes¶
– B-tree (default) — equality, range CREATE INDEX idx_users_email ON users(email); – Composite index — column order matters! CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC); – Partial index — only a subset of data CREATE INDEX idx_active_users ON users(email) WHERE active = true; – GIN index — JSONB, full-text CREATE INDEX idx_data ON products USING GIN(metadata);
Anti-Patterns¶
- SELECT * (loads unnecessary columns)
- N+1 problem (see separate article)
- LIKE ‘%term%’ (cannot use an index)
- Functions on indexed columns WHERE YEAR(created_at) = 2024
- Missing LIMIT on large tables
Key Takeaway¶
Always use EXPLAIN ANALYZE. Proper indexes, no SELECT *, composite indexes in the right order.