spb/ultra-sharp-agent-skills Public

1<!--2Author: Simon-Pierre Boucher3Contact: contact@spboucher.ai4-->56# Patterns — Optimizing SQL Performance78## Contents9- Reading EXPLAIN ANALYZE10- Finding the workload's worst queries11- Index recipes12- N+1 rewrites13- Keyset pagination14- Materialized views15- Dialect deviations (MySQL, SQLite)16- Gotchas1718## Reading EXPLAIN ANALYZE1920```sql21EXPLAIN (ANALYZE, BUFFERS)22SELECT id, total_cents FROM orders23WHERE user_id = 42 AND created_at >= '2026-01-01';24```2526Read inner-most node first; the fix usually targets the node with the largest27`actual time`. Checklist per node:2829| Signal | Meaning | Action |30|---|---|---|31| `Seq Scan` + `Rows Removed by Filter` huge | selective filter, no usable index | add index (see recipes) |32| `rows=3` estimated vs `rows=30000` actual | stale statistics | `ANALYZE orders;` then re-plan |33| `Sort Method: external merge Disk` | sort spills to disk | index matching ORDER BY, or raise `work_mem` for the session |34| `Nested Loop` with huge outer side | join misestimate | fix stats; check join column indexes |35| `Heap Fetches` high on Index Only Scan | visibility map stale | `VACUUM orders;` |3637## Finding the workload's worst queries3839```sql40-- requires: CREATE EXTENSION pg_stat_statements;41SELECT calls, mean_exec_time::int AS mean_ms,42       (calls * mean_exec_time)::int AS total_ms, query43FROM pg_stat_statements44ORDER BY calls * mean_exec_time DESC45LIMIT 10;46```4748Optimize by `total_ms` (aggregate cost), not by single-query time.4950## Index recipes5152```sql53-- Equality-then-range composite (rule 3):54CREATE INDEX orders_user_created_idx ON orders (user_id, created_at);5556-- Covering index for an index-only scan:57CREATE INDEX orders_user_created_cov_idx58    ON orders (user_id, created_at) INCLUDE (total_cents);5960-- Expression index (query must use the same expression):61CREATE INDEX users_email_lower_idx ON users (lower(email));6263-- Partial index for a hot subset:64CREATE INDEX orders_pending_idx ON orders (created_at)65    WHERE status = 'pending';6667-- Trigram index for LIKE '%term%':68CREATE EXTENSION IF NOT EXISTS pg_trgm;69CREATE INDEX users_name_trgm_idx ON users USING gin (display_name gin_trgm_ops);7071-- Build without blocking writes (production):72CREATE INDEX CONCURRENTLY ...;7374-- Find unused indexes:75SELECT indexrelid::regclass, idx_scan76FROM pg_stat_user_indexes77WHERE idx_scan = 0 AND indexrelid::regclass::text NOT LIKE '%_pkey';78```7980## N+1 rewrites8182```python83# ❌ one query per user84for uid in user_ids:85    cur.execute("SELECT * FROM orders WHERE user_id = %s", (uid,))8687# ✅ one query, group in app code88cur.execute(89    "SELECT user_id, id, total_cents FROM orders WHERE user_id = ANY(%s)",90    (user_ids,))91```9293Aggregate-per-parent variant in one round trip:9495```sql96SELECT u.id, COALESCE(SUM(o.total_cents), 0) AS spend97FROM users u98LEFT JOIN orders o ON o.user_id = u.id99WHERE u.id = ANY($1)100GROUP BY u.id;101```102103## Keyset pagination104105```sql106-- page 1107SELECT id, created_at FROM orders108ORDER BY created_at DESC, id DESC LIMIT 20;109110-- next page: pass the last row's (created_at, id)111SELECT id, created_at FROM orders112WHERE (created_at, id) < ($1, $2)113ORDER BY created_at DESC, id DESC LIMIT 20;114115CREATE INDEX orders_created_id_idx ON orders (created_at DESC, id DESC);116```117118Tie-break with `id` is mandatory — `created_at` alone skips/duplicates rows on119equal timestamps. Tradeoff: no random page jumps.120121## Materialized views122123```sql124CREATE MATERIALIZED VIEW daily_revenue AS125SELECT created_at::date AS day, SUM(total_cents) AS revenue_cents126FROM orders GROUP BY created_at::date;127128CREATE UNIQUE INDEX daily_revenue_day_idx ON daily_revenue (day);129REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;  -- needs the unique index130```131132## Dialect deviations (MySQL, SQLite)133134- MySQL: `EXPLAIN ANALYZE` (8.0.18+); no partial/expression indexes before 8.0.13 (functional indexes); use `performance_schema` instead of `pg_stat_statements`; no `INCLUDE` — add columns to the index key.135- SQLite: `EXPLAIN QUERY PLAN`; run `ANALYZE;` to populate stats; partial indexes supported; no concurrent index builds.136137## Gotchas138139- `CREATE INDEX` (without `CONCURRENTLY`) takes a write lock on the table.140- `EXPLAIN` without `ANALYZE` shows estimates only — plans, not reality.141- Casts defeat indexes: `WHERE id::text = $1` seq-scans; cast the parameter instead.142- Low-selectivity indexes (boolean flags) are usually ignored by the planner — a partial index on the rare value works.143- `random_page_cost` default (4.0) is tuned for spinning disks; on SSDs the planner may wrongly prefer seq scans — typical SSD setting is 1.1.144- After bulk loads, run `ANALYZE` (and `VACUUM`) before judging any plan.145- ORMs hide N+1: enable SQL logging before believing "the query is slow" — often it's 500 queries.146