如何刪除所有未用於主鍵或約束的索引

我有一個舊數據集，其中包含許多未使用的索引。我想清理它並使其恢復到添加自定義索引之前的樣子。

有沒有辦法刪除除強制性索引（如主鍵、唯一約束等）之外的所有索引？

獲取列表：

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<>在這裡擺弄

這適用於Postgres 14。未來的版本可能會有所偏差。

這列出了未由約束 ( PRIMARY, UNIQUE, ) 實現的每個索引 - 使用to進行EXCLUDE雙重檢查以排除與任何約束的任何連接。 另外，索引不用於表，也不用於複製。LEFT JOIN``pg_constraint
CLUSTER

有關pg_index的手冊中的更多詳細資訊。

檢查結果。其餘索引中可能有任意數量的有用索引。就像具有傳出FOREIGN KEY約束的列一樣，索引是可選的，但通常建議使用…

您確定嗎？（你確定你確定嗎？）

您可以一個一個地刪除，或者使用這個生成的命令一次刪除它們：

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;

如果有到數據庫的並發連接，不要使用這個怪物命令，因為它會阻塞整個數據庫，可能會阻塞很長一段時間。而且很容易死鎖。

引用自：https://dba.stackexchange.com/questions/306259