Índice que o banco decidiu ignorar: quando o plano de consulta muda sozinho

A intuição de quem escreve SQL é que a consulta descreve o resultado e o banco descreve o caminho, e essa parte está certa. O que quase sempre falta é o passo seguinte: o caminho é escolhido por um otimizador baseado em custo, que estima quantas linhas cada operação vai devolver e escolhe o plano mais barato segundo essa estimativa. A estimativa não é uma medição, é uma projeção feita a partir de estatísticas amostradas em algum momento do passado. Nada disso é estável, e nenhuma parte disso está no seu código.

A consequência é que a mesma consulta pode ter planos diferentes em dias diferentes sem que uma linha tenha mudado. Isso não é um defeito do banco, é o comportamento desejado: se a tabela cresceu de dez mil para dez milhões de linhas, o plano correto mudou de verdade, e um otimizador que insistisse no plano antigo seria pior. O problema aparece quando a estimativa está errada, porque aí o otimizador escolhe corretamente segundo uma realidade que não existe.

A pergunta útil quando uma consulta degrada sem alteração de código não é o que mudou no código, é qual das entradas do otimizador mudou. Existem quatro, e cada uma tem um sintoma e um teste próprios.

As duas linhas que mais enganam são a terceira e a quarta. A degradação por volume parece contínua, mas na prática é um degrau: enquanto o custo estimado da varredura sequencial for maior que o do índice, nada acontece, e no dia em que a tabela cruza o ponto de empate o plano vira de uma execução para a outra. A correlação entre colunas é ainda mais silenciosa, porque o otimizador supõe independência por padrão. Se você filtra por cidade e por estado, ele multiplica as duas seletividades como se fossem eventos independentes e estima cem vezes menos linhas do que existem, quando na verdade cidade determina estado.

O erro operacional mais comum na investigação é olhar apenas o plano estimado. Ele mostra o que o otimizador pretende fazer e quantas linhas ele acha que vai encontrar, e é exatamente essa crença que está errada quando a consulta degrada. O que resolve o diagnóstico é o plano executado, que traz lado a lado a estimativa e o número real de linhas de cada nó. A razão entre esses dois números é o sinal mais informativo que existe nesse tipo de incidente.

-- PostgreSQL: o que pedir quando a consulta degradou sem mudanca de codigo.
-- ANALYZE executa de verdade, BUFFERS mostra quanta pagina foi lida.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT p.id, p.total_centavos, p.criado_em
FROM pedidos p
WHERE p.cliente_id = $1
  AND p.status = 'pago'
  AND p.criado_em >= now() - interval '90 days'
ORDER BY p.criado_em DESC
LIMIT 50;

-- Trecho tipico do plano ruim. O que importa nao e o tempo, e a razao
-- entre rows estimado e rows real: 12 contra 184392 e um erro de 15000x.
--
-- Limit  (cost=0.43..812.10 rows=50 width=28)
--        (actual time=11240.882..11240.901 rows=50 loops=1)
--   ->  Index Scan Backward using pedidos_criado_em_idx on pedidos p
--         (cost=0.43..2996318.55 rows=12 width=28)
--         (actual time=11240.879..11240.895 rows=50 loops=1)
--         Filter: (cliente_id = 8812 AND status = 'pago')
--         Rows Removed by Filter: 4183992
--         Buffers: shared hit=91204 read=812118
--
-- Leitura: o banco escolheu percorrer o indice de data de tras para frente
-- apostando que acharia 50 linhas do cliente rapidamente. Como esse cliente
-- tem poucos pedidos num universo grande, ele varreu 4,1 milhoes de linhas
-- ate juntar as 50. O indice existe, esta integro, e foi usado. O plano e
-- que estava errado.

-- Confirmacao de que o problema e estimativa, nao falta de indice:
SELECT
  schemaname,
  relname,
  n_live_tup,
  n_mod_since_analyze,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'pedidos';

-- n_mod_since_analyze proximo de n_live_tup significa que a estatistica
-- descreve uma tabela que nao existe mais.

