他のバージョンの文書 ： 17 | 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2
PostgreSQL 17.0文書
第14章 性能に関するヒント
前へ 上へ14.1. EXPLAINの利用 次へ

14.1. EXPLAINの利用 #

PostgreSQLは受理した問い合わせから問い合わせ計画を作り出します。 問い合わせの構造と含まれるデータの性質に適した正しい問い合わせ計画を選択することが、良い性能を得るために非常に重要になります。 ですので、システムには優れた計画の選択を試みる複雑なプランナが存在します。 EXPLAINコマンドを使えば、任意の問い合わせに対してプランナがどのような問い合わせ計画を作ったのかわかります。 問い合わせ計画を読みこなすには、ある程度の経験が必要です。 本節ではその基本を提供しようと考えます。

本節の例は、v17の開発版ソースを用いてVACUUM ANALYZEを実行した後でリグレッションテストデータベースから取り出したものです。 実際にこの例を試すと、似たような結果になるはずですが、おそらく推定コストや行数は多少異なることになるでしょう。 ANALYZEによる統計情報は厳密なものではなくランダムなサンプリングを行った結果であり、また、コストは本質的にプラットフォームに何かしら依存するためです。

例では、簡潔で人が読みやすいEXPLAINのデフォルトの「text」出力書式を使用します。 今後の解析でEXPLAINの出力をプログラムに渡すことを考えているのであれば、代わりに機械読み取りが容易な出力書式（XML、JSON、YAML）のいずれかを使用する必要があります。

14.1.1. EXPLAINの基本 #

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

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

EXPLAIN SELECT * FROM tenk1;

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

この問い合わせにはWHERE句がありませんので、テーブル行をすべてスキャンしなければなりません。 このためプランナは単純なシーケンシャルスキャン計画を使用することを選びました。 （左から右に）括弧で囲まれた数値には以下のものがあります。

  • 初期処理の推定コスト。 出力段階が開始できるようになる前に消費される時間、例えば、SORTノードで実行されるソート処理の時間です。

  • 全体推定コスト。 これは計画ノードが実行完了である、つまりすべての利用可能な行を受け取ることを前提として示されます。 実際には、ノードの親ノードはすべての利用可能な行を読む前に停止する可能性があります（以下のLIMITの例を参照）。

  • この計画ノードが出力する行の推定数。ここでも、ノードが実行を完了することを前提としています。

  • この計画ノードが出力する行の（バイト単位での）推定平均幅。

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

上位ノードのコストには、すべての子ノードのコストもその中に含まれていることを理解することは重要です。 このコストはプランナが関与するコストのみ反映する点もまた重要です。 とりわけ、出力値をテキスト形式に変換したり、クライアントに送信したりするために要する時間はこのコストでは考慮されません。 これらは実際の経過時間の重要な要素となる可能性がありますが、計画を変更しても変えることができないため、プランナはこれらのコストを無視します。 （全ての正しい計画は同じ行セットを出力するものと期待されます。）

rowsの値は、計画ノードによって処理あるいはスキャンされた行数を表しておらず、ノードによって発行された行数を表すので、多少扱いにくくなっています。 該当ノードに適用されるすべてのWHERE句条件によるフィルタ処理の結果、スキャンされる行より少ない行数になることがよくあります。 理想的には、最上位の行数の推定値は、実際に問い合わせによって返され、更新され、あるいは削除された概算の行数となります。

例に戻ります。 

EXPLAIN SELECT * FROM tenk1;

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

これらの数値はとても素直に導かれます。以下を実行すると、 

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

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

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

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

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

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

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

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

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

                                  QUERY PLAN
-------------------------------------------------------------------​-----------
 Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
         Index Cond: (unique1 < 100)

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

ここでWHERE句に別の条件を付与してみましょう。 

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

                                  QUERY PLAN
