Geekly Articles each Day

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


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

パート1

キャッシュ


私たちの要求を満たすとき、物理レベルで何が起こりますか？ それを理解しましょう。 私のサーバーはUbuntu 13.10で稼働しています。 OSレベルのディスクキャッシュが使用されます。
PostgreSQLを停止し、ファイルシステムに強制的に変更をコミットし、キャッシュをクリアして、PostgreSQLを起動します。
> sudo service postgresql-9.3 stop > sudo sync > sudo su - # echo 3 > /proc/sys/vm/drop_caches # exit > sudo service postgresql-9.3 start

キャッシュがクリアされました。BUFFERSオプションを使用してクエリを実行してください。
 EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo;

クエリプラン
-fooのSeqスキャン（コスト= 0.00..18334.10行= 1000010幅= 37）（実際の時間= 0.525..734.754行= 1000010ループ= 1）
バッファー：共有読み取り= 8334
合計ランタイム：1253.177ミリ秒
（3行）

テーブルは部分的に読み込まれます-ブロック。 キャッシュは空です。 テーブルはディスクから完全に読み取られます。 これを行うには、8334ブロックをカウントする必要がありました。
Buffers: shared read -ディスクから読み取られたブロックの数。

最後のリクエストを繰り返します
 EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM foo;

クエリプラン
-fooのSeqスキャン（コスト= 0.00..18334.10行= 1000010幅= 37）（実際の時間= 0.173..693.000行= 1000010ループ= 1）
バッファー：共有ヒット= 32読み取り= 8302
合計ランタイム：1208.433ミリ秒
（3行）

Buffers: shared hit -PostgreSQLキャッシュから読み取られたブロックの数。
このクエリを数回繰り返すと、毎回PostgreSQLがキャッシュからより多くのデータを取得する方法がわかります。 すべてのクエリで、PostgreSQLはキャッシュにデータを追加します。
キャッシュからの読み取り操作は、ディスクからの読み取り操作よりも高速です。 この傾向は、 Total runtime値を追跡することで確認できTotal runtime 。
キャッシュサイズは、 postgresql.confファイルのshared_buffers定数によって決まります。

どこ


リクエストに条件を追加します
 EXPLAIN SELECT * FROM foo WHERE c1 > 500;

クエリプラン
-fooのSeqスキャン（コスト= 0.00..20834.12行= 999522幅= 37）
フィルター：（c1> 500）
（2行）

テーブルにインデックスがありません。 クエリを実行すると、各テーブルレコードが順番に読み取られます（ Seq Scan ）。 各レコードは、条件c1 > 500と比較されます。 条件が満たされると、レコードが結果に入力されます。 それ以外の場合は、破棄されます。 Filterはまさにこの動作を意味します。
論理的なcostの値が増加しました。
結果の行の予想数（ rows ）が減少しました。
オリジナルでは、 costがこのような値をとる理由と、予想される行数の計算方法について説明しています。

インデックスを作成します。
 CREATE INDEX ON foo(c1); EXPLAIN SELECT * FROM foo WHERE c1 > 500;

クエリプラン
-fooのSeqスキャン（コスト= 0.00..20834.12行= 999519幅= 37）
フィルター：（c1> 500）
（2行）

予想される行数が変更されました。 明確にした。 残りは新しいものではありません。 インデックスはどうですか？
 EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c1 > 500;

クエリプラン
-fooのSeqスキャン（コスト= 0.00..20834.12行= 999519幅= 37）（実際の時間= 0.572..848.895行= 999500ループ= 1）
フィルター：（c1> 500）
フィルターによって削除された行：510
合計ランタイム：1330.788ミリ秒
（4行）

100万件を超える510行のみがフィルタリングされます。 テーブルの99.9％以上をカウントする必要がありました。

Seq Scanを無効にして、インデックスの使用を強制します。
 SET enable_seqscan TO off; EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c1 > 500;

クエリプラン
-fooでfoo_c1_idxを使用したインデックススキャン（コスト= 0.42..34623.01行= 999519幅= 37）（実際の時間= 0.178..1018.045行= 999500ループ= 1）
インデックス条件：（c1> 500）
合計ランタイム：1434.429ミリ秒
（3行）

