---
name: optimizing-sql-performance
description: Diagnoses and fixes slow SQL queries through query plans, indexing strategy, and query rewrites. Use when the user says a query or page is slow, asks to read or interpret EXPLAIN or EXPLAIN ANALYZE output, asks which index to create, mentions N+1 queries, slow pagination, missing indexes, or asks to speed up a report or endpoint backed by SQL. Do not use for operational incidents such as locks, deadlocks, connection exhaustion, or replication lag, and not for designing new schemas from scratch — separate skills cover those.
---

<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# Optimizing SQL Performance

## When to use / when NOT to use
- **Use for:** slow queries, index design, query-plan analysis, N+1 elimination, pagination performance.
- **Do NOT use for:** locks/deadlocks/replication incidents (`troubleshooting-databases`), new-schema design (`designing-database-schemas`), plain query authoring (`writing-sql-queries`).

Default dialect: PostgreSQL.

## Core rules

1. **Measure first — never optimize on a hunch.** Get the real query, run `EXPLAIN (ANALYZE, BUFFERS)`, and on servers check `pg_stat_statements` for the workload's actual top offenders before touching anything.

2. **Read the plan for the two classic tells:**
   - a **Seq Scan** on a large table filtered by a selective predicate → missing index;
   - a **row-estimate mismatch** (estimated 3 rows, actual 30,000) → stale statistics: run `ANALYZE table` before designing indexes around a lie.

3. **Composite index column order: equality columns first, then the range/sort column.**
   - ✅ `CREATE INDEX ON orders (user_id, created_at)` for `WHERE user_id = $1 AND created_at >= $2`
   - ❌ `(created_at, user_id)` — range first makes the equality column unusable for narrowing.

4. **Covering index when the same hot query still heap-fetches:** `INCLUDE` the selected columns to enable index-only scans. Reserve for measured hot paths — every index taxes writes.

5. **Drop unused indexes.** Check `pg_stat_user_indexes.idx_scan = 0` over a representative period; an unused index is pure write overhead and storage.

6. **Kill N+1 at the application boundary:** one query per collection, not per row.
   - ✅ `WHERE user_id = ANY($1)` then group in app code, or JOIN
   - ❌ loop issuing `SELECT … WHERE user_id = $1` per user

7. **Keyset pagination for deep pages.**
   - ✅ `WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20`
   - ❌ `OFFSET 100000 LIMIT 20` — scans and discards 100k rows every page.

8. **Materialized view only when it earns its keep:** an aggregation proven slow by rule 1, tolerating staleness, refreshed on a schedule (`REFRESH MATERIALIZED VIEW CONCURRENTLY`). Otherwise fix the query/index.

## Workflow

1. Capture the exact slow query with real parameter values.
2. Baseline: `EXPLAIN (ANALYZE, BUFFERS)` — record total time and the dominant node.
3. Apply ONE change (index, rewrite, `ANALYZE`) chosen from the rules above.
4. Re-run the same `EXPLAIN (ANALYZE, BUFFERS)`; keep the change only if the dominant node improved and total time dropped meaningfully.
5. Repeat 3–4 until the target is met; report before/after timings and every index added or dropped.

## Edge cases & failure modes
- **Cannot run EXPLAIN ANALYZE on prod writes** → wrap in `BEGIN; EXPLAIN ANALYZE …; ROLLBACK;`.
- **Fast in psql, slow in app** → parameter-sensitive plan or connection/ORM overhead; compare with `PREPARE`/generic plan and log ORM SQL.
- **Index exists but unused** → type mismatch (`text` vs `varchar` cast), function on the column (`lower(email)` needs an expression index), or the planner is right (low selectivity).
- **LIKE '%term%'** → B-tree can't help; needs `pg_trgm` GIN index or full-text search.
- **Everything is slow, not one query** → out of scope here; hand to `troubleshooting-databases`.

## References
Plan-reading walkthrough, index recipes, N+1 and pagination rewrites: see [references/patterns.md](references/patterns.md).