-------------------------------------------------------------------​-----------
 Bitmap Heap Scan on tenk1  (cost=5.04..225.20 rows=1 width=244)
   Recheck Cond: (unique1 < 100)
   Filter: (stringu1 = 'xxx'::name)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
         Index Cond: (unique1 < 100)

追加されたstringu1 = 'xxx'条件は出力行数推定値を減らしますが、同じ行集合にアクセスしなければなりませんので、コストは減りません。 これは、このインデックスがunique1列に対してのみ存在していて、stringu1の句をインデックス条件として適用できないためです。 代わりに、インデックスによって取り出される行に対するフィルタとして適用されます。 これにより、追加の検査分を反映するため、コストは実際には少し上がります。

場合によってはプランナは「単純な」インデックススキャン計画を選択します。 

EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;

                                 QUERY PLAN
-------------------------------------------------------------------​----------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.29..8.30 rows=1 width=244)
   Index Cond: (unique1 = 42)

この種の計画では、テーブル行はインデックス順で取り出されます。 このため読み取りがより高価になりますが、この場合取り出す行数が少ないため、改めて行位置をソートし直すための追加コストは割に合いません。 単一の行のみを取り出す問い合わせでは、この計画種類がよく現れます。 また、ORDER BYを満たすために必要となる余分な必要なソート処理がないため、インデックスの順序に一致するORDER BY条件を持つ問い合わせでよく使用されます。 この例では、ORDER BY unique1を追加すると、要求された順序がインデックスによってすでに暗黙的に提供されているため、同じ計画が使用されます。

プランナはORDER BY句をいくつかの方法で実装できます。 上の例ではこのようなORDER BY句を暗黙的に実装できることを示しています。 プランナは明示的なSortステップを追加もします。 

EXPLAIN SELECT * FROM tenk1 ORDER BY unique1;

                            QUERY PLAN
-------------------------------------------------------------------
 Sort  (cost=1109.39..1134.39 rows=10000 width=244)
   Sort Key: unique1
   ->  Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

ソートキーで必要な接頭辞の順序がプランの一部で保証されている場合、インクリメンタルソート(Incremental Sort)ステップを使用することを決定できます。 

EXPLAIN SELECT * FROM tenk1 ORDER BY hundred, ten LIMIT 100;

                                              QUERY PLAN
-------------------------------------------------------------------​-----------------------------
 Limit  (cost=19.35..39.49 rows=100 width=244)
   ->  Incremental Sort  (cost=19.35..2033.39 rows=10000 width=244)
         Sort Key: hundred, ten
         Presorted Key: hundred
         ->  Index Scan using tenk1_hundred on tenk1  (cost=0.29..1574.20 rows=10000 width=244)

通常のソートと比較して、インクリメンタルソートは、結果セット全体がソートされる前にタプルを戻すことができます。 これにより、特にLIMITがある問い合わせで最適化が可能になります。 また、メモリ使用量が削減され、ソートがディスクにオーバーフローする可能性が減少しますが、結果セットを複数のソートバッチに分割するオーバーヘッドが増加という代償を払うことになります。

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

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

                                     QUERY PLAN
-------------------------------------------------------------------​------------------
 Bitmap Heap Scan on tenk1  (cost=25.07..60.11 rows=10 width=244)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   ->  BitmapAnd  (cost=25.07..25.07 rows=10 width=0)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
               Index Cond: (unique1 < 100)
         ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..19.78 rows=999 width=0)
               Index Cond: (unique2 > 9000)

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

以下にLIMITの影響を示す例を示します。 

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

                                     QUERY PLAN
-------------------------------------------------------------------​------------------
 Limit  (cost=0.29..14.28 rows=2 width=244)
   ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..70.27 rows=10 width=244)
         Index Cond: (unique2 > 9000)
         Filter: (unique1 < 100)

