クエリの最適化。 PostgreSQLのEXPLAINの基本（パート3）

私は、ギヨーム・レラージによる「理解の説明」の著者による改訂版の発行を続けています。
繰り返しになりますが、簡潔にするために情報の一部が省略されていることに注意してください。そのため、元の情報に慣れることを強くお勧めします。

前のパーツ：

パート1
パート2

ORDER BY

DROP INDEX foo_c1_idx; EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1; 

クエリプラン
-並べ替え（コスト= 117993.01..120493.04行= 1000010幅= 37）（実際の時間= 571.591..651.524行= 1000010ループ= 1）
ソートキー：c1
ソート方法：外部マージディスク：45952kB
-> fooのSeqスキャン（コスト= 0.00..18334.10行= 1000010幅= 37）（実際の時間= 0.007..62.041行= 1000010ループ= 1）
合計ランタイム：690.984ミリ秒
（5行）

最初に、 Seq Scanテーブルfoo生成されます。 次にSortます。 EXPLAINコマンドの出力では、記号->はアクションの階層（ node ）を示します 。 アクションが早く実行されると、よりインデントされて表示されます。
Sort Key -ソート条件。
Sort Method: external merge Disk -ソート時に45952kB 、 45952kBのディスクボリューム上の一時ファイル45952kBます。

このトピックを理解している人に、 external mergeとexternal sort違いを説明してもらいexternal sort 。

BUFFERSオプションで確認します。

 EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo ORDER BY c1; 

クエリプラン
-並べ替え（コスト= 117993.01..120493.04行= 1000010幅= 37）（実際の時間= 568.412..652.308行= 1000010ループ= 1）
ソートキー：c1
ソート方法：外部マージディスク：45952kB
バッファー：共有ヒット= 8334、一時読み取り= 5745書き込み= 5745
-> fooのSeqスキャン（コスト= 0.00..18334.10行= 1000010幅= 37）（実際の時間= 0.010..68.203行= 1000010ループ= 1）
バッファー：共有ヒット= 8334
合計ランタイム：698.032ミリ秒
（7行）

実際、 temp read=5745 written=5745 5745-5745ブロックの8Kb = 45960Kbが一時ファイルに書き込まれ、読み取られました。 8334ブロックの操作がキャッシュで実行されました。

ファイルシステムの操作は、メモリ内の操作よりも遅くなります。
work_mem使用するメモリ量を増やしてみましょう。

 SET work_mem TO '200MB'; EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1; 

クエリプラン
-並べ替え（コスト= 117993.01..120493.04行= 1000010幅= 37）（実際の時間= 265.301..296.777行= 1000010ループ= 1）
ソートキー：c1
ソート方法：クイックソートメモリ：102702kB
-> fooのSeqスキャン（コスト= 0.00..18334.10行= 1000010幅= 37）（実際の時間= 0.006..57.836行= 1000010ループ= 1）
合計ランタイム：328.746ミリ秒
（5行）

Sort Method: quicksort Memory: 102702kB全体はRAMで実行されます。

郵便番号：

 CREATE INDEX ON foo(c1); EXPLAIN (ANALYZE) SELECT * FROM foo ORDER BY c1; 

クエリプラン
-fooでfoo_c1_idxを使用したインデックススキャン（コスト= 0.42..34327.57行= 1000010幅= 37）（実際の時間= 0.023..126.076行= 1000010ループ= 1）
合計ランタイム：153.452ミリ秒
（2行）

アクションのうち、 Index Scanのみが残り、クエリの速度に大きな影響を与えました。

限界

以前に作成したインデックスを削除します。

 DROP INDEX foo_c2_idx1; EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo WHERE c2 LIKE 'ab%'; 

クエリプラン
-fooのSeqスキャン（コスト= 0.00..20834.12行= 100幅= 37）（実際の時間= 0.033..94.757行= 3824ループ= 1）
フィルター：（c2 ~~ 'ab％' :: text）
フィルターによって削除された行：996186
バッファー：共有ヒット= 8334
合計ランタイム：94.924ミリ秒
（5行）

Seq Scan and Filterを使用する予定です。

 EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo WHERE c2 LIKE 'ab%' LIMIT 10; 

