Find unused indexes
pg_unused_indexesIdentify unused or rarely used indexes that incur write overhead and disk cost, using scan counts and reset age to avoid false conclusions.
Instructions
Indexes that have never been scanned or have very low usage, largest first. Each unused index costs write amplification (every INSERT/UPDATE maintains it) and disk space, so before adding a new index, check whether the fix is to drop a dead one. Returns {rows, stats_reset, stats_reset_age_seconds}.
READ THIS BEFORE RECOMMENDING A DROP: scans is a counter, not a verdict. It only counts since the last statistics reset, which is why the top-level stats_reset and stats_reset_age_seconds are part of the answer. If the counters were reset an hour ago, EVERY index looks unused; if stats_reset is null, the start of the window is unknown and the counts prove nothing. This list is only trustworthy once the reset age comfortably exceeds the slowest cycle that could use the index - a monthly report, a quarterly close, a yearly job, a failover-only query path.
PRIMARY KEY and UNIQUE indexes are already excluded from these results: they enforce a constraint and stay load-bearing at zero scans, so they never appear here and their absence is not evidence of anything.
On PostgreSQL 16+ each row also carries last_idx_scan, the timestamp of the most recent scan (null = never scanned since the reset). 'Not scanned since 2026-02-14' is a far better basis for a decision than a bare count.
On PostgreSQL 18+, do not fall back on the old 'the leading column is never filtered, so this index is dead weight' reasoning. Skip scan lets the planner use a multi-column btree whose leading column is unconstrained, so such an index can now be doing real work.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| limit | No | Max rows to return (default 50). | |
| schema | No | Limit to one schema. If omitted, all user schemas are included. | |
| maxScans | No | Include indexes with scan count <= this (default 10). Use 0 for 'never scanned'. |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| rows | Yes | ||
| _warnings | No | ||
| stats_reset | Yes | Every counter in `rows` is cumulative SINCE this point. Null = start of the window unknown. | |
| stats_reset_age_seconds | Yes | Seconds since `stats_reset`; null whenever that is null. |