Get Index statistics in Postgresql database

I always observed that around 30 to 40% indexes created on a database are never used by any queries.

Having extra indexes on a DB, can degrade the performance (update and insert statements) , consumes lot of space and can increase the time of backup and restoring the DB.

It make sense to check the index utilization.

To check the same in Postgresql Database follow below steps.


1: Open postgresql.conf file. This file is inside your data directory of postgres.
2: Change following parameters

A: logging_collector = on
B:track_activities = on
C:track_counts = on
3: save and close the file
4: Restart the postgresql server

Now do all possible operartions using application, just to make sure all possible queries got fired to database.

Now just run the following query



select * from pg_stat_all_indexes where schemaname= order by relname,idx_scan desc;


You will find statistics of all indexes.