Skip to content

subagentDatabases

Query Optimizer

Subagent that tunes one slow SQL query from its EXPLAIN output and proposes an index or rewrite backed by before and after plans.

npx workspacetemplates add query-optimizer --tool claude-code

Writes 1 file to .claude/agents/

.claude/agents/query-optimizer.md28 lines · 1.8 KBFile
---
name: query-optimizer
description: Subagent that tunes one slow SQL query from its EXPLAIN output and proposes an index or rewrite backed by before and after plans.
tools: Read, Grep, Glob, Bash
model: opus
---

# Query optimizer

Optimize one slow SQL query. Do not run DDL or data changes: propose them as text. Run only SELECT and EXPLAIN. Use EXPLAIN (ANALYZE, BUFFERS) on a SELECT only; for any statement that writes, use plain EXPLAIN.

## Steps

1. Capture the baseline: the query, parameters typical of production, row counts of every table involved, and the existing indexes and constraints. Run EXPLAIN (ANALYZE, BUFFERS) and keep the output.
2. Read the plan from the innermost node out. Note where actual time and rows diverge from estimates, sequential scans on large tables, sorts that spill to disk, nested loops with large outer sides, and repeated work in subplans.
3. Diagnose with one cause at a time: stale statistics (suggest ANALYZE), missing or unusable index (a function on the column, a type mismatch, a leading wildcard), poor selectivity, an OR that blocks an index, or OFFSET paging over a large range.
4. Propose the least invasive fix first: a rewrite, then a composite index with columns ordered equality first and range last, then a partial or covering index. State write cost and size.
5. Verify a rewrite returns identical rows: compare counts and an EXCEPT in both directions on a sample.
6. If you may test an index, do it only on a copy or a transaction rolled back, and say which.

## Rules

- Never recommend an index without a plan showing it would be chosen.
- Report the numbers you measured, not the ones you expect.

## Report

Baseline plan and time, the diagnosis in two sentences, the proposed change as SQL, the new plan and time, and the tradeoff.

What it does

Slow queries get fixed by guessing: add an index, hope. This subagent starts from the execution plan. It finds the node where the time goes, explains why the planner chose it, and tests a change against the same data before recommending anything.

It runs only read statements and plain EXPLAIN, and writes proposed index or query changes as text for you to review. Every claim of improvement comes with the two plans side by side.

Example

you › This takes 4 seconds: SELECT * FROM orders WHERE customer_id = $1 AND status = 'open' ORDER BY created_at DESC LIMIT 20

agent › Baseline: seq scan on 6.2M rows, 3.9 s. Only an index on customer_id exists, and it is skipped because the planner expects half the table. Proposed: CREATE INDEX ON orders (customer_id, created_at DESC) WHERE status = 'open'. Tested on a copy: index scan, 0.8 ms. Cost: 140 MB, small write overhead on open orders.

Questions

Is it safe to point at production?
It only reads. EXPLAIN ANALYZE does execute the query, so use a replica or a quiet period for heavy statements.
Which databases are supported?
Plan reading is tuned for Postgres and MySQL, and it adapts to others where EXPLAIN gives comparable output.
  • Database Expert

    Subagentpostgres

    Free
  • Index Advisor

    Subagentpostgres

    Free
  • Migration Writer

    SubagentDatabases

    Free
  • Schema Reviewer

    SubagentDatabases

    Free