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.

  • Is it possible to know when where statistics reset? – Gonzalo Vasquez May 4 '16 at 13:23
  • 1
    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

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.