Non-required index statistics for PostgreSQL

I wanted to get an idea of index usage and non-usage on a database for a client, so I spent some time writing up a query to do so after some searching of the Internets. This is inspired by Josh Berkus’ earlier post on nearly the same topic, and the basis of the query definitely comes from that page. I recommend taking a look at his post for more detail on this subject.

I’ve reworked it slightly:

• Use pg_index directly so we are joining on relation IDs rather than depending on string names
• Shows all non-unique indexes in your database ordered by number of times used
• Includes the total relation size which will include other indexes and any toasted data
• Excludes any empty tables (not uncommon in ORM situations or where a feature you implemented sits unused)

SELECT idstat.schemaname AS schema_name,
    idstat.relname AS table_name,
    idstat.indexrelname AS index_name,
    idstat.idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size,
    pg_size_pretty(pg_relation_size(idstat.indexrelid)) AS index_size,
    pg_size_pretty(pg_total_relation_size(idstat.relid)) AS total_size,
    n_tup_upd + n_tup_ins + n_tup_del as num_writes,
    pg_get_indexdef(idstat.indexrelid) AS definition
FROM pg_stat_user_indexes AS idstat
JOIN pg_index AS indexes
    ON idstat.indexrelid = indexes.indexrelid
JOIN pg_stat_user_tables AS tabstat
    ON idstat.relid = tabstat.relid
WHERE indexes.indisunique = false
    AND pg_relation_size(idstat.relid) > 0
ORDER BY times_used, idstat.relname, indexrelname;

See Also

• Concise summary query for pg_stat_statements - February 4, 2013
• Useful PostgreSQL schema diff tool - January 9, 2011
• PostgreSQL thoughts and experiences - May 11, 2009