これは上と同じ問い合わせですが、すべての行を取り出す必要がないためLIMITを付けています。 プランナはどうすべきかについて考えを変えました。 インデックススキャンノードの総コストと総行数があたかも実行完了したかのように表示されていることに注意してください。 しかしLimitノードが、これらの行の1/5だけを取り出した後で停止することが想定されています。 そのため総コストは1/5程度のみとなり、これが問い合わせの実際の推定コストとなります。 この計画は、以前の計画にLimitノードを追加することより好まれます。 以前の計画へのLimit追加でのLimitはビットマップスキャンの起動コストを払うことを避けることができないため、総コストは25単位を超えてしまうからです。

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

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

                                      QUERY PLAN
-------------------------------------------------------------------​-------------------
 Nested Loop  (cost=4.65..118.50 rows=10 width=488)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
         Recheck Cond: (unique1 < 10)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
               Index Cond: (unique1 < 10)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244)
         Index Cond: (unique2 = t1.unique2)

この計画では、入力または子として２つのテーブルスキャンを持つネステッドループ結合ノードがあります。 計画のツリー構造を反映して、ノード要約行はインデント付けされます。 結合の先頭、「外部」、子は以前に説明したものと似たビットマップスキャンです。 そのコストと行数は、該当ノードにunique1 < 10 WHERE句が適用されるため、SELECT ... WHERE unique1 < 10で得られたものと同じです。 この段階ではt1.unique2 = t2.unique2句は関係しておらず、外部スキャンにおける出力行数に影響していません。 ネステッドループ結合ノードは、外部の子から得られた行毎に、その２番目または「内部の」子を一回実行します。 現在の外部の行からの列の値は内部スキャンに組み込まれます。 ここで、外部行からのt1.unique2の値が利用できますので、上述の単純なSELECT ... WHERE t2.unique2 = constantの場合に示したものと似た計画とコストが得られます。 （実際、推定コストは、t2に対するインデックススキャンが繰り返される間に発生することが想定されるキャッシュの結果、上で示した値よりわずかに低くなります。） ループノードのコストは、外部スキャンのコストと、各々の外部の行に対して内部スキャンが繰り返されることによるコスト（ここでは10 * 7.90）を加え、さらに結合処理を行うための少々のCPU時間を加えたものになります。

この例では、結合の出力行数は2つのスキャンの出力行数の積に等しくなっていますが、いつもそうなるわけではありません。 2つのテーブルに関係するWHERE句は、入力スキャン時ではなく、結合を行う際に適用されるからです。 以下が例です。 

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

                                         QUERY PLAN
-------------------------------------------------------------------​--------------------------
 Nested Loop  (cost=4.65..49.36 rows=33 width=488)
   Join Filter: (t1.hundred < t2.hundred)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244)
         Recheck Cond: (unique1 < 10)
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0)
               Index Cond: (unique1 < 10)
   ->  Materialize  (cost=0.29..8.51 rows=10 width=244)
         ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..8.46 rows=10 width=244)
               Index Cond: (unique2 < 10)

条件t1.hundred < t2.hundredはtenk2_unique2インデックスの中では試験されません。 このため結合ノードで適用されます。 これは結合ノードの推定出力行数を減らしはしますが、入力スキャンには影響しません。

ここではプランナが、具体化計画ノードをその上に挿入することで、結合の内部リレーションの「具体化」を選択していることに注意してください。 これは、たとえネステッドループ結合ノードが外部リレーションから各行につき一度、そのデータを10回読む必要があったとしても、t2インデックススキャンが一度だけ行なわれることを意味します。 具体化ノードはそのデータを読んだときにメモリに保存し、その後の読み出しではそのデータをメモリから返します。

外部結合を扱う時、「結合フィルタ」および通常の「フィルタ」の両方が付随する結合計画ノードが現れる可能性があります。 結合フィルタ条件は外部結合のON句を元にしますので、結合フィルタ条件に合わない行がNULLで展開された行として発行され続けます。 しかし通常のフィルタ条件が外部結合規則の後に適用され、条件に合わない行は削除されます。 内部結合では、これらのフィルタ種類の間に意味的な違いはありません。

