問い合わせの性能は多くの要因に影響されます。ユーザが制御できるものもありますが、利用しているシステム設計に起因する根本的な要因もあります。この章ではPostgreSQL の性能を理解し、チューニングするためのヒントを提供します。
PostgreSQL は与えられた問い合わせから問い合わせ計画を作り出します。問い合わせの構造と含まれるデータの性質に適した正しい問い合わせ計画を選択することが良い性能を得るために非常に重要になります。EXPLAINコマンドを使えば、システムがどのような問い合わせ計画を作ったのかわかります。問い合わせ計画を読みこなすことについては、別途広範囲にわたるチュートリアルを作らなければならないほどの腕が必要です。 この文書はそこまでカバーしませんが、基本的な情報はいくつか提供します。
現在のところ EXPLAIN が出力する数値には以下のものがあります。
予想される初期処理のコスト (出力用のスキャンが開始されるまでに消費される時間、たとえば SORT ノードで実行されるソート処理の時間)。
予想される全体のコスト (結果の行全体が抽出される場合のコスト。 とはいっても、LIMIT 句を使った問い合わせの場合は、全体のコスト計算をする前に停止します)。
この計画ノードが出力する行の推定数 (これも、実行が完了した場合のみ。)
この計画ノードが出力する行の (バイト単位での) 推定平均幅。
コストは、取り出すディスクページ単位で測定されます。 (CPU の負担に関しての推定は、適当に恣意的に決めた基準にしたがってディスクページ単位で換算されます。 これらの基準値の効果を知りたい場合は、PostgreSQL 7.3 管理者用ガイドの実行時設定パラメータの一覧を参照して下さい。)
上位ノードのコストには、全ての子ノードのコストもその中に含まれていることを留意してください。このコストはプランナやオプティマイザが関与するコストのみ反映する点もまた重要です。 とりわけ、結果の行をフロントエンドに転送するコストは、実際の処理時間の大半を占める可能性があるにも関わらず、プランナは無視します。 これは、計画をいかに変更しようと、どうすることもできないからです。 (正しい計画はどんなものであれ、すべて同じ行を結果として出力すると信頼しています。)
出力行数は問い合わせによって処理あるいはスキャンされた行数を表していないので、多少扱いにくくなっています。該当ノードに適用されるすべての WHERE 句制約から来る選択性推定値を反映し、通常の時よりも少ない行数になります。理想的には、最上位の行数の推定値は、実際に問い合わせによって返され、更新され、あるいは削除された概算の行数となります。
いくつか例を示します (VACUUM ANALYZE をかけた後の regress test データベースを使っています。使用したシステムは 7.3 開発版です)。
regression=# EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..333.00 rows=10000 width=148)
これはほとんど見たままです。もし、
SELECT * FROM pg_class WHERE relname = 'tenk1';
を実行すると、tenk1 には 233 のディスクページと 10000 の行があることがわかります。 ですから、単位あたり 1.0 とコストが決まっている 233 ページの読み出しに、現在 0.01 に設定されている cpu_tuple_cost を 10000 倍したものを加えたものが、コストの推定値になるわけです （SHOW cpu_tuple_cost を試してみて下さい）。
では、WHERE 条件を加えて問い合わせを変更してみます。
regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..358.00 rows=1033 width=148)
Filter: (unique1 < 1000)WHERE 句があるため、出力行数の見積りが小さくなっています。 しかし、依然として 10000 行すべてをスキャンする必要があるため、コストは小さくなっていません。実際には、WHERE 条件を検査するために CPU 時間が余計にかかることを反映して、ほんの少しですがコストが上昇しています。
この問い合わせが選択する実際の行数は 1000です。 しかし、推定は概算値にすぎません。この実験を二回実行した場合、おそらく多少異なる推定値を得るでしょう。 もっというと、これは ANALYZE コマンドを行う度に変化します。 なぜなら、ANALYZE で生成される統計情報は、テーブルのランダムな標本から取り出されるからです。
では、さらに問い合わせを変更し、条件をより強く制限してみます。
regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..179.33 rows=49 width=148)
Index Cond: (unique1 < 50)WHERE 句条件の選択性を十分に高めると、プランナはシーケンススキャンよりもインデックススキャンが安価であると決定するようになることが分かります。 この計画では、インデックスがあるためにたった 50 行だけにアクセスすればよいことになります。 ですから、ディスクページ全体を逐次読みとるよりも、インデックスによる個々の取り出しは高価につくにも関わらず、この計画が採用されます。
WHERE 条件に句をもう 1 つ追加します。
regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND
regression-# stringu1 = 'xxx';
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..179.45 rows=1 width=148)
Index Cond: (unique1 < 50)
Filter: (stringu1 = 'xxx'::name)追加した句 stringu1 = 'xxx' により出力行数の推定値は減少するものの、検査する行の集合は変わらないためにコストは減っていません。 この stringu1 句は、インデックス条件としては適用できないことに注意してください (このインデックスは、unique1 列のみを対象としているからです)。 その代わり、stringu1 句は、インデックスによって抽出された行のフィルターとして適用できます。 これにより、追加分のチェックを反映するため、コストは実際には少し上がります。
今まで説明に使ってきたフィールドを使って2つのテーブルを結合してみましょう。
regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# AND t1.unique2 = t2.unique2;
QUERY PLAN
----------------------------------------------------------------------------
Nested Loop (cost=0.00..327.02 rows=49 width=296)
-> Index Scan using tenk1_unique1 on tenk1 t1
(cost=0.00..179.33 rows=49 width=148)
Index Cond: (unique1 < 50)
-> Index Scan using tenk2_unique2 on tenk2 t2
(cost=0.00..3.01 rows=1 width=148)
Index Cond: ("outer".unique2 = t2.unique2)
この入れ子状ループ結合では、外部スキャンに、最後の例で示したのと同じインデックススキャンが使われています。そして、unique1 < 50 WHERE 句を該当ノードに適用しているため、コストと出力行数は変わっていません。この段階では t1.unique2 = t2.unique2 句は関係しておらず、外部スキャンにおける出力行数に影響していません。 内部スキャンでは、外部スキャンにおける現在の行の unique2 の値が内部のインデックススキャンに投げ込まれ、t2.unique2 = constant のようなインデックス条件を生成します。 したがって、内部スキャンの計画とコストは、たとえば EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42 のような問い合わせと同じです。 ループノードのコストは、外部スキャンのコストと、各々の外部の行に対して内部スキャンが繰り返されることによるコスト （ここでは 49 * 3.01） を加え、更に結合処理を行うための少々の CPU 時間を加えたものになります。
この例では、ループ全体の出力行数は 2 つのスキャンの出力行数の積に等しくなっていますが、いつもそうなるわけではありません。たいていの場合、2 つのリレーションに関係する WHERE 句があって、その WHERE 句は入力スキャン時ではなく、結合を行う際に適用されるからです。 たとえば、WHERE ... AND t1.hundred < t2.hundred という句を追加したとすると、結合ノードの出力行数を減らしはしますが、入力スキャンには影響しません。
各問い合わせ計画の種類に対する enable/disable フラグを使用して、プランナが最も良いと考えている戦略を強制的に無視させる方法により、異なった計画を観察することができます。 （非常に原始的なツールですが、利用価値があります。Section 10.3も参照ください。)
regression=# SET enable_nestloop = off;
SET
regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------
Hash Join (cost=179.45..563.06 rows=49 width=296)
Hash Cond: ("outer".unique2 = "inner".unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..333.00 rows=10000 width=148)
-> Hash (cost=179.33..179.33 rows=49 width=148)
-> Index Scan using tenk1_unique1 on tenk1 t1
(cost=0.00..179.33 rows=49 width=148)
Index Cond: (unique1 < 50)この計画では、インデックススキャンを使って tenk1 から必要な 50 行を取り出してメモリ上のハッシュテーブルに格納し、その後、tenk2 をシーケンシャルスキャンして、tenk2 の行毎に t1.unique2 = t2.unique2 を満たすかどうかハッシュテーブルを検査します。 tenk1 を読みとり、ハッシュテーブルを設定するためのコストは、tenk2 の読みとりを始めるまで行を入手しませんので、完全にハッシュ結合の初期コストとなります。 この結合の推定総時間には、ハッシュテーブルを 10000 回検査する、大きな CPU 時間が含まれています。 しかし、この計画型ではハッシュテーブルの設定は一度のみのため、10000 x 179.33 分のコストはかからないことに注意して下さい。
EXPLAIN ANALYZE を使用して、プランナが推定するコストの精度を点検することができます。 このコマンドは実際にその問い合わせを実行し、計画ノード毎に要した実際の実行時間を、普通の EXPLAIN が示す推定コストと同じ値と一緒に表示します。例えば、以下のような結果を得ることができます。
regression=# EXPLAIN ANALYZE
regression-# SELECT * FROM tenk1 t1, tenk2 t2
regression-# WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop (cost=0.00..327.02 rows=49 width=296)
(actual time=1.18..29.82 rows=50 loops=1)
-> Index Scan using tenk1_unique1 on tenk1 t1
(cost=0.00..179.33 rows=49 width=148)
(actual time=0.63..8.91 rows=50 loops=1)
Index Cond: (unique1 < 50)
-> Index Scan using tenk2_unique2 on tenk2 t2
(cost=0.00..3.01 rows=1 width=148)
(actual time=0.29..0.32 rows=1 loops=50)
Index Cond: ("outer".unique2 = t2.unique2)
Total runtime: 31.60 msec"actual time" 値は実時間をミリ秒単位で表されていること、"cost" 推定値はディスク読みとりに関する何らかの単位で表されていることに注意して下さい。 ですからそのまま比較することはできません。注目する点はその割合です。
問い合わせ計画の中には、一度に複数の副計画ノードを実行できるものがあります。 例えば、上述の入れ子状ループの計画では、内部インデックススキャンは外部の行毎に一度行われます。このような場合、"loops" 値はそのノードを実行する総回数を報告し、表示される実際の時間と行数は 1 実行当たりの平均です。これは表示された推定コストと比較できる値を作成することで行います。"loops"値をかけることで、そのノードで実際に費やされた総時間を得ることができます。
EXPLAIN ANALYZE で表示される Total runtime (総実行時間) には、結果行を操作するための時間の他に、エグゼキュータの起動、停止時間も含まれます。解析や書き換え、計画作成の時間は含まれません。 SELECT 問い合わせでは、総実行時間は最上位計画ノードで報告される総時間よりも通常少し大きくなります。 INSERT、UPDATE、および DELETE コマンドでは、その総実行時間はかなり大きくなります。そこには、結果行を処理する時間が含まれるからです。 これらのコマンドでは、最上位計画ノードの時間は、基本的に新しい行を演算し、古い行の場所を決定するための時間を示し、そこには変更を行う時間は含まれません。
EXPLAIN の結果を、試験を行ったもの以外の推定に使ってはいけないことは、いうまでもありません。 例えば、小さなテーブルの結果は、巨大なテーブルに適用できるとは仮定できません。プランナの推定コストは線形ではなく、そのため、テーブルの大小によって異なる計画を選択します。極端な例ですが、テーブルが 1 ディスクページしか占めない場合、インデックスが使用できる、できないに関係なく、ほとんど常にシーケンシャルスキャン計画を得ることになります。プランナは、どのような場合でもテーブルを処理するために 1 ディスクページ読みとりを行なうので、インデックスを参照するための追加的ページ読みとりを行う価値がないことを知っています。