Índices Duplicados e Redundantes

Índices duplicados desperdiçam disco, tornam as escritas mais lentas e confundem os DBAs. Audite catálogos e pg_stat_user_indexes antes de adicionar mais um índice composto no PostgreSQL 18.4.

Receita

Cartão de receita de referência rápida - pronto para copiar e colar.

SELECT indexrelid::regclass AS index_name, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE schemaname = 'public' AND relname = 'orders'
ORDER BY idx_scan;

Quando usar isso: A tabela tem muitos índices, a latência de inserção aumenta ou o autovacuum não consegue acompanhar.

Exemplo de Trabalho

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
-- Redundante: prefixo esquerdo do composto
CREATE INDEX orders_customer_idx ON orders (customer_id);
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);
 
-- Intenção duplicada (mesmas colunas, nome diferente)
CREATE INDEX orders_cust_created_idx ON orders (customer_id, created_at);
 
SELECT
  c.relname AS table_name,
  i.relname AS index_name,
  pg_get_indexdef(i.oid) AS definition,
  s.idx_scan
FROM pg_class c
JOIN pg_index ix ON ix.indrelid = c.oid
JOIN pg_class i ON i.oid = ix.indexrelid
LEFT JOIN pg_stat_user_indexes s ON s.indexrelid = i.oid
WHERE c.relname = 'orders'
ORDER BY pg_get_indexdef(i.oid);

Remova orders_customer_idx se todas as consultas que precisam de customer_id também filtram ou ordenam por created_at, ou mantenha o índice de coluna única apenas quando existirem consultas prefixo-únicas e mostrarem idx_scan > 0.

O que isso demonstra:

• Detecção de redundância de prefixo via pg_get_indexdef
• Evidência de idx_scan antes da remoção
• Colisões de nomes escondendo duplicatas

Análise Detalhada

Como Funciona

• O Índice A é prefixo-redundante do B se as colunas-chave de A são um prefixo esquerdo das de B e nenhuma restrição única exige A sozinho.
• Índices duplicados têm definições de chave idênticas (possivelmente nomes diferentes de migrações repetidas).
• Restrições de chave primária e UNIQUE criam índices; não duplique com um INDEX simples nas mesmas colunas.
• Índices não utilizados (idx_scan = 0 desde a reinicialização das estatísticas) são candidatos à remoção após uma janela de observação.

Consultas de Auditoria

-- Encontra índices nunca usados (desde a reinicialização das estatísticas)
SELECT relname, indexrelid::regclass, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname NOT IN ('pg_catalog', 'information_schema');
 
-- Ranking de tamanho
SELECT indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Armadilhas

• Remover índice de prefixo enquanto o ORM usa apenas igualdade na primeira coluna - O plano pode regredir. Correção: Verifique pg_stat_statements e idx_scan.
• Remover índice UNIQUE pensando que é redundante - A restrição também é removida. Correção: Remova a restrição ou re-adicione UNIQUE no índice restante.
• Reinicializar estatísticas e depois remover imediatamente - idx_scan zero engana. Correção: Observe durante um ciclo de negócios.
• Uso apenas em réplica - As estatísticas na primária podem não refletir as consultas de relatório na réplica se não forem roteadas. Correção: Agregue estatísticas das réplicas também.
• Criação duplicada concorrente - Ferramentas de migração executadas novamente. Correção: Migrações idempotentes com IF NOT EXISTS.

Alternativas

FAQs

Relacionados

• Ordem de Índices Multicoluna - Regras de prefixo
• REINDEX & REINDEX CONCURRENTLY - Reconstruir após rotatividade
• Bloat de Tabela e Índice - Medição de tamanho
• Melhores Práticas de Manutenção de Índice - Higiene contínua

Versões de Stack: Esta página foi escrita para PostgreSQL 18.4 (estável 18, manutenção 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, e PostGIS 3.5+.