O detalhe que fecha o diagnóstico nesse exemplo é a linha de linhas removidas pelo filtro. Ela mostra que o banco leu quatro milhões de registros e descartou quase todos, o que é a assinatura clássica de um plano que usou o índice errado com convicção. Não adianta olhar se o índice existe: ele existe, foi usado, e o uso é que foi improdutivo. Um índice composto sobre cliente, status e data resolveria esse caso, mas antes de criar qualquer índice vale medir se a estatística estava correta, porque criar índice para compensar estimativa ruim gera uma coleção de índices que ninguém consegue remover depois.

A prática que economiza mais tempo é registrar o plano executado das consultas críticas em condições normais, antes do incidente. Sem o plano de referência, a investigação vira comparação com a memória de alguém, e a memória tende a lembrar do tempo, não do caminho. Guardar o plano bom permite responder em minutos a única pergunta que importa no meio do incidente: o plano mudou ou os dados mudaram.

Existe uma classe de incidente que confunde até times experientes: a mesma consulta, com o mesmo SQL, roda rápido para uma parte dos clientes e lenta para outra, e o comportamento vira do avesso depois de um reinício. A causa é o plano ser preparado uma vez, a partir do primeiro conjunto de parâmetros que chegou, e reaproveitado para todos os valores seguintes. Se o primeiro valor era atípico, todo o resto herda um plano feito sob medida para um caso que quase nunca acontece.

CENARIO: consulta parametrizada por cliente_id
tabela pedidos: 40 milhoes de linhas, 12 mil clientes

  Cliente A (varejo pequeno): 38 pedidos
  Cliente B (marketplace):    9,2 milhoes de pedidos

PRIMEIRO PLANEJAMENTO COM CLIENTE A
  otimizador ve: filtro devolve ~38 linhas
  escolhe: Index Scan em pedidos_cliente_idx  -> correto para A
  plano fica em cache e passa a valer para todos

  execucao com A:  3 ms      (38 buscas no indice)
  execucao com B:  47 s      (9,2 milhoes de buscas no indice,
                              cada uma com salto aleatorio no heap)

APOS REINICIO, PRIMEIRO PLANEJAMENTO COM CLIENTE B
  otimizador ve: filtro devolve ~9,2 milhoes de linhas
  escolhe: Seq Scan + agregacao             -> correto para B
  plano fica em cache e passa a valer para todos

  execucao com B:  6 s       (uma varredura, leitura sequencial)
  execucao com A:  6 s       (varre 40 milhoes para achar 38 linhas)

O QUE ISSO SIGNIFICA NA PRATICA
  o mesmo SQL tem dois planos corretos e nenhum plano correto para os dois
  o sintoma reportado depende de quem reiniciou o servico e quando
  medir a media esconde tudo: a media dos dois casos nao existe na realidade

SAIDAS REAIS
  1. planejar por execucao nas consultas com distribuicao torta
     (custo: replanejamento por chamada, so vale para consulta cara)
  2. separar a consulta por faixa de cardinalidade na aplicacao
     (dois caminhos explicitos, cada um com o plano que lhe cabe)
  3. aumentar o alvo de estatistica na coluna e deixar o banco
     enxergar a distribuicao real em vez do valor medio

A saída que quase todo time tenta primeiro é a que menos funciona: desativar o cache de plano globalmente. Isso troca um problema pontual por um custo permanente de planejamento em todas as consultas do sistema, inclusive nas milhares que estavam perfeitamente bem. A decisão correta é por consulta, e o critério é a assimetria da distribuição do parâmetro. Se o mesmo filtro devolve trinta e oito linhas para um valor e nove milhões para outro, essa consulta precisa de tratamento explícito. Se a distribuição é razoavelmente uniforme, o plano em cache é uma otimização legítima e não deve ser tocado.

