Upgrade to Pro — share decks privately, control downloads, hide ads and more …

BRIN(Block Range INdex)

nuko_yokohama
November 24, 2023

BRIN(Block Range INdex)

This presentation will provide an overview of the index BRIN provided by PotgreSQL, how it works, and how to tune it.
We hope that you will consider using BRIN indexes as an option when designing your database.

nuko_yokohama

November 24, 2023
Tweet

More Decks by nuko_yokohama

Other Decks in Technology

Transcript

  1. せっかくなので BRIN を使ってみよう!
    PostgreSQL Conferecnce 2023 (2023-11-24)

    View full-size slide

  2. 自己紹介
    ● ぬこ@横浜 , @nuko_yokohama
    ● にゃーん
    ● 趣味でポスグレをやってる者だ
    ● PostgreSQL アンカンファレンスゆるふわ枠

    View full-size slide

  3. 内容
    ● 概要
    ● 使用例
    ● アンチパターン
    ● BRIN vs B-Tree
    ● BRIN のしくみ
    ● チューニング
    ● メンテナンス
    ● まとめ
    PostgreSQL の
    インデックス設計時に
    こんな選択肢もあることを
    理解してもらえるのがゴール

    View full-size slide

  4. PostgreSQL でサポートされるインデックス
    ● PostgreSQL 標準配布物に含まれるインデックス
    バージョン 内容
    B-Tree PostgreSQL のデフォルトインデックス種別。
    Hash 列値を32bitハッシュ化。=比較のみ対応。
    GiST/SP-GiST 特殊な用途のインデックスの基盤
    GIN 汎用転置インデックス。配列、JSON 、全文検索等。
    BRIN Block Range Index 。本資料で説明。
    contrib/bloom ブルームフィルタインデックス。=比較のみ対応。

    View full-size slide

  5. 概要
    ● BRIN=Block Range INdex
    ● 値が存在するテーブルのブロック番号の範囲をもつ
    ● BRIN の特徴
    – インデックスサイズ:極小
    – Colleration に依存
    – ほどほどに高速な検索

    View full-size slide

  6. History of BRIN
    ● BRIN の歴史
    バージョン 内容
    9.5 ● BRIN が追加されました。
    10 ● BRIN の要約をより積極的に行うオプションを追加しました。
    ● BRIN スキャンが有益かどうかの判断の精度を改善しました。
    14 ● BRIN が範囲ごとに複数の最小値と最大値を記録できるようにしました。
    ● BRIN がブルームフィルタを使えるようにしました。
    16 ● BRIN 列のみが更新される場合に HOT 更新を許可しました。
    ● pageinspect 関数 brin_page_items() に空範囲出力列を追加しました。

    View full-size slide

  7. BRIN の構築
    ● BRIN の構築は、通常のインデックスと同様に CREATE
    INDEX コマンドを使用
    ● USING 句に brin を指定する。
    postgres@brin=# \d test
    Unlogged table "public.test"
    Column | Type | Collation | Nullable | Default
    --------+--------------------------+-----------+----------+---------
    id | integer | | not null |
    reg_ts | timestamp with time zone | | |
    data | text | | |
    Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
    postgres@brin=# CREATE INDEX reg_ts_brin ON test USING brin (reg_ts);
    CREATE INDEX

    View full-size slide

  8. BRIN の構築(対応するデータ型)
    ● 対応しているデータ型
    – 文字型、数字型、日付型、バイナリ型
    ● 数字型っぽい money 型は未対応(!)
    – その他応用的な型
    ● inet, macaddr, anyrange, box, uuid, ...
    – psql の \dAp brin メタコマンドで確認可能

    View full-size slide

  9. BRIN の構築(ユーザ定義型)
    ● ユーザ定義型の場合、 BRIN に対応するインデックスアクセ
    スメソッドを作成していないと CREATE INDEX でエラー。
    postgres@brin=# CREATE TABLE ut_test(id int, fd fraction, dummy text);
    CREATE TABLE
    postgres@brin=# \d ut_test
    Table "public.ut_test"
    Column | Type | Collation | Nullable | Default
    --------+----------+-----------+----------+---------
    id | integer | | |
    fd | fraction | | |
    dummy | text | | |
    postgres@brin=# EXPLAIN SELECT * FROM ut_test WHERE fd >= '999/2' AND fd <= '1005/2';
    QUERY PLAN
    ----------------------------------------------------------------------
    Seq Scan on ut_test (cost=0.00..25.30 rows=5 width=52)
    Filter: ((fd >= '999/2'::fraction) AND (fd <= '1005/2'::fraction))
    (2 rows)
    postgres@brin=# CREATE INDEX fd_brin ON ut_test USING brin (fd);
    ERROR: data type fraction has no default operator class for access method "brin"
    HINT: You must specify an operator class for the index or define a default operator class for the data type.
    postgres@brin=#

    View full-size slide

  10. BRIN による検索
    ● BRIN を設定した列を条件に指定すると検索時に BRIN が使用
    される。
    ● BRIN を使った検索の実行計画例
    postgres@brin=# EXPLAIN SELECT * FROM test WHERE reg_ts BETWEEN '2025-01-01' AND '2025-01-02';
    QUERY PLAN
    -------------------------------------------------------------------------------------------------------------
    --------------------------------------------
    Bitmap Heap Scan on test (cost=21.59..35190.58 rows=6239 width=45)
    Recheck Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-02
    00:00:00+09'::timestamp with time zone))
    -> Bitmap Index Scan on reg_ts_brin (cost=0.00..20.03 rows=12407 width=0)
    Index Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-
    02 00:00:00+09'::timestamp with time zone))
    (4 rows)

    View full-size slide

  11. アンチパターン

    View full-size slide

  12. BRIN と colleration
    ● pg_stats.colleration
    – 物理的な行の並び順と論理的な列の値の並び順に関する統
    計的相関。
    ● 1 or -1 に近い場合:強い相関がある
    ● 0 に近い場合:相関がない
    ● BRIN は colleration が 1 or -1 に近くないと有効ではない

    View full-size slide

  13. Good:colleration が 1 or -1 に近い
    ● BRIN 対象列の colleration が 1 になっている例
    postgres@brin=# \d test
    Unlogged table "public.test"
    Column | Type | Collation | Nullable | Default
    --------+--------------------------+-----------+----------+---------
    id | integer | | not null |
    reg_ts | timestamp with time zone | | |
    data | text | | |
    Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
    postgres@brin=# CREATE INDEX reg_ts_brin ON test USING brin (reg_ts);
    CREATE INDEX
    postgres@brin=# SELECT tablename, attname, n_distinct, correlation FROM pg_stats WHERE tablename = 'test';
    tablename | attname | n_distinct | correlation
    -----------+---------+------------+-------------
    test | id | -1 | 1
    test | reg_ts | -1 | 1
    test | data | -1 | 0.002063701
    (3 rows)
    correlation=1
    → 論理値と物理的な
    配置は完全相関

    View full-size slide

  14. Good:colleration が 1 or -1 に近い
    ● 検索時は最小限のブロックスキャンとなる
    EXPLAIN ANALYZE SELECT * FROM test WHERE reg_ts BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 01:00:00';
    QUERY PLAN
    -------------------------------------------------------------------------------------------------------------------
    --------------------------------------
    Bitmap Heap Scan on test (cost=20.10..35189.08 rows=258 width=45) (actual time=0.879..2.611 rows=277 loops=1)
    Recheck Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01
    01:00:00+09'::timestamp with time zone))
    Rows Removed by Index Recheck: 12139
    Heap Blocks: lossy=128
    -> Bitmap Index Scan on reg_ts_brin (cost=0.00..20.03 rows=12407 width=0) (actual time=0.204..0.205 rows=1280
    loops=1)
    Index Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01
    01:00:00+09'::timestamp with time zone))
    Planning Time: 0.254 ms
    Execution Time: 2.751 ms
    (8 rows)
    最小限のブロックスキャン
    なので高速

    View full-size slide

  15. Bad:colleration が 0 に近い
    ● BRIN 対象列の colleration が 0 近くになっている例
    postgres@brin=# \d test
    Unlogged table "public.test"
    Column | Type | Collation | Nullable | Default
    --------+--------------------------+-----------+----------+---------
    id | integer | | not null |
    reg_ts | timestamp with time zone | | |
    data | text | | |
    Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
    postgres@brin=# CREATE INDEX reg_ts_brin ON test USING brin (reg_ts);
    CREATE INDEX
    postgres@brin=# SELECT tablename, attname, n_distinct, correlation FROM pg_stats WHERE tablename = 'test';
    tablename | attname | n_distinct | correlation
    -----------+---------+------------+--------------
    test | id | -1 | 0.0024409215
    test | reg_ts | -1 | 0.0024409215
    test | data | -1 | 1
    (3 rows)
    correlation が 0 に近い
    → 論理的な値と物理配置に
    相関はほぼない

    View full-size slide

  16. Bad:colleration が 0 に近い
    ● BRIN を使ってもブロックが絞り込めない!
    postgres@brin=# EXPLAIN ANALYZE SELECT * FROM test WHERE reg_ts BETWEEN '2025-01-01 00:00:00' AND '2025-01-01
    01:00:00';
    QUERY PLAN
    -------------------------------------------------------------------------------------------------------------------
    ----------
    ----------------------------
    Bitmap Heap Scan on test (cost=33.18..229725.69 rows=282 width=45) (actual time=13.760..1559.978 rows=277
    loops=1)
    Recheck Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01
    01:00:00+09'::timestamp with time zone))
    Rows Removed by Index Recheck: 9999723
    Heap Blocks: lossy=103093
    -> Bitmap Index Scan on reg_ts_brin (cost=0.00..33.11 rows=5082895 width=0) (actual time=3.556..3.558
    rows=1031280 loops
    =1)
    Index Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01
    01:00:00+09'::timestamp with time zone))
    Planning Time: 0.097 ms
    Execution Time: 1560.306 ms
    (8 rows)
    大量のブロックを読込み
    ほとんど Recheck で除外
    とても遅い・・・

    View full-size slide

  17. Bad: 更新が多発するケース
    ● 最初は colleraion が 1/-1 に近い場合でも以下のようなケースでは
    colleration が 0 に近くなっていく。
    – 更新が頻繁に発生し、最初に格納されたデータが物理的に後に移
    動する
    – DELETE や UPDATE によって無効領域→空き領域になった箇所
    に新しいデータが格納される
    ● DELETE/UPDATE が多発するテーブルには BRIN は向いていない。

    View full-size slide

  18. BRIN vs B-Tree

    View full-size slide

  19. BRIN vs B-Tree
    BRIN B-Tree
    インデックスサイズ /
    作成時間
    サイズは非常に小さい
    作成時間も小さい
    サイズは大
    作成時間は大
    検索時間 検索結果件数(範囲)が多
    い場合は B-Tree より早い
    検索結果件数が少ない場合
    は BRIN より高速
    制約 使用可能なデータ型は限定
    CLUSTER 不可
    なし
    その他 アンチパターンケースあり アンチパターンケースなし

    View full-size slide

  20. BRIN vs B-Tree
    ● インデックスサイズ / インデックス作成時間
    btree
    brin
    0 5000 10000 15000 20000 25000 30000
    27422
    5
    インデックスサイズ
    ブロック数
    btree
    brin
    0 2000 4000 6000 8000 10000 12000
    9854.367
    1962.126
    インデックス作成時間
    インデックス作成時間 (ms)

    View full-size slide

  21. BRIN vs B-Tree
    ● 取得件数と検索時間
    – 取得件数が少ない場合は B-Tree が有利
    – ある程度の件数になると BRIN も B-Tree も差がなくなる
    0 50000 100000 150000 200000 250000
    0
    200
    400
    600
    800
    1000
    1200
    インデックス種別と検索時間
    btree
    brin
    (seq scan)
    取得件数
    検索時間 (ms)
    0 1000 2000 3000 4000 5000 6000 7000 8000
    0
    1
    2
    3
    4
    5
    6
    7
    8
    インデックス種別と検索時間(件数小の区間)
    btree
    brin
    (seq scan)
    取得件数
    検索時間 (ms)

    View full-size slide

  22. BRIN のしくみ

    View full-size slide

  23. contrib/pageinspect
    ● BRIN のファイル内容を解析するときに役に立った拡張機能
    – https://www.postgresql.org/docs/16/pageinspect.html
    ● テーブルやインデックスファイル内容を可視化する
    ● BRIN にも対応している
    – ページ種類の報告
    – ページ内容の報告
    ● 以降のページではこの拡張機能を使って説明しています。

    View full-size slide

  24. BRIN ファイルの構成
    ● BRIN ファイルは3つのパートに分かれている
    metainfo ページ
    revmap ページ
    regular ページ
    1 ページ固定
    ページ数可変
    ページ数可変
    先頭
    末尾

    View full-size slide

  25. metainfo ページ
    ● BRIN の先頭ページ
    ● BRIN 全体のメタ情報を格納。項目は以下参照。
    項目 機能
    magic マジックナンバー
    version バージョン番号。 BRIN_CURRENT_VERSION(1)
    pageperrange ストレージパラメータ pages_per_range の値
    lastrevmappage 最終 revmap ページ

    View full-size slide

  26. revmap ページ
    ● BRIN 内の各エントリがどのブロックに入っているかを示す。
    ● ( ブロック番号 , 通番 )
    ● pageinspect で確認するとこんな感じ。
    pages
    ---------
    (2,1)
    (2,2)
    (2,3)
    (中略)
    (2,291)
    (3,1)
    (3,2)
    (中略)
    (4,223)
    (4,224)
    (中略)
    (0,0)
    (1360 rows)
    この BRIN は
    2 ページから 4 ページ
    まで items がある

    View full-size slide

  27. regular ページ
    ● レンジに対応するブロックの情報や値が格納されている
    項目 機能
    itemoffset BRIN 内の通番
    blknum レンジに対応するテーブルのブロック番号
    attnum インデックス列番号
    allnulls レンジ内のすべての値が NULL か?
    hasnulls レンジ内に NULL があるか?
    placeholder 仮確保されたレンジか?
    empty レンジはタプルを含まない
    value レンジの値

    View full-size slide

  28. regular ページ
    ● pageinspect で見るとこんな感じ
    postgres@brin=# SELECT itemoffset, blknum, value FROM brin_page_items(get_raw_page('reg_ts_brin',2),
    'reg_ts_brin') ORDER BY value ASC LIMIT 5;
    itemoffset | blknum | value
    ------------+--------+----------------------------------------------------
    1 | 0 | {2023-01-01 00:00:00+09 .. 2023-01-02 20:49:55+09}
    2 | 128 | {2023-01-02 20:50:08+09 .. 2023-01-04 17:40:03+09}
    3 | 256 | {2023-01-04 17:40:16+09 .. 2023-01-06 14:30:11+09}
    4 | 384 | {2023-01-06 14:30:24+09 .. 2023-01-08 11:20:19+09}
    5 | 512 | {2023-01-08 11:20:32+09 .. 2023-01-10 08:10:27+09}
    (5 rows)
    ● itemoffset=3 はブロック番号 256 ~ 383 の中に
    2023-01-04 17:40:16 ~ 2023-01-06 14:30 の範囲の
    データが格納されていることを示している。

    View full-size slide

  29. BRIN 検索のイメージ
    ● BRIN インデックス内の参照:①~③
    ● テーブルファイル内の参照:④~⑤
    metainfo ページ
    revmap ページ
    items ページ
    BRIN ファイル
    items ページ
    pagepaerrange
    items は
    2 ~ 3 ブロックだ
    条件に合うブロッ
    ク番号は xxx,
    yyy だ
    テーブルファイル
    ページ
    ページ (xxx)
    ページ
    ページ (yyy)
    ページ
    ページ
    ページ
    ・・・
    Recheck
    検索条件に合致する
    タプル
    ブロック番号 xxx, yyy から
    pagepaerrange 数分の
    ブロックを読む





    View full-size slide

  30. チューニング

    View full-size slide

  31. BRIN チューニングパラメータ
    ● CREATE INDEX で BRIN を作成する時に、以下のストレージ
    パラメータを設定可能
    項目 機能 デフォルト値
    pages_per_range リレーションのページ数を除算する数
    リレーション読込数の単位にもなる
    128
    autosummarize autovacuum 起動時に VACUUM 対象でな
    くてもサマライズする。
    (詳細は BRIN のメンテナンス参照)
    off

    View full-size slide

  32. pages_per_range パラメータ
    ● BRIN サイズと検索時間とのトレードオフ
    ● この数値を大きくする
    – BRIN のサイズが小さくなる
    – 検索時にスキャンされるブロック数の単位が大きくなる
    ● この数値を小さくする
    – BRIN のサイズが大きくなる
    – 検索時にスキャンされるブロック数の単位が小さくなる
    – 検索範囲が比較的小さいケースだと有効

    View full-size slide

  33. pages_per_range パラメータ
    ● pages_per_range を 32, 64, 128, 256, 512 に変更
    ● インデックスサイズ / 作成時間
    0 64 128 192 256 320 384 448 512
    0
    5
    10
    15
    20
    25
    30
    35
    pages_per_range と BRIN サイズ
    pages_per_range
    ブロック数
    0 64 128 192 256 320 384 448 512
    0
    500
    1000
    1500
    2000
    2500
    3000
    3500
    4000
    4500
    5000
    pages_per_range と BRIN 作成時間
    pages_per_range
    BRIN 作成時間 (ms)

    View full-size slide

  34. pages_per_range パラメータ
    ● pages_per_range を 32, 64, 128, 256, 512 に変更
    ● 数ブロック内の結果のみヒットする検索
    ● スキャンブロック数 / 検索時間
    0 64 128 192 256 320 384 448 512
    0
    100
    200
    300
    400
    500
    600
    pages_per_range とスキャンブロック数
    heap ブロック数
    heap+index ブロック数
    pages_per_range
    検索時間 (ms)
    0 64 128 192 256 320 384 448 512
    0
    2
    4
    6
    8
    10
    12
    14
    16
    pages_per_range と検索時間
    pages_per_range
    検索時間 (ms)
    heap+index
    ブロック数
    が逆転している

    View full-size slide

  35. メンテナンス

    View full-size slide

  36. BRIN のメンテナンス
    ● BRIN を設定しているテーブルへ更新があった場合、インデックス自
    体は更新されるが「サマライズ」はされない。
    ● サマライズ
    – 区間内の最小・最大値を更新する
    ● サマライズの実行
    – 自動 VACUUM や手動 VACUUM 背景で実行される。
    – SQL 関数で即時のサマライズも可能

    View full-size slide

  37. 挿入直後にサマライズされないときの問題
    ● BRIN 構築後に大量データの挿入があり、かつ自動 VACUUM/
    VACUUM が実行されないと挿入データのサマリが生成されな
    い。
    ● BRIN 検索時にサマリがないブロックは必ず読んでしまうため
    性能劣化する。

    View full-size slide

  38. 挿入直後にサマライズされないときの問題
    ● 以下のように実行
    – 初期データ 1000 万件挿入( 10000 ブロック程度の挿入)
    ● 最小値 2023-01-01 00:00:00
    ● 最大値 2027-02-10 23:46:59
    – timestamp 型の列に BRIN
    – 検索(1)
    – INSERT で 100 万件挿入( 2028-01-01 00:00:00 ~)
    – 検索(2)
    同じ検索クエリでも
    INSERT 前後では
    処理時間に大きく変わる

    View full-size slide

  39. 検索 (1)
    ● BRIN 構築直後→検索
    EXPLAIN ANALYZE SELECT * FROM test WHERE reg_ts BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 01:00:00';
    QUERY PLAN
    -----------------------------------------------------------------------------------------------------------------
    ----------------------------------------
    Bitmap Heap Scan on test (cost=20.11..35189.09 rows=299 width=45) (actual time=0.854..2.545 rows=277 loops=1)
    Recheck Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01
    01:00:00+09'::timestamp with time zone))
    Rows Removed by Index Recheck: 12139
    Heap Blocks: lossy=128
    -> Bitmap Index Scan on reg_ts_brin (cost=0.00..20.03 rows=12407 width=0) (actual time=0.188..0.188
    rows=1280 loops=1)
    Index Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01
    01:00:00+09'::timestamp with time zone))
    Planning Time: 0.557 ms
    Execution Time: 2.676 ms
    (8 rows)

    View full-size slide

  40. 検索 (2)
    ● BRIN 構築直後→ 100 万件ロード→検索
    EXPLAIN ANALYZE SELECT * FROM test WHERE reg_ts BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 01:00:00';
    QUERY PLAN
    -----------------------------------------------------------------------------------------------------------------
    ----------------------------------------
    Bitmap Heap Scan on test (cost=20.11..35913.31 rows=329 width=45) (actual time=0.698..124.456 rows=277 loops=1)
    Recheck Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01
    01:00:00+09'::timestamp with time zone))
    Rows Removed by Index Recheck: 1004843
    Heap Blocks: lossy=10363
    -> Bitmap Index Scan on reg_ts_brin (cost=0.00..20.03 rows=12415 width=0) (actual time=0.313..0.314
    rows=103630 loops=1)
    Index Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01
    01:00:00+09'::timestamp with time zone))
    Planning Time: 0.062 ms
    Execution Time: 124.605 ms
    (8 rows)

    View full-size slide

  41. 挿入直後にサマライズされないときの問題
    ● INSERT 前 / 後の検索時間

    検索条件 INSERT 前 INSERT 後
    検索件数 2025-01-01 の 1 時間 277 277
    2028-01-01 の 1 時間 0 277
    読込ブロック数 2025-01-01 の 1 時間 128 10363
    2028-01-01 の 1 時間 0 10235
    検索時間 (ms) 2025-01-01 の 1 時間 2.7 124.6
    2028-01-01 の 1 時間 0.18 102.1
    読込ブロック数増大
    → 検索時間の悪化

    View full-size slide

  42. autosummarize
    ● デフォルトは off
    ● autosummarize=on
    – autovacuum 発動時に BRIN 設定テーブルが autovacuum
    対象でなくても、サマライズを実行する。
    ● autosummarize=on でも INSERT 性能への影響はない。

    View full-size slide

  43. BRIN メンテナンス関数
    ● VACUUM 背景で BRIN はメンテナンスされるが、以下の関数を実行してメンテナンス
    も可能
    – サマライズされていない場合、 brin_summarize_new_values で即時反映が可能
    関数名 機能
    brin_summarize_new_values サマライズされていないページ範囲を元にインデッ
    クスタプルを生成する
    brin_summarize_range 指定したブロックに対して
    brin_summarize_new_values 相当を実行
    brin_desummarize_range 指定のブロックを含むページ範囲のインデックスタ
    プルを削除する

    View full-size slide

  44. brin_summarize_new_values の使用例
    ● BRIN 構築直後→ 100 万件ロード→サマライズ関数実行→検索
    SELECT brin_summarize_new_values('reg_ts_brin');
    brin_summarize_new_values
    ---------------------------
    80
    (1 row)
    Time: 176.186 ms
    EXPLAIN ANALYZE SELECT * FROM test WHERE reg_ts BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 01:00:00';
    QUERY PLAN
    -----------------------------------------------------------------------------------------------------------------
    ----------------------------------------
    Bitmap Heap Scan on test (cost=24.11..35917.31 rows=329 width=45) (actual time=0.577..1.599 rows=277 loops=1)
    Recheck Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01
    01:00:00+09'::timestamp with time zone))
    Rows Removed by Index Recheck: 12139
    Heap Blocks: lossy=128
    -> Bitmap Index Scan on reg_ts_brin (cost=0.00..24.03 rows=12415 width=0) (actual time=0.181..0.181
    rows=1280 loops=1)
    Index Cond: ((reg_ts >= '2025-01-01 00:00:00+09'::timestamp with time zone) AND (reg_ts <= '2025-01-01
    01:00:00+09'::timestamp with time zone))
    Planning Time: 0.058 ms
    Execution Time: 1.730 ms
    (8 rows)

    View full-size slide

  45. brin_summarize_new_values の使用例
    ● INSERT 前 / 後 / サマライズ後の検索時間

    検索条件 INSERT 前 INSERT 後 サマライズ後
    検索件数 2025-01-01 の 1 時間 277 277 277
    2028-01-01 の 1 時間 0 277 277
    読込ブロック数 2025-01-01 の 1 時間 128 10363 128
    2028-01-01 の 1 時間 0 10235 128
    検索時間 (ms) 2025-01-01 の 1 時間 2.7 124.6 1.7
    2028-01-01 の 1 時間 0.18 102.1 1.8
    バルク INSERT の後には
    VACUUM か
    サマライズかけるべし

    View full-size slide

  46. BRIN とメンテナンスコマンド
    ● BRIN/BRIN を設定したテーブルに対する挙動
    メンテナンス機能 / コマンド BRIN に対する動作
    VACUUM/ 自動 VACUUM VACUUM 背景で BRIN のサマライズを行う
    VACUUM FULL テーブル詰め込みの後、その状態から BRIN を再
    生成する
    REINDEX 特に制約なし
    CLUSTER BRIN を指定した CLUSTER は不可
    ANALYZE ANALZYE 実行の影響はなし
    pg_repack 列指定でテーブル並べ替え→ BRIN を再生成

    View full-size slide

  47. まとめ
    ● BRIN は B-tree のような汎用性はないが、以下のケースでは有効
    – colleration が 1/-1 に近い
    ● 登録日時等、後から挿入するデータが大きくなるケース
    – INSERT/SELECT のみ行うテーブル
    – ある程度広い範囲の検索を行うケース
    ● BRIN のメンテナンスの運用設計は必要
    – 自動 VACUUM に任せるか、サマライズするか、 REINDEX か

    View full-size slide

  48. おしまい

    View full-size slide