EXPLAIN — 問い合わせ文の実行計画を表示する
EXPLAIN [ (option
[, ...] ) ]statement
EXPLAIN [ ANALYZE ] [ VERBOSE ]statement
ここでoption
は以下のいずれかを取ることができます。 ANALYZE [boolean
] VERBOSE [boolean
] COSTS [boolean
] BUFFERS [boolean
] TIMING [boolean
] SUMMARY [boolean
] FORMAT { TEXT | XML | JSON | YAML }
与えられた文に対して、PostgreSQLプランナが生成する実行計画を表示します。 実行計画は、問い合わせ文が参照するテーブル(複数の場合もある)をスキャンする方法(単純なシーケンシャルスキャン、インデックススキャンなど)、複数のテーブルを参照する場合に、各テーブルから取り出した行を結合するために使用する結合アルゴリズムを示すものです。
表示内容の中でも、最も重要なのは、文の実行にかかるコストの見積もりです。
これは、プランナが文の実行にかかる時間(任意の、しかし慣習的にはディスクページ抽出を意味するコスト単位で計測)を推測したものです。
具体的には、2つの数が表示されます。
1つは最初の行が返されるまでのスタートアップコスト、もう1つはすべての行が返されるまでの合計コストです。
ほとんどの問い合わせにとって問題となるのは合計コストの方ですが、EXISTS
副問い合わせなどのコンテキストでは、プランナは合計コストが最も短くなるプランよりも、スタートアップコストが最も短くなるプランを選びます(エクゼキュータは行を1つ取得した後に停止するからです)。
また、LIMIT
句を使って問い合わせが返す行数を制限する場合、プランナは実際にはどの計画が一番低コストになるかを概算するため、全体を処理した場合のコストの間で適切な補間を行います。
ANALYZE
オプションを付けると、計画を作るだけでなく、文が実際に実行されます。
この場合は、各計画ノードで費された総経過時間(ミリ秒単位)と実際に返された全行数など、実際の実行時の統計情報が追加表示されます。
プランナの推測と実際の値が近いかどうかを確認するために、このオプションは有用です。
ANALYZE
を使用した場合は、文が実際に実行されることを忘れないでください。
EXPLAIN
はSELECT
が返す出力をまったく表示しませんが、文に伴う副作用は通常通り発生します。
INSERT
、UPDATE
、DELETE
、CREATE TABLE AS
、EXECUTE
文に対して、データに影響を与えないようにEXPLAIN ANALYZE
を実行したい場合は、以下の方法を使用してください。
BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;
ANALYZE
およびVERBOSE
オプションのみが、この順序でのみ、オプションリストを括弧で括ることなく、指定可能です。
PostgreSQL 9.0より前までは、括弧がない構文のみがサポートされていました。
すべての新しいオプションは括弧付き構文のみでサポートされることを想定しています。
ANALYZE
コマンドを実行し、実際の実行時間やその他の統計情報を表示します。
このパラメータのデフォルトはFALSE
です。
VERBOSE
計画についての追加情報を出力します。
具体的には、計画ツリー、スキーマ修飾テーブル、関数名内の各ノードに対して出力列リストを含めます。
常に範囲テーブルの別名を付けて式内の変数を命名し、また常に統計情報が表示される各トリガの名前を出力します。
このパラメータのデフォルトはFALSE
です。
COSTS
各計画ノードの推定起動コストと総コスト、さらに推定行数と各行の推定幅に関する情報を含めます。
このパラメータのデフォルトはTRUE
です。
BUFFERS
バッファの使用状況に関する情報を含めます。
具体的には、共有ブロックのヒット数、読み取り数、ダーティブロック数、書き出し数、ローカルブロックのヒット数、ダーティブロック数、読み取り数、書き出し数、一時ブロックの読み取り数、書き出し数が含められます。
ヒットとは、必要な時にキャッシュ内にそのブロックが見つかったため読み取りが避けられたことを意味します。
共有ブロックには、通常のテーブルとインデックスからのデータが含まれます。
ローカルブロックには、一時テーブルとそのインデックスからのデータが含まれます。
一時ブロックには、ソートやハッシュ、マテリアライズ計画ノードなどで使用される短期間有効なデータが含まれます。
ダーティブロック数は、これまでは変更がなかったがその問い合わせによって変更されたブロックの数を示します。
書き出しブロック数は、問い合わせ処理の間にバックエンドにより、ダーティ状態だったブロックの内キャッシュから追い出されたブロックの数を示します。
上位レベルのノードで表示されるブロック数には、その子ノードすべてで使用されるブロックが含まれます。
テキスト形式では、非ゼロの値のみが出力されます。
このパラメータはANALYZE
パラメータも有効である場合にのみ使用することができます。
デフォルトはFALSE
です。
TIMING
実際のスタートアップ時間とノードで費やされた時間が追加表示されます。
一部のシステムでは、システムクロックを何度も読み取るオーバーヘッドのため問い合わせがかなり低速になる可能性があります。
このため、実際の時間ではなく実際の行数のみが必要であるのであれば、このパラメータはFALSE
に設定する方が有益でしょう。
文全体の実行時間は、このオプションによってノードレベルの時間計測が無効であった場合であっても、常に計測されます。
このパラメータはANALYZE
パラメータも有効である場合にのみ使用することができます。
デフォルトはTRUE
です。
SUMMARY
要約情報(例えば、時間の情報の合計)を問い合わせ計画の後に出力します。
要約情報はANALYZE
が使われるときはデフォルトで含まれ、それ以外の場合はデフォルトでは含まれませんが、このオプションを使えば有効にできます。
EXPLAIN EXECUTE
の計画時間には、計画をキャッシュから取得するのに要する時間、および必要なら再計画するのに要する時間も含まれます。
FORMAT
出力形式を指定します。
TEXT、XML、JSON、YAMLを指定可能です。
TEXT以外の出力にはTEXT出力と同じ情報が含まれていますが、プログラムによる解析がより容易になります。
このパラメータのデフォルトはTEXT
です。
boolean
選択したオプションを有効とするか無効とするかを指定します。
オプションを有効にするためにはTRUE
、ON
、1
のいずれかを書きます。
無効にするにはFALSE
、OFF
、 0
のいずれかを書きます。
boolean
値は省略可能です。
省略時はTRUE
とみなされます。
statement
実行計画の表示対象となる、SELECT
、INSERT
、UPDATE
、DELETE
、VALUES
、EXECUTE
、DECLARE
、CREATE TABLE AS
、CREATE MATERIALIZED VIEW AS
のいずれかの文です。
コマンドの結果は、statement
に対して選択された計画をテキストで説明します。
オプションで、実行時の統計情報で注釈が付けられます。
14.1では出力される情報について説明します。
PostgreSQL問い合わせプランナが十分な情報を使って問合せを最適化できるようにするには、問い合わせ内で使用されるすべてのテーブルに関するpg_statistic
のデータを最新状態にしなければなりません。
通常自動バキュームデーモンにより自動的に処理されます。
しかし最近その内容が大きく変更されたテーブルでは、自動バキュームがその変更に追いつくまで待つのではなく、手作業によるANALYZEを実行する必要があるかもしれません。
実行計画内の各ノードの実行時コストを測定するために、現在のEXPLAIN ANALYZE
実装は、問い合わせ実行に対し、情報収集のためのオーバーヘッドを加えます。
この結果、ある問い合わせについてのEXPLAIN ANALYZE
実行が、普通に問い合わせを実行した場合より非常に時間がかかることがあります。
このオーバーヘッドの量は問い合わせの性質と使用するプラットフォームに依存します。
実行の間非常に短い時間を必要とする計画ノードに関して、時刻を得るためのシステムコールの操作が相対的に低速なプラットフォーム上で最悪な場合が発生します。
integer
列を1つ持ち、10000行が存在するテーブルに対して、単純な問い合わせを行った場合の問い合わせ計画を表示します。
EXPLAIN SELECT * FROM foo; QUERY PLAN --------------------------------------------------------- Seq Scan on foo (cost=0.00..155.00 rows=10000 width=4) (1 row)
以下は同じ問い合わせをJSON出力形式で出力したものです。
EXPLAIN (FORMAT JSON) SELECT * FROM foo; QUERY PLAN -------------------------------- [ + { + "Plan": { + "Node Type": "Seq Scan",+ "Relation Name": "foo", + "Alias": "foo", + "Startup Cost": 0.00, + "Total Cost": 155.00, + "Plan Rows": 10000, + "Plan Width": 4 + } + } + ] (1 row)
インデックスが存在し、問い合わせのWHERE
条件でインデックスを利用できる場合、EXPLAIN
は異なる計画を表示する可能性があります。
EXPLAIN SELECT * FROM foo WHERE i = 4; QUERY PLAN -------------------------------------------------------------- Index Scan using fi on foo (cost=0.00..5.98 rows=1 width=4) Index Cond: (i = 4) (2 rows)
以下は同じ問い合わせをYAML形式で表したものです。
EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4'; QUERY PLAN ------------------------------- - Plan: + Node Type: "Index Scan" + Scan Direction: "Forward"+ Index Name: "fi" + Relation Name: "foo" + Alias: "foo" + Startup Cost: 0.00 + Total Cost: 5.98 + Plan Rows: 1 + Plan Width: 4 + Index Cond: "(i = 4)" (1 row)
読者への演習としてXML形式については記載しません。
以下は同じ計画ですが、コスト推定値を出力しません。
EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4; QUERY PLAN ---------------------------- Index Scan using fi on foo Index Cond: (i = 4) (2 rows)
次に、集約関数を使用した問い合わせに対する問い合わせ計画の例を示します。
EXPLAIN SELECT sum(i) FROM foo WHERE i < 10; QUERY PLAN --------------------------------------------------------------------- Aggregate (cost=23.93..23.93 rows=1 width=4) -> Index Scan using fi on foo (cost=0.00..23.92 rows=6 width=4) Index Cond: (i < 10) (3 rows)
以下は、EXPLAIN EXECUTE
によってプリペアド文に対する実行計画を表示する例です。
PREPARE query(int, int) AS SELECT sum(bar) FROM test WHERE id > $1 AND id < $2 GROUP BY foo; EXPLAIN ANALYZE EXECUTE query(100, 200); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ HashAggregate (cost=9.54..9.54 rows=1 width=8) (actual time=0.156..0.161 rows=11 loops=1) Group Key: foo -> Index Scan using test_pkey on test (cost=0.29..9.29 rows=50 width=8) (actual time=0.039..0.091 rows=99 loops=1) Index Cond: ((id > $1) AND (id < $2)) Planning time: 0.197 ms Execution time: 0.225 ms (6 rows)
もちろん、ここで示した具体的な数値は対象とするテーブルの実際の中身によって変わります。
また、この数値や選択された問い合わせ戦略は、プランナの改良のため、PostgreSQLのリリース間で異なる可能性がありますので注意してください。
さらに、ANALYZE
コマンドは、データの統計情報を推定する際にランダムなサンプリングを行うため、実際のテーブル内の分布が変わっていなくても、新たにANALYZE
を実行すると推定コストが変わることがあります。
標準SQLではEXPLAIN
文は定義されていません。