Decisão: qual tipo de índice?

O tipo de índice correto depende do padrão de acesso da consulta (igualdade, intervalo ou busca textual/JSON), da cardinalidade da coluna e do equilíbrio entre leitura e gravação. O índice errado desperdiça espaço e ainda deixa as gravações mais lentas. Esta árvore parte do formato da consulta e orienta você entre as opções B-tree, Hash, Bitmap, GIN/GiST, índice parcial e covering index, e lembra que a ordem das colunas é crítica em um índice composto.

Como a sua consulta usa esta coluna?

O padrão de acesso é o fator mais decisivo na escolha de um índice. Você apenas compara a coluna com '=', faz intervalo/ordenação (>, <, BETWEEN, ORDER BY), ou busca texto completo, um campo JSON, dados geométricos/arrays? Comece por aqui; não escolha um tipo de índice até que a necessidade esteja clara.

• Igualdade e intervalo/ordenação combinados (=, >, <, BETWEEN, ORDER BY)
• Apenas igualdade (=), sem intervalo nem ordenação
• Busca full-text, em campo JSON/JSONB, array ou geométrica
• A consulta sempre visa o mesmo pequeno subconjunto de linhas (ex.: WHERE status='active')

Qual é a cardinalidade da coluna (número de valores distintos)?

Se houver necessidade de intervalo/ordenação, o padrão é B-tree; porém, para colunas com pouquíssimos valores distintos (baixa cardinalidade: gênero, status, sim/não) em um data warehouse com muitas leituras, um índice Bitmap pode ser melhor. A coluna tem muitos valores únicos ou poucos valores que se repetem?

• Alta cardinalidade (muitos valores únicos) ou carga OLTP
• Baixa cardinalidade e muitas leituras / data warehouse (DWH)

A consulta lê apenas as colunas do índice?

O padrão de acesso é adequado a um B-tree. Uma última otimização: se todas as colunas que a consulta retorna e filtra estiverem no índice, o engine pode responder apenas pelo índice, sem visitar a tabela (index-only / covering scan). Sua consulta lê poucas colunas ou precisa da maior parte da linha?

• Sim, poucas colunas; pode ser respondida sem visitar a tabela
• Não, o acesso padrão basta

Recomendação: B-tree (padrão)

Um B-tree é uma estrutura de árvore balanceada que suporta uma ampla gama de padrões de acesso: igualdade, intervalo, ordenação (ORDER BY), correspondência de prefixo com LIKE 'abc%' e MIN/MAX. É o tipo de índice padrão na maioria dos engines relacionais e o ponto de partida certo em caso de dúvida.

Recomendação: índice Hash (apenas igualdade)

Um índice hash armazena o hash do valor da coluna e é muito rápido apenas para buscas de igualdade exata ('='). Não suporta buscas por intervalo, ordenação nem prefixo, por isso seu caso de uso é restrito.

Recomendação: índice Bitmap (baixa cardinalidade / DWH)

Um índice bitmap mantém um array de bits para cada valor distinto e combina muito rapidamente várias condições de baixa cardinalidade com AND/OR bit a bit. É forte em cargas com muitas leituras, como data warehousing e relatórios.

Recomendação: GIN / GiST (texto, JSON, array, geometria)

O PostgreSQL tem famílias de índices especializadas para tipos de acesso que um B-tree padrão não consegue tratar. O GIN aponta para muitos componentes dentro de um valor (palavras, chaves JSONB, elementos de array); o GiST se adequa a consultas geométricas/de intervalo/de vizinho mais próximo.

Recomendação: índice parcial / filtrado

Um índice parcial indexa apenas as linhas que satisfazem uma condição (WHERE ...) em vez da tabela inteira. Se as consultas sempre visam o mesmo pequeno subconjunto, o índice fica muito menor e mais rápido, e o custo de gravação cai.

Recomendação: covering index (INCLUDE)

Um covering index contém todas as colunas de que a consulta precisa, permitindo que o engine responda apenas pelo índice, sem visitar a tabela (heap) (index-only scan). Oferece um ganho significativo de velocidade para consultas executadas com frequência que retornam poucas colunas.