Índice que ninguém usa e toda escrita paga: encontrar e remover índices sem arriscar a consulta rara

Índice não é de graça só porque nenhuma leitura o usa. Toda inserção na tabela vira uma inserção em cada árvore de índice, cada uma com seu registro de WAL e suas páginas disputando espaço no shared_buffers. A atualização é pior: se qualquer coluna indexada mudar, ou se não houver espaço na página para a nova versão da linha, o Postgres não consegue fazer um update HOT e precisa inserir uma entrada nova em todos os índices da tabela, inclusive nos que não têm relação com a coluna alterada.

No ERP, o índice em atualizado_em tinha sido criado para um job de sincronização desativado havia um ano. Como toda atualização de pedido grava atualizado_em, nenhuma delas podia ser HOT: a taxa de updates HOT da tabela era de 4%, e cada mudança de status de pedido gerava 22 inserções em índices. O custo não aparece em pg_stat_statements como uma consulta lenta. Ele se espalha por todas as escritas, pelo vacuum, pela réplica e pelo backup.

-- Indices sem nenhuma varredura desde o ultimo reset, com tamanho e restricoes.
-- E so a lista de suspeitos: o numero vale para ESTA instancia e para ESTE periodo.
SELECT s.schemaname || '.' || s.indexrelname AS indice,
       s.relname                              AS tabela,
       s.idx_scan,
       s.last_idx_scan,                       -- Postgres 16 ou superior
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS tamanho,
       i.indisunique,
       i.indisprimary,
       (SELECT string_agg(c.conname, ', ')
          FROM pg_constraint c
         WHERE c.conindid = s.indexrelid)     AS restricoes
  FROM pg_stat_user_indexes s
  JOIN pg_index i ON i.indexrelid = s.indexrelid
 WHERE s.idx_scan = 0
 ORDER BY pg_relation_size(s.indexrelid) DESC;

-- Desde quando esses contadores valem nesta instancia.
SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

-- O que a tabela paga para manter os indices: proporcao de updates HOT.
SELECT relname,
       n_tup_ins,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS pct_hot,
       pg_size_pretty(pg_relation_size(relid))       AS heap,
       pg_size_pretty(pg_indexes_size(relid))        AS indices
  FROM pg_stat_user_tables
 WHERE relname = 'pedidos';

A primeira consulta lista suspeitos, não culpados. No primário do ERP, ela trouxe 9 índices com idx_scan = 0, somando 31 GB. Dois deles eram exatamente os que o fechamento fiscal usava na réplica. A diferença entre essa lista e uma lista segura para apagar é o assunto das próximas seções.

O contador de pg_stat_user_indexes conta quantas vezes o índice foi usado em varreduras desde o último reset, nesta instância. Cada parte dessa frase esconde uma armadilha, e a remoção de oito meses antes caiu na primeira delas.

• As estatísticas são por instância. Uma réplica de leitura mantém seus próprios contadores, e o primário não sabe o que ela leu. Um índice usado só pela réplica de BI aparece com idx_scan = 0 no primário, mesmo sendo essencial.
• O contador é zerado por pg_stat_reset(), por um crash ou desligamento imediato, por pg_upgrade e pela restauração de backup. Depois de um failover, o novo primário carrega os contadores que tinha como réplica. Sem olhar stats_reset, um "zero" pode significar só "zero desde ontem".
• A janela pode ser curta demais. Fechamento mensal, auditoria anual, exportação de dados pessoais a pedido do titular e scripts de incidente rodam raramente e dependem de índices que ficam semanas sem uma varredura.
• Índices que sustentam PRIMARY KEY, UNIQUE ou EXCLUDE aplicam uma regra. A checagem de unicidade na inserção não conta como varredura, então um índice único pode ter idx_scan = 0 e ser a única coisa que impede pedidos com número duplicado.
• O contrário também engana: idx_scan alto não prova que o índice é necessário. Se existem (loja_id) e (loja_id, criado_em), o planejador usa os dois, e os dois aparecem como usados, embora o segundo atenda sozinho as consultas do primeiro.

No Postgres 16 ou superior, a coluna last_idx_scan mostra o momento da última varredura e ajuda a separar "nunca usado" de "usado no começo do mês". Ela continua sendo por instância e continua sendo zerada junto com os contadores, então resolve a leitura do tempo, não a do escopo.

