Re: Disable an index temporarily

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.