クエリの最適化。 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



。

まとめ

• Seq Scan-テーブル全体が読み取られます。
• インデックススキャン-WHERE条件にインデックスを使用し、行を選択するときにテーブルを読み取ります。
• ビットマップインデックススキャン-最初にインデックススキャン、次にテーブル選択コントロール。 多数の回線に効果的です。
• インデックスのみのスキャンが最速です。 インデックスのみが読み取られます。

パート3