現在位置: ホーム / OSSブログ / PostgreSQL 9.5のBRINインデックス

PostgreSQL 9.5のBRINインデックス

2015 年秋にリリース予定の PostgreSQL 9.5 には BRIN (Block Range INdex) と呼ばれる新しいインデックスが追加されます。名前の通り、まとまった単位のブロックに含まれている値をインデックスする仕組みを提供します。本記事では、 BRINについてご紹介しています。

こんにちは。

2015 年秋にリリース予定の PostgreSQL 9.5 には BRIN (Block Range INdex) と呼ばれる新しいインデックスが追加されます。名前の通り、まとまった単位のブロックに含まれている値をインデックスする仕組みを提供します。ブロックとは PostgreSQL のデータ保存単位の 1 つで、内部的に一まとまり (デフォルト 8 KB) の領域として管理されている物です。デフォルトでは 128 ブロック、つまり 1 MB分の領域に含まれているインデックス対象のカラムのデータをインデックスします。

■ BRIN のメリット

BRIN は特定のブロック範囲に含まれているインデックス対象カラムの最大値と最小値のみを保存します。対照的に BTree インデックスは全てのデータをインデックスします。

- BRIN のインデックスは Btree に比べかなり小さい

データを挿入した場合、BTree は複雑な木構造の操作が必要になりますが BRIN はブロック範囲の最大値 / 最小値のみ更新します。挿入や更新の操作が大幅に簡素化されます。

- BRIN はインデックス操作が簡素で高速

つまり、インデックス対象のデータがシリアルにならんでいる場合で特定のデータ範囲を検索したい、といったニーズがあるデータにはメリットが大きいことが解ります。

■ BRIN が不得意なデータ

BRIN は特定のブロック範囲のインデックス対象データの最大値 / 最小値のみを保存しています。検索対象のデータがブロック範囲内にある場合、そのブロック範囲全体をスキャンします。インデックス対象のデータがランダムである場合は、テーブル全体をスキャンした方が速くなると考えられます。例えば、データのハッシュ値を保存しているカラムに BRIN を使っても全く意味がありません。

PostgreSQL 9.5 のマニュアルに記載されているように、BRIN は lossy なインデックスです。データが含まれているブロックを確実に返すものではありません。

BRIN indexes can satisfy queries via regular bitmap index scans, and will return all tuples in all pages within each range if the summary info stored by the index is consistent with the query conditions. The query executor is in charge of rechecking these tuples and discarding those that do not match the query conditions ? in other words, these indexes are lossy. Because a BRIN index is very small, scanning the index adds little overhead compared to a sequential scan, but may avoid scanning large parts of the table that are known not to contain matching tuples.

http://www.postgresql.org/docs/9.5/static/brin-intro.html

確実にデータを取得する必要がある場合は BRIN は使えません。とは言っても BRIN はそもそも時間やシリアルに増える ID などのデータに適したインデックスであり、このようなデータでは最新のデータを取りこぼすようなことがあっても大きな問題にはなりません。

■ BRIN の用途

BRIN はその仕組みから時間と共に増えていき更新されないログデータなどに適しています。

BRIN 自体は多くのデータ型をサポートしています。しかし、データ型をサポートしていても保存されるデータが順番に列んでいない場合はインデックスの効果は期待できません。逆に言うと、サポートしているデータ型でデータが順番に列んでいるならインデックスの効果を期待できます。

Table 61-1. Built-in BRIN Operator Classes
Name    Indexed Data Type  Indexable Operators
abstime_minmax_ops abstime     < = >
int8_minmax_ops  bigint     < = >
bit_minmax_ops  bit      < = >
varbit_minmax_ops bit varying    < = >
box_inclusion_ops box      << &< && &> >> ~= @> > |&>
bytea_minmax_ops bytea     < = >
bpchar_minmax_ops character    < = >
char_minmax_ops  "char"     < = >
date_minmax_ops  date     < = >
float8_minmax_ops double precision  < = >
inet_minmax_ops  inet     < = >
network_inclusion_ops inet    && >>= <> <<
int4_minmax_ops  integer     < = >
interval_minmax_ops interval    < = >
macaddr_minmax_ops macaddr     < = >
name_minmax_ops  name     < = >
numeric_minmax_ops numeric     < = >
pg_lsn_minmax_ops pg_lsn     < = >
oid_minmax_ops  oid      < = >
range_inclusion_ops any range type   << &< && &> >> @>  >=
float4_minmax_ops real     < = >
reltime_minmax_ops reltime     < = >
int2_minmax_ops  smallint    < = >
text_minmax_ops  text     < = >
tid_minmax_ops  tid      < = >
timestamp_minmax_ops timestamp without time zone < = >
timestamptz_minmax_ops timestamp with time zone < = >
time_minmax_ops time without time zone  < = >
timetz_minmax_ops time with time zone  < = >
uuid_minmax_ops  uuid     < = >

http://www.postgresql.org/docs/9.5/static/brin-builtin-opclasses.html

■ BRIN と BTree インデックスの大きさの違い

