Index Design In Detail
Um índice no PostgreSQL é uma estrutura separada e ordenada que fica ao lado de uma tabela e oferece ao planejador um caminho mais rápido para linhas específicas do que ler a tabela inteira.
Projetar índices de forma eficaz é menos sobre memorizar a sintaxe CREATE INDEX e mais sobre entender o que um índice realmente é internamente, como o planejador decide se o usará e qual o custo para você toda vez que uma linha muda.
Esta página constrói esse modelo mental para que as páginas mais táticas desta seção, sobre ordenação de colunas, redundância e reindexação, façam sentido como consequências da mesma estrutura subjacente, em vez de regras separadas para memorizar.
Resumo
- Um índice é uma estrutura ordenada e mantida separadamente que permite ao planejador localizar linhas correspondentes sem ler a tabela inteira.
- Por que Importa: Sem um índice, cada consulta filtrada custa uma varredura completa da tabela, e esse custo cresce linearmente com o tamanho da tabela.
- Conceitos Chave: B-tree, index scan (varredura de índice), leftmost prefix (prefixo mais à esquerda), heap access (acesso ao heap), index-only scan (varredura apenas por índice), visibility map (mapa de visibilidade).
- Quando Usar: Projetar um novo índice requer conhecer os formatos de consulta que ele deve atender, não apenas quais colunas "parecem importantes".
- Limitações / Compromissos: Cada índice adiciona sobrecarga de gravação, armazenamento e trabalho de vácuo, e um índice não utilizado ou redundante é custo puro sem benefício.
- Tópicos Relacionados: estatísticas do planejador de consultas, leitura de planos EXPLAIN, autovacuum e bloat, detalhes internos do método de acesso.
Fundamentos
O tipo de índice padrão e mais comum no PostgreSQL é a B-tree, uma estrutura de árvore balanceada que mantém suas chaves em ordem classificada e permite ao planejador encontrar qualquer chave, ou um intervalo contíguo de chaves, em um pequeno número limitado de leituras de página.
Pense nisso da mesma forma que pensaria em uma lista telefônica classificada por sobrenome: encontrar "Silva" não requer a leitura de todas as páginas, porque a estrutura classificada permite que você salte diretamente para o bairro certo e escaneie um pequeno intervalo local.
Essa analogia também explica a regra mais importante de índices B-tree compostos: eles só ajudam você a localizar eficientemente valores por um prefixo mais à esquerda de suas colunas-chave, em ordem.
Um índice em (sobrenome, primeiro_nome) atende perfeitamente a uma consulta em sobrenome isoladamente, da mesma forma que uma lista telefônica classificada por sobrenome ainda permite que você encontre todos os Silvas, mas não pode atender eficientemente a uma consulta apenas por primeiro_nome, porque os primeiros nomes estão espalhados pela ordem classificada.
Uma entrada de índice simples armazena os valores das colunas indexadas mais um ponteiro, chamado TID, de volta à localização física da linha no heap da tabela.
Por causa desse ponteiro, a maioria das varreduras de índice são, na verdade, operações de duas etapas: o planejador percorre o índice para encontrar TIDs correspondentes, depois segue cada ponteiro de volta ao heap para buscar o restante da linha.
Essa segunda etapa, chamada acesso ao heap, é onde se esconde grande parte do custo real de uma varredura de índice, porque as páginas do heap não são necessariamente armazenadas na mesma ordem do índice, então seguir muitos ponteiros pode significar muitas leituras dispersas e não sequenciais.
O PostgreSQL também suporta tipos de índice não-B-tree construídos para diferentes formatos de dados: GIN para consultas de contenção sobre JSONB ou arrays, GiST e seus parentes para busca geométrica e de vizinho mais próximo, e BRIN para tabelas extremamente grandes e naturalmente ordenadas, como dados de séries temporais.
Cada um deles existe porque o modelo de correspondência de prefixo ordenado de uma B-tree simplesmente não se encaixa em todos os formatos de consulta, não porque sejam estritamente índices "melhores".
Mecânicas e Interações
Criar um índice não força o planejador a usá-lo; ele apenas adiciona um novo candidato ao conjunto de caminhos de acesso que o planejador irá calcular em relação a todas as outras opções, incluindo uma varredura sequencial simples.
Se o planejador realmente escolhe seu novo índice depende inteiramente do modelo de custo discutido nas páginas do planejador deste site: a seletividade estimada do predicado da sua consulta, combinada com random_page_cost e o tamanho da tabela, determina se o acesso disperso ao heap de uma varredura de índice supera simplesmente ler a tabela diretamente.
É por isso que um índice em uma coluna de baixa seletividade, que corresponde a uma grande fração das linhas, é frequentemente ignorado pelo planejador, mesmo que tecnicamente "possa" ser usado, porque uma varredura sequencial é genuinamente mais barata quando você acabaria seguindo ponteiros de volta para a maior parte da tabela de qualquer maneira.
O mapa de visibilidade introduz um segundo caminho de otimização importante: se todas as linhas em uma página de heap forem conhecidas por serem visíveis para todas as transações, o PostgreSQL pode, às vezes, pular completamente o acesso ao heap e responder a uma consulta usando apenas o índice, desde que todas as colunas que a consulta precisa estejam presentes no próprio índice.
Essa otimização é chamada de varredura apenas por índice (index-only scan), e é a razão pela qual a cláusula INCLUDE existe, permitindo que você carregue colunas de carga adicionais nas páginas folha do índice puramente para satisfazer esse atalho, sem tornar essas colunas parte da chave pesquisável.
Índices parciais interagem com este mesmo modelo de custo de um ângulo diferente: adicionando uma cláusula WHERE à própria definição do índice, você encolhe o índice para apenas as linhas que realmente importam para um padrão de consulta recorrente específico, o que reduz tanto seu tamanho quanto o custo que o planejador atribui à sua varredura.
Cada um desses benefícios tem um custo espelhado no lado da gravação, porque um índice não é um artefato somente leitura; é uma estrutura viva que deve ser mantida correta em cada INSERT, UPDATE e DELETE que toca em uma coluna indexada.
Um UPDATE que altera uma coluna indexada normalmente requer que o PostgreSQL insira uma nova entrada de índice e, dependendo se a atualização se qualifica para a otimização de tupla somente heap, essa amplificação de gravação pode se multiplicar em todos os índices definidos na tabela.
Com o tempo, à medida que as linhas são atualizadas e excluídas, as páginas B-tree acumulam entradas mortas, uma condição geralmente chamada de bloat de índice (index bloat), que aumenta o tamanho do índice no disco e retarda as varreduras até que o vácuo recupere o espaço ou uma reconstrução o compacte.
-- Dois índices, dois trabalhos muito diferentes da mesma estrutura B-tree:
-- um satisfaz WHERE + ORDER BY juntos, o outro limita a um subconjunto quente.
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);
CREATE INDEX orders_open_idx
ON orders (customer_id) WHERE status = 'open';Considerações Avançadas e Aplicações
Em escala, o design de índices se torna um problema de portfólio em vez de uma decisão por consulta, porque cada índice que você adiciona para atender a um padrão de consulta adiciona custo contínuo de gravação e vácuo a cada gravação na tabela, independentemente de essa consulta ser executada novamente ou não.
Equipes que adicionam um índice para cada consulta lenta ad-hoc sem auditar periodicamente o uso tendem a acumular índices redundantes ou sobrepostos, onde um índice composto de três colunas torna um índice separado de coluna única em sua coluna principal totalmente supérfluo.
CREATE INDEX CONCURRENTLY existe especificamente para sistemas de produção, construindo o índice sem reter o bloqueio exclusivo que um CREATE INDEX simples tomaria, ao custo de uma construção mais lenta e um pequeno risco de precisar tentar novamente se for interrompido no meio da construção.
Tabelas com alta taxa de gravação, somente inserção e chaves monotonicamente crescentes, como tabelas de séries temporais ou de logs de eventos, geralmente preferem índices BRIN em vez de B-tree, porque BRIN armazena apenas intervalos de resumo grosseiros por bloco em vez de uma entrada por linha, trocando precisão por uma estrutura dramaticamente menor que custa muito menos para manter.
Cargas de trabalho especializadas levam o design de índices ainda mais longe: os índices de vizinho mais próximo aproximado do pgvector trocam a recuperação exata pela velocidade de consulta em busca de similaridade, e os índices espaciais do PostGIS dependem do GiST para tornar as consultas de contenção de caixa delimitadora tratáveis, nenhuma das quais uma B-tree poderia atender.
A manutenção de índices também não é uma decisão única, já que REINDEX CONCURRENTLY existe precisamente porque o bloat e a fragmentação de divisão de página se acumulam ao longo da vida de um índice ocupado, e reconstruir periodicamente um índice muito utilizado pode encolhê-lo e restaurar o desempenho previsível da varredura.
| Tipo de Índice | Força | Fraqueza | Melhor Ajuste |
|---|---|---|---|
| B-tree | Igualdade, intervalo, ordenação, consultas de prefixo mais à esquerda em compostos | Útil apenas por prefixo de chave; grande em chaves largas de alta cardinalidade | A escolha padrão para quase todas as colunas escalares de filtro/ordenação |
| GIN | Contenção rápida em JSONB, arrays, texto completo | Gravações mais lentas e maiores que B-tree | Colunas de busca de texto completo, associação de array, @> |
| GiST/SP-GiST | Consultas geométricas, de sobreposição de intervalo, de vizinho mais próximo | Mais lento que B-tree para igualdade simples | Geometrias PostGIS, tipos de intervalo, busca de similaridade |
| BRIN | Pegada minúscula em tabelas enormes e naturalmente ordenadas | Inútil se os dados não estiverem fisicamente ordenados pela chave | Tabelas de séries temporais ou de logs somente anexáveis |
Conceitos Errôneos Comuns
- "Adicionar um índice garante que o planejador o usará." - O planejador só escolhe uma varredura de índice quando seu custo estimado supera todos os outros planos candidatos para essa consulta específica.
- "Mais índices sempre tornam as consultas mais rápidas." - Cada índice adiciona sobrecarga de gravação e vácuo, e um índice que nunca é escaneado é custo puro.
- "A ordem das colunas em um índice composto não importa, apenas quais colunas estão incluídas." - Índices compostos B-tree só atendem a consultas eficientes em um prefixo mais à esquerda, então a ordem é frequentemente toda a decisão de design.
- "Uma varredura de índice nunca toca na própria tabela." - A maioria das varreduras de índice ainda segue um ponteiro de volta ao heap; apenas as varreduras apenas por índice, e apenas quando o mapa de visibilidade coopera, evitam essa etapa.
- "Um índice único e uma restrição UNIQUE são mecanismos diferentes." - Uma restrição UNIQUE é implementada usando um índice único nos bastidores; eles são a mesma estrutura com uma restrição declarada por cima.
- "Uma vez construído, um índice permanece otimamente eficiente para sempre." - Atualizações e exclusões contínuas fragmentam e incham as páginas B-tree, razão pela qual operações de manutenção como REINDEX existem como uma rotina, não um resgate.
FAQs
O que um índice B-tree realmente armazena?
Ele armazena os valores das colunas indexadas em ordem classificada, mais um ponteiro (TID) de volta à localização de cada linha correspondente no heap da tabela.
Essa estrutura classificada é o que permite ao PostgreSQL localizar um valor, ou um intervalo de valores, sem escanear todas as linhas.
Por que um índice composto em (a, b) não ajuda uma consulta que filtra apenas em b?
Um índice composto B-tree é classificado principalmente por sua primeira coluna, de modo que os valores da segunda coluna estão espalhados, não agrupados.
Apenas uma consulta que filtra em um prefixo mais à esquerda das colunas-chave pode usar o índice eficientemente para esse fim.
Criar um índice força o PostgreSQL a usá-lo?
Não, o índice apenas se torna mais um caminho de acesso candidato que o planejador calcula em relação a alternativas como uma varredura sequencial.
Predicados de baixa seletividade, em particular, são frequentemente atendidos de forma mais barata por uma varredura sequencial, mesmo quando um índice correspondente existe.
O que é uma varredura apenas por índice e por que ela é mais rápida?
É uma varredura que responde a uma consulta usando apenas os próprios dados do índice, pulando a segunda etapa de seguir ponteiros de volta ao heap.
Requer que todas as colunas que a consulta precisa já estejam presentes no índice e depende do mapa de visibilidade confirmando que as páginas de heap relevantes não precisam de uma nova verificação de visibilidade.
Por que eu adicionaria colunas a um índice usando INCLUDE em vez de apenas adicioná-las à chave?
Colunas INCLUDE acompanham nas páginas folha do índice para habilitar varreduras apenas por índice sem torná-las parte da chave pesquisável e classificável.
Manter a chave em si estreita mantém o índice menor e as regras de correspondência de prefixo mais à esquerda mais fáceis de raciocinar.
Por que todo índice retarda as gravações?
Qualquer INSERT, UPDATE ou DELETE que toque em uma coluna indexada também deve atualizar a própria estrutura B-tree desse índice para permanecer correta.
Uma tabela com muitos índices multiplica esse custo em todos eles em cada gravação relevante.
O que é bloat de índice e de onde ele vem?
Bloat é o acúmulo de espaço morto ou semi-vazio dentro das páginas de uma B-tree à medida que as linhas são atualizadas e excluídas ao longo do tempo.
Ele aumenta o tamanho do índice no disco e pode retardar as varreduras até que o vácuo recupere o espaço ou uma reconstrução compacte a estrutura.
Quando devo usar um índice parcial em vez de um completo?
Quando um padrão de consulta filtra consistentemente para um subconjunto estável e bem conhecido de linhas, como status = 'open'.
Um índice parcial apenas indexa esse subconjunto, o que reduz tanto seu tamanho quanto seu custo de manutenção em relação à indexação da tabela inteira.
Por que eu escolheria BRIN em vez de uma B-tree?
BRIN armazena intervalos de resumo grosseiros por bloco em vez de uma entrada por linha, tornando-o muito menor e mais barato de manter em tabelas muito grandes.
Ele só funciona bem quando a coluna indexada está naturalmente correlacionada com a ordem física das linhas, como um timestamp somente anexável.
Uma restrição UNIQUE é um mecanismo diferente de um índice único?
Não, declarar uma restrição UNIQUE cria um índice único nos bastidores, e ambos aplicam e atendem a consultas através da mesma estrutura.
Não há um mecanismo de verificação de restrição separado além desse índice.
Por que dois índices na mesma tabela seriam considerados redundantes?
Se as colunas-chave de um índice são um prefixo estrito mais à esquerda de outro, o índice mais curto raramente justifica seu próprio custo de manutenção.
O planejador geralmente pode satisfazer os casos de uso do índice mais curto usando o índice composto mais longo em vez disso.
A construção de um índice sempre bloqueia gravações na tabela?
Um CREATE INDEX simples pega um bloqueio que impede gravações concorrentes durante sua duração.
CREATE INDEX CONCURRENTLY evita esse bloqueio exclusivo ao custo de uma construção mais lenta e uma pequena chance de precisar tentar novamente se for interrompido.
Relacionados
- Index Design Basics - a receita prática para combinar índices com formatos de consulta
- Multicolumn Index Order - a regra do prefixo mais à esquerda aplicada na prática
- Duplicate & Redundant Indexes - auditando o problema de custo de portfólio descrito aqui
- REINDEX & REINDEX CONCURRENTLY - reparando o bloat assim que ele se acumula
- Seq Scan vs Index Scan - como o planejador realmente escolhe entre esses caminhos de acesso
Versões Stack: Esta página foi escrita para PostgreSQL 18.4 (linha estável 18, linha de manutenção 17), com comportamento GIN/GiST/BRIN e mecânicas de varredura apenas por índice estáveis em versões principais recentes; pgvector 0.8+ e PostGIS 3.5+ são referenciados por seus tipos de índice especializados.