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