Índices em profundidade

Índice é a única ferramenta que muda a complexidade de uma query de O(N) para O(log N) sem reescrever o código. Em sistemas grandes a diferença não é estética — é entre segundos e milissegundos, entre rodar e cair sob carga, entre o cliente clicar e desistir. Mais do que qualquer outra escolha de banco, a estratégia de indexação separa quem entende Postgres de quem usa Postgres.

O senso comum trata índice como solução universal: query lenta? cria índice. Em alguns casos isso resolve; em muitos, agrava — o otimizador escolhe um índice errado, ou o índice criado não pode ser usado pela cláusula da query, ou o ganho de leitura é menor que o custo de escrita que ele introduz. Índice é compensação, não mágica. Cada índice criado é mais trabalho a cada INSERT e UPDATE, mais espaço em disco, e mais opções para o otimizador errar.

Este conceito mergulha em como cada tipo de índice funciona por dentro, em que padrões de query cada um serve, e como decidir qual criar. O foco é Postgres, mas as estruturas de dados são universais — B-tree existe em SQL Server, MySQL, Oracle, e o raciocínio se transfere.

B-tree — o índice padrão e o que ele faz

Quando você cria CREATE INDEX idx ON tabela(coluna) sem especificar tipo, Postgres cria um índice B-tree. O nome é enganoso — não é uma "binary tree", é uma estrutura balanceada onde cada nó pode ter centenas de filhos. Foi descrita por Rudolf Bayer e Edward McCreight em 1972 (na Boeing, original sobre sistemas bancários) e domina indexação até hoje porque casa perfeitamente com a estrutura de páginas de disco e cache de memória.

Visualize: a raiz aponta para algumas dezenas de "blocos intermediários" ordenados por valor da coluna; cada bloco intermediário aponta para outros, e assim por diante até as folhas — que contêm os valores em ordem e ponteiros para as linhas reais da tabela (em Postgres, o ctid: page + offset). Para encontrar uma linha, o banco lê a raiz (1 página), desce até a folha (geralmente 3-4 páginas em árvores grandes), pega o ctid e busca a linha (1 página). Total: cinco acessos a disco, contra varrer milhões de linhas.

A B-tree resolve, naturalmente, todos os operadores que dependem de ordem total: =, <, <=, >, >=, BETWEEN, IS NULL, e LIKE 'prefixo%' (mas não LIKE '%algo'). Também serve para ORDER BY sobre a coluna indexada — a árvore já está ordenada, então o banco caminha as folhas em ordem sem ordenar de novo.

Índice composto — a ordem das colunas importa

CREATE INDEX idx ON pedidos(cliente_id, criado_em) cria um índice em duas colunas. A árvore organiza primeiro por cliente_id, depois — dentro de cada cliente_id — por criado_em. Esse índice serve queries que filtram pela primeira coluna, ou pelas duas em ordem; não serve queries que filtram só pela segunda. Quem só busca por criado_em vai ler o índice todo — pior do que sequential scan, geralmente.

A regra de ouro é: coloque primeiro a coluna mais seletiva entre as que aparecem com igualdade; depois colunas que aparecem em range. Se a query filtra cliente_id = 42 AND criado_em >= '2026-01-01', (cliente_id, criado_em) é a ordem ideal. Já se filtra status = 'pago' AND cliente_id = 42, a ordem depende: status tem 4-5 valores possíveis (baixa seletividade), cliente_id tem milhões. O índice útil é (cliente_id, status) — não o contrário.

Hash, BRIN, GiST, GIN, SP-GiST — quando B-tree não basta

Hash

Resolve apenas igualdade — não <, não ORDER BY, não prefix-match. O ganho sobre B-tree é menor do que parece (B-tree em coluna inteira faz lookup com poucas páginas extras), e perde em flexibilidade. Postgres só tornou hash index crash-safe na versão 10 (2017); antes disso era praticamente inutilizável. Hoje é raro ser a escolha — use B-tree como default e considere hash apenas quando profile mostrar ganho claro em coluna de igualdade pura, não-ordenada e nunca usada em ordenação.

