O'Reilly logo

PostgreSQL 9 Administration Cookbook - Second Edition by Simon Riggs, Gianni Ciolli, Hannu Krosing, Gabriele Bartolini

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

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