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



  • 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)?



  • 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 https://dbfiddle.uk/?rdbms=postgres_14&fiddle=306f67c05ec8029fe5ec3210847607b5

    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 https://www.postgresql.org/docs/current/catalog-pg-index.html .

    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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2