Índices e planos de execução: faça o otimizador trabalhar a seu favor

Todo motor relacional traz um otimizador que decide como executar a sua consulta, e todo otimizador é um modelo de custo alimentado por estatísticas. Quando uma consulta está lenta, a pergunta interessante não é “que índice devo criar?”, e sim “no que o otimizador acreditava, e onde ele errou?”. O plano de execução é o único lugar onde essa resposta está.

Este guia parte do princípio de que você já escreve predicados que o otimizador consegue aproveitar — nada de funções sobre a coluna indexada, nada de conversões implícitas, os hábitos abordados em boas práticas de SQL. O que vem a seguir é a camada de baixo: o que um índice é fisicamente, o que o torna utilizável e como ler um plano com atenção suficiente para agir. Os exemplos usam a sintaxe do PostgreSQL 17, com os equivalentes no Oracle indicados.

O que um B-tree consegue e não consegue fazer

Quase todo índice que você cria é uma árvore balanceada de chaves ordenadas. As páginas internas guardam chaves separadoras que direcionam a busca; as páginas folha guardam as chaves em ordem, cada uma com um ponteiro para a linha (um identificador de tupla no heap no PostgreSQL, um ROWID no Oracle). As páginas folha são encadeadas, então, depois que a busca chega ao destino, o motor pode andar para frente ou para trás sem voltar à raiz.

Essa estrutura permite exatamente quatro coisas, e entendê-las explica quase qualquer dúvida sobre índices:

1. Busca por igualdade. O motor desce até a primeira chave correspondente. O custo é logarítmico em relação ao número de linhas — três ou quatro leituras de página numa tabela grande.
2. Varredura por intervalo. O motor desce uma vez e depois percorre as folhas enquanto a chave ainda respeita o limite. >, <, BETWEEN e um LIKE 'abc%' por prefixo se reduzem todos a isso.
3. Ordenação. As folhas já estão ordenadas, então um índice pode entregar o ORDER BY sem etapa de sort, o que faz uma diferença enorme quando combinado com LIMIT.
4. Unicidade. É por meio de um índice único que o motor garante chaves primárias e constraints de unicidade.

O que ele não consegue é responder a nada que não comece num ponto conhecido da ordem das chaves. LIKE '%invoice%' não tem prefixo inicial. Nem um predicado sobre a segunda coluna de um índice composto quando a primeira não está restrita — embora o PostgreSQL 18 tenha amenizado isso com o skip scan, que sonda cada valor distinto da coluna inicial quando ela tem poucos valores distintos. Trate isso como rede de segurança, não como princípio de design.

Índices compostos: a ordem das colunas é o design

Um índice em (a, b, c) ordena as linhas por a, depois por b dentro de cada a igual, depois por c. A consequência: o índice serve para uma consulta que restringe um prefixo inicial dessas colunas. WHERE a = ? usa o índice, WHERE a = ? AND b = ? usa melhor ainda, e WHERE b = ? sozinho praticamente não consegue usar.

A regra que decorre disso vale a pena decorar: primeiro as colunas de igualdade, depois a coluna em que você faz varredura por intervalo ou ordena. Assim que a varredura encontra um predicado de intervalo, tudo o que vem depois no índice deixa de servir para posicionar a busca — serve apenas para filtrar as linhas que o intervalo retorna.

-- Query: recent unpaid invoices for one customer
SELECT id, issued_at, amount
FROM   invoice
WHERE  customer_id = $1 AND status = 'UNPAID' AND issued_at >= $2
ORDER  BY issued_at DESC
LIMIT  50;

-- Equalities first, range/sort column last
CREATE INDEX invoice_cust_status_issued
  ON invoice (customer_id, status, issued_at DESC);

Esse índice atende a consulta inteira com uma única varredura por intervalo e sem sort. Troque as colunas para (issued_at, customer_id, status) e o motor vai ter que percorrer todas as faturas desde $2 e filtrar, o que não faz diferença numa tabela pequena e é desastroso numa grande.

Um índice de cobertura vai um passo além: se o índice contém todas as colunas que a consulta usa, o motor nem chega a visitar a tabela. O PostgreSQL mostra isso como Index Only Scan, e isso só é possível quando o visibility map indica que as páginas envolvidas estão totalmente visíveis — mais um motivo pelo qual o autovacuum importa, como discutido na comparação entre Oracle e PostgreSQL. Colunas necessárias só na saída, nunca para filtrar ou ordenar, vão em INCLUDE em vez de na chave, o que mantém a árvore mais enxuta:

CREATE INDEX invoice_cust_issued_covering
  ON invoice (customer_id, issued_at DESC) INCLUDE (amount, status);

Seletividade e estatísticas

