Diagnose + Add Database Indexes
Diagnose slow database queries and add the right indexes — driven by the query plan, not by hunches — then prove the speedup without harming writes. Analyzes FIRST with EXPLAIN/query plans to find the actual full scans and missing indexes, then adds the targeted indexes (right columns, right order, covering where it helps), then a reviewer confirms the plans now use the index and queries are faster. Covers EXPLAIN/query plans, full-table-scan detection, composite index column order, covering indexes, selectivity, and the write-amplification tradeoff.
Steps
Entry step: analyze. Each step names the specialist role it wants; the full working prompt is expandable.
- Analyze the queriesdata-analystentry
use query plans to find scans and missing indexes
Show working prompt
Diagnose with data, not hunches — index by the query plan. Step 1: Identify the slow queries and run EXPLAIN/EXPLAIN ANALYZE on each to capture the actual plan, noting full table scans, expensive sorts, and the rows examined vs returned. Step 2: For each problem query, determine the columns in WHERE/JOIN/ORDER BY that drive the scan and would benefit from an index, and the right COLUMN ORDER for a composite (most selective / equality before range). Step 3: Consider covering indexes where a query reads only a few columns. Step 4: Note the WRITE cost tradeoff so you do not over-index. Step 5: Write a numbered acceptance-criteria checklist of 5-9 items (e.g. 'each target query's plan no longer does a full table scan', 'the added index columns/order match the query predicates', 'no redundant/duplicate index is added', 'write-heavy tables are not over-indexed'). `write_task_note` the plans + proposed indexes + checklist and write the same to the produces path.
- Add the indexesdeveloper
write the index migration to the analysis
Show working prompt
Write the index changes to the analysis. Step 1: Author the CREATE INDEX statements (as a migration) with the columns and ORDER the analysis specified — composite order matters. Step 2: Add covering/included columns only where the analysis justified them; avoid duplicating an existing index's prefix. Step 3: Use concurrent/online index creation if the database supports it to avoid locking, and include the down/rollback (DROP INDEX). Step 4: Keep the change minimal — only the indexes the plans demanded, not a scattershot of indexes that bloat writes. Write the migration to the produces path. On a loop-back, adjust only the index the reviewer flagged. `write_task_note` the migration path and which criteria now pass.
- Verify the plansreviewer
confirm plans use the index and queries are faster
Show working prompt
Verify the indexes actually help. Step 1: Re-run EXPLAIN on each target query and confirm the plan now USES the new index (index scan/seek) instead of a full table scan. Step 2: Confirm the query timing improved against the baseline. Step 3: Confirm the index column order matches the predicates and no redundant/duplicate index was created. Step 4: Sanity-check that write-heavy tables were not over-indexed (the tradeoff is acknowledged). Write a verification note with before/after plans and timings to the produces path.
- Evaluatereviewer
Grade the deliverable against every acceptance criterion. All pass → finish; any fail → loop back and fix the gap.
Show working prompt
Re-run EXPLAIN on the target queries. For EACH acceptance criterion: confirm each query's plan now uses the index (no full scan), confirm timings improved over baseline, confirm the index columns/order match the predicates, and confirm no redundant index and no over-indexing of write-heavy tables. Write PASS/FAIL per criterion; an index the plan does not use is a FAIL. Then route — this is the whole point of the loop: - **Every criterion PASSES →** call `advance_task_step({ ref, stepId: "evaluate", next: "finish" })`. - **Any criterion FAILS →** write the specific gaps to notes, then call `advance_task_step({ ref, stepId: "evaluate", next: "change" })` to loop back. The builder fixes exactly those gaps. Never route to `finish` while any criterion is unmet. The build phase's completion gate already blocked a grossly-incomplete deliverable; your job is the judgment an automated check cannot make (does it actually work, read well, look right). After ~3 unproductive loops, stop and report DONE_WITH_CONCERNS so the user can step in. - Finishdeveloper
All acceptance criteria met. Stamp a short summary and report DONE.
Show working prompt
Every acceptance criterion passed. Write a one-paragraph DONE summary to task notes via `write_task_note`: what was built, the deliverable path(s), and a one-line confirmation that each criterion is met. Then report DONE.
Triggers
Phrases that suggest this craftbook to a crew.
- add database indexes
- speed up slow queries
- tune database performance
- fix a slow query
- explain plan and index
Source
View this craftbook on GitHub · MIT license