-
2023-07-12 PostgreSQLPostgreSQL チューニング
部分インデックスの効果を検証(2)
前回( https://www.insight-ltd.co.jp/?post_type=tech_blog&p=715 )に引き続き、今回も部分インデックスの効果について検証した結果をお知らせします。
前回は通常の BTree インデックスでしたが、今回は Hash インデックスで部分インデックスを利用した場合の効果を確認しました。
部分インデックスの概要については前回を参照してください。
部分インデックスの効果を確認
検証した環境は前回と同じく
- Ubuntu 22.04.1 LTS
- PostgreSQL 15.2
です。
検証手順
検証の手順も変わっていませんが、改めて提示しておきます。
- boolean 型のカラムをもつテーブルを用意し、100 万行のレコードを追加
- テーブルの名前は test_table
- boolean 型のカラムの名前は flg
- flg カラムに対して、部分インデックスと通常のインデックス（レコード全件に対するインデックス）を定義
- flg = true を 10 万行、20 万行、30 万行、40 万行、50 万行、と変動させて SELECT （下記 2 SQL）の実行速度がどう変動するかを確認
SELECT * FROM test_table WHERE flg = trueSELECT * FROM test_table WHERE flg = false
という流れで SELECT の実行速度を確認しました。
検証結果
SELECT を実行した結果をグラフにしたものを記載します。
（グラフが小さくて見にくい場合は、画像を保存するか新しいタブで開いて拡大してください）
(1)部分インデックスが定義されている条件での SELECT の実行速度
(2)部分インデックスが定義されていない条件での SELECT の実行速度
- 縦軸が SQL 実行速度（単位は ms）です。
- 横軸が flg = true のレコード件数の割合です。
- 100 万行に対する割合で、例えば 10 % であれば、flg = true のレコード件数は 10 万行となります。
- 「ヒント句あり」となっているのは、pg_hint_plan モジュールのヒント句でインデックスを利用するように指定した場合の実行時間です。
検証結果の確認
- 部分インデックスの場合、部分インデックスが利用される条件にもかかわらず、シーケンシャルスキャンが行われるため低速になっています。
- Hashインデックスを部分インデックスとして定義した場合、検索に利用されないようです。
- 通常のインデックスの場合、(1) と (2) の結果から、検索条件に該当するレコード数が少なければ少ないほど、高速に実行されることが分かりました。
- BTree インデックスと同様に、Hash インデックスでも flg = true の件数が 20 万行（全レコード件数に対して 20 %）を超えると、インデックス利用されなくなりました。
- ヒント句でインデックスを利用するように指定すると、SELECT が高速化されました。
BTree インデックスと Hash インデックスの比較
部分インデックスとは内容がずれてしまいますが、BTree インデックスと Hash インデックスの比較もしました。
テーブルサイズは 2 GB、レコード件数は 100 万件。
インデックスが定義されているのは boolean 型の flg カラムになります。
サイズ比較
- Hash インデックスのサイズは、BTree インデックスの 70 % 前後となりました。
実行速度比較
-
-
Hash インデックスの実行速度は、BTree インデックスの 110 % 前後となりました。
意外なことに Hash インデックスの方が、BTree インデックスより遅いという結果となりました。
一般的には BTree インデックスよりも Hash インデックスの方が高速に実行されるのですが、1バイトの bollean 型のカラムを検索する場合などには BTree インデックスの方が高速なのかもしれません。
まとめ
今回は Hash インデックスを利用して、部分インデックスと通常のレコード全体に対するインデックスの実行時間を比較しました。
また、BTree インデックスと Hash インデックスの比較も行いました。
Hash インデックスは、部分インデックスとして定義しても検索に利用されないことなど、今回の検証で新たに知る内容もありました。
インデックスは検索を高速化してくれる便利なものですが、使い方を誤ると遅延の原因となりますので、正しく使っていきたいものです。