はじめに

直近開発した内容でPostgresQLにバックエンド経由で流しているスロークエリを改善する作業があった。
UIを経由して複数の検索条件を設定しバックエンドで条件分岐しながらクエリを組み立てていく処理が実装されていた。 特定の条件で検索すると30秒ほどかかってしまい、タイムアウトしてしまう問題があった。
当然ChatGPTやClaude Codeに頼り切って解決に至ったのだが、その際に自分が考えていたことについて振り返っていく。

解決に至った流れ

• Alloy DBのQuery Insightを利用して該当のスロークエリを特定※1
  • この時点で特定条件部分のNested Loopにボトルネックがあることはうっすら確認できた
• スロークエリを組み立てているバックエンドの処理と選択条件を決めているUIのソースコードを特定※2
• スロークエリのプレースホルダ部分を実際の値に差し替えて本来流れた形を復元
• localなDBでデータ量を増やした状態で実行計画を確認し、AIと相談しながら解決策を複数立案
• 簡単な順に検証
  • 運良く2個目の解決案で大きなコードの手直しを加えることなく20倍程度のパフォーマンスの改善ができたため解決
• PRの準備やリグレッションが入らないように準備
  • SQLの実行結果が修正前後で変わっていないことを確認する検算用のSQLを作成して確認 など

ここからは印象的な点について述べていく。

そもそも自分がPostgresQLのことをうっすらとしかわかっていなかった

本格的にスロークエリを解決するタスクを持ったのが初めてだったのもあり、今までDBの深い知識が求められてこなかった。
実行計画の読み方やJOIN戦略、ANALYZEやVACUUMの仕様、相関サブクエリといった言葉がAIからバンバン出てくる。 自分が理解していないままPRにしてしまうのは勿体無いし責任感に欠けると感じたので、1から教えてもらったり公式の仕様を調べたりした。
バイブコーディング的に進めることももちろんできるだろう。 しかしDBのチューニングの機会は今後ももらえるようになりたいし、効果がはっきり出るので成果のアピールもしやすい。 自分のスキルとして持っておきたいのでしっかり調べることにした。
専門用語が知っている言葉になってからは解決に至るのが早く、やっぱり自力をつけるのが一番大事だと実感する。

今回のスロークエリになっていた原因と解決に至った修正方法

特定条件をCTEでまとめて、後から相関サブクエリとして検索条件に利用するパターンが採用されていた。 相関サブクエリに対して外側のテーブルの行数分の走査が走ってしまい、件数の増加に伴って実行時間が伸びていた。
1回で済ませるために相関サブクエリ部分をCTE + LEFT JOINな方法に差し替えることで解決した。
調査中、近い内容を解説したZennの記事を見つけて、コレコレコレってなり理解がいっきに進んだ。
https://zenn.dev/kou_row_line/articles/6acb7d888c9f32749c41

さいごに

書いていて思ったけど、SQLの内容を説明するのに足る理解は多分できていない。
これをきっかけにDB関連の本を読みはじめようかな。

備考

※1 この時点ではQuery Insightで確認できるSQLはバックエンドのプレースホルダが置き換えられる前のSQLであり、実際にどの値が使用されるのかはわからない。
※2 面倒なのでSQLをCodex CLIに教えて特定してもらった。