Como Funcionam os Índices
Um índice é uma estrutura secundária que permite ao PostgreSQL encontrar linhas sem ler todas as linhas de uma tabela.
Sem um, responder a WHERE email = 'ada@example.com' significa escanear a tabela inteira e verificar cada linha, o que é aceitável para mil linhas e doloroso para cem milhões.
Esta página constrói o modelo mental por trás da indexação antes de você recorrer a um método de acesso específico como B-tree, GIN, GiST ou BRIN nas outras páginas desta seção.
Acertar este modelo é importante porque os índices não são gratuitos, e a verdadeira habilidade é saber quando a aceleração de leitura vale o custo de escrita.
Resumo
- Um índice troca custo de tempo de escrita e armazenamento por velocidade de tempo de leitura, mantendo uma estrutura separada e pesquisável ao lado da tabela.
- Por Que Importa: Um índice ausente transforma uma consulta de milissegundos em uma varredura completa da tabela, e um índice redundante taxa silenciosamente cada escrita para sempre.
- Conceitos Chave: método de acesso, seletividade, estimativa de custo do planejador, mapa de visibilidade, amplificação de escrita, inchaço de índice (bloat).
- Quando Usar: Adicione um índice quando uma consulta filtra, junta ou ordena por uma coluna e a tabela é grande o suficiente para que uma varredura seja cara.
- Limitações / Trocas: Cada índice retarda inserções e atualizações, consome espaço em disco e precisa de manutenção como
REINDEXou atenção do autovacuum ao longo do tempo. - Tópicos Relacionados: Índices B-tree, GIN e GiST, BRIN, varreduras somente de índice (index-only scans), índices parciais e de cobertura (covering indexes).
Fundamentos
O tipo de índice padrão e mais comum no PostgreSQL é a B-tree, uma estrutura de árvore balanceada que mantém os valores em ordem classificada.
Uma B-tree responde eficientemente a consultas de igualdade, consultas de intervalo e recuperação classificada, o que cobre a grande maioria das cláusulas WHERE e ORDER BY do dia a dia.
O PostgreSQL também oferece outros métodos de acesso criados para formatos de dados que uma B-tree lida mal: GIN para valores que contêm muitos elementos como arrays e busca de texto completo, GiST para consultas geométricas e de sobreposição de intervalos, e BRIN para tabelas muito grandes onde a ordem física das linhas já se correlaciona com o valor de uma coluna.
Pense em um índice da mesma forma que pensaria no índice no final de um livro didático: ele não contém o conteúdo em si, ele contém ponteiros classificados de volta para as páginas onde esse conteúdo vive.
Esse ponteiro, no caso do PostgreSQL, é um identificador de tupla (TID), um endereço físico de uma versão de linha no disco.
Um índice B-tree simples em uma coluna de e-mail se parece com isto na prática.
CREATE INDEX customers_email_idx ON app.customers (email);Após esta instrução, o PostgreSQL mantém uma estrutura classificada de valores de e-mail ao lado da tabela, e uma consulta que filtra por e-mail pode pular quase diretamente para as linhas correspondentes em vez de ler a tabela inteira.
Seletividade é o conceito que determina se esse índice realmente ajuda: uma consulta que corresponde a uma linha em um milhão é altamente seletiva e se beneficia enormemente de um índice, enquanto uma consulta que corresponde a metade da tabela ganha pouco porque a maior parte da tabela seria tocada de qualquer maneira.
Mecânicas e Interações
Quando o PostgreSQL recebe uma consulta, o planejador não usa simplesmente um índice porque um tecnicamente se aplica ao predicado.
Ele estima o custo de cada plano viável, incluindo uma varredura sequencial, uma varredura de índice e uma varredura de índice de bitmap, usando estatísticas de tabela e coluna coletadas por ANALYZE.
Para um predicado de baixa seletividade, uma varredura sequencial pode ser mais barata do que uma varredura de índice, porque as consultas de acesso aleatório através de um índice custam mais por linha do que o índice economiza ao pular linhas não correspondentes.
É por isso que forçar o uso de índices raramente é o instinto certo, e por que estatísticas desatualizadas após uma carga em massa são uma causa comum de planos subitamente ruins.
Uma varredura de índice de bitmap é o meio-termo do planejador: ele usa o índice para construir um bitmap em memória das localizações de linhas correspondentes, classifica esse bitmap por página física e, em seguida, lê a tabela em ordem de página em vez de pular aleatoriamente.
Índices compostos, multicolunares, adicionam outra camada de nuance, pois a ordem das colunas dentro do índice determina quais formatos de consulta podem usá-lo.
Um índice em (account_id, created_at) atende a consultas que filtram apenas por account_id ou por ambas as colunas juntas, mas geralmente não pode atender a uma consulta que filtra apenas por created_at, porque a coluna principal controla a ordem de classificação que o índice realmente armazena.
Cada índice também tem um custo do lado da escrita que é fácil de subestimar.
INSERT INTO app.customers (email) VALUES ('new@example.com');
-- também atualiza customers_email_idx, mais o índice da chave primáriaCada inserção, atualização ou exclusão deve manter todos os índices dessa tabela, não apenas a chave primária, então uma tabela com oito índices paga esse custo oito vezes em cada escrita.
A otimização Heap-Only Tuple (HOT) do PostgreSQL pode evitar a atualização de índices quando uma atualização altera apenas colunas que nenhum índice cobre e a nova versão da linha cabe na mesma página, é por isso que manter colunas frequentemente atualizadas fora de índices desnecessários tem um benefício real e mensurável.
O mapa de visibilidade, uma estrutura compacta por tabela que rastreia quais páginas contêm apenas linhas visíveis para todas as transações, é o que permite que uma varredura somente de índice (index-only scan) responda a uma consulta diretamente do índice sem tocar na tabela, desde que todas as colunas que a consulta precisa estejam presentes no índice.
Considerações Avançadas e Aplicações
Em escala, a estratégia de índice se torna tanto uma questão de subtração quanto de adição.
pg_stat_user_indexes expõe idx_scan, uma contagem em execução de quantas vezes cada índice foi realmente usado, e um índice com zero varreduras ao longo de semanas de tráfego de produção é um forte candidato à remoção.
CREATE INDEX CONCURRENTLY existe especificamente porque uma construção de índice normal bloqueia escritas durante sua duração, e em uma tabela grande e ocupada esse bloqueio pode significar tempo de inatividade real.
A variante concorrente constrói o índice em segundo plano ao custo de uma construção mais lenta e a possibilidade de um índice INVALID se a construção for interrompida, que então precisa ser descartada e retentada.
A escolha entre métodos de acesso é fundamentalmente uma questão de como seus predicados se parecem, não uma questão de um tipo ser geralmente melhor do que outro.
| Método de Acesso | Força | Fraqueza | Melhor Ajuste |
|---|---|---|---|
| B-tree | Igualdade, intervalo e recuperação classificada; o padrão seguro | Nenhuma ajuda para predicados de contenção ou sobreposição | A maioria das cláusulas de filtragem, junções e ORDER BY de OLTP |
| GIN | Consultas rápidas dentro de valores compostos como arrays, jsonb e vetores de texto completo | Escritas mais lentas e maiores que B-tree | Busca de texto completo, contenção de array, consultas de chave jsonb |
| GiST | Suporta predicados de vizinho mais próximo e de sobreposição através de uma árvore flexível | Consultas são aproximadas e refinadas, geralmente mais lentas que B-tree para igualdade simples | Dados geométricos, sobreposição de intervalos, consultas espaciais PostGIS |
| BRIN | Tamanho minúsculo do índice em relação ao tamanho da tabela | Só é eficaz quando a ordem física das linhas se correlaciona com o valor indexado | Tabelas somente de adição (append-only), ordenadas por tempo, com dezenas de milhões de linhas ou mais |
Varreduras somente de índice (index-only scans) merecem atenção especial como técnica de escalonamento, pois adicionar colunas não-chave com INCLUDE pode permitir que uma consulta seja respondida inteiramente a partir do índice quando o mapa de visibilidade está atualizado, eliminando uma consulta à tabela por linha.
Índices parciais levam a seletividade adiante indexando apenas o subconjunto de linhas que uma carga de trabalho realmente consulta, como indexar apenas WHERE deleted_at IS NULL, o que mantém o índice pequeno e rápido, mesmo que a tabela inteira cresça com linhas históricas que as consultas raramente tocam.
A observabilidade em torno de índices vale a pena ser incorporada às operações rotineiras, pois rastrear o crescimento do tamanho do índice, a taxa de inchaço e as contagens de varredura ao longo do tempo detecta tanto índices ausentes, visíveis como consultas lentas, quanto redundantes, visíveis como sobrecarga de escrita sem benefício de leitura correspondente.
Concepções Equivocadas Comuns
- "Mais índices sempre tornam as consultas mais rápidas." Cada índice adicional adiciona custo de tempo de escrita e armazenamento, e um índice que o planejador nunca escolhe usar é sobrecarga pura sem benefício compensatório.
- "O planejador sempre usará um índice se um existir na coluna filtrada." O planejador compara os custos estimados em todos os planos viáveis, e uma varredura sequencial realmente vence para predicados de baixa seletividade ou tabelas pequenas.
- "Um índice composto em (a, b) também atende a consultas que filtram apenas em b." A coluna principal define a ordem de classificação do índice, portanto, uma consulta que pula a coluna principal geralmente não pode usar esse índice de forma eficiente.
- "Varreduras somente de índice sempre evitam tocar na tabela." Elas só o evitam quando o mapa de visibilidade confirma que as páginas relevantes não têm linhas invisíveis para a transação atual, o que requer vacuuming regular para se manter verdadeiro.
- "Reconstruir um índice sempre requer tempo de inatividade."
REINDEX CONCURRENTLYeCREATE INDEX CONCURRENTLYexistem precisamente para evitar o bloqueio exclusivo que uma reconstrução simples tomaria.
FAQs
Por que o PostgreSQL não usa meu índice, mesmo que ele exista na coluna em que estou filtrando?
- O planejador estima que uma varredura sequencial ou de bitmap é mais barata para esta consulta específica e distribuição de dados.
- Estatísticas desatualizadas após uma carga em massa podem fazer com que o planejador julgue mal a seletividade.
- Executar
ANALYZEna tabela geralmente resolve planos que parecem errados após grandes alterações de dados.
Qual é o custo real de adicionar um índice?
Cada inserção, atualização ou exclusão que toca em uma coluna indexada também deve atualizar esse índice, portanto, cada índice adicional adiciona latência de escrita incremental e uso de disco que escala com o tamanho da tabela.
O que é uma varredura de índice de bitmap e por que o planejador a usa?
Uma varredura de índice de bitmap constrói um mapa em memória das localizações de linhas correspondentes a partir do índice e, em seguida, lê a tabela em ordem de página física, o que é frequentemente mais barato do que uma varredura de índice simples quando um número moderado de linhas corresponde.
A ordem das colunas importa em um índice multicolunar?
Sim, a coluna principal determina a ordem de classificação do índice, portanto, uma consulta que filtra apenas em uma coluna não principal geralmente não pode usar o índice da mesma forma que uma consulta que filtra na coluna principal.
Como encontro índices que são seguros para remover?
- Consulte
pg_stat_user_indexese olhe para a colunaidx_scan. - Um índice com zero ou quase zero varreduras em uma janela de tempo representativa raramente está justificando seu custo de escrita.
- Confirme se a janela de observação cobre trabalhos periódicos, como relatórios de fim de mês, antes de remover qualquer coisa.
O que é uma varredura somente de índice e por que ela é mais rápida?
Uma varredura somente de índice responde a uma consulta usando apenas o índice, pulando completamente a consulta à tabela, o que é possível quando todas as colunas solicitadas estão presentes no índice e o mapa de visibilidade confirma que as páginas relevantes são totalmente visíveis.
Devo adicionar um índice para cada coluna em que filtro?
Não automaticamente, pois uma coluna com baixa seletividade, como um booleano, raramente se beneficia o suficiente de um índice para compensar seu custo de escrita, e índices multicolunares ou parciais geralmente superam uma pilha de índices de coluna única.
Por que eu usaria GIN ou GiST em vez de uma B-tree?
- Uma B-tree não pode responder eficientemente a "este array contém este valor" ou "estes dois intervalos se sobrepõem".
- GIN é construído para consultas de contenção dentro de valores compostos como arrays, jsonb e vetores de busca de texto completo.
- GiST generaliza para consultas de vizinho mais próximo e de sobreposição, é por isso que o PostGIS depende dele para dados espaciais.
O que BRIN troca para obter um tamanho de índice tão pequeno?
BRIN armazena intervalos de resumo por bloco em vez de uma estrutura classificada completa, então é dramaticamente menor que uma B-tree, mas só é útil quando a ordem física das linhas de uma tabela já se correlaciona com a coluna indexada, como uma tabela somente de adição ordenada por tempo de inserção.
Por que CREATE INDEX às vezes bloqueia escritas por muito tempo?
Um CREATE INDEX simples pega um bloqueio que impede escritas concorrentes na tabela durante a construção, é por isso que CREATE INDEX CONCURRENTLY existe como uma alternativa mais lenta, mas não bloqueante, para tabelas de produção.
O que acontece se a construção de um índice CONCURRENTLY falhar no meio do caminho?
O índice é deixado em um estado INVALID, o que significa que consome espaço e sobrecarga de escrita, mas nunca é usado pelo planejador, portanto, precisa ser descartado e reconstruído.
Um índice único é o mesmo que uma restrição única (unique constraint)?
Uma restrição única é suportada por um índice único internamente, então funcionalmente elas impõem a mesma regra, embora uma restrição também apareça explicitamente no catálogo de restrições da tabela para fins de documentação e ferramentas.
Como o mapa de visibilidade se relaciona com a indexação?
O mapa de visibilidade rastreia quais páginas da tabela contêm apenas linhas visíveis para todas as transações, e é o que permite que uma varredura somente de índice pule completamente a tabela, então uma tabela que raramente é vacuumed perde muito do benefício das varreduras somente de índice.
Relacionados
- Noções Básicas de Índices - exemplos práticos de criação e inspeção de índices
- Índices B-tree - regras de igualdade, intervalo e ordenação multicolunar em profundidade
- GIN & GiST - métodos de acesso para predicados de contenção e sobreposição
- BRIN - índices de intervalo de blocos para tabelas muito grandes e naturalmente ordenadas
- Varreduras Somente de Índice - como o mapa de visibilidade habilita leituras sem tabela
- Índices Parciais e de Cobertura - encolhendo índices para as linhas que importam
Versões do Stack: Esta página foi escrita para PostgreSQL 18.4 (principal estável 18, linha de manutenção 17 também suportada).