Hatena::ブログ(Diary)

日々の記録 別館

2015-07-13

BRINBRIN物語

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

BRINってなんぞ?

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

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

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

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

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

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

インデックスサイズ

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

int4型を1000万件突っ込んで、その列に対してbtree/BRINそれぞれのインデックスを作成して、そのサイズを見てみると・・・
f:id:nuko_yokohama:20150713194458p:image:w360
おお、圧倒的なサイズの差。見よこの減り!!
今回試したのは1000万件程度だけど、これが億件、十億件オーダーになってくるとBRINインデックスサイズの小ささは確かに大きな武器になってきそう。

インデックス作成時間

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


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

f:id:nuko_yokohama:20150713194457p:image:w360

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

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

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

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

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

f:id:nuko_yokohama:20150713194456p:image:w360

こうかはばつぐんだ!
インデックスを設定した状態での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

f:id:nuko_yokohama:20150713194455p:image:w360

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

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

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

pgbench測定

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

参照のみ

f:id:nuko_yokohama:20150713200800p:image:w360

  • 検索性能の差がそのまま顕著に現れたという結果ですね。

更新あり

f:id:nuko_yokohama:20150713200801p:image:w360

  • 更新あり、といってもpgbenchの場合、pgbench_accountsに対する操作はUPDATEなので、更新の前に検索相当の処理が実行されるから、BRIN側にとっては別に有利ではない。

そしてINSERT操作のみ発生するhistroyにはそもそもインデックスがない・・・
ということで、pgbenchのモデルは元々BRIN向きではない。

まとめ

ここまでの測定結果を元に、BRINの活かしどころを考えてみると

  • レコード数が非常に大きなケース
  • 挿入主体、かつ検索キーとして使う列がcorrelationが1に近いケース。
  • 検索性能はそこそこ早ければ良いケース

みたいなパターンかな。
例えばログ蓄積モデルで、OLTPみたいに極限まで検索性能を要求しなくて、たまにログを検索するときに、ほどほど高速に検索できるみたいな。

アンチパターンとしては、

  • correlationが0に近いような列
  • 極限まで検索性能/更新性能を要求する

かなあ。
そういうときには、btreeを使ったほうが安全そう。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/nuko_yokohama/20150713/1436786726
リンク元