Teste com 179 índices recomendados no Postgres mostra que 18% pioraram a consulta

O teste que faltava aos advisors de índice

Prateek Arora, criador do PgLens, um advisor de índices de código aberto↳Open source71 conteúdosComo o Open Source Está Liberando o Poder da Automação para TodosDev (Back & Front) · out 2025Código aberto: programadores criam software da NASA sem saberDev (Back & Front) · abr 2021N8N: O que é a ferramenta open source que está revolucionando a automação em TI?Dev (Back & Front) · dez 2025Ver tudo em Dev (Back & Front) → para PostgreSQL↳PostgreSQL11 conteúdosPostgreSQL via SSL com GolangData · abr 20195 itens legais sobre data types do PostgreSQLData · mar 20195 serviços gratuitos na cloud para bancos de dados PostgresData · fev 2025Ver tudo em Data →, publicou no Planet PostgreSQL um experimento incômodo para quem vive de recomendar índice: de 179 recomendações já aprovadas pelo próprio planejador do banco, 32 deixaram a consulta real mais lenta, e sete chegaram a dobrar (ou mais) o tempo de execução.

O ponto de partida é técnico e específico. Como a maioria dos advisors de índice do mercado, o PgLens usa a extensão HypoPG para criar um índice hipotético, rodar EXPLAIN de novo e comparar o custo estimado antes e depois. Se o custo estimado cai pelo menos 15%, a recomendação é aprovada e mostrada ao usuário como ganho. Arora queria saber quanto vale, na prática, esse número, então decidiu construir de verdade cada índice sugerido e cronometrar a consulta de ponta a ponta.

Metodologia: Join Order Benchmark, 7 GB de dados reais

Para o teste, ele usou o Join Order Benchmark (JOB): 113 consultas sobre a base real do IMDB, somando 7 GB, rodando em PostgreSQL 16 com hypopg 1.4. O JOB foi desenhado justamente para expor os casos em que o otimizador erra a estimativa de linhas retornadas por um join, o que o torna um teste duro para qualquer advisor baseado em custo estimado em vez de medição real.

O PgLens gerou 214 recomendações validadas pelo planejador para as consultas do benchmark, mas essas recomendações apontam para apenas 10 índices distintos: um único índice de chave estrangeira, por exemplo, serve de solução para várias consultas diferentes. Antes de rodar o teste, Arora fixou o critério de sucesso: 15% mais rápido é vitória, 5% mais lento é derrota. Definir essa régua antes de olhar os resultados é o que dá credibilidade ao experimento, e isso interessa a qualquer DBA que avalie ferramenta de terceiro.

Os números: vitória, derrota e neutralidade

Dos 179 índices efetivamente medidos, 128 cumpriram a promessa: a consulta ficou pelo menos 15% mais rápida. Mas 32 (18% do total) pioraram o tempo real em 5% ou mais, e sete delas mais que dobraram o tempo de execução. Fazendo a conta, os 19 restantes ficaram numa faixa neutra: nem o ganho mínimo de 15%, nem a perda de 5% que configuraria derrota.

Além desses 179, outras 34 recomendações nem puderam ser testadas, porque o índice sugerido simplesmente não pôde ser construído no banco real. Essas 34 apontam todas para o mesmo índice, o segundo colocado na lista de sugestões do PgLens para essas consultas: movie_info(info).

O caso mais grave: do bom senso ao desastre

O pior resultado do lote foi a consulta 10c, com um índice sugerido sobre cast_info(movie_id). O planejador estimava 91% de redução de custo. Medido de verdade, o tempo saiu de 0,93 s para 7,3 s. Arora rodou de novo manualmente para conferir, e o padrão se repetiu: cerca de 1 s virou cerca de 9 s.

A causa é um clássico de quem lê plano de execução todos os dias. Com o índice criado, o planejador escolheu um nested loop que consulta cast_info uma vez para cada linha de um join anterior. Só que ele subestimou quantas linhas esse join devolveria, e o loop rodou muito mais vezes do que o plano previa: a consulta leu 71 vezes mais buffers do que deveria. O HypoPG não detecta esse tipo de erro porque consulta o mesmo planejador, com a mesma estimativa de linhas errada que já existia antes do índice.

Arora resume o problema de forma direta:

Todo advisor construído sobre índices hipotéticos tem esse ponto cego, o meu incluído.

Every advisor built on hypothetical indexes has this blind spot, mine included.Prateek Arora, criador do PgLens

Para quem administra banco em produção, a lição não é nova, mas o número é um lembrete valioso: índice não corrige estimativa de cardinalidade errada, e às vezes piora o plano ao abrir caminho para uma estratégia de junção ruim.

Os 34 que nunca saíram do papel: limite físico do B-tree

O segundo problema é estrutural, não de estimativa. Ao tentar construir o índice sobre movie_info(info), o Postgres recusou a operação:

ERROR: index row requires 9392 bytes, maximum size is 8191

Dos 14,8 milhões de valores daquela coluna, 1.182 excedem o tamanho máximo que uma entrada de B-tree aceita. Um índice hipotético nunca escreve uma entrada de verdade, então o HypoPG não tinha como prever essa falha: ele simula custo, não grava dado. Qualquer advisor que pare na simulação, sem tentar o CREATE INDEX real (ou pelo menos validar o tamanho dos valores), vai recomendar algo que o banco rejeita na hora H.

O que o planejador acerta: ranking, não magnitude

Nem tudo saiu errado, e esse é o ponto mais útil do experimento para quem avalia ferramenta de recomendação. O índice que o PgLens apontou como #1 economizou 364 dos 424 segundos totais gastos pelas consultas às quais ele se aplicava. Olhando o conjunto todo, a ordem dos índices por economia estimada ficou próxima da ordem por economia medida (correlação de Spearman de 0,83).

O que não se sustentou foi o percentual isolado por consulta. Saber que "este índice corta 91% do custo" quase nada disse sobre o ganho real daquela consulta específica. Em outras palavras: perguntar "qual índice importa mais?" é uma pergunta que o planejador responde razoavelmente bem; perguntar "quanto essa consulta vai ganhar?" não é.

Mudanças que Arora fez no PgLens depois do teste

Com o resultado em mãos, Arora alterou o comportamento da ferramenta em quatro pontos:

• Todo número vindo do planejador agora é rotulado como estimativa, nunca exibido como "ganho de velocidade" garantido.
• O comando pglens confirm constrói o índice numa cópia do banco e cronometra as consultas reais do usuário, antes e depois, sem tocar no ambiente de produção.
• Depois que o índice é efetivamente criado, o PgLens passa a mostrar o tempo medido de cada consulta, antes e depois, em vez de só a estimativa de custo.
• A ferramenta agora avisa quando uma coluna pode conter valores grandes demais para caber numa entrada de B-tree, antes de sugerir o índice.

O que fica para quem administra Postgres em produção

O experimento de Arora não invalida o uso de advisors de índice, mas delimita exatamente onde confiar neles. Ranking relativo entre candidatos a índice tende a ser confiável; percentual de ganho isolado por consulta, não. Essa distinção importa mais do que qualquer número de marketing de ferramenta de terceiro.

Em resumo: antes de aplicar qualquer sugestão de índice, seja de advisor automático, seja de intuição de equipe, vale repetir o caminho que o experimento descreve: rodar EXPLAIN (ANALYZE, BUFFERS) de verdade numa cópia dos dados, cronometrar a consulta com e sem o índice, e só então decidir. Confiar cegamente no custo estimado de um plano hipotético, sem medir o tempo de execução real, é abrir mão exatamente do que separa um DBA criterioso de quem só aplica receita de ferramenta.

Vale registrar também o segundo tipo de risco, menos discutido: a compatibilidade do dado com a estrutura do índice. Antes de criar um índice B-tree sobre uma coluna de texto livre ou JSON, é prudente checar a distribuição de tamanho dos valores, porque o limite de 8.191 bytes por entrada é uma restrição física do Postgres, não uma sugestão.

O PgLens é aberto (licença Apache-2.0), roda localmente (self-hosted) e, segundo o autor, só lê o banco, nunca escreve nele sem autorização explícita do comando de confirmação. O projeto está em github.com/Prateek-Arora/pglens, ainda como release candidate, com o método do benchmark, as tabelas e os scripts documentados em docs/benchmarks.md e reproduzíveis via make accuracy-job. Para quem quiser contestar os números, Arora convida: rode pglens confirm na sua própria base e compare.

Fonte: Planet PostgreSQL