クエリプラン
-制限（コスト= 0.00..2083.41行= 10幅= 37）（実際の時間= 0.037..0.607行= 10ループ= 1）
バッファー：共有ヒット= 26
-> fooのSeqスキャン（コスト= 0.00..20834.12行= 100幅= 37）（実際の時間= 0.031..0.599行= 10ループ= 1）
フィルター：（c2 ~~ 'ab％' :: text）
フィルターによって削除された行：3053
バッファー：共有ヒット= 26
合計ランタイム：0.628 ms
（7行）

Seq Scanテーブルの行をSeq Scan Filterと条件を比較します。 条件を満たす10個のレコードが満たされるとすぐに、スキャンは終了します。 この場合、結果の10行を取得するには、テーブル全体ではなく3063レコードのみを読み取る必要があり、そのうち3053レコードが拒否されました（ Rows Removed by Filter ）。
同じことがIndex Scan起こります。

参加する

新しいテーブルを作成し、その統計を収集します。

 CREATE TABLE bar (c1 integer, c2 boolean); INSERT INTO bar SELECT i, i%2=1 FROM generate_series(1, 500000) AS i; ANALYZE bar; 

2テーブルクエリ

 EXPLAIN (ANALYZE) SELECT * FROM foo JOIN bar ON foo.c1=bar.c1; 

クエリプラン
-ハッシュ結合（コスト= 13463.00..49297.22行= 500000幅= 42）（実際の時間= 87.441..907.555行= 500010ループ= 1）
ハッシュ条件：（foo.c1 = bar.c1）
-> fooのSeqスキャン（コスト= 0.00..18334.10行= 1000010幅= 37）（実際の時間= 0.008..67.951行= 1000010ループ= 1）
->ハッシュ（コスト= 7213.00..7213.00行= 500000幅= 5）（実際の時間= 87.352..87.352行= 500000ループ= 1）
バケット：65536バッチ：1メモリ使用量：18067kB
-> Seq Scan on Bar（コスト= 0.00..7213.00行= 500000幅= 5）（実際の時間= 0.007..33.233行= 500000ループ= 1）
合計ランタイム：920.967ミリ秒
（7行）

最初に、（ bar ）テーブルがスキャンされます（ Seq Scan ）。 各行について、ハッシュが計算されます。
次に、 Seq Scanテーブルfooスキャンされ、このテーブルの各行に対してハッシュが計算され、 Hash Cond条件によってbarテーブルのハッシュと比較（ Hash Join ）されます。 一致が見つかった場合、結果の文字列が表示されます。それ以外の場合、文字列はスキップされます。
メモリの18067kBを使用して、テーブルのbarハッシュをホストしました。

インデックスを追加する

 CREATE INDEX ON bar(c1); EXPLAIN (ANALYZE) SELECT * FROM foo JOIN bar ON foo.c1=bar.c1; 

クエリプラン
-結合の結合（コスト= 1.69..39879.71行= 500000幅= 42）（実際の時間= 0.037..263.357行= 500010ループ= 1）
マージ条件：（foo.c1 = bar.c1）
-> fooでfoo_c1_idxを使用したインデックススキャン（コスト= 0.42..34327.57行= 1000010幅= 37）（実際の時間= 0.019..58.920行= 500011ループ= 1）
->バーでbar_c1_idxを使用したインデックススキャン（コスト= 0.42..15212.42行= 500000幅= 5）（実際の時間= 0.008..71.719行= 500010ループ= 1）
合計ランタイム：283.549ミリ秒
（5行）

Hash使用されなくなりました。 両方のテーブルのインデックスでMerge JoinとIndex ScanをMerge Joinと、パフォーマンスが大幅に向上します。

左から参加：

 EXPLAIN (ANALYZE) SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1; 

クエリプラン
-ハッシュ左結合（コスト= 13463.00..49297.22行= 1000010幅= 42）（実際の時間= 82.682..926.331行= 1000010ループ= 1）
ハッシュ条件：（foo.c1 = bar.c1）
-> fooのSeqスキャン（コスト= 0.00..18334.10行= 1000010幅= 37）（実際の時間= 0.004..68.763行= 1000010ループ= 1）
->ハッシュ（コスト= 7213.00..7213.00行= 500000幅= 5）（実際の時間= 82.625..82.625行= 500000ループ= 1）
バケット：65536バッチ：1メモリ使用量：18067kB
-> Seq Scan on Bar（コスト= 0.00..7213.00行= 500000幅= 5）（実際の時間= 0.003..31.890行= 500000ループ= 1）
合計ランタイム：950.625ミリ秒
（7行）

Seq Scan ？
Seq Scanを禁止した場合の結果を見てみましょう。

 SET enable_seqscan TO off; EXPLAIN (ANALYZE) SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1; 

クエリプラン
-左結合のマージ（コスト= 0.85..58290.02行= 1000010幅= 42）（実際の時間= 0.024..353.819行= 1000010ループ= 1）
マージ条件：（foo.c1 = bar.c1）
-> fooでfoo_c1_idxを使用したインデックススキャン（コスト= 0.42..34327.57行= 1000010幅= 37）（実際の時間= 0.011..112.095行= 1000010ループ= 1）
->バーのbar_c1_idxを使用したインデックススキャン（コスト= 0.42..15212.42行= 500000幅= 5）（実際の時間= 0.008..63.125行= 500010ループ= 1）
合計ランタイム：378.603ミリ秒
（5行）

プランナーによると、インデックスを使用する方がハッシュを使用するよりも高価です。 これは、十分な量の割り当てられたメモリで可能です。 work_memを増やしたことを覚えていwork_memか？
ただし、メモリが不足している場合、スケジューラの動作は異なります。

 SET work_mem TO '15MB'; SET enable_seqscan TO ON; EXPLAIN (ANALYZE) SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1; 

クエリプラン
-左結合のマージ（コスト= 0.85..58290.02行= 1000010幅= 42）（実際の時間= 0.014..376.395行= 1000010ループ= 1）
マージ条件：（foo.c1 = bar.c1）
-> fooでfoo_c1_idx1を使用したインデックススキャン（コスト= 0.42..34327.57行= 1000010幅= 37）（実際の時間= 0.005..124.698行= 1000010ループ= 1）
->バーのbar_c1_idxを使用したインデックススキャン（コスト= 0.42..15212.42行= 500000幅= 5）（実際の時間= 0.006..66.813行= 500010ループ= 1）
合計ランタイム：401.990ミリ秒
（5行）

禁止されたIndex Scanは、EXPLAIN出力はどのようになりますか？

 SET work_mem TO '15MB'; SET enable_indexscan TO off; EXPLAIN (ANALYZE) SELECT * FROM foo LEFT JOIN bar ON foo.c1=bar.c1; 

クエリプラン
-ハッシュ左結合（コスト= 15417.00..63831.18行= 1000010幅= 42）（実際の時間= 93.440..712.056行= 1000010ループ= 1）
ハッシュ条件：（foo.c1 = bar.c1）
-> fooのSeqスキャン（コスト= 0.00..18334.10行= 1000010幅= 37）（実際の時間= 0.008..65.901行= 1000010ループ= 1）
->ハッシュ（コスト= 7213.00..7213.00行= 500000幅= 5）（実際の時間= 93.308..93.308行= 500000ループ= 1）
バケット：65536バッチ：2メモリ使用量：9045kB
-> Seq Scan on Bar（コスト= 0.00..7213.00行= 500000幅= 5）（実際の時間= 0.007..33.718行= 500000ループ= 1）
合計ランタイム：736.726ミリ秒
（7行）

cost明らかに増加しました。 Batches: 2理由Batches: 2 。 ハッシュ全体がメモリに収まらず、それぞれ9045kBの2つのパケットに分割する必要がありました。

ここでも、教祖の助けを求めます。 LEFT JOINと十分なwork_mem 、 Merge Left Join使用がHash Left Joinよりも高価な理由を説明してください。

今日はここで止まります。

UPD。
PostgreSQLインデックスに関する多くの有用なことがOleg BartunovとAlexander Korotkov によって語られました 。

ここでは、PostgreSQLのPostgreSQL インデックス 、 パート2 、 パート3の最新記事へのリンクを紹介します 。 多くのことが明らかにされています。