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

今回は、実際の業務において押さえておきたいパフォーマンスチューニング手法の「探し」について解説します。ここでの「探し」とは、データベース内でアクセスすべきデータを効率的に絞り込んで探し出すことであり、時間がかかる処理や無駄な資源消費を排除することがテーマです。以下に、「パフォーマンスチューニング9つの技」の全体概要を示します。

今回の記事は、パフォーマンスチューニングの観点と仕組みを理解することに主眼を置いています。具体的な対処方法についてはシステムによって異なるため、マニュアルの確認や、各種チューニングサービスのご利用をご検討ください。なお、この記事で対象にしているPostgreSQLのバージョンは9.5以降です。

本記事の構成

本記事「パフォーマンスチューニング9つの技」は以下4つの記事から構成されています。他の記事も併せてご覧ください。

• パフォーマンスチューニング9つの技 ～はじめに～
• パフォーマンスチューニング9つの技 ～「書き」について～
• パフォーマンスチューニング9つの技 ～「探し」について～（本記事）
• パフォーマンスチューニング9つの技 ～「基盤」について～

1. パフォーマンスチューニングの「探し」とは

PostgreSQLは、SQLが発行されると、アクセス先のデータの特性を示す『統計情報』を参照し、一番効率の良いアクセス手順である『実行計画』を決定した上で、データベースへのアクセス処理を実行します。『実行計画』は、検索、結合、ソートなどの処理の階層的な組み合わせで構成されます。より効率的な『実行計画』が生成されるようにするためには、テーブルへの適切なインデックスを作成したり、SQL文の記述を工夫したりする必要があります。すると、アクセスするデータの範囲を効果的に絞り込まれてディスクI/Oが削減でき、また処理コストの低い手法で処理されるようになり、結果的にパフォーマンス向上につながります。図1に、今回説明するPostgreSQLのアーキテクチャー上の着目ポイントについて示します。

図1 PostgreSQLの「探し」のポイント

PostgreSQL全体の構成や、サーバープロセスの処理（パーサー、リライター、プランナー、エグゼキューター）の説明は、「PostgreSQLのアーキテクチャー概要」を参照してください。

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

1.1 インデックス定義の効果について

インデックスが存在しないテーブルからデータを検索する場合、テーブルの先頭レコード（タプル）から順に全データにアクセスするため、処理に時間を費やします。

図2 インデックスを利用しない場合のデータ検索

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

図3 インデックスを利用した場合のデータ検索

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

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

図4 インデックスの定義例

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

• テーブルのデータ量が多く、一部のレコードデータにのみアクセスする
• カラムにおける値のばらつきが大きい（カーディナリティーが高い）

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

1.2 プランナーが作成する実行計画の見かたについて

PostgreSQLがSQLを実行する処理において、大きくパフォーマンスに影響を与える機構がプランナーです。プランナーは、SQLが発行されると、定期的に実行されるautovacuumから呼び出されるANALYZE処理によって更新される「統計情報」を参照して、「実行計画（問い合わせ計画）」を作成します。「統計情報」には現在のテーブルにおけるデータ量やばらつきなどの統計的な情報が保持されており、プランナーは、この情報を参考にさまざまな検索方法や結合方法などの中から、処理コストが最小になる組み合わせを見つけ出し、「実行計画」を作成します。

実際にプランナーがどのような実行計画を作ったのかを知るためには、EXPLAINコマンドを使います。パラメーターにANALYZEを指定すると、実際にSQLを実行させ、その際の稼働時間を表示します。また、BUFFERSを指定すると、バッファーの使用状況に関する情報が表示されます。
以下に、SELECT文の実行計画の例を示します。

はじめに、各ノードの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から計算すると以下のような結果になります。

図5 ノードごとの処理時間の計算方法

2. パフォーマンスチューニングの「探し」の技

ここからは、具体的なインデックスの活用例、最適な実行計画を生成する例、および、クライアント側でのSQLの書き方を工夫する方法について説明します。

2.1 インデックスを有効活用する

インデックスを活用しパフォーマンスを向上させるための手法を、いくつか紹介します。
ここでは以下の内容で説明します。

• 2.1.1 インデックス定義のより有効な活用方法
  • ソート処理（ORDER BY句）をインデックススキャンで代用する、LIMIT句がある場合はさらに有効
  • 検索方式Index Only Scanを活用する
• 2.1.2 複合インデックス作成時のポイント
  • インデックス定義はより絞り込める条件のカラムから順番に記述する、ただし例外あり
