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.

2 Answers 2


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.

  • 2
    Is it possible to know when where statistics reset? Commented May 4, 2016 at 13:23
  • 4
    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
    Commented May 4, 2016 at 15:14
  • 3
    and to do that: select stats_reset from pg_stat_database; Commented Nov 21, 2019 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;
  • 2
    This looks great. Can you add a bit of comments to some of the conditions? E.g. indisunique, idx_scan=0, pg_relation_size(i.indexrelid) / nullif(idx_scan, 0)?
    – pir
    Commented Jun 17, 2022 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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