Índice, EXPLAIN e a query lenta

Quase toda query lenta que eu já investiguei caía em uma de três categorias: falta índice, o índice existe mas não pode ser usado, ou o índice é usado e ainda assim o banco vai à tabela buscar o resto.

As três têm diagnóstico e correção diferentes, e as três aparecem no EXPLAIN.

O que é um índice, por dentro#

Um índice de banco relacional é uma B+tree. Cada nó ocupa uma página de disco e guarda centenas de chaves com ponteiros. Com um fator de ramificação de umas trezentas, uma árvore de três níveis endereça vinte e sete milhões de registros.

Três acessos a disco no pior caso, e os dois primeiros níveis quase sempre estão em memória, então na prática é um.

A variante "mais" significa que os dados ficam só nas folhas, e as folhas são ligadas entre si numa lista. Isso deixa os nós internos mais enxutos e transforma varredura por faixa em caminhar numa lista encadeada.

É por isso que índice de banco é árvore e não hash: hash resolve igualdade em tempo constante e não resolve faixa nem ordenação. E faixa e ordenação são a maior parte das suas queries.

O custo do índice#

Índice acelera leitura e desacelera escrita, porque toda inserção, atualização e remoção precisa atualizar todas as árvores relevantes. Índice também ocupa disco e memória.

Consequência: índice não usado é puro custo. E quase todo banco de produção tem vários.

No Postgres, a consulta é direta:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Índices com zero varreduras desde o último reset das estatísticas são candidatos a remoção. Confira antes se não são de unicidade: esses têm outra função.

A ordem das colunas decide tudo#

Índice composto segue o princípio do dicionário telefônico: ordenado por sobrenome e depois por nome.

Você acha todos os "Silva" rapidamente. Você acha "Silva, João" rapidamente. Mas se você só sabe o nome "João", o dicionário não ajuda: teria que ler tudo.

Índice em (a, b) serve para:

• filtro em a
• filtro em a e b

Não serve para filtro apenas em b.

A regra prática para montar índice composto: coloque primeiro as colunas usadas com igualdade, depois a coluna usada com faixa, e por último as usadas só para ordenar.

O que impede o índice de ser usado#

Existem padrões que anulam o índice mesmo com ele criado. Os mais frequentes:

Função na coluna. WHERE upper(email) = 'X' não usa o índice em email. Solução: criar índice na expressão, ou normalizar na escrita.

LIKE com curinga no início. WHERE nome LIKE '%silva' não pode usar B-tree, porque a árvore é ordenada pelo começo da string. Para isso existe índice de trigrama ou busca textual.

Comparação entre tipos diferentes. Uma coluna varchar comparada com número força conversão e derruba o índice.

Baixa seletividade. Se a condição retorna metade da tabela, varrer a tabela é mais rápido que pular de índice para tabela milhares de vezes. O banco sabe disso e escolhe o Seq Scan de propósito. Nesse caso, o Seq Scan não é o problema.

Index only scan: a otimização subestimada#

Quando o banco usa um índice, ele normalmente encontra o ponteiro e vai à tabela buscar as colunas que faltam. São dois acessos.

Se o índice contiver todas as colunas de que a query precisa, o banco responde sem tocar na tabela. Isso é um index only scan, e é um acesso em vez de dois.

No Postgres, você consegue isso com INCLUDE:

CREATE INDEX idx_pedidos_cliente
ON pedidos (cliente_id, criado_em)
INCLUDE (status, valor);

As colunas do INCLUDE não participam da ordenação: só ficam armazenadas na folha. É a diferença entre pagar um acesso e pagar dois, em toda execução.

Na minha experiência, é a mudança de índice com melhor relação entre esforço e ganho que existe, e a menos aplicada.

Como ler um EXPLAIN ANALYZE#

Você quer olhar três coisas, nessa ordem.

Primeira: o tipo de acesso. Seq Scan numa tabela grande com filtro seletivo é sinal de índice faltando. Index Scan é bom. Index Only Scan é ótimo. Bitmap Heap Scan aparece quando muitas linhas casam e o banco decide ordenar os acessos ao disco, normalmente razoável.

Segunda: estimativa versus realidade. O plano mostra quantas linhas o banco esperava e quantas vieram. Se ele esperava dez e vieram cem mil, as estatísticas estão desatualizadas, e o plano escolhido foi ruim por falta de informação. Rode ANALYZE na tabela.

Terceira: onde está o tempo. Cada nó do plano mostra o tempo acumulado. Procure o nó que consome a maior fatia e trabalhe nele. Não otimize o resto.

E adicione BUFFERS ao EXPLAIN: ele mostra quantas páginas foram lidas do cache e quantas do disco. Uma query que lê muito do disco é candidata a caber melhor em memória ou a precisar de um índice mais enxuto.

O roteiro completo#

Quando uma query estiver lenta:

1. Rode EXPLAIN (ANALYZE, BUFFERS).
2. Se for Seq Scan com filtro seletivo → crie o índice, respeitando a ordem das colunas.
3. Se for Index Scan mas com muitos acessos à tabela → considere INCLUDE para virar Index Only Scan.
4. Se a estimativa estiver muito errada → rode ANALYZE e considere aumentar o alvo de estatística naquela coluna.
5. Se nada disso resolver → o problema pode não ser a query. Pode ser lock, pode ser I/O saturado, pode ser plano ruim por parâmetro. Aí você sobe um nível e vai para observabilidade.