Índices no PostgreSQL: o guia que ninguém te deu mas você precisava

1. Anatomia de um índice: o que acontece por baixo do capô

Quando você cria um índice no PostgreSQL, está essencialmente construindo uma estrutura de dados separada que referencia sua tabela principal. A escolha da estrutura certa é o primeiro passo para um banco de dados performático.

B-Tree é o tipo padrão e mais versátil. Ideal para operações de igualdade e intervalo (=, <, >, BETWEEN). Mantém os dados ordenados e permite buscas em O(log n). Para 99% dos casos, é a escolha correta.

Hash serve apenas para operações de igualdade. Raramente supera a B-Tree, exceto em casos muito específicos com chaves de tamanho fixo.

GiST (Generalized Search Tree) é a base para dados geométricos (PostGIS), buscas full-text e operações de similaridade. Permite indexar tipos complexos como pontos, polígonos e vetores.

GIN (Generalized Inverted Index) é o rei das buscas full-text e arrays. Inverte a lógica: em vez de mapear chave → linha, mapeia termo → linhas que o contêm.

BRIN (Block Range Index) é o segredo para dados temporais. Em vez de indexar cada linha, agrupa blocos físicos e armazena o valor mínimo e máximo de cada grupo. Para logs e séries temporais, reduz o tamanho do índice em até 95%.

O custo oculto: todo índice adicional aumenta a latência de escritas (INSERT, UPDATE, DELETE) e gera mais trabalho para o VACUUM. O planner usa estatísticas do pg_statistic para decidir entre scan sequencial e index scan — se as estatísticas estiverem desatualizadas, decisões erradas acontecem.

-- Verificando estatísticas de uma tabela
SELECT tablename, attname, n_distinct, most_common_vals 
FROM pg_stats 
WHERE tablename = 'pedidos';

2. Tipos de índice que você (provavelmente) está subutilizando

Índices parciais são subestimados. Eles indexam apenas um subconjunto de linhas, economizando espaço e acelerando consultas específicas:

-- Índice apenas para pedidos ativos
CREATE INDEX idx_pedidos_ativos ON pedidos (data_criacao) 
WHERE status = 'ativo';

-- Consulta que se beneficia:
SELECT * FROM pedidos WHERE status = 'ativo' AND data_criacao > '2024-01-01';

Índices funcionais resolvem problemas de busca case-insensitive e expressões complexas:

-- Busca por email sem se preocupar com maiúsculas/minúsculas
CREATE INDEX idx_email_lower ON usuarios (LOWER(email));

-- Agora esta consulta usa o índice:
SELECT * FROM usuarios WHERE LOWER(email) = 'maria@exemplo.com';

Índices multicoluna exigem atenção à ordem das colunas. A regra de ouro: coloque primeiro as colunas de maior seletividade (que filtram mais linhas):

-- Ruim: cidade tem baixa cardinalidade
CREATE INDEX idx_ruim ON pedidos (cidade, data_criacao);

-- Bom: data é mais seletiva que cidade
CREATE INDEX idx_bom ON pedidos (data_criacao, cidade);

O PostgreSQL pode usar o índice para buscas na primeira coluna, mas não necessariamente para a segunda se a primeira não for filtrada.

3. Performance na prática: lendo planos de execução com EXPLAIN

Entender os tipos de scan é essencial:

  • Index Scan: acessa o índice e depois busca as linhas na tabela principal (heap). Pode envolver I/O aleatório.
  • Index Only Scan: todas as informações necessárias estão no próprio índice. Evita acessar a tabela. Muito mais rápido.
  • Bitmap Scan: combina múltiplos índices ou lê blocos em ordem física. Ideal para consultas que retornam muitas linhas.
EXPLAIN ANALYZE 
SELECT id, email FROM usuarios WHERE LOWER(email) = 'teste@exemplo.com';

-- Saída típica para Index Only Scan:
-- Index Only Scan using idx_email_lower on usuarios  
--   Index Cond: (lower((email)::text) = 'teste@exemplo.com'::text)
--   Heap Fetches: 0  -- Zero acessos à tabela principal!

Para detectar índices inúteis, consulte as estatísticas:

-- Índices nunca usados
SELECT schemaname, tablename, indexname, idx_scan 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0 
ORDER BY tablename;

-- Tabelas com muitos scans sequenciais
SELECT schemaname, tablename, seq_scan, seq_tup_read 
FROM pg_stat_all_tables 
WHERE seq_scan > 1000 
ORDER BY seq_scan DESC;

