Database Reference
In-Depth Information
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 one. What
we want is a way of removing the index that if we discover that removing it was a mistake, we
can put the index back again quickly.
How to do 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 the following function
to "undrop" the index:
CREATE OR REPLACE FUNCTION trial_undrop_index(iname TEXT)
RETURNS VOID
LANGUAGE SQL AS $$
UPDATE pg_index
SET indisvalid = true
WHERE indexrelid = $1::regclass;
$$;
How it works...
This recipe also uses some inside knowledge. When we create an index using
CREATE
INDEX
CONCURRENTLY
, it is a two-stage process. The first phase builds the index, and then marks
it invalid. Inserts, updates, and deletes now begin maintaining the index, but we do a further
pass over the table to see if we missed anything before we declare the index valid. User
queries don't use the index until it says valid.
Once the index is built and the flag is valid, then if we set the flag to invalid, the index will still
be maintained, just not used by queries. This allows us to turn the index off quickly, though
with the option to turn it back on again if we realize we actually do need the index after all.
This makes it practical to test whether dropping the index will alter the performance of any of
your most important queries.
