Skip to content

Instantly share code, notes, and snippets.

@camallen

camallen/pg_find_unused_indexes.sql

Created December 2, 2016 13:44
Show Gist options
  • Save camallen/4d46cc0853efefbf55fb7ed0a4a4afa3 to your computer and use it in GitHub Desktop.
Save camallen/4d46cc0853efefbf55fb7ed0a4a4afa3 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