When to use this
- A specific query or endpoint is confirmed slow and needs diagnosis.
- Reviewing new query code for an N+1 pattern before it ships.
- Not for blind index-adding without an execution plan confirming it would help -- unnecessary indexes cost write performance and storage.
The skill file
Copy this verbatim. It's written in the SKILL.md format (YAML frontmatter + markdown instructions) that Claude Code, and increasingly other agent tools, read directly.
--- name: query-optimizer description: Diagnose a slow database query using its actual execution plan (EXPLAIN/EXPLAIN ANALYZE) to find missing indexes, bad join order, or N+1 query patterns, rather than guessing from the SQL text. Use for a confirmed-slow query or when reviewing new query code. --- # Query Optimizer ## Get the actual execution plan Run `EXPLAIN ANALYZE` (or the equivalent for this database) on the real query with realistic data volume. Guessing at what's slow from reading SQL text alone misses what the query planner is actually doing. ## What to look for in the plan - **Sequential/full table scan** where an index lookup should be possible -- check whether an index exists on the filtered/joined column, and whether the query is written in a way that lets the planner use it (e.g. no function wrapping the indexed column in the WHERE clause). - **Join order and type**: is the planner joining a huge table first when filtering a smaller one first would reduce the working set sooner? - **Row estimate vs. actual**: a large mismatch between estimated and actual rows often means stale statistics or a query shape the planner can't estimate well. - **N+1 pattern**: in application code, check whether a loop issues one query per iteration instead of a single batched query/join -- this won't show up in a single query's EXPLAIN output, it shows up as repeated near-identical queries in a query log. ## Before recommending a new index Confirm via the execution plan that the index would actually be used (right column order for a composite index, not blocked by a function wrapping the column) and weigh the write-path cost -- a table with heavy writes and marginal read benefit may not be worth it. ## Output The specific bottleneck from the plan, the fix (index, query rewrite, batching), and for a new index, confirmation it would actually be used plus the write-cost trade-off.
Installing it elsewhere
The frontmatter/body split above is Claude Code's convention. Here's how to carry the same instructions into other tools:
Save the file below verbatim (frontmatter included) at that path, project-local or in ~/.claude/skills/ for a user-level skill. Claude Code loads the name/description pair to decide when to pull it in, or you invoke it directly as /query-optimizer.
Convert the YAML frontmatter to Cursor's rule format (description, globs, alwaysApply: false) and keep the markdown body as the rule content. Cursor surfaces it by description match, same idea as Claude Code's auto-load.
Codex CLI (and increasingly other agentic CLIs) read AGENTS.md at the repo root as always-on instructions. Paste the markdown body under a heading like ## {title}; for GitHub Copilot's coding agent, the equivalent file is .github/copilot-instructions.md.
Append the markdown body to .windsurfrules at the repo root. Windsurf treats the whole file as always-on context, so keep only the instructions you want applied on every request.
- Adding an index without confirming via EXPLAIN that the query would actually use it.
- Missing an N+1 pattern because it only shows up as repeated queries in application logs, not in a single query's plan.
- Recommending a query rewrite based on the SQL text alone without checking the actual plan on realistic data volume.