BRIN — Block Range INdex

Para tabelas onde a coluna é fisicamente correlacionada à ordem de inserção (timestamps em log, IDs sequenciais), BRIN guarda apenas o min/max de cada bloco de 128 páginas. É microscópico em comparação com B-tree (kilobytes em vez de gigabytes) e excelente para queries de range em séries temporais. A condição é a correlação física: se as linhas de uma data específica estão espalhadas pela tabela toda, BRIN não ajuda. Em tabelas tipo eventos com milhões de linhas inseridas cronologicamente, BRIN sobre criado_em elimina sequential scan com fração mínima do espaço de um B-tree.

GiST — Generalized Search Tree

Estrutura genérica para tipos com geometria ou ordem parcial. Usado nativamente em índices espaciais (PostGIS), tipos RANGE, full-text search (tsvector) e EXCLUDE constraints. Quando você cria EXCLUDE USING GIST (sala_id WITH =, periodo WITH &&) para impedir reservas sobrepostas na mesma sala, GiST é o que faz a checagem ser rápida.

GIN — Generalized Inverted Index

Para colunas com vários valores por linha: arrays, JSONB, full-text tsvector. Inverte a relação — em vez de "linha → valores", guarda "valor → linhas que contêm". É o índice canônico para perguntas como "quais documentos contêm a palavra Codd?" ou "quais usuários têm tag 'premium' no JSONB de preferências?". Mais lento que B-tree para inserir; bem mais rápido para essas queries específicas.

SP-GiST — Space-Partitioned GiST

Versão para tipos cujo domínio é particionado naturalmente (IPs em árvores trie, palavras em radix tree). Pouco usado fora de casos muito específicos. Vale conhecer pelo nome.

-- Cada tipo no seu lugar
CREATE INDEX idx_ped_cliente_data    ON pedidos(cliente_id, criado_em);   -- B-tree
CREATE INDEX idx_ped_criado_em_brin  ON pedidos USING BRIN (criado_em);   -- BRIN
CREATE INDEX idx_doc_busca           ON documentos USING GIN (to_tsvector('portuguese', conteudo));
CREATE INDEX idx_prod_tags           ON produtos USING GIN (tags);        -- array
CREATE INDEX idx_prod_attrs          ON produtos USING GIN (atributos jsonb_path_ops);
CREATE INDEX idx_reserva_periodo     ON reservas USING GIST (periodo);    -- range

Índices parciais, expressões e cobertura

Índice parcial — só onde importa

Em uma tabela pedidos com 100M linhas, 99,9% têm status terminal ('entregue' ou 'cancelado') e nunca mais serão consultadas. Os 0,1% pendentes são consultados o tempo todo. Um índice parcial sobre o status pendente cobre 100k linhas em vez de 100M:

CREATE INDEX idx_pedidos_pendentes
ON pedidos (criado_em)
WHERE status = 'pendente';

O índice é minúsculo, escrita praticamente não custa, e qualquer query que filtre por status = 'pendente' ganha acesso indexado. Esse padrão — "índice parcial para o subconjunto quente" — é uma das técnicas mais altas de retorno em Postgres. Aparece pouco em livros introdutórios; aparece em todo Postgres bem-tunado em produção.

Índice expressão — quando você indexa cálculo

WHERE LOWER(email) = 'a@b.com' não usa índice comum sobre email. A função LOWER precisa ser conhecida pelo índice:

CREATE INDEX idx_email_lower ON usuarios (LOWER(email));

Alternativamente, no Postgres 12+ você pode usar generated columns e indexar a coluna gerada — fica mais legível. Para email, há também o tipo CITEXT que faz comparação case-insensitive nativa, eliminando a necessidade de LOWER espalhado pelo código.

Covering index — entregar tudo sem ir à tabela

Um índice geralmente aponta para a linha; o banco lê o índice e depois lê a tabela para pegar as colunas que precisa. Se a query pede só colunas que estão no índice, Postgres pode pular a leitura da tabela — index-only scan. Para incluir colunas no índice sem participar da chave de busca, use INCLUDE:

