O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required