- Os índices do PostgreSQL são estruturas centrais para acelerar o acesso aos dados, reduzindo a quantidade de dados que precisa ser lida do disco e melhorando o desempenho das consultas
- Os índices são oferecidos em vários formatos, como Btree, Hash, BRIN, GIN, GiST e SP-GiST, cada um otimizado para diferentes características de dados e padrões de consulta
- Os índices também trazem vários custos, como espaço em disco, desempenho de escrita, complexidade do planejador de consultas e uso de memória
- Recursos avançados como índices parciais, índices multicoluna, índices de cobertura e índices por expressão podem maximizar a eficiência em situações específicas
- A escolha e o gerenciamento adequados dos índices são destacados como elementos centrais da otimização de desempenho no PostgreSQL
Conceitos básicos de índices
- Um índice é uma estrutura que aumenta a velocidade das consultas ao reduzir a quantidade de dados lidos do disco pelo banco de dados
- Chave primária, chave única e restrições de exclusão também são implementadas por meio de índices
- Quando o resultado da consulta corresponde a menos de 15~20% da tabela inteira, o índice é eficaz; acima disso, um scan sequencial pode ser mais eficiente
- O PostgreSQL fornece por padrão 6 tipos de índice, e mais tipos podem ser usados por meio de extensões
- Cada índice conecta o valor da chave à posição dos dados correspondente (TID)
Estrutura de dados armazenada em disco
- As tabelas do PostgreSQL são armazenadas como arquivos heap, compostos por páginas de 8KB
- Cada linha (tuple) é armazenada sem uma ordem específica, e o endereço interno é identificado por ctid (current tuple id)
- Ex.:
(0,1) significa a primeira tuple da página 0
- O índice conecta essas posições do heap (
ctid) em uma estrutura em árvore para permitir buscas rápidas
Como os índices aceleram o acesso aos dados
- Sem índice, o PostgreSQL executa um scan sequencial lendo todas as páginas
- No exemplo de consulta, ao procurar
name='Ronaldo', são lidas 6272 páginas e o processo leva 265ms
- Ao adicionar um índice, a operação muda para Index Scan, lendo apenas 4 páginas e concluindo em 0.077ms
- O índice mapeia o valor ao
ctid, encontrando rapidamente apenas as linhas necessárias
- O tamanho do arquivo de índice pode ser semelhante ao tamanho da tabela (ex.: tabela de 30MB → índice de 30MB)
Fatores de custo dos índices
- Além de melhorar o desempenho, os índices também trazem vários custos adicionais
Espaço em disco
- Os índices ocupam espaço de armazenamento separado e podem ser maiores que a tabela
- Isso gera custo adicional em backup, replicação e recuperação de falhas
- A eficiência de espaço pode ser melhorada com índices parciais, índices multicoluna, BRIN etc.
Operações de escrita
- Em
UPDATE, INSERT e DELETE, se uma coluna indexada for alterada, ocorre sobrecarga de atualização do índice
Planejador de consultas
- Quanto mais índices houver, mais opções o planejador precisa considerar, aumentando o tempo de elaboração do plano de execução
Uso de memória
- As páginas de índice são carregadas no shared buffer e ficam em cache; quanto mais índices, maior a pressão sobre a memória
- Devido ao limite de tamanho dos nós btree, quanto maiores forem as colunas, maior será a profundidade da árvore
- Em ordenação, scans multicoluna, vacuum, reindex etc., também há uso adicional de work memory
Principais tipos de índice
Btree
- A estrutura de índice padrão do PostgreSQL, um índice de uso geral presente na maioria dos DBMS
- Oferece buscas rápidas com complexidade de tempo O(log n)
- Estrutura de árvore balanceada em que todos os nós folha têm a mesma profundidade
- É vantajoso para operações ORDER BY e JOIN, além de ser usado em restrições de chave primária e chave única
- Os nós internos armazenam ponteiros para nós inferiores, e os nós folha armazenam chaves e ponteiros para o heap
- Por meio de ponteiros para nós à esquerda e à direita, é possível fazer navegação bidirecional
Uso de múltiplos índices
- O PostgreSQL pode combinar vários índices com operações de bitmap AND/OR para tratar condições compostas
- Ex.: na condição
age=30 AND login_count=100, os bitmaps de dois índices são combinados
Índices multicoluna
- É possível agrupar várias colunas em um único índice para economizar espaço e melhorar a velocidade
- Porém, a ordem das colunas é importante, e o índice só pode ser usado para condições que coincidam a partir da esquerda
Índices parciais
- Indexam apenas determinadas linhas por meio de uma expressão condicional
- Reduzem o tamanho do índice, melhoram a adequação à RAM e aumentam a velocidade de consulta
- Ex.:
create index on rules(status) where status='enabled';
- São úteis quando a distribuição de valores é desequilibrada (
status <> 'TODO' etc.)
Índices de cobertura
- Se todas as colunas necessárias para a consulta estiverem incluídas no índice, é possível retornar o resultado sem acessar o heap (index-only scan)
create index abc_cov_idx on bar(a, b) including c;- São mais eficientes em espaço do que índices multicoluna
Índices por expressão
- Indexam não o valor da coluna em si, mas o resultado de uma função ou expressão
- Ex.:
CREATE INDEX idx_lower_name ON customers (lower(name));
- São úteis ao pesquisar por valores transformados, como
LOWER(name)
- Apenas funções imutáveis (immutable) podem ser usadas
Hash
- Índice baseado em uma estrutura de hashmap, com boa eficiência de espaço para strings longas ou UUIDs
- Armazena um código hash de 32 bits para reduzir o tamanho
- Suporta apenas comparação de igualdade (
=), e não permite ordenação nem índices multicoluna
- Com distribuição uniforme do hash, pode oferecer leitura mais rápida que Btree
- Segundo a documentação oficial, índices hash reduzem I/O em tabelas grandes por meio de acesso direto à página de bucket
BRIN (Block Range Index)
- Índice que armazena apenas os valores mínimo e máximo de cada intervalo de blocos
- É extremamente compacto e amigável ao cache
- Adequado para grandes volumes, dados append-only e séries temporais
- Se as linhas forem atualizadas com frequência, a eficiência cai por causa do armazenamento duplicado causado por MVCC
- Com a configuração
pages_per_range, é possível ajustar o trade-off entre precisão e tamanho
GIN (Generalized Inverted Index)
- Índice adequado para busca em dados compostos
- Dá suporte à busca de elementos específicos em textos, arrays, JSONB etc.
- Usa estratégias dedicadas (opclass) para cada tipo de dado
- Para JSON, recomenda-se usar colunas JSONB; para texto, usar com tsvector ou a extensão pg_trgm
GiST & SP-GiST
- A árvore de busca generalizada (GiST) e a árvore de particionamento espacial (SP-GiST) são frameworks para implementar índices de tipos de dados específicos
- O GiST oferece uma árvore balanceada, enquanto o SP-GiST suporta uma estrutura não balanceada
- São usados com geoinformação, inet, intervalos e vetores de texto
- O GIN é mais rápido em consultas, enquanto o GiST tem menor custo de construção e manutenção
- Em busca de texto completo, deve-se escolher entre os dois conforme os requisitos
Conclusão
- Os índices são centrais para a otimização de desempenho no PostgreSQL, e o equilíbrio entre ganho de leitura e custo de escrita/armazenamento é importante
- Ao escolher o tipo de índice de acordo com as características dos dados e o padrão de consulta, é possível obter uma operação de banco de dados rápida e eficiente
- Um bom projeto de índices é um elemento essencial para garantir a escalabilidade e a estabilidade de sistemas de grande porte
1 comentários
Comentários do Hacker News
A documentação oficial do PostgreSQL é realmente muito bem escrita e prazerosa de ler, então vale compartilhar
Documento introdutório sobre índices do PostgreSQL
A parte sobre índices de múltiplas colunas é quase igual ao que eu aprendi
Mas eu fiquei curioso se isso ainda continua valendo nas versões mais recentes do PostgreSQL
No passado, vi bitmap index scan sendo usado em uma consulta parecida com o terceiro exemplo, e desde então passei a repensar a “sabedoria convencional”
Como referência sobre índices, acho que o site e o livro Use The Index, Luke são um clássico que o time inteiro deveria ler
Nas versões anteriores isso até era possível, mas exigia um scan do índice inteiro, o que era ineficiente
Vídeo relacionado: link do YouTube
Acho que seria ótimo se o PostgreSQL tivesse suporte nativo a incremental view maintenance
É um conceito que, como os índices, se atualiza automaticamente quando os dados-base mudam, mas que não fica limitado a uma view específica e pode se aplicar a views arbitrárias
Existem muitos projetos relacionados, como Noria, Materialize, Apache Flink, GCP Continuous Queries, Spark Streaming Tables, Delta Tables, ClickHouse streaming tables, TimescaleDB, ksqlDB, StreamSQL etc.
No PostgreSQL, recentemente uma extensão chamada pg_ivm começou a tratar desse problema
A discussão sobre B-tree vs Hash índices é interessante
Muita gente acha que hash é melhor para colunas de ID, mas na prática o B-tree padrão é mais eficiente
Principalmente quando há inserção de valores quase sequenciais, a estrutura em árvore leva vantagem
Ainda assim, no post de blog mencionado desta vez, foi dito o contrário: que o hash venceu no benchmark
O timing deste texto foi ótimo
A regra da coluna líder em índices de múltiplas colunas sempre me confundiu, mas por causa do bitmap index scan ela deixou de ser tão crítica quanto antes
O recurso de skip scan do PostgreSQL 18 muda bastante o entendimento tradicional, então quem aprendeu com base em versões antigas precisa atualizar o modelo mental
Acho que é um texto realmente excelente como material sobre PostgreSQL
Sobre índices B-tree, já faz tempo que eu consulto com frequência o Use The Index, Luke
Acho leitura obrigatória
Vai muito além de um guia introdutório simples; é profundo, mas ainda assim fácil de ler desde que não entre na parte de estruturas internas
Gosto desse estilo de escrita simples e humilde
É boa essa forma de transmitir conhecimento de maneira direta