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, 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:

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:

SELECT
    indrelid::regclass AS TableName
    ,array_agg(indexrelid::regclass) AS Indexes
FROM pg_index 
GROUP BY 
    indrelid
    ,indkey 
HAVING COUNT(*) > 1;