SQL Optimization

---
name: sql-optimization
description: Diagnose and fix slow database queries. Use when a query, endpoint, or page is slow and the database is the suspected bottleneck.
---

# SQL Optimization

Let the query planner tell you the truth: EXPLAIN ANALYZE before and after, always.

## Diagnose

1. Get the real query with real parameters (from logs, pg_stat_statements, or the ORM's query log). ORM-generated SQL must be read as SQL, not guessed from the ORM code.
2. Run `EXPLAIN (ANALYZE, BUFFERS)` on it against production-like data volume. A query that is fast on 100 rows can be catastrophic on 10M.
3. Read the plan for the classic red flags:
   - Sequential scan on a large table where you filter a small subset
   - Rows estimated vs actual off by orders of magnitude (stale stats → ANALYZE the table)
   - Nested loop over huge row counts; sorts spilling to disk
4. Check for N+1 at the application layer: one query per item in a list. Fix with a JOIN, an IN query, or the ORM's eager-loading — and verify the query count dropped.

## Fix patterns

- **Missing index:** add an index matching the WHERE/JOIN/ORDER BY columns. Composite index column order: equality columns first, then range, then sort. Verify the plan actually uses it (an unused index only slows writes).
- **Selecting everything:** select only needed columns; avoid functions on indexed columns in WHERE (`WHERE lower(email) = ...` needs a functional index).
- **Pagination:** replace OFFSET on large sets with keyset/cursor pagination (`WHERE id > :last ORDER BY id LIMIT n`).
- **Count(*)** on huge tables for UI badges: use estimates or a counter table.

## Verify

- Re-run EXPLAIN ANALYZE: report before → after execution time with the same data.
- Confirm writes are not harmed: note any new index's write cost.
- Migrations: add indexes concurrently on live systems (`CREATE INDEX CONCURRENTLY` in Postgres, outside a transaction).