11.1. EXPLAINの利用

Postgresは与えられた問い合わせから 問い合わせ実行計画を作り出します。 問い合わせの構造と含まれるデータの性質に適した正しい問い合わせ実行計画を 作成することが良い性能を得るために非常に重要になります。 EXPLAINコマンドを使えばシステムが、どのような問 い合わせ実行計画を作ったのかわかります。 問い合わせ実行計画を読みこなすことは別にチュートリアルを 作らなければならないほどの技なのですが、残念ながら、それを書いている時間 ありません。 ここではてっとり早く端折った説明をします。

現在のところEXPLAINが出力する数値は以下のものです。

• 予想される初期処理のコスト(検索結果が出力されるまでに消費される 時間、たとえば SORT ノードで実行されるソート処理の時間)

• 予想される全体のコスト(結果のタプル全体が抽出される場合のみ。必ずし もそうでない場合もある。たとえば、LIMIT を使うとこのコスト全部がかから ない)

• このプランノードが出力する行数の予想(これもLIMITを考慮しません)。

• このプランノードが出力する行の平均幅(バイト単位)の予想。

これらのコストは、ディスクから読み出すページ単位で計測します(CPU 消費 見積りは、適当に恣意的に決めた基準にしたがってディスクページ単位に換算 されます。もしこれらの基準値の効果を知りたい場合は、 Administrator's Guideのランタイムコンフィギュレーション パラメータの一覧を見てください)。

特記すべき点は、上位のノードのコストは、すべての下位 のノードのコストを含んでいるということです。また、このコストはプランナ やオプティマイザが関与するコストしか反映していない点も重要です。とりわ け、結果のタプルをフロントエンドに転送するコストは、実際には処理時間の 大半を占める可能性があるにも関わらず、プランナは無視します。これは、プ ランを如何に変更しようともどうすることもできないからです(正しいプラン はどんなものであれ、すべて同じ結果のタプルを出力すると考えられます)。

出力行数についてはちょっと特殊です。処理や検索の対象となる行数 ではなく、 該当ノードに適用されるWEHRE節の制限から来る検索率の見積りを反映した、 通常より少ない行数になります。 理想的ならば、最上位の行数に関する見積もりが実際に問い合わせによって 返却、更新、あるいは削除される実際の行数になります(これもLIMITの効果を考慮しません)。

実際には可変長列の平均幅について関知していないため、行の平均幅は役 に立ちません。私は将来これを改良しようと思っていますが、行幅というもの はさほど使われているわけではないため、やるだけの価値があるかどうかわか りません。

いくつか例を示します(vacuum analyzeをかけたあとのregress testデータベー スを使っています。使用したシステムは 7.0リリース直前のものです)。

regression=# explain select * from tenk1;
NOTICE:  QUERY PLAN:

Seq Scan on tenk1  (cost=0.00..333.00 rows=10000 width=148)
    

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

select * from pg_class where relname = 'tenk1';
    

を実行すると、 tenk1には233のディスクページと10000タプルがあることがわかります。です から、単位あたり1.0とコストが決まっている233 ブロックの読み出し、それ に加えて これは現在 0.01 に設定されている cpu_tuple_cost(show cpu_tuple_costを試してみて下さい)を 1000 倍したものを加えたものがコ ストの見積りになるわけです。

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

regression=# explain select * from tenk1 where unique1 < 1000;
NOTICE:  QUERY PLAN:

Seq Scan on tenk1  (cost=0.00..358.00 rows=1000 width=148)
    

WHERE節があるため、出力行数の見積りが小さくなっています(不自然に見積り が正確なのは、単に tenk1 が非常に単純なケースであるからです。unique1 列には 0 から 9999 までの 10000 の異なる値が割り当てられているため、コ スト見積り部が最小値から最大値の間で線形補間を行なった結果の値にぴった り一致します)。しかし、依然として 100000 行すべてを走査する必要がある ため、コストは小さくなっていません。実際には、WHERE条件を検査するため に余分なCPUタイムがかかることを反映してほんの少しですがコストが上昇し ています。

ではさらに問い合わせを変更し、条件節の制限を強くしてみます。

regression=# explain select * from tenk1 where unique1 < 100;
NOTICE:  QUERY PLAN:

Index Scan using tenk1_unique1 on tenk1  (cost=0.00..89.35 rows=100 width=148)
    

WHERE節の条件を十分検索率の高いものにすると、プランナはシーケンススキャ ンよりもインデックススキャンが安価でであると決定するようになることが分 かります。この計画では、インデックスがあるためにたった100タプルだけを アクセスすればよいことになります。ですから、個々インデックスのアクセス は高価につくにも関わらず、この計画が採用されます。

別の検索条件を追加します。

regression=# explain select * from tenk1 where unique1 < 100 and
regression-# stringu1 = 'xxx';
NOTICE:  QUERY PLAN:

Index Scan using tenk1_unique1 on tenk1  (cost=0.00..89.60 rows=1 width=148)
    

追加した節 "stringu1 = 'xxx'" により出力行数の見積りは減少するものの、 検査するタプルの集合は変わらないためにコストは減っていません。

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

regression=# explain select * from tenk1 t1, tenk2 t2 where t1.unique1 < 100
regression-# and t1.unique2 = t2.unique2;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..144.07 rows=100 width=296)
  ->  Index Scan using tenk1_unique1 on tenk1 t1
             (cost=0.00..89.35 rows=100 width=148)
  ->  Index Scan using tenk2_unique2 on tenk2 t2
             (cost=0.00..0.53 rows=1 width=148)
    

この nested-loop 結合では、外側の走査に、最後の例で示したのと同じイン デックス走査が使われています。そして、"unique1 < 100" WHERE 節を該当ノー ドに適用しているため、コストと出力行数は変わっていません。この段階では "t1.unique2 = t2.unique2" 節は関係しておらず、外側の走査における出力行 数に影響していません。内側の走査では、外側の走査における現在のタプルの unique2 の値が内側のインデックス走査に投げ込まれ、インデックスの比較条 件 "t2.unique2 = constant" を生成します。したがって、内側の走査のプラ ンとコストは、たとえば"explain select * from tenk2 where unique2 = 42" のような問い合わせと同じです。ループノードのコストは、外側の走査のコス トと、各々の外側のタプルに対して内側の走査が繰り返されることによるコス ト(ここでは 100 * 0.53)を加え、更に結合処理を行うための少々のCPU時間を 加えたものになります。

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

プランナが最も良いと考えている戦略を強制的に否決することにより、別のプ ランを観察することができます(非常に原始的なツールですが、今現在使える のはこれだけです。Section 11.2も見てください)。

regression=# set enable_nestloop = off;
SET VARIABLE
regression=# explain select * from tenk1 t1, tenk2 t2 where t1.unique1 < 100
regression-# and t1.unique2 = t2.unique2;
NOTICE:  QUERY PLAN:

Hash Join  (cost=89.60..574.10 rows=100 width=296)
  ->  Seq Scan on tenk2 t2
               (cost=0.00..333.00 rows=10000 width=148)
  ->  Hash  (cost=89.35..89.35 rows=100 width=148)
        ->  Index Scan using tenk1_unique1 on tenk1 t1
               (cost=0.00..89.35 rows=100 width=148)
    

このプランでは、インデックス操作を使ってtenk1から必要な100行を取り出し てメモリ上のハッシュテーブルに格納し、tenk2に対して"t1.unique2 = t2.unique2"を満たすものがあるかどうかそのハッシュテーブルをチェックし ながら順次スキャンをtenk2に対して行います。tenk2の読み出しを開始するま では出力結果は得られないため、tenk1を読みだしてハッシュテーブルをセッ トするコストは、そのハッシュジョインに対しては初期処理のコストとなりま す。この結合に関するコスト見積りには、ハッシュテーブルを10000回検査す る非常に大きなCPUコストが含まれています。しかしながら、ハッシュテーブ ルのセットはこのプランでは一度しか行われないため、89.35 の 10000 倍の コストが課せられるわけではないことに注意して下さい。