• 2.1.3 SQL文記述時におけるインデックスの有効な利用方法
  • SQLのOR条件式を工夫することで、インデックスの絞り込みを有効にする
  • 関数や演算を利用した条件なら「式インデックス」を利用する

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

ソート処理（ORDER BY句）をインデックススキャンで代用する、LIMIT句がある場合はさらに有効

SELECT文にORDER BY句を使用する場合には、ORDER BY句に書かれているカラムの順番でインデックスが定義されていれば、SELECT文の実行時にソート処理が省略されます。それは、ソート処理を実行しなくてもインデックスを順番に見ていくことでソートされた結果を取り出すことができるためです。その結果、SELECT文は高速に処理できます。また、LIMIT句がある場合は、LIMITで指定された個数までしかデータにアクセスしないで済むため、さらに効果が見込めます。

ここで、例を挙げて、実際の動作を確認してみます。以下のように、テーブルtblと、カラムc1に対するインデックスのみを作成し、テストデータも作成します。

CREATE TABLE tbl (c1 int, c2 int, c3 int);
CREATE INDEX tbl_idx1 ON tbl(c1);
INSERT INTO tbl SELECT * FROM generate_series(1, 100) A, generate_series(1, 100) B, generate_series(1, 100);

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

SELECT * FROM tbl WHERE c1 = 10 ORDER BY c2, c3 LIMIT 500;

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

postgres=# EXPLAIN ANALYZE SELECT * FROM tbl
postgres-# WHERE c1 = 10 ORDER BY c2, c3 LIMIT 500;
                              QUERY PLAN
-----------------------------------------------------------------------------
 Limit  (cost=6354.97..6356.22 rows=500 width=12)
            (actual time=117.410..118.260 rows=500 loops=1) 【3】
   ->  Sort  (cost=6354.97..6378.89 rows=9567 width=12)
                 (actual time=117.407..117.676 rows=500 loops=1) 【2】
         Sort Key: c2, c3
         Sort Method: top-N heapsort  Memory: 63kB
         ->  Bitmap Heap Scan on tbl
                 (cost=210.57..5878.26 rows=9567 width=12)
                     (actual time=102.016..108.973 rows=10000 loops=1) 【1】
               Recheck Cond: (c1 = 10)
               Heap Blocks: exact=150
               ->  Bitmap Index Scan on tbl_idx1
                       (cost=0.00..208.18 rows=9567 width=0)
                           (actual time=101.977..101.978 rows=10000 loops=1) 【1】
                     Index Cond: (c1 = 10)
 Planning Time: 0.192 ms
 Execution Time: 118.609 ms

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

postgres=# CREATE INDEX tbl_idx2 ON tbl (c1, c2, c3);
CREATE INDEX
postgres=# EXPLAIN ANALYZE SELECT * FROM tbl
postgres-# WHERE c1 = 10 ORDER BY c2, c3 LIMIT 500;
                                        QUERY PLAN
-----------------------------------------------------------
 Limit  (cost=0.42..1077.55 rows=500 width=12)
            (actual time=2.770..6.758 rows=500 loops=1)
   ->  Index Only Scan using tbl_idx2 on tbl
           (cost=0.42..20610.08 rows=9567 width=12)
               (actual time=2.763..6.195 rows=500 loops=1)
         Index Cond: (c1 = 10)
         Heap Fetches: 500
 Planning Time: 7.099 ms
 Execution Time: 8.013 ms    ←ソート処理が不要になり、高速に実行されている

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

図6 ソート処理をインデックススキャンで代用する例（LIMIT句も利用）

検索方式Index Only Scanを活用する

SELECT文で指定されたWHERE句と選択リストに指定されたカラムがすべてインデックスに含まれている場合、および、インデックスの種類がB-treeなどのようにIndex Only Scanをサポートしている場合には、プランナーが実行計画を作成する際に、高速なスキャン方式であるIndex Only Scanを選択します。Index Only Scanは、インデックスデータをアクセスするだけで処理ができ、テーブルデータへのアクセスが不要になるため高速な検索が可能です。ただし、インデックスデータのみで高速に処理させるためには、該当のレコードが存在するページが、すべてのトランザクションから取り出し可能な状態になっている必要があり、その情報は可視性マップ（注1）から取得されます。そのケースに該当する例としては、部署データなどの基本的に変更の殆どないマスターデータのテーブルを検索する場合が挙げられます。以下に、その動作概要を示します。

• 注1
  可視性マップ（ビジビリティマップ）の説明は、「PostgreSQLのアーキテクチャー概要」を参照してください。

