O'Reilly logo

PostgreSQL High Performance Cookbook by Dinesh Kumar, Chitij Chauhan

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required