-- Query típica:
SELECT id, valor_total FROM pedidos WHERE cliente_id = ? AND criado_em >= ?;

-- Índice cobrindo tudo o que a query precisa:
CREATE INDEX idx_ped_cobrindo
ON pedidos (cliente_id, criado_em) INCLUDE (id, valor_total);

O ganho é real para queries quentes — eliminar metade dos acessos a disco. O custo é tamanho do índice maior e mais work em escrita. Use cirurgicamente, não em todo índice.

O custo invisível de cada índice

A pergunta que separa quem entende de quem só usa: quanto custa esse índice em escrita?. Cada INSERT precisa atualizar todos os índices da tabela. UPDATE em coluna indexada exige inserir nova versão e marcar a antiga como morta no índice. DELETE dispara limpeza (autovacuum) que precisa varrer índices.

Em sistemas write-heavy (logs, métricas, eventos), índice excessivo derruba write throughput. Casos reais: tabela com 28 índices que escrevia 200 inserts/s, depois de poda agressiva (cada índice examinado contra o catálogo de queries) ficou com 4 índices e passou a 5000 inserts/s. O ganho não veio de hardware — veio de escrever menos.

Postgres expõe estatísticas em pg_stat_user_indexes: idx_scan conta quantas vezes cada índice foi efetivamente usado. Índice com zero scans depois de semanas é candidato a remoção. Antes de remover em produção, considere se ele suporta uma query rara mas crítica (relatório mensal, operação manual). A remoção é segura — se errar, recriar custa tempo, não dado.

Quando o otimizador ignora seu índice

Você criou o índice, escreveu a query certa, e EXPLAIN mostra sequential scan. Frustrante. Os motivos típicos:

• Tabela pequena: para tabelas de poucos milhares de linhas, sequential scan é mais barato que descer índice. Postgres sabe disso. Não é bug — é otimização.
• Função na coluna: WHERE LOWER(email) = ? com índice em email não funciona. Precisa de índice sobre a expressão.
• Tipos mismatch: WHERE id_texto = 42 onde id_texto é VARCHAR exige cast que invalida o índice. Use o tipo certo no parâmetro.
• Estatísticas desatualizadas: depois de bulk load, ANALYZE tabela dá ao otimizador a noção atualizada de cardinalidade. Sem isso, planos podem ir para o sequencial errado.
• Seletividade baixa: query retornaria 30% das linhas. Postgres calcula que ler índice + ir à tabela 30% das vezes é mais caro que sequential scan. Isso é correto. A solução não é forçar índice; é repensar a query (talvez agregação, talvez filtro adicional).
• OR entre colunas indexadas: WHERE a=? OR b=? com índices separados em a e b às vezes vira sequencial. Considere índice composto, BitmapOr, ou reescrever como UNION.

Índices nas três linguagens — definição e introspecção

A definição de índices fica no banco; o que muda entre ecossistemas é como se declara, se versiona, e como se inspeciona em runtime.

protected override void OnModelCreating(ModelBuilder b) {
    b.Entity<Pedido>()
        .HasIndex(p => new { p.ClienteId, p.CriadoEm })
        .HasDatabaseName("idx_ped_cliente_data");

    // Índice parcial (Postgres-specific extension):
    b.Entity<Pedido>()
        .HasIndex(p => p.CriadoEm)
        .HasFilter("status = 'pendente'")
        .HasDatabaseName("idx_pedidos_pendentes");

    // Covering index:
    b.Entity<Pedido>()
        .HasIndex(p => new { p.ClienteId, p.CriadoEm })
        .IncludeProperties(p => new { p.Id, p.ValorTotal });
}

// Inspeção: dotnet ef migrations script gera DDL completo
// pg_stat_user_indexes consultável via FromSqlRaw para diagnóstico

from sqlalchemy import Index, text

class Pedido(Base):
    __tablename__ = "pedidos"
    __table_args__ = (
        Index("idx_ped_cliente_data", "cliente_id", "criado_em"),
        Index("idx_pedidos_pendentes", "criado_em",
              postgresql_where=text("status = 'pendente'")),
        Index("idx_ped_cobrindo", "cliente_id", "criado_em",
              postgresql_include=["id", "valor_total"]),
    )

