Como funciona um índice de banco de dados, e quando adicionar um piora as coisas?

Um índice é uma estrutura ordenada separada, geralmente uma árvore B, mapeando valores de coluna para localizações de linha para o engine poder buscar em vez de varrer. Ele acelera leituras que filtram ou ordenam por essas colunas. Ele custa nas escritas, já que cada insert, update e delete tem que mantê-lo, e custa armazenamento. Colunas de baixa seletividade e índices que nada consulta costumam ser prejuízo líquido.

Um índice é essencialmente uma cópia ordenada de uma ou mais colunas com ponteiros de volta para as linhas, geralmente uma árvore B, para o engine poder fazer busca binária em vez de ler cada página. Isso transforma um filtro numa tabela grande de uma varredura sequencial em um punhado de leituras de página. O custo é que toda escrita tem que atualizar o índice também, então uma tabela com oito índices paga oito vezes no insert, e cargas em massa ficam bem mais lentas. A outra coisa que eu observo é a seletividade. Se uma coluna é um booleano que é verdadeiro em noventa por cento das linhas, o planner geralmente vai só varrer, porque acesso aleatório a tantas linhas custa mais do que ler sequencialmente. Tive um caso em que alguém adicionou um índice numa coluna de status com três valores e nada melhorou. O que de fato resolveu foi um índice composto em tenant id e created at, casando com o filtro e a ordenação, para o banco poder buscar e depois ler em ordem sem um passo separado de ordenação.