図7 Index Only Scanによるインデックス活用例

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

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

2.1.2 複合インデックス作成時のポイント

インデックス定義はより絞り込める条件のカラムから順番に記述する、ただし例外あり

複数カラムを組み合わせて1つのインデックスが作成できる「複合インデックス」を利用する場合、基本的に、データ量をより少なく絞り込める条件をもつカラムから順にインデックスカラムを記述することで高速に絞り込むことができます。例えば、カラムc1よりも、カラムc2の方がより少なく絞り込める場合、以下のようにインデックスを定義します。

CREATE INDEX tbl_idx1 ON tbl(c2, c1);

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

CREATE TABLE type_tbl(type char, num int, name text);
INSERT INTO type_tbl SELECT 'a', s, 'a' || s FROM generate_series(1, 100000) s;
INSERT INTO type_tbl SELECT 'b', s, 'b' || s FROM generate_series(1, 100000) s;
INSERT INTO type_tbl SELECT 'c', s, 'c' || s FROM generate_series(1, 100000) s;

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

SELECT * FROM type_tbl WHERE type = 'b' AND num BETWEEN 1000 AND 2000;

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

postgres=# CREATE INDEX typ_idx ON type_tbl(num, type);
CREATE INDEX
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM type_tbl
postgres-# WHERE type = 'b' AND num BETWEEN 1000 AND 2000;
                                        QUERY PLAN
--------------------------------------------------------------------------------------
Bitmap Heap Scan on type_tbl  (cost=75.42..1595.19 rows=1034 width=12)
                                  (actual time=0.604..1.341 rows=1001 loops=1)
   Recheck Cond: ((num >= 1000) AND (num <= 2000) AND (type = 'b'::bpchar))
   Heap Blocks: exact=7
   Buffers: shared hit=18
   ->  Bitmap Index Scan on typ_idx  (cost=0.00..75.16 rows=1034 width=0)
                                         (actual time=0.593..0.594 rows=1001 loops=1)
         Index Cond: ((num >= 1000) AND (num <= 2000) AND (type = 'b'::bpchar))
         Buffers: shared hit=11
 Planning Time: 0.228 ms
 Execution Time: 2.026 ms

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

postgres=# CREATE INDEX typ_idx2 ON type_tbl(type, num);
CREATE INDEX
postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM type_tbl
postgres-# WHERE type = 'b' AND num BETWEEN 1000 AND 2000;
                                        QUERY PLAN
-----------------------------------------------------------------------------------------
Index Scan using typ_idx2 on type_tbl  (cost=0.42..1382.30 rows=1003 width=12)
                                           (actual time=0.032..0.767 rows=1001 loops=1)
   Index Cond: ((type = 'b'::bpchar) AND (num >= 1000) AND (num <= 2000))
   Buffers: shared hit=13
 Planning Time: 0.123 ms
 Execution Time: 1.365 ms

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

2.1.3 SQL文記述時におけるインデックスの有効な利用方法

SQLのOR条件式を工夫することで、インデックスの絞り込みを有効にする

SQL文にOR条件が記述されているとき、プランナーは、その条件を使ってインデックスを利用しようとすると、インデックス検索2回とマージ処理1回を行うよう実行計画を立てます。これは、処理コストが高く、処理自体が複雑なため処理に時間を費やします。プランナーのコスト計算によっては、シーケンシャルスキャンによる検索の方が処理コストが低いと見積もられる場合もあり、いずれにしても、処理に時間がかかる結果になります。
この対策としては、OR条件のものをまとめて1つの条件にして、OR条件のない条件を新たに追加できないか考えます。1つにまとめた新たな条件と今までの条件を並列に記述しておくことで検索結果を変えずにインデックスによる絞り込みを有効にすることができます。

例を挙げて説明します。日付（date）と時間（time）が別々のカラムで構成されるテーブルにおいて、2019年4月1日 12時以降のデータが欲しい場合は、以下のような条件式となります。

 (date > '2019/4/1') OR (date = '2019/4/1' AND time >= '12:00:00')

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

図8 SQLにOR条件がある場合の処理の流れ

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

CREATE TABLE time_data(date date, time time, data text);
INSERT INTO time_data SELECT '1980-1-1'::date + cast( i || 'days' AS interval), ('00:00:00' + trunc(random()*86400) * '1 second'::interval), i::text FROM generate_series(1,15000)s(i);

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

postgres=# CREATE INDEX time_date_idx ON time_data(date, time);
postgres=# EXPLAIN ANALYZE SELECT * FROM time_data
postgres-# WHERE (date > '2019/4/1') OR (date = '2019/4/1' AND time >= '12:00:00');
                                        QUERY PLAN
