In this recipe, we will be discussing how to find the unused indexes from their creation time, which is utilizing the unnecessary I/O.
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.
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 ...