PostgreSQL 9.1.5文書
前のページ	巻戻し	第 14章性能に関するヒント	早送り	次のページ

14.1. `EXPLAIN`の利用

PostgreSQLは受理した問い合わせから問い合わせ計画を作り出します。問い合わせの構造と含まれるデータの性質に適した正しい問い合わせ計画を選択することが、良い性能を得るために非常に重要になります。ですので、システムには優れた計画の選択を試みる複雑なプランナが存在します。 EXPLAINコマンドを使えば、任意の問い合わせに対してプランナがどのような問い合わせ計画を作ったのかわかります。問い合わせ計画を読みこなすことについては、別途広範囲にわたるチュートリアルを作らなければならないほどの腕が必要です。本書ではそこまでカバーしませんが、基本的な情報をいくつか提供します。

問い合わせ計画は計画ノードのツリー構造です。ツリー構造の最下層ノードはテーブルスキャンノードで、テーブルから行そのものを返します。シーケンシャルスキャン、インデックススキャン、ビットマップインデックススキャンといったテーブルアクセス方法の違いに応じ、スキャンノードの種類に違いがあります。問い合わせが結合・集約・ソートなど、行そのものに対する操作を必要としている場合、スキャンノードの上位に更に、これらの操作を行うためのノードが追加されます。これらの操作の実現方法にも通常複数の方法がありますので、異なった種類のノードがここに出現することもあり得ます。 EXPLAINには計画ツリー内の各ノードにつき1行の出力があり、基本ノード種類とプランナが生成したその計画ノードの実行に要するコスト推定値を示します。 1行目（もっとも上位ノード）には、計画全体の実行コスト推定値が含まれます。プランナはこの値が最小になるように動作します。

どのような出力となるのかを示すためだけに、ここで簡単な例を示します。 [1]

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

EXPLAINが（左から右に）出力する数値には以下のものがあります。

初期処理の推定コスト（出力用のスキャンが開始されるまでに消費される時間、例えば、SORTノードで実行されるソート処理の時間）。
全体推定コスト。（すべての行を受け取った場合。ただしすべてではない場合もあります。例えばLIMIT句を取る問い合わせではLimit計画ノードへの入力ノードの総コストをかける前に停止するため短くなります。）
この計画ノードが出力する行の推定数（これも、実行が完了した場合のみ）。
この計画ノードが出力する行の（バイト単位での）推定平均幅。

コストはプランナのコストパラメータ（項18.7.2参照）によって決まる任意の単位で測定されます。取り出すディスクページ単位でコストを測定することが、伝統的な方式です。つまり、seq_page_costを慣習的に1.0に設定し、他のコストパラメータを相対的に設定します。（本節の例では、デフォルトのコストパラメータで実行しています。）

上位ノードのコストには、すべての子ノードのコストもその中に含まれていることに十分留意してください。このコストはプランナが関与するコストのみ反映する点もまた重要です。とりわけ、結果の行をクライアントに転送するコストは、実際の処理時間の重要な要因となる可能性があるにもかかわらず、考慮されません。プランナは、計画をいかに変更しようと、どうすることもできないため、これを無視します。（正しい計画はどんなものであれ、すべて同じ行を結果として出力すると信じています。）

rowsの値は、計画ノードによって処理あるいはスキャンされた行数を表していないので、多少扱いにくくなっています。該当ノードに適用されるすべてのWHERE句条件の選択性推定値を反映し、通常少ない行数になります。理想的には、最上位の行数の推定値は、実際に問い合わせによって返され、更新され、あるいは削除された概算の行数となります。

例に戻ります。

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

これはほとんど見たままです。もし、

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

を実行すると、tenk1には358のディスクページと10000の行があることがわかります。推定コストは（ディスクページ読み取り x seq_page_cost）+（スキャンした行 x cpu_tuple_cost）と計算されます。デフォルトでは、seq_page_costは1.0、cpu_tuple_costは0.01です。ですから、推定コストは(358 * 1.0) + (10000 * 0.01) = 458となります。

では、WHERE条件を加えて元の問い合わせを変更してみます。

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..483.00 rows=7033 width=244)
   Filter: (unique1 < 7000)

EXPLAINの出力が適用されるWHERE句が"フィルタ"条件として表示していることに注意してください。これは、この計画ノードがスキャンした各行に対してその条件を検査することを意味し、その条件を通過したもののみが出力されます。 WHERE句があるため、推定出力行数が小さくなっています。しかし、依然として10000行すべてをスキャンする必要があるため、コストは小さくなっていません。実際には、WHERE条件を検査するためにCPU時間が余計にかかることを反映して、ほんの少し（正確には10000 x cpu_operator_cost）ですがコストが上昇しています。