問い合わせの選択性を少し変更すると、非常に異なる結合計画が得られるかもしれません。 

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

                                        QUERY PLAN
-------------------------------------------------------------------​-----------------------
 Hash Join  (cost=226.23..709.73 rows=100 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244)
   ->  Hash  (cost=224.98..224.98 rows=100 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
                     Index Cond: (unique1 < 100)

ここでプランナはハッシュ結合の使用を選択しました。 片方のテーブルの行がメモリ内のハッシュテーブルに格納され、もう片方のテーブルがスキャンされた後、各行に対して一致するかどうかハッシュテーブルを探索します。 再度、インデントが計画の構造が表されていることに注意してください。 tenk1に対するビットマップスキャンはハッシュノードへの入力です。 外部の子計画から行を読み取り、各行に対してハッシュテーブルを検索します。

他にも、以下に示すようなマージ結合という結合があり得ます。 

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

                                        QUERY PLAN
-------------------------------------------------------------------​-----------------------
 Merge Join  (cost=0.56..233.49 rows=10 width=488)
   Merge Cond: (t1.unique2 = t2.unique2)
   ->  Index Scan using tenk1_unique2 on tenk1 t1  (cost=0.29..643.28 rows=100 width=244)
         Filter: (unique1 < 100)
   ->  Index Scan using onek_unique2 on onek t2  (cost=0.28..166.28 rows=1000 width=244)

マージ結合は、結合キーでソートされる入力データを必要とします。 この例では、各入力がインデックススキャンを使用して正しい順序で行にアクセスすることでソートされますが、シーケンシャルスキャンとソートも使用できます。 （多くの行をソートする場合、インデックススキャンでは非シーケンシャルなディスクアクセスが必要となるため、シーケンシャルスキャンとソートの方がインデックススキャンより優ります。）

19.7.1に記載したenable/disableフラグを使用して、プランナが最も良いと考えている戦略を強制的に無視させる方法により、異なった計画を観察することができます。 （非常に原始的なツールですが、利用価値があります。 14.3も参照してください。） 例えば、マージ結合が前の例の最善の結合タイプであると確信できない場合は、以下を試みることができます。 

SET enable_mergejoin = off;

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

                                        QUERY PLAN
-------------------------------------------------------------------​-----------------------
 Hash Join  (cost=226.23..344.08 rows=10 width=488)
   Hash Cond: (t2.unique2 = t1.unique2)
   ->  Seq Scan on onek t2  (cost=0.00..114.00 rows=1000 width=244)
   ->  Hash  (cost=224.98..224.98 rows=100 width=244)
         ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244)
               Recheck Cond: (unique1 < 100)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
                     Index Cond: (unique1 < 100)

これは、このケースではハッシュ結合がマージ結合よりも50%近く高価になるとプランナが考えていることを示しています。 当然ながら、次の疑問はこれが正しいかどうかでしょう。 以下で説明するように、EXPLAIN ANALYZEを使って調べることができます。

一部の問い合わせ計画には、元の問い合わせ内の副SELECTから発生する 副計画（subplans）が含まれています。 このような問い合わせは、通常の結合プランに変換できる場合もありますが、変換できない場合は、次のような計画が生成されます。 

EXPLAIN VERBOSE SELECT unique1
FROM tenk1 t
WHERE t.ten < ALL (SELECT o.ten FROM onek o WHERE o.four = t.four);

                               QUERY PLAN
-------------------------------------------------------------------​------
 Seq Scan on public.tenk1 t  (cost=0.00..586095.00 rows=5000 width=4)
   Output: t.unique1
   Filter: (ALL (t.ten < (SubPlan 1).col1))
   SubPlan 1
     ->  Seq Scan on public.onek o  (cost=0.00..116.50 rows=250 width=4)
           Output: o.ten
           Filter: (o.four = t.four)

