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.