チューニング ～ SQLチューニングを実施する ～
PostgreSQLインサイド

SQLチューニングは、SQL実行において、処理に時間がかかっているSQLを対象に内部処理を解析し、最適な動作に改善していくことを目的としています。ここでは、サンプルを使用してSQLチューニングを実施し、その流れを解説します。

1. テーブルのスキャン方法と結合方法

SQLチューニングでは、SQLの実行計画を解析する必要があります。SQLチューニングを実施する前に、実行計画の解析で前提となるテーブルのスキャン方法と結合方法を簡単に説明します。

スキャン方法

テーブルへのアクセス方法です。代表的なスキャン方法には、以下があります。

• Seq Scan（シーケンシャルスキャン）
  テーブルを最初から最後までシーケンシャルにアクセスします。取り出す件数が多い場合に有効な方法です。
• Index Scan（インデックススキャン）
  インデックスとテーブルを交互にランダムアクセスします。WHERE句による絞り込みにより取り出す件数が少ない場合や目的のデータにピンポイントでアクセスしたい場合に有効な方法です。

結合方法

2つのテーブルを結合する場合の結合方法です。2つのテーブルのスキャン結果を入力として、1つの結果を出力します。代表的な結合方法には、以下があります。ここでは、結合対象の2つのテーブルを区別するため「外側テーブル」と「内側テーブル」と呼びます。

• Nested Loop
  
• Merge Join
  
• Hash Join
  

• Nested Loop（ネステッドループ結合）
  外側テーブル1行ごとに内側テーブルを1周ループしながら結合する方法です。外側テーブルの行数が少なく、内側テーブルにインデックスがある場合に処理が高速になります。
• Merge Join（マージ結合）
  2つのテーブルを結合キーでソート後、順番に突き合わせて結合する方法です。ソートに時間がかかる場合は有効な方法ではありません。結合キーが主キーである、または結合キーにインデックスを定義することでソート済みの状態を突き合わせることになるため、処理が高速になります。大きなテーブル同士を結合する場合に有効な方法です。
• Hash Join（ハッシュ結合）
  内側テーブルの結合キーでハッシュを作成し、ハッシュと外側テーブルの行を突き合わせて結合する方法です。ハッシュはメモリーに作成するため、一度作成してしまえば、高速に結合できます。ただし、ハッシュがメモリーサイズより大きくなってしまう場合は、ファイルアクセスが発生するため処理が遅くなります。比較的小さなテーブルと大きなテーブルを結合する場合に有効な方法です。

2. 処理が遅いSQLを検出

統計情報ビューとサーバーログから、処理が遅いSQLを検出します。

統計情報ビューを利用して検出する

pg_stat_statementsビューに対して以下のSQLを実行し、実行時間が長いSQLを、時間がかかっている順に3件出力します。なお、PostgreSQL 13からは、total_timeをtotal_exec_timeに置き換えて実行してください。

SELECT query, calls, CAST(total_time as numeric(10,3)), CAST(total_time/calls AS numeric(10,3)) AS avg_time FROM pg_stat_statements ORDER BY avg_time DESC,calls LIMIT 3;

出力内容

pg_stat_statementsビューの内容から、salesテーブルとpriceテーブルを検索するSQLの実行で時間がかかっていることがわかります。

参考

クエリ統計情報（pg_stat_statements）以外にも、プランナ統計情報（pg_statistic）や関数統計情報（pg_stat_user_functions）を定期的に取得しておくことで、性能劣化が発生した際の分析に利用できます。

サーバーログを確認する

統計情報ビューで平均6秒かかっているSQLが検出できたため、次に、サーバーログを利用して3秒以上かかっているSQLを確認してみます（postgresql.confのlog_min_duration_statementパラメーターを3秒に設定）。

統計情報ビューを利用した場合と同様に、salesテーブルとpriceテーブルを検索するSQLの実行で時間がかかっていることがわかります。

参考

サーバーログでは、個々のSQLについて、実際にどれくらいの時間（duration）がかかっているか、また、呼び出し元のユーザー（user）、データベース（db）、アプリケーション（app）などを確認することができます。ただし、これらの情報がログに出力されるよう、事前にpostgresql.confのlog_min_duration_statementパラメーターやlog_line_prefixパラメーターを設定する必要があります。

3. 原因を調査

salesテーブルとpriceテーブルに対する検索処理で、なぜ時間がかかっているのかを調べてみましょう。

実行計画を表示する

