Torsten Förtsch wrote on 20.04.2014 10:09:
The problem is I have a number of indexes in a large system that are
very similar. And I suspect some of them are superfluous.
Example:
btree (fmb_id, action_type)
btree (fmb_id)
Action_type in this case is one out of a handful of values (should
perhaps be an ENUM but is TEXT) and for most of the table the
combination of (fmb_id, action_type) is unique. The table itself has
~2E8 rows. So it takes a while to build these indexes from scratch.
Now, we have several performance counters in place. I want to disable
these indexes one by one and see what happens. I am probably not able to
find all of the queries that use them. But I believe that nothing much
happens if I drop one of them (preferably the former?).
What about monitoring pg_stat_all_indexes to see if an index is used:
http://www.postgresql.org/docs/current/static/monitoring-stats.html#PG-STAT-ALL-INDEXES-VIEW
Btw: in the above example the second one is definitely not needed.
Any query that uses the second one (single column) can also use the first one.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