A correção é transformar uma leitura pontual em uma série. Um coletor diário grava o contador de cada índice em cada nó, incluindo todas as réplicas que recebem leitura, e a análise soma os incrementos da janela. O coletor falha alto quando não consegue ler algum nó, porque um nó sem dados é exatamente o tipo de buraco que transforma um índice usado em candidato à remoção.

// coletar-uso-indices.mjs: roda uma vez por dia (cron) e guarda o contador de
// varreduras de cada indice em TODOS os nos. As estatisticas sao por instancia:
// o primario nao enxerga o que as replicas leem.
import pg from 'pg';

const nos = [
  { nome: 'primario', url: process.env.DATABASE_URL_PRIMARIO },
  { nome: 'replica-api', url: process.env.DATABASE_URL_REPLICA_API },
  { nome: 'replica-relatorios', url: process.env.DATABASE_URL_REPLICA_RELATORIOS },
];

const CONSULTA_USO = `
  SELECT s.schemaname || '.' || s.indexrelname AS indice,
         s.idx_scan,
         d.stats_reset
    FROM pg_stat_user_indexes s
   CROSS JOIN pg_stat_database d
   WHERE d.datname = current_database()`;

export async function coletarUso(destino) {
  const coletadoEm = new Date();
  const falhas = [];

  for (const no of nos) {
    const client = new pg.Client({ connectionString: no.url, statement_timeout: 10000 });
    let conectado = false;
    try {
      await client.connect();
      conectado = true;
      const { rows } = await client.query(CONSULTA_USO);
      await destino.query(
        `INSERT INTO indice_uso_snapshot (coletado_em, no, indice, idx_scan, stats_reset)
         SELECT $1, $2, u.indice, u.idx_scan, u.stats_reset
           FROM unnest($3::text[], $4::bigint[], $5::timestamptz[])
             AS u(indice, idx_scan, stats_reset)`,
        [
          coletadoEm,
          no.nome,
          rows.map((r) => r.indice),
          rows.map((r) => r.idx_scan),
          rows.map((r) => r.stats_reset),
        ],
      );
    } catch (erro) {
      // Um no sem coleta nao pode virar "indice sem uso": a consulta de analise
      // exige que todos os nos tenham sido observados.
      falhas.push(`${no.nome}: ${erro.message}`);
    } finally {
      if (conectado) await client.end().catch(() => {});
    }
  }

  if (falhas.length) throw new Error(`Coleta incompleta: ${falhas.join('; ')}`);
}

const destino = new pg.Pool({ connectionString: process.env.DATABASE_URL_COLETA });
try {
  await coletarUso(destino);
} finally {
  await destino.end();
}

A análise compara cada coleta com a anterior do mesmo nó. Quando stats_reset muda ou o contador diminui, o valor atual é tratado como incremento inteiro, porque o contador recomeçou do zero. O índice só entra na lista final se a soma for zero, se todos os nós configurados tiverem sido observados e se a cobertura passar de 35 dias, o que garante pelo menos uma virada de mês.

CREATE TABLE IF NOT EXISTS indice_uso_snapshot (
  coletado_em timestamptz NOT NULL,
  no          text        NOT NULL,
  indice      text        NOT NULL,
  idx_scan    bigint      NOT NULL,
  stats_reset timestamptz,
  PRIMARY KEY (no, indice, coletado_em)
);

-- Varreduras de cada indice na janela, somando todos os nos e tratando resets.
WITH ordenado AS (
  SELECT no, indice, coletado_em, idx_scan, stats_reset,
         lag(idx_scan)    OVER w AS scan_anterior,
         lag(stats_reset) OVER w AS reset_anterior
    FROM indice_uso_snapshot
   WHERE coletado_em >= now() - interval '42 days'
  WINDOW w AS (PARTITION BY no, indice ORDER BY coletado_em)
),
delta AS (
  SELECT no, indice, coletado_em,
         CASE
           WHEN scan_anterior IS NULL THEN 0
           -- contador zerado (pg_stat_reset, crash, failover): o valor atual e todo novo
           WHEN stats_reset IS DISTINCT FROM reset_anterior
             OR idx_scan < scan_anterior THEN idx_scan
           ELSE idx_scan - scan_anterior
         END AS scans
    FROM ordenado
)
SELECT indice,
       sum(scans)                          AS scans_na_janela,
       count(DISTINCT no)                  AS nos_observados,
       max(coletado_em) - min(coletado_em) AS cobertura
  FROM delta
 GROUP BY indice
