# Query Optimization

> Find and fix slow Postgres queries — rank by pg_stat_statements, diagnose with EXPLAIN (ANALYZE, BUFFERS), kill N+1 at the ORM layer, add the right index. Use when an endpoint is slow, DB CPU is high, or before scaling traffic. Not for schema/index design from scratch (use schema-design) or result-level caching (use caching-strategy).

- Skill: `jaykim88/query-optimization` (Agent Skill)
- Install (CLI): `npx skillmds@latest add jaykim88/query-optimization`
- Raw SKILL.md: https://api.skillmd.com/api/skills/jaykim88/query-optimization/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- License: MIT
- Author: JayKim88 (https://skillmd.com/u/jaykim88)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/jaykim88/query-optimization

---


# Query Optimization

## Purpose
Find the queries that actually cost the most, understand *why* they're slow from the plan, and fix the root cause (missing index, N+1, bad estimate) — measure-first, never guess.

**Universal** — the find-rank-diagnose-fix workflow (statement stats → query plan → index/rewrite) applies to any SQL DB; tool names differ.

## Procedure

1. **Rank queries by total cost — measure first**
   - Use `pg_stat_statements` ordered by `total_exec_time` (not single-call time)
   - The biggest win is usually a moderately-slow query called millions of times, not the one slow query
   - Identify the top 5 offenders

2. **Diagnose each offender with `EXPLAIN (ANALYZE, BUFFERS)`**
   - Look for: **Seq Scan** on large tables, **bad row estimates** (estimated vs actual rows far apart), **nested loops** over big sets, high **buffers read** (IO)
   - `ANALYZE` runs the query; `BUFFERS` shows IO — both needed for truth
   - Compare **planning time vs execution time** — if planning dominates, suspect many partitions, complex inheritance, or stale prepared-statement plans
   - "Slow query" can really be **connection-pool exhaustion** (queries queue behind connections, not behind themselves) — confirm via `pg_stat_activity` waits before chasing the plan

3. **Detect N+1 at the ORM layer first**
   - Symptom: one query + N follow-up queries per row
   - Fix in the ORM (`include` / `findMany` batching / DataLoader) before reaching for raw SQL
   - N+1 is the #1 backend perf bug and it hides in the application, not the DB

4. **Fix by root cause**
   - Missing index → add the right type (see `schema-design`)
   - Bad estimate → `ANALYZE` the table / increase statistics target. **Multi-column correlations** (e.g. `WHERE country='KR' AND city='Seoul'`) need `CREATE STATISTICS` (extended stats) — single-column stats over-estimate selectivity
   - Index used to work, now slow → suspect **index bloat** from heavy UPDATE/DELETE; `REINDEX INDEX CONCURRENTLY` and tune autovacuum
   - **Hot-row contention** (many writers contending on one row) → not an index problem; design `SELECT ... FOR UPDATE SKIP LOCKED` for queue patterns, or partition the hot key (cross-ref `transaction-management`)
   - N+1 → batch / eager-load
   - Genuinely expensive aggregate → materialized view or cache (see `caching-strategy`)

5. **Validate (validation loop)**
   - Re-run `EXPLAIN ANALYZE`; if still Seq Scan / still slow → the index isn't being used (check column order, type mismatch, function-wrapping) → adjust and re-run
   - Re-check `pg_stat_statements` after deploy — confirm the query dropped in ranking

## Anti-patterns

| ❌ Anti-pattern | ✅ Correct |
|---|---|
| Adding indexes by guessing | `pg_stat_statements` → `EXPLAIN ANALYZE` → targeted index |
| Optimizing the single slowest query | Optimize highest `total_exec_time` (frequency × cost) |
| Fixing N+1 with raw SQL | Fix at ORM layer (batching/eager-load) first |
| `EXPLAIN` without `ANALYZE` | `EXPLAIN (ANALYZE, BUFFERS)` for real timings + IO |
| Index on `WHERE lower(email)` but querying `email` | Match index expression to query expression |
| Diagnosing as "slow query" when it's connection-pool saturation | Confirm via `pg_stat_activity` waits before tuning the plan |
| Bloated index after long heavy writes (silently slow) | `REINDEX INDEX CONCURRENTLY`; tune autovacuum so it doesn't reach this state |
| Single-column stats for correlated predicates | `CREATE STATISTICS` (extended stats) for multi-column correlations |

## Severity tiers

| Tier | Examples | Action SLA |
|---|---|---|
| **Critical** | N+1 on a hot endpoint causing timeouts; full table scan on a multi-million row table per request | Fix immediately |
| **Major** | Missing index on a frequent query (high total_exec_time); unbounded query (no LIMIT) | Fix this sprint |
| **Minor** | Suboptimal plan on a rare query; slightly stale table statistics | Schedule within 2 sprints |

## Completion Criteria
- [ ] Top 5 `pg_stat_statements` offenders diagnosed
- [ ] No Seq Scan on large tables in hot paths
- [ ] N+1 eliminated at ORM layer
- [ ] Each fix verified with before/after `EXPLAIN ANALYZE`
- [ ] All Critical findings fixed; all Major scheduled

## Output
- **Query audit report**: `docs/query-audit-YYYY-MM-DD.md` — top offenders, plans, fixes, before/after timings
- **Index migrations** + ORM eager-load changes
- **Commit format**: `perf(db): eliminate N+1 in <endpoint>` / `perf(db): add index for <query>`

## Implementation

### TypeScript + Prisma + Postgres (default)
- Stats: `SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;`
- N+1: Prisma `include` / `select` with relations; avoid per-row `findUnique` in a loop
- Plan: `EXPLAIN (ANALYZE, BUFFERS) <query>` in psql / Supabase SQL editor
- Prisma query logging (`log: ['query']`) in dev to spot N+1
- **PgBouncer transaction-pooling caveat**: it doesn't keep server-side prepared statements across transactions. Either run PgBouncer in session-pooling for prepared-statement workloads, or disable Prisma prepared statements (`?statement_cache_size=0`) — silent perf cliff otherwise
- Bloat / autovacuum: `pg_stat_user_indexes` for unused indexes; `pgstattuple` for bloat; `REINDEX INDEX CONCURRENTLY` to rebuild without lock

### Other stacks
- **Python**: SQLAlchemy `selectinload`/`joinedload` for N+1; `pg_stat_statements` same
- **Go**: sqlc / GORM `Preload`; same Postgres tooling
- **Universal**: `pg_stat_statements` + `EXPLAIN ANALYZE` are Postgres; MySQL uses `performance_schema` + `EXPLAIN ANALYZE` (8.0+)

## Related skills
- `schema-design` — the fix is often the right index type
- `performance-profiling` — query time is usually the top backend bottleneck
- `caching-strategy` — cache the query result when optimization hits its limit

## Reference
- **Key insight encoded**: Use `pg_stat_statements` to rank by total time, then `EXPLAIN (ANALYZE, BUFFERS)` the offenders looking for Seq Scans / bad row estimates / N+1 before adding indexes. Three senior diagnostics that look like "slow query" but aren't: connection-pool exhaustion, index bloat after heavy writes (re-`REINDEX CONCURRENTLY`), and correlated-predicate misestimation (needs extended `CREATE STATISTICS`). PgBouncer transaction-pooling silently breaks server-side prepared statements — pin pooling mode to your driver's assumption.