これは、FORCIA Advent Calendar 2021の3日目の記事です。
どうもこんにちは。
タブをスペースに変換するのを忘れてpsqlに怒られがちな新卒2年目の駆け出しエンジニア吉田です。（Web業界に2年近くもいて「駆け出し」を名乗ってよいものか微妙ですが、強気に主張していきます）
私はここ一年間、大規模旅行アプリの開発に携わってきました。旅行アプリの検索機能は、フォルシアの主力事業であるSpookが最も威力を発揮する領域でもあります。
Spookは「膨大で複雑なデータ」を「高速」に検索するための技術基盤です。（参考： https://www.forcia.com/technology/#spooks ）
参考記事にもある通り、Spookの高速検索は「独自の検索最適化技術」と「これまで培ってきたノウハウ」の合わせ技の上に成り立つものです。技術とノウハウを駆使して高速なDB検索をすることがフォルシアエンジニアの仕事の一つ…
なのですが、駆け出しエンジニアの私にはまだノウハウが備わっておらず、自分の実装した部分はデータ量を増やすととても重い、検索に失敗するなどの問題が次々に勃発…
これを解決すべく、私はかなりの期間クエリとにらめっこし、修正やチューニングに時間を割くことになりました。
本記事は、そんな私がこの一年間クエリ改善活動を行う中ではまった、SQLの「沼」についてまとめたものになります。ドがつくほどの基本的な内容から笑える（笑えない）ミスまで5選用意しましたので、誰かの何かの参考になれば幸いです。
※「SQLの」と題していますが、私がアサインされたプロジェクトで利用しているPostgreSQL 12系（以後postgresと記述します）での話となるため、RDBMS全体に拡張できない話が含まれることをご承知おきください。
hash joinだから大丈夫！と思っていたら…？
問題
ある日、いつものようにスロークエリをexplain analyzeしていたところ、どうやらテーブルの結合処理（join）に問題があるらしいことがわかりました。
postgresの結合方式はnested loop join, merge join, hash joinの3種類あり、プランナがその時々に応じて最適な結合方式を選んで実行するのですが、今回のケースではhash joinが選択されているようでした。
原因
hash joinは
1. 内部表の結合キーのハッシュテーブルを作成し
2. 外部表の結合キーのハッシュを突き合わせる
という方式で実行されるため、内外それぞれ1回だけテーブルが走査されることになり、インデックスの有無にかかわらずある程度速いはずの結合方式になります。
一方でハッシュテーブルのサイズが利用可能なメモリより大きくなってしまう場合、ディスク書き出しが発生し非常に遅くなる可能性もあります。
今回のケースはまさにこれがスロークエリの原因でした。初期開発時はレコード数が小さく無視できていた非効率な処理が、試験時にまとまったデータが用意されたことで顕在化した形になります。
無駄な結合をなくす、結合前に絞り込んで対象レコード数を減らす、などの対応でこれらは解決できました。
set句を付けたのに反映されない！
問題
SQLにはset句というものがあり、実行時のパラメータを制御できます。
例えばひとつ前の問題でプランナが選択する結合方式の話をしましたが、 例えばクエリの実行前に set enable_nestloop to off; と記述することでnested loop join以外の結合方式を強要する、というような使い方ができます。
何らかの理由でプランナが最適な実行計画を選んでくれない状態になった場合など、応急処置的にset句を付与することはよくあり、私のアプリでも何箇所かに利用されています。
ところがある日、いつものようにスロークエリをexplain analyzeしていたところ、set句を付与したのにもかかわらずそれがクエリに反映されていない、という事態に気付いてしまいました。
原因
結論としては、「set句とset句を付与したいクエリが別のトランザクションで実行されていた」ことが理由でした。
set句には session/local というパラメータが指定でき、それぞれset句を同一セッションまたは同一トランザクションに限定できます。
今回のケースではクエリを実行する前に set local ~ という形でパラメータ制御を付与しており、これが記述されたファイルをpsqlというpostgres公式のクライアント経由で実行していました。
psqlから外部SQLを実行する場合には psql -f (ファイル名) というようなコマンドを実行します。ここで実行されるSQL文は単一トランザクションで実行されないため、SQLファイル先頭のset句とクエリが異なるトランザクションで実行されてしまっていまい結果としてset句の制御がクエリに適用されない、という現象が起きていました。
解決策としては set session とする、クエリを begin; ~ commit; で囲って同一トランザクション内で実行する、psqlの実行オプションに --single-transaction を指定する、など色々あります。
※完全に余談ですが、「nested loop joinは完全にoffにできない」ことが理由の場合も（まれですが）考えられます。set enable_nestloop to off; とした場合の挙動は、nested loop joinのcostを極端に大きくすることで選ばれなくする、というもののため、それでも選ばれてしまう可能性はあります（例えばenable_nestloop, enable_hashjoin, enable_mergejoinを全てoffにするとnested loop joinが選択されます）。
どうしてそんなにのろいのか join編
さっきからこの人joinの話しかしてないですね。
問題
ある日、いつものようにスロークエリをexplain analyzeしていたところ、大量のテーブルをjoinする部分に時間がかかっていることがわかりました。
今回のケースでは各テーブルのレコード数はそこまで多くなく、結合した結果が膨れ上がるようには思えないテーブル条件にもかかわらず、条件から考えて不可解な時間がかかっていました。
原因
基本的にjoinの順序はpostgresのプランナが最適なものを選んでくれるため、SQL文を書く際にあまり結合順は意識しません。
しかし、結合順序を計算するコストが高過ぎる場合はSQLの記述順に結合されます（基本的に全探索で最もコストの低い結合順が選ばれるため、計算コストは指数オーダーで増えます）。
この「コストが高すぎる」判定として join_collapse_limit というパラメータが使用されており、最終的に結合順の計算対象となるテーブル数がこの値を超えた場合は結合コストの計算が行われず記述順に結合されます（デフォルトでは8となります）。
今回の対応としては、試しによさげな結合順を試してみたところ想定通りの速度でクエリが返ってくるようになったため、 join_collapse_limit の設定値は変更せずクエリ側の修正のみで解決できました。
どうしてそんなにのろいのか and編
問題
ある日、いつものようにスロークエリをexplain analyzeしていたわけではないのですが、試験中にアプリを操作していたところ、とても簡単な条件なのにレスポンスが時間内に返ってこないという問題が発生していました。
原因
いつものようにスロークエリをexplain analyzeしようと思ってクエリをログに出力してみたのですが、その結果
select (columns) from table where column > 0 and column > 0 and column > 0 and column > 0 and column > 0 and column > 0 and column > 0 and ...
というクエリが（アプリのバグによって）発行されていることが判明しました。
今回のケースはアプリのバグを修正して事なきを得たのですが、後々気になっていつものようにスロークエリをexplain analyzeで調べたところ、（当たり前と言われればそうですが）同じ条件であっても記述された回数評価されているようだ、ということがわかりました。
極端すぎる例だったかもしれませんが、条件の重複がクエリパフォーマンスの悪化を招いている可能性はアプリのロジックだけを追っていると意外と気付きにくいのではないか、という点ではよい教訓になりました。
計算できるレコードに絞ったのに計算できないぞ？
問題
ある日、いつものようにスロークエリをexplain analyzeしようと思って遅いAPIにリクエストを投げたところ、500エラーが返ってきました。
このリクエストでは以下のようなクエリを実行していました。
-- Step.1: where句で計算が可能なレコードを絞る
with target as (
select id from table
where [keisan is dekiru]
)
-- Step.2: 計算が可能なレコードに対して実際に料金を計算する
,calc as (
select
id
,[keisan] as calc
from plan
inner join target using (id)
)
-- Step.3: Step.1 に Step.2 を結合する
select
id
,calc
from target
left join calc using (id)
エラーログを確認したところ、最初にwith句で計算可能なレコードに絞ったにもかかわらず「計算できないレコードに対して計算しようとしている」ということがわかりました。
原因
今回のケースの特徴は、異なるCTEで何度も同じテーブルを参照するような作りになっている点です。
postgresのプランナが最もコストの小さい実行計画を作る際、このカラムは同じタイミングで取るのが最も適当だと判断した場合は副問い合わせをまたいだ最適化が行われる可能性があります。
また、postgres 12からはCTEのmaterialize（一時テーブルの実体を生成する処理）がデフォルトでoffとなっており、CTEをまたいだクエリの最適化が行われる可能性があります。
さらに今回の場合、Step.1とStep.2のレコードは一対一対応しているにもかかわらず、Step.3で内部結合していない点も問題でした。
これらの要素が重なった結果、Step.2のpriceを計算する部分が先に評価されてしまい、Step.1の絞り込み対象外のレコードに対して実行された結果エラーとなったことがわかりました。
要するにこの記事を通して私が言いたかったことは
クエリチューニングをする際はexplain analyzeをしましょう！ということです。
SQLにはexplainという実行計画を問い合わせる句がありますが、postgresの場合はanalyzeというパラメータを付与することで、実際にクエリを実行した結果の情報も含めて取得できます。
今回の例では紹介しませんでしたが、例えば実行計画で見積もった行数と実際に取得できた行数に乖離がある場合などは、プランナの利用する統計情報が実態に即していないことがスロークエリの原因になっていることがわかったりもします。
十八史略にも「先づexplain analyzeより始めよ」とある通り、クエリ改善における最初の一手はexplain analyzeです。迷ったらexplain analyze。困ったらexplain analyze。何はともあれexplain analyze。
そんなわけで、今日も元気にスロークエリをexplain analyzeしてこようと思います。皆さんもよき検索ライフを！
この記事を書いた人
吉田 侑弥
2020年度新卒入社エンジニア
サブクエリのエイリアスもよく忘れて怒られます。
Discussion