Unused and missing indexes

An index is a standing charge

Every index costs three things. Disk, which is the least of them. Memory, because index pages compete with table pages for shared buffers and a rarely used index still evicts something. And write throughput, because every insert and every update that changes an indexed column must maintain every index on the table.

That third cost is the one that surprises people, and it has a specific mechanism behind it. PostgreSQL can often update a row without touching the indexes at all, using a heap-only tuple, but only when no indexed column changed and there is room on the same page. Add an index on a frequently updated column and a large share of updates stop qualifying, so each one now writes index entries too, and creates dead index entries for vacuum to clean up later. The ratio is visible directly:

SELECT relid::regclass AS table_name,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct,
       n_tup_newpage_upd
FROM pg_stat_all_tables
WHERE n_tup_upd > 100000
ORDER BY hot_pct;

A heavily updated table with a low hot_pct is paying for its indexes on every single write. n_tup_newpage_upd, added in PostgreSQL 16, counts the updates that had to move to a new page, which distinguishes “an indexed column changed” from “the page was full” and therefore separates an indexing problem from a fillfactor one.

Indexes nothing reads

The scan counters are the starting point:

SELECT s.relid::regclass                                  AS table_name,
       s.indexrelid::regclass                             AS index_name,
       s.idx_scan,
       s.last_idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid))     AS index_size,
       i.indisvalid
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
  AND NOT i.indisreplident
  AND i.indpred IS NULL
ORDER BY pg_relation_size(s.indexrelid) DESC;

The exclusions are not decoration. A unique index is enforcing a constraint whether or not anyone queries through it. indisreplident marks the index logical replication uses to identify rows, and dropping it breaks replication rather than queries. indpred IS NULL leaves out partial indexes, which are often built for one rare query and can legitimately show a scan count near zero for months.

Then four caveats, each of which has caused someone to drop an index they needed.

The counters are cumulative since the statistics were last reset, so check when that was with stats_reset in pg_stat_database before believing a zero. last_idx_scan, available from PostgreSQL 16, makes this much easier: a null there means never scanned since the reset, and a real timestamp tells you exactly how stale “unused” is. What that column actually records is the moment statistics were reported rather than the moment of the scan, which is worth knowing before treating it as an access log: see the timestamp that dates an unused index.

Statistics are per node and are not replicated. An index that is untouched on the primary may be the only thing keeping the reporting replica alive, and the primary has no way to know. Check every node before dropping anything.

Quarterly and annual jobs exist. An index used once at year end looks identical to an index used never.

And the planner’s choice is not the only use. An index also enforces uniqueness, supports a foreign key check, and can be the reason a constraint validates quickly.

When the evidence looks good and you still want a rehearsal, there is one: open a transaction, DROP INDEX, run the queries you are worried about, and roll back. The index is absent for the duration and returns intact afterwards. It is not free, because the drop takes an ACCESS EXCLUSIVE lock on the table that is held until the rollback, so do it on a clone or in a window where a brief stall is acceptable. Rebuilding a large index on a busy table is expensive enough that being sure is worth the inconvenience.

Indexes another index already covers

A B-tree on (a, b, c) can serve any query that would have used an index on (a) or (a, b). Those narrower indexes are redundant, and they accumulate because each was added by a different person solving a different ticket.

SELECT i.indrelid::regclass                            AS table_name,
       i.indexrelid::regclass                          AS redundant_index,
       j.indexrelid::regclass                          AS covered_by,
       pg_size_pretty(pg_relation_size(i.indexrelid))  AS reclaimable
FROM pg_index i
JOIN pg_index j
  ON j.indrelid    = i.indrelid
 AND j.indexrelid <> i.indexrelid
 AND j.indkey::text LIKE i.indkey::text || ' %'
WHERE NOT i.indisunique
  AND NOT i.indisprimary
  AND i.indpred   IS NULL
  AND i.indexprs  IS NULL
  AND j.indpred   IS NULL
  AND j.indexprs  IS NULL
ORDER BY pg_relation_size(i.indexrelid) DESC;

indkey renders as a space-separated list of column numbers, so the LIKE is asking whether one index’s key list is a strict prefix of another’s. Confirm each candidate by eye before acting: the query does not compare operator classes, collations or INCLUDE columns, and two indexes on the same columns with different sort orders are not interchangeable for a query with an ORDER BY.

While you are in pg_index, look for the wreckage of failed builds:

SELECT indexrelid::regclass AS index_name,
       indrelid::regclass   AS table_name,
       indisvalid, indisready, indislive
FROM pg_index
WHERE NOT indisvalid OR NOT indisready;

CREATE INDEX CONCURRENTLY that fails leaves an invalid index behind. It is never used for queries and it is still maintained on every write, which is the worst of both. Drop it, concurrently, and start the build again.

The scans that wanted an index

PostgreSQL ships no index advisor, and any list of “missing indexes” is an inference. The honest version of that inference starts at the table level:

SELECT relid::regclass AS table_name,
       seq_scan, idx_scan,
       seq_tup_read,
       seq_tup_read / nullif(seq_scan, 0) AS rows_per_seq_scan,
       n_live_tup
FROM pg_stat_all_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

A large rows_per_seq_scan on a large table means whole-table reads are happening routinely, which is the signature of a filter with nothing to use. A small one means the table is tiny and a sequential scan is correct; the planner is not wrong to read forty rows without an index.

That narrows the search. It does not name the column, and nothing in the catalog will. For that you need the statement, which means pg_stat_statements ordered by total time, then EXPLAIN (ANALYZE, BUFFERS) on the worst offenders, looking for a filter that discards most of what it reads. The rows removed by filter line is the specific thing to search a plan for; it is the planner telling you how much work it did for nothing. There is more on reading plans in query plan regression.

Build the candidate with CREATE INDEX CONCURRENTLY and measure the same statement afterwards. An index that does not change the plan is an index you now have to maintain forever.

Indexes that are the right shape and the wrong size

A B-tree does not shrink when rows are deleted. Pages that empty out are reused, but a long run of deletes and updates leaves an index with poor leaf density, and a bloated index costs the same page reads as a useful one:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstatindex('public.orders_created_at_idx');

avg_leaf_density well below its healthy range and a high leaf_fragmentation are the signals. The fix is REINDEX INDEX CONCURRENTLY, which builds a replacement and swaps it without an exclusive lock, and which has been available since PostgreSQL 12. Do it during a quiet period anyway; it is still a full build, and it needs room for both copies.