Início / dba / Perguntas / 306259
Accepted
ITChap
Asked: 2022-01-20 01:16:57 +0800 CST

Como descartar todos os índices não usados ​​para chaves primárias ou restrições

  • 772

Eu tenho um conjunto de dados antigo com muitos índices não utilizados. Eu gostaria de limpá-lo e trazê-lo de volta ao que parecia antes da adição de índices personalizados.

Existe uma maneira de descartar todos os índices, exceto os obrigatórios (como chaves primárias, restrições exclusivas etc.)?

postgresql index

2 respostas

  1. Best Answer

    Para obter uma lista:

    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<>fique aqui

    Isso funciona para o Postgres 14 . Versões futuras podem divergir.

    Isso lista todos os índices que não são implementados por uma restrição ( PRIMARY, UNIQUE, EXCLUDE) - verificado novamente com um LEFT JOINto pg_constraintpara descartar qualquer conexão com qualquer restrição.
    Além disso, o índice não é usado para CLUSTERa tabela, nem para replicação.

    Mais detalhes no manual sobre pg_index .

    Inspecione o resultado. Pode haver qualquer número de índices úteis entre os demais. Como colunas com FOREIGN KEYrestrições de saída, onde um índice é opcional, mas frequentemente recomendado...
    Tem certeza? (Tem certeza que você tem certeza?)

    Você pode soltar um por um ou usar este comando gerado para soltá-los todos de uma vez:

    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;

    Se houver conexões simultâneas com o banco de dados, não use este comando-monstro, pois ele bloqueará todo o banco de dados, possivelmente por um longo período de tempo. E pode facilmente travar.

    • 2

  2. Aqui está minha versão de uma consulta para encontrar todos os índices não utilizados:

    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

relate perguntas