We have some large indexes that we suspect are not used on our Rails site, and would like to drop them to save space and calculate. However, this can be catastrophic if it turns out that they are being used. How can we confirm that they are not in use?
One option is to log all requests for a while and run an “explanation plan” for any of them that use the specified table. But I heard that the “plan of explanation” can sometimes be inaccurate. We would also need to collect requests within a few hours to make sure there are quite a lot of logs to store and process.
If there was a way to temporarily disable the index, we would be ready to do it if we could quickly turn it on if there were problems. But I see no way to do this everywhere; you can only specify an ignore index indication for individual sql statements.
source
share