Find unused indexes

Question

I'm using the following query to find unused indexes:

SELECT 
PSUI.indexrelid::regclass AS IndexName 
,PSUI.relid::regclass AS TableName 
FROM pg_stat_user_indexes AS PSUI 
JOIN pg_index AS PI 
ON PSUI.IndexRelid = PI.IndexRelid 
WHERE PSUI.idx_scan = 0 
AND PI.indisunique IS FALSE;

Should I run any stats gathering syntax or anything else before running it? Is the above query OK for such purpose? I mean, then all indexes shown in the SQL output should be just deleted?

It's a 8 year old BD, so resulting rows may be actually left overs and, I guess there should be enough stats so tell wherever and is used or not.

Answer 1

Seems like a decent approach. Of course, one should apply some human verification to this before automatically dropping everything that seems unused. For example, it's conceivable that the statistics were recently reset and/or an index is only used for some occasional batch tasks.

Answer 2

FWIW here's a query I've been using

SELECT
  relname AS table,
  indexrelname AS index,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
  idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique AND idx_scan =0 AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;