TECH BLOG
技術ブログ

今回は PostgreSQL チューニングのお話として、部分インデックスの効果について検証した結果をお知らせしたいと思います。

部分インデックスとは

部分インデックスとは、公式ドキュメント( https://www.postgresql.jp/document/current/html/indexes-partial.html )では

部分インデックスとは、テーブルの部分集合に構築されるインデックスです。 部分集合は、（部分インデックスの述語と呼ばれる）条件式で定義されます。 部分インデックスには、その述語を満たすテーブル行のみに対するエントリが含まれます。

と説明されており、文字通り、テーブルのレコードの一部にだけ定義されたインデックスとなります。
「カーディナリティが低いカラム」や「一部の値でレコード全体の数十パーセントを占めているカラム」などは、通常のインデックスではなく、部分インデックスを定義した方がよい場合があります。

カーディナリティとは

インデックスを定義する際に、よく「カーディナリティが高いカラムに定義してください」というお話を聞くかと思います。
カーディナリティとは、データの種類がどれだけ多いか（または少ないか）を表す言葉となっています。
例えば、レコードが 100 万行格納されているテーブルに type というカラムがあったとして、その type カラムに格納されている値が「A」「B」「C」の 3 種類だとすると「カーディナリティは低い」と表現されます。
逆に 50 万種類の値が格納されていると「カーディナリティは高い」と表現されます。

部分インデックスの効果を確認

では、実際に部分インデックスを利用した検索の実行速度がどうなるのかを確認しました。
検証した環境は

• Ubuntu 22.04.1 LTS
• PostgreSQL 15.2

です。

検証手順

1. boolean 型のカラムをもつテーブルを用意し、100 万行のレコードを追加
   • テーブルの名前は test_table
   • boolean 型のカラムの名前は flg
2. flg カラムに対して、部分インデックスと通常のインデックス（レコード全件に対するインデックス）を定義
3. flg = true を 10 万行、20 万行、30 万行、40 万行、50 万行、と変動させて SELECT （下記 2 SQL）の実行速度がどう変動するかを確認
   • SELECT * FROM test_table WHERE flg = true
   • SELECT * FROM test_table WHERE flg = false

という流れで SELECT の実行速度を確認しました。

検証結果

SELECT を実行した結果をグラフにしたものを記載します。
（グラフが小さくて見にくい場合は、画像を保存するか新しいタブで開いて拡大してください）

(1)部分インデックスが定義されている条件での SELECT の実行速度

(2)部分インデックスが定義されていない条件での SELECT の実行速度

• 縦軸が SQL 実行速度（単位は ms）です。
• 横軸が flg = true のレコード件数の割合です。
  • 100 万行に対する割合で、例えば 10 % であれば、flg = true のレコード件数は 10 万行となります。
• 「ヒント句あり」となっているのは、pg_hint_plan モジュールのヒント句でインデックスを利用するように指定した場合の実行時間です。

検証結果から分かったこと

• (1)のグラフより、部分インデックスが定義されている条件であれば、通常のインデックスと部分インデックスで実行速度に差がないことを確認できました。
• (2)のグラフより、部分インデックスが定義されていない条件では、当然ですが通常のインデックスの方が部分インデックスよりも高速に実行されます。
• flg = true の件数が 20 万行（全レコード件数に対して 20 %）を超えると、インデックス利用されなくなりました。
  • ヒント句でインデックスを利用するように指定すると、SELECT が高速化されました。

まとめ

今回は部分インデックスと通常のレコード全体に対するインデックスの実行時間を検証し、比較しました。
今回、検証に利用したインデックスの種類は B-tree インデックスですが、flg = true のような一致検索では Hash インデックスも利用可能です。
次回は Hash インデックスの部分インデックスを利用した SELECT の実行速度などを検証した結果をお知らせしたいと思います。