# Para GIN sobre JSONB:
Index("idx_prod_attrs", Produto.atributos,
      postgresql_using="gin",
      postgresql_ops={"atributos": "jsonb_path_ops"})

# Inspeção em runtime:
# SELECT * FROM pg_stat_user_indexes WHERE schemaname = 'public';

-- 0007_add_indices.up.sql
CREATE INDEX CONCURRENTLY idx_ped_cliente_data
    ON pedidos (cliente_id, criado_em);

CREATE INDEX CONCURRENTLY idx_pedidos_pendentes
    ON pedidos (criado_em) WHERE status = 'pendente';

CREATE INDEX CONCURRENTLY idx_prod_attrs
    ON produtos USING GIN (atributos jsonb_path_ops);

-- 0007_add_indices.down.sql
DROP INDEX CONCURRENTLY IF EXISTS idx_ped_cliente_data;
DROP INDEX CONCURRENTLY IF EXISTS idx_pedidos_pendentes;
DROP INDEX CONCURRENTLY IF EXISTS idx_prod_attrs;

// Diagnóstico em runtime:
const idxStatsQuery = `
  SELECT indexrelname, idx_scan, idx_tup_read
  FROM pg_stat_user_indexes
  WHERE schemaname = 'public'
  ORDER BY idx_scan DESC`;

rows, _ := pool.Query(ctx, idxStatsQuery)

Manutenção — bloat, REINDEX, VACUUM

Postgres usa MVCC: UPDATE não muda a linha existente, cria nova versão e marca a antiga como morta. Linhas mortas acumulam até autovacuum limpá-las. Em índices, esse acúmulo é "bloat" — folhas com referências mortas, ou não usadas, que aumentam tamanho do índice e degradam performance lentamente.

Sintomas: o índice cresce muito além do que faria sentido pelo número de linhas; pg_stat_user_indexes mostra muito idx_tup_read para pouco idx_tup_fetch. O remédio é REINDEX CONCURRENTLY idx_nome — recria o índice em background sem bloquear escrita. Postgres 12+ tornou REINDEX CONCURRENTLY tabela inteira possível, e a partir do 14 várias situações foram melhoradas. Em sistemas com muito UPDATE em colunas indexadas, schedule de reindex periódico vale o investimento.

A extensão pgstattuple mede bloat exato; ferramentas como pgmetrics, pg_repack e pg_squeeze automatizam manutenção sem janela.

Heurísticas de criação — um framework

Quando você se pega criando muitos índices, vale ter um critério. Um framework prático:

1. Comece pelas queries. Liste as 10 queries mais frequentes do sistema (ferramentas como pg_stat_statements contam por você). São essas que precisam de índice — não a suposição do que será consultado.
2. Cada query → uma estratégia. Para cada query lenta, decida: B-tree em quais colunas, em que ordem, com filtro parcial se aplicável, com INCLUDE para virar index-only.
3. Combine quando possível. Antes de criar índice novo, veja se algum existente pode ser estendido. Dois índices (a) e (a,b) normalmente significam que o primeiro é redundante.
4. Meça antes e depois. EXPLAIN ANALYZE antes do índice; depois. Salve no commit ou ADR. Ganho real e custo de escrita ficam documentados.
5. Revise periodicamente. Trimestre ou semestre, olhe pg_stat_user_indexes. Índices não usados viram dívida silenciosa.

Como praticar

1. Carregue 1M de linhas em uma tabela e meça SELECT * FROM tabela WHERE coluna = ? sem índice e com índice. Faça o mesmo para queries com range, com função, com OR. Anote os tempos. Veja com seus olhos.
2. Audite os índices de um sistema seu. Rode SELECT indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan. Olhe os com idx_scan = 0. Investigue cada um — algum é dispensável? Documente o porquê de cada um manter ou ir.
3. Crie um índice parcial para um caso real do seu sistema (status quente, usuários ativos, registros não-arquivados). Compare o tamanho antes e depois com \di+ no psql. O ganho geralmente surpreende.