PostgreSQL: Script to find the unused and duplicate index

As I am preparing important scripts for PostgreSQL DBA so here I am also sharing one more important script to find the unused and duplicate index in PostgreSQL.

The management and maintenance of database index is a day to day exercise for a Database Administrator. Wrong or unused index can create performance issues for a frequently used table.

At every insert and update, the data of an index are also changing and it requires some IO operations. Better to find unused index and delete to it.

Sometimes, I have also found that duplicate indexes on the same table, e.g. same table, same columns, same order of columns and created with a different name. Internally this will also impact to our database performance.

I am sharing two script different scripts to find the unused and duplicate index in PostgreSQL.

Script to find unused indexes in PostgreSQL:

Script to find duplicate indexes in PostgreSQL:

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

2 Comments on "PostgreSQL: Script to find the unused and duplicate index"

Notify of

Sort by:   newest | oldest | most voted
2 months 5 days ago

Again, thanks, the application my team developed has some duplicated indexes, also idle conections and commit´s that also become idle and do nothing.