パフォーマンスチューニング9つの技 ～「探し」について～

ここで、実際のパフォーマンスチューニングをする上での基礎知識として、インデックスを定義することによる効果と、プランナーが作成する実行計画の見かたについて説明します。

テーブルにインデックスを定義した場合は、指定されたカラムのデータを基にソートした順でインデックスが作成され、それらのデータがテーブルのどこにあるのかを記憶します。必要なデータへアクセスする際には、インデックスを利用することで、ソートしたデータの中から該当のレコードを探せるため、アクセスしなければならないデータ量を減らし、検索処理を高速化することができます。

ただし、高速化のためにインデックスを大量に定義し過ぎると、データ格納の際に大量のインデックスを更新しなければならないため、データ格納においてパフォーマンス劣化の問題が発生します。また、インデックス単体においても、インデックスのカラム数が多くなると更新時に時間を費やします。そのため、データ格納においてパフォーマンス問題が発生した場合は、インデックスに定義するカラムを必要最低限に減らすなどの検討も必要になるかもしれません。したがって、インデックス定義は、パフォーマンスを改善したいSQLで利用されているテーブルのカラムに対して優先的に行い、また複数のSQLから有効に利用できるようにするなど、インデックスを作り過ぎないことも検討してください。

例えば、カラムC1、C2、C3に対する条件を記述したSQLと、カラムC1、C2、C4に対する条件を記述したSQLがある場合、C1、C2、C3、C4のインデックスを作成し、両方のSQLでそのインデックスを利用することで高速に処理されるようになります。なお、カラムC3、C4のデータのばらつきが小さく（カーディナリティーが低い）、条件で絞り込みがほとんどできない場合は、C1、C2のインデックスのみを作成して両方のSQLで利用可能なインデックスを定義することで、データ格納時の速度も考慮した対策になります。

インデックスを定義するかどうかの判断については、性能要件に基づいてパフォーマンスを満たしていないSQLがあれば、データ格納時におけるパフォーマンス劣化の問題を考慮しながらインデックスを定義していく必要があります。一般的にインデックスを定義したほうが良いテーブルは、以下のようなケースがあります。

なお、これらに該当する場合でも、実際にWHERE句や結合条件としてほとんど使われないカラムへのインデックス定義や、データが頻繁に書き換わってインデックス更新がネックになるようなテーブルについては、インデックス利用が適さないことがあるため、十分に検討する必要があります。

はじめに、各ノードの1行目の情報の意味について示します。

次に、各ノードの処理に費やした時間について見てみます。

A）大きく木構造になっており、インデントで上下の関係を表しています。
この実行例では、上位ノード「Nested Loop」の下に、2つの下位ノード「Seq Scan」、「Index Scan」があります。
B）actual timeのそれぞれの値は、上位ノードは下位ノードの値も含めて計上されます。そのため、各ノード単位でかかるコストや時間は、下位ノードの値を差し引きする必要があります。
C）ノードがループして実行される場合は、loopsに実行された回数が記載され、括弧内に書かれているrows、actual timeにはその時の平均値が出力されます。なお、括弧外のBuffers部分などについては、平均値ではなく累計値で出力されます。また、並列処理についても並列度がloopsに含まれるため、並列処理の際には注意が必要です。
D）actual timeには、"最初の1行目を返却した時間..最後の行を返却した時間"の形式で出力されます（単位はミリ秒）。
E）各ノードの時間は、（actual timeの2つ目の値 × loops） － （下位のノードのactual timeの2つ目の値 × loops）で求められます。下位ノードが複数あれば、それらの値をすべてを差し引きます。並列処理の場合は、loopsの値に起動させたワーカー ＋ 1となるため、並列で動作している分のloopsの掛け算は不要になります。

上記の実行計画について、ノードごとの処理時間をactual timeとloopsから計算すると以下のような結果になります。

2.1.1 インデックス定義のより有効な活用方法

まずは、以下のSQLのように、ORDER BY句のc2、c3にインデックスを利用しないケースを見てみます。

実行計画を確認するため、EXPLAINコマンドにANALYZEオプションを指定して実行します。実行結果を見ると、インデックスはカラムc1にのみ定義されているため、【1】でc1 ＝ 10のデータすべてを取り出し、【2】でc2とc3でソートし、【3】で最後にLIMITでレコード数を制限していることが分かります。

これに対し、以下のようにc1、c2、c3カラムに複合インデックスを作成した上で先ほどと同じSQLを実行すると、ORDER BY句に指定されたc2、c3カラムを含むインデックスが定義されていることで、時間のかかるソート処理が動作せず、高速に実行されていることが確認できます。

この理由について説明すると、まず、WHERE句にあるc1は等価条件（＝）のためc1の検索するデータは1種類となり、インデックス検索を行うことで残りのc2、c3はソート順にデータが取り出せる状態になっています。そのため、ソート処理を実施しなくてもソートされた結果が取り出せることになります。また、すでにソートされたものを取り出しているため、LIMIT句の条件もさらに効果的に働きます。もともとは10,000件取り出してソートしてから500件に絞っていたものが、500件のデータを取り出すだけで済みます。
その結果、ソート処理が不要になり、かつ、アクセスするデータ量が減るため、高速化につながっています。