HAVING sum(scans) = 0
   AND count(DISTINCT no) = 3                               -- todos os nos configurados
   AND max(coletado_em) - min(coletado_em) >= interval '35 days'
 ORDER BY indice;

A janela depende do negócio, não do banco. No ERP, a coleta ficou 42 dias ligada para cobrir o fechamento de outubro e a primeira semana de novembro, quando roda a apuração de impostos. Para os jobs anuais, que nenhuma janela razoável cobre, a fonte é outra: um catálogo das consultas raras, montado com quem cuida do fiscal, da auditoria e do atendimento ao titular de dados, que é usado na etapa de ensaio.

A segunda fonte de desperdício são índices usados que outro índice já atende. Um b-tree em (loja_id, criado_em) serve qualquer consulta que filtre só por loja_id, porque a árvore está ordenada primeiro por essa coluna. Manter também um índice só em (loja_id) paga a escrita duas vezes para ganhar uma árvore um pouco menor na leitura. A consulta abaixo encontra esses casos comparando colunas e classes de operador como prefixo.

-- Indices b-tree cujas colunas e classes de operador sao prefixo de outro indice
-- da mesma tabela. Parciais e com expressao ficam de fora: analise manual.
WITH idx AS (
  SELECT i.indexrelid,
         i.indrelid,
         i.indisunique,
         string_to_array(i.indkey::text, ' ')   AS colunas,
         string_to_array(i.indclass::text, ' ') AS classes,
         c.relam
    FROM pg_index i
    JOIN pg_class c ON c.oid = i.indexrelid
   WHERE i.indpred IS NULL
     AND i.indexprs IS NULL
)
SELECT a.indrelid::regclass   AS tabela,
       a.indexrelid::regclass AS redundante,
       b.indexrelid::regclass AS coberto_por,
       pg_size_pretty(pg_relation_size(a.indexrelid)) AS tamanho
  FROM idx a
  JOIN idx b
    ON b.indrelid = a.indrelid
   AND b.indexrelid <> a.indexrelid
   AND b.relam = a.relam
   AND b.colunas[1:cardinality(a.colunas)] = a.colunas
   AND b.classes[1:cardinality(a.classes)] = a.classes
   -- duplicata exata: lista so um dos dois
   AND (cardinality(b.colunas) > cardinality(a.colunas) OR a.indexrelid > b.indexrelid)
 WHERE NOT a.indisunique
 ORDER BY pg_relation_size(a.indexrelid) DESC;

No ERP, a consulta apontou três índices de prefixo e uma duplicata exata criada por duas migrações de times diferentes, com nomes diferentes, no mesmo trimestre. Os redundantes também passam pelo ensaio da próxima seção: a árvore maior costuma ser só um pouco mais lenta, mas uma consulta muito frequente que faz index-only scan pode perder essa vantagem se o índice maior for bem mais largo.

O Postgres não tem índice invisível nativo, mas a extensão hypopg permite esconder um índice do planejador dentro de uma sessão. Isso não economiza nenhuma escrita, porque o índice continua sendo mantido, mas permite rodar o EXPLAIN de cada consulta rara do catálogo como se o índice não existisse, sem afetar nenhuma outra conexão. O ensaio é feito numa cópia restaurada com volume real, onde um plano ruim não custa nada.

-- 1. Guardar a volta: a definicao exata, versionada junto com a migracao.
SELECT pg_get_indexdef('public.pedidos_atualizado_em_idx'::regclass);
-- CREATE INDEX pedidos_atualizado_em_idx ON public.pedidos USING btree (atualizado_em)

-- 2. Ensaiar as consultas raras sem o indice, numa sessao da copia restaurada.
--    hypopg_hide_index esconde o indice do planejador so nesta sessao.
CREATE EXTENSION IF NOT EXISTS hypopg;
SELECT hypopg_hide_index('public.pedidos_atualizado_em_idx'::regclass);
EXPLAIN (COSTS ON)
SELECT loja_id, sum(valor_centavos)
  FROM pedidos
 WHERE atualizado_em >= date '2026-10-01' AND atualizado_em < date '2026-11-01'
 GROUP BY loja_id;
