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.

Getting ready

The following query will list all invalid indexes, if any:

SELECT ir.relname AS indexname 
, it.relname AS tablename 
, n.nspname AS schemaname 
FROM pg_index i 
JOIN pg_class ir ON ir.oid = i.indexrelid 
JOIN pg_class it ON it.oid = i.indrelid 
JOIN pg_namespace n ON n.oid = it.relnamespace 
WHERE NOT i.indisvalid; 

Take note of these indexes, so that later you can tell whether...