PostgreSQL 11 Administration Cookbook

How to do it…

Here, 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  VOIDLANGUAGE  SQL  AS  $$ UPDATE  pg_indexSET  indisvalid  =  falseWHERE  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 the following function to undrop the index:

CREATE  OR  REPLACE  FUNCTION  trial_undrop_index(iname  TEXT) RETURNS  VOIDLANGUAGE  SQL  AS  $$ UPDATE  pg_indexSET  indisvalid  =  trueWHERE  indexrelid  =  $1::regclass;$$;

Be careful to avoid undropping ...