PolarDB PostgreSQLは、受信する各クエリのクエリプランを考案する。 クエリ構造とデータのプロパティに一致する適切なプランを選択することは、優れたパフォーマンスのために絶対に重要です。そのため、システムには、適切なプランを選択しようとする複雑なプランナーが含まれます。 EXPLAINコマンドを使用して、プランナーがクエリに対して作成するクエリプランを確認できます。 計画読書は習得するためにある程度の経験を必要とする芸術ですが、このセクションでは基本をカバーしようとします。
このセクションの例は、VACUUM ANALYZEを実行した後の回帰テストデータベースから引用したものです。 例を自分で試すと同様の結果が得られるはずですが、ANALYZEの統計は正確ではなくランダムなサンプルであり、コストは本質的にプラットフォームに依存するため、推定コストと行数はわずかに異なる場合があります。
この例では、EXPLAINのデフォルトの「テキスト」出力形式を使用しています。 EXPLAINの出力をさらに分析するためにプログラムにフィードする場合は、代わりにマシンで読み取り可能な出力形式 (XML、JSON、またはYAML) を使用する必要があります。
説明の基本
クエリプランの構造は、プランノードのツリーである。 ツリーの最下部レベルのノードはスキャンノードです。つまり、テーブルから生の行を返します。 シーケンシャルスキャン、インデックススキャン、ビットマップインデックススキャンなど、テーブルアクセス方法ごとに異なるタイプのスキャンノードがあります。 VALUES句やFROMのset-returning関数など、独自のスキャンノードタイプを持つテーブル以外の行ソースもあります。 クエリが生の行に対して結合、集約、ソート、または他の操作を必要とする場合、これらの操作を実行するためにスキャンノードの上に追加のノードがあります。 ここでも、通常、これらの操作を実行するための可能な方法は複数あります。そのため、ここでもさまざまなノードタイプが表示されます。 EXPLAINの出力は、プラン・ツリー内の各ノードに対して1つの行を有し、基本ノード・タイプと、プランナがそのプラン・ノードの実行のために作成したコスト推定値とを示す。 ノードの追加のプロパティを示すために、ノードのサマリー行からインデントされた追加の行が表示される場合があります。 一番最初の行 (一番上のノードの要約行) は、計画の推定総実行コストを有し、計画者が最小化しようとするのはこの数である。
出力がどのように見えるかを示すための簡単な例を次に示します。
EXPLAIN SELECT * からtenk1;
クエリプラン
-------------------------------------------------------------
Seqスキャンでtenk1 (コスト=0.00 .. 458.00行=10000幅=244) このクエリにはWHERE句がないため、テーブルのすべての行をスキャンする必要があります。そのため、プランナーは単純なシーケンシャルスキャンプランを使用することを選択しています。 括弧内に引用されている数字は (左から右へ) です。
推定スタートアップコスト。 これは、出力フェーズが開始できる前に費やされる時間であり、例えば、ソートノードにおいてソートを行うための時間である。
推定総コスト。 これは、計画ノードが完了まで実行される、すなわち、すべての利用可能な行が検索されるという仮定に基づいて述べられる。 実際には、ノードの親ノードは、使用可能なすべての行を読み取ることができない場合があります (以下の
LIMITの例を参照) 。このプランノードによって出力される推定行数。 ここでも、ノードは、完了まで実行されると仮定される。
このプランノードが出力する行の推定平均幅 (バイト単位) 。
コストは、プランナーのコストパラメータによって決定される任意の単位で測定されます。 従来の方法は、ディスクページフェッチの単位でコストを測定することです。つまり、seq_page_costisは従来1.0に設定され、他のコストパラメータはそれに対して設定されます。 このセクションの例は、デフォルトのコストパラメータで実行されます。
上位レベルのノードのコストには、そのすべての子ノードのコストが含まれていることを理解することが重要です。 また、コストはプランナーが気にかけていることだけを反映していることを認識することも重要です。 特に、コストは、クライアントに結果行を送信するのに費やされる時間を考慮しない。これは、実際の経過時間の重要な要素であり得るが、計画者は、計画を変更することによってそれを変更することができないので、それを無視する。 (すべての正しい計画は同じ行セットを出力します、私たちは信頼します。)
行の値は、プランノードによって処理またはスキャンされた行の数ではなく、ノードによって出力された数であるため、少し注意が必要です。 これは、ノードで適用されているWHERE句条件によるフィルタリングの結果として、スキャンされた数よりも少ないことがよくあります。 理想的には、最上位レベルの行の見積もりは、クエリによって実際に返された、更新された、または削除された行の数に近似します。
私たちの例に戻る:
EXPLAIN SELECT * からtenk1;
クエリプラン
-------------------------------------------------------------
Seqスキャンでtenk1 (コスト=0.00 .. 458.00行=10000幅=244) これらの数は非常に簡単に得られる。 あなたがするならば:
SELECT relpages、reltuples FROM pg_class WHERE relname = 'tenk1';tenk1に358のディスクページと10000行があることがわかります。 推定コストは、(ディスクページ読み取り * seq_page_cost) + (スキャンされた行 * cpu_tuple_cost) として計算されます。 デフォルトでは、seq_page_costは1.0、cpu_tuple_costは0.01であるため、推定コストは (358*1.0) + (10000*0.01) = 458です。
次に、クエリを変更してWHERE条件を追加しましょう。
EXPLAIN SELECT * からtenk1 WHERE unique1 < 7000;
クエリプラン
------------------------------------------------------------
Seqスキャンでtenk1 (コスト=0.00 .. 483.00行=7001幅=244)
フィルター: (unique1 < 7000) EXPLAIN出力は、Seq Scanプランノードにアタッチされた "filter" 条件として適用されているWHERE句を示しています。 これは、プランノードがスキャンする行ごとに条件をチェックし、条件に合格した行のみを出力することを意味します。 WHERE句のため、出力行の見積もりが減少しました。 ただし、スキャンはまだすべての10000行を訪問する必要があるため、コストは減少しません。実際、WHERE条件のチェックに費やされた余分なCPU時間を反映するために、(正確には * cpu_operator_costを10000して) 少し上昇しています。
このクエリが選択する実際の行数は7000ですが、行の推定は概算にすぎません。 この実験を複製しようとすると、おそらくわずかに異なる推定値が得られます。さらに、ANALYZEコマンドごとに変更される可能性があります。これは、ANALYZEによって生成された統計がテーブルのランダム化されたサンプルから取得されるためです。
それでは、条件をより制限的にしましょう。
EXPLAIN SELECT * からtenk1 WHERE unique1 < 100;
クエリプラン
------------------------------------------------------------------------------
tenk1のビットマップヒープスキャン (コスト=5.07 .. 229.20行=101幅=244)
Recheck Cond :( unique1 < 100)
-> tenk1_unique1のビットマップインデックススキャン (コスト=0.00 .. 5.04行=101幅=0)
Index Cond: (unique1 < 100) ここで、プランナは2段階プランを使用することを決定しました。子プランノードがインデックスを訪問してインデックス条件に一致する行の位置を見つけ、次に上位プランノードがテーブル自体からそれらの行を実際にフェッチします。 行を個別にフェッチすることは、それらを順次読み取るよりもはるかに高価ですが、テーブルのすべてのページを訪問する必要があるわけではないため、これは順次スキャンよりも安価です。 (2つのプランレベルを使用する理由は、上位プランノードが、インデックスによって識別された行位置を読み取る前に物理的順序にソートして、別々のフェッチのコストを最小限に抑えるためです。 ノード名に記載されている「ビットマップ」は、ソートを行う機構である。
次に、WHERE句に別の条件を追加しましょう。
EXPLAIN SELECT * からtenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';
クエリプラン
------------------------------------------------------------------------------
tenk1のビットマップヒープスキャン (コスト=5.04 .. 229.43行=1幅=244)
Recheck Cond :( unique1 < 100)
フィルター: (stringu1 = 'xxx'::name)
-> tenk1_unique1のビットマップインデックススキャン (コスト=0.00 .. 5.04行=101幅=0)
Index Cond: (unique1 < 100) stringu1 = 'xxx' という条件を追加すると、出力行数の推定値が減少しますが、同じ行セットにアクセスする必要があるため、コストは減少しません。 このインデックスはunique1列にのみ存在するため、stringu1句をインデックス条件として適用することはできません。 代わりに、インデックスによって取得された行にフィルタとして適用されます。 したがって、この余分なチェックを反映して、実際にはコストがわずかに上昇しています。
場合によっては、プランナーは「単純な」インデックススキャン計画を好むでしょう。
EXPLAIN SELECT * からtenk1 WHERE unique1 = 42;
クエリプラン
------------------------------------------------------------------------------
tenk1でtenk1_unique1を使用したインデックススキャン (コスト=0.29 .. 8.30行=1幅=244)
インデックスCond :( unique1 = 42) このタイプのプランでは、テーブルの行はインデックス順にフェッチされるため、読み取るのにさらに費用がかかりますが、行の場所をソートする余分なコストがかかるほど少ないため、それだけの価値がありません。 このプランタイプは、1行だけを取得するクエリでよく見られます。 また、インデックスの順序に一致するORDER BY条件を持つクエリにもよく使用されます。これは、order BYを満たすために追加の並べ替え手順が必要ないためです。 この例では、インデックスがすでに暗黙的に要求された順序付けを提供しているため、ORDER BY unique1を追加すると同じプランが使用されます。
プランナは、いくつかの方法でORDER BY句を実装することができる。 上記の例は、そのような順序付け句が暗黙的に実装され得ることを示す。 プランナーは、明示的なソートステップを追加することもできます。
EXPLAIN SELECT * からtenk1 ORDER BY unique1;
クエリプラン
-------------------------------------------------------------------
並べ替え (コスト=1109.39 .. 1134.39行=10000幅=244)
ソートキー: unique1
-> Seqスキャンでtenk1 (コスト=0.00 .. 445.00行=10000幅=244) プランの一部が必要なソートキーのプレフィックスの順序付けを保証する場合、プランナーは代わりに増分ソートステップを使用することを決定します。
EXPLAIN SELECT * からtenk1 ORDER BY four, ten LIMIT 100;
クエリプラン
------------------------------------------------------------------------------------------------------
制限 (コスト=521.06 .. 538.05行=100幅=244)
-> 増分ソート (コスト=521.06 .. 2220.95行=10000幅=244)
ソートキー: 4、10
Presortedキー: 4
-> tenk1でindex_tenk1_on_fourを使用したインデックススキャン (コスト=0.29 .. 1510.08行=10000幅=244) 通常のソートと比較して、ソートすると、結果セット全体がソートされる前にタプルを返すことができます。これにより、特にLIMITクエリによる最適化が可能になります。 また、メモリ使用量とディスクへのソートの流出の可能性を減らすこともできますが、結果セットを複数のソートバッチに分割するオーバーヘッドが増加するという犠牲が伴います。
WHEREで参照されている複数の列に別々のインデックスがある場合、プランナーはインデックスのANDまたはorの組み合わせを使用することを選択できます。
EXPLAIN SELECT * からtenk1 WHERE unique1 < 100 AND unique2 > 9000;
クエリプラン
-------------------------------------------------------------------------------------
tenk1のビットマップヒープスキャン (コスト=25.08 .. 60.21行=10幅=244)
Recheck Cond :( (unique1 < 100) AND (unique2 > 9000))
-> BitmapAnd (コスト=25.08 .. 25.08行=10幅=0)
-> tenk1_unique1のビットマップインデックススキャン (コスト=0.00 .. 5.04行=101幅=0)
Index Cond: (unique1 < 100)
-> tenk1_unique2のビットマップインデックススキャン (コスト=0.00 .. 19.78行=999幅=0)
Index Cond :( unique2 > 9000) ただし、これには両方のインデックスにアクセスする必要があるため、1つのインデックスだけを使用して他の条件をフィルターとして扱う場合と比較して、必ずしも勝利ではありません。 関係する範囲を変更すると、それに応じてプランが変更されます。
LIMITの効果を示す例を次に示します。
EXPLAIN SELECT * からtenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;
クエリプラン
-------------------------------------------------------------------------------------
制限 (コスト=0.29 .. 14.48行=2幅=244)
-> tenk1でtenk1_unique2を使用したインデックススキャン (コスト=0.29 .. 71.27行=10幅=244)
Index Cond :( unique2 > 9000)
フィルター: (unique1 < 100) これは上記と同じクエリですが、すべての行を取得する必要がないようにLIMITを追加し、プランナーは何をすべきかについて考えを変えました。 インデックススキャンノードの合計コストと行数は、完了まで実行されたかのように表示されます。 ただし、Limitノードは、これらの行の5分の1のみを取得した後に停止すると予想されるため、その合計コストは5分の1にすぎません。これは、クエリの実際の推定コストです。 このプランは、Limitノードを前のプランに追加するよりも優先されます。これは、Limitがビットマップスキャンの開始コストを支払うことを回避できないため、このアプローチでは合計コストが25単位を超えるためです。
議論してきた列を使用して、2つのテーブルに参加してみましょう。
EXPLAIN SELECT *
tenk1 t1、tenk2 t2から
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;
クエリプラン
-------------------------------------------------------------------------------------
ネストされたループ (コスト=4.65 .. 118.62行=10幅=488)
-> ビットマップのヒープスキャンtenk1 t1 (コスト=4.36 .. 39.47行=10幅=244)
Recheck Cond :( unique1 < 10)
-> tenk1_unique1のビットマップインデックススキャン (コスト=0.00 .. 4.36行=10 width=0)
Index Cond: (unique1 < 10)
-> tenk2 t2でtenk2_unique2を使用したインデックススキャン (コスト=0.29 .. 7.91行=1幅=244)
インデックスCond: (unique2 = t1.unique2) このプランでは、入力または子として2つのテーブルスキャンを持つネストループ結合ノードがあります。 ノード概要行のインデントは、プランツリー構造を反映しています。 結合の最初の、または「外側の」子は、以前に見たものと同様のビットマップスキャンです。 そのコストと行数は、SELECTから得られるものと同じです... WHERE unique1 < 10そのノードでWHERE句unique1 < 10を適用しているためです。 t1.unique2 = t2.unique2句はまだ関係がないため、外側スキャンの行数には影響しません。 ネストされたループ結合ノードは、外側の子から取得された各行に対して1回、その第2の、すなわち「内側の」子を実行する。 現在の外側の行の列の値は内側のスキャンに差し込むことができます。ここでは、外側の行のt1.unique2の値が利用可能であるため、単純なSELECT... WHERE t2.unique2 =case。 (t2のインデックススキャンの繰り返し中に発生すると予想されるキャッシュの結果として、実際には推定コストは上記よりも少し低くなります。) 次に、ループノードのコストは、外側走査のコストに加えて、各外側行に対する内側走査の1回の繰り返し (ここでは10*7.91) に加えて、結合処理のための少しのCPU時間に基づいて設定される。
この例では、結合の出力行数は2つのスキャンの行数の積と同じですが、両方のテーブルに言及する追加のWHERE句が存在する可能性があり、結合ポイントでのみ適用でき、どちらの入力スキャンにも適用できないため、すべての場合に当てはまります。 例は次のとおりです。
EXPLAIN SELECT *
tenk1 t1、tenk2 t2から
どこt1.unique1 < 10とt2.unique2 < 10とt1.hude< t2.hude;
クエリプラン
-------------------------------------------------------------------------------------
ネストされたループ (コスト=4.65 .. 49.46行=33幅=488)
参加フィルター :( t1.hundred < t2.hundred)
-> ビットマップのヒープスキャンtenk1 t1 (コスト=4.36 .. 39.47行=10幅=244)
Recheck Cond :( unique1 < 10)
-> tenk1_unique1のビットマップインデックススキャン (コスト=0.00 .. 4.36行=10 width=0)
Index Cond: (unique1 < 10)
-> マテリアライズ (コスト=0.29 .. 8.51行=10幅=244)
-> tenk2 t2でtenk2_unique2を使用したインデックススキャン (コスト=0.29 .. 8.46行=10幅=244)
インデックスCond: (unique2 < 10) tenk2_unique2インデックスでt1.hundred < t2.hundredという条件をテストできないため、結合ノードで適用されます。 これは、結合ノードの推定出力行数を減少させるが、いずれの入力走査も変更しない。
ここで、プランナーは、materializeプランノードをその上に配置することによって、結合の内部関係を「マテリアライズ」することを選択していることに注意してください。 これは、ネストされたループ結合ノードが、外部関係からの各行に対して1回、そのデータを10回読み取る必要があるとしても、t2インデックススキャンが1回だけ行われることを意味する。 Materializeノードは、読み取られたデータをメモリに保存し、その後の各パスでメモリからデータを返します。
外部結合を処理すると、「結合フィルター」条件とプレーン「フィルター」条件の両方がアタッチされた結合プランノードが表示される場合があります。 Join Filter条件は、外側結合のON句から取得されるため、join Filter条件に失敗した行は、null拡張行として出力されます。 ただし、外部結合ルールの後にプレーンフィルタ条件が適用されるため、無条件に行を削除するように動作します。 内部結合では、これらのタイプのフィルタの間に意味の違いはありません。
クエリの選択性を少し変更すると、非常に異なる結合計画が得られる可能性があります。
EXPLAIN SELECT *
tenk1 t1、tenk2 t2から
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
クエリプラン
-------------------------------------------------------------------------------------
ハッシュ結合 (コスト=230.47 .. 713.98行=101幅=488)
ハッシュコンド :( t2.unique2 = t1.unique2)
-> Seqスキャンでtenk2 t2 (コスト=0.00 .. 445.00行=10000幅=244)
-> ハッシュ (コスト=229.20 .. 229.20行=101幅=244)
-> ビットマップのヒープスキャンtenk1 t1 (コスト=5.07 .. 229.20行=101幅=244)
Recheck Cond :( unique1 < 100)
-> tenk1_unique1のビットマップインデックススキャン (コスト=0.00 .. 5.04行=101幅=0)
Index Cond: (unique1 < 100) ここで、プランナーは、ハッシュ結合を使用することを選択しており、この結合では、一方のテーブルの行がメモリ内ハッシュテーブルに入力され、その後、他方のテーブルがスキャンされ、ハッシュテーブルが各行との一致についてプローブされる。 ここでも、インデントがプラン構造をどのように反映しているかに注意してください。tenk1のビットマップスキャンは、ハッシュテーブルを構築するハッシュノードへの入力です。 次に、Hash Joinノードに返されます。hash Joinノードは、外部の子プランから行を読み取り、ハッシュテーブルでそれぞれを検索します。
別の可能なタイプの結合は、ここに示すマージ結合です。
EXPLAIN SELECT *
tenk1 t1、onek t2から
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
クエリプラン
-------------------------------------------------------------------------------------
結合結合 (コスト=198.11 .. 268.19行=10幅=488)
マージCond :( t1.unique2 = t2.unique2)
-> tenk1 t1でtenk1_unique2を使用したインデックススキャン (コスト=0.29 .. 656.28行=101幅=244)
フィルター: (unique1 < 100)
-> 並べ替え (コスト=197.83 .. 200.33行=1000幅=244)
ソートキー: t2.unique2
-> Seqスキャンonek t2 (コスト=0.00 .. 148.00行=1000幅=244) マージ結合では、入力データを結合キーでソートする必要があります。 このプランでは、tenk1データは、インデックススキャンを使用して正しい順序で行を訪問することによってソートされますが、onekには、そのテーブルで訪問する行がさらに多いため、シーケンシャルスキャンとソートが推奨されます。 (シーケンシャル・スキャン・アンド・ソートは、インデックス・スキャンによって要求される非シーケンシャル・ディスク・アクセスのために、多くの行をソートするためにインデックス・スキャンを頻繁に打ち負かす。)
バリアントプランを見る1つの方法は、有効 /無効フラグを使用して、プランナーに最も安いと思われる戦略を無視させることです。 たとえば、前の例のテーブルonekを処理する最良の方法であると確信していない場合は、試してみてください。
SET enable_sort = off;
EXPLAIN SELECT *
tenk1 t1、onek t2から
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
クエリプラン
------------------------------------------------------------------------------------------
結合結合 (コスト=0.56 .. 292.65行=10幅=488)
マージCond :( t1.unique2 = t2.unique2)
-> tenk1 t1でtenk1_unique2を使用したインデックススキャン (コスト=0.29 .. 656.28行=101幅=244)
フィルター: (unique1 < 100)
-> onek t2でonek_unique2を使用したインデックススキャン (コスト=0.28 .. 224.79行=1000幅=244) これは、プランナーが、インデックススキャンによるonekのソートは、シーケンシャルスキャンアンドソートよりも約12% 費用がかかると考えていることを示しています。 もちろん、次の質問はそれについて正しいかどうかです。 以下に説明するように、EXPLAIN ANALYZEを使用して調査できます。
分析の説明
EXPLAINのANALYZEオプションを使用して、プランナーの見積もりの精度を確認できます。 このオプションを使用すると、EXPLAINは実際にクエリを実行し、各プランノード内に累積された真の行カウントと真の実行時間が、プレーンのEXPLAINが示すのと同じ推定値とともに表示されます。 たとえば、次のような結果が得られる場合があります。
EXPLAIN分析SELECT *
tenk1 t1、tenk2 t2から
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;
クエリプラン
---------------------------------------------------------------------------------------------------------------------------------
ネストされたループ (コスト=4.65 .. 118.62行=10幅=488) (実時間=0.128 .. 0.377行=10ループ=1)
-> Bitmap Heap Scan on tenk1 t1 (コスト=4.36 .. 39.47行=10幅=244) (実時間=0.057 .. 0.121行=10ループ=1)
Recheck Cond :( unique1 < 10)
-> tenk1_unique1のビットマップインデックススキャン (コスト=0.00 .. 4.36行=10幅=0) (実際の時間=0.024 .. 0.024行=10ループ=1)
Index Cond: (unique1 < 10)
-> tenk2 t2でtenk2_unique2を使用したインデックススキャン (コスト=0.29 .. 7.91行=1幅=244) (実際の時間=0.021 .. 0.022行=1ループ=10)
インデックスCond: (unique2 = t1.unique2)
計画時間: 0.181 ms
実行時間: 0.501 ms 「実際の時間」値は、リアルタイムのミリ秒単位であるが、コスト推定値は、任意の単位で表されるので、一致する可能性は低いことに留意されたい。 通常最も重要なことは、推定された行数が現実に合理的に近いかどうかです。 この例では、見積もりはすべて無効でしたが、実際には非常に珍しいことです。
いくつかのクエリプランでは、サブプランノードを複数回実行することが可能である。 たとえば、内部インデックススキャンは、上記のネストされたループプランの外側の行ごとに1回実行されます。 このような場合、ループ値はノードの実行総数を報告し、表示される実際の時間と行の値は実行ごとの平均です。 これは、コスト見積もりが表示される方法と比較できる数値を作成するために行われます。 ループ値を掛けて、ノードで実際に費やされた合計時間を取得します。 上記の例では、tenk2でインデックススキャンを実行するために合計0.220ミリ秒を費やしました。
場合によっては、EXPLAIN ANALYZEは、プランノードの実行時間と行数を超える追加の実行統計を示します。 たとえば、並べ替えノードとハッシュノードは追加情報を提供します。
EXPLAIN分析SELECT *
tenk1 t1、tenk2 t2から
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;
クエリプラン
--------------------------------------------------------------------------------------------------------------------------------------------
並べ替え (コスト=717.34 .. 717.59行=101幅=488) (実際の時間=7.761 .. 7.774行=100ループ=1)
ソートキー: t1.fivethous
ソート方法: quicksortメモリ: 77kB
-> ハッシュ結合 (コスト=230.47 .. 713.98行=101幅=488) (実際の時間=0.711 .. 7.427行=100ループ=1)
ハッシュコンド :( t2.unique2 = t1.unique2)
-> Seq tenk2 t2でスキャン (コスト=0.00 .. 445.00行=10000幅=244) (実際の時間=0.007 .. 2.583行=10000ループ=1)
-> ハッシュ (コスト=229.20 .. 229.20行=101幅=244) (実際の時間=0.659 .. 0.659行=100ループ=1)
バケット: 1024バッチ: 1メモリ使用量: 28kB
-> Bitmap Heap Scan on tenk1 t1 (コスト=5.07 .. 229.20行=101幅=244) (実時間=0.080 .. 0.526行=100ループ=1)
Recheck Cond :( unique1 < 100)
-> tenk1_unique1のビットマップインデックススキャン (コスト=0.00 .. 5.04行=101幅=0) (実際の時間=0.049 .. 0.049行=100ループ=1)
Index Cond: (unique1 < 100)
計画時間: 0.194 ms
実行時間: 8.008 ms Sortノードには、使用されたソート方法 (特に、ソートがメモリ内かディスク上か) と、必要なメモリまたはディスク容量が表示されます。 ハッシュノードには、ハッシュバケットとバッチの数、およびハッシュテーブルに使用されるメモリのピーク量が表示されます。 (バッチの数が1を超える場合、ディスク領域の使用も含まれますが、それは示されていません。)
別のタイプの追加情報は、フィルタ条件によって削除された行の数です。
EXPLAIN ANALYZE SELECT * からテン <7;
クエリプラン
---------------------------------------------------------------------------------------------------------
Seq tenk1のスキャン (コスト=0.00 .. 483.00行=7000幅=244) (実際の時間=0.016 .. 5.107行=7000ループ=1)
フィルター :( ten < 7)
フィルターによって削除された行: 3000
計画時間: 0.083 ms
実行時間: 5.905 ms これらのカウントは、結合ノードで適用されるフィルタ条件にとって特に価値がある。 「Rows Removed」行は、少なくとも1つのスキャンされた行、または結合ノードの場合には潜在的な結合対がフィルタ条件によって拒否された場合にのみ現れる。
フィルタ条件と同様のケースは、「損失のある」インデックススキャンで発生します。 たとえば、特定の点を含むポリゴンのこの検索を考えてみましょう。
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5、2.0)';
クエリプラン
------------------------------------------------------------------------------------------------------
Seq polygon_tblのスキャン (コスト=0.00 .. 1.05行=1幅=32) (実際の時間=0.044 .. 0.044行=0ループ=1)
フィルタ: (f1 @> '((0.5,2))':: ポリゴン)
フィルターによって削除された行: 4
計画時間: 0.040 ms
実行時間: 0.083 ms プランナーは、このサンプルテーブルが小さすぎてインデックススキャンを気にすることができないと (非常に正確に) 考えているため、すべての行がフィルタ条件によって拒否される単純なシーケンシャルスキャンがあります。 しかし、インデックススキャンを強制的に使用すると、次のようになります。
SET enable_seqscanをオフにします。
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> ポリゴン '(0.5、2.0)';
クエリプラン
--------------------------------------------------------------------------------------------------------------------------
polygon_tblでgpolygonindを使用したインデックススキャン (コスト=0.13 .. 8.15行=1幅=32) (実際の時間=0.062 .. 0.062行=0ループ=1)
Index Cond: (f1 @> '((0.5,2))'::polygon)
インデックスによって削除された行Recheck: 1
計画時間: 0.034 ms
実行時間: 0.144 ms ここでは、インデックスが1つの候補行を返し、インデックス条件の再チェックによって拒否されたことがわかります。 これは、GiSTインデックスがポリゴン包含テストでは「非可逆」であるために発生します。実際には、ターゲットと重なるポリゴンを持つ行を返し、それらの行に対して正確な包含テストを実行する必要があります。
EXPLAINにはBUFFERSオプションがあり、ANALYZEと共に使用してさらに多くのランタイム統計を取得できます。
EXPLAIN (ANALYZE、BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
クエリプラン
---------------------------------------------------------------------------------------------------------------------------------
ビットマップヒープtenk1のスキャン (コスト=25.08 .. 60.21行=10幅=244) (実際の時間=0.323 .. 0.342行=10ループ=1)
Recheck Cond :( (unique1 < 100) AND (unique2 > 9000))
バッファ: shared hit=15
-> BitmapAnd (コスト=25.08 .. 25.08行=10幅=0) (実際の時間=0.309 .. 0.309行=0ループ=1)
バッファ: shared hit=7
-> tenk1_unique1のビットマップインデックススキャン (コスト=0.00 .. 5.04行=101幅=0) (実際の時間=0.043 .. 0.043行=100ループ=1)
Index Cond: (unique1 < 100)
バッファ: shared hit=2
-> tenk1_unique2のビットマップインデックススキャン (コスト=0.00 .. 19.78行=999幅=0) (実際の時間=0.227 .. 0.227行=999ループ=1)
Index Cond :( unique2 > 9000)
バッファ: shared hit=5
計画時間: 0.088 ms
実行時間: 0.423 ms BUFFERSによって提供される番号は、クエリのどの部分が最もI/O集約的であるかを識別するのに役立ちます。
EXPLAIN ANALYZEは実際にクエリを実行するため、クエリが出力する結果が破棄されてEXPLAINデータが印刷される場合でも、副作用は通常どおり発生します。 テーブルを変更せずにデータ変更クエリを分析する場合は、後でコマンドをロールバックできます。
BEGIN;
EXPLAIN ANALYZE UPDATE tenk1 SET百=百 + 1 WHERE unique1 < 100;
クエリプラン
--------------------------------------------------------------------------------------------------------------------------------
tenk1の更新 (コスト=5.07 .. 229.46行=101幅=250) (実際の時間=14.628 .. 14.628行=0ループ=1)
-> ビットマップのヒープスキャン (コスト=5.07 .. 229.46行=101幅=250) (実際の時間=0.101 .. 0.439行=100ループ=1)
Recheck Cond :( unique1 < 100)
-> tenk1_unique1のビットマップインデックススキャン (コスト=0.00 .. 5.04行=101幅=0) (実際の時間=0.043 .. 0.043行=100ループ=1)
Index Cond: (unique1 < 100)
計画時間: 0.079 ms
実行時間: 14.727 ms
ロールバック; この例で見られるように、クエリがINSERT、UPDATE、またはDELETEコマンドの場合、テーブル変更を適用する実際の作業は、最上位のInsert、Update、またはDeleteプランノードによって行われます。 このノードの下のプランノードは、古い行の位置を特定し、および /または新しいデータを計算する作業を実行する。 そのため、上記では、すでに見たのと同じ種類のビットマップテーブルスキャンが表示され、その出力は、更新された行を格納するUpdateノードに供給されます。 データ変更ノードはかなりの実行時間がかかる可能性がありますが (ここでは、時間の大部分を消費しています) 、プランナーは現在、その作業を考慮するためにコスト見積もりに何も追加していません。 これは、実行する作業がすべての正しいクエリ計画で同じであるため、計画の決定には影響しないためです。
UPDATEまたはDELETEコマンドが継承階層に影響を与える場合、出力は次のようになります。
EXPLAIN UPDATE親SET f2 = f2 + 1 WHERE f1 = 101;
クエリプラン
-----------------------------------------------------------------------------------
親の更新 (コスト=0.00 .. 24.53行=4幅=14)
親の更新
child1の更新
child2の更新
child3の更新
-> Seq親のスキャン (コスト=0.00 .. 0.00行=1幅=14)
フィルター :( f1 = 101)
-> childr1でchild1_f1_keyを使用したインデックススキャン (コスト=0.15 .. 8.17行=1幅=14)
Index Cond :( f1 = 101)
-> child2でchild2_f1_keyを使用したインデックススキャン (コスト=0.15 .. 8.17行=1幅=14)
Index Cond :( f1 = 101)
-> child3でchild3_f1_keyを使用したインデックススキャン (コスト=0.15 .. 8.17行=1幅=14)
Index Cond :( f1 = 101) この例では、Updateノードは、3つの子テーブルと、最初に言及した親テーブルとを考慮する必要がある。 したがって、テーブルごとに1つずつ、4つの入力スキャンサブプランがあります。 明確にするために、更新ノードは、対応するサブプランと同じ順序で、更新される特定のターゲットテーブルを示すように注釈が付けられている。
EXPLAIN ANALYZEで示される計画時間は、解析されたクエリからクエリプランを生成して最適化するのにかかった時間です。 解析や書き換えは含まれません。
EXPLAIN ANALYZEで示される実行時間には、エグゼキュータの起動時間とシャットダウン時間、および起動されたトリガーの実行時間が含まれますが、解析、書き換え、計画時間は含まれません。 関連するInsert、Update、またはDeleteノードの時間には、[BEFORE] トリガーの実行に費やされた時間が含まれます。ただし、計画全体の完了後にAFTERトリガーが起動されるため、AFTERトリガーの実行に費やされた時間はカウントされません。 各トリガーで費やされた合計時間 (BEFOREまたはAFTER) も別々に表示されます。 遅延制約トリガは、トランザクションの終了まで実行されず、したがって、EXPLAIN ANALYZEによって全く考慮されないことに留意されたい。
注意事項
EXPLAIN ANALYZEによって測定される実行時間が、同じクエリの通常の実行から逸脱する可能性がある2つの重要な方法がある。 第1に、出力行がクライアントに配信されないので、ネットワーク伝送コストおよびI/O変換コストは含まれない。 第2に、EXPLAIN ANALYZEによって追加される測定オーバーヘッドは、特に、遅いgettimeofday() オペレーティング・システム・コールを有するマシン上で、大きくなり得る。 pg_test_timingツールを使用して、システムのタイミングのオーバーヘッドを測定できます。
EXPLAINの結果は、実際にテストしている状況とは大きく異なる状況に外挿すべきではありません。たとえば、おもちゃサイズのテーブルの結果を大きなテーブルに適用するとは想定できません。 プランナーのコスト見積もりは線形ではないため、テーブルの大小に応じて別のプランを選択する場合があります。 極端な例として、1つのディスクページしか占有しないテーブルでは、インデックスが使用可能かどうかにかかわらず、ほぼ常にシーケンシャルスキャン計画が得られます。 プランナーは、どのような場合でもテーブルを処理するために1つのディスクページの読み取りが必要になることを認識しているため、インデックスを確認するために追加のページの読み取りを実行する価値はありません。 (上記のpolygoning_tblの例でこれが発生していることがわかりました。)
実際の値と推定値がうまく一致しない場合がありますが、実際には何も間違っていません。 このようなケースの1つは、LIMITまたは同様の効果によってプランノードの実行が短く停止された場合に発生します。 たとえば、以前に使用したLIMITクエリでは、
EXPLAIN ANALYZE SELECT * からtenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;
クエリプラン
-------------------------------------------------------------------------------------------------------------------------------
制限 (コスト=0.29 .. 14.71行=2幅=244) (実時間=0.177 .. 0.249行=2ループ=1)
-> tenk1でtenk1_unique2を使用したインデックススキャン (コスト=0.29 .. 72.42行=10幅=244) (実際の時間=0.174 .. 0.244行=2ループ=1)
Index Cond :( unique2 > 9000)
フィルター: (unique1 < 100)
フィルターによって削除された行: 287
計画時間: 0.096 ms
実行時間: 0.336 ms つまり、インデックススキャンノードの推定コストと行数は、完了まで実行されたかのように表示されます。 しかし、実際には、Limitノードは、2つを取得した後に行を要求することを停止したため、実際の行カウントは2に過ぎず、実行時間はコスト推定値よりも短い。 これは推定エラーではなく、推定値と真の値が表示される方法の不一致です。
マージ結合には、不注意を混乱させる可能性のある測定アーティファクトもあります。 マージ結合は、他の入力を使い果たし、1つの入力の次のキー値が他の入力の最後のキー値よりも大きい場合、1つの入力の読み取りを停止します。このような場合、一致はなくなり、最初の入力の残りをスキャンする必要はありません。 これにより、LIMITで言及されているような結果で、1人の子供のすべてを読むことはできません。 また、外側の (第1の) 子が重複するキー値を有する行を含む場合、内側の (第2の) 子は、そのキー値に一致する行の部分についてバックアップされ、再走査される。EXPLAIN ANALYZEは、実際の追加の行であるかのように、同じ内側の行のこれらの繰り返し排出をカウントします。 多くの外側の重複がある場合、内側の子プランノードの報告された実際の行数は、実際に内側の関係にある行の数よりも大幅に大きくなる可能性があります。
BitmapAndおよびBitmapOrノードは、実装の制限により、実際の行カウントを常にゼロとして報告します。
通常、EXPLAINはプランナーによって作成されたすべてのプランノードを表示します。 しかし、実行者が、計画時に利用できなかったパラメータ値に基づいて、特定のノードが行を生成できないために実行する必要がないと判断できる場合があります。 (現在、これはパーティションテーブルをスキャンしているAppendまたはMergeAppendノードの子ノードに対してのみ発生します。) この場合、これらのプランノードはEXPLAIN出力から省略され、代わりにSubplans Removed: アノテーションが表示されます。