Explicação em camadas

Camada 1, uma frase

Um índice é uma estrutura de dados extra que o banco mantém ao lado da tabela para encontrar linhas sem precisar ler todas.

O que simplifiquei aqui: disse "encontrar linhas", mas índice também serve para ordenar e para responder consultas sem tocar na tabela. A camada 5 volta nisso.

---

Camada 2, analogia

É o índice remissivo no fim de um livro. Sem ele, para achar todas as menções a "fotossíntese" você folheia as 400 páginas. Com ele, vai ao "F", lê "fotossíntese: 12, 87, 203" e pula direto.

O índice custa páginas a mais no livro, e se você reescrever um capítulo precisa refazê-lo. É por isso que índice não é grátis: ele ocupa disco e torna a escrita mais lenta, porque toda inserção precisa atualizar o índice também.

Onde a analogia quebra: o índice do livro está em ordem alfabética e você o percorre linearmente com os olhos. O banco usa uma árvore, que ele navega por saltos, a diferença é o que torna a busca rápida mesmo com bilhões de entradas.

O que simplifiquei aqui: sugeri que existe um tipo só de índice. Existem vários (B-tree, hash, GIN, GiST), e eles servem para perguntas diferentes.

---

Camada 3, como funciona

O índice mais comum é uma B-tree. Imagine assim:

1. No topo há um nó com algumas chaves de corte: "menor que 1000 vai à esquerda, entre 1000 e 5000 vai ao meio, acima de 5000 vai à direita".
2. Cada um desses caminhos leva a outro nó com o mesmo tipo de decisão, mais refinada.
3. Depois de 3 ou 4 saltos você chega numa folha, que contém a chave e um ponteiro para a posição física da linha na tabela.
4. O banco vai até essa posição e lê a linha.

Cada salto elimina a maior parte do que restava. É por isso que buscar em 1 bilhão de linhas custa cerca de 30 saltos, não 1 bilhão de comparações.

Quando você insere uma linha, o banco precisa inserir a chave na folha certa. Se a folha encher, ela se divide, e a divisão pode subir pela árvore. É esse trabalho que torna a escrita mais cara.

O que simplifiquei aqui: disse que a folha aponta para a linha e o banco "vai lá ler". Nem sempre, se todas as colunas que a consulta precisa já estão no índice, o banco para na folha e nunca toca na tabela. Isso se chama index-only scan, e é a diferença entre rápido e muito rápido.

---

Camada 4, formalização

CREATE INDEX idx_pedidos_cliente_data
  ON pedidos (cliente_id, criado_em DESC);

• (cliente_id, criado_em) é um índice composto. A ordem importa: a árvore é organizada primeiro por cliente_id, e só dentro de cada cliente_id por criado_em.
• Isso significa que ele serve para WHERE cliente_id = 7 e para WHERE cliente_id = 7 ORDER BY criado_em, mas não para WHERE criado_em > '2026-01-01' sozinho. É a regra do prefixo mais à esquerda, você não acha "todos os João" num índice ordenado por sobrenome e depois nome.
• Complexidade: busca em O(log n), onde n é o número de linhas. Varredura completa é O(n).
• Seletividade é a fração de linhas que o filtro devolve. Se WHERE ativo = true devolve 90% da tabela, o planejador ignora o índice e faz varredura completa, ler 90% via ponteiros dispersos é mais lento que ler tudo em sequência.

Ligando à camada 3: os "nós de decisão" são páginas internas da árvore; a "posição física" é o ctid no PostgreSQL ou o ponteiro da chave primária no InnoDB do MySQL.

O que simplifiquei aqui: falei como se o planejador sempre acertasse. Ele decide com base em estatísticas amostradas, que podem estar desatualizadas.

---

Camada 5, onde é mais sutil

• O planejador pode escolher errado. Ele estima quantas linhas o filtro devolve a partir de estatísticas coletadas periodicamente. Se a distribuição mudou muito desde a última coleta, ele estima mal e escolhe o plano ruim. Rodar ANALYZE conserta mais consultas lentas do que criar índice novo.
• Índice demais deixa a escrita inviável. Cada INSERT atualiza todos os índices da tabela. Uma tabela com 12 índices tem escrita várias vezes mais cara. Em tabela de log com escrita intensa, isso derruba o sistema.
• Índice não usado é puro custo. Ocupa disco, torna escrita lenta, e não acelera nada. Vale auditar: no PostgreSQL, pg_stat_user_indexes mostra quantas vezes cada índice foi usado.
• A camada 2 escondeu a ordenação. Índice não serve só para filtrar, ele também entrega as linhas já ordenadas. Um ORDER BY que casa com o índice elimina a etapa de ordenação inteira, que costuma ser o gargalo real de consultas com LIMIT.
• Índice parcial e por expressão existem. CREATE INDEX ... WHERE ativo = true indexa só o subconjunto que você consulta, menor, mais rápido, mais barato de manter.
• A discussão viva na área é sobre estruturas alternativas: LSM-trees (usadas em RocksDB, Cassandra) invertem o trade-off, tornando escrita muito barata e leitura mais cara. Não existe "melhor índice", existe o que casa com a proporção entre leitura e escrita da sua carga.

Para sua decisão: antes de criar o índice, rode EXPLAIN ANALYZE na consulta lenta. Se aparecer Seq Scan numa tabela grande com filtro seletivo, o índice provavelmente ajuda. Se aparecer Seq Scan porque o filtro devolve metade da tabela, o índice não vai ser usado e o problema é outro.