-------------------------------------------------------------------------------------------------
Bitmap Heap Scan on time_data  (cost=45.90..229.54 rows=665 width=17)
                                   (actual time=0.119..0.563 rows=666 loops=1)
   Recheck Cond: ((date > '2019-04-01'::date) OR ((date = '2019-04-01'::date)
                 AND ("time" >= '12:00:00'::time without time zone)))
   Heap Blocks: exact=5
   ->  BitmapOr  (cost=45.90..45.90 rows=665 width=0) (actual time=0.100..0.101 rows=0 loops=1)
         ->  Bitmap Index Scan on time_data_idx  (cost=0.00..41.27 rows=665 width=0)
                                                     (actual time=0.093..0.094 rows=665 loops=1)
               Index Cond: (date > '2019-04-01'::date)
         ->  Bitmap Index Scan on time_data_idx  (cost=0.00..4.29 rows=1 width=0)
                                                     (actual time=0.004..0.004 rows=1 loops=1)
               Index Cond: ((date = '2019-04-01'::date)
                           AND ("time" >= '12:00:00'::time without time zone))
 Planning Time: 0.153 ms
 Execution Time: 1.010 ms

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

 (date >= '2019/4/1’) AND ( (date > '2019/4/1') OR (date = '2019/4/1' and time >= '12:00:00') )

図9 追加するAND条件

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

postgres=# EXPLAIN ANALYZE SELECT * FROM time_data
postgres-# WHERE (date >= '2019/4/1')
postgres-#     AND ((date > '2019/4/1') OR (date = '2019/4/1' AND time >= '12:00:00'));
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
Bitmap Heap Scan on time_data  (cost=41.29..226.61 rows=30 width=17)
                                   (actual time=0.073..0.545 rows=666 loops=1)
   Recheck Cond: (date >= '2019-04-01'::date)
   Filter: ((date > '2019-04-01'::date) OR ((date = '2019-04-01'::date)
           AND ("time" >= '12:00:00'::time without time zone))) 【2】
   Heap Blocks: exact=5
   ->  Bitmap Index Scan on time_data_idx  (cost=0.00..41.28 rows=666 width=0)
                                               (actual time=0.059..0.059 rows=666 loops=1) 【1】
         Index Cond: (date >= '2019-04-01'::date)
 Planning Time: 0.126 ms
 Execution Time: 0.953 ms

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

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

SELECT * FROM tbl WHERE lower(col1) = 'value';

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

CREATE INDEX tbl_lower_col1_idx ON tbl (lower(col1));

参考

WHERE句の条件文で異なるデータ型による比較を行った場合の影響について

WHERE句の条件文において、列定義と比較対象のデータ型が異なると、パーサは実行計画を生成する際に、列定義のデータ型を型変換してしまう場合があります。すると、インデックスを作成したときのデータ型が変わることで、インデックスが使われなくなることがあるため、注意が必要です。以下にその例を示します。

【準備】

postgres=# CREATE TABLE tbl(id integer, name text); ← 列定義のidはinteger型
postgres=# INSERT INTO tbl SELECT s, 's' || s FROM generate_series(1, 10000) s;
INSERT 0 10000
postgres=# CREATE INDEX tbl_idx ON tbl(id); ← 列定義idでインデックスを作成
CREATE INDEX
postgres=# analyze tbl;
ANALYZE

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

postgres=# EXPLAIN ANALYZE SELECT count(id) FROM tbl WHERE id = 1;
                                   QUERY PLAN
------------------------------------------------------------------------------------------
 Aggregate  (cost=8.30..8.31 rows=1 width=8)
                (actual time=0.088..0.097 rows=1 loops=1)
   ->  Index Only Scan using tbl_idx on tbl  (cost=0.29..8.30 rows=1 width=4)
                                                 (actual time=0.058..0.065 rows=1 loops=1)
         Index Cond: (id = 1)
         Heap Fetches: 6
 Planning Time: 0.440 ms
 Execution Time: 0.192 ms

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

postgres=# EXPLAIN ANALYZE SELECT count(id) FROM tbl WHERE id = 1::numeric;
                        QUERY PLAN
---------------------------------------------------------------------
 Aggregate  (cost=205.12..205.13 rows=1 width=8)
                (actual time=3.392..3.404 rows=1 loops=1)
   ->  Seq Scan on tbl  (cost=0.00..205.00 rows=50 width=4)
                            (actual time=0.039..3.376 rows=1 loops=1)
         Filter: ((id)::numeric = '1'::numeric)
         Rows Removed by Filter: 9999
 Planning Time: 0.286 ms
 Execution Time: 3.486 ms

2.2 実行計画を評価し制御する

PostgreSQLは、autovacuumによって定期的に最新化される統計情報を基に最適な実行計画を作成しますが、テーブルの更新頻度が高くて常にデータの状況が変化している場合など、統計情報と実際のデータとのかい離が発生し、非効率で時間のかかる実行計画が作成されることで、パフォーマンスが不安定になる場合があります。
この場合には、まず、実行計画を確認して時間を費やしている箇所を特定し、インデックス利用やSQL文の見直しによるパフォーマンスチューニングが正しく行われているかを確認します。その上で、パフォーマンスを安定させるために、pg_hint_planを用いて実行計画の結果を制御したり、pg_dbms_statsを用いて統計情報を固定化したりすることを検討します。

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

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

CREATE TABLE tbl1 (id int, data text, foreign_id int);
CREATE TABLE tbl2 (id int, data text, primary key (id));
INSERT INTO tbl1 SELECT s, md5(clock_timestamp()::text), s % 100 FROM generate_series(1, 1000000) s;
INSERT INTO tbl2 SELECT s, md5(clock_timestamp()::text) FROM generate_series(1, 100000) s;

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

postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT t1.id, t1.data, t2.data FROM tbl1 t1, tbl2 t2
postgres-# WHERE t1.foreign_id = t2.id AND t1.id BETWEEN 10 AND 20;
                                        QUERY PLAN
---------------------------------------------------------------------------------------------
Nested Loop  (cost=0.29..24354.31 rows=1 width=70)
                 (actual time=0.358..131.686 rows=11 loops=1) 【上位ノード】
   Buffers: shared hit=289 read=9090
   ->  Seq Scan on tbl1 t1  (cost=0.00..24346.00 rows=1 width=41)
                                (actual time= 0.342..131.519 rows=11 loops=1) 【下位ノード1】
         Filter: ((id >= 10) AND (id <= 20))
         Rows Removed by Filter: 999989
         Buffers: shared hit=256 read=9090
   ->  Index Scan using tbl2_pkey on tbl2 t2  (cost=0.29..8.31 rows=1 width=37)
                                                  (actual time= 0.012..0.012 rows=1 loops=11) 【下位ノード2】
         Index Cond: (id = t1.foreign_id)
         Buffers: shared hit=33
 Planning Time: 0.208 ms
 Execution Time: 131.737 ms

各処理に費やした時間については、以下の結果になります。
【上位ノード】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コマンドを実行することで解決できます。

参考

カーソル利用時の実行計画について

カーソルを利用してデータを取得する場合は、設定ファイルpostgresql.confのcursor_tuple_fractionパラメーターの値に従い、起動を高速にする実行計画を作成します。これは、1つ目の最初のレコード（タプル）を取り出すためのコストが小さくなるようにしているためです。そのため、カーソルを利用する場合とそうでない場合とで、実行計画が変わることがあることに注意してください。

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

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

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

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

2.3 クライアント側で無駄を削減する

PostgreSQLのクライアント側でSQLを記述する際の工夫や、パフォーマンス改善につながる手法について紹介します。ここでは以下の内容で説明します。

• 何度も実行するSQLは、プリペアド文を定義し再利用する
• コネクションプーリングを使い、接続処理を省略する
• ユーザー定義関数を用いて処理を関数化し、ネットワーク通信を省略する
• フェッチサイズを調整する

何度も実行する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などは、内部での初期化処理や記述方法によっては、処理を関数化する前よりも遅くなることもあるため、試行しながら進める必要があります。

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

JDBC、ODBCなどのインターフェイスを利用する際、PostgreSQLとのデータの受け渡しサイズ（フェッチサイズ）がパラメーターとして存在しており、このフェッチサイズを調整してパフォーマンス改善することが可能です。ある程度大きなデータ量の受け渡しがある場合には、フェッチサイズを大きくして、実行結果を一度に受け取ることで通信の実行回数を減らし、パフォーマンス改善につながることがあります。JDBC、ODBCのフェッチサイズは以下の方法で変更できます。

• JDBCの接続文字列に設定できるdefaultRowfetchSizeパラメーターにより変更
• ODBCのCache Sizeオプションにより変更

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

2021年8月27日更新

こちらもおすすめ

• チューニング ～ データベースチューニング ～
• 【豆知識】SQLチューニングアプローチの考え方

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