Índices de banco de dados

Por que acelera

Sem índice, o banco lê a tabela inteira (seq scan) para achar as linhas do WHERE. Com um índice em árvore B — o padrão no PostgreSQL e no MySQL — as chaves ficam ordenadas e a busca custa O(log n): de cada nó se desce para o filho certo até a folha, que aponta para a linha. A ordem também serve para ORDER BY, faixas (BETWEEN, >) e prefixos (LIKE 'abc%').

Tipos que valem conhecer

• Árvore B: igualdade, faixas e ordenação; serve para quase tudo.
• Composto (cliente_id, criado_em): vale para filtros que usam as colunas da esquerda para a direita; um filtro só por criado_em não aproveita.
• De cobertura (INCLUDE): a consulta é respondida só pelo índice, sem ir à tabela.
• Parcial (WHERE ativo): indexa só as linhas que importam e fica pequeno.
• GIN: arrays, jsonb e busca de texto; hash: só igualdade.

Quando o índice não é usado

• Função sobre a coluna: WHERE lower(email) = ? só usa um índice criado sobre lower(email).
• LIKE '%abc' (curinga no começo) não aproveita a ordem.
• Filtro pouco seletivo: se metade da tabela atende, ler tudo é mais barato e o planejador ignora o índice — e está certo.
• Tipos diferentes na comparação (texto contra número) forçam conversão.

Na dúvida, rode EXPLAIN ANALYZE e veja se aparece Index Scan e quantas linhas foram lidas.

O custo

Cada índice é uma estrutura a mais para atualizar em todo INSERT, UPDATE e DELETE, e ocupa disco e memória. Crie índices para as consultas que existem de verdade (veja as mais lentas no pg_stat_statements), remova os que nunca são usados e, em tabelas grandes, crie com CREATE INDEX CONCURRENTLY para não travar as escritas.