O otimizador escolhe entre uma varredura de índice e uma varredura sequencial com base na seletividade estimada: a fração de linhas que um predicado mantém. Um índice compensa quando essa fração é pequena, porque cada linha correspondente pode custar uma leitura de página aleatória. Quando a fração passa de alguns pontos percentuais de uma tabela grande, ler tudo sequencialmente sai mais barato. É por isso que uma coluna status com dois valores quase nunca se beneficia de um B-tree comum, e por isso ninguém deveria se surpreender quando um índice nela não é usado.

As estimativas de seletividade vêm das estatísticas coletadas pelo ANALYZE (PostgreSQL, executado automaticamente pelo autovacuum) ou pelo DBMS_STATS (Oracle): o número de valores distintos por coluna, a fração de nulos, uma lista dos valores mais comuns com suas frequências e um histograma do restante. Três situações as quebram:

• Estatísticas desatualizadas depois de uma carga em massa ou de um update em massa. A correção é rodar ANALYZE como última etapa de qualquer job de ETL.
• Colunas correlacionadas. O planejador assume independência, então WHERE city = 'Lyon' AND country = 'FR' é estimado multiplicando duas seletividades, embora a primeira implique a segunda, o que produz uma estimativa muito abaixo da real. A resposta do PostgreSQL são as estatísticas estendidas: CREATE STATISTICS city_country (dependencies, ndistinct) ON city, country FROM address;
• Distribuição assimétrica. Uma coluna em que um único valor cobre 90% das linhas precisa de um histograma e, no Oracle, se beneficia do adaptive cursor sharing, para que valores de bind diferentes possam receber planos diferentes.

Aumentar default_statistics_target (ou o equivalente por coluna) deixa os histogramas mais finos, ao custo de uma análise mais lenta. Faça isso para as poucas colunas com distribuições realmente difíceis, não globalmente.

Tipos de índice além do B-tree

Dois modificadores se aplicam a qualquer um deles. Um índice parcial armazena apenas as linhas que atendem a um predicado, o que é perfeito quando as consultas sempre miram um subconjunto pequeno: CREATE INDEX ON job (created_at) WHERE status = 'PENDING'; dá a você um índice minúsculo sobre uma tabela de fila, não importa quanto histórico concluído se acumule. Um índice de expressão armazena o resultado de uma função e devolve o uso de índice a um predicado que, de outra forma, não seria sargable: CREATE INDEX ON person (lower(email)); é usado por WHERE lower(email) = $1, desde que a consulta repita exatamente a mesma expressão.

O PostgreSQL também monta bitmap heap scans na hora: ele junta num bitmap em memória os identificadores de tupla correspondentes vindos de um ou mais índices, ordena-os pela localização física e depois lê o heap nessa ordem. É assim que dois índices separados de coluna única podem cooperar num AND ou num OR — de forma útil, mas nunca tão bem quanto um índice composto bem ordenado.

Como ler o EXPLAIN ANALYZE

Peça a versão medida, com buffers, e leia-a como uma árvore que é executada do nó mais interno para fora:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT c.name, sum(i.amount)
FROM   customer c
JOIN   invoice i ON i.customer_id = c.id
WHERE  i.issued_at >= date '2026-01-01'
GROUP  BY c.name;

HashAggregate  (cost=18422.10..18560.42 rows=11066 width=40)
               (actual time=214.883..218.014 rows=10871 loops=1)
  Group Key: c.name
  ->  Hash Join  (cost=612.00..17250.44 rows=234332 width=36)
                 (actual time=6.201..168.442 rows=233980 loops=1)
        Hash Cond: (i.customer_id = c.id)
        ->  Index Scan using invoice_issued_at_idx on invoice i
              (cost=0.43..14887.02 rows=234332 width=12)
              (actual time=0.038..92.117 rows=233980 loops=1)
              Index Cond: (issued_at >= '2026-01-01'::date)
              Buffers: shared hit=4210 read=8891
        ->  Hash  (cost=474.00..474.00 rows=11040 width=32)
                  (actual time=6.104..6.105 rows=11040 loops=1)
              ->  Seq Scan on customer c  (cost=0.00..474.00 rows=11040 width=32)
Planning Time: 0.412 ms
Execution Time: 219.774 ms

Leia nesta ordem:

1. Compare o rows= estimado com o rows= real em cada nó. Um fator de dez ou mais é o sinal que você procura, e o nó mais baixo em que ele aparece é a causa; tudo acima herda o erro. Aqui as estimativas são excelentes, então o formato do plano é confiável.
2. Multiplique por loops. Dentro de um nested loop, as contagens de linhas e os tempos reais são por iteração. Um nó que mostra 3 linhas e 0,2 ms com loops=50000 consumiu dez segundos.
3. Olhe os Buffers. shared hit veio do cache, read veio do sistema operacional e temp read/written significa que uma operação transbordou para o disco — geralmente um sort ou hash que passou do work_mem.
4. Diferencie Index Cond de Filter. Uma condição de índice restringe a varredura; um filtro é aplicado às linhas que a varredura já retornou. Rows Removed by Filter: 480000 significa que você buscou meio milhão de linhas para jogá-las fora.
5. Confira os tempos globais. Um tempo de planejamento alto em relação ao de execução sugere partições demais ou uma tabela com índices demais.

