PostgreSQL 9.2.4文書 | ||||
---|---|---|---|---|
前のページ | 上に戻る | 第 14章性能に関するヒント | 次のページ |
PostgreSQLは受理した問い合わせから問い合わせ計画を作り出します。 問い合わせの構造と含まれるデータの性質に適した正しい問い合わせ計画を選択することが、良い性能を得るために非常に重要になります。 ですので、システムには優れた計画の選択を試みる複雑なプランナが存在します。 EXPLAINコマンドを使えば、任意の問い合わせに対してプランナがどのような問い合わせ計画を作ったのかわかります。 問い合わせ計画を読みこなすためには相当な経験が必要な腕が必要です。 本節ではその基本を提供しようと考えます。
本節の例は、9.2の開発版ソースを用いてVACUUM ANALYZEを実行した後でリグレッション試験データベースから取り出したものです。 実際にこの例を試すと、似たような結果になるはずですが、おそらく推定コストや行数は多少異なることになるでしょう。 ANALYZEによる統計情報は厳密なものではなくランダムなサンプリングを行った結果であり、また、コストは本質的にプラットフォームに何かしら依存するためです。
例では、簡潔で人が読みやすいEXPLAINのデフォルトの"text"出力書式を使用します。 今後の解析でEXPLAINの出力をプログラムに渡すことを考えているのであれば、代わりに機械読み取りが容易な出力書式(XML、JSON、YAML)のいずれかを使用すべきです。
問い合わせ計画は計画ノードのツリー構造です。 ツリー構造の最下層ノードはスキャンノードで、テーブルから行そのものを返します。 シーケンシャルスキャン、インデックススキャン、ビットマップインデックススキャンといったテーブルアクセス方法の違いに応じ、スキャンノードの種類に違いがあります。 また、VALUES句やFROM内の集合を返す関数など独自のスキャンノード種類を持つ、テーブル行を元にしないものがあります。 問い合わせが結合、集約、ソートなど、行そのものに対する操作を必要としている場合、スキャンノードの上位に更に、これらの操作を行うためのノードが追加されます。 これらの操作の実現方法にも通常複数の方法がありますので、異なった種類のノードがここに出現することもあり得ます。 EXPLAINには計画ツリー内の各ノードにつき1行の出力があり、基本ノード種類とプランナが生成したその計画ノードの実行に要するコスト推定値を示します。 さらに、ノードの追加属性を表示するためにノードの要約行からインデント付けされた行が出力される可能性があります。 最初の1行目(もっとも上位ノード)には、計画全体の実行コスト推定値が含まれます。 プランナはこの値が最小になるように動作します。
どのような出力となるのかを示すためだけに、ここで簡単な例を示します。
EXPLAIN SELECT * FROM tenk1; QUERY PLAN ------------------------------------------------------------- Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
この問い合わせにはWHERE句がありませんので、テーブル行をすべてスキャンしなければなりません。 このためプランナは単純なシーケンシャルスキャン計画を使用することを選びました。 (左から右に)括弧で囲まれた数値には以下のものがあります。
初期処理の推定コスト。 出力段階が開始できるようになる前に消費される時間、例えば、SORTノードで実行されるソート処理の時間です。
全体推定コスト。 これは計画ノードが実行完了である、つまりすべての利用可能な行を受け取ることを前提として示されます。 実際には、ノードの親ノードはすべての利用可能な行を読む前に停止する可能性があります(以下のLIMITの例を参照)。
この計画ノードが出力する行の推定数。ここでも、ノードが実行を完了することを前提としています。
この計画ノードが出力する行の(バイト単位での)推定平均幅。
コストはプランナのコストパラメータ(項18.7.2参照)によって決まる任意の単位で測定されます。 取り出すディスクページ単位でコストを測定することが、伝統的な方式です。 つまり、seq_page_costを慣習的に1.0に設定し、他のコストパラメータを相対的に設定します。 本節の例では、デフォルトのコストパラメータで実行しています。
上位ノードのコストには、すべての子ノードのコストもその中に含まれていることを理解することは重要です。 このコストはプランナが関与するコストのみ反映する点もまた重要です。 とりわけ、結果の行をクライアントに転送するコストは、実際の処理時間の重要な要因となる可能性があるにもかかわらず、考慮されません。 プランナは、計画をいかに変更しようと、どうすることもできないため、これを無視します。 (正しい計画はどんなものであれ、すべて同じ行を結果として出力すると信じています。)
rowsの値は、計画ノードによって処理あるいはスキャンされた行数を表していず、ノードによって発行された行数を表すので、多少扱いにくくなっています。 該当ノードに適用されるすべてのWHERE句条件によるフィルタ処理の結果、スキャンされる行より少ない行数になることがよくあります。 理想的には、最上位の行数の推定値は、実際に問い合わせによって返され、更新され、あるいは削除された概算の行数となります。
例に戻ります。
EXPLAIN SELECT * FROM tenk1; QUERY PLAN ------------------------------------------------------------- Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
この数は非常に素直に導かれます。
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
を実行すると、tenk1
には358のディスクページと10000の行があることがわかります。
推定コストは(ディスクページ読み取り x seq_page_cost)+(スキャンした行 x cpu_tuple_cost)と計算されます。
デフォルトでは、seq_page_costは1.0、cpu_tuple_costは0.01です。
ですから、推定コストは(358 * 1.0) + (10000 * 0.01) = 458となります。
では、WHERE条件を加えて、問い合わせを変更してみます。
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000; QUERY PLAN ------------------------------------------------------------ Seq Scan on tenk1 (cost=0.00..483.00 rows=7001 width=244) Filter: (unique1 < 7000)
EXPLAINの出力が適用されるWHERE句が、Scan計画ノードに付随する"フィルタ"条件として表示していることに注意してください。 これは、この計画ノードがスキャンした各行に対してその条件を検査することを意味し、その条件を通過したもののみが出力されます。 WHERE句があるため、推定出力行数が小さくなっています。 しかし、依然として10000行すべてをスキャンする必要があるため、コストは小さくなっていません。 実際には、WHERE条件を検査するためにCPU時間が余計にかかることを反映して、ほんの少し(正確には10000 x cpu_operator_cost)ですがコストが上昇しています。
この問い合わせが選択する実際の行数は7000です。 しかし、rowsの推定行数は概算値に過ぎません。 この実験を2回実行した場合、おそらく多少異なる推定値を得るでしょう。 もっと言うと、これはANALYZEコマンドを行う度に変化することがあり得ます。 なぜなら、ANALYZEで生成される統計情報は、テーブルのランダムな標本から取り出されるからです。
では、条件をより強く制限してみます。
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100; QUERY PLAN ------------------------------------------------------------------------------ Bitmap Heap Scan on tenk1 (cost=5.03..229.17 rows=101 width=244) Recheck Cond: (unique1 < 100) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.01 rows=101 width=0) Index Cond: (unique1 < 100)
ここでは、プランナは2段階の計画を使用することを決定しました。 子の計画ノードは、インデックスを使用して、インデックス条件に合う行の場所を検索します。 そして、上位計画ノードが実際にテーブル自体からこれらの行を取り出します。 行を別々に取り出すことは、シーケンシャルな読み取りに比べ非常に高価です。 しかし、テーブルのすべてのページを読み取る必要はありませんので、シーケンシャルスキャンより低価になります。 (2段階の計画を使用する理由は、別々に行を取り出すコストを最小にするために、上位の計画ノードがインデックスにより識別された行の位置を読み取る前に物理的な順序でソートすることです。 ノードで記載されている"bitmap"は、ソートを行う機構の名前です。)
ここでWHERE句に別の条件を付与してみましょう。
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx'; QUERY PLAN ------------------------------------------------------------------------------ Bitmap Heap Scan on tenk1 (cost=5.01..229.40 rows=1 width=244) Recheck Cond: (unique1 < 100) Filter: (stringu1 = 'xxx'::name) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.01 rows=101 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.00..8.27 rows=1 width=244) Index Cond: (unique1 = 42)
この種の計画では、テーブル行はインデックス順で取り出されます。 このため読み取りがより高価になりますが、これが行の位置のソートにかかる追加コストが高価になることはほとんどありません。 単一の行のみを取り出す問い合わせでは、この計画種類がよく現れます。 また、ORDER BYを満たすために必要となる余分な必要なソート処理がないため、インデックスの順序に一致するORDER BY条件を持つ問い合わせでよく使用されます。
WHERE句で参照される複数の列に対するインデックスが存在する場合、プランナはインデックスをANDやORで組み合わせて使用することを選択する可能性があります。
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000; QUERY PLAN ------------------------------------------------------------------------------------- Bitmap Heap Scan on tenk1 (cost=25.01..60.14 rows=10 width=244) Recheck Cond: ((unique1 < 100) AND (unique2 > 9000)) -> BitmapAnd (cost=25.01..25.01 rows=10 width=0) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.01 rows=101 width=0) Index Cond: (unique1 < 100) -> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.74 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.00..14.25 rows=2 width=244) -> Index Scan using tenk1_unique2 on tenk1 (cost=0.00..71.23 rows=10 width=244) Index Cond: (unique2 > 9000) Filter: (unique1 < 100)
これは上と同じ問い合わせですが、すべての行を取り出す必要がないためLIMITを付けています。 プランナはどうすべきかについて考えを変えました。 インデックススキャンノードの総コストと総行数があたかも実行完了したかのように表示されていることに注意してください。 しかしLimitノードが、これらの内5行のみを取り出した後で停止することが想定されています。 そのため総コストは1/5程度のみとなり、これが問い合わせの実際の推定コストとなります。 この計画は、以前の計画に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.33..118.25 rows=10 width=488) -> Bitmap Heap Scan on tenk1 t1 (cost=4.33..39.44 rows=10 width=244) Recheck Cond: (unique1 < 10) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.33 rows=10 width=0) Index Cond: (unique1 < 10) -> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..7.87 rows=1 width=244) Index Cond: (unique2 = t1.unique2)
この計画では、入力または子として2つのテーブルスキャンを持つ入れ子状ループ結合ノードがあります。 計画のツリー構造を反映して、ノード要約行はインデント付けされます。 結合の先頭、"外部"、子は以前に説明したものと似たビットマップスキャンです。 そのコストと行数は、該当ノードにunique1 < 10 WHERE句が適用されるため、SELECT ... WHERE unique1 < 10で得られたものと同じです。 この段階ではt1.unique2 = t2.unique2句は関係しておらず、外部スキャンにおける出力行数に影響していません。 入れ子状ループ結合ノードは、外部の子から得られた行毎に、その2番目または"内部の"子を一回実行します。 現在の外部の行からの列の値は内部スキャンに組み込まれます。 ここで、外部行からのt1.unique2の値が利用できますので、上述の単純なSELECT ... WHERE t2.unique2 = constantの場合に示したものと似た計画とコストが得られます。 (実際、推定コストは、t2に対するインデックススキャンが繰り返される間に発生することが想定されるキャッシュの結果、上で示した値よりわずかに低くなります。) ループノードのコストは、外部スキャンのコストと、各々の外部の行に対して内部スキャンが繰り返されることによるコスト(ここでは10 * 7.87)を加え、さらに結合処理を行うための少々のCPU時間を加えたものになります。
この例では、結合の出力行数は2つのスキャンの出力行数の積に等しくなっていますが、いつもそうなるわけではありません。 2つのテーブルに関係するWHERE句は、入力スキャン時ではなく、結合を行う際に適用されるからです。 例として、もう1つ条件を加えてみます。
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2 AND t1.hundred < t2.hundred; QUERY PLAN -------------------------------------------------------------------------------------- Nested Loop (cost=4.33..118.28 rows=3 width=488) Join Filter: (t1.hundred < t2.hundred) -> Bitmap Heap Scan on tenk1 t1 (cost=4.33..39.44 rows=10 width=244) Recheck Cond: (unique1 < 10) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.33 rows=10 width=0) Index Cond: (unique1 < 10) -> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..7.87 rows=1 width=244) Index Cond: (unique2 = t1.unique2)
t1.hundred < t2.hundred追加条件はtenk2_unique2インデックスの中では試験されません。 このため結合ノードで適用されます。 これは結合ノードの推定出力行数を減らしはしますが、入力スキャンには影響しません。
外部結合を扱う時、"結合フィルタ"および通常の"フィルタ"の両方が付随する結合計画ノードが現れる可能性があります。 結合フィルタ条件は外部結合のON句を元にしますので、結合フィルタ条件に合わない行がNULLで展開された行として発行され続けます。 しかし通常のフィルタ条件が外部結合規則の後に適用され、条件に合わない行は削除されます。 内部結合では、これらのフィルタ種類の間に意味的な違いはありません。
問い合わせの選択性を少し変更すると、非常に異なる結合計画が得られるかもしれません。
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2; QUERY PLAN ------------------------------------------------------------------------------------------ Hash Join (cost=230.43..713.94 rows=101 width=488) Hash Cond: (t2.unique2 = t1.unique2) -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) -> Hash (cost=229.17..229.17 rows=101 width=244) -> Bitmap Heap Scan on tenk1 t1 (cost=5.03..229.17 rows=101 width=244) Recheck Cond: (unique1 < 100) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.01 rows=101 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=197.83..267.93 rows=10 width=488) Merge Cond: (t1.unique2 = t2.unique2) -> Index Scan using tenk1_unique2 on tenk1 t1 (cost=0.00..656.25 rows=101 width=244) Filter: (unique1 < 100) -> Sort (cost=197.83..200.33 rows=1000 width=244) Sort Key: t2.unique2 -> Seq Scan on onek t2 (cost=0.00..148.00 rows=1000 width=244)
マージ結合は、結合キーでソートされる入力データを必要とします。 この計画では、正確な順序で行をアクセスするためにtenk1データがインデックススキャンを用いてソートされます。 しかし、このテーブルの中でより多くの行がアクセスされるため、onekではシーケンシャルスキャンとソートが好まれています。 (多くの行をソートする場合、インデックススキャンでは非シーケンシャルなディスクアクセスが必要となるため、シーケンシャルスキャンとソートの方がインデックススキャンより優れています。)
項18.7.1に記載したenable/disableフラグを使用して、プランナが最も良いと考えている戦略を強制的に無視させる方法により、異なった計画を観察することができます。 (非常に原始的なツールですが、利用価値があります。 項14.3も参照してください。) 例えば、前の例にてonekテーブルを扱う最善の方法がシーケンシャルスキャンとソートであると納得できなければ、以下を試みることができます。
SET enable_sort = off; EXPLAIN SELECT * FROM tenk1 t1, onek t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2; QUERY PLAN ------------------------------------------------------------------------------------------ Merge Join (cost=0.00..292.36 rows=10 width=488) Merge Cond: (t1.unique2 = t2.unique2) -> Index Scan using tenk1_unique2 on tenk1 t1 (cost=0.00..656.25 rows=101 width=244) Filter: (unique1 < 100) -> Index Scan using onek_unique2 on onek t2 (cost=0.00..224.76 rows=1000 width=244)
これは、プランナが、シーケンシャルスキャンとソートよりインデックススキャンによるonekのソート処理がおよそ12%程高価であるとみなしたことを示します。 当然ながら、次の疑問はこれが正しいかどうかでしょう。 後で説明する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.33..118.25 rows=10 width=488) (actual time=0.370..1.126 rows=10 loops=1) -> Bitmap Heap Scan on tenk1 t1 (cost=4.33..39.44 rows=10 width=244) (actual time=0.254..0.380 rows=10 loops=1) Recheck Cond: (unique1 < 10) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.33 rows=10 width=0) (actual time=0.164..0.164 rows=10 loops=1) Index Cond: (unique1 < 10) -> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..7.87 rows=1 width=244) (actual time=0.041..0.048 rows=1 loops=10) Index Cond: (unique2 = t1.unique2) Total runtime: 2.414 ms
"actual time"値は実時間をミリ秒単位で表されていること、cost推定値は何らかの単位で表されていることに注意してください。 ですからそのまま比較することはできません。 注目すべきもっとも重要な点は通常、推定行数が実際の値と合理的に近いかどうかです。 この例では、推定はすべて正確ですが、現実的にはあまりありません。
問い合わせ計画の中には、何回も副計画ノードを実行できるものがあります。 例えば、上述の入れ子状ループの計画では、内部インデックススキャンは外部の行ごとに一度行われます。 このような場合、loops値はそのノードを実行する総回数を報告し、表示される実際の時間と行数は1実行当たりの平均です。 これで値を表示された推定コストと比較できるようになります。 loops値をかけることで、そのノードで実際に費やされた総時間を得ることができます。 上の例では、tenk2に対するインデックススキャンの実行のために合計0.480ミリ秒要しています。
場合によっては、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=717.30..717.56 rows=101 width=488) (actual time=104.950..105.327 rows=100 loops=1) Sort Key: t1.fivethous Sort Method: quicksort Memory: 68kB -> Hash Join (cost=230.43..713.94 rows=101 width=488) (actual time=3.680..102.396 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.046..46.219 rows=10000 loops=1) -> Hash (cost=229.17..229.17 rows=101 width=244) (actual time=3.184..3.184 rows=100 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 27kB -> Bitmap Heap Scan on tenk1 t1 (cost=5.03..229.17 rows=101 width=244) (actual time=0.612..1.959 rows=100 loops=1) Recheck Cond: (unique1 < 100) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.01 rows=101 width=0) (actual time=0.390..0.390 rows=100 loops=1) Index Cond: (unique1 < 100) Total runtime: 107.392 ms
ソートノードは使用されるソート方式(具体的にはソートがメモリ内かディスク上か)および必要なメモリまたはディスクの容量を表示します。 ハッシュノードでは、ハッシュバケット数とバッチ数、ハッシュテーブルで使用されるメモリのピーク容量が表示されます。 (バッチ数が1を超える場合、同時にディスクの使用容量も含まれますが、表示はされません。)
他の種類の追加情報はフィルタ条件によって除外される行数があります。
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Seq Scan on tenk1 (cost=0.00..483.00 rows=7000 width=244) (actual time=0.111..59.249 rows=7000 loops=1) Filter: (ten < 7) Rows Removed by Filter: 3000 Total runtime: 85.340 ms
特に結合ノードで適用されるフィルタ条件ではこれらの数が有用です。 "Rows Removed"行は、少なくともスキャンされた1行、結合ノードにおける結合組み合わせの可能性がフィルタ条件によって拒絶された時にのみ現れます。
"非可逆"インデックススキャンはフィルタ条件に似た状況です。 例えば、特定の点を含有する多角形の検索を考えてみます。
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; QUERY PLAN ------------------------------------------------------------------------------------------------------ Seq Scan on polygon_tbl (cost=0.00..1.05 rows=1 width=32) (actual time=0.251..0.251 rows=0 loops=1) Filter: (f1 @> '((0.5,2))'::polygon) Rows Removed by Filter: 4 Total runtime: 0.517 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.00..8.27 rows=1 width=32) (actual time=0.293..0.293 rows=0 loops=1) Index Cond: (f1 @> '((0.5,2))'::polygon) Rows Removed by Index Recheck: 1 Total runtime: 1.054 ms
ここで、インデックスが1つの候補行を返し、それがインデックス条件の再検査により拒絶されることが分かります。 多角形の含有試験では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.23 rows=10 width=244) (actual time=3.069..3.213 rows=10 loops=1) Recheck Cond: ((unique1 < 100) AND (unique2 > 9000)) Buffers: shared hit=16 -> BitmapAnd (cost=25.07..25.07 rows=10 width=0) (actual time=2.967..2.967 rows=0 loops=1) Buffers: shared hit=7 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.02 rows=102 width=0) (actual time=0.732..0.732 rows=200 loops=1) Index Cond: (unique1 < 100) Buffers: shared hit=2 -> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.80 rows=1007 width=0) (actual time=2.015..2.015 rows=1009 loops=1) Index Cond: (unique2 > 9000) Buffers: shared hit=5 Total runtime: 3.917 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.03..229.42 rows=101 width=250) (actual time=81.055..81.055 rows=0 loops=1) -> Bitmap Heap Scan on tenk1 (cost=5.03..229.42 rows=101 width=250) (actual time=0.766..3.396 rows=100 loops=1) Recheck Cond: (unique1 < 100) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.01 rows=101 width=0) (actual time=0.461..0.461 rows=100 loops=1) Index Cond: (unique1 < 100) Total runtime: 81.922 ms ROLLBACK;
この例で分かるように、問い合わせがINSERT、UPDATE、DELETEである場合、テーブル変更を行うための実作業は最上位のInsert、Update、Delete計画ノードで行われます。 このノード以下にある計画ノードは、古い行の検索、新しいデータの計算、あるいはその両方を行います。 このため、前に述べたものと同じ種類のビットマップテーブルスキャンがあり、その出力が更新される行を格納するUpdateノードに渡されることが分かります。 データ変更ノードが実行時間の多くを費やす可能性があります(現在これが一番多くの時間を費やしています)が、プランナは現在その作業を考慮してコスト推定に何も加えません。 これは、行われる作業がすべての正確な問い合わせ計画の作業と同一であるためでであり、このため計画の決定に影響を与えません。
EXPLAIN ANALYZEで表示されるTotal runtime(総実行時間)にはエクゼキュータの起動、停止時間、発行される何らかのトリガの実行時間も含まれますが、解析や書き換え、計画作成の時間は含まれません。 BEFOREトリガがあればその実行時間は関連するInsert、Update、Deleteノード用の時間に含まれます。 しかし、AFTERトリガは計画全体が完了した後に発行されますので、AFTERトリガの実行時間は計上されません。 また、各トリガ(BEFORE、AFTERのいずれか)で費やされる総時間は別々に表示されます。 しかし、遅延制約トリガはトランザクションが終わるまで実行されませんので、EXPLAIN ANALYZEでは表示されないことに注意してください。
EXPLAIN ANALYZEにより測定される実行時間が同じ問い合わせを普通に実行する場合と大きくそれる可能性がある、2つの重大な点があります。
1つ目は、出力行がクライアントに配信されませんので、ネットワーク転送コストとI/O変換に関するコストが含まれないことです。
2つ目は、EXPLAIN ANALYZEによって加わる測定オーバーヘッドが大きくなることが、特にgettimeofday()
オペレーティングシステムコールが低速なマシンであり得ることです。
pg_test_timingを用いて、使用中のシステムの時間測定にかかるオーバーヘッドを測ることができます。
EXPLAINの結果を試験を行ったものと大きく異なる状況の推定に使ってはいけません。 例えば、小さなテーブルの結果は、巨大なテーブルに適用できるとは仮定できません。 プランナの推定コストは線形ではなく、そのため、テーブルの大小によって異なる計画を選択する可能性があります。 極端な例ですが、テーブルが1ディスクページしか占めない場合、インデックスが使用できる、できないに関係なく、ほとんど常にシーケンシャルスキャン計画を得ることになります。 プランナは、どのような場合でもテーブルを処理するために1ディスクページ読み取りを行うので、インデックスを参照するための追加的ページ読み取りを行う価値がないことを知っています。 (上述のpolygon_tblの例でこれが起こることを示しています。)
実際の値と推定値がうまく合わないが本当は間違ったものがない場合があります。 こうした状況の1つは、LIMITや同様な効果により計画ノードの実行が短時間で終わる時に起こります。 例えば、以前に使用したLIMIT問い合わせでは
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..14.25 rows=2 width=244) (actual time=1.652..2.293 rows=2 loops=1) -> Index Scan using tenk1_unique2 on tenk1 (cost=0.00..71.23 rows=10 width=244) (actual time=1.631..2.259 rows=2 loops=1) Index Cond: (unique2 > 9000) Filter: (unique1 < 100) Rows Removed by Filter: 287 Total runtime: 2.857 ms
インデックススキャンノードの推定コストと行数が実行完了したかのように表示されます。 しかし現実では、Limitノードが2行を取り出した後に行の要求を停止します。 このため実際の行数は2行のみであり、実行時間は提示された推定コストより小さくなります。 これは推定間違いではなく、単なる推定値と本当の値を表示する方法における矛盾です。
またマージ結合には、注意しないと混乱を招く測定上の乱れがあります。 マージ結合は他の入力が使い尽くされ、ある入力の次のキー値が他の入力の最後のキー値より大きい場合、その入力の読み取りを停止します。 このような場合、これ以上一致することはあり得ず、最初の入力の残りをスキャンする必要がありません。 この結果、子のすべては読み取られず、LIMITの説明のようになります。 また、外部(最初)の子が重複するキー値を持つ行を含む場合、内部(2番目)の子はバックアップされ、そのキー値が一致する行部分を再度スキャンされます。 EXPLAIN ANALYZEはこうした繰り返される同じ内部行の排出を実際の追加される行と同様に計上します。 外部で多くの重複がある場合、内部の子計画ノードで繰り返される実際の行数は、内部リレーションにおける実際の行数より非常に多くなることがあり得ます。
実装上の制限のため、BitmapAndおよびBitmapOrノードは常に実際の行数をゼロと報告します。