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
Nice Article !
Really this will help to people of PostgreSQL Community.
I have also prepared small demonstration on, to find a missing indexes in PostgreSQL.
You can visit my article using below link.
http://www.dbrnd.com/2015/10/postgresql-script-to-find-a-missing-indexes-of-the-schema/
Excellent and useful information, thanks for the list. WhatsApp++ Pokemon Go++ Instagram++