Recommends, orders, and prunes indexes for a specific query or table - composite column order, selectivity rules, partial and covering indexes, duplicate/unused cleanup, and write-amplification tradeoffs. Use when a query is slow and EXPLAIN shows a Seq Scan or a sort, before adding a CREATE INDEX, or when auditing a table's index set for bloat or duplicates. Do NOT use to diagnose an unknown slow query from scratch - start with sql-query-optimizer; do NOT use when the query shape itself is the problem (function-wrapped predicates, leading wildcards, correlated subqueries) - use query-rewriter instead; do NOT use when the table is too large and needs partitioning or a time-series strategy - use partition-planner instead.
Click to play with sound.
---
name: Index Advisor
description: Recommends, orders, and prunes indexes for a specific query or table - composite column order, selectivity rules, partial and covering indexes, duplicate/unused cleanup, and write-amplification tradeoffs. Use when a query is slow and EXPLAIN shows a Seq Scan or a sort, before adding a CREATE INDEX, or when auditing a table's index set for bloat or duplicates. Do NOT use to diagnose an unknown slow query from scratch - start with sql-query-optimizer; do NOT use when the query shape itself is the problem (function-wrapped predicates, leading wildcards, correlated subqueries) - use query-rewriter instead; do NOT use when the table is too large and needs partitioning or a time-series strategy - use partition-planner instead.
---
# Index Advisor
An index trades write speed and disk for read speed. Add the fewest indexes that serve real query shapes, order their columns deliberately, and prove every change with a plan. The costly mistake is the speculative index: it fails to help the read (wrong column order, predicate too unselective) while permanently taxing every write. This skill owns index design only - plan diagnosis belongs to sql-query-optimizer, and query restructuring belongs to query-rewriter.
## Inputs to collect
1. The slow query text and its `EXPLAIN (ANALYZE, BUFFERS)` output (Postgres) or `EXPLAIN ANALYZE` / `EXPLAIN FORMAT=JSON` (MySQL). No plan, no recommendation.
2. The table's row count and the existing index list (`\d table` / `SHOW INDEX`).
3. Predicate selectivity: roughly what fraction of rows each WHERE predicate matches. Estimate from `n_distinct` in `pg_stats` or a quick `COUNT(*)` ratio; label estimates as guesses.
4. The write profile: is this table write-hot (high INSERT/UPDATE rate), and which columns get updated?
5. For an audit: `pg_stat_user_indexes` (or the MySQL equivalent) over a representative window.
## Workflow
1. **Capture evidence first.** Get the actual access path from the plan. Note the Seq Scan, expensive Sort, or rows-removed-by-filter that an index would eliminate. Never recommend an index without a plan that shows the problem.
2. **Check selectivity before designing anything.** A B-tree index pays off when the predicate selects roughly under 5-10% of rows; once a query fetches more than ~10-20% of a table, the planner rightly prefers a Seq Scan and the index will sit unused. A column with very low cardinality (boolean, status, enum with a handful of values) is not worth a plain index - that is partial-index territory (step 4) or no index at all.
3. **Order composite columns: equality first, then one range/sort.** Extract the query shape - equality predicates (`=`, `IN`), the one range/sort column (`<`, `>`, `BETWEEN`, `ORDER BY`), and the returned columns. Equality columns lead, then a single range or sort column. `(tenant_id, created_at)` serves `WHERE tenant_id = ? ORDER BY created_at`; reversing it does not. By the leftmost-prefix rule, `(a, b, c)` already serves `(a)` and `(a, b)` - so do not also create `(a)`; it is redundant for lookups. Among equality columns, order for prefix reuse across the workload's query shapes.
4. **Make it partial when one predicate is always present.** If queries always filter the same constant, scope the index: `CREATE INDEX ON orders (created_at) WHERE status = 'pending'` is tiny and hot. Prefer a partial index over a plain index on a low-cardinality column.
5. **Make it covering when the heap fetch dominates.** To get an index-only scan: in Postgres add `INCLUDE (amount, status)`; in MySQL InnoDB the PK is already appended, so add the covered columns to the index key. Confirm with the plan showing `Index Only Scan` (Postgres) or `Using index` (MySQL).
6. **Prune dead weight.** Find unused indexes via `pg_stat_user_indexes` where `idx_scan = 0` over a representative window (long enough to cover monthly jobs). Find duplicates by identical or prefix-redundant column lists and drop the subset. Apply with `CREATE INDEX CONCURRENTLY` / `DROP INDEX CONCURRENTLY` (Postgres) to avoid table locks.