How to identify unused indexes in PostgreSQL

Identify unused indexes.

Indexes are not always useful. When adding an index to a table, data reading is accelerated, but there is a side effect: when changing data in this table, the index will be updated at the same time, i.e. all insertions, changes, or deletions of rows will be slower than they were before the index was added. In fact, you have to sacrifice performance in one place for acceleration in another.

It happens that indexes were added for some reason, but they are not used now and will not be used in the future. It is useful to remove such indexes to free up space on a hard drive and speed up data changing. The following query will help with this:

This query displays a list of all indexes sorted by scan frequency (column №3). If some indexes have a scan frequency (index_scans_count) of zero, then you should consider deleting them. But wait…

When considering candidates for deletion, you must check:

• Maybe the table is not used or planner avoids the index for some other reason?
• How much space does the index take? Maybe he is not worth the attention at the moment?
• What is the number of sequential scans of the table? You may need to add additional fields to the index to prevent sequential scans.

Reset statistics.

It happens, that a index was once used, but there is no certainty that the index is still used. Then you need to recollect the statistics. Clear statistics in the database:

Now, if you run the query again to get statistics on the use of indexes, the table names will remain there, but all data about scans will be reset. You can continue to work — the database will again collect statistics and after some time it will be possible to identify unused indexes again.