Skip to content

Instantly share code, notes, and snippets.

@dmitry-naumenko
Created January 10, 2020 10:01
Show Gist options
  • Save dmitry-naumenko/cef3456d6775ca0f377375af38c9e66f to your computer and use it in GitHub Desktop.
Save dmitry-naumenko/cef3456d6775ca0f377375af38c9e66f to your computer and use it in GitHub Desktop.
Identify unused indexes.
SELECT
idstat.relname AS TABLE_NAME,
indexrelname AS index_name,
idstat.idx_scan AS index_scans_count,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
tabstat.idx_scan AS table_reads_index_count,
tabstat.seq_scan AS table_reads_seq_count,
tabstat.seq_scan + tabstat.idx_scan AS table_reads_count,
n_tup_upd + n_tup_ins + n_tup_del AS table_writes_count,
pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size
FROM
pg_stat_user_indexes AS idstat
JOIN
pg_indexes
ON
indexrelname = indexname
AND
idstat.schemaname = pg_indexes.schemaname
JOIN
pg_stat_user_tables AS tabstat
ON
idstat.relid = tabstat.relid
WHERE
indexdef !~* 'unique'
ORDER BY
idstat.idx_scan DESC,
pg_relation_size(indexrelid) DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment