This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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, and unused index can create a performance issues for the whole database system.
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 drop it.
Sometimes, I 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 different scripts for finding the unused and duplicate index in PostgreSQL.
Script to find the unused indexes in PostgreSQL:
|
1 2 3 4 5 6 7 8 |
SELECT 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 the duplicate indexes in PostgreSQL:
|
1 2 3 4 5 6 7 8 |
SELECT indrelid::regclass AS TableName ,array_agg(indexrelid::regclass) AS Indexes FROM pg_index GROUP BY indrelid ,indkey HAVING COUNT(*) > 1; |