A terceira saída é a mais barata e a mais esquecida. O banco mantém um histograma por coluna com um número limitado de compartimentos, e quando a distribuição tem uma cauda longa esse número padrão não representa os valores extremos. Aumentar o alvo de estatística para a coluna de cliente e reanalisar a tabela costuma fazer o otimizador enxergar que existe um valor com nove milhões de ocorrências, e a partir daí ele escolhe planos diferentes para valores diferentes sempre que puder planejar por execução. É uma mudança de uma linha e resolve mais casos do que parece.

A coleta automática de estatísticas dispara quando uma fração da tabela é modificada, e essa fração é proporcional ao tamanho. Numa tabela de dez mil linhas, o gatilho ocorre depois de algumas centenas de alterações. Numa tabela de quarenta milhões, ele só ocorre depois de milhões. Quanto maior a tabela, mais tempo ela passa com estatísticas velhas, e o problema é justamente que as tabelas grandes são as que mais dependem de estimativas boas.

Existe um caso específico que causa incidente com frequência desproporcional: a coluna de data em tabela que só cresce. A estatística guarda o valor máximo observado na última coleta. Toda consulta que filtra pelos últimos sete dias cai fora do intervalo conhecido, e o otimizador estima uma quantidade minúscula de linhas porque, segundo o que ele sabe, aquele intervalo está além do fim dos dados. A estimativa de uma linha em um filtro que devolve duzentas mil é uma receita para o plano errado, e ela acontece todo dia em toda tabela de eventos que não é reanalisada com frequência.

1. Identifique as tabelas cuja distância entre modificações e última análise é maior que a fração de gatilho, começando pelas maiores.
2. Para tabelas de eventos com coluna de data crescente, reduza o gatilho de análise automática na própria tabela em vez de mexer no ajuste global.
3. Aumente o alvo de estatística nas colunas usadas em filtros com distribuição torta e reanalise, medindo o plano antes e depois.
4. Declare a dependência entre colunas correlacionadas quando o banco suportar estatística estendida, para eliminar o erro por multiplicação de seletividades.
5. Registre o plano executado das consultas críticas como referência versionada, para que a próxima investigação comece com uma comparação e não com uma hipótese.

-- Ajustes por objeto, nao globais. Cada um resolve uma causa distinta.

-- 1) Tabela de eventos que so cresce: analisar com mais frequencia.
--    O padrao dispara depois de 10% da tabela modificada, o que em
--    40 milhoes de linhas significa 4 milhoes de insercoes de atraso.
ALTER TABLE pedidos SET (
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 5000
);

-- 2) Coluna com distribuicao torta: mais compartimentos no histograma.
--    O padrao de 100 nao representa um cliente que sozinho responde
--    por 20% das linhas.
ALTER TABLE pedidos ALTER COLUMN cliente_id SET STATISTICS 1000;
ANALYZE pedidos;

-- 3) Colunas correlacionadas: dizer ao otimizador que elas nao sao
--    independentes. Sem isso ele multiplica as seletividades e erra
--    por ordens de grandeza em filtros combinados.
CREATE STATISTICS pedidos_cliente_status_deps (dependencies, ndistinct)
  ON cliente_id, status FROM pedidos;
ANALYZE pedidos;

-- 4) Verificar se o erro de estimativa caiu de fato, comparando o
--    numero estimado com o real no mesmo filtro.
EXPLAIN (ANALYZE, SUMMARY OFF)
SELECT count(*) FROM pedidos
WHERE cliente_id = 8812 AND status = 'pago';

-- Antes:  rows=12      actual rows=184392   -> erro de 15000x
-- Depois: rows=176410  actual rows=184392   -> erro de 1,04x

A estatística estendida sobre colunas correlacionadas é o ajuste com melhor relação entre esforço e resultado nesse conjunto, e é o menos usado. O caso clássico é filtro por dois campos onde um determina o outro: cidade e estado, categoria e subcategoria, cliente e canal de origem. Sem a declaração, o otimizador trata cada filtro como um sorteio independente e multiplica as probabilidades, chegando a uma estimativa que pode estar cem ou mil vezes abaixo da realidade. Com a declaração, ele passa a usar a contagem conjunta observada, e o plano muda na primeira execução seguinte.