インデックスサイズの違いは実際に作ってみると簡単に検証できます。整数型 ID だけを持つテーブルに1億レコード保存し、BRIN と BTree インデックスを作成してみます。PostgreSQL は 2015/7/23 時点の git リポジトリの PostgreSQL 9.4 Alpha1 ブランチを利用し、デフォルトでデータベースを初期化した物を使っています。

=# CREATE TABLE brin_example AS SELECT generate_series(1,100000000) AS id;
SELECT 100000000
Time: 22950.425 ms
=# CREATE INDEX btree_index ON brin_example(id);
CREATE INDEX
Time: 59939.857 ms
=# CREATE INDEX brin_index ON brin_example USING brin(id);
CREATE INDEX
Time: 9901.646 ms

インデックスの作成時間はおよそ 1/6 (9901.646 ms vs 59939.857 ms) になっています。

テーブルサイズとインデックスサイズを確認してみます。

=# SELECT relname, pg_size_pretty(pg_relation_size(oid)) FROM pg_class WHERE relname LIKE 'brin_%' OR relname = 'btree_index' ORDER BY relname;
┌──────────────┬────────────────┐
│   relname    │ pg_size_pretty │
├──────────────┼────────────────┤
│ brin_example │ 3457 MB        │
│ brin_index   │ 104 kB         │
│ btree_index  │ 2142 MB        │
└──────────────┴────────────────┘
(3 rows)

ブロック内のデータ範囲しか保存していない BRIN の方が圧倒的にインデックスサイズが小く、およそ 1/21000 (104 KB vs 2142MB) のサイズしかありません。データ量が非常に多い場合、挿入 / 更新性能以外にも、インデックスに利用するディスクスペースも無視できないくらい大きくなります。

BRIN インデックスはブロックの大きさを指定できるので、異なる大きさを指定してインデックスを作成してみます。

=# CREATE INDEX brin_index_64 ON brin_example USING brin(id) WITH (pages_per_range = 64);
CREATE INDEX
Time: 9891.635 ms
=# CREATE INDEX brin_index_256 ON brin_example USING brin(id) WITH (pages_per_range = 256);
CREATE INDEX
Time: 9763.162 ms
=# CREATE INDEX brin_index_512 ON brin_example USING brin(id) WITH (pages_per_range = 512);
CREATE INDEX
Time: 10063.837 ms
=# CREATE INDEX brin_index_1024 ON brin_example USING brin(id) WITH (pages_per_range = 1024); 
CREATE INDEX
Time: 9675.843 ms

インデックス作成には全てのレコードをスキャンする必要がありますが、BRIN は構造が簡単なのでブロックの大きさを変えてもほとんどインデックス作成時間には影響がありません。

これらのインデックスサイズは以下のようになります。

=# SELECT relname, pg_size_pretty(pg_relation_size(oid)) FROM pg_class WHERE relname LIKE 'brin_%' ORDER BY relname;
┌─────────────────┬────────────────┐
│     relname     │ pg_size_pretty │
├─────────────────┼────────────────┤
│ brin_example    │ 3457 MB        │
│ brin_index      │ 104 kB         │
│ brin_index_1024 │ 32 kB          │
│ brin_index_256  │ 64 kB          │
│ brin_index_512  │ 40 kB          │
│ brin_index_64   │ 192 kB         │
└─────────────────┴────────────────┘
(6 rows)

デフォルトの 128 ブロックからブロックの大きさを半分の 64 ブロックにすると概ねインデックスサイズは倍 (104 KB vs 192 KB) になります。ブロックの大きさを 256、512、1024 と増やしていっても、元々あるオーバーヘッドのために半分のまた半分とはなりませんが、インデックスサイズが小くなっていくことが確認できます。

この情報から整数の様に比較的データサイズが小さいカラムしか持たないテーブルの場合、ブロックの大きさを大きくしてもあまり意味がないことがわかります。

ダミーのテキストカラムを作るとどうなるか確認してみます。

=# DROP TABLE brin_example ;
DROP TABLE
Time: 1231.560 ms
=# CREATE TABLE brin_example AS SELECT generate_series(1,100000000) AS id, 'Dummy text. Dummy text. Dummy text. Dummy text. Dummy text. Dummy text. Dummy text. Dummy text. Dummy text. Dummy text. Dummy text. Dummy text. Dummy text. Dummy text. Dummy text. Dummy text. Dummy text. '::text ;
SELECT 100000000
Time: 41579.073 ms
=# CREATE INDEX brin_index ON brin_example USING brin(id);
CREATE INDEX
Time: 39835.597 ms
=# CREATE INDEX brin_index_64 ON brin_example USING brin(id) WITH (pages_per_range = 64);
CREATE INDEX
Time: 15069.162 ms
=# CREATE INDEX brin_index_256 ON brin_example USING brin(id) WITH (pages_per_range = 256);
CREATE INDEX
Time: 13445.317 ms
=# CREATE INDEX brin_index_512 ON brin_example USING brin(id) WITH (pages_per_range = 512);
CREATE INDEX
Time: 13751.639 ms
=# CREATE INDEX brin_index_1024 ON brin_example USING brin(id) WITH (pages_per_range = 1024);
CREATE INDEX
Time: 13365.218 ms
=# \d brin_example 
  Table "public.brin_example"