まず、何が原因となっているのかを調査するためにEXPLAINコマンドを実行して、対象SQLの実行計画を確認します。ANALYZEオプションを付けて実際にSQLを実行し、その実測値もあわせて確認してみます。ANALYZEオプションを使用すると実際にSQL文が実行されます。INSERT文やDELETE文などの実行計画を表示する際は、BEGIN文とROLLBACK文を使用してデータに影響を与えないようにしてください。

実行計画から、以下がわかります。また、最終的な実行時間は、16529.869msとなっています。

• （1）salesテーブルとpriceテーブルの結合方法として「Merge Join」が選択されている。
• （2）salesテーブルのソート処理が「外部ソート（external merge：ソート結果を外部ファイルに書き出すソート）」になっており、処理に時間がかかっている。その結果、マージ結合の処理時間が長くなってしまっている。
• （3）salesテーブルとpriceテーブルのスキャン方法に、「Seq Scan」が選択されている。
• （4）推測値と実測値のrowsがほぼ一致しているため、統計情報は最新化されている。
• （5）priceテーブルは、「quicksort（メモリー上のソート）」が選択されているため、メモリー内（74KB使用）で高速に処理されている。

まず、チューニングポイントとして、actual timeを参考に、どの処理に時間がかかっているかを確認します。上記（4）,（5）については特に問題がないことが確認できますが、（1）,（2）,（3）からは、結合処理に時間がかかっていると分析できます。そこで、マージ結合が適切な結合であるか、またその際の処理内容を改善できないか検討します。

参考

（2）では、postgesql.confのwork_memパラメーターの値以上のメモリーをソートで使用しているために外部ソートとなっています。この場合、以下を実施し、work_memパラメーターの値をDisk（108648KB）より大きくすることでソート方式をquicksortに変えて処理を高速化するといった対策も考えられます。

• サーバー側でpostgesql.confファイルを変更
• クライアント側で環境変数PGOPTIONSを使用して変更
• クライアント側でset文を使用して変更

サーバー側のpostgresql.confファイルを変更すると、データベースクラスタ全体に対して変更が有効になり、使用メモリー量の大幅な増加が考えられます。このため、できるだけクライアント側での変更を選択し、影響範囲を局所化してください。

データ件数を確認する

salesテーブルとpriceテーブルのデータ件数を確認します。

salesテーブルには500万件、priceテーブルには1000件のデータが登録されていました。今回のケースでは、データベース環境に対して比較的大きなテーブル同士を結合しており、マージ結合が妥当な結合方法であると推測できます。

テーブル定義を確認する

次に、salesテーブルとpriceテーブルのテーブル定義を出力し、WHERE句の条件で指定された結合キーである“name”の定義内容を確認します。

上記から、priceテーブルでは、主キーである列“name”がインデックスとなっていることがわかります。しかし、salesテーブルは大きなテーブルであるにも関わらず、結合キーにインデックスが定義されていません。マージ結合の処理において、結合キーにインデックスを使用していないためソートに時間がかかっていると推測できます。

4. チューニング

「3. 原因を調査」での調査結果から、インデックスを追加し、性能が改善するかを確認します。

インデックスを追加する

salesテーブルの列“name”に対して、インデックス“sales_name_idx”を追加します。

実行計画を表示する

再度、実行計画を表示してみます。

マージ結合の事前処理として実行されていたソート処理とそれに伴う「Seq Scan」が、インデックス“sales_name_idx”を利用した「Index Scan」に変更されています。これにより、マージ結合にかかる時間が短くなり（インデックス追加前：13546.656ms、インデックス追加後：7972.011ms）、全体として、実行時間（Execution time）が短縮されました（インデックス追加前：16529.869ms、インデックス追加後：9975.771ms）。
また、EXPLAIN ANALYZEの実行時間（Execution time）は、「サーバーログを確認する」で確認したSQLの実行時間（duration）と異なる場合があります。「サーバーログを確認する」では、6518.096msとなっていましたので、チューニング後のサーバーログでもSQLの実行時間（duration）を確認してみます。（postgresql.confのlog_min_duration_statementパラメーターを0（すべてを出力）に設定）

durationが1479.164msであり、サーバーログからもSQLの実行時間が短縮されたことがわかります。

ここでは、実際にチューニングを行いながら、SQLチューニングを説明しました。SQL実行で性能に問題が発生した場合は、実行計画を分析し適切な処理となるようにチューニングを実施してください。

2021年1月22日更新

こちらもおすすめ

• チューニング ～ データベースチューニング ～
• チューニング ～ SQLチューニングの概要 ～
• pg_hint_planで実行計画を制御する
• pg_dbms_statsで統計情報を固定化して、実行計画を制御する

本コンテンツに関するお問い合わせ