Postgresクエリを1,000倍高速化する方法

• MattermostはElasticsearchを使用してデータベース負荷を下げ、はるかに高速な検索結果を提供している
  • Elasticsearchが正しく動作するには、検索対象となるすべてのデータをインデックス化する必要がある
  • すでにインデックス化されたデータについては、その後の新しい投稿やファイルのインデックス作業はかなり速い
  • しかし、非常に大きなデータベース（1億件の投稿）を最初から完全にインデックス化するのは非常に遅く、18時間経っても半分も終わらず、しかも次第に遅くなっていた
• データベース呼び出しごとの所要時間グラフから、PostStore.GetPostsBatchForIndexingメソッドのSQLクエリが問題だと特定された
  • このクエリは基本的に作成タイムスタンプ基準で投稿を並べ替え、指定されたタイムスタンプより新しいN件の投稿を返す
  • このクエリを繰り返し実行し、すべての投稿がインデックス化されるまでindexing jobが実行される
• EXPLAIN (ANALYZE, BUFFERS)を使ってクエリ実行計画を分析した:
  • Postsテーブルでインデックススキャンを実行する際、Filter条件を適用するために4,000万ブロックを処理していた（309GB）
  • ChannelsテーブルとのJOINは問題ではなかった
  • WHERE条件のOR句のうち Posts.CreateAt > ?1 の部分だけを適用すると、はるかに高速になった（30ms）
  • そこに Posts.CreateAt = ?1 AND Posts.Id > ?2 条件を適用すると、さらに極めて高速になった（0.047ms）
• 原因の特定:
  • 元のクエリはPostsのすべての行を走査してFilterで絞り込んでいたのに対し、修正後のクエリはインデックスだけを確認して必要な行だけを取り出していた
  • 時間が経つにつれてクエリがどんどん遅くなった理由は、除外しなければならない行が増え続けたためだった
• 解決策:
  • PostgreSQLのrow constructor comparison機能を活用し、条件を (Posts.CreateAt, Posts.Id) > (?1, ?2) に変更した
  • この変更後のクエリでは実行時間が34ミリ秒まで大幅に短縮された
  • ただしMySQLでは変更後のクエリのほうがむしろ遅く動作した。MySQLでは元のクエリのほうが速かったため、DBごとに異なるクエリを使うようコードを分岐処理した
• 学んだこと:
  • EXPLAINではBUFFERSオプションを常に使うこと
  • FilterではなくIndex Condを活用すること
  • PostgreSQLとMySQLはほぼ常に異なる動作をすることを前提にすること
• 結論
  • 最適化によってクエリ実行時間を1,000倍以上短縮できた
  • この最適化はMattermost v9.7.0およびv9.5 ESRバージョンに反映された
  • この最適化作業から多くを学ぶことができた

[dontcryme]

最後の文章にもありますが、この記事のタイトルは少し釣りっぽい感じなので……もう少し実質的に言い換えるなら

「失敗を通じて学んだ PostgreSQL 活用事例」

でしょうか..

[vwjdalsgkv]

うーん……個人的には、このレベルの文章を特定の会社や製品を前面に出して書くのであれば、むしろその製品に対する信頼度はかなり下がってしまう気がします。
整理は明快ですが、その中に込められた技術的な価値がやや不足しているように見えて、少し残念ですね。

[savvykang]

私もこの記事を見て、むしろ信頼性が下がりました。有料で販売している製品なのに、大規模処理のテストもなしに機能をリリースしたということですから。あれほど簡単なインデックスは、機能開発の段階で設定しておくべきではないかと思います。ソフトウェア開発の手順がかなり省略されているように見えます。