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

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.


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.

  • 1
    Is it possible to know when where statistics reset? – Gonzalo Vasquez May 4 '16 at 13:23
  • 3
    You can check in pg_stat_database.stats_reset. This detects not only intentional resets, but also resets due to crashes or unclean shutdowns. Stats can also be reset on individual objects, but I don't think there is any way to see when/if that has happened. – jjanes May 4 '16 at 15:14
  • 2
    and to do that: select stats_reset from pg_stat_database; – John Bachir Nov 21 '19 at 20:38

FWIW here's a query I've been using

  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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.