Thursday, December 9, 2010

Query to find unused indexes in Postgresql

0 comments
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

Leave a Reply

Labels