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.
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:
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; $$;
Then, run it to do a trial of dropping the index.
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...