Slow Query Optimizer: Plan-Proven SQL Fixes

What it does

Speeds up slow SQL queries and ORM code with evidence. It runs the real query plan, finds the actual cause (N+1 queries, a missing or unusable index, non-sargable filters, OFFSET pagination), fixes it, and re-runs the same measurement. It also checks that the rewritten query returns exactly the same rows.

Why it exists

Adding an index because a query "looks slow" is a guess, and guessed indexes slow down every write for nothing. Many slow endpoints aren't missing an index at all: they run one query per row, or wrap the indexed column in a function. The plan tells you which.

How it works

1. Scopes to the slow query or endpoint you name, and agrees on the target time and data size.
2. Measures with EXPLAIN ANALYZE (Postgres, MySQL 8) or EXPLAIN QUERY PLAN plus timing (SQLite), and counts queries per request to catch N+1.
3. Reads the plan with a bundled guide: the slowest node, estimate vs. actual rows, full scans, spilled sorts, large OFFSETs.
4. Fixes in leverage order: query shape first, then a matching index, then statistics, then configuration. A bundled catalog covers ORM N+1 fixes (Django, SQLAlchemy, Rails, Prisma), non-sargable predicates, index design and keyset pagination.
5. Verifies with the same measurement, checks results are identical, and reports index write cost and a lock-free rollout.

What you get

Before/after time on the agreed data, the plan evidence for the cause, the SQL or ORM change and index DDL, how results were confirmed identical, and the index's write cost and rollout plan.

When not to use it

Database server tuning without a specific slow query, schema design from scratch (use a database-design skill), or NoSQL stores.

See it in action

Request:

Use the slow-query-optimizer skill on dashboard.py. The top-customers dashboard endpoint is too slow against our database (shop.db, about 50,000 orders and growing). Make it fast.

Result: 4.99 s → 4.3 ms on 50,000 orders, with identical results for every window and list size tested. Removing the N+1 alone took it to 17 ms; the covering index did the rest. The complete, unedited output, the inputs, and the before/after files are in examples/slow-query-optimizer.

Known limitations

• Needs to run queries (or have you run them) for real measurements; without that it gives commands, not results.
• Timing on a laptop or staging copy can differ from production; use production-sized data where possible.
• SQLite has no EXPLAIN ANALYZE, so plans there show the access path but not per-node time.
• Index size estimates may be approximate when the database doesn't expose size statistics.

FAQ

Will it add indexes everywhere? No. It fixes the query shape first and adds only an index that serves the specific slow query, and it reports the write cost of that index.

How do I know the faster query is still correct? It compares old and new results with an order-independent check (EXCEPT both ways or sorted comparison). In the demo, results matched for every window and list size tested.

Does it work with my ORM? Yes. It counts queries per request to find N+1 patterns and uses the bundled fixes for Django, SQLAlchemy, Rails and Prisma, or plain SQL.

Compatibility

Works with any agent that supports the SKILL.md standard. The demo was run with Claude Code (claude-sonnet-5-5) on SQLite. Best with shell access to run the plan and timing; without it, it gives you the exact commands to run and works from the output you paste.

Open source

Part of claude-rigor-skills: 33 MIT-licensed skills that make AI show its work. Documentation: https://github.com/hemapriyan-rk/claude-rigor-skills/tree/main/skills/slow-query-optimizer