このかなり人工的な例は、二つの点を説明するのに役立ちます。すなわち、外側の計画階層からの値は副計画に渡すことができて（ここではt.fourが渡されます）、副SELECTの結果は外側の計画で利用できます。 これらの結果値は、EXPLAINで(subplan_name).colNのような記法で表示され、これは副SELECTのN番目の出力列を指します。

上の例で、ALL演算子は、外側の問い合わせの各行に対して副計画を実行します（高い推定コストの原因となります）。 一部の問い合わせでは、それを回避するためにハッシュされた副計画（hashed subplan）を使用できます。 

EXPLAIN SELECT *
FROM tenk1 t
WHERE t.unique1 NOT IN (SELECT o.unique1 FROM onek o);

                                         QUERY PLAN
-------------------------------------------------------------------​-------------------------
 Seq Scan on tenk1 t  (cost=61.77..531.77 rows=5000 width=244)
   Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1)))
   SubPlan 1
     ->  Index Only Scan using onek_unique1 on onek o  (cost=0.28..59.27 rows=1000 width=4)
(4 rows)

ここでは、副計画が1回実行され、その出力がメモリ内のハッシュテーブルに置かれ、次に外側のANY演算子によって検査されます。 これには、副SELECTが外側の問い合わせの変数を参照しないことと、ANYの比較演算子がハッシュ適用できることが必要です。

外側の問い合わせの変数を参照しないことに加えて、副SELECTが複数行を返せない場合は、代わりにinitplanとして次のように計画作成されます。 

EXPLAIN VERBOSE SELECT unique1
FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer);

                             QUERY PLAN
------------------------------------------------------------​--------
 Seq Scan on public.tenk1 t1  (cost=0.02..470.02 rows=1000 width=4)
   Output: t1.unique1
   Filter: (t1.ten = (InitPlan 1).col1)
   InitPlan 1
     ->  Result  (cost=0.00..0.02 rows=1 width=4)
           Output: ((random() * '10'::double precision))::integer

initplanは外側の計画の実行ごとに1回だけ実行され、その結果は外側の計画の後の行で再利用するために保存されます。 したがって、この例ではrandom()は1回だけ評価され、t1.10のすべての値がランダムに選択された同じ整数と比較されます。 これは、副SELECT構造がない場合とは大きく異なります。

14.1.2. EXPLAIN ANALYZE #

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

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

                                                           QUERY PLAN
-------------------------------------------------------------------​--------------------------------------------------------------
 Nested Loop  (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10 loops=1)
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10 loops=1)
         Recheck Cond: (unique1 < 10)
         Heap Blocks: exact=10
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10 loops=1)
               Index Cond: (unique1 < 10)
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1 loops=10)
         Index Cond: (unique2 = t1.unique2)
 Planning Time: 0.485 ms
 Execution Time: 0.073 ms

「actual time」値は実時間をミリ秒単位で表されていること、cost推定値は何らかの単位で表されていることに注意してください。 ですからそのまま比較することはできません。 注目すべきもっとも重要な点は通常、推定行数が実際の値と合理的に近いかどうかです。 この例では、推定はすべて正確ですが、現実的にはあまりありません。

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

場合によっては、EXPLAIN ANALYZEは計画ノードの実行時間と行数以上の実行統計情報をさらに表示します。 例えば、ソートとハッシュノードでは以下のような追加情報を提供します。 

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

                                                                 QUERY PLAN
-------------------------------------------------------------------​-------------------------------------------------------------------​------
 Sort  (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100 loops=1)
   Sort Key: t1.fivethous
   Sort Method: quicksort  Memory: 74kB
   ->  Hash Join  (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100 loops=1)
         Hash Cond: (t2.unique2 = t1.unique2)
         ->  Seq Scan on tenk2 t2  (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000 loops=1)
         ->  Hash  (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 35kB
               ->  Bitmap Heap Scan on tenk1 t1  (cost=5.06..224.98 rows=100 width=244) (actual time=0.030..0.450 rows=100 loops=1)
                     Recheck Cond: (unique1 < 100)
                     Heap Blocks: exact=90
                     ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100 loops=1)
                           Index Cond: (unique1 < 100)
 Planning Time: 0.187 ms
 Execution Time: 3.036 ms