Os operadores de join contam sua própria história. O Nested Loop sonda a relação interna uma vez para cada linha externa, o que é ótimo quando o lado externo é minúsculo e a sondagem interna é indexada, e catastrófico quando a estimativa do lado externo estava errada. O Hash Join monta uma tabela hash com a entrada menor e passa a maior por ela — a escolha usual para equijoins grandes. O Merge Join consome os dois lados em ordem, o que é barato quando os índices já fornecem essa ordem e caro quando obriga a dois sorts. A saída de planos do Oracle nomeia os mesmos três formatos e acrescenta métodos de distribuição do HASH JOIN na execução paralela.

Quando o planejador erra

Planos ruins quase sempre remontam a uma estimativa ruim, e a lista de causas é curta:

• Uma estimativa de linhas errada, por estatísticas desatualizadas, colunas correlacionadas ou um predicado que o otimizador não consegue enxergar — uma chamada de função, um valor escondido atrás de um bind opaco ou um join sobre uma expressão.
• Sensibilidade a parâmetros. O plano genérico de um prepared statement pode ser montado para um valor médio e depois reaproveitado para um valor muito assimétrico. O PostgreSQL gera planos customizados nas primeiras execuções e pode passar depois para um genérico; definir plan_cache_mode = force_custom_plan na sessão ajuda a diagnosticar isso. O Oracle chama o mesmo fenômeno de bind peeking e o atenua com adaptive cursor sharing.
• Um índice faltando, ou um índice cuja ordem de colunas não combina com o predicado.
• Modelo de custo desajustado. O valor padrão de random_page_cost, 4.0, pressupõe discos rotativos. Em SSDs, baixá-lo para 1.1 desloca legitimamente o equilíbrio a favor das varreduras de índice.
• work_mem insuficiente, forçando um hash ou sort a transbordar para o disco; o plano está certo, mas a execução não.

Um ORM acrescenta uma camada de indireção, mas não muda nada disso: o SQL gerado continua tendo um plano, e o lazy loading continua transformando uma consulta em mil — veja Spring Data JPA para saber como buscar dados em conjunto.

O custo de um índice

Índices não são de graça, e a conta chega no caminho de escrita. Todo insert e todo delete atualizam todos os índices da tabela; um update mexe nos índices cujas colunas ele altera, e em todos se a linha não puder continuar na mesma página. Índices também ocupam cache que as páginas de dados usariam, e incham: um índice muito atualizado acumula entradas mortas até ser reconstruído. No PostgreSQL, REINDEX INDEX CONCURRENTLY reconstrói sem pegar um lock disruptivo, e CREATE INDEX CONCURRENTLY adiciona um índice a uma tabela em pleno uso ao custo de duas passadas pela tabela.

Antes de criar um índice, veja se dá para estender um que já existe — um índice em (customer_id) fica redundante assim que (customer_id, issued_at) existe. Antes de manter um, verifique se alguma coisa o usa:

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0
ORDER  BY pg_relation_size(indexrelid) DESC;

Zero scans desde o último reset de estatísticas, numa tabela que é consultada, significa que o índice é puro custo extra — a menos que sustente uma constraint. Projete bem o schema primeiro, como abordado em modelagem de banco de dados e normalização; índices se ajustam aos padrões de acesso, não resgatam um modelo quebrado.

Principais pontos

• Um B-tree atende igualdade, intervalos, ordenação e unicidade a partir de uma estrutura ordenada, e só consegue iniciar uma varredura a partir de um prefixo conhecido da sua chave.
• Num índice composto, coloque as colunas de igualdade primeiro e a coluna de intervalo ou ordenação por último; adicione com INCLUDE as colunas de saída que não são filtradas para permitir index-only scans.
• O otimizador decide com base na seletividade estimada, então estatísticas erradas — desatualizadas, correlacionadas ou assimétricas — são a causa mais comum de um plano ruim.
• Além dos B-trees, use GIN para dados multivalorados, BRIN para tabelas enormes correlacionadas com a ordem física, índices bitmap para data warehouses com leitura predominante e índices parciais ou de expressão para tornar barato um subconjunto pequeno.
• Leia o EXPLAIN ANALYZE do nó mais interno para fora, compare linhas estimadas com reais, multiplique por loops e fique de olho em linhas removidas por filtro e em transbordamentos para arquivos temporários.
• Todo índice cobra seu preço nas escritas e consome cache: estenda um índice existente em vez de criar uma quase duplicata, e remova o que o pg_stat_user_indexes mostra que ninguém usa.