Depois do segundo incidente do mesmo tipo, alguém sempre propõe fixar o plano. A ideia é legítima e o risco é conhecido: um plano fixado é uma decisão tomada com os dados de hoje e aplicada aos dados de daqui a dois anos. Se a tabela dobrar de tamanho ou o perfil de acesso mudar, o plano fixado deixa de ser o melhor e não existe mecanismo que perceba isso sozinho. Fixar plano não é errado, é uma dívida com data de vencimento que precisa estar escrita em algum lugar.

A ordem dessa tabela é a ordem de tentativa, e ela não é arbitrária. Corrigir a estatística é reversível, barato e ataca a causa. Criar índice é permanente e cobra em toda escrita, então merece a pergunta de se o plano bom já não existia. Reescrever a consulta é a única opção que remove o problema em vez de compensá-lo, e vale sempre que a forma escrita for a culpada, o que acontece com mais frequência do que o time admite: uma função aplicada sobre a coluna indexada, uma conversão implícita de tipo ou um filtro com valor nulo tratado de forma ingênua são suficientes para tornar um índice inutilizável.

Fixar o plano fica em último lugar não porque seja ilegítimo, mas porque é a única opção que congela uma decisão e desliga o mecanismo que a corrigiria. Quando for necessário durante um incidente, a regra que evita o arrependimento é simples: toda fixação de plano nasce com data de revisão e com um alerta que dispara se a razão entre linhas estimadas e reais naquele nó ultrapassar o limiar. Sem esses dois itens, a fixação vira uma decisão de dois anos atrás que ninguém lembra de ter tomado.

O monitoramento habitual de banco observa tempo médio de consulta, e o tempo médio é justamente a métrica que perde esse incidente. Uma consulta que roda dez mil vezes por minuto em quatro milissegundos e passa a rodar em onze segundos para dois por cento dos parâmetros mantém a média em uma faixa aceitável por horas. O que muda imediatamente e de forma inequívoca é o plano, e é isso que precisa ser observado.

• Mudança de identificador de plano para uma consulta na lista de críticas: alerta imediato, sem limiar de tempo, porque um plano novo é um evento discreto e a comparação é exata. O alerta carrega o plano anterior e o novo lado a lado, para que a decisão de reverter ou aceitar seja tomada em minutos.
• Razão entre linhas estimadas e linhas reais acima de dez vezes em qualquer nó de consulta crítica: alerta diário agregado, porque esse número denuncia estatística envelhecida antes de o plano virar. É o único alerta dessa lista que dispara enquanto o desempenho ainda está bom, e por isso é o que mais evita incidente.
• Percentil noventa e nove da consulta acima de vinte vezes a mediana da mesma consulta na mesma janela: alerta de assimetria, que pega o caso do parâmetro capturado. A comparação precisa ser com a mediana da própria consulta, não com um limiar absoluto, porque o que importa é a distância entre os casos bons e os ruins do mesmo SQL.

O segundo alerta é o que muda o modo de operação do time. Ele não reporta lentidão, reporta que o otimizador está trabalhando com uma descrição errada da tabela, o que é uma condição que precede a lentidão por dias ou semanas. Tratá-lo como manutenção de rotina, e não como incidente, é o que transforma esse tipo de degradação de surpresa em tarefa agendada. O terceiro alerta é o que distingue a consulta que está lenta para todo mundo daquela que está lenta para alguns, e essa distinção decide entre corrigir estatística e separar caminhos de execução.

Vale registrar o que nenhum desses alertas faz: nenhum deles diz que o plano novo é pior. Um plano pode mudar porque a tabela cresceu e a mudança pode estar correta. O papel do alerta é garantir que a mudança seja vista por alguém no dia em que acontece, com os dois planos disponíveis para comparação, em vez de ser descoberta três semanas depois pelo time comercial que parou de conseguir abrir o painel.