この問い合わせが選択する実際の行数は7000です。しかし、rowsの推定行数は概算値に過ぎません。この実験を2回実行した場合、おそらく多少異なる推定値を得るでしょう。もっと言うと、これはANALYZEコマンドを行う度に変化します。なぜなら、ANALYZEで生成される統計情報は、テーブルのランダムな標本から取り出されるからです。

では、条件をより強く制限してみます。

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=2.37..232.35 rows=106 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
         Index Cond: (unique1 < 100)

ここでは、プランナは2段階の計画を使用することを決定しました。最下層の計画ノードは、インデックスを使用して、インデックス条件に合う行の場所を検索します。そして、上位計画ノードが実際にテーブル自体からこれらの行を取り出します。行を別々に取り出すことは、シーケンシャルな読み取りに比べ非常に高価です。しかし、テーブルのすべてのページを読み取る必要はありませんので、シーケンシャルスキャンより低価になります。（2段階の計画を使用する理由は、別々に行を取り出すコストを最小にするために、上位の計画ノードがインデックスにより識別された行の位置を読み取る前に物理的な順序でソートすることです。ノードで記載されている"bitmap"は、ソートを行う機構の名前です。）

WHERE条件の選択性が十分であれば、プランナが"単純な"インデックススキャン計画に切り替える可能性があります。

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..10.00 rows=2 width=244)
   Index Cond: (unique1 < 3)

この場合、テーブル行はインデックスの順番で取り出されます。これは高価な読み取りなのですが、行数が少ないため、行位置をソートする余計なコストをかける必要はありません。単一行のみを取り出す問い合わせや、インデックス順に一致するORDER BY条件を有する問い合わせでは、この種の計画をよく見かけます。

WHERE条件に句をもう1つ追加します。

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3 AND stringu1 = 'xxx';

                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..10.01 rows=1 width=244)
   Index Cond: (unique1 < 3)
   Filter: (stringu1 = 'xxx'::name)

追加した句stringu1 = 'xxx'により出力行数の推定値は減少するものの、検査する行の集合は変わらないためにコストは減っていません。このstringu1句は、インデックス条件としては適用できないことに注意してください（このインデックスは、unique1列のみを対象としているからです）。その代わり、stringu1句は、インデックスによって抽出された行のフィルタとして適用されています。これにより、追加の検査分を反映するため、コストは実際には少し上がります。

WHERE句で参照される複数の列に対するインデックスが存在する場合、プランナはインデックスをANDやORで組み合わせて使用することを選択する可能性があります。

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=11.27..49.11 rows=11 width=244)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   ->  BitmapAnd  (cost=11.27..11.27 rows=11 width=0)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
               Index Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..8.65 rows=1042 width=0)
               Index Cond: (unique2 > 9000)

しかし、これは両方のインデックスを参照する必要があります。そのため、インデックスを1つ使用し、他の条件についてはフィルタとして扱う方法と比べて常に勝るとは限りません。含まれる範囲を変更すると、それに伴い計画も変わることが分かるでしょう。

今まで説明に使ってきたフィールドを使って2つのテーブルを結合してみましょう。

EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Nested Loop  (cost=2.37..553.11 rows=106 width=488)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244)
         Recheck Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
               Index Cond: (unique1 < 100)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..3.01 rows=1 width=244)
         Index Cond: (unique2 = t1.unique2)

この入れ子状ループ結合では、外部（上側）スキャンに、最後の例で示したのと同じビットマップインデックススキャンが使われています。そして、unique1 < 100 WHERE句を該当ノードに適用しているため、コストと出力行数は変わっていません。この段階ではt1.unique2 = t2.unique2句は関係しておらず、外部スキャンにおける出力行数に影響していません。内部（下側）スキャンでは、外部スキャンにおける現在の行のunique2の値が内部のインデックススキャンに投げ込まれ、unique2 = constantのようなインデックス条件を生成します。したがって、内部スキャンの計画とコストは、例えばEXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42のような問い合わせと同じです。ループノードのコストは、外部スキャンのコストと、各々の外部の行に対して内部スキャンが繰り返されることによるコスト（ここでは106 * 3.01）を加え、さらに結合処理を行うための少々のCPU時間を加えたものになります。

この例では、結合の出力行数は2つのスキャンの出力行数の積に等しくなっていますが、いつもそうなるわけではありません。 2つのテーブルに関係するWHERE句は、入力スキャン時ではなく、結合を行う際に適用されるからです。例えば、WHERE ... AND t1.hundred < t2.hundredという句を追加したとすると、結合ノードの出力行数を減らしはしますが、入力スキャンには影響しません。

