EXPLAINの利用PostgreSQLは受理した問い合わせから問い合わせ計画を作り出します。問い合わせの構造と含まれるデータの性質に適した正しい問い合わせ計画を選択することが、良い性能を得るために非常に重要になります。システムには優れた計画の選択を試みる複雑なプランナが存在します。EXPLAINコマンドを使えば、任意の問い合わせに対してプランナがどのような問い合わせ計画を作ったのかわかります。問い合わせ計画を読みこなすことは、習得するにはある程度の経験が必要な技術です。本節ではその基本を提供しようと考えます。ANALYZEを実行した後でリグレッションテストデータベースから取り出したものです。実際にこの例を試すと、似たような結果になるはずですが、おそらく推定コストや行数は多少異なることになるでしょう。ANALYZEによる統計情報は厳密なものではなくランダムなサンプリングを行った結果であり、また、コストは本質的にプラットフォームに何かしら依存するためです。例では、簡潔で人が読みやすいEXPLAINのデフォルトの「text」出力書式を使用します。今後の解析でEXPLAINの出力をプログラムに渡すことを考えているのであれば、代わりに機械読み取りが容易な出力書式(XML、JSON、YAML)のいずれかを使用すべきです。問い合わせ計画は計画ノードのツリー構造です。ツリー構造の最下層ノードはスキャンノードで、テーブルから行そのものを返します。シーケンシャルスキャン、インデックススキャン、ビットマップインデックススキャンといったテーブルアクセス方法の違いに応じ、スキャンノードの種類に違いがあります。VALUES句やFROM内の集合を返す関数など独自のスキャンノード種類を持つ、テーブル行を元にしないものがあります。問い合わせが結合、集約、ソートなど、行そのものに対する操作を必要としている場合、スキャンノードの上位に更に、これらの操作を行うためのノードが追加されます。これらの操作の実現方法にも通常複数の方法がありますので、異なった種類のノードがここに出現することもあり得ます。EXPLAINには計画ツリー内の各ノードにつき1行の出力があり、基本ノード種類とプランナが生成したその計画ノードの実行に要するコスト推定値を示します。ノードの追加属性を表示するためにノードの要約行からインデント付けされた行が出力される可能性があります。最初の1行目(最上位ノード)には、計画全体の実行コスト推定値が含まれます。プランナはこの値が最小になるように動作します。どのような出力となるのかを示すためだけに、ここで簡単な例を示します。width=244)この問い合わせにはWHERE句がありませんので、テーブル行をすべてスキャンしなければなりません。このためプランナは単純なシーケンシャルスキャン計画を使用することを選びました。(左から右に)括弧で囲まれた数値には以下のものがあります。初期処理の推定コスト。出力段階が開始できるようになる前に消費される時間、例えば、SORTノードで実行されるソート処理の時間です。これは計画ノードが実行完了である、つまりすべての利用可能な行を受け取ることを前提として示されます。実際には、ノードの親ノードはすべての利用可能な行を読む前に停止する可能性があります(以下のLIMITの例を参照)。この計画ノードが出力する行の推定数。ここでも、ノードが実行を完了することを前提としています。この計画ノードが出力する行の(バイト単位での)推定平均幅。プランナコスト定数参照)によって決まる任意の単位で測定されます。取り出すディスクページ単位でコストを測定することが、伝統的な方式です。seq_page_costを慣習的に1.0に設定し、他のコストパラメータを相対的に設定します。本節の例では、デフォルトのコストパラメータで実行しています。上位ノードのコストには、すべての子ノードのコストもその中に含まれていることを理解することは重要です。このコストはプランナが関与するコストのみ反映する点もまた重要です。結果の行をクライアントに転送するコストは、実際の処理時間の重要な要因となる可能性があるにもかかわらず、考慮されません。プランナは、計画をいかに変更しようと、どうすることもできないため、これを無視します。(正しい計画はどんなものであれ、すべて同じ行を結果として出力すると信じています。)rowsの値は、計画ノードによって処理あるいはスキャンされた行数を表しておらず、ノードによって発行された行数を表すので、多少扱いにくくなっています。該当ノードに適用されるすべてのWHERE句条件によるフィルタ処理の結果、スキャンされる行より少ない行数になることがよくあります。理想的には、最上位の行数の推定値は、実際に問い合わせによって返され、更新され、あるいは削除された概算の行数となります。width=244)この数は非常に素直に導かれます。'tenk1';を実行すると、tenk1には358のディスクページと10000の行があることがわかります。cpu_tuple_cost)と計算されます。デフォルトでは、seq_page_costは1.0、cpu_tuple_costは0.01です。WHERE条件を加えて、問い合わせを変更してみます。Scan計画ノードに付随する「フィルタ」条件として適用されるWHERE句を表示していることに注意してください。これは、この計画ノードがスキャンした各行に対してその条件を検査することを意味し、その条件を通過したもののみが出力されます。WHERE句があるため、推定出力行数が小さくなっています。して10000行すべてをスキャンする必要があるため、コストは小さくなっていません。cpu_operator_cost)ですがコストが上昇しています。この問い合わせが選択する実際の行数は7000です。rowsの推定行数は概算値に過ぎません。この実験を2回実行した場合、おそらく多少異なる推定値を得るでしょう。言うと、これはANALYZEコマンドを行う度に変化することがあり得ます。ANALYZEで生成される統計情報は、テーブルのランダムな標本から取り出されるからです。条件をより強く制限してみます。100)ここでは、プランナは2段階の計画を使用することを決定しました。子の計画ノードは、インデックスを使用して、インデックス条件に合う行の場所を検索します。上位計画ノードが実際にテーブル自体からこれらの行を取り出します。行を別々に取り出すことは、シーケンシャルな読み取りに比べ非常に高価です。テーブルのすべてのページを読み取る必要はありませんので、シーケンシャルスキャンより低価になります。(2段階の計画を使用する理由は、別々に行を取り出すコストを最小にするために、上位の計画ノードがインデックスにより識別された行の位置を読み取る前に物理的な順序でソートすることです。ノードで記載されている「bitmap」は、ソートを行う機構の名前です。)ここでWHERE句に別の条件を付与してみましょう。'xxx'条件は出力行数推定値を減らしますが、同じ行集合にアクセスしなければなりませんので、コストは減りません。このインデックスがunique1列に対してのみ存在するため、stringu1句をインデックス条件として適用できないことに注意してください。代わりに、インデックスによって取り出される行に対するフィルタとして適用されます。これにより、追加の検査分を反映するため、コストは実際には少し上がります。場合によってはプランナは「単純な」インデックススキャン計画を選択します。42)この種の計画では、テーブル行はインデックス順で取り出されます。このため読み取りがより高価になりますが、この場合取り出す行数が少ないため、改めて行位置をソートし直すための追加コストは割に合いません。単一の行のみを取り出す問い合わせでは、この計画種類がよく現れます。BY条件を持つ問い合わせでよく使用されます。WHERE句で参照される複数の列に対して別々のインデックスが存在する場合、プランナはインデックスをANDやORで組み合わせて使用することを選択する可能性があります。9000)しかし、これは両方のインデックスを参照する必要があります。そのため、インデックスを1つ使用し、他の条件についてはフィルタとして扱う方法と比べて常に勝るとは限りません。含まれる範囲を変更すると、それに伴い計画も変わることが分かるでしょう。以下にLIMITの影響を示す例を示します。100)これは上と同じ問い合わせですが、すべての行を取り出す必要がないためLIMITを付けています。プランナはどうすべきかについて考えを変えました。インデックススキャンノードの総コストと総行数があたかも実行完了したかのように表示されていることに注意してください。Limitノードが、これらの内5行のみを取り出した後で停止することが想定されています。そのため総コストは1/5程度のみとなり、これが問い合わせの実際の推定コストとなります。この計画は、以前の計画にLimitノードを追加することより好まれます。Limitはビットマップスキャンの起動コストを払うことを避けることができないためです。このため総コストはこの方法に25単位ほど増加します。今まで説明に使ってきたフィールドを使って2つのテーブルを結合してみましょう。t1.unique2)この計画では、入力または子として2つのテーブルスキャンを持つ入れ子状ループ結合ノードがあります。計画のツリー構造を反映して、ノード要約行はインデント付けされます。以前に説明したものと似たビットマップスキャンです。10で得られたものと同じです。t2.unique2句は関係しておらず、外部スキャンにおける出力行数に影響していません。入れ子状ループ結合ノードは、外部の子から得られた行毎に、その2番目または「内部の」子を一回実行します。現在の外部の行からの列の値は内部スキャンに組み込まれます。constantの場合に示したものと似た計画とコストが得られます。(実際、推定コストは、t2に対するインデックススキャンが繰り返される間に発生することが想定されるキャッシュの結果、上で示した値よりわずかに低くなります。7.87)を加え、さらに結合処理を行うための少々のCPU時間を加えたものになります。この例では、結合の出力行数は2つのスキャンの出力行数の積に等しくなっていますが、いつもそうなるわけではありません。2つのテーブルに関係するWHERE句は、入力スキャン時ではなく、結合を行う際に適用されるからです。t2.hundredはtenk2_unique2インデックスの中では試験されません。このため結合ノードで適用されます。これは結合ノードの推定出力行数を減らしはしますが、入力スキャンには影響しません。ここではプランナが、具体化計画ノードをその上に挿入することで、結合の内部リレーションの「具体化」を選択していることに注意してください。これは、たとえ入れ子上ループ結合ノードが外部リレーションから各行につき一度、そのデータを10回読む必要があったとしても、t2インデックススキャンが一度だけ行なわれることを意味します。具体化ノードはそのデータを読んだときにメモリに保存し、その後の読み出しではそのデータをメモリから返します。外部結合を扱う時、「結合フィルタ」および通常の「フィルタ」の両方が付随する結合計画ノードが現れる可能性があります。結合フィルタ条件は外部結合のON句を元にしますので、結合フィルタ条件に合わない行がNULLで展開された行として発行され続けます。通常のフィルタ条件が外部結合規則の後に適用され、条件に合わない行は削除されます。内部結合では、これらのフィルタ種類の間に意味的な違いはありません。問い合わせの選択性を少し変更すると、非常に異なる結合計画が得られるかもしれません。100)ここでプランナはハッシュ結合の使用を選択しました。片方のテーブルの行がメモリ内のハッシュテーブルに格納され、もう片方のテーブルがスキャンされた後、各行に対して一致するかどうかハッシュテーブルを探索します。繰り返しますが、インデント付けにより計画の構造が表されます。tenk1に対するビットマップスキャンはハッシュノードへの入力です。外部の子計画から行を読み取り、各行に対してハッシュテーブルを検索します。他にも、以下に示すようなマージ結合という結合があり得ます。width=244)マージ結合は、結合キーでソートされる入力データを必要とします。この計画では、正確な順序で行をアクセスするためにtenk1データがインデックススキャンを用いてソートされます。このテーブルの中でより多くの行がアクセスされるため、onekではシーケンシャルスキャンとソートが好まれています。(多くの行をソートする場合、インデックススキャンでは非シーケンシャルなディスクアクセスが必要となるため、シーケンシャルスキャンとソートの方がインデックススキャンより優れています。プランナメソッド設定に記載したenable/disableフラグを使用して、プランナが最も良いと考えている戦略を強制的に無視させる方法により、異なった計画を観察することができます。(非常に原始的なツールですが、利用価値があります。明示的なJOIN句でプランナを制御するも参照してください。前の例にてonekテーブルを扱う最善の方法がシーケンシャルスキャンとソートであると納得できなければ、以下を試みることができます。width=244)これは、プランナが、シーケンシャルスキャンとソートよりインデックススキャンによるonekのソート処理がおよそ12%程高価であるとみなしたことを示します。当然ながら、次の疑問はこれが正しいかどうかでしょう。ANALYZEを使用することで調査することができます。ANALYZEEXPLAINのANALYZEオプションを使用して、プランナが推定するコストの精度を点検することができます。このオプションを付けるとEXPLAINは実際にその問い合わせを実行し、計画ノードごとに実際の行数と要した実際の実行時間を、普通のEXPLAINが示すものと同じ推定値と一緒に表示します。以下のような結果を得ることができます。time」値は実時間をミリ秒単位で表されていること、cost推定値は何らかの単位で表されていることに注意してください。そのまま比較することはできません。注目すべきもっとも重要な点は通常、推定行数が実際の値と合理的に近いかどうかです。この例では、推定はすべて正確ですが、現実的にはあまりありません。問い合わせ計画の中には、何回も副計画ノードを実行する可能性のあるものがあります。上述の入れ子状ループの計画では、内部インデックススキャンは外部の行ごとに一度行われます。このような場合、loops値はそのノードを実行する総回数を報告し、表示される実際の時間と行数は1実行当たりの平均です。これで値を表示された推定コストと比較できるようになります。loops値をかけることで、そのノードで実際に費やされた総時間を得ることができます。上の例では、tenk2に対するインデックススキャンの実行のために合計0.220ミリ秒要しています。ANALYZEは計画ノードの実行時間と行数以上の実行統計情報をさらに表示します。ソートとハッシュノードでは以下のような追加情報を提供します。msソートノードは使用されるソート方式(具体的にはソートがメモリ内かディスク上か)および必要なメモリまたはディスクの容量を表示します。ハッシュノードでは、ハッシュバケット数とバッチ数、ハッシュテーブルで使用されるメモリのピーク容量が表示されます。(バッチ数が1を超える場合、同時にディスクの使用容量も含まれますが、表示はされません。)他の種類の追加情報はフィルタ条件によって除外される行数があります。ms特に結合ノードで適用されるフィルタ条件ではこれらの数が有用です。Removed」行は、少なくともスキャンされた1行、結合ノードにおける結合組み合わせの可能性がフィルタ条件によって拒絶された時にのみ現れます。「非可逆」インデックススキャンはフィルタ条件に似た状況です。特定の点を含有する多角形の検索を考えてみます。msプランナは(ほぼ正確に)、インデックススキャンを考慮するには例のテーブルが小さ過ぎるとみなします。このため、フィルタ条件によってすべての行が拒絶される、普通のシーケンシャルスキャンとなります。インデックススキャンの使用を強制するのであれば、以下のようにします。msここで、インデックスが1つの候補行を返し、それがインデックス条件の再検査により拒絶されることが分かります。多角形の含有試験ではGiSTインデックスが「非可逆」であるため、これは発生します。実際には対象と重なる多角形を持つ行を返し、そしてこれらの行が正確に含有関係であることを試験しなければなりません。EXPLAINには、より多くの実行時統計情報を取り出すために、ANALYZEに付与できるBUFFERSオプションがあります。msBUFFERSにより提供される数は、問い合わせのどの部分がもっとも大きいI/Oであるかを識別する役に立ちます。ANALYZEが実際に問い合わせを実行しますので、EXPLAINのデータを出力することを優先して問い合わせの出力が破棄されたとしても、何らかの副作用が通常通り発生することに注意してください。テーブルを変更すること無くデータ変更問い合わせの解析を行いたければ、以下の例のように、実行後コマンドをロールバックしてください。msROLLBACK;この例で分かるように、問い合わせがINSERT、UPDATE、DELETEである場合、テーブル変更を行うための実作業は最上位のInsert、Update、Delete計画ノードで行われます。このノード以下にある計画ノードは、古い行の検索、新しいデータの計算、あるいはその両方を行います。このため、前に述べたものと同じ種類のビットマップテーブルスキャンがあり、その出力が更新される行を格納するUpdateノードに渡されることが分かります。データ変更ノードが実行時間の多くを費やす可能性があります(現在これが一番多くの時間を費やしています)が、プランナは現在その作業を考慮してコスト推定に何も加えません。これは、行われる作業がすべての正確な問い合わせ計画の作業と同一であるためであり、このため計画の決定に影響を与えません。UPDATEもしくはDELETEコマンドが継承階層に影響する場合には、出力は以下のようになるでしょう。101)この例では、Updateノードは元々言及されている親テーブルに加えて3つの子テーブルを考慮することが必要です。そのため、テーブル毎に1つ、4つの入力スキャン副計画があります。明確にするため、Updateノードには対応する副計画と同じ順に更新される特定の対象テーブルを示す注釈が付けられています。9.5からの新しいものです。以前のバージョンでは副計画を調べることで対象テーブルを勘で当てなければなりませんでした。timeは、解析された問い合わせから問い合わせ計画を生成し最適化するのに掛かった時間です。解析と書き換えは含みません。される何らかのトリガの実行時間も含まれますが、解析や書き換え、計画作成の時間は含まれません。BEFOREトリガがあればその実行時間は関連するInsert、Update、Deleteノード用の時間に含まれます。AFTERトリガは計画全体が完了した後に発行されますので、AFTERトリガの実行時間は計上されません。各トリガ(BEFORE、AFTERのいずれか)で費やされる総時間は別々に表示されます。ANALYZEでは考慮されないことに注意してください。ANALYZEにより測定される実行時間が同じ問い合わせを普通に実行する場合と大きくそれる可能性がある、2つの重大な点があります。1つ目は、出力行がクライアントに配信されませんので、ネットワーク転送コストとI/O変換に関するコストが含まれないことです。ANALYZEによって加わる測定オーバーヘッドが大きくなることが、特にgettimeofday()オペレーティングシステムコールが低速なマシンであり得ることです。pg_test_timingを用いて、使用中のシステムの時間測定にかかるオーバーヘッドを測ることができます。EXPLAINの結果を試験を行ったものと大きく異なる状況の推定に使ってはいけません。小さなテーブルの結果は、巨大なテーブルに適用できるとは仮定できません。プランナの推定コストは線形ではなく、そのため、テーブルの大小によって異なる計画を選択する可能性があります。極端な例ですが、テーブルが1ディスクページしか占めない場合、インデックスが使用できる、できないに関係なく、ほとんど常にシーケンシャルスキャン計画を得ることになります。プランナは、どのような場合でもテーブルを処理するために1ディスクページ読み取りを行うので、インデックスを参照するための追加的ページ読み取りを行う価値がないことを知っています。(上述のpolygon_tblの例でこれが起こることを示しています。)実際の値と推定値がうまく合わないが本当は間違ったものがない場合があります。した状況の1つは、LIMITや同様な効果により計画ノードの実行が短時間で終わる時に起こります。msインデックススキャンノードの推定コストと行数が実行完了したかのように表示されます。現実では、Limitノードが2行を取り出した後に行の要求を停止します。このため実際の行数は2行のみであり、実行時間は提示された推定コストより小さくなります。これは推定間違いではなく、単なる推定値と本当の値を表示する方法における矛盾です。マージ結合には、注意しないと混乱を招く測定上の乱れがあります。マージ結合は他の入力が使い尽くされ、ある入力の次のキー値が他の入力の最後のキー値より大きい場合、その入力の読み取りを停止します。このような場合、これ以上一致することはあり得ず、最初の入力の残りをスキャンする必要がありません。この結果、子のすべては読み取られず、LIMITの説明のようになります。外部(最初)の子が重複するキー値を持つ行を含む場合、内部(2番目)の子はバックアップされ、そのキー値が一致する行部分を再度スキャンされます。ANALYZEはこうした繰り返される同じ内部行の排出を実際の追加される行と同様に計上します。外部で多くの重複がある場合、内部の子計画ノードで繰り返される実際の行数は、内部リレーションにおける実際の行数より非常に多くなることがあり得ます。実装上の制限のため、BitmapAndおよびBitmapOrノードは常に実際の行数をゼロと報告します。プランナで使用される。