BRINBRIN物語 - 日々の記録別館

PostgreSQL 9.5-alphaがリリースされて一週間、いろんな人がいろんな機能を試しているかと思いますが、おいらも本リリース前にいろいろ試してみようかと。
まず、9.5の目玉機能の一つとも思われるBRINについて試してみましたよと。

BRINってなんぞ？

BRINはBlock Range INdexを略したもので、そのまま略すとブロックレンジインデックス・・・ってこれだけじゃ何かやっぱり良く分からんですね。
PostgreSQL9.5alphaのドキュメントを見ると・・・

BRINは、特定の列が表内の物理的な場所といくつかの自然な相関関係を持っている非常に大きなテーブルを処理するために設計されている。
ブロックレンジは、テーブル内の物理的に隣接しているページのグループである。
各ブロックレンジのために、いくつかのサマリー情報がインデックスに保存されている。

うーん、わかったようなわからんような。
で、次の節を見てみる。

BRINインデックスは通常のビットマップインデックススキャンを介してクエリを満たす、かつインデックスに保存されたサマリ情報が照会条件と一致している場合には、各範囲内のすべてのページ内のすべてのタプルを返します。
クエリエグゼキュータは、これらのタプルを再確認し、クエリの条件に一致しないものの廃棄を担当する。
言い換えると、これらのインデックスは損失が多い。
BRINインデックスは非常に小さいので、インデックスをスキャンすると、シーケンシャルスキャンに比べてわずかなオーバーヘッドが追加されるが、一致するタプルを含まないことが知られているテーブルの大部分のスキャンを回避することができる。

ふーむ。要はインデックスを基に限定した範囲のブロックに対してシーケンシャルスキャンをして、そのブロック内のタプルをRecheckする検索方式なのかな。
ここから読み取れるのは

BRINのサイズは(btree等と比較すると)非常に小さい。
- ということはインデックス構築時間やINSERTによるインデックス更新時間も小さくなる(はず)
そんかし検索はbtreeと比較すると遅いはず。btreeのようにダイレクトにタプルの場所を特定するものではなく、おおよその場所(ブロック)を特定して、その後にRecheckを実施して検索対象かどうかを決定するから。
表内の物理的な場所といくつかの自然な相関関係を持っている、という記述から考えると、correlationが1あるいは-1に近い列に対して設定すべきインデックスなのかな。

で、その理解が合ってるのかどうか、測定して確認してみることにした。

インデックスサイズ

まず、インデックスサイズの差を見てみることにする。

int4型を1000万件突っ込んで、その列に対してbtree/BRINそれぞれのインデックスを作成して、そのサイズを見てみると・・・

おお、圧倒的なサイズの差。見よこの減り！！
今回試したのは1000万件程度だけど、これが億件、十億件オーダーになってくるとBRINインデックスサイズの小ささは確かに大きな武器になってきそう。

インデックス作成時間

で、インデックスサイズが減ることによって当然ながらインデックス構築時間にも影響が出てくるはず。
ということでインデックス構築時間も測定してみた。
今回は、インデックス対象となる列のcorrelationも数パターン変えて、傾向の違いを見てみることにした。

パターン	correlation	備考
パターン1	1	今回の場合はgenerate_series()で値と物理配置を完全に一致させてみる。
パターン2	0.69995	ある程度値と物理順序を一致させつつ、多少乱数値によって相関性を崩してみる。
パターン3	0.418687	パターン2より更に相関性を下げたケース。
パターン4	-0.00352207	乱数値を使うことで値の順序と物理配置の相関をなくした列。

やはりインデックス作成時間もbtreeと比較するとBRINが大きく短縮されている。
- correlationが1のように綺麗に整列された状態と、そうでない場合でのインデックス作成時間はBRINよりもbtreeのほうが影響を受けやすい？
- BRINの場合はサイズが小さい＆変わらないから、インデックス作成時間にも影響が現れないのかも。

インデックス設定状態での挿入時間

インデックス作成時間が短縮されるのであれば、インデックスが設定された状態での行挿入の時間も短くなると思う。
なので、btree/BRINの各インデックスを設定した状態で、

空の状態から1000万件COPY
更に追加で1000万件COPY(2回)

の時間を測定してみた。比較のためにインデックスが存在しない状態でのCOPY時間も測定しておく。

こうかはばつぐんだ！
インデックスを設定した状態でのCOPY時間についてもbtreeとBRINで大きな差異がある。
挿入主体のモデルではBRINを使う意義は結構ありそうだ。

検索性能

さて、つぎは検索。
検索性能とcorrelationの関連を一緒に確認してみる。
1000万件のint型データを挿入して、btree/BRINを設定。該当のカラムを検索条件とするSELECTをEXPLAIN ALNALYZE付きで発行して、その時間を測定してみる。比較のためにインデックスを設定しないパターンも測定しておく。

correlationパターン	インデックスなし	btree	brin
パターン1	1110.250	0.017	1.030
パターン2	-	0.011	1.823
パターン3	-	0.011	1.095
パターン4	-	0.012	1402.571

むむむ・・・
パターン4のようにランダム値をセットした列、つまりcreelationがほぼ0の列の場合、シーケンシャルスキャンよりBRINスキャンのほうが遅くなる！
まあ、特定のブロックを特定してスキャンを回避できないので、遅いのは当然。

さて、btreeとBRINでどの程度検索性能に差があるのかを比較してみる。
さっきのグラフのY軸を拡大してみると・・・

btreeと比較すると検索性能は遅い、遅い、実際遅い。
まあ、遅いとはいっても絶対値で見ればそれほど遅くはないし、correlatoinがほぼ0のような極端なケースを除外すれば、インデックスがないより十分に速い。

pgbench測定

ついでにpgbenchで軽くbtree/BRINの性能も測ってみた。
scale=100でまずデータを投入。
件数が多いテーブルはpgbench_accountsのみなのでaidに元々設定されているbtreeインデックス(primary keyによる暗黙のインデックス)と、BRIN(ALTER TABLE ... DROP CONSTRAINTでPKを外して、BRINを生成)で比較してみた。
一応、3回測定してその平均値をとってみた。