Pg_hint_plan: Force PostgreSQL a executar os planos de consulta do jeito que você quer

Artigo original ↗ Discussão no Hacker News ↗

A extensão pg_hint_plan do PostgreSQL, que permite aos usuários direcionar ou efetivamente forçar planos específicos de consulta, está atraindo interesse de engenheiros que lidam com escolhas imprevisíveis do planejador em cargas de trabalho grandes ou críticas. Defensores veem hints como uma ferramenta essencial de “último recurso” para estabilizar o desempenho durante incidentes de produção ou contornar limitações de longa data do otimizador, enquanto críticos alertam que codificar planos fixos pode envelhecer mal à medida que dados e versões do PostgreSQL evoluem. A discussão também expõe problemas mais profundos no modelo de estatísticas e planejamento do PostgreSQL — especialmente em consultas com LIMIT e ORDER BY — e destaca como extensões, índices compostos/parciais e configurações do planejador são usados na prática para recuperar o controle.

O que o pg_hint_plan oferece

  • A extensão permite que os usuários influenciem o planejador do PostgreSQL em direção a planos específicos (joins, índices etc.) sem alterar o PostgreSQL principal.
  • É visto como especialmente valioso para:
    • Incidentes de produção de emergência quando o planejador escolhe um plano desastroso.
    • Pesquisa/experimentação, quando você quer fixar algumas escolhas de plano enquanto deixa o planejador lidar com o resto.
    • Migração de outros bancos de dados (por exemplo, via Babelfish) em que cargas de trabalho existentes dependem de hints.

Problemas práticos ao usá-lo

  • A interface da tabela de hints é descrita como frágil:
    • A correspondência é sensível a espaços em branco e pode falhar silenciosamente.
    • O tratamento de parâmetros é desajeitado; ferramentas anteriores tornavam difícil iterar em consultas parametrizadas, embora versões mais novas do psql ajudem.
  • A extensão altera os custos do plano para “sugerir fortemente” um plano; se um plano com hint for impossível, o PostgreSQL ainda escolhe outra coisa.

Debate: hints são bons ou ruins?

  • Preocupações:
    • Hints podem congelar planos subótimos à medida que os dados e as versões do PostgreSQL evoluem.
    • Desenvolvedores raramente revisitam consultas com hints; o acúmulo de cruft acontece.
    • A postura da equipe principal de desenvolvimento (como descrita): é melhor melhorar estatísticas e o otimizador do que adicionar hints ao SQL.
  • Contrapontos:
    • A previsibilidade em produção costuma ser mais valiosa do que a otimalidade teórica; evitar regressões súbitas de plano é crítico.
    • Estatísticas e configuração do planejador são complexas, globais e arriscadas; hints são localizados e mais fáceis de raciocinar para uma única consulta.
    • As estatísticas e o otimizador do PostgreSQL têm fraquezas conhecidas em grande escala e com dados correlacionados ou enviesados; alguns problemas permanecem sem solução há anos.
    • Hints são apresentados como “assembly inline”: ruins como padrão, inestimáveis para casos raros de borda.

Alternativas e abordagens de ajuste

  • Remédios sugeridos sem hints:
    • VACUUM ANALYZE, autovacuum ajustado.
    • Aumentar os alvos de estatísticas por coluna ou usar estatísticas estendidas/customizadas.
    • Ajustes de configuração do planejador (join_collapse_limit, parâmetros de custo) limitados com SET LOCAL.
    • Mudanças de esquema/índice (índices compostos ou parciais) para dar melhor suporte às consultas frequentes.

Pontos de dor do planejador e exemplos

  • Vários relatos de consultas com LIMIT + ORDER BY + WHERE escolhendo planos muito ruins, especialmente com colunas correlacionadas ou enviesadas.
  • Há divergência sobre com que frequência ocorrem flips catastróficos de plano, mas há consenso de que eles acontecem, particularmente em tabelas muito grandes.
  • Alguns argumentam que isso torna o PostgreSQL “perigoso” em produção sem uma forma confiável de fixar ou sobrescrever planos.