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.
//
Busque em todas as páginas da documentação
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.
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".
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';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 |
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.
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.
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.
É 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.
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.
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.
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 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.
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.
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.
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.
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.
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.
Revisado por Chris St. John·Última atualização: 15 de jul. de 2026