4. Armadilhas comuns e anti-padrões que matam a performance

Over-indexing é o pecado capital. Cada índice adicional:
- Aumenta o tempo de INSERT em ~0.5ms por índice
- Consome espaço em disco (um índice B-Tree ocupa ~30% do tamanho da tabela)
- Gera mais trabalho para o autovacuum

Índices em colunas de baixa cardinalidade são inúteis. Indexar uma coluna booleana ou um status com 3 valores possíveis raramente ajuda — o planner prefere o scan sequencial mesmo com o índice presente.

-- Inútil: status tem apenas 3 valores
CREATE INDEX idx_status_inutil ON pedidos (status);

-- Útil: apenas se combinado com outra coluna mais seletiva
CREATE INDEX idx_status_data ON pedidos (status, data_criacao);

O mito do índice clusterizado: o PostgreSQL não tem índices clusterizados como o MySQL (InnoDB). O comando CLUSTER reordena fisicamente a tabela com base em um índice, mas é uma operação única — inserts futuros quebram a ordenação. Só faz sentido para tabelas predominantemente de leitura.

5. Estratégias de manutenção e convivência com grandes volumes

Reindexando sem bloquear é possível com CONCURRENTLY:

-- Cria um novo índice em paralelo, sem travar a tabela
CREATE INDEX CONCURRENTLY idx_temp ON pedidos (data_criacao);

-- Remove o índice antigo
DROP INDEX CONCURRENTLY idx_antigo;

O trade-off: CONCURRENTLY é mais lento (precisa esperar transações concorrentes) e consome mais CPU e I/O.

Bloat de índices ocorre quando tuplas mortas se acumulam. Identifique com:

-- Estimar bloat de índices (requer pgstattuple)
SELECT * FROM pgstatindex('idx_pedidos_data');

Para limpeza pesada, pg_repack é a ferramenta padrão:

-- Reorganiza a tabela e índices sem bloqueio
pg_repack -d meu_banco -t pedidos -k

Automatize com scripts em cron:

#!/bin/bash
# Rotina semanal de manutenção
for table in $(psql -d meu_banco -t -c "SELECT tablename FROM pg_tables WHERE schemaname='public' AND tablename NOT LIKE '%_prt_%'"); do
    echo "Reindexando $table..."
    psql -d meu_banco -c "REINDEX TABLE CONCURRENTLY $table;"
done

6. Casos reais de otimização com índices no mundo corporativo

Caso 1: Busca full-text em artigos de blog

Sem índice: consulta levava 8 segundos em 2 milhões de artigos.

-- Antes: scan sequencial
SELECT titulo FROM artigos 
WHERE to_tsvector('portuguese', conteudo) @@ to_tsquery('portuguese', 'postgresql & índices');

-- Depois: índice GIN
CREATE INDEX idx_artigos_fts ON artigos USING GIN (to_tsvector('portuguese', conteudo));

Resultado: 8s → 45ms.

Caso 2: Logs de servidor (100 milhões de registros por mês)

Sem índice: consultas por data varriam a tabela inteira (30s+).

-- Índice BRIN: compacto e eficiente para dados sequenciais
CREATE INDEX idx_logs_data_brin ON logs_acesso USING BRIN (data_hora) 
WITH (pages_per_range = 32);

Resultado: índice de 80MB (vs 2.5GB com B-Tree), consultas em 150ms.

Caso 3: Query de relatório que travava o sistema

Consulta original: JOIN entre pedidos e itens, filtro por data e status, demorava 12 segundos.

-- Índice composto que matou a query
CREATE INDEX idx_pedidos_relatorio ON pedidos (status, data_criacao DESC, cliente_id);

-- Consulta otimizada:
SELECT p.id, p.cliente_id, SUM(i.valor) as total
FROM pedidos p
JOIN itens_pedido i ON i.pedido_id = p.id
WHERE p.status = 'finalizado'
  AND p.data_criacao BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY p.id, p.cliente_id;

Resultado: 12s → 30ms. A chave foi a ordem das colunas: status (filtro exato) primeiro, depois data (range), e cliente_id já incluso para Index Only Scan.

Índices são ferramentas poderosas, mas não são balas de prata. Cada índice criado deve ser justificado por uma query real, monitorado quanto ao uso e removido quando não mais necessário. O PostgreSQL oferece flexibilidade — use-a com sabedoria.

Referências