ソートノードは使用されるソート方式（具体的にはソートがメモリ内かディスク上か）および必要なメモリまたはディスクの容量を表示します。 ハッシュノードでは、ハッシュバケット数とバッチ数、ハッシュテーブルで使用されるメモリのピーク容量が表示されます。 （バッチ数が１を超える場合、同時にディスクの使用容量も含まれますが、表示はされません。）

他の種類の追加情報はフィルタ条件によって除外される行数があります。 

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;

                                               QUERY PLAN
-------------------------------------------------------------------​--------------------------------------
 Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000 loops=1)
   Filter: (ten < 7)
   Rows Removed by Filter: 3000
 Planning Time: 0.102 ms
 Execution Time: 2.145 ms

特に結合ノードで適用されるフィルタ条件ではこれらの数が有用です。 「Rows Removed」行は、少なくともスキャンされた１行、結合ノードにおける結合組み合わせの可能性がフィルタ条件によって拒絶された時にのみ現れます。

「非可逆」インデックススキャンはフィルタ条件に似た状況です。 例えば、特定の点を含有する多角形の検索を考えてみます。 

EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';

                                              QUERY PLAN
-------------------------------------------------------------------​-----------------------------------
 Seq Scan on polygon_tbl  (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0 loops=1)
   Filter: (f1 @> '((0.5,2))'::polygon)
   Rows Removed by Filter: 7
 Planning Time: 0.039 ms
 Execution Time: 0.033 ms

プランナは（ほぼ正確に）、インデックススキャンを考慮するには例のテーブルが小さ過ぎるとみなします。 このため、フィルタ条件によってすべての行が拒絶される、普通のシーケンシャルスキャンとなります。 しかしインデックススキャンの使用を強制するのであれば、以下のようにします。 

SET enable_seqscan TO off;

EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';

                                                        QUERY PLAN
-------------------------------------------------------------------​-------------------------------------------------------
 Index Scan using gpolygonind on polygon_tbl  (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0 loops=1)
   Index Cond: (f1 @> '((0.5,2))'::polygon)
   Rows Removed by Index Recheck: 1
 Planning Time: 0.039 ms
 Execution Time: 0.098 ms

ここで、インデックスが１つの候補行を返し、それがインデックス条件の再検査により拒絶されることが分かります。 多角形の含有試験ではGiSTインデックスが「非可逆」であるため、これは発生します。 実際には対象と重なる多角形を持つ行を返し、そしてこれらの行が正確に含有関係であることを試験しなければなりません。

EXPLAINには、より多くの実行時統計情報を取り出すために、ANALYZEに付与できるBUFFERSオプションがあります。 

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;

                                                           QUERY PLAN
-------------------------------------------------------------------​--------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=25.07..60.11 rows=10 width=244) (actual time=0.105..0.114 rows=10 loops=1)
   Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
   Heap Blocks: exact=10
   Buffers: shared hit=14 read=3
   ->  BitmapAnd  (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0 loops=1)
         Buffers: shared hit=4 read=3
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100 loops=1)
               Index Cond: (unique1 < 100)
               Buffers: shared hit=2
         ->  Bitmap Index Scan on tenk1_unique2  (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999 loops=1)
               Index Cond: (unique2 > 9000)
               Buffers: shared hit=2 read=3
 Planning:
   Buffers: shared hit=3
 Planning Time: 0.162 ms
 Execution Time: 0.143 ms

BUFFERSにより提供される数は、問い合わせのどの部分がもっとも大きいI/Oであるかを識別する役に立ちます。

