Index Optimization em PostgreSQL: Roteiro de Diagnóstico e Correção

Índice mal planejado é uma das causas mais comuns de degradação silenciosa em PostgreSQL: a query continua funcionando, só fica cada vez mais lenta, até que alguém abre um EXPLAIN ANALYZE em produção e descobre um Seq Scan em uma tabela de dezenas de milhões de linhas. Este roteiro cobre como diagnosticar índices ausentes, redundantes ou inchados (bloat), e como aplicar a correção com o menor impacto possível em produção.

Contexto do problema

Índice não é "otimização automática" — é uma estrutura de dados que o planner do PostgreSQL só usa se achar que compensa o custo de mantê-la atualizada em cada INSERT/UPDATE/DELETE. Os sintomas mais comuns de má gestão de índices:

• Queries que deveriam ser pontuais fazendo Seq Scan em tabelas grandes.

• Índices existentes que nunca são usados pelo planner, mas continuam consumindo espaço em disco e desacelerando escritas.

• Índices que já foram úteis, mas cresceram por bloat (páginas com muitas entradas mortas) e perderam eficiência.

  
  

Diagnóstico passo a passo

1. Confirmar se a query está de fato sem índice adequado

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
FROM tabela
WHERE coluna = valor;

Se o plano mostrar Seq Scan numa tabela grande com Filter fazendo a maior parte do trabalho, e o tempo de execução real (não o estimado) for alto, é sinal de índice ausente ou inutilizável para aquele predicado.

2. Levantar o uso real dos índices existentes

SELECT relname AS tabela,
       indexrelname AS indice,
       idx_scan,
       idx_tup_read,
       idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS tamanho
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

idx_scan baixo ou zero em um índice antigo (considerando que o servidor não tenha sido reiniciado recentemente, o que zera as estatísticas) é candidato a remoção — ele custa em cada escrita e não está sendo usado nas leituras.

3. Verificar bloat e fragmentação de índice

pgstatindex mede dois problemas distintos, e vale diferenciar antes de decidir a correção: bloat é espaço morto dentro das páginas (tuplas obsoletas que o vacuum ainda não recuperou), fragmentação é a desordem física das páginas no disco. Os dois degradam o índice por motivos diferentes, mas a correção para ambos é a mesma: reconstruir o índice. PostgreSQL não expõe um percentual de bloat pronto nas views padrão; a forma confiável de medir é com a extensão pgstatindex, do módulo pgstattuple:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT avg_leaf_density, leaf_fragmentation
FROM pgstatindex('nome_do_indice');

avg_leaf_density baixo indica bloat — páginas com muito espaço morto, normalmente causado por autovacuum atrasado ou padrões intensos de UPDATE/DELETE. leaf_fragmentation alto indica fragmentação — páginas logicamente sequenciais espalhadas fisicamente no disco, o que penaliza scans que percorrem o índice em ordem.

Solução aplicada

Criar índice sem bloquear escritas

Em produção, sempre com CONCURRENTLY — evita o lock exclusivo que a criação padrão de índice impõe sobre INSERT/UPDATE/DELETE na tabela:

CREATE INDEX CONCURRENTLY idx_tabela_coluna
ON tabela (coluna);

CONCURRENTLY leva mais tempo e não pode rodar dentro de uma transação, mas não trava a tabela para escrita durante a construção.

Index parcial, quando o acesso é sempre sobre um subconjunto

Se a maioria das consultas filtra por uma condição fixa (por exemplo, apenas registros com um determinado status), um índice parcial reduz tamanho e custo de manutenção:

CREATE INDEX CONCURRENTLY idx_tabela_status_pendente
ON tabela (coluna)
WHERE status = 'pendente';

Na minha opinião `índice parcial` é uma das features mais interessantes lançadas recentemente (SQLServer também permite, a propósito), mas tem que verificar se a versão usada tem suporte à este novo recurso.

Índice covering, para evitar acesso à tabela

Quando a query seleciona poucas colunas além do predicado de busca, incluir essas colunas no índice com INCLUDE permite Index-Only Scan, sem visitar a tabela:

CREATE INDEX CONCURRENTLY idx_tabela_coluna_covering
ON tabela (coluna)
INCLUDE (coluna_retornada);

Removendo bloat e fragmentação

Para índices com bloat confirmado ou com o nível de fragmentação alta, REINDEX CONCURRENTLY reconstrói o índice sem bloquear leituras/escritas na tabela (disponível a partir do PostgreSQL 12):

REINDEX INDEX CONCURRENTLY nome_do_indice;

Removendo índices não usados

Depois de confirmar em pg_stat_user_indexes que o índice não é utilizado — e que isso não é só um efeito de restart recente do servidor —:

DROP INDEX CONCURRENTLY idx_nao_usado;

Resultado e validação

A validação é sempre comparativa, antes e depois, com o mesmo EXPLAIN (ANALYZE, BUFFERS) usado no diagnóstico: o plano deve trocar Seq Scan por Index Scan ou Index Only Scan, e o tempo de execução real deve cair de forma consistente em execuções repetidas (não só na primeira, que pode estar sendo favorecida por cache). Vale também acompanhar idx_scan do novo índice nos dias seguintes, para confirmar que o planner de fato está optando por usá-lo em produção, e monitorar o impacto em escrita — todo índice novo tem custo em INSERT/UPDATE/DELETE, então o ganho em leitura precisa compensar esse custo.

Como regra geral: índice não é solução default para query lenta. Antes de criar um novo, vale confirmar com EXPLAIN ANALYZE que o gargalo é realmente falta de índice, e não estatísticas desatualizadas (ANALYZE pendente), parâmetros de memória mal dimensionados, ou uma query que precisa ser reescrita.

E, se precisar de apoio para identificar este e outros problemas, fique à vontade para entrar em contato conosco: contato@siltechconsult.com.br.