10 anos de melhorias no otimizador do PostgreSQL

 (rmarcus.info)
2 pontos por GN⁺ 2024-04-19 | 1 comentários | Compartilhar no WhatsApp
  • Comparando a latência de consulta no 90º percentil com o Join Order Benchmark do PostgreSQL 8 ao 16, confirma-se empiricamente a melhora de longo prazo no desempenho de cauda
  • Em relação ao PostgreSQL 8, o 16 reduziu a latência de cauda quase pela metade, e o intervalo de 13 a 16 permaneceu em um nível geralmente estável
  • Na análise de regressão, observou-se uma melhora média de 15% no desempenho a cada avanço de uma versão principal, embora um modelo linear possa não explicar bem o padrão das mudanças
  • O experimento fixou as condições com GCC 13.2, Docker no Arch Linux, shared_buffers de 8GB e work_mem de 8MB, focando na qualidade do otimizador de consultas
  • Ao interpretar a magnitude das melhorias, é preciso considerar não só o otimizador, mas também mudanças no motor de execução, como workers paralelos e compilação JIT

Configuração do benchmark do PostgreSQL 8~16

  • O objeto da análise são as versões principais de 8 a 16 do PostgreSQL, um otimizador de consultas open source
  • No benchmark foi usado o Join Order Benchmark, um conjunto de consultas com muitas junções complexas
    • Esse benchmark foi introduzido no artigo “How Good are Query Optimizers, Really?”
  • Cada versão do PostgreSQL foi compilada com GCC 13.2 dentro de um contêiner Docker no Arch Linux
  • O ambiente de medição foi ajustado para observar a qualidade do otimizador de consultas, mais do que desempenho de índices ou de I/O
    • shared_buffers foi configurado em 8GB, grande o suficiente para conter todo o banco de dados
    • work_mem foi fixado em 8MB em todas as versões
  • Cada consulta foi executada uma vez para aquecer o cache e, em seguida, registrou-se a latência mediana de mais 5 execuções
  • Em cada versão principal foi usada a versão menor mais recente
    • Por exemplo, para PostgreSQL 8 foi usada a 8.4.22
    • Essas versões menores normalmente saíram depois da nova versão principal, mas em geral incluem apenas correções de bugs, sem novos recursos ou melhorias de desempenho

Resultados da medição e interpretação

  • O desempenho de cauda do PostgreSQL melhorou bastante no geral
    • Comparando PostgreSQL 8 e 16, a latência de cauda caiu quase pela metade
    • Do PostgreSQL 13 ao 16, o nível se manteve geralmente estável
  • A análise de regressão foi usada para verificar se a tendência de queda entre o número da versão principal e a latência de consulta é significativa, além de quantificar o ganho por versão
    • Segundo a regressão linear, cada nova versão principal trouxe em média uma melhora de 15% no desempenho no Join Order Benchmark
    • Ainda assim, um modelo linear pode ser inadequado para medir o padrão real das mudanças
  • É difícil explicar todas as melhorias apenas pelo otimizador de consultas
    • Melhorias no motor de execução, como workers paralelos e compilação JIT, também afetam o desempenho
    • Como os planos de execução de cada consulta do JOB mudaram ao longo dos anos continua sendo tema para uma análise separada
  • Ao migrar do PostgreSQL 8 para o 16, há chance de a latência de cauda da carga de trabalho cair bastante
  • Em comparações de pesquisa, é importante notar que o próprio PostgreSQL continua se fortalecendo como linha de base
    • Neo e Bao foram comparados ao PostgreSQL 11, mas pesquisas mais recentes comparam com PostgreSQL 14, 15 e 16
    • Mesmo que uma técnica antiga melhore 30% em relação ao PostgreSQL e uma técnica recente melhore 25%, a técnica recente pode ter sido comparada com um PostgreSQL mais forte
  • Os valores medidos originais podem ser consultados nos raw data

Leituras relacionadas

