実行計画とは
実行計画はSQL文が参照するテーブルをスキャンする方法や、テーブルを結合するアルゴリズム等を示します。
また、実行計画はその時点の統計情報や各種パラメータを参照してプランナが生成します。
発行するSQL文の文頭にEXPLAINを付与することで内容を確認できます。
プランタイプ
本稿では実行計画におけるプランタイプにについて記載します。
プランタイプとはエグゼキュータが実際にデータ処理するための具体的な手順のことを指します。
テーブルスキャンやテーブルの結合等、以下のような複数の種類があります。
- Seq Scan
- Index Scan
- Bitmap Scan
- Sort
- Nested Loop
- Merge Join
プランタイプは種類により初期コストの有無が異なります。
Seq Scan
- テーブルを全行チェックして必要行を走査する
- インデックスを利用していない場合に選択される
- 利用できるインデックスが存在していてもオプティマイザのコス計算結果によって、シーケンシャルスキャンが選択される場合もある
対象テーブルのレコード数に比例してコストが増加するため、レコード数が多いテーブルに対しては別のアクセス方式が選択されるようなチューニングが必要です。
本来はインデックススキャンの利用が想定されている場面で、利用されず性能上の問題が発生する場合は以下のようなことが考えられます。
- LIKE演算子で部分一致検索
- 条件式に関数を利用しているに関数インデックス未作成
- 条件式に複数列インデックスの一部の列しか利用していない 等
Index Scan
- インデックスとテーブルを交互にアクセスして必要行を走査
- 大量のテーブルから少ない行を取得するときに優位性が高い
事前に作成されたインデックスに対して部分的にアクセスを行い、特定行を高速に取得します。
大量行のテーブルから大量行を取得する場合、インデックススキャンではインデックスとテーブルに対して
交互にアクセスするため低効率となります。
Bitmap Scan
- 検索条件からインデックスのビットマップを生成して走査に利用
- ビットマップ生成の初期コストがかかる
- 同一ページ内に取得対象行が多くあるとまとめて取得できる分I/Oコストが有利
- 複数のインデックスを結合した検索の場合に優位性が高い
BitmapScanではまずインデックスに対しアクセスを行い、取得対象となるデータの格納位置と対応するビットマップが作成されます。
次に作成されたBitmapを利用してテーブルに対してシーケンシャルアクセスを実行します。
大量行を取得する場合、取得行数がある程度多くても効率的にデータを取得することができます。
ただし、Bitmapを生成するための初期コストがかかります。
Sort
- データが
work_mem上に収まればクイックソート -
log_tmp_filesに0を設定することでソートがwork_memに収まりきらなかった場合に作成される - ORDER BY以外にもGROUP BY、DISTINCT、UNION等で行われる
テーブルスキャン後、ORDER BY等のデータの並び替え処理で採用されます。
データがwork_mem上に収まれば並び替え処理を全てメモリ上で実施するクイックソートが選択されますが、
work_memに収まらない場合は、ディスク併用したマージソートが採用されます。
Nested Loop
- 最も単純な結合方式
- 外側テーブルを1行ずつ取得し、内側テーブルの全レコードと比較
- 初期コスト0
- 結合キーにインデックスがあると効率が良い
テーブル結合した時のプランタイプの一つ。
結合する互いのテーブルの行数が多いとそれだけ膨大な処理量になってしまいます。
Merge Join
- 両方のテーブルの結合キーを結合する前にソート
- ソートのための初期コストがかかる
- 結合キーにインデックスがある場合はIndex Scanが採用されるためソートが不要になる
並び替えの初期コストが発生してしまうが、テーブルスキャンする回数が一度で済むので
行数の多いテーブル同士を結合する際に効率化となります。
また、結合条件となる列に対して事前にB-treeインデックスが作成されている場合は
並び替え処理の代わりにインデックスを利用するので、初期コストも発生しなくなります。
Hash Join
- 内側テーブルの結合キーのハッシュ表を結合前に作成
-
log_tmp_filesに0を設定することでハッシュ表がwork_memに収まりきらない場合に作成される一時ファイルの状況をログに出力可能
初期コストは発生するが、結合条件にインデックスがされていない場合でも比較的高速な結合が期待できます。
ハッシュテーブルはwork_mem上に作成されます。ハッシュテーブルがwork_memのサイズに収まりきらない場合は一時ファイルが作成・利用されます。