How do I drop all invalid indexes in postgres?

Question

Using this query:

I have hundreds of invalid indexes:

SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;

 public  | post_aggregates_pkey_ccnew_ccnew_ccnew1
 public  | post_aggregates_post_id_key_ccnew_ccnew_ccnew1
 public  | idx_post_aggregates_stickied_hot_ccnew_ccnew_ccnew1
 public  | idx_post_aggregates_hot_ccnew_ccnew_ccnew1
...

They don't appear to be in use, and I have no idea why they are being created (seems to me that they shouldn't remain), as the original indexes still exist.

klin · Accepted Answer · 2022-06-03T13:40:36.583

You need dynamic commands inside a function or anonymous code block.

do $$
declare
    rec record;
begin
    for rec in
        select relnamespace::regnamespace as namespace, relname
        from pg_index i
        join pg_class c on c.oid = i.indexrelid
        where not indisvalid
    loop
        execute format('drop index %s.%s', rec.namespace, rec.relname);
        -- optionally:
        -- raise notice '%', format('drop index %s.%s', rec.namespace, rec.relname);
    end loop;
end $$;

Postgres automatically creates an index when creating or altering table constraints in CREATE TABLE or ALTER TABLE. Other than these, it never creates indexes on its own.

The most likely cause of invalid indexes is careless use of the CREATE [UNIQUE] INDEX CONCURRENTLY command. When the command is executed in parallel transactions, there is a high probability of deadlocks, which cause the command to fail and leave an invalid index. When a unique index is created concurrently, the uniqueness violation may also lead to failure.

Concurrent indexing should be under the strict control of an administrator who is aware of these issues, especially when it is automatically performed on a regular basis.

How do I drop all invalid indexes in postgres?

1 Answers1