How to drop all indexes not used for primary keys or constraints

Question

I have an old dataset with a lot of unused indexes. I would like to clean it up and bring it back to what it looked like before custom indexes were added.

Is there a way to drop all indexes except the mandatory ones (like primary keys, unique constraints, etc)?

Answer 1

To get a list:

SELECT n.nspname AS schema, i.indrelid::regclass::text AS tbl, cl.relname AS idx, pg_get_indexdef(i.indexrelid)
     , 'DROP INDEX ' || i.indexrelid::regclass AS drop_cmd
FROM   pg_index           i
JOIN   pg_class           cl ON cl.oid = i.indexrelid
JOIN   pg_namespace       n  ON n.oid = cl.relnamespace
LEFT   JOIN pg_constraint co ON co.conindid = i.indexrelid
WHERE  n.nspname <> 'information_schema'  -- ignore system schemas
AND    n.nspname NOT LIKE 'pg\_%'         -- ignore system schemas
AND    co.conindid IS NULL   -- no connected constraint
AND    NOT i.indisprimary    -- not PK
AND    NOT i.indisunique     -- not UNIQUE
AND    NOT i.indisexclusion  -- not EXCLUDE
AND    NOT i.indisclustered  -- not clustered
AND    NOT i.indisreplident  -- not replicated
ORDER  BY 1, 2, 3;

db<>fiddle here

This works for Postgres 14. Future versions may deviate.

This lists every index that is not implemented by a constraint (PRIMARY, UNIQUE, EXCLUDE) - double-checked with a LEFT JOIN to pg_constraint to rule out any connection to any constraint.
Plus, the index is not used to CLUSTER the table, nor for replication.

More details in the manual about pg_index.

Inspect the result. There may be any number of useful indexes among the rest. Like columns with outgoing FOREIGN KEY constraints, where an index is optional but often recommended ...
Are you sure? (Are you sure you are sure?)

You can drop one by one, or use this generated command to drop them all at once:

SELECT 'DROP INDEX ' || string_agg(i.indexrelid::regclass::text, ', ' ORDER  BY n.nspname, i.indrelid::regclass::text, cl.relname) AS drop_cmd
FROM   pg_index i
JOIN   pg_class cl ON cl.oid = i.indexrelid
JOIN   pg_namespace n ON n.oid = cl.relnamespace
LEFT   JOIN pg_constraint co ON co.conindid = i.indexrelid
WHERE  n.nspname <> 'information_schema'
AND    n.nspname NOT LIKE 'pg\_%'
AND    co.conindid IS NULL  --  no connected constraint
AND    NOT i.indisprimary
AND    NOT i.indisunique
AND    NOT i.indisexclusion
AND    NOT i.indisclustered
AND    NOT i.indisreplident;

If there are concurrent connections to the database, don't use this monster-command, as it will block the whole DB, possibly for an extended period of time. And it may easily deadlock.

Answer 2

Here is my version of a query to find all unused indexes:

SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT i.indisunique   -- is not a UNIQUE index
  AND NOT EXISTS          -- does not enforce a constraint
         (SELECT 1 FROM pg_catalog.pg_constraint c
          WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;