チューニング ~ 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件出力します。

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実行で性能に問題が発生した場合は、実行計画を分析し適切な処理となるようにチューニングを実施してください。

2019年4月19日公開

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

お電話でのお問い合わせ

Webでのお問い合わせ

当社はセキュリティ保護の観点からSSL技術を使用しております。

ページの先頭へ