主页 / dba / 问题 / 306259
Accepted
ITChap
Asked: 2022-01-20 01:16:57 +0800 CST

如何删除所有未用于主键或约束的索引

  • 772

我有一个旧数据集，其中包含许多未使用的索引。我想清理它并使其恢复到添加自定义索引之前的样子。

有没有办法删除除强制性索引（如主键、唯一约束等）之外的所有索引？

postgresql index

2 个回答

  1. Best Answer

    获取列表：

    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 JOINpg_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;

    如果有到数据库的并发连接，不要使用这个怪物命令，因为它会阻塞整个数据库，可能会阻塞很长一段时间。而且很容易死锁。

    • 2

  2. 这是我查找所有未使用索引的查询版本：

    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;
    • 1

相关问题