Is it possible to temporarily disable an index in Postgres?

Question

I have got one index on a table that I would like to temporarily disable, I can't find any documentation suggesting that it's possible, though.

Reason: I've got an index that might be causing problems in queries unrelated to to any of the ones it was designed to speed up. It's a new index, and the system as a whole seems slower since it was introduced. I just want to be able to reliably eliminate it as the culprit, and this seems like the easiest way, other solution suggestions, as well as better question suggestions, are also welcome.

Answer 1

You can poke the system catalogue to disable an index:

update pg_index set indisvalid = false where indexrelid = 'test_pkey'::regclass 

This means that the index won't be used for queries but will still be updated. It's one of the flags used for concurrent index building. Note that I've only done a quick test to see if the index still seems to be updated, caveat emptor.

Answer 2

begin; drop index foo_ndx; explain analyze select * from foo; rollback; 

I don't think there is a way to disable just one, though you can do this in a transaction to make recovering from it dead simple. You can also disable indexscan to disable all indices.

Also, make sure you are doing explain analyze on your queries.