6

Postgresql 9.3 Debian 7

I have a lot of huge indexes in a legacy database I'm trying to optimize. Thinking about dropping all the useless ones, but how can I tell how often they are used and if they are not used at all.

Is there any usage statistics somewhere or some trick query to do that?

7

See this post, which links to a big query summarizing all the indexes which may not be pulling their weight.

7

You can monitor the index usage through pg_stat_user_indexes and pg_statio_user_indexes

More details about the statistics collector can be found in the manual:
http://www.postgresql.org/docs/current/static/monitoring-stats.html

You should be careful with dropping unused unique indexes though. They might not be used for reading, but they are most probably vital to the integrity of your data.

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.