EXPLAIN ANALYZEが実際に問い合わせを実行しますので、EXPLAINのデータを出力することを優先して問い合わせの出力が破棄されたとしても、何らかの副作用が通常通り発生することに注意してください。 テーブルを変更すること無くデータ変更問い合わせの解析を行いたければ、以下の例のように、実行後コマンドをロールバックしてください。 

BEGIN;

EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;

                                                           QUERY PLAN
-------------------------------------------------------------------​-------------------------------------------------------------
 Update on tenk1  (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0 loops=1)
   ->  Bitmap Heap Scan on tenk1  (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100 loops=1)
         Recheck Cond: (unique1 < 100)
         Heap Blocks: exact=90
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100 loops=1)
               Index Cond: (unique1 < 100)
 Planning Time: 0.151 ms
 Execution Time: 1.856 ms

ROLLBACK;

この例で分かるように、問い合わせがINSERT、UPDATE、DELETE、MERGEである場合、テーブル変更を行うための実作業は最上位のInsert、Update、Delete、Merge計画ノードで行われます。 このノード以下にある計画ノードは、古い行の検索、新しいデータの計算、あるいはその両方を行います。 このため、前に述べたものと同じ種類のビットマップテーブルスキャンがあり、その出力が更新される行を格納するUpdateノードに渡されることが分かります。 データ変更ノードが実行時間の多くを費やす可能性があります（現在これが一番多くの時間を費やしています）が、プランナは現在その作業を考慮してコスト推定に何も加えません。 これは、行われる作業がすべての正確な問い合わせ計画の作業と同一であるためであり、このため計画の決定に影響を与えません。

UPDATE、DELETE、MERGEコマンドがパーティションテーブルや継承階層に影響する場合には、出力は以下のようになるでしょう。 

EXPLAIN UPDATE gtest_parent SET f1 = CURRENT_DATE WHERE f2 = 101;

                                       QUERY PLAN
-------------------------------------------------------------------​---------------------
 Update on gtest_parent  (cost=0.00..3.06 rows=0 width=0)
   Update on gtest_child gtest_parent_1
   Update on gtest_child2 gtest_parent_2
   Update on gtest_child3 gtest_parent_3
   ->  Append  (cost=0.00..3.06 rows=3 width=14)
         ->  Seq Scan on gtest_child gtest_parent_1  (cost=0.00..1.01 rows=1 width=14)
               Filter: (f2 = 101)
         ->  Seq Scan on gtest_child2 gtest_parent_2  (cost=0.00..1.01 rows=1 width=14)
               Filter: (f2 = 101)
         ->  Seq Scan on gtest_child3 gtest_parent_3  (cost=0.00..1.01 rows=1 width=14)
               Filter: (f2 = 101)

この例では、Updateノードは3つの子テーブルを考慮しますが、元の問い合わせに記述されていたパーティションテーブルを考慮しません（そこにデータ格納されることはないため）。 そのため、テーブル毎に3つの入力スキャン副計画があります。 明確にするため、Updateノードには対応する副計画と同じ順に更新される特定の対象テーブルを示す注釈が付けられています。

EXPLAIN ANALYZEで表示されるPlanning timeは、解析された問い合わせから問い合わせ計画を生成し最適化するのに掛かった時間です。 解析と書き換えは含みません。

EXPLAIN ANALYZEで表示されるExecution time（実行時間）にはエグゼキュータの起動、停止時間、発行される何らかのトリガの実行時間も含まれますが、解析や書き換え、計画作成の時間は含まれません。 BEFOREトリガがあればその実行時間は関連するInsert、Update、Deleteノード用の時間に含まれます。 しかし、AFTERトリガは計画全体が完了した後に発行されますので、AFTERトリガの実行時間は計上されません。 また、各トリガ（BEFORE、AFTERのいずれか）で費やされる総時間は別々に表示されます。 しかし、遅延制約トリガはトランザクションが終わるまで実行されませんので、EXPLAIN ANALYZEでは考慮されないことに注意してください。

