# 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.

# 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.