Database Query Optimization

Slow queries rarely announce themselves. By the time users complain, the bad pattern has already been in production for weeks. Here's how to catch and fix them before they hurt.

Read the plan, don't guess

EXPLAIN ANALYZE is the first thing to reach for. It shows what the database actually did — seq scan vs index scan, join strategy, row estimates vs actuals. Huge gaps between estimated and actual rows usually mean stale statistics.

EXPLAIN ANALYZE
SELECT u.*, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id;

Index what you filter and join on

The most common fix is also the most basic: add an index on the columns in WHERE, JOIN, and ORDER BY. A composite index (user_id, created_at DESC) can replace two separate ones and enable index-only scans.

Kill N+1 at the source

The N+1 pattern — one query for a list, then one per item — is the silent killer. An ORM usually offers eager loading (include, with, preload). Use it. When in doubt, turn on query logging in dev and count.

Benchmark under realistic load

A query that's fast on 1,000 rows might be catastrophic on 10 million. Seed your dev database with production-shaped data, or run EXPLAIN against a copy. Optimizing against empty tables is optimizing for the wrong problem.