Find Unused PostgreSQL Indexes with pg_stat_all_indexes

calendar Apr 10, 2024 / · 7 min read · postgresql performance indexing index maintenance pg_stat_all_indexes pg_index pg_stat_database  ·
Share on: linkedin copy

Find Unused PostgreSQL Indexes Before They Cost You

Which indexes have gone unused since the statistics were last reset? pg_stat_all_indexes counts every index scan per index, and an index whose idx_scan is still zero after weeks of normal traffic is a candidate to drop. The query below lists those indexes, largest first, and leaves out the ones that enforce a unique or primary key constraint.

Purpose and Overview

Every index has a cost. It takes disk space, PostgreSQL has to maintain it as rows are inserted and updated, and its pages compete for shared buffers with the data your queries actually read. An index that no query uses pays all of those costs and returns nothing.

Unused indexes build up for ordinary reasons: a query was rewritten, a feature was removed, an ORM created an index nobody asked for, or two people added overlapping indexes for the same problem. Nothing in PostgreSQL removes them on its own, so they stay until someone looks.

The cumulative statistics system makes the check cheap. pg_stat_all_indexes has one row per index in the current database, and its idx_scan column is the "number of index scans initiated on this index." The query returns each index with zero scans, the table it belongs to, its schema, and its size on disk, so the biggest savings come first.

Sample Code

 1SELECT
 2    s.schemaname,
 3    s.relname                                      AS table_name,
 4    s.indexrelname                                 AS index_name,
 5    s.idx_scan,
 6    pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
 7FROM pg_stat_all_indexes s
 8JOIN pg_index i ON i.indexrelid = s.indexrelid
 9WHERE s.schemaname NOT IN ('pg_catalog', 'information_schema')
10  AND s.schemaname NOT LIKE 'pg_toast%'
11  AND s.idx_scan = 0
12  AND NOT i.indisunique
13  AND NOT i.indisprimary
14  AND NOT i.indisexclusion
15ORDER BY pg_relation_size(s.indexrelid) DESC;

Check how long the counters have been collecting before you trust the result:

1SELECT datname, stats_reset
2FROM pg_stat_database
3WHERE datname = current_database();

Notes: Runs on any supported PostgreSQL version. Run it in each database you want to check, since pg_stat_all_indexes covers the current database only. A zero means "not used since the counters were last reset," not "never used."

Code Breakdown

The main query joins the index statistics to the index catalog, filters to user indexes with no scans, and sorts by size.

The statistics view

pg_stat_all_indexes supplies the schema, table and index names and the idx_scan counter. It includes indexes on system tables too, which is why the WHERE clause removes pg_catalog, information_schema and the pg_toast schemas. The narrower pg_stat_user_indexes view does the same filtering for you — it is "the same as pg_stat_all_indexes, except that only indexes on user tables are shown" — and either works here.

The join to pg_index

The pg_index catalog holds the properties of each index. Three of its flags matter: indisunique ("this is a unique index"), indisprimary ("this index represents the primary key of the table"), and indisexclusion ("this index supports an exclusion constraint"). Those indexes enforce rules on your data whether or not a query ever scans them, so an idx_scan of zero says nothing about whether they are needed. Filtering them out keeps the list to indexes that exist only for speed.

Sizing by OID, not by name

pg_relation_size(s.indexrelid) reads the index size by its OID, which works for an index in any schema. Passing the index name as text and casting it to regclass fails for an index outside the current search path, so the OID is the safer input. pg_relation_size "computes the disk space used by one fork of the specified relation," and with one argument that is the main data fork. pg_size_pretty turns the byte count into a readable figure.

The reset check

pg_stat_database.stats_reset is the "time at which these statistics were last reset." If that was yesterday, a zero in idx_scan means almost nothing. If it was three months ago and the database has been busy, a zero is a strong signal.

Key Index Statistics

idx_scan

The "number of index scans initiated on this index." It is the most direct measure of whether queries use the index, and it is the column the query filters on.

idx_tup_read and idx_tup_fetch

idx_tup_read is the "number of index entries returned by scans on this index," and idx_tup_fetch is the "number of live table rows fetched by simple index scans using this index." The documentation notes that a bitmap scan increments idx_tup_read for each index it uses but counts table row fetches against the table, not the index, so idx_tup_fetch can stay at zero for an index that bitmap scans use heavily. That is why the query relies on idx_scan and not on the tuple counters.

last_idx_scan

From PostgreSQL 16, pg_stat_all_indexes also has last_idx_scan, "the time of the last scan on this index." It answers a better question than a count: not whether the index has ever been used, but when it was last used. An index last scanned eight months ago is a stronger drop candidate than one with a low count and a scan this morning.

When counters reset

Counters survive a clean restart: when a server, including a physical replica, shuts down cleanly, PostgreSQL saves the statistics to the pg_stat directory. After an unclean shutdown — an immediate shutdown, a crash, a start from a base backup, or point-in-time recovery — all statistics counters are reset. Calling pg_stat_reset() also clears them.

Practical Applications

The query is a starting list, not a drop list. Each result needs a short review before anything is removed.

Quarterly index review

Run the query once a quarter on each production database, after confirming stats_reset is old enough to cover the full business cycle, including month-end and year-end jobs. Indexes used only by a monthly report will look unused for most of a month.

Before a dump-and-restore migration

Dropping unused indexes before a pg_dump and restore shortens the restore, because every index in the dump is rebuilt after the data loads. The size column shows which indexes are worth the review.

Checking read replicas

Each server keeps its own statistics. If your application sends reads to replicas, an index that shows zero scans on the primary may be busy on a replica. Run the same query on every replica before dropping anything.

Dropping safely

Drop a confirmed index with DROP INDEX CONCURRENTLY, which drops it "without locking out concurrent selects, inserts, updates, and deletes on the index's table." It cannot run inside a transaction block, does not support CASCADE, and cannot be used on indexes of partitioned tables. Save the index definition from pg_indexes.indexdef first, so you can recreate it if a slow query appears.

Version Compatibility

The main query uses only pg_stat_all_indexes, pg_index and pg_relation_size, which have been in PostgreSQL for many major versions, so it runs unchanged on every currently supported release.

last_idx_scan is new in PostgreSQL 16; the PostgreSQL 15 documentation for the cumulative statistics views does not list it. On 16 and later, add it to the select list and sort by it to see how long each index has been idle. On older versions, the only way to measure time since last use is to snapshot idx_scan on a schedule and compare the snapshots.

Best Practices

  • Check stats_reset first — a short collection window produces false "unused" results.
  • Never drop unique, primary key or exclusion-constraint indexes on scan counts alone — they enforce data rules, not just speed.
  • Check every replica — statistics are kept per server.
  • Save the definition before dropping — keep pg_indexes.indexdef so the index can be rebuilt.
  • Use DROP INDEX CONCURRENTLY on busy tables — it avoids blocking reads and writes during the drop.
  • Recheck after big releases — new code paths can start using an index the old code ignored.

References

Posts in this series