┌────────┬─────────┬───────────┐
│ Column │  Type   │ Modifiers │
├────────┼─────────┼───────────┤
│ id     │ integer │           │
│ text   │ text    │           │
└────────┴─────────┴───────────┘
Indexes:
    "brin_index" brin (id)
    "brin_index_1024" brin (id) WITH (pages_per_range=1024)
    "brin_index_256" brin (id) WITH (pages_per_range=256)
    "brin_index_512" brin (id) WITH (pages_per_range=512)
    "brin_index_64" brin (id) WITH (pages_per_range=64)

=# SELECT relname, pg_size_pretty(pg_relation_size(oid)) FROM pg_class WHERE relname LIKE 'brin_%' ORDER BY relname;
┌─────────────────┬────────────────┐
│     relname     │ pg_size_pretty │
├─────────────────┼────────────────┤
│ brin_example    │ 23 GB          │
│ brin_index      │ 624 kB         │
│ brin_index_1024 │ 96 kB          │
│ brin_index_256  │ 320 kB         │
│ brin_index_512  │ 168 kB         │
│ brin_index_64   │ 1224 kB        │
└─────────────────┴────────────────┘
(6 rows)

最初のインデックス作成に長い時間がかかっているのはディスクのデータがメモリにキャッシュされていなかったことが原因と思われます。このシステムは 32GB のメモリがあるので 23GB のデータならすべてキャッシュできるので、2 回目以降は安定した速度でインデックスが作成されています。

今回はテーブル定義が変わり、text 型のカラムが増え、カラムデータとしてテキストが保存され "1 つのブロックが保存できるレコード数が少くなった" ので、より多くのスペースが BRIN インデックスに利用されています。

BTree インデックスはブロックに保存できるレコード数の影響を受けないので、整数カラムのみのテーブルでインデックスを作成した時と大きさは変わりません。

=# CREATE INDEX btree_index ON brin_example(id);CREATE INDEX
Time: 71807.035 ms
=# SELECT relname, pg_size_pretty(pg_relation_size(oid)) FROM pg_class WHERE relname = 'btree_index' ORDER BY relname;
┌─────────────┬────────────────┐
│   relname   │ pg_size_pretty │
├─────────────┼────────────────┤
│ btree_index │ 2142 MB        │
└─────────────┴────────────────┘
(1 row)

BRIN が利用するディスク領域はテーブルのデータサイズに較べて無視できるほど小さいですが、レコードの大きさが増えると BRIN のサイズが大きくなる、と覚えておくとよいかも知れません。

■ BRIN のブロックサイズ指定

BRIN を使って検索する場合、BRIN インデックス全体をスキャンし、目的のデータを含むブロック範囲を抽出し、そのブロック範囲のデータすべてをスキャンする、といった動作になります。

┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────
│                                                         QUERY PLAN                                          
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────
│ Bitmap Heap Scan on brin_example  (cost=48.01..52.02 rows=1 width=4) (actual time=6.515..6.771 rows=1 loops=
│   Recheck Cond: (id = 52342323)                                                                             
│   Rows Removed by Index Recheck: 33791                                                                      
│   Heap Blocks: lossy=1024                                                                                   
│   ->  Bitmap Index Scan on brin_index_1024  (cost=0.00..48.01 rows=1 width=0) (actual time=0.715..0.715 rows
│         Index Cond: (id = 52342323)                                                                         
│ Planning time: 0.154 ms                                                                                     
│ Execution time: 6.792 ms                                                                                    
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────
(8 rows)

例えば、日時で販売データを処理することが多く、一時間単位や1分単位でデータブロックの範囲 (デフォルト:128) が埋まってしまうなら、ブロックの範囲を大きな値にした方がより良い性能を期待できます。

BRIN を最適に使うには、保存するデータの大きさ、単位あたりのデータ数、よくクエリに利用するデータ範囲を考えてブロック領域の大きさを決めると良いことがわかります。しかし、そもそも BRIN のインデックスデータは非常に小さく(このケースでもデフォルトの 128 ブロックで 1 MBも利用していない)、この程度のインデックスデータはすべてスキャンしても大した負荷にはなりません。あまり神経質に BRIN のブロック範囲をチューニングする必要はないでしょう。ほとんどの場合、デフォルトの 128 ブロックで問題なく動作するでしょう。

例外は 1 レコードが非常に大きい場合です。例えば、ユーザーが送信した大きなデータ (画像など) を BYTEA 型で保存しログを取っている、といった場合には、デフォルトのブロック数では BRIN のディスク容量オーバーヘッドが無視できない、といった状態になることもありえます。

■ まとめ

BRIN は BTree や他のインデックスの代用となるインデックスではありません。しかし、適した用途に用いると挿入性能を向上させたり、ディスクスペースを節約したり、場合によってはより良い性能を期待できます。BRIN は癖の強いインデックスですが、大量のログを処理しなければならない場合などに便利です。

サイオスOSSよろず相談室

サイオスOSSよろず相談室(1)

問い合わせボタン