SELECT hypopg_unhide_all_indexes();

-- 3. Remover um por vez, fora do pico e fora da semana de fechamento.
--    CONCURRENTLY nao roda dentro de transacao: a migracao precisa desligar o BEGIN automatico.
SET lock_timeout = '3s';
DROP INDEX CONCURRENTLY IF EXISTS public.pedidos_atualizado_em_idx;

-- 4. Se a observacao mostrar regressao: recriar sem bloquear escrita e conferir o resultado.
CREATE INDEX CONCURRENTLY pedidos_atualizado_em_idx ON public.pedidos USING btree (atualizado_em);
SELECT indisvalid FROM pg_index WHERE indexrelid = 'public.pedidos_atualizado_em_idx'::regclass;

1. Classificar: índice que sustenta restrição fica, a menos que a restrição em si seja revista. Índice usado em qualquer nó na janela fica ou vai para a análise de redundância.
2. Ensaiar: rodar o EXPLAIN de cada consulta do catálogo de raras com o índice escondido. Se o plano trocar uma varredura de índice por uma sequencial em tabela grande, o índice fica e ganha um comentário dizendo quem o usa.
3. Guardar a volta: versionar o resultado de pg_get_indexdef e medir na cópia quanto tempo o CREATE INDEX CONCURRENTLY leva. É esse número que define o pior caso de uma remoção errada.
4. Remover um por vez, com DROP INDEX CONCURRENTLY e lock_timeout, fora do pico e longe da semana de fechamento. Remover em lote torna impossível saber qual remoção causou uma regressão.
5. Observar um ciclo: acompanhar o tempo médio das consultas principais em pg_stat_statements, o seq_scan da tabela em pg_stat_user_tables e os planos registrados pelo auto_explain para consultas acima de um limite.
6. Se houver regressão, recriar com a definição guardada e conferir indisvalid, porque um CREATE INDEX CONCURRENTLY que falha deixa para trás um índice inválido que continua custando escrita.

Índice candidato
      |
      v
Sustenta PK, UNIQUE ou EXCLUDE? ---- sim ----> fica (a regra depende dele)
      | não
      v
Varreduras em todos os nós, janela >= 35 dias ---- > 0 ----> fica (ou checar redundância)
      | zero
      v
EXPLAIN das consultas raras com o índice escondido (hypopg) ---- plano piora ----> fica
      | plano igual
      v
Guardar pg_get_indexdef + medir tempo de recriação na cópia
      |
      v
DROP INDEX CONCURRENTLY, um por vez, com lock_timeout, fora do fechamento
      |
      v
Observar um ciclo inteiro ---- regressão ----> CREATE INDEX CONCURRENTLY com a definição guardada

O cuidado com lock_timeout não é detalhe. DROP INDEX CONCURRENTLY não bloqueia leituras nem escritas durante a remoção, mas precisa esperar as transações que estão usando a tabela. Uma transação esquecida aberta pode deixar o comando esperando, e qualquer DDL que venha atrás dele fica na fila. Com o tempo limite, o comando desiste e pode ser repetido depois.

Dos 22 índices, 9 saíram em três semanas, um por dia útil: cinco sem uso em nenhum dos três nós durante 42 dias, três redundantes por prefixo e a duplicata exata. Os dois índices que o fechamento fiscal usa na réplica ficaram, agora com comentário, e o índice único em numero_pedido ficou apesar do idx_scan = 0. Nenhuma remoção precisou ser desfeita.

O salto nos updates HOT veio quase inteiro da remoção do índice em atualizado_em, e foi ele que mais reduziu o WAL. Para que a tabela não voltasse a acumular índices, o time adotou três regras simples:

• Todo índice novo entra por migração com COMMENT ON INDEX dizendo qual consulta atende e qual time é dono, o que transforma "ninguém sabe para que serve" em uma pergunta com destinatário.
• A revisão de migração roda a consulta de redundância contra o esquema com o índice novo, antes do merge.
• O coletor continua ligado, e a cada trimestre a lista de índices sem uso em todos os nós vira uma tarefa com dono, em vez de uma limpeza heroica a cada dois anos.