SQL Query Optimizer
EXPLAIN-driven, not folklore: read the plan, fix the expensive node, verify it moved. Recommends only indexes the planner will use.
v1.0.0 · ~587 tokens · ⬇ 0 · Updated July 6, 2026
What it does
Optimizes a slow query from evidence: EXPLAIN (ANALYZE, BUFFERS), find the expensive node, apply the specific win (index with correct column order, sargable rewrite, keyset pagination, N+1 collapse), then re-run EXPLAIN to confirm. Names the write-cost trade-off; refuses to add an index without the plan that justifies it. Postgres-first.
Example uses
Diagnose a slow dashboard query
A production query is slow and you want the fix backed by the actual plan, not folklore.
This Postgres query powers our admin dashboard and takes 8 seconds: it joins orders to customers, filters on lower(c.email) LIKE '%@acme.com', and sorts by created_at DESC LIMIT 50. Run EXPLAIN (ANALYZE, BUFFERS), find the most expensive node, and give me the specific fix — index, sargable rewrite, or both — plus the write-cost trade-off.Replace deep OFFSET pagination
Pagination gets slower the deeper users go into the list.
Our activity feed uses LIMIT 25 OFFSET n, and page 400 takes 6 seconds while page 1 is instant. Convert the query to keyset pagination and confirm with EXPLAIN ANALYZE that the expensive node actually changed.Justify an index before adding
Someone proposes an index and you want proof the planner will actually use it.
A teammate wants an index on users(status) because 'the query filters on status' — but status has three distinct values across 2 million rows. Check with EXPLAIN whether the planner would ever use it, and if not, tell us what the plan says the query actually needs.Install
# 1. Create the skill folder in your Claude setup mkdir -p ~/.claude/skills/sql-query-optimizer # 2. Download SKILL.md into it (or move the file you just downloaded) # → ~/.claude/skills/sql-query-optimizer/SKILL.md # 3. Claude Code auto-discovers it on next launch.
Inside the skill
--- name: sql-query-optimizer description: Diagnose and speed up a slow SQL query with EXPLAIN-driven analysis, not guesswork. Use when a query is slow, a page is slow because of the DB, or the user asks "optimize this query", "why is this slow", "add the right index". Postgres-first. --- # SQL Query Optimizer Optimize from evidence (the plan), not folklore. The database tells you exactly what it's doing — read it before changing anything. ## Step 1 — Get the plan `EXPLAIN (ANALYZE, BUFFERS) <query>` on realistic data. Read for: - **Seq Scan** on a large table where a filter/join should use an index. - **Rows estimated vs actual** wildly off → stale stats (`ANALYZE`) or bad predicate. - **Nested Loop** over many rows (should be hash/merge join), or a **Sort**/**Hash** spilling to disk. - The single most expensive node (highest actual time × loops) — fix that first. ## Common wins (apply the one the plan points to) - **Missing index**: on the columns in `WHERE` / `JOIN` / `ORDER BY`. Composite index column order = equality columns first, then range/sort. Build `CONCURRENTLY` in prod. - **Non-sargable predicate**: `WHERE fn(col) = x` or `col::text = ...` can't use an index → rewrite so the column is bare, or add an expression index. - **SELECT ***: fetch only needed columns (enables index-only scans, less I/O). - **N+1**: many small queries in a loop → one join or `WHERE id = ANY(...)`. - **OFFSET pagination** deep in a table → keyset pagination (`WHERE id > last`). - **Over-fetching then filtering in app** → push the filter/limit into SQL. ## Verify Re-run EXPLAIN ANALYZE after the change. Confirm the expensive node changed (Seq Scan → Index Scan, lower actual time). If it didn't improve, revert — an unused index is pure cost (write amplification, planning). Measure, don't assume. ## Output ``` QUERY OPTIMIZATION Diagnosis: <the expensive node + why, from the plan> Change: <index / rewrite / pagination — the specific one> Before → after: <actual ms, or estimated if no prod data> Trade-off: <index write cost / storage — name it> ``` ## Rules - No plan, no diagnosis. Never "add an index" without the EXPLAIN that justifies it. - Every index has a cost on writes — recommend only ones the plan will actually use. - Realistic data volume matters: a Seq Scan on 40 rows is correct; don't optimize it.
Changelog
- v1.0.02026-07-03Initial clean-room write.
Frequently asked questions
Is SQL Query Optimizer free?
Yes. SQL Query Optimizer is free to download and MIT-licensed.
Where do I install SQL Query Optimizer?
Place the SKILL.md file in ~/.claude/skills/sql-query-optimizer/ and Claude Code auto-discovers it on next launch.
How many tokens does SQL Query Optimizer use?
About 587 tokens — it is designed to be token-lean.
We're developers and SaaS builders who use these tools daily in production. Every review comes from hands-on experience building real products — DealPropFirm, ThePlanetIndicator, PropFirmsCodes, and many more. We don't just review tools — we build and ship with them every day.
Written and tested by developers who build with these tools daily.