Skip to content

commandDatabases

Add Index

Proposes an index for one slow query with plan evidence and the write cost, then creates it safely only when you approve.

npx workspacetemplates add add-index --tool claude-code

Writes 1 file to .claude/commands/

.claude/commands/add-index.md15 lines · 1.2 KBFile
---
description: Proposes an index for one slow query with plan evidence and the write cost, then creates it safely only when you approve.
argument-hint: "[slow query or file:line]"
---

Propose an index for this slow query: $ARGUMENTS (SQL, or a file and line).

1. Read the query, the table definition and every existing index on the table. Note row counts if known.
2. Get the plan with `EXPLAIN` (read-only connection, never `EXPLAIN ANALYZE` on a write). If no database is reachable, reason from the schema and say the plan is predicted.
3. Choose the index: equality columns first, then range or sort columns; add INCLUDE or a partial WHERE only when it clearly pays. Check whether an existing index can be extended instead of adding a new one.
4. State the evidence (scan type, rows, cost) and the price: extra disk, write overhead on INSERT and UPDATE, and which other queries it also helps.
5. Write the migration in the project's tool with a non-blocking build (`CREATE INDEX CONCURRENTLY` on Postgres) and a drop in the down step.
6. Do not create anything yet.

Show the proposal and ask: "Add this migration?" After a yes, create the file and give the command to verify the new plan.

What it does

An index speeds one read and slows every write, so adding one by habit is a mistake. This command starts from the slow query, looks at its plan and existing indexes, and proposes the narrowest index that helps.

It states what the index costs in disk and write time, writes the migration with a non-blocking build where the database supports it, and waits for your approval.

Example

you › /add-index SELECT * FROM events WHERE account_id = $1 AND created_at > $2 ORDER BY created_at DESC

agent › Plan: seq scan on events, 6.2M rows, 840 ms. Existing index on account_id alone returns 40k rows to sort.
Proposed: (account_id, created_at DESC). Cost: about 310 MB and a small insert overhead; the old account_id index becomes redundant.
Add this migration?

Questions

Will it add an index without asking?
No. It shows evidence and cost first and creates the migration only after you approve.
What if an existing index could work?
It checks for that first and proposes extending or replacing an index rather than piling on another.
  • Explain Query

    CommandDatabases

    Free
  • Schema Diff Review

    CommandDatabases

    Free
  • Seed Database

    CommandDatabases

    Free
  • Write Migration

    CommandDatabases

    Free