Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PostgreSQL query that finds indexes without hits
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 pg_relation_size(indexrelid::regclass) DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.