Skip to content

Instantly share code, notes, and snippets.

@zwolf

zwolf/pg_find_unused_indexes.sql

Forked from camallen/pg_find_unused_indexes.sql
Created March 28, 2017 20:36
Show Gist options
  • Save zwolf/fd344b21138e0ef829d2ce063816f5aa to your computer and use it in GitHub Desktop.
Save zwolf/fd344b21138e0ef829d2ce063816f5aa to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL - find unused indexes
Raw
pg_find_unused_indexes.sql
SELECT
relid::regclass AS table,
indexrelid::regclass AS index,
pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
idx_tup_read,
idx_tup_fetch,
idx_scan
FROM
pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE
idx_scan = 0
AND indisunique IS FALSE
ORDER BY index_size DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment