問い合わせの性能は多くの要因に影響されます。 ユーザが制御できるものもありますが、背後にあるシステム設計に起因する根本的な要因もあります。 本章ではPostgreSQLの性能を理解し、チューニングするためのヒントを提供します。
PostgreSQLは与えられた問い合わせから問い合わせ計画を作り出します。 問い合わせの構造と含まれるデータの性質に適した正しい問い合わせ計画を選択することが良い性能を得るために非常に重要になります。 EXPLAINコマンドを使えば、任意の問い合わせに対してシステムがどのような問い合わせ計画を作ったのかわかります。 問い合わせ計画を読みこなすことについては、別途広範囲にわたるチュートリアルを作らなければならないほどの腕が必要です。 この文書はそこまでカバーしませんが、基本的な情報をいくつか提供します。
現在のところEXPLAINが出力する数値には以下のものがあります。
初期処理の推定コスト(出力用のスキャンが開始されるまでに消費される時間、たとえばSORTノードで実行されるソート処理の時間)。
全体の推定コスト(結果の行全体が抽出される場合のコスト。とはいっても、LIMIT句を使った問い合わせの場合は、全体のコスト計算をする前に停止します)。
この計画ノードが出力する行の推定数(これも、実行が完了した場合のみ。)
この計画ノードが出力する行の(バイト単位での)推定平均幅。
コストは、取り出すディスクページ単位で測定されます。 (CPUの負担に関しての推定は、適当に恣意的に決めた基準にしたがってディスクページ単位で換算されます。 これらの基準値の効果を知りたい場合は、項16.4.2の実行時設定パラメータの一覧を参照して下さい。)
上位ノードのコストには、全ての子ノードのコストもその中に含まれていることに十分留意してください。 このコストはプランナやオプティマイザが関与するコストのみ反映する点もまた重要です。 とりわけ、結果の行をフロントエンドに転送するコストは、実際の処理時間の大半を占める可能性があるにも関わらず、考慮されません。 プランナは、計画をいかに変更しようと、どうすることもできないため、これを無視します。 (正しい計画はどんなものであれ、すべて同じ行を結果として出力すると信じています。)
出力行数は問い合わせによって処理あるいはスキャンされた行数を表していないので、多少扱いにくくなっています。 該当ノードに適用されるすべてのWHERE句制約から来る選択性推定値を反映し、通常の時よりも少ない行数になります。 理想的には、最上位の行数の推定値は、実際に問い合わせによって返され、更新され、あるいは削除された概算の行数となります。
いくつか例を示します (VACUUM ANALYZEをかけた後のregression testデータベースを使っています。使用したソースは7.3開発版です)。
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条件を加えて問い合わせを変更してみます。
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で生成される統計情報は、テーブルのランダムな標本から取り出されるからです。
では、さらに問い合わせを変更し、条件をより強く制限してみます。
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 つ追加します。
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND 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つのテーブルを結合してみましょう。
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 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フラグを使用して、プランナが最も良いと考えている戦略を強制的に無視させる方法により、異なった計画を観察することができます。 （非常に原始的なツールですが、利用価値があります。 項13.3も参照ください。)
SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 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時間が含まれています。 しかし、この計画型ではハッシュテーブルの設定は一度のみのため、10000x179.33分のコストはかからないことに注意して下さい。
EXPLAIN ANALYZEを使用して、プランナが推定するコストの精度を点検することができます。 このコマンドは実際にその問い合わせを実行し、計画ノード毎に要した実際の実行時間を、普通のEXPLAINが示す推定コストと同じ値と一緒に表示します。 例えば、以下のような結果を得ることができます。
EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 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.181..29.822 rows=50 loops=1)
-> Index Scan using tenk1_unique1 on tenk1 t1
(cost=0.00..179.33 rows=49 width=148)
(actual time=0.630..8.917 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.295..0.324 rows=1 loops=50)
Index Cond: ("outer".unique2 = t2.unique2)
Total runtime: 31.604 ms"actual time"値は実時間をミリ秒単位で表されていること、"cost"推定値はディスク読みとりに関する何らかの単位で表されていることに注意して下さい。 ですからそのまま比較することはできません。 注目する点はその割合です。
問い合わせ計画の中には、何回も副計画ノードを実行できるものがあります。 例えば、上述の入れ子状ループの計画では、内部インデックススキャンは外部の行毎に一度行われます。 このような場合、"loops"値はそのノードを実行する総回数を報告し、表示される実際の時間と行数は1実行当たりの平均です。 これは表示された推定コストと比較できる値を作成することで行います。 "loops"値をかけることで、そのノードで実際に費やされた総時間を得ることができます。
EXPLAIN ANALYZEで表示されるTotal runtime(総実行時間)には、結果行を操作するための時間の他に、エグゼキュータの起動、停止時間も含まれます。 解析や書き換え、計画作成の時間は含まれません。 SELECT問い合わせでは、総実行時間は最上位計画ノードで報告される総時間よりも通常少し大きくなります。 INSERT、UPDATE、および DELETEコマンドでは、その総実行時間はかなり大きくなります。 そこには、結果行を処理する時間が含まれるからです。 これらのコマンドでは、最上位計画ノードの時間は、基本的に新しい行を演算し、古い行の場所を決定するための時間を示し、そこには変更を行う時間は含まれません。
EXPLAINの結果を、試験を行ったもの以外の推定に使ってはいけないことは、いうまでもありません。 例えば、小さなテーブルの結果は、巨大なテーブルに適用できるとは仮定できません。 プランナの推定コストは線形ではなく、そのため、テーブルの大小によって異なる計画を選択します。 極端な例ですが、テーブルが1ディスクページしか占めない場合、インデックスが使用できる、できないに関係なく、ほとんど常に逐次スキャン計画を得ることになります。 プランナは、どのような場合でもテーブルを処理するために1ディスクページ読みとりを行なうので、インデックスを参照するための追加的ページ読みとりを行う価値がないことを知っています。