項18.7.1に記載したenable/disableフラグを使用して、プランナが最も良いと考えている戦略を強制的に無視させる方法により、異なった計画を観察することができます。（非常に原始的なツールですが、利用価値があります。項14.3も参照してください。）

SET enable_nestloop = off;
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Hash Join  (cost=232.61..741.67 rows=106 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..458.00 rows=10000 width=244)
   ->  Hash  (cost=232.35..232.35 rows=106 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0)
                     Index Cond: (unique1 < 100)

この計画では、上と同様にインデックススキャンを使ってtenk1から必要な100行を取り出してメモリ上のハッシュテーブルに格納し、その後、tenk2をシーケンシャルスキャンして、tenk2の行ごとにt1.unique2 = t2.unique2を満たすかどうかハッシュテーブルを検査します。 tenk1を読み取り、ハッシュテーブルを設定するためのコストは、tenk2の読み取りを始めるまで何も出力がありませんので、ハッシュ結合の初期コストとなります。また、この結合の推定総時間には、ハッシュテーブルを10000回検査する、大きなCPU時間が含まれています。しかし、この種類の計画ではハッシュテーブルの設定は一度のみのため、10000×232.35分のコストはかからないことに注意してください。

EXPLAIN ANALYZEを使用して、プランナが推定するコストの精度を点検することができます。このコマンドは実際にその問い合わせを実行し、計画ノードごとに要した実際の実行時間を、普通のEXPLAINが示す推定コストと同じ値と一緒に表示します。例えば、以下のような結果を得ることができます。

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;

                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)
         Recheck Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..2.37 rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)
               Index Cond: (unique1 < 100)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1 loops=100)
         Index Cond: (unique2 = t1.unique2)
 Total runtime: 14.452 ms

"actual time"値は実時間をミリ秒単位で表されていること、cost推定値は何らかの単位で表されていることに注意してください。ですからそのまま比較することはできません。注目する点は、実時間と推定コストの割合に矛盾がないかどうかです。

問い合わせ計画の中には、何回も副計画ノードを実行できるものがあります。例えば、上述の入れ子状ループの計画では、内部インデックススキャンは外部の行ごとに一度行われます。このような場合、loops値はそのノードを実行する総回数を報告し、表示される実際の時間と行数は1実行当たりの平均です。これで値を表示された推定コストと比較できるようになります。 loops値をかけることで、そのノードで実際に費やされた総時間を得ることができます。

EXPLAIN ANALYZEで表示されるTotal runtime（総実行時間）にはエクゼキュータの起動、停止時間も含まれますが、解析や書き換え、計画作成の時間は含まれません。 INSERT、UPDATEおよびDELETEコマンドでは、テーブルの変更を適用するために費やされる時間が最上位のInsert、Update、Delete計画ノードに計上されます。（このノードの下位にある計画ノードは、古い行の場所を決定すること、新しい行を計算すること、またはその両方の作業を表します。） BEFOREトリガがもしあれば、BEFOREトリガの実行に費やされる時間は関連するInsert、Update、Deleteノードに計上されます。しかしAFTERトリガの実行に費やされる時間は計上されません。トリガ（BEFOREまたはAFTER）に費やされた時間はトリガ毎に別々に表示され、また、総実行時間に含まれます。しかし、遅延制約トリガはトランザクションが終わるまで実行されませんので、EXPLAIN ANALYZEでは表示されないことに注意してください。

EXPLAIN ANALYZEにより測定される実行時間が同じ問い合わせを普通に実行する場合と大きくそれる可能性がある、２つの重大な点があります。１つ目は、出力行がクライアントに配信されませんので、ネットワーク転送コストとI/O書式処理に関するコストが含まれないことです。２つ目は、EXPLAIN ANALYZEによって加わるオーバーヘッドが大きくなることが、特にgettimeofday()カーネルコールが低速なマシンであり得ることです。

EXPLAINの結果を試験を行ったもの以外の推定に使ってはいけないことは言うまでもありません。例えば、小さなテーブルの結果は、巨大なテーブルに適用できるとは仮定できません。プランナの推定コストは線形ではなく、そのため、テーブルの大小によって異なる計画を選択する可能性があります。極端な例ですが、テーブルが1ディスクページしか占めない場合、インデックスが使用できる、できないに関係なく、ほとんど常にシーケンシャルスキャン計画を得ることになります。プランナは、どのような場合でもテーブルを処理するために1ディスクページ読み取りを行うので、インデックスを参照するための追加的ページ読み取りを行う価値がないことを知っています。

前のページ	ホーム	次のページ
性能に関するヒント	上に戻る	プランナで使用される統計情報

14.1. EXPLAINの利用

注意

14.1. `EXPLAIN`の利用