Tornando uma consulta do Postgres 1.000 vezes mais rápida

• A Mattermost usa Elasticsearch para reduzir a carga no banco de dados e fornecer resultados de busca muito mais rápidos
  • Para o Elasticsearch funcionar corretamente, é preciso indexar todos os dados que serão pesquisados
  • Para dados já indexados, o trabalho de indexação de novos posts e arquivos depois disso é bastante rápido
  • Porém, indexar completamente do zero um banco de dados muito grande (100 milhões de posts) é muito lento (nem chegou à metade em 18 horas e foi ficando cada vez mais lento)
• Por meio de um gráfico do tempo gasto por chamada ao banco de dados, foi identificado que a consulta SQL do método PostStore.GetPostsBatchForIndexing era o problema
  • Essa consulta basicamente ordena os posts pelo timestamp de criação e retorna os N posts mais novos do que um determinado timestamp
  • O job de indexação executa essa consulta repetidamente até que todos os posts sejam indexados
• A análise do plano de execução da consulta com EXPLAIN (ANALYZE, BUFFERS) mostrou:
  • Ao executar um index scan na tabela Posts, 40 milhões de blocos estavam sendo processados para aplicar a condição de Filter (309GB)
  • O JOIN com a tabela Channels não era o problema
  • Se fosse aplicada apenas a parte Posts.CreateAt > ?1 da cláusula OR do WHERE, a consulta ficava muito mais rápida (30ms)
  • A partir daí, ao aplicar a condição Posts.CreateAt = ?1 AND Posts.Id > ?2, ela ficava extremamente rápida (0,047ms)
• Identificação da causa:
  • A consulta original percorria todas as linhas de Posts e filtrava com Filter, enquanto a consulta modificada verificava apenas o índice e extraía somente as linhas necessárias
  • O motivo de a consulta ficar cada vez mais lenta com o tempo era que ela precisava filtrar um número cada vez maior de linhas
• Solução:
  • Foi usado o recurso de row constructor comparison do PostgreSQL, mudando a condição para (Posts.CreateAt, Posts.Id) > (?1, ?2)
  • Com essa mudança, o tempo de execução da consulta caiu drasticamente para 34 milissegundos
  • Porém, no MySQL, a consulta alterada acabou ficando ainda mais lenta. Como no MySQL a consulta original era mais rápida, o código passou a usar consultas diferentes para cada banco
• Lições aprendidas:
  • Sempre usar a opção BUFFERS com EXPLAIN
  • Fazer uso de Index Cond em vez de Filter
  • Partir do princípio de que PostgreSQL e MySQL quase sempre se comportam de forma diferente
• Conclusão
  • Com a otimização, foi possível reduzir o tempo de execução da consulta em mais de 1000 vezes
  • Essa otimização foi incluída no Mattermost v9.7.0 e na versão v9.5 ESR
  • Foi possível aprender muita coisa com esse trabalho de otimização

[dontcryme]

Como também está no último texto, o título deste artigo parece um pouco caça-cliques... mas, para deixá-lo mais prático, talvez fosse algo como

'casos de uso do PostgreSQL aprendidos por meio de erros'

?

[vwjdalsgkv]

Hum... pessoalmente, se alguém escrevesse um texto desse nível associando-o a uma empresa/produto específico, acho que isso acabaria reduzindo bastante a minha confiança nesse produto.
A organização está bem clara, mas é uma pena que o valor técnico contido nele pareça um pouco insuficiente.

[savvykang]

Depois de ler este texto, na verdade minha confiança diminuiu ainda mais. Afinal, eles lançaram uma funcionalidade de um produto que vendem sem nem sequer fazer testes de processamento em grande volume. Acho que um índice simples nesse nível deveria ter sido configurado já na etapa de desenvolvimento da funcionalidade. Parece que muitas etapas do processo de desenvolvimento de software foram ignoradas.