spb/ultra-sharp-agent-skills Public

1---2name: optimizing-sql-performance3description: 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.4---56<!--7Author: Simon-Pierre Boucher8Contact: contact@spboucher.ai9-->1011# Optimizing SQL Performance1213## When to use / when NOT to use14- **Use for:** slow queries, index design, query-plan analysis, N+1 elimination, pagination performance.15- **Do NOT use for:** locks/deadlocks/replication incidents (`troubleshooting-databases`), new-schema design (`designing-database-schemas`), plain query authoring (`writing-sql-queries`).1617Default dialect: PostgreSQL.1819## Core rules20211. **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.22232. **Read the plan for the two classic tells:**24   - a **Seq Scan** on a large table filtered by a selective predicate → missing index;25   - a **row-estimate mismatch** (estimated 3 rows, actual 30,000) → stale statistics: run `ANALYZE table` before designing indexes around a lie.26273. **Composite index column order: equality columns first, then the range/sort column.**28   - ✅ `CREATE INDEX ON orders (user_id, created_at)` for `WHERE user_id = $1 AND created_at >= $2`29   - ❌ `(created_at, user_id)` — range first makes the equality column unusable for narrowing.30314. **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.32335. **Drop unused indexes.** Check `pg_stat_user_indexes.idx_scan = 0` over a representative period; an unused index is pure write overhead and storage.34356. **Kill N+1 at the application boundary:** one query per collection, not per row.36   - ✅ `WHERE user_id = ANY($1)` then group in app code, or JOIN37   - ❌ loop issuing `SELECT … WHERE user_id = $1` per user38397. **Keyset pagination for deep pages.**40   - ✅ `WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20`41   - ❌ `OFFSET 100000 LIMIT 20` — scans and discards 100k rows every page.42438. **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.4445## Workflow46471. Capture the exact slow query with real parameter values.482. Baseline: `EXPLAIN (ANALYZE, BUFFERS)` — record total time and the dominant node.493. Apply ONE change (index, rewrite, `ANALYZE`) chosen from the rules above.504. Re-run the same `EXPLAIN (ANALYZE, BUFFERS)`; keep the change only if the dominant node improved and total time dropped meaningfully.515. Repeat 3–4 until the target is met; report before/after timings and every index added or dropped.5253## Edge cases & failure modes54- **Cannot run EXPLAIN ANALYZE on prod writes** → wrap in `BEGIN; EXPLAIN ANALYZE …; ROLLBACK;`.55- **Fast in psql, slow in app** → parameter-sensitive plan or connection/ORM overhead; compare with `PREPARE`/generic plan and log ORM SQL.56- **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).57- **LIKE '%term%'** → B-tree can't help; needs `pg_trgm` GIN index or full-text search.58- **Everything is slow, not one query** → out of scope here; hand to `troubleshooting-databases`.5960## References61Plan-reading walkthrough, index recipes, N+1 and pagination rewrites: see [references/patterns.md](references/patterns.md).62