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:
PSUI.indexrelid::regclass AS IndexName
,PSUI.relid::regclass AS TableName
FROM pg_stat_user_indexes AS PSUI
JOIN pg_index AS PI
ON PSUI.IndexRelid = PI.IndexRelid
WHERE PSUI.idx_scan = 0
AND PI.indisunique IS FALSE;
Script to find duplicate indexes in PostgreSQL:
indrelid::regclass AS TableName
,array_agg(indexrelid::regclass) AS Indexes
HAVING COUNT(*) > 1;
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 dbrnd.com, 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.