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