Index Scan 、 Filterなくインデックスfoo_c1_idxインデックスfoo_c1_idxが使用されます。
テーブルのほぼ全体をフェッチする場合、インデックスを使用すると、 costとクエリ実行時間が増加するだけです。 プランナーは愚かではありません！

Seq Scanの使用禁止をキャンセルすることを忘れないでください：
 SET enable_seqscan TO on;


リクエストを変更します。
 EXPLAIN SELECT * FROM foo WHERE c1 < 500;

クエリプラン
-fooでfoo_c1_idxを使用したインデックススキャン（コスト= 0.42..25.78行= 491幅= 37）
インデックス条件：（c1 <500）
（2行）

次に、プランナーはインデックスを使用することにしました。

状態を複雑にします。 テキストボックスを使用します。
 EXPLAIN SELECT * FROM foo WHERE c1 < 500 AND c2 LIKE 'abcd%';

クエリプラン
-fooでfoo_c1_idxを使用したインデックススキャン（コスト= 0.42..27.00行= 1幅= 37）
インデックス条件：（c1 <500）
フィルター：（c2 ~~ 'abcd％' :: text）
（3行）

ご覧のとおり、インデックスfoo_c1_idx条件c1 < 500 foo_c1_idxれます。 c2 ~~ 'abcd%'::text場合、フィルターが使用されます。
結果の出力では 、LIKE演算子のPOSIX形式が使用されることに注意してください。

条件がテキストフィールドのみの場合：
 EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c2 LIKE 'abcd%';

クエリプラン
-fooのSeqスキャン（コスト= 0.00..20834.12行= 100幅= 37）（実際の時間= 14.497..412.030行= 10ループ= 1）
フィルター：（c2 ~~ 'abcd％' :: text）
フィルターによって削除された行：1,000,000
合計ランタイム：412.120ミリ秒
（4行）

予想、 Seq Scan 。

c2インデックスを作成します。
 CREATE INDEX ON foo(c2); EXPLAIN (ANALYZE) SELECT * FROM foo WHERE c2 LIKE 'abcd%';

クエリプラン
-fooのSeqスキャン（コスト= 0.00..20834.12行= 100幅= 37）（実際の時間= 20.992..424.946行= 10ループ= 1）
フィルター：（c2 ~~ 'abcd％' :: text）
フィルターによって削除された行：1,000,000
合計ランタイム：425.039ミリ秒
（4行）

再度Seq Scanしますか？ データベースはテキストフィールドにUTF-8形式を使用しているため、インデックスは使用されません。
そのような場合にインデックスを作成するときは、演算子クラスtext_pattern_ops使用する必要があります。
 CREATE INDEX ON foo(c2 text_pattern_ops); EXPLAIN SELECT * FROM foo WHERE c2 LIKE 'abcd%';

クエリプラン
-fooのビットマップヒープスキャン（コスト= 4.58..55.20行= 100幅= 37）
フィルター：（c2 ~~ 'abcd％' :: text）
-> foo_c2_idx1のビットマップインデックススキャン（コスト= 0.00..4.55行= 13幅= 0）
インデックス条件：（（c2〜> =〜 'abcd' ::テキスト）AND（c2〜<〜 'abce' ::テキスト））
（4行）

やった！ わかった！
Bitmap Index Scan foo_c2_idx1インデックスを使用して必要なレコードを決定し、PostgreSQLがテーブル自体にクロールします：（ Bitmap Heap Scan ）-これらのレコードが実際に存在することを確認します。 この動作は、PostgreSQLのバージョン管理に関連しています。

行全体ではなく、インデックスを作成するフィールドのみを選択した場合
 EXPLAIN SELECT c1 FROM foo WHERE c1 < 500;

クエリプラン
-fooでfoo_c1_idxを使用したインデックスのみのスキャン（コスト= 0.42..25.78行= 491幅= 4）
インデックス条件：（c1 <500）
（2行）

Index Only Scanは、表の行全体を読み取る必要がないため、 Index Scanよりも高速です： width=4 。

まとめ




パート3

Source: https://habr.com/ru/post/J203386/

More articles:


All Articles