Server Index Usage View — PostgreSQL

The Index Usage view shows how often each index has been used since statistics were last reset, so you can spot indexes wasting space and write overhead. It also surfaces tables read by sequential scan, where an index may be missing.

The statistics are reset after a crash or other unclean shutdown, or when someone resets them manually.

Summary Bar #

Metric	What it means
Unused Indexes — In Active Tables	The number of unused indexes on tables that have been queried. Required indexes aren't counted. Since the indexes are unused, it's wasted effort to update the indexes on every `INSERT`, `UPDATE`, and `DELETE`.
Unused Indexes — In Inactive Tables	The number of unused indexes on tables with no scan activity at all. Required indexes aren't counted. The tables haven't really been used yet, so first run a normal workload and check again.
Tables w/ Seq Scans	The number of tables read by sequential scan. Scanning every row is slow, so you may want more indexes.

View Modes #

At the top of the view is a mode selector. Index Usage lists each index, letting you see whether it is Used/Unused and some statistics. The Tables with Seq Scans mode lists tables, letting you see how many sequential scans occurred and how heavily scans have been used to return lots of rows.

Index Usage Mode

Column	What it shows
Status	Used: The index has had at least one scan. Cold: The index has had no scans, but neither has its table, so it can't be judged yet. Unused: The index has had no scans, but its table has been queried. Required: The index enforces or supports a rule, such as a primary key, so it can't simply be dropped. An index shows this status no matter how many scans it has. A PK, Unique, or Exclusion badge beside the index name says which rule it enforces.
Schema, Table, Index	Identifying the specific index location and name.
Scans	The number of index scans since the last reset.
Size	The amount of disk space the index uses.
Table Writes	The number of `INSERT`, `UPDATE`, and `DELETE` operations on the table. Every write updates all of its indexes, so high values amplify the cost of unused ones.
Last Scan	When the index was last used, or "Never". (PostgreSQL 16 and later only.)

Tables with Seq Scans Mode

Column	What it shows
Schema, Table	The table.
Seq Scans	The number of sequential scans performed.
Tuples Read	The number of rows read across all sequential scans. Large numbers mean a lot of data pulled (perhaps unnecessarily).
Table Writes	The number of writes, which is shown for context on how active the table is.

Filters and Options #

The Show menu at the top right of the view narrows which indexes are listed. Its title always describes what it is currently showing, such as "All Indexes".

Unused Only shows only indexes with no scans. This does not display Required indexes, so what remains are candidates to be removed.
Hide Required hides Required indexes.
Active Tables Only hides Cold indexes, so you see only confirmed usage.

The Filter field narrows the list by schema, table, or index name.

Before Dropping an Index #

Before deciding to drop an index, consider each of these points:

Statistics age. After the statistics are reset, even heavily used indexes show zero scans. Let real workload accumulate first.
Seasonality. An index used only by monthly or annual jobs looks unused most of the time.
Required indexes. An index with the Required status enforces or supports a rule, such as a primary key or a UNIQUE constraint. Dropping it changes how the table behaves, so don't drop it unless you mean to change that rule.
Last Scan (PostgreSQL 16+) confirms whether an index has been used in the current statistics period.

To drop an index, run DROP INDEX index_name; in the Query view.