（1）Index Only Scanによりインデックスを検索する。
（2）SELECT文の条件に指定されたレコードの存在するページの可視性マップをチェックする。
（3）可視性マップをチェックした結果、ページすべてがトランザクションとして取り出し可能であるかどうか判断する。
可能である場合は、ページにアクセスせずインデックスのデータを取得する。
可能でない場合は、実際にページをアクセスし、取り出しが可能かどうかを判断した上でデータを取得する。

そのため、（3）において、ページすべてがトランザクションとして取り出し可能である率が高い程、効果が見込めます。
なお、可視性マップは、1ページ当たり2ビットのデータでありファイルサイズとしては小さいため、可視性マップへのアクセスによるI/O増加に比べて、テーブルデータへのアクセスによるI/O減少の効果が高くなります。

しかし、範囲の条件を記述する場合は、より絞り込める順番に書くよりも、範囲の条件を後に書いた方が良い場合もあるため、注意が必要です。その例を挙げて、実際の動作を確認してみます。準備として、以下のようなテーブルを作成し、テストデータも作成します。

まずは、以下のSQLのように、カラムnumが範囲の条件になっているケースを見てみます。

この場合、typeは300,000分の100,000（3分の1）に絞り込め、numは300,000分の3,000（100分の1）に絞り込めます。そのため、より絞り込めるカラムnumから順番にインデックスを定義し、実行計画を確認してみます。なお、共有バッファーからの読み取りページ数や、ファイルからの読み取りブロック数が確認できるよう、EXPLAINコマンドにBUFFERSオプションも追加します。

比較のために、CREATE INDEXで指定するカラムの順を変更し、再度、実行計画を確認してみます。

利用されたバッファー数（Buffers:）を見ると、前者の18に対して、後者の方が13と少なくなっており、範囲の条件を後にした方が利用するバッファー数が少なく、結果的に処理時間も速くなっています。このように、範囲の条件を利用する際には、定義を後にした方がパフォーマンスが良くなる場合があるため、実際に確認してみることをお勧めします。

まず、上記のカラムdate、timeのインデックスを利用して検索しようとすると、プランナーは date > '2019/4/1' の条件で検索し、その後、date = '2019/4/1' AND time >= '12:00:00' の条件でも検索します。次にこれらの結果をマージするよう実行計画を立てます。この処理はコストが高いため、パフォーマンスが出ないことが考えられます。

次に、実際の動作を確認してみます。準備として、以下のようなテーブルを作成し、テストデータも作成します。

カラムdateとtimeのインデックスを定義し、SELECT文の実行計画を確認してみると、インデックス検索2回とマージ処理1回の動作が行われている事が確認できます。

そこで、ORの両辺にあるdateの条件が両方とも date >= ‘2019/4/1’ を満たすので、この条件をANDで追加します。

その実行計画を確認してみると、インデックス検索において追加した条件で絞り込み【1】、インデックス検索で利用しなかった条件でフィルタリング【2】することでデータを取り出せていることが分かります。その結果、1回のインデックス検索で範囲を絞って検索を実施することができるため、処理時間の短縮につながります。

関数や演算を利用した条件なら「式インデックス」を利用する

SQLの条件文において、カラムに対して関数や演算を利用している場合、カラム名のみでインデックスを作成しても、そのインデックスは利用されません。このような場合には、関数や演算の結果をインデックス化する「式インデックス」を利用してください。「式インデックス」を利用すると、関数や演算結果をインデックス化できるため、インデックスによる絞り込みが利用でき、高速な検索が可能になります。
例えば、大文字小文字を区別せずに文字列を比較するために、以下のようなlower関数を使用した条件文を記述します。

この場合にインデックスを利用するためには、以下のようにlower関数の結果をインデックスに定義します。

【準備】

条件文のデータ型を等しく指定した場合（比較対象がinteger型）には、インデックスが利用されます。

次に、条件文のデータ型が異なる場合（比較対象がnumeric型）は、列定義idがnumericに型変換されることで、インデックスは使われず、シーケンシャルスキャン（Seq Scan）が選択されます。なお、PL/pgSQLなどで比較対象の「1::numeric」のところに、numeric型の変数を利用したときも同様です。

実行計画から時間を費やしている箇所を特定する

実際の実行計画を参考に、SQLが時間を費やしている箇所を特定し、妥当性を確認します。
準備として、以下のようなテーブルを作成し、テストデータも作成します。

以下のSELECT文について、実行計画を出力します。

各処理に費やした時間については、以下の結果になります。
【上位ノード】131.686 － Seq Scanの時間（131.519） － Index Scanの時間（0.132） ＝ 0.035 ms
【下位ノード1】131.519 × 実行回数1回 ＝ 131.519 ms
【下位ノード2】0.012 × 実行回数11回 ＝ 0.132 ms

