とあるバッチ処理の中で、実行結果が何時間経ってもまるで返ってこないクエリがありました。
SQL初心者の私には大きな課題でしたが、試行錯誤の末、そのクエリを約6秒で返せるほどにパフォーマンスを改善することができました！
この記事では、SQLの知識が浅い私がどのようにしてクエリのパフォーマンスを向上させたのか、その具体的なステップと方法についてご紹介します。
前提
筆者のパフォーマンス改善前のステータス
- 文系卒4年目
- 主にバックエンドエンジニア(Java)
- SQLについて
- 何も見ずにできるのはSELECTとJOINくらい。
- パフォーマンスを意識してクエリを書いたことがない。
- 実行計画が読めない。
検証DB
PostgreSQL 14
実装以外でやったこと
ボトルネックの特定
パフォーマンス改善の第一歩は敵を知ることです。
EXPLAINを用いて実行計画を取得し、どこに問題があるのかを明確にします。
ただし、何時間経っても結果が返ってこないクエリに対してEXPLAIN ANALYZEは使用できない点には注意が必要です。
ポイント： パフォーマンスが劣化したときと同条件のパラメータで計測することが重要です。今回は、毎時実行されるバッチ処理で特定の条件下でのみ問題が発生していたため、その条件を再現して実行計画を確認しました。その結果、ボトルネックとなっている箇所を特定することができました。
要件の再整理
クエリのパフォーマンス改善を行う際には改めてその処理が何をしたいのか、要件を確認することが非常に重要だと感じました。
要件を明確にすることで、改善の為にとれるアプローチの幅が広がるかもしれません。
ポイント： 問題の処理は結果として何が達成されていればよいのか？改善コストに対するリターンは？などを考えてみるとよいと思います。
数年に一度しか発生せず、手動復旧が容易で改善にあまりにもコストがかかる場合は、今すぐ修正しなくてもよさそうなど、判断が可能になります。
今回は下記のような状況だったため、設計単位で見直すのではなく、可能であればクエリの改善によるパフォーマンス向上を目指しました。
- 年に数回発生
- 手動復旧は可能だが面倒
- 特定のマイナー条件下でしか発生しない
- 再設計には大きなコストがかかる
実装でやったこと
①CTEの導入
CTEとは一言で説明すると
「WITH句によって、1つのクエリ内のために存在する一時テーブルを定義できる。」
です。
一言で記載させて頂きましたが、私が思うCTEを使うことのメリットは圧倒的可読性・保守性の向上です。
大抵の場合、問題になるクエリは長く複雑な巨大クエリだと思います。
そのようなクエリはボトルネックが特定できても、クエリを修正することは大きな壁に見えます。（少なくとも私にはそう見えました。）
CTEを利用することで、各処理を部品に分解することで役割・責任が明確になり処理に名前を付けられることに加え、ネストが浅くなることが期待できるので、圧倒的に可読性が向上します。
各処理を部品に分解することは ＝ 大きな問題を細分化すること です。
大きすぎて見えなかった答えも、小さな部品に分解して対応することで少しずつ改善に近づけることができました。
※元々PostgreSQLにおけるCTEは一時テーブルのような役割を果たし、そのクエリ結果セットをマテリアライズ化する仕組みでした。つまり、一度CTEを定義すると、その結果が一時的に保存され、後続のクエリで利用される形となります。
クエリの結果セットをマテリアライズ化することがオーバヘッドを生むケースがあるため、PostgreSQL 12で改善が入り、特定の条件下ではマテリアライズ化せず、問い合わせの中で自動的にサブクエリとして扱われるようになりました。
このため、ある程度マテリアライズ化の影響を考えずに可読性向上のためにCTEを使用しても問題はないように思います。
OR条件を撤廃し、別々で計算しUNIONで結合
実行計画を確認したところ、今回一番のボトルネックとなっていた箇所は下記のようにOR条件で複雑なサブクエリを計算していたことでした。
SELECT FROM hoge WHERE id IN (複雑な計算サブクエリ) OR id IN (複雑な計算サブクエリ)
このクエリ構造だけなら大きな問題にはならなかったかもしれませんが、さらにNested Loopが絡むことで、ループの回数分OR条件の評価が繰り返され、パフォーマンスが著しく低下してしまいました。
そこで、サブクエリを共通テーブル式（CTE）に分解し、それぞれ個別に計算した後、最後にUNIONで結合する方法を採用しました。
簡単な例を用いて解説します。
下図のようなテーブルがあり、teamid = 1 または departmentid = 1に所属するemployeeを取得したいとします。
各テーブルには100万件のデータを登録しております。
改善前のクエリを下記のようにします。
SELECT id FROM employees WHERE id IN (SELECT employeeid FROM team_members WHERE teamid = 1) OR id IN (SELECT employeeid FROM department_members WHERE departmentid = 1);
実行計画を見てみましょう。
QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Seq Scan on employees (cost=24265.66..45635.66 rows=750000 width=4) (actual time=57.466..209.304 rows=2 loops=1) Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2)) Rows Removed by Filter: 999998 SubPlan 1 -> Gather (cost=1000.00..12092.69 rows=4783 width=4) (actual time=0.444..57.407 rows=2 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on team_members (cost=0.00..10614.39 rows=1993 width=4) (actual time=17.353..36.245 rows=1 loops=3) Filter: (teamid = 1) Rows Removed by Filter: 333336 SubPlan 2 -> Gather (cost=1000.00..12147.69 rows=5333 width=4) (actual time=0.188..40.632 rows=2 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on department_members (cost=0.00..10614.39 rows=2222 width=4) (actual time=13.577..26.985 rows=1 loops=3) Filter: (departmentid = 1) Rows Removed by Filter: 333336 Planning Time: 0.435 ms Execution Time: 209.433 ms (19 rows)
一番上にSeq Scan on employeesとありますね。
employees.idにはBtree インデックスが張られているのに全件検索してしまっていることが分かると思います。
オプティマイザによる判断ですが、インデックスが張られているカラムのデータを2件取得するため100万件のデータをスキャンして99万9998件のデータを捨てるなんてもったいないですね。
上記のクエリをUNIONで書き換えると下記のようになります。
SELECT id FROM employees WHERE id IN ( SELECT employeeid FROM team_members WHERE teamid = 1 UNION SELECT employeeid FROM department_members WHERE departmentid = 1 );
同じように実行計画を見てみましょう。
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=23417.83..39076.05 rows=10116 width=4) (actual time=72.329..76.653 rows=2 loops=1) -> HashAggregate (cost=23417.40..23518.56 rows=10116 width=4) (actual time=72.262..76.582 rows=2 loops=1) Group Key: department_members.employeeid Batches: 1 Memory Usage: 409kB -> Gather (cost=1000.00..23392.11 rows=10116 width=4) (actual time=0.534..76.486 rows=4 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Append (cost=0.00..21380.51 rows=10116 width=4) (actual time=12.809..49.830 rows=1 loops=3) -> Parallel Seq Scan on department_members (cost=0.00..10614.39 rows=2222 width=4) (actual time=10.134..23.346 rows=1 loops=3) Filter: (departmentid = 1) Rows Removed by Filter: 333336 -> Parallel Seq Scan on team_members (cost=0.00..10614.39 rows=1993 width=4) (actual time=11.601..39.719 rows=1 loops=2) Filter: (teamid = 1) Rows Removed by Filter: 500004 -> Index Only Scan using employees_pkey on employees (cost=0.42..1.53 rows=1 width=4) (actual time=0.032..0.032 rows=1 loops=2) Index Cond: (id = department_members.employeeid) Heap Fetches: 0 Planning Time: 0.510 ms Execution Time: 77.058 ms (19 rows)
なんとemployeesテーブルのスキャンがIndex Only Scan using employees_pkey on employeesになりました！
Index Only ScanとIndex Scanは全く別ものですが、ここではスキャンのコストはIndex Only Scan > Index Scan > Seq Scanの順で安いくらいだと思っておきましょう。
実行時間も、209.433 ms → 77.058 ms で約1/3くらいになっていますね！
条件A OR 条件B のようなWHERE句は、条件Aを評価した後、基本的には条件Bも評価しないといけない為、条件の評価が複雑である場合やループが絡む場合、そのコストが無視できないほど大きくなることは想像に容易いと思います。
その上で、インデックスを張っているカラムに対してもインデックススキャンで選択されないケースがあるので、やはりWHERE句にOR条件を使用する際は慎重にならなければならないと思います。
今度はCTEで書き換えてみましょう。
WITH target_team AS ( SELECT employeeid FROM team_members WHERE teamid = 1 ), target_department AS ( SELECT employeeid FROM department_members WHERE departmentid = 1 ), target_employees AS ( SELECT employeeid FROM target_team UNION SELECT employeeid FROM target_department ) SELECT id FROM employees WHERE id IN (SELECT employeeid FROM target_employees );
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=23417.83..39076.05 rows=10116 width=4) (actual time=136.353..148.608 rows=2 loops=1) -> HashAggregate (cost=23417.40..23518.56 rows=10116 width=4) (actual time=136.291..148.539 rows=2 loops=1) Group Key: department_members.employeeid Batches: 1 Memory Usage: 409kB -> Gather (cost=1000.00..23392.11 rows=10116 width=4) (actual time=2.506..148.459 rows=4 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Append (cost=0.00..21380.51 rows=10116 width=4) (actual time=24.877..93.209 rows=1 loops=3) -> Parallel Seq Scan on department_members (cost=0.00..10614.39 rows=2222 width=4) (actual time=39.413..63.205 rows=1 loops=3) Filter: (departmentid = 1) Rows Removed by Filter: 333336 -> Parallel Seq Scan on team_members (cost=0.00..10614.39 rows=1993 width=4) (actual time=7.578..44.964 rows=1 loops=2) Filter: (teamid = 1) Rows Removed by Filter: 500004 -> Index Only Scan using employees_pkey on employees (cost=0.42..1.53 rows=1 width=4) (actual time=0.030..0.030 rows=1 loops=2) Index Cond: (id = department_members.employeeid) Heap Fetches: 0 Planning Time: 0.886 ms Execution Time: 149.007 ms (19 rows)
実行計画はCTEにする前とほぼ同じですが、WITH句で定義したクエリに名前が付き、それぞれの処理が何をしているのかが明確に分かるようになり、可読性が向上しました。
実行計画が変わっていないということは、前述で少し紹介した通りWITH句で定義したクエリは一時テーブルを作成せず、問い合わせの中でただのサブクエリに書き換えられて実行されたということを意味しています。
マテリアライズ化についてもみてみましょう。
自動でマテリアライズ化を判断してもらうには同じCTEを2回以上使用しなければならない為、下記はバカらしいクエリになりますが気にせず動作だけみてみましょう。
前回求めたtarget_employeesを求める際にも使用したtarget_departmentを使って、EXCEPTでtarget_employeesとtarget_departmentの差集合を求めてみます。
WITH target_team AS ( SELECT employeeid FROM team_members WHERE teamid = 1 ), target_department AS ( SELECT employeeid FROM department_members WHERE departmentid = 1 ), target_employees AS ( SELECT employeeid FROM target_team UNION SELECT employeeid FROM target_department ), except_target_department AS ( SELECT employeeid FROM target_employees EXCEPT SELECT employeeid FROM target_department ) SELECT id FROM employees WHERE id IN (SELECT employeeid FROM except_target_department );
実行計画を確認してみます。
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=24524.49..25965.90 rows=200 width=4) (actual time=81.878..85.944 rows=0 loops=1) CTE target_department -> Gather (cost=1000.00..12147.69 rows=5333 width=4) (actual time=0.424..38.759 rows=2 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on department_members (cost=0.00..10614.39 rows=2222 width=4) (actual time=9.871..21.239 rows=1 loops=3) Filter: (departmentid = 1) Rows Removed by Filter: 333336 -> Subquery Scan on except_target_department (cost=12376.38..12957.71 rows=200 width=4) (actual time=81.876..81.929 rows=0 loops=1) -> HashSetOp Except (cost=12376.38..12955.71 rows=200 width=8) (actual time=81.875..81.927 rows=0 loops=1) -> Append (cost=12376.38..12917.09 rows=15449 width=8) (actual time=81.831..81.919 rows=4 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=12376.38..12679.86 rows=10116 width=8) (actual time=81.830..81.913 rows=2 loops=1) -> HashAggregate (cost=12376.38..12477.54 rows=10116 width=4) (actual time=81.828..81.910 rows=2 loops=1) Group Key: team_members.employeeid Batches: 1 Memory Usage: 409kB -> Append (cost=1000.00..12351.09 rows=10116 width=4) (actual time=0.618..81.818 rows=4 loops=1) -> Gather (cost=1000.00..12092.69 rows=4783 width=4) (actual time=0.617..47.058 rows=2 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on team_members (cost=0.00..10614.39 rows=1993 width=4) (actual time=11.549..26.911 rows=1 loops=3) Filter: (teamid = 1) Rows Removed by Filter: 333336 -> CTE Scan on target_department (cost=0.00..106.66 rows=5333 width=4) (actual time=0.429..34.754 rows=2 loops=1) -> Subquery Scan on "*SELECT* 2" (cost=0.00..159.99 rows=5333 width=8) (actual time=0.002..0.003 rows=2 loops=1) -> CTE Scan on target_department target_department_1 (cost=0.00..106.66 rows=5333 width=4) (actual time=0.001..0.001 rows=2 loops=1) -> Index Only Scan using employees_pkey on employees (cost=0.42..4.30 rows=1 width=4) (never executed) Index Cond: (id = except_target_department.employeeid) Heap Fetches: 0 Planning Time: 0.504 ms Execution Time: 86.389 ms (30 rows)
CTE target_departmentというものが現れましたね。
これは、target_departmentを計算して一時テーブルを先に作成した。ということです。
以降に出てくるCTE Scan on target_departmentでは、その一時テーブルをスキャンすることを指しています。
マテリアライズ化されていなかったら100万件のデータが入ったdepartment_membersへのSeq Scanが2回必要になりますが、マテリアライズ化されたことで100万件のSeq Scanは1回で、その後は必要なデータだけが入ったtarget_departmentへのアクセスでよくなりました。
マテリアライズ は手動で指定することも可能です。
ASの後ろにオプションを指定します。
CTE hoge AS (NOT MATERIALIZED | MATERIALIZED) ( クエリ );
マテリアライズを無効にしてみましょう。
WITH target_team AS ( SELECT employeeid FROM team_members WHERE teamid = 1 ), target_department AS NOT MATERIALIZED ( SELECT employeeid FROM department_members WHERE departmentid = 1 ), target_employees AS ( SELECT employeeid FROM target_team UNION SELECT employeeid FROM target_department ), except_target_department AS ( SELECT employeeid FROM target_employees EXCEPT SELECT employeeid FROM target_department ) SELECT id FROM employees WHERE id IN (SELECT employeeid FROM except_target_department );
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=23417.83..36900.26 rows=200 width=4) (actual time=111.308..115.896 rows=0 loops=1) -> Subquery Scan on except_target_department (cost=23417.40..36039.76 rows=200 width=4) (actual time=111.306..115.893 rows=0 loops=1) -> HashSetOp Except (cost=23417.40..36037.76 rows=200 width=8) (actual time=111.305..115.891 rows=0 loops=1) -> Append (cost=23417.40..35999.14 rows=15449 width=8) (actual time=74.509..115.883 rows=4 loops=1) -> Subquery Scan on "*SELECT* 1" (cost=23417.40..23720.88 rows=10116 width=8) (actual time=74.508..74.607 rows=2 loops=1) -> HashAggregate (cost=23417.40..23518.56 rows=10116 width=4) (actual time=74.506..74.604 rows=2 loops=1) Group Key: department_members.employeeid Batches: 1 Memory Usage: 409kB -> Gather (cost=1000.00..23392.11 rows=10116 width=4) (actual time=0.862..74.510 rows=4 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Append (cost=0.00..21380.51 rows=10116 width=4) (actual time=15.360..55.096 rows=1 loops=3) -> Parallel Seq Scan on department_members (cost=0.00..10614.39 rows=2222 width=4) (actual time=9.786..25.092 rows=1 loops=3) Filter: (departmentid = 1) Rows Removed by Filter: 333336 -> Parallel Seq Scan on team_members (cost=0.00..10614.39 rows=1993 width=4) (actual time=15.682..44.997 rows=1 loops=2) Filter: (teamid = 1) Rows Removed by Filter: 500004 -> Subquery Scan on "*SELECT* 2" (cost=1000.00..12201.02 rows=5333 width=8) (actual time=0.436..41.270 rows=2 loops=1) -> Gather (cost=1000.00..12147.69 rows=5333 width=4) (actual time=0.435..41.267 rows=2 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on department_members department_members_1 (cost=0.00..10614.39 rows=2222 width=4) (actual time=10.215..22.277 rows=1 loops=3) Filter: (departmentid = 1) Rows Removed by Filter: 333336 -> Index Only Scan using employees_pkey on employees (cost=0.42..4.30 rows=1 width=4) (never executed) Index Cond: (id = except_target_department.employeeid) Heap Fetches: 0 Planning Time: 1.142 ms Execution Time: 116.425 ms (30 rows)
CTE target_departmentが消え、Seq Scan on department_members department_membersが2回現れたことが分かります。
自動のマテリアライズ化がオーバヘッドを生む場合は、手動での調整も試みてください！
最後に
パフォーマンスの改善は、練習したくても実際のテスト環境を作るのが難しかったりします。
最初は実行計画もパフォーマンス改善手法も全く分からなかった私でも、問題を細分化し1つずつ対応することで実際にパフォーマンスを改善することができたので、もしパフォーマンス改善に興味がある方は、そのようなタスクが転がっていたときは勇気を出して手を挙げてみましょう！
少しでも背中を押すことができれば幸いです！
大原 敦（オオハラ アツシ）寝つきが悪いせいで人生の100分の1くらいは無駄にしている気がします。
