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.6.2参照）によって決まる任意の単位で測定されます。 取り出すディスクページ単位でコストを測定することが、伝統的な方式です。 つまり、seq_page_costを慣習的に1.0に設定し、他のコストパラメータを相対的に設定します。 本節の例では、デフォルトのコストパラメータで実行しています。
上位ノードのコストには、全ての子ノードのコストもその中に含まれていることに十分留意してください。 このコストはプランナが関与するコストのみ反映する点もまた重要です。 とりわけ、結果の行をクライアントに転送するコストは、実際の処理時間の重要な要因となる可能性があるにもかかわらず、考慮されません。 プランナは、計画をいかに変更しようと、どうすることもできないため、これを無視します （正しい計画はどんなものであれ、全て同じ行を結果として出力すると信じています）。
出力行数は計画ノードによって処理あるいはスキャンされた行数を表していないので、多少扱いにくくなっています。 該当ノードに適用される全ての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です。 しかし、行推定は概算値に過ぎません。 この実験を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: (t2.unique2 = t1.unique2)
この入れ子状ループ結合では、外部スキャンに、最後の例で示したのと同じビットマップインデックススキャンが使われています。 そして、unique1 < 100 WHERE句を該当ノードに適用しているため、コストと出力行数は変わっていません。 この段階ではt1.unique2 = t2.unique2句は関係しておらず、外部スキャンにおける出力行数に影響していません。 内部スキャンでは、外部スキャンにおける現在の行のunique2の値が内部のインデックススキャンに投げ込まれ、t2.unique2 = constantのようなインデックス条件を生成します。 したがって、内部スキャンの計画とコストは、例えばEXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42のような問い合わせと同じです。 ループノードのコストは、外部スキャンのコストと、各々の外部の行に対して内部スキャンが繰り返されることによるコスト（ここでは106 * 3.01）を加え、さらに結合処理を行うための少々のCPU時間を加えたものになります。
この例では、結合の出力行数は2つのスキャンの出力行数の積に等しくなっていますが、いつもそうなるわけではありません。 たいていの場合、2つのテーブルに関係するWHERE句があって、そのWHERE句は入力スキャン時ではなく、結合を行う際に適用されるからです。 例えば、WHERE ... AND t1.hundred < t2.hundredという句を追加したとすると、結合ノードの出力行数を減らしはしますが、入力スキャンには影響しません。
項18.6.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: (t2.unique2 = t1.unique2)
Total runtime: 14.452 ms"actual time"値は実時間をミリ秒単位で表されていること、"cost"推定値は何らかの単位で表されていることに注意してください。 ですからそのまま比較することはできません。 注目する点は、実時間と推定コストの割合に矛盾がないかどうかです。
問い合わせ計画の中には、何回も副計画ノードを実行できるものがあります。 例えば、上述の入れ子状ループの計画では、内部インデックススキャンは外部の行ごとに一度行われます。 このような場合、"loops"値はそのノードを実行する総回数を報告し、表示される実際の時間と行数は1実行当たりの平均です。 これは表示された推定コストと比較できる値を作成することで行います。 "loops"値をかけることで、そのノードで実際に費やされた総時間を得ることができます。
EXPLAIN ANALYZEで表示されるTotal runtime（総実行時間）には、結果行を操作するための時間の他に、エクゼキュータの起動、停止時間も含まれます。 解析や書き換え、計画作成の時間は含まれません。 SELECT問い合わせでは、総実行時間は最上位計画ノードで報告される総時間よりも通常少し大きくなります。 INSERT、UPDATE、およびDELETEコマンドでは、その総実行時間はかなり大きくなる可能性があります。 そこには、結果行を処理する時間が含まれるからです。 これらのコマンドでは、最上位計画ノードの時間は、基本的に新しい行を演算し、古い行の場所を決定するための時間を示し、そこには変更を行う時間は含まれません。 トリガが発行される場合、トリガの発行にかかる時間も最上位計画ノードには含まれず、トリガ毎に別々に表示されます。
EXPLAINの結果を試験を行ったもの以外の推定に使ってはいけないことは言うまでもありません。 例えば、小さなテーブルの結果は、巨大なテーブルに適用できるとは仮定できません。 プランナの推定コストは線形ではなく、そのため、テーブルの大小によって異なる計画を選択する可能性があります。 極端な例ですが、テーブルが1ディスクページしか占めない場合、インデックスが使用できる、できないに関係なく、ほとんど常にシーケンシャルスキャン計画を得ることになります。 プランナは、どのような場合でもテーブルを処理するために1ディスクページ読み取りを行うので、インデックスを参照するための追加的ページ読み取りを行う価値がないことを知っています。
| [1] | 本節の例は、8.2開発版ソースのリグレッション試験データベースから、VACUUM ANALYZEを実行した後で取り出したものです。 実際にこの例を試すと、似たような結果になるはずですが、おそらく推定コストや行数は多少異なることになるでしょう。 ANALYZEによる統計情報は厳密なものではなくランダムなサンプリングを行った結果であるからです。 |