これらの情報を基に、実行計画の妥当性を確認します。例えば、Seq Scanで時間を費やしている場合はインデックスを追加することを検討します。しかし、loopsの回数が多く時間を費やしている場合は、結合方法を変更するなど、ノードの要素を強制的に変更することも検討してください。結合方法の変更については次の項目で説明しますが、コマンド「SET enable_nestloop = off」により一時的にNested Loopを抑止することで行うことも可能です。
なお、【上位ノード】と【下位ノード1】において、推定時の行数（rows）が1であるのに対し、実行時の行数（rows）は11になっています。これは、統計情報が最新化されていない可能性が考えられます。この場合は、autovacuumの実行頻度の設定を見直すか、あるいは、手動でANALYZEコマンドを実行することで解決できます。

pg_hint_planを用いて、実行計画を制御する

実行計画を見て利用して欲しいインデックスや結合方法が使われていなかった場合、ヒント句を指定し、希望する方法でアクセスするように制御することで、パフォーマンス改善を実施します。詳細は「pg_hint_planで実行計画を制御する」を参照してください。

pg_dbms_statsを用いて、統計情報を固定化する

テーブルの更新頻度が高く、統計情報の最新化が間に合わない場合など、実行計画作成時に正しい統計情報が参照できないような場合に、統計情報を固定し、常に一定の実行計画が作成されるようにして、パフォーマンス劣化を防ぎます。詳細は「pg_dbms_statsで統計情報を固定化して、実行計画を制御する」を参照してください。

何度も実行するSQLは、プリペアド文を定義し再利用する

パラメーターだけ異なるSQL文を何度も実行する場合、プリペアド文（PREPARE）を定義し、実行計画を再利用させることで、実行計画の作成が繰り返されることを防止し、パフォーマンスが改善できます。プリペアド文は、汎用的な実行計画を作成し、その実行計画を利用するかどうかをPostgreSQL内部で自動的に判断しています。具体的には以下のように動作します。

1）プリペアド文で準備したSQLを繰り返し実行する時に、最初の5回目までは、毎回、パラメーターに合わせた実行計画（実行計画A1からA5とします）を作成します。
2）6回目以降に実行する時は、パラメーターの値が未設定である汎用的な実行計画（実行計画Bとします）を作成し、今まで作成した実行計画A1からA5までの平均コストと、実行計画Bのコストを比較して、今後、汎用的な実行計画を利用するか否かを判定します。
3）実行計画Bのコストが実行計画Aの平均コストよりもそれほど大きくない場合、それ以降のSQL実行で実行計画を毎回作成せず、汎用的な実行計画（実行計画B）を利用します。
あるいは、実行計画Bのコストが実行計画A1からA5までの平均コストよりもかなり大きい場合は、パラメーターに合わせた実行計画を毎回作成して利用します。

なお、プリペアド文の中で使用されているデータベースオブジェクトが定義（DDL）に変更があった場合、もしくは、search_pathの値が変わった場合に、上記の解析処理と計画作成が再度行われます。

PostgreSQL 12からは、設定ファイルpostgresql.confにplan_cache_modeパラメーターが追加され、より柔軟な設定が可能になりました。以下の3パターンから選択することができます。

コネクションプーリングを使い、接続処理を省略する

頻繁にPostgreSQLへの接続と切断を繰返すシステムのような場合、このコネクション処理がオーバーヘッドになります。その場合は、コネクションプーリングを用いることで、すでに接続されているコネクションを再利用でき、パフォーマンス改善につなげることができます。コネクションプーリングは、Pgpool-IIを利用したり、JDBCやNpgsql（.NET Data Provider）などのインターフェイスに組み込まれているものを利用したり、クライアント側でコネクションプーリング機能を作り込んだりすることで実現できます。

ユーザー定義関数を用いて処理を関数化し、ネットワーク通信を省略する

クライアント側で行っていた定型のSQL処理を、ユーザー定義関数としてPostgreSQLサーバー側に配置することで、サーバー側で処理を完結させ、SQLを発行するたびに発生していたネットワーク通信を省略することでパフォーマンスが改善できます。ただし、ユーザー定義関数のPL/pgSQLなどは、内部での初期化処理や記述方法によっては、処理を関数化する前よりも遅くなることもあるため、試行しながら進める必要があります。

フェッチサイズを調整する

PostgreSQLからデータを取得するプロセスは「フェッチ」と呼ばれます。このフェッチ処理では、大きなデータ量を扱う場合、一度に取得するデータ量を増やし、通信の実行回数を減少させることで、データの受け渡しが効率化され、パフォーマンスの改善につながることがあります。JDBCおよびODBCではパラメータで調整できますが、組込みSQLではアプリの改修が必要になります。具体的な手段としては、以下の方法があります。

PostgreSQLのパフォーマンスチューニングの重要なポイントである「探し」についての考え方と手法について解説しました。次回は、「基盤」をテーマに解説します。