Thursday, December 9, 2010

Query to find unused indexes in Postgresql

2 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

2 Responses so far

  1. 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/

  2. Excellent and useful information, thanks for the list. WhatsApp++ Pokemon Go++ Instagram++

Leave a Reply

Labels