Carefully removing unwanted indexes

Carefully removing? You mean press Enter gently after typing DROP INDEX? Err, no!

The thinking is that it takes a long time to build an index, and a short time to drop it. What we want is a way of removing an index such that if we discover that removing it was a mistake, we can put the index back again quickly.

How to do it…

We will describe a procedure that allows us to deactivate an index without actually dropping it, so that we can appreciate what its contribution was and possibly reactivate it:

1. First, create the following function:

   CREATE OR REPLACE FUNCTION trial_drop_index(iname TEXT)
   RETURNS VOID
   LANGUAGE SQL AS $$
   UPDATE pg_index
   SET indisvalid = false
   WHERE indexrelid = $1::regclass;
   $$;
   

2. Then, run it to do a trial of dropping the index.

3. If you experience performance issues after dropping the index, then use this function to "undrop" the index:

   CREATE OR REPLACE FUNCTION trial_undrop_index(iname TEXT)
   RETURNS VOID
   LANGUAGE SQL AS $$
   UPDATE pg_index...