AI agent skill for SQL tuning — read EXPLAIN ANALYZE plans, propose composite/covering/partial indexes, catch N+1 / OFFSET / LIKE-%prefix anti-patterns, with re-measure protocol. PostgreSQL & MySQL. Works with Claude Code, Codex, Cursor. By Viprasol Tech.
SaferSkills independently audited sql-optimizer (Agent Skill) and scored it 100/100 (green). The audit ran 55 deterministic rules across Security, Supply Chain, Maintenance, Transparency, and Community; it found 0 high-severity and 0 lower-severity findings. The full rule-by-rule trace and per-finding evidence are below. Free, methodology-open.
Findings & checks · 0 flagged
Every scanned point with the score it earned and what moved between them.
First recorded scan — no prior version to compare against.
The primary manifest — the file an agent reads to learn what this artifact does.
A disciplined, repeatable methodology for making slow SQL fast — read the EXPLAIN ANALYZE plan, find the single worst node, hypothesize the single fix that moves it, ship it, and re-measure. The skill is grounded in how senior DBAs actually work: small, ordered, evidence-driven changes — not a shotgun blast of CREATE INDEX statements.
Most "slow query" tickets come down to one of a small number of patterns: a missing or wrong-order composite index, a stale-statistics cardinality miss, a sort spilling to disk, or an N+1 in the app layer. This skill encodes the workflow, the plan-reading glossary, the indexing rules (leftmost-prefix, covering, partial, expression), the anti-patterns, and a red-flag scan into a single repeatable pass.
Activate when the user:
EXPLAIN FORMAT=JSON) output and asksyou to read it.
("should we add this index?", "is this column order right?").
Prisma, EF Core) and asks why it's slow — or shares logs that look like N+1.
an APM trace with a hot SQL call.
"is this an N+1?", "how do I read this plan?", "is my composite index in the right order?"
Honest scope & limits — read first. - I cannot run the query against a live database. I reason over what you provide: the query, theEXPLAINoutput, the schema/DDL, row counts, sample data, and engine/version. - Plans are data- and config-dependent. A plan on a 1k-row dev table says nothing about a 100M-row prod table — always re-run `EXPLAIN ANALYZE` on prod-sized data. - Index changes are not free: every index slows writes and consumes buffer cache. Verify the win before shipping. - ⚠️ `EXPLAIN ANALYZE` on an `INSERT` / `UPDATE` / `DELETE` actually performs the write. Wrap inBEGIN; ... ROLLBACK;or run on a clone. - This skill is not a substitute for proper APM + slow-query monitoring in production.
Before reading a single plan line, establish exactly what you're tuning.
This skill goes deepest on PostgreSQL and MySQL 8+, with coverage of SQLite/SQL Server where the workflow is the same. Version matters: PG 12+ inlines CTEs by default, MySQL 8 added hash join and histograms, etc.
Capture the actual SQL it emits (django.db.connection.queries, Rails ActiveRecord::Base.logger, Hibernate show_sql=true, etc.).
CREATE TABLE for every table in the query, plusevery existing index (\d+ tablename in PG, SHOW CREATE TABLE in MySQL). Without this you cannot tell what's missing.
different things at 10M vs 10B. For the worst query, also: rows after each predicate (SELECT count(*) FROM t WHERE …).
EXPLAIN (ANALYZE, BUFFERS) inPG and EXPLAIN ANALYZE or EXPLAIN FORMAT=JSON in MySQL 8+. Plain EXPLAIN is a guess; ANALYZE runs the query for real. (See the DML warning above.)
a hot OLTP query (every web request) or an analytical query (nightly job)? The fix calculus is different.
connection pool size, replication setup. Tell-tale signs of parameter sniffing or plan instability.
If any of these are missing, ask — don't invent them.
1. Reproduce on real data — tiny dev sets always pick the wrong plan.
2. Capture the plan — EXPLAIN (ANALYZE, BUFFERS) in PG;
EXPLAIN ANALYZE / FORMAT=JSON in MySQL 8+.
3. Diagnose the worst node — highest (actual_time × loops) or biggest
"Rows Removed by Filter" wins.
4. Hypothesize one fix — missing index? wrong order? stale stats?
N+1? subquery that should be a join?
5. Fix ONE thing, re-EXPLAIN — never two changes at once.
6. Re-measure hot AND cold cache — hot run hides disk cost; do both.The order is non-negotiable. Skipping step 1 wastes everyone's afternoon. Skipping step 6 ships a "fix" that only works while pages are still in RAM.
Every PostgreSQL and MySQL plan is built from a small set of operators. Knowing what each one means — and when it's a red flag — is 80% of the skill.
| Node | What it does | Good when… | Red flag when… |
|---|---|---|---|
| Seq Scan | Full table scan | Table is small (<10k rows) or you genuinely need most rows | Big table + selective filter (e.g. WHERE country='IN' on 100M rows) |
| Index Scan | Walks an index, then fetches the heap | Index is selective and you need columns not in the index | Filter is unselective — you read most of the index and most of the heap |
| Index Only Scan | Index alone satisfies the query (covering index + visibility map up to date) | Hot read paths with a covering / INCLUDE index | (Usually none — this is the target) |
| Bitmap Index Scan + Bitmap Heap Scan | Gather many index hits, then read heap in physical order | Medium-selectivity filters; multiple index OR combinations | "Lossy" recheck on huge result sets — sometimes worse than Seq Scan |
| Nested Loop | For each outer row, probe inner — O(outer × inner-probe-cost) | Outer is tiny AND inner has a usable index on the join key | Outer is huge OR inner has no index ⇒ effectively O(n²) |
| Hash Join | Build hash on smaller side, probe with bigger side | Big × big equi-join | Batches: > 1 ⇒ memory spill — bump work_mem |
| Merge Join | Both inputs sorted on the join key, scan in parallel | Inputs already sorted by index | Forces a Sort node that spills to disk |
| Sort | Order rows | Small in-memory sort | external merge Disk: 4123kB — bump work_mem or add an ordering index |
| Hash Aggregate | Group via hash | Distinct values fit in work_mem | Spills — same fix as Hash Join |
| Group Aggregate | Group via sorted scan | Input is already sorted | Forces a costly Sort node |
| Materialize | Spool inner side for re-use in Nested Loop | Tiny inner | Hides a big inner — usually means the plan picked wrong |
| CTE Scan | Reads a materialized CTE | You actually want to materialize | PG 12+ inlines CTEs unless MATERIALIZED; an unexpected CTE Scan can be a planner barrier |
Beyond the operators, two diagnostic rules matter more than any single node:
node and compare the planner's rows= estimate to the actual rows the node returned. A 10×, 100×, or 10000× miss is a flashing red light: the planner picked the wrong join order, the wrong join strategy, or the wrong index. Fix the stats first, then re-EXPLAIN — often the bad plan goes away on its own.
returned 50 rows but Rows Removed by Filter: 1,200,000, the planner scanned 1.2M rows and threw 99.996% of them away. That's a missing index condition: the filter belongs inside the index, not on top of it.
Most "slow query" tickets are fixed by adding (or fixing the order of) a single index. Index design is its own discipline; here are the rules that matter for tuning.
| Type | Use for | Notes |
|---|---|---|
| B-tree | Equality + range; the default; ASC/DESC orderings | 95% of indexes |
| Hash | Equality only | Rarely worth it — B-tree is almost as fast |
| GIN | Multi-value: JSONB, tsvector, arrays, pg_trgm | The fix for LIKE '%abc%' |
| GiST | Geometric, range types, legacy full-text | Niche |
| BRIN | Huge append-only tables physically sorted by a column (time-series) | Tiny index, fast on a range scan |
| Partial | CREATE INDEX … WHERE active = TRUE | Indexes only the hot subset; smaller + faster |
| Expression | CREATE INDEX … (lower(email)) | Required when the query wraps the column in a function |
For an index INDEX (a, b, c):
| Query predicate | Uses the index? |
|---|---|
WHERE a = ? | ✅ uses the a part |
WHERE a = ? AND b = ? | ✅ uses a, b |
WHERE a = ? AND b = ? AND c = ? | ✅ uses the full index |
WHERE a = ? AND c = ? | ⚠️ uses only a — c becomes a filter (skip-scan possible in some engines but don't rely on it) |
WHERE b = ? | ❌ does not use the index — no leftmost match |
WHERE b = ? AND c = ? | ❌ does not use the index |
ORDER BY a, b | ✅ index ordering can avoid a Sort |
ORDER BY b, a | ❌ wrong order — Sort is needed |
The practical heuristic for column order:
Equality columns first → range columns next → sort columns last.
So a query like WHERE country = ? AND created_at > ? ORDER BY created_at DESC wants INDEX (country, created_at DESC) — not the other way around.
INCLUDE indexesCREATE INDEX idx_users_country_created_at
ON users (country, created_at DESC)
INCLUDE (email);The INCLUDE columns aren't part of the key but are stored in the index leaf. This lets the planner pick an Index-Only Scan — it never has to touch the heap. For hot read paths returning a few columns, this is a massive win.
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';If 95% of orders are complete and you only ever query pending, a partial index is smaller, hotter in cache, and faster than a full index on (status, created_at).
CREATE INDEX idx_users_email_lower ON users (lower(email));
-- Query MUST match the expression:
SELECT * FROM users WHERE lower(email) = '[email protected]';A plain INDEX (email) is defeated by the lower() wrap in the predicate — see pitfall #2.
INDEX (is_active) whereonly two values exist; the planner correctly ignores it.
WHERE upper(email) = ? defeatsINDEX (email). Fix with an expression index or stop wrapping.
WHERE a = ? OR b = ? canforce a full scan. Often a UNION ALL of two indexed queries is faster.
WHERE id = '42' where id is bigint. Thecast can block index use; pass the right type.
single most common index bug.
INSERT / UPDATE /DELETE and consumes buffer cache. Drop the indexes nobody uses.
country = 'US' matches80% of rows; the planner needs to know.
ANALYZE the table; in PG, raisedefault_statistics_target on skewed columns.
a normal B-tree; needs pg_trgm + GIN (PG) or a generated/reverse column.
pg_stat_user_indexes.idx_scan = 0 (or MySQL's sys.schema_unused_indexes). They cost writes and cache forever.
= false. The index is on disk but the planner ignores it. DROP INDEX CONCURRENTLY` and rebuild.
Bad estimates ⇒ wrong plans. Period. If the planner thinks a node will return 100 rows and it returns 10,000,000, it will pick Nested Loop and your query melts.
ANALYZE users; -- refresh per-column histograms
ALTER TABLE users ALTER COLUMN country
SET STATISTICS 1000; -- raise sample size for a skewed column
SET default_statistics_target = 500; -- session/global default; 100 → 500–1000 on skewed schemas
-- Extended statistics for correlated columns:
CREATE STATISTICS users_country_signup_stats (dependencies, ndistinct)
ON country, signup_source FROM users;
ANALYZE users;Autovacuum runs ANALYZE periodically, but only when row churn exceeds a threshold — bulk loads and slow-trickle writes often leave stats stale.
ANALYZE TABLE users; -- refresh InnoDB stats
ANALYZE TABLE users UPDATE HISTOGRAM ON country WITH 64 BUCKETS; -- skewed-column histogramMySQL 8 introduced histograms — use them for skewed columns the optimizer otherwise misjudges.
The pattern: load 100 parents, then for each parent issue a child query — 101 round-trips instead of 1 or 2.
# Rails — N+1
users = User.where(country: "IN") # 1 query
users.each { |u| puts u.orders.count } # +100 queriesfast succession (often microseconds apart).
django.db.backends, RailsActiveRecord logger, Hibernate show_sql, Prisma log: ['query']).
pg_stat_statements row with very high calls and low mean_exec_timefor a query that fetches a parent's children.
for display:
User.where(country: "IN").includes(:orders)User.objects.filter(country="IN").prefetch_related("orders")(or select_related for forward FK / one-to-one).
LEFT JOIN FETCH in JPQL/HQL.db.Users.Include(u => u.Orders).selectinload(User.orders).SELECT * FROM orders WHERE user_id IN (?, ?, ?, …)then group in app code.
sum, not the rows:
SELECT u.id, COUNT(o.id) AS order_count
FROM users u LEFT JOIN orders o ON o.user_id = u.id
WHERE u.country = 'IN' GROUP BY u.id;field resolutions into a single batched query.
| Anti-pattern | Fix |
|---|---|
SELECT * in a hot path | List the columns you actually need; lets covering indexes win |
OFFSET 100000 pagination | Keyset pagination: WHERE id > $last_seen_id ORDER BY id LIMIT 50 |
OR across columns | UNION ALL of two indexed queries is often a better plan |
Scalar subquery in SELECT (runs once per row) | JOIN LATERAL or a CTE that runs once |
NOT IN (subquery) with possible NULLs | NOT EXISTS — NOT IN returns no rows when the subquery returns even one NULL |
LIKE '%abc%' | pg_trgm + GIN index; or full-text search |
DISTINCT masking a missing join key | Find the duplicate-introducing join and fix it |
ORDER BY rand() / ORDER BY RANDOM() | TABLESAMPLE or keyset random; never sort the whole table |
Massive IN (…) lists (> ~1000 items) | Insert into a temp table and JOIN |
| Uncorrelated subquery that runs once | Lift to a JOIN — usually a free win |
These are bigger levers than most micro-index tweaks and are routinely under-tuned.
PostgreSQL (defaults are conservative for any modern server):
| Setting | Typical | What it does |
|---|---|---|
shared_buffers | ~25% of RAM | PG's own page cache |
effective_cache_size | ~60–75% of RAM | Planner's estimate of total cache (PG + OS) — affects index vs seq choice |
work_mem | 4 MB default ⇒ raise per workload | Memory per Sort / Hash node, per parallel worker. Too small ⇒ Disk spill. Too big × many concurrent queries ⇒ OOM |
maintenance_work_mem | 256 MB – 1 GB | Used for CREATE INDEX, VACUUM, etc. |
random_page_cost | 1.1 on SSDs (default 4.0 assumes spinning disks) | Lower value makes the planner prefer index over seq |
MySQL (InnoDB):
| Setting | Typical | What it does |
|---|---|---|
innodb_buffer_pool_size | 50–75% of RAM | InnoDB's page cache; the single biggest knob |
innodb_io_capacity | match your disk | Affects flush + LRU pacing |
tmp_table_size / max_heap_table_size | bump for big GROUP BY | Avoid in-memory → disk tmp table spillover |
These move scans from disk to RAM — the difference between 50 ms and 50 µs per page is the difference between "slow query" and "fast query."
PostgreSQL
jit = on, default since PG 12) — speeds upbig-cost analytical plans by JIT-compiling expression evaluators. Adds startup latency on small queries — disable for OLTP if it shows up as overhead.
and many index scans can run with multiple workers. Tune max_parallel_workers_per_gather.
PARTITION BY RANGE / LIST / HASH. With **partitionpruning, queries hit only the relevant partitions; combined with partition-wise joins**, very large tables become tractable.
MySQL 8
block-nested-loop on big × big joins). Outer joins use hash join from 8.0.20+ when supported.
ALTER TABLE t ALTER INDEX idx INVISIBLE totest removing an index safely (planner ignores it, but it stays on disk and is still maintained on writes; flip back instantly if a query regresses).
Both
query for every literal value, and protect against SQL injection. But: on parameter-sensitive queries this is the source of parameter sniffing / plan instability — same SQL, different plans across runs, because the cached plan was made for an unrepresentative bind value.
Run this checklist over any plan:
Rows Removed by Filter ≫ rows returned (over-fetching).external merge Disk: ...kB.Index Cond is empty but Filter is heavy (wrong/missing index).pg_stat_statements.OFFSET over 5-figure values.LIKE '%...' on a non-trigram column.Batches: > 1 (memory spill).sniffing / plan instability).
pg_index.indisvalid = false).If any box is ticked, name it in the diagnosis.
Return your review in this exact shape.
One of:
locking, missing critical index on a hot path).
Followed by one sentence: "Verdict because …"
| Node | Actual rows | Actual time (ms) | Loops | Flag |
|---|---|---|---|---|
| Seq Scan on users | 750,000 | 4,820 | 1 | 🚩 over-fetch |
| Hash Join | 50 | 5,100 | 1 | ℹ️ |
| Sort | 50 | 5,180 | 1 | 🚩 external merge Disk: 8 MB |
Worst node first. One row per non-trivial node.
Two or three sentences. Root cause(s) only — not the fix yet.
Example: "The Seq Scan on `users` filters 50M rows down to ~750k with a selective `country = 'IN'` predicate; the planner has no index on `country` so it must scan the whole table. The downstream Sort then spills to disk because `work_mem` is the 4 MB default."
Numbered list, smallest-blast-radius first. Always include the exact DDL for index changes, the before/after SQL for query rewrites, and the config command for stats/memory changes.
-- 1. Add the missing predicate index (no table rewrite, online build):
CREATE INDEX CONCURRENTLY idx_users_country_created_at
ON users (country, created_at DESC);
-- 2. Bump work_mem for this session to keep the sort in RAM:
SET work_mem = '64MB';
-- 3. Refresh statistics on the join column:
ANALYZE users;For N+1 fixes, show the single resulting SQL and the ORM change next to each other:
# Before — 1 + N
users = User.objects.filter(country="IN")
for u in users:
u.orders.count()
# After — 1
users = (
User.objects
.filter(country="IN")
.annotate(order_count=Count("orders"))
)The exact commands to confirm the fix:
-- PG: hot run after warm-up
EXPLAIN (ANALYZE, BUFFERS) <the query>;
-- Cold cache (Linux): sudo sysctl -w vm.drop_caches=3
-- then re-run EXPLAIN (ANALYZE, BUFFERS).State the target: "expect Seq Scan → Index Scan, sort in memory, total time < 50 ms."
CREATE INDEX CONCURRENTLY is onlinebut takes ~2× as long, can fail and leave an invalid index, and won't finish if other long transactions block it. Plan a window.
INSERT /UPDATE / DELETE that touches its keys. If the table is write-hot, measure both sides.
country = 'US' (80%of rows) may be terrible for country = 'IN' (1.5%). Prepared statements + a non-representative first bind ⇒ stuck on the wrong plan. Watch for plan instability after the change.
EXPLAIN ANALYZE an UPDATE /DELETE against production without wrapping in BEGIN; ... ROLLBACK; — it actually performs the write.
I cannot run this query against your live database. Plans are data-and-config-dependent — re-run EXPLAIN (ANALYZE, BUFFERS) on prod-sized data, on both hot and cold caches, before declaring victory. Index changes slow writes; verify the win.Mirror the verdict banner:
scan on a > 10M-row table on a request path, long-running write blocking reads, or a data-corruption / lost-update risk (e.g. OFFSET over an evolving result set).
path; an N+1 in a production endpoint; a bad cardinality estimate driving wrong join order.
disk on an analytical query, SELECT * in a hot path.
COUNT(*) vs COUNT(1) — bothare equivalent in modern engines).
considering once this table crosses ~500M rows").
This skill pairs with:
the surrounding code; SQL is one layer.
and you also want to scope authz / rate limit / cost-budget risks.
Educational engineering guidance, not a substitute for production APM, slow-query monitoring, or a qualified DBA on critical systems. Not affiliated with or endorsed by Anthropic.
~30 seconds. Free. No account. Every finding cites a rule and a line of evidence.