Following query finds out the unused indexes for top 20 most used tables in Postgresql:
SELECT
i.relname,i.indexrelname,i.idx_scan,i.idx_tup_read,i.idx_tup_fetch, c.reltuples, c.relpages
FROM
pg_stat_user_indexes as i
INNER JOIN pg_class as c
ON i.indexrelname = c.relname
WHERE
i.relname IN
(
--top 20 most used tables
SELECT c.relname as name
FROM pg_stat_user_tables as t
INNER JOIN pg_class as c
ON t.relid = c.oid
WHERE c.relkind ='r'
ORDER BY
COALESCE(t.idx_tup_fetch,0)+COALESCE(t.seq_tup_read,0) DESC
LIMIT 20
)
ORDER BY i.idx_scan ASC
SELECT
i.relname,i.indexrelname,i.idx_scan,i.idx_tup_read,i.idx_tup_fetch, c.reltuples, c.relpages
FROM
pg_stat_user_indexes as i
INNER JOIN pg_class as c
ON i.indexrelname = c.relname
WHERE
i.relname IN
(
--top 20 most used tables
SELECT c.relname as name
FROM pg_stat_user_tables as t
INNER JOIN pg_class as c
ON t.relid = c.oid
WHERE c.relkind ='r'
ORDER BY
COALESCE(t.idx_tup_fetch,0)+COALESCE(t.seq_tup_read,0) DESC
LIMIT 20
)
ORDER BY i.idx_scan ASC