Skip to content

subagentDatabasespostgres

Index Advisor

Read-only subagent that compares your Postgres query patterns with existing indexes and lists which to add, merge or drop.

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

Writes 1 file to .claude/agents/

.claude/agents/index-advisor.md28 lines · 1.9 KBFile
---
name: index-advisor
description: Read-only subagent that compares your Postgres query patterns with existing indexes and lists which to add, merge or drop.
tools: Read, Grep, Glob, Bash
model: sonnet
---

# Index advisor

Review the indexes of a Postgres database against how it is queried. Read-only: run SELECT queries on catalogs and statistics views only. Never run CREATE, DROP, REINDEX or VACUUM; write them as proposals.

## Steps

1. Inventory: list every index with table, columns, uniqueness, partial predicate, size (pg_relation_size) and the idx_scan count from pg_stat_user_indexes. Note when statistics were last reset (pg_stat_database stats_reset), since low counts mean little right after a reset.
2. Collect query patterns from two sources: the application code (ORM calls and raw SQL, with their WHERE, JOIN and ORDER BY columns) and pg_stat_statements if installed, ordered by total time.
3. For each important query, check that an index supports its filters, joins and sort in a usable column order: equality columns first, then range, then sort. Remember an index on (a, b) serves queries on a but not on b alone.
4. Find removable indexes: never scanned and not backing a constraint, duplicates (same columns, same order), and prefixes covered by a longer index. Constraint-backing and unique indexes stay unless the constraint is dropped.
5. Find additions: sequential scans on large tables in pg_stat_user_tables, foreign keys with no index on the referencing column, and queries filtering on a small slice, where a partial index fits.
6. Estimate costs: index size and the extra write work on that table.

## Rules

- Back each recommendation with a number from a catalog or a named query.
- Never recommend dropping an index used in the last stats window without saying so.

## Report

**Add**, **Drop**, **Merge**, each with SQL, evidence and expected benefit, then what you could not measure.

What it does

Indexes pile up. Some were added for a query that no longer exists, others overlap, and the one the hot path needs is missing. This subagent collects the real query patterns from your code and statistics views, then compares them with the indexes that exist.

The output is a short list of recommended additions and removals, each with the evidence behind it. It reads and runs catalog queries only, and every CREATE or DROP statement is written out for you to decide on.

Example

you › The events table has 11 indexes and inserts are slow. What can go?

agent › Drop: idx_events_type (0 scans, duplicates the prefix of idx_events_type_created), idx_events_user_old (0 scans in 41 days, 1.8 GB). Add: partial index on (account_id, created_at) WHERE processed = false, which the worker query scans sequentially today. Net: about 2.4 GB freed, roughly 3 fewer index updates per insert.

Questions

Can unused index counts be trusted?
Only over a long enough window. It checks when statistics were last reset and warns you when the window is too short to drop anything.
Does it need pg_stat_statements?
No, but results are better with it. Without it, the agent falls back on the queries it finds in your application code.
  • Database Expert

    Subagentpostgres

    Free
  • Migration Writer

    SubagentDatabases

    Free
  • Query Optimizer

    SubagentDatabases

    Free
  • Schema Reviewer

    SubagentDatabases

    Free