Repository navigation
No index usage collection for PostgreSQL, where an unused index costs more than on SQL Server #2541
Description
Part of #2530.
The gap
Nothing collects pg_stat_user_indexes (or pg_stat_all_indexes). SQL Server users already get get_index_usage; PostgreSQL users get nothing equivalent.
idx_scan, idx_tup_read and idx_tup_fetch are the whole story for "is this index earning its keep", and pg_stat_user_indexes joined to pg_class gives the size to weigh it against.
Why it matters more here than on SQL Server
On SQL Server an unused nonclustered index costs write throughput and space. On PostgreSQL it costs the same plus it participates in every VACUUM — index cleanup is a large share of vacuum work, so dead indexes directly slow the maintenance that #2530's Vacuum surface exists to monitor. An unused index on a hot table is a vacuum problem as much as a write problem.
It is also the cheapest tuning advice a monitoring tool can give: dropping an index nobody scans has no downside to model.
Notes
idx_scanis cumulative since stats reset.stats_resetonpg_stat_database(Collect pg_stat_database: temp-file spills, cache hit ratio, deadlocks and commit ratio in one read #2539) is the way to tell "never scanned" from "reset yesterday" — worth doing after that one so the distinction is available, because reporting a freshly-reset index as unused is exactly the wrong advice.- Index size wants
pg_relation_size, and on a large database that is not free — check whatPgAutovacuumStatsCollectoralready does about relation sizing rather than inventing a second approach. - Partial and expression indexes read as unused when the planner cannot match them; the read should carry enough (
indexdef) that a human can tell "unused" from "unusable".
Activity
erikdarlingdata commentedon Aug 23, 2026
Shipped in #2560. Two things in the framing here changed under measurement.
1. "
stats_resetonpg_stat_database(#2539) is the way to tell 'never scanned' from 'reset yesterday'" — right instinct, wrong plumbing. Doing it as a cross-collector join at read time would have been fragile:pg_database_statsis a separate collector that can be disabled, gated off, or simply not have a sample in the same window, and any of those turns the denominator into a silent absence.stats_resetis instead captured per row by the index collector itself — one scalar subquery per cycle, constant per database, and the row becomes self-contained.That also made room for the better answer, which the issue does not ask for and which is the actual value of collecting this at all: the read reports scans over the STORED WINDOW, not just the lifetime counter.
idx_scanis all a live query can give, and an index with nine million lifetime scans and none in the last ninety days reads as heavily used. Both figures ship — the lifetime one so the read cannot appear to disagree with psql, andscans_in_windowbecause it is the one that decides anything.Two guards on that difference, each proven RED against a live store by reverting only the thing under test:
GREATEST(scans - first, 0)across a 9000 → 50 → 100 reset0-8900— a negative scan count at the top of a least-used-first listIS DISTINCT FROMon a first-ever reset (stats_resetNULL → timestamp)Retention is 90 days for the reason the issue implies but does not state outright: the retention window is the evidence. An index can only be called unused for as long as we have been watching it, and 30 days cannot clear a monthly report. 90 still cannot clear an annual one, which the read says rather than glossing.
2. "Dropping an index nobody scans has no downside to model" is the one claim here I'd push back on, and the pushback is most of the work. It is true only for the case where nothing else is true, and that case is rarer than it sounds. The single most common zero-scan index on any schema is a unique index backing a PRIMARY KEY or UNIQUE constraint — enforcing uniqueness is not a scan of the kind
idx_scancounts, so a constraint index can enforce correctness on every INSERT for years and still report zero. Advice derived from the counter alone tells somebody to drop their primary key.So every row carries the facts that decide it — primary key, constraint-backing, replica identity, partial, expression, valid, ready, plus the full
pg_get_indexdef— and the tool refuses to recommend a drop the data cannot support, including for an index it has not watched for at least two samples (PostgreSQL records no index creation time anywhere, so how long we have been watching is the only evidence an index is old enough to judge). Every one of those states was exercised against the live rig rather than asserted:gadget_sku_status_uqgadget_sku_uqgadget_partial_big_idxgadget_expr_idxgadget_broken_idxYour note about partial and expression indexes reading as unused when the planner cannot match them was exactly right, and
indexdefis carried for precisely that reason.3. On
pg_relation_sizecost — checked, andPgAutovacuumStatsCollectorsettles it. That collector already callspg_total_relation_sizeunconditionally per row, so per-relation sizing is established house practice and this follows it rather than inventing a second approach. The fan-out control is elsewhere: a 64 KB size floor (below which there is nothing to model — write amplification, space and vacuum cleanup all scale with index size), and a daily cadence inherited fromindex_object_stats, the SQL Server collector answering the same question, rather than from the PostgreSQL fan-out sibling. On a 2,001-table / 6,005-index database the floor removed 2,001 indexes. Invalid indexes bypass the floor — verified live, a 16 KB invalid index came through.One measured version floor, and it is the column that matters most for this question:
last_idx_scanis PostgreSQL 16+. Running the gated-on form against live 13/14/15 givesERROR: column i.last_idx_scan does not exist, which fails the whole collection every cycle rather than just that column. It is substituted with a typed NULL so the row shape does not change across a mixed fleet.The collector is writer-only, which is a decision rather than a copy of the autovacuum gate: a replica reports its own scan counts, not the writer's, so an index that is load-bearing on the primary reads as never-scanned there. A missing answer sends someone looking; a wrong one sends them to
DROP INDEX.Merge pull request #2560 from erikdarlingdata/feature/2541-2542-pg-in…
erikdarlingdata commentedon Aug 23, 2026
Shipped in #2560 (merged to
dev):pg_index_usage_statsat schema v84, daily, per-database, writers only, plusget_pg_index_usageand a Storage tab on both UIs.The scan counts were the easy half. The shippable half is that unused is not droppable — every row carries primary-key / constraint / replica-identity / partial / expression / invalid flags plus the full
indexdef, and the tool refuses to recommend a drop the data cannot support, including for an index it has watched for fewer than two samples.It also reports both the lifetime counter and
scans_in_windowderived from stored history — the latter being the figure no live query against the server can produce, and the one that actually answers "is this index earning its keep now".One correction to this issue, mine: I wrote that dropping an index nobody scans "has no downside to model". That is true only when nothing else is true of the index — and the single commonest zero-scan index is a constraint index. The framing had the risk exactly backwards.
Version floors measured on 13/14/15/16/17/18, not assumed: exactly one column needed a gate —
last_idx_scanis PG16+, proven red withcolumn i.last_idx_scan does not exist.Not verified: no live Aurora run (stock PostgreSQL only — these are core catalogs, but that is reasoning rather than measurement), and no end-to-end
DarlingWorkersweep into a store.Document the session-states surface, and correct four stale counts #2560
Collect PostgreSQL index usage and table bloat (erikdarlingdata#2541, e…