Index Advisor — Find Missing and Unused Indexes

Two halves: indexes that should exist but don't, and indexes that exist but shouldn't. The first half is where most of the wins are.

Missing index proposals

The advisor scans captured query plans for Seq Scans on tables over 10k rows where the WHERE clause includes indexable columns. It proposes a B-tree by default, GIN for arrays or tsvector, GIST for geometry. Each proposal carries the CREATE INDEX DDL, the estimated size, the list of query fingerprints that would benefit, and (with hypopg) a simulated cost reduction.

Unused indexes

Indexes where pg_stat_user_indexes.idx_scan has been zero or very low over 30 days. Each one wastes disk and slows writes. The list shows the index name and size, last scan timestamp (null if never scanned), the DROP INDEX statement, and a "matches UNIQUE constraint" flag — when that flag is set, don't drop, you need it for data integrity even though scan count is zero.

Why hypopg matters

Without hypopg, proposals are heuristic: "this column is in WHERE and has no index, suggest one." Often right, sometimes wrong — the planner picks a different path for reasons heuristics can't see.

With hypopg, MonPG runs CREATE HYPOTHETICAL INDEX and re-runs EXPLAIN on your actual query. If the planner picks the hypothetical index, the proposal is validated. If not, it's dropped from the list. The false-positive rate goes essentially to zero.

The recommended workflow

1. Install hypopg (see setup).
2. Let MonPG run for 24+ hours to build a query-plan corpus.
3. Review Missing index proposals: reject the bogus ones, accept the clear wins.
4. Run the proposed CREATE INDEX CONCURRENTLY during low-traffic hours.
5. Re-check 24 hours later — accepted suggestions should disappear from the list as MonPG sees the new index being used.