最上位ノードに表示される時間には、問い合わせの出力データを表示可能な形式に変換したり、それをクライアントに送信したりするのに要する時間は含まれません。 EXPLAIN ANALYZEはデータをクライアントに送信することはありませんが、SERIALIZEオプションを指定することで、問い合わせの出力データを表示可能な形式に変換し、そのために必要な時間を測定するように指示できます。 その時間は分けて表示され、また、それは合計のExecution timeにも含まれます。

14.1.3. 警告 #

EXPLAIN ANALYZEにより測定される実行時間が同じ問い合わせを普通に実行する場合と大きくそれる可能性がある、２つの重大な点があります。 １つ目は、出力行がクライアントに配信されませんので、ネットワーク転送コストが含まれないことです。 I/O変換のコストも、SERIALIZEが指定されない限り、含まれません。 ２つ目は、EXPLAIN ANALYZEによって加わる測定オーバーヘッドが大きくなることが、特にgettimeofday()オペレーティングシステムコールが低速なマシンであり得ることです。 pg_test_timingを用いて、使用中のシステムの時間測定にかかるオーバーヘッドを測ることができます。

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

実際の値と推定値がうまく合わないが本当は間違ったものがない場合があります。 こうした状況の１つは、LIMITや同様な効果により計画ノードの実行が短時間で終わる時に起こります。 例えば、以前に使用したLIMIT問い合わせでは 

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

                                                          QUERY PLAN
-------------------------------------------------------------------​------------------------------------------------------------
 Limit  (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2 loops=1)
   ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.29..70.50 rows=10 width=244) (actual time=0.051..0.070 rows=2 loops=1)
         Index Cond: (unique2 > 9000)
         Filter: (unique1 < 100)
         Rows Removed by Filter: 287
 Planning Time: 0.077 ms
 Execution Time: 0.086 ms

インデックススキャンノードの推定コストと行数が実行完了したかのように表示されます。 しかし現実では、Limitノードが２行を取り出した後に行の要求を停止します。 このため実際の行数は２行のみであり、実行時間は提示された推定コストより小さくなります。 これは推定間違いではなく、単なる推定値と本当の値を表示する方法における矛盾です。

またマージ結合には、注意しないと混乱を招く測定上の乱れがあります。 マージ結合は他の入力が使い尽くされ、ある入力の次のキー値が他の入力の最後のキー値より大きい場合、その入力の読み取りを停止します。 このような場合、これ以上一致することはあり得ず、最初の入力の残りをスキャンする必要がありません。 この結果、子のすべては読み取られず、LIMITの説明のようになります。 また、外部（最初）の子が重複するキー値を持つ行を含む場合、内部（２番目）の子はバックアップされ、そのキー値が一致する行部分を再度スキャンされます。 EXPLAIN ANALYZEはこうした繰り返される同じ内部行の排出を実際の追加される行と同様に計上します。 外部で多くの重複がある場合、内部の子計画ノードで繰り返される実際の行数は、内部リレーションにおける実際の行数より非常に多くなることがあり得ます。

実装上の制限のため、BitmapAndおよびBitmapOrノードは常に実際の行数をゼロと報告します。

通常EXPLAINはプランナが生成したすべてのプランノードを表示します。 しかし、プラン時にパラメータ値が入手できずそのノードが行を生成できないために、エグゼキュータがあるノードが実行不要であると判断できるケースがあります。 （今の所、これはパーティションテーブルをスキャンしているAppendあるいはMergeAppendノードの子ノードでのみ起きることがあります。） これが起きると、これらのプランノードはEXPLAINの出力から削除され、Subplans Removed: Nという注釈が代わりに表示されます。

前へ 上へ 次へ
第14章 性能に関するヒント ホーム 14.2. プランナで使用される統計情報