1 comentários

 
GN⁺ 2024-04-19
Comentários do Hacker News

  • Uso o Postgres há 15 anos e passei a maior parte da carreira modelando e resolvendo problemas de otimização matemática, e acho que há três pontos centrais neste tema
    Todo problema de otimização precisa de dados de custo, e quanto mais e melhores forem esses dados, melhor. O Postgres teve melhorias, como estatísticas entre colunas, mas ainda há lacunas grandes, como a latência de chamadas de sistema. A latência para ler páginas do disco varia muito entre sistemas, mas o Postgres não mede isso diretamente e depende de valores de configuração. Também faltam estatísticas de chave estrangeira, então joins que seguem chaves estrangeiras não deveriam gerar planos ruins, mas isso ainda acontece às vezes
    Especialmente para consultas grandes e caras, é preciso haver planejamento tardio ou planejamento de cenários alternativos. Hoje o plano é fechado antes da execução, mas o número de linhas ou as estimativas de cardinalidade obtidas nas fases iniciais da execução podem melhorar bastante o plano das fases posteriores
    Aprendizado de máquina também é uma área com espaço para melhorias, mas as tentativas que vi até agora não foram impressionantes. Em vez de usar aprendizado de máquina no plano em si, ele deveria ser usado em descoberta e estimativa de custos. É preciso criar modelos de custo melhores e fazer o mecanismo de otimização usar esses dados

    • Queria ouvir mais opiniões. Por exemplo, me surpreende que latência de chamadas de sistema esteja tão no topo da lista. Acho que a visão geral da comunidade de bancos de dados é que o modelo de custo é razoavelmente bom, e o que é realmente ruim é a estimativa de cardinalidade
      No caso de planejamento tardio/alternativo, fico me perguntando se execução adaptativa de consultas é uma abordagem razoável. Dá para fazer com que as informações do começo da execução influenciem o plano posterior, mas escolher errado os primeiros joins é algo comum, e me preocupa que seja difícil se recuperar disso sem coisas como Yannakakis/SIPs
      Quanto a “aprendizado de máquina para otimização de consultas”, claramente tenho meus vieses. Ainda assim, todas as abordagens de “aprendizado de máquina para planejamento” que vi acabam, por baixo dos panos, usando aprendizado de máquina para descoberta/estimativa de custos. Essas abordagens tentam equilibrar os dados que coletam, isto é, exploração, com a qualidade dos planos que produzem, isto é, aproveitamento. Curiosamente, se você usar aprendizado de máquina de um jeito totalmente separado do planejamento, as estimativas ficam mais precisas, mas os planos reais de consulta ficam piores: https://people.csail.mit.edu/tatbul/publications/flowloss_vl...
      Tenho interesses nessa área, então é bom considerar isso ao avaliar minha opinião
    • Planos alternativos parecem muito promissores. Há pouco tempo vi um plano de consulta que estimava que uma subconsulta produziria cerca de 1.000 linhas, então usou loops aninhados com index scan, mas na prática eram cerca de 1 bilhão de linhas
      Ainda não sei por que a estimativa errou tanto, mas se fosse possível trocar de loop aninhado para hash join quando a contagem de linhas passasse de algum limite, isso provavelmente ajudaria muito a evitar planos desastrosos
    • Queria entender exatamente o que significa dizer que faltam estatísticas de chave estrangeira. O Postgres, como muitos bancos de dados relacionais, não cria índices em chaves estrangeiras automaticamente, e imagino que você já saiba disso
      Você está falando de problemas na ordem dos joins?
    • Fico curioso se você considera o MSSQL melhor nessa parte

  • O otimizador de consultas do Postgres tenta reduzir o número de páginas lidas do disco e o número de páginas escritas no disco como resultado intermediário. Por isso, parece errado usar shared buffers grandes o bastante para comportar todos os dados e então fazer benchmark do otimizador de consultas
    Nesse caso, você estaria medindo não a qualidade do plano de consulta gerado, mas a velocidade do otimizador e do processador de joins. Na prática, eu nem me surpreenderia se os planos gerados em cada versão fossem todos iguais e só a velocidade de execução tivesse sido medida

    • Não é bem assim. O alvo da otimização não é só páginas lidas do disco, mas também coisas como uso de CPU
      O custo é uma unidade arbitrária feita para se correlacionar com tempo gasto, não com número de leituras de disco, então comparar planos com tudo já em RAM também é totalmente válido. Por convenção, uma leitura de uma página de disco é escalada como 1,0, mas isso é diferente de dizer que “o otimizador minimiza o número de leituras de páginas de disco”. Também poderia ter sido definido como 1,0 para 1 ms em uma máquina qualquer
    • É bem possível que os planos fossem parecidos e que o que tenha sido medido fossem melhorias no mecanismo de execução. O Join Order Benchmark foi projetado para testar a qualidade do otimizador
      O otimizador do PG tenta reduzir não só o número de páginas lidas do disco, mas também o número de tuplas que a CPU examina, a quantidade de avaliações de expressões condicionais etc., e todos esses números são combinados em um “custo” que vira a função que o otimizador minimiza
      Medições de desempenho com cache frio e cache quente podem dar resultados diferentes, e este experimento é claramente um cenário de cache quente. Mas o cache frio também tem os problemas mencionados. No tamanho de dados do Join Order Benchmark, o efeito das melhorias de B-tree do PG em economizar algumas operações de I/O pode acabar dominando as melhorias baseadas em CPU
      Como referência, o plano da consulta no percentil 90 de latência mudou de um plano que usava loop join e merge join no PG 8.4 para um plano que usa hash join no PG 16, e essa consulta já não é mais a consulta do percentil 90. Isso pode ser visto ao menos como alguma evidência de melhoria no otimizador

  • O texto menciona o compilador JIT do PostgreSQL, mas até agora eu só vi ele piorar o desempenho das consultas. Já coloquei sua desativação na checklist de instalação

    • Um cliente teve os piores problemas de desempenho depois de migrar para Postgres. Estranhamente, isso só acontecia em Docker e na configuração do servidor de testes, não nas máquinas dos desenvolvedores. Os desenvolvedores estavam executando Postgres via Homebrew
      No fim, descobrimos que o Homebrew instalava o Postgres sem suporte a JIT, e em uma máquina de desenvolvedor certas consultas terminavam em 200 ms, enquanto em ambientes com JIT ativado levavam 4 a 5 segundos. Eu não uso Postgres tão a fundo, então levei um tempo para descobrir a causa, e desde então sempre desligo o JIT e não penso mais nisso
    • O compilador JIT é excelente para consultas analíticas
      No PostgreSQL, também dá para configurar o limiar de ativação do JIT, então você pode aumentar o critério para ele entrar em ação
    • O JIT do pg mostra bem que o LLVM não é tão bom para JIT, e isso fica pior porque o Postgres não tem um cache persistente de consultas compartilhado
      Se desse para compilar de forma assíncrona para consultas futuras, provavelmente seria menos prejudicial. Na verdade, JIT em geral, especialmente backends de otimização, tende a funcionar mais próximo desse modelo
    • O Postgres não consegue fazer JIT de uma consulta uma vez e depois executar essa consulta compilada várias vezes?

  • Interessante, mas o esquema de numeração de versões do Postgres mudou no v10. 9.6, 9.5, 9.4, 9.3, 9.2, 9.1, 9.0, 8.4, 8.3, 8.2, 8.1 e 8.0 são, na prática, todos versões principais distintas
    Também seria interessante ver como o desempenho mudou nessas versões

    • Ainda assim, fui grato por eles terem mantido a compatibilidade do sistema de arquivos entre versões principais do v9.0 ao 9.6, permitindo upgrades in-place mais rápidos trocando apenas os binários
      Isso pode até ter limitado o avanço, mas atualizações anuais que exigem mais downtime ou reindexação não são nada agradáveis, e talvez seja por isso que muitos sites adiem o upgrade até o fim do suporte da versão anterior. Especialmente usuários do AWS RDS
      Os upgrades com replicação lógica desde o v10 têm vantagens de disponibilidade, mas são projetos grandes com custo inevitável e risco significativo, a menos que o esquema seja relativamente simples
    • Concordo totalmente. Interpretei o número da versão no estilo semver e escolhi a versão mais recente de cada major, mas isso é diferente da forma como o PostgreSQL tradicionalmente trata números de versão principais
      Por exemplo, PG 8.2 e 8.1 são versões principais diferentes, mas eu as interpretei como se fossem versões menores. A principal razão para isso foi reduzir o número de versões a testar, e concordo que uma análise mais completa deveria testar cada versão principal real

  • Foi dito que “é claro que essa melhoria não se deve inteiramente ao otimizador de consultas”, mas seria interessante ver se houve mudanças nos planos de execução entre as versões

  • Isso me lembrou a lei de Proebsting: https://proebsting.cs.arizona.edu/law.html

    • O lado bom da otimização de compiladores é que ela pode melhorar o desempenho sem tocar fisicamente nas CPUs existentes. A cada ano, extrai-se mais desempenho de máquinas que alguém projetou, e isso se acumula com o tempo
      Imagine qual seria o impacto ambiental de otimizar o desempenho do Python em 1%. Quanto CO2 na atmosfera isso reduziria? Provavelmente mais do que a pegada ambiental combinada de você, sua família e seus amigos. Talvez até equivalente à da cidade inteira onde você mora. E tudo isso porque alguém gastou tempo implementando alguns truques com operações de bits
    • Não sei por quê. Essa lei parece dizer que melhorias de desempenho em software não significam grande coisa, enquanto este artigo mostra que as melhorias no Postgres foram substanciais
      Será porque 15% parece pouco? Neste contexto, não é pouco nem de longe. É menor que os 60% da lei linkada, e fica ainda menor se você dividir como 15/10, mas não dá para comparar desempenho do Postgres com avanço de hardware. Para igualar aqui um ganho de 1% no que está sendo medido, seria preciso uma melhoria enorme de hardware
      Não acho que essa lei seja tão absurda quanto algumas pessoas dizem, mas ela fala sobre tempo de compilação de linguagens de programação. Eu não compararia algo relativamente pouco importante assim com armazenamento e consumo de dados, que dá para dizer que estão entre as coisas mais importantes da ciência da computação
    • Neste caso, o pesquisador compilou todas as versões do PostgreSQL com o mesmo GCC 13.2 e testou no mesmo sistema operacional
    • Parece uma “lei” bem fraca. Foi feita como piada? Os números parecem tirados do nada, em nível de “vamos supor”, e a conclusão erra feio. Parece insinuar que otimizações que melhoram em 4% ao ano o desempenho de muito software no mundo seriam perda de tempo
      O único ponto de comparação apresentado é a lei de Murphy. Fico curioso sobre a diferença de custo entre desenvolver hardware mais rápido e continuar melhorando compiladores. Dependendo de como o retorno sobre investimento se comparar, por exemplo em dólares por ponto percentual de ganho de desempenho, talvez essa “lei” tenha algum peso
      Por outro lado, este texto sobre Postgres parece mostrar retornos decrescentes na otimização, o que contraria a premissa dessa “lei” de ganho constante ano após ano. Ao mesmo tempo, isso também poderia servir para confirmar a insinuação de Proebsting de que, no longo prazo, otimização é um investimento ruim

  • Essa análise está um pouco confusa. Não entendi como chegaram a uma tendência de queda nos dados que não aparece no gráfico
    A mediana parece cair um pouco nas primeiras versões e depois voltar a subir nas versões mais recentes. O R² é muito baixo, então a correlação não parece convincente. Basicamente, parece que a latência de cauda melhorou e o resto depende do ambiente

    • Sou o autor do post do blog
      A interpretação de que “a latência de cauda melhorou e o resto depende do ambiente” é válida, mas eu a consideraria uma leitura conservadora. Claro, para muitas aplicações — talvez a maioria — a latência de cauda é muito importante. Além disso, a latência de cauda também é justamente o alvo principal dos engenheiros do otimizador: reduzir o tempo de execução das consultas mais demoradas

  • Como é a otimização de consultas? Fico curioso se a otimização acontece no nível do SQL ou no nível do algoritmo

    • Tirando o PostgreSQL, na maioria dos bancos de dados que usei, a maior parte da otimização acontece no nível do algoritmo. Ou seja, escolhe-se o algoritmo ideal e a ordem de execução para uma consulta específica.
      Isso parece acontecer porque várias consultas SQL diferentes podem ser transformadas no mesmo “comando” ou plano de execução, e a própria semântica do SQL não oferece muito espaço para otimização no nível da linguagem.
      Como foi dito em outra resposta, uma das decisões importantes é se é possível trocar um scan completo da tabela por uma busca em índice ou um index scan.
      Por exemplo, se for necessário fazer um scan completo da tabela e for preciso realizar um cálculo considerável para cada linha para decidir se ela entra no conjunto de resultados, o otimizador pode trocar esse scan completo por um scan paralelo da tabela e mesclar os resultados de cada tarefa paralela.
      Ao escrever código de alto desempenho para compiladores, é preciso saber como o otimizador do compilador transforma o código-fonte em código de máquina. Assim, você pode preferir códigos que o otimizador trata bem e evitar padrões que geram código de máquina mais lento. No fim, o otimizador é programado para detectar e transformar certos padrões.
      Com o otimizador de consultas e os planos de execução acontece a mesma coisa. É preciso aprender quais padrões o otimizador de consultas do banco de dados que você usa consegue tratar para gerar planos de execução eficientes
    • Ele descreve todas as formas possíveis de executar o SQL e depois escolhe o plano mais rápido. Por exemplo, ao procurar a linha de um usuário cujo user_id é xx, ele escolhe entre ler a tabela inteira e filtrar ou usar uma estrutura de dados dedicada.
      Usando índice, é possível encontrar em tempo logarítmico em relação ao número de linhas. Além disso, há muitas outras possibilidades, como escolher a ordem dos joins, escolher a estratégia de join e empurrar condições de filtro para a origem. Esse é o amplo campo da otimização de SQL
    • Em um nível bem alto, o objetivo do planejador de consultas é minimizar o custo de ler dados do disco. Ele coleta estatísticas de colunas pré-calculadas, como número de linhas e quantidade de valores distintos, para estimar quantas linhas a consulta vai atingir.
      Com essas informações, ele define a ordem dos joins, escolhe índices e assim por diante. Joins podem ser executados com vários algoritmos, como hash, loop e merge. A opção mais barata varia conforme fatores como se um dos lados cabe na memória de trabalho, se ambos os lados já estão ordenados, por exemplo graças a um index scan, etc.
    • A otimização de consultas é o processo de escolher o algoritmo que fornece o resultado solicitado pelo SQL

  • Como o site parece estar fora do ar, dá para ver isto aqui: https://web.archive.org/web/20240417050840/https://rmarcus.i...