EXPLAINの利用PostgreSQLは与えられた問い合わせから問い合わせ計画を作り出します。問い合わせの構造と含まれるデータの性質に適した正しい問い合わせ計画を選択することが良い性能を得るために非常に重要になります。優れた計画の選択を試みるシステムには複雑なプランナが存在します。EXPLAINコマンドを使えば、任意の問い合わせに対してプランナがどのような問い合わせ計画を作ったのかわかります。問い合わせ計画を読みこなすことについては、別途広範囲にわたるチュートリアルを作らなければならないほどの腕が必要です。本書ではそこまでカバーしませんが、基本的な情報をいくつか提供します。問い合わせ計画は、計画ノードのツリー構造です。最下層ノードはテーブルスキャンノードで、テーブルから行そのものを返します。シーケンシャルスキャン、インデックススキャン、ビットマップインデックススキャンといったテーブルアクセス方法の違いに応じ、スキャンノードの種類に違いがあります。問い合わせが結合、集約、ソート、その他行そのものに対する操作を必要としている場合、スキャンノードの上位に更に、これらの操作を行うためのノードが追加されます。繰り返しますが、こうした操作を行う時、通常複数の可能性が存在します。異なるノード種類がそこに出現する可能性があります。EXPLAINには、計画ツリー内の各ノードに対し1行の出力があり、基本ノード種類とプランナが生成したその計画ノードの実行に要するコスト推定値を示します。1行目(もっとも上位ノード)には、計画全体の実行コスト推定値が含まれます。プランナはこの値が最小になるように動作します。どのような出力となるのかを示すためだけに、ここで簡単な例を示します。width=244)EXPLAINが出力する数値には以下のものがあります。初期処理の推定コスト(出力用のスキャンが開始されるまでに消費される時間、例えばSORTノードで実行されるソート処理の時間)。全体の推定コスト(結果の行全体が抽出される場合のコスト。言っても、LIMIT句を使った問い合わせの場合は、Limit計画ノードへの入力ノードの総コスト計算をする前に停止します)。この計画ノードが出力する行の推定数(これも、実行が完了した場合のみ)。この計画ノードが出力する行の(バイト単位での)推定平均幅。コストはプランナのコストパラメータ(項18.6.2参照)によって決まる任意の単位で測定されます。取り出すディスクページ単位でコストを測定することが、伝統的な方式です。seq_page_costを慣習的に1.0に設定し、他のコストパラメータを相対的に設定します。本節の例では、デフォルトのコストパラメータで実行しています。上位ノードのコストには、全ての子ノードのコストもその中に含まれていることに十分留意してください。このコストはプランナが関与するコストのみ反映する点もまた重要です。結果の行をクライアントに転送するコストは、実際の処理時間の重要な要因となる可能性があるにもかかわらず、考慮されません。(正しい計画はどんなものであれ、全て同じ行を結果として出力すると信じています)。出力行数は計画ノードによって処理あるいはスキャンされた行数を表していないので、多少扱いにくくなっています。該当ノードに適用される全てのWHERE句制約から来る選択性推定値を反映し、通常少ない行数になります。理想的には、最上位の行数の推定値は、実際に問い合わせによって返され、更新され、あるいは削除された概算の行数となります。width=244)これはほとんど見たままです。'tenk1';を実行すると、tenk1には358のディスクページと10000の行があることがわかります。cpu_tuple_cost)です。デフォルトでは、seq_page_costは1.0、cpu_tuple_costは0.01です。WHERE条件を加えて問い合わせを変更してみます。7000)EXPLAINの出力が適用されるWHERE句が"フィルタ"条件として表示していることに注意してください。これは、この計画ノードがスキャンした各行に対してその条件を検査することを意味し、その条件を通過したもののみが出力されます。WHERE句があるため、出力行数の見積りが小さくなっています。して10000行全てをスキャンする必要があるため、コストは小さくなっていません。cpu_operator_cost)ですがコストが上昇しています。この問い合わせが選択する実際の行数は7000です。行推定は概算値に過ぎません。この実験を2回実行した場合、おそらく多少異なる推定値を得るでしょう。言うと、これはANALYZEコマンドを行う度に変化します。ANALYZEで生成される統計情報は、テーブルのランダムな標本から取り出されるからです。条件をより強く制限してみます。100)ここでは、プランナは2段階の計画を使用することを決定しました。最下層の計画ノードは、インデックスを使用して、インデックス条件に合う行の場所を検索します。上位計画ノードが実際にテーブル自体からこれらの行を取り出します。行を別々に取り出すことは、シーケンシャルな読み取りに比べ非常に高価です。(2段階の計画を使用する理由は、別々に行を取り出すコストを最小にするために、上位の計画ノードがインデックスにより識別された行の位置を読み取り前に物理的な順序でソートすることです。ノードで記載されている"bitmap"は、ソートを行う機構の名前です)。WHERE条件の選択性が十分であれば、プランナが"単純な"インデックススキャン計画に切り替える可能性があります。3)この場合、テーブル行はインデックスの順番で取り出されます。これは高価な読み取りなのですが、行数が少ないため、行位置をソートする余計なコストをかける必要はありません。BY条件を要求する問い合わせでは、この種の計画種類をよく見かけます。WHERE条件に句をもう1つ追加します。'xxx'により出力行数の推定値は減少するものの、検査する行の集合は変わらないためにコストは減っていません。このstringu1句は、インデックス条件としては適用できないことに注意してください(このインデックスは、unique1列のみを対象としているからです)。その代わり、stringu1句は、インデックスによって抽出された行のフィルタとして適用されています。これにより、追加分のチェックを反映するため、コストは実際には少し上がります。WHERE句で使用される複数の列に対するインデックスが存在する場合、プランナはインデックスをANDやORで組み合わせて使用することを選択する可能性があります。9000)しかし、これは両方のインデックスを参照する必要があります。そのため、インデックスを1つ使用し、他の条件についてはフィルタとして扱う方法と比べて常に勝るとは限りません。含まれる範囲を変更すると、それに伴い計画も変わることが分かるでしょう。今まで説明に使ってきたフィールドを使って2つのテーブルを結合してみましょう。t1.unique2)この入れ子状ループ結合では、外部スキャンに、最後の例で示したのと同じビットマップインデックススキャンが使われています。WHERE句を該当ノードに適用しているため、コストと出力行数は変わっていません。t2.unique2句は関係しておらず、外部スキャンにおける出力行数に影響していません。constantのようなインデックス条件を生成します。42のような問い合わせと同じです。3.01)を加え、さらに結合処理を行うための少々のCPU時間を加えたものになります。この例では、結合の出力行数は2つのスキャンの出力行数の積に等しくなっていますが、いつもそうなるわけではありません。場合、2つのテーブルに関係するWHERE句があって、そのWHERE句は入力スキャン時ではなく、結合を行う際に適用されるからです。t2.hundredという句を追加したとすると、結合ノードの出力行数を減らしはしますが、入力スキャンには影響しません。(非常に原始的なツールですが、利用価値があります。項14.3も参照してください)。t2.unique2を満たすかどうかハッシュテーブルを検査します。tenk1を読み取り、ハッシュテーブルを設定するためのコストは、tenk2の読み取りを始めるまで行を入手しませんので、完全にハッシュ結合の初期コストとなります。この結合の推定総時間には、ハッシュテーブルを10000回検査する、大きなCPU時間が含まれています。この計画型ではハッシュテーブルの設定は一度のみのため、10000×232.35分のコストはかからないことに注意してください。ANALYZEを使用して、プランナが推定するコストの精度を点検することができます。このコマンドは実際にその問い合わせを実行し、計画ノードごとに要した実際の実行時間を、普通のEXPLAINが示す推定コストと同じ値と一緒に表示します。以下のような結果を得ることができます。time"値は実時間をミリ秒単位で表されていること、"cost"推定値は何らかの単位で表されていることに注意してください。そのまま比較することはできません。注目する点は、実時間と推定コストの割合に矛盾がないかどうかです。問い合わせ計画の中には、何回も副計画ノードを実行できるものがあります。上述の入れ子状ループの計画では、内部インデックススキャンは外部の行ごとに一度行われます。このような場合、"loops"値はそのノードを実行する総回数を報告し、表示される実際の時間と行数は1実行当たりの平均です。これは表示された推定コストと比較できる値を作成することで行います。"loops"値をかけることで、そのノードで実際に費やされた総時間を得ることができます。runtime(総実行時間)には、結果行を操作するための時間の他に、エクゼキュータの起動、停止時間も含まれます。解析や書き換え、計画作成の時間は含まれません。SELECT問い合わせでは、総実行時間は最上位計画ノードで報告される総時間よりも通常少し大きくなります。INSERT、UPDATE、およびDELETEコマンドでは、その総実行時間はかなり大きくなる可能性があります。そこには、結果行を処理する時間が含まれるからです。これらのコマンドでは、最上位計画ノードの時間は、基本的に新しい行を演算し、古い行の場所を決定するための時間を示し、そこには変更を行う時間は含まれません。トリガが発行される場合、トリガの発行にかかる時間も最上位計画ノードには含まれず、トリガ毎に別々に表示されます。EXPLAINの結果を試験を行ったもの以外の推定に使ってはいけないことは言うまでもありません。小さなテーブルの結果は、巨大なテーブルに適用できるとは仮定できません。プランナの推定コストは線形ではなく、そのため、テーブルの大小によって異なる計画を選択する可能性があります。極端な例ですが、テーブルが1ディスクページしか占めない場合、インデックスが使用できる、できないに関係なく、ほとんど常にシーケンシャルスキャン計画を得ることになります。プランナは、どのような場合でもテーブルを処理するために1ディスクページ読み取りを行うので、インデックスを参照するための追加的ページ読み取りを行う価値がないことを知っています。ANALYZEを実行した後で取り出したものです。実際にこの例を試すと、似たような結果になるはずですが、おそらく推定コストや行数は多少異なることになるでしょう。ANALYZEによる統計情報は厳密なものではなくランダムなサンプリングを行った結果であるからです。プランナで使用される。