Finding unused indexes

In this recipe, we will be discussing how to find the unused indexes from their creation time, which is utilizing the unnecessary I/O.

Getting ready

In a database, unused indexes will cause an unnecessary I/O for each write operation to be written into a table. To find these unused indexes, we have to depend on the number of scans that an index has performed as of that moment. From the time of index creation, if the index scan count is zero and the index is not a primary key index, then we can treat that as an unused index.

Note

To get the number of index scans of a table, we have to depend on PostgreSQL statistical counters. However, these counters can be reset to zero using pg_stat_reset(). It would be wise to check when was the last time the stats were reset, using the stats_reset column from the pg_stat_database view.

How to do it...

Let's run the following query to get the list of unused indexes from the database:

        benchmarksql=# SELECT 
        indrelid...