PostgreSQL/索引 BRIN

索引 GIN

PostgreSQL
索引 BRIN

索引 Hash

BRIN (塊範圍索引) 是一種結構，可以加速對包含大量行（> 百萬）且行在資料檔案內以特定物理順序出現的表的查詢。典型的用例是當某一列包含時間戳或生成的序列號，這些資料很少或從未改變，例如：物聯網資料、計算值、感測器輸出、日誌資訊。

您可以將 BRIN 想象成表的“虛擬分割槽”。如果查詢適合此虛擬分割槽，則需要掃描的行數會顯著減少。

BRIN 的強大之處在於它只需要很少的空間。對於包含數億行的表，BRIN 的典型大小為幾 KB，可以輕鬆地放入 RAM。所有其他索引型別都需要更多的空間，25-50% 的表大小並不罕見。

SQL 語法

DROP TABLE IF EXISTS t3;

-- create a table with a timestamp column
CREATE TABLE t3 (id INTEGER, ts TIMESTAMP);

-- insert data
INSERT INTO t3 VALUES (1, '2022-01-01 00:00:01');
INSERT INTO t3 VALUES (2, '2022-01-01 00:00:02');
-- ...

-- create a BRIN index
CREATE INDEX t3_brin_idx ON t3 USING BRIN(ts);

-- use the index with the usual operators. (As far as there are less than 100,000 rows, the index is not used.)
SELECT * FROM t3 WHERE ts = '2022-01-01 00:00:02';

描述

提示：PostgreSQL 術語中使用“塊”一詞，它與“頁”是同義詞（8192 位元組），參見此處。在 BRIN 上下文中，術語“塊範圍”表示資料檔案內許多相鄰頁面的連續序列。

建立 BRIN 時，資料檔案（堆）的頁面序列被虛擬地劃分為切片，稱為塊範圍。例如，如果檔案包含 600 頁，則前 128 頁屬於塊範圍 #1，第二組 128 頁屬於塊範圍 #2，... 直到塊範圍 #5，包含剩餘的頁面數。塊範圍的預設大小為 128 頁；可以在 CREATE INDEX 命令中更改它。接下來，會掃描所有行，並儲存每個塊範圍的索引列的最小值和最大值。請注意，每個最小值/最大值對構成一個數字值範圍。

BRIN 結構由這些塊範圍編號及其相關值範圍組成，例如，塊範圍 #1：最小值=11，最大值=25；塊範圍 #2：最小值=25，最大值=31。因此，它的名字是塊範圍索引。

理想情況下，值範圍不會重疊，但這並非必需。資料檔案中行順序與其在感興趣列中的內容之間的相關性源於 INSERT 命令的序列以及不斷增長的值：以後的 INSERT（對於沒有顯著空閒空間的表）應該包含相等或更高的值。此相關性可能會在以後的 UPDATE 命令操作中隨著時間的推移而丟失。在這種情況下，BRIN 的優勢可能會丟失。

當在執行查詢時使用此類 BRIN 結構時，就會知道特定塊範圍內的所有行都處於其資料範圍之內，反之，其值範圍之外的任何值都不在其任何列中。但不知道哪些具體的值真正存在於資料中！這對使用 BRIN 結構具有重大影響；見下文。

複雜資料型別，例如幾何物件（如矩形），儲存的是更復雜的資料，而不是最小值/最大值，例如邊界框。

包含 BRIN 的檔案包含 3 種不同的頁面型別。

檔案的第一個頁面 (#0) 儲存元資訊，例如範圍對映頁面的數量。
第二個 (#1) 和其他一些頁面包含所謂的範圍對映。它由元組 ID（TID）組成，這些 ID 指向下一級 BRIN 的頁面，即索引頁。由於 TID 有固定的大小（6 個位元組），因此可以像陣列一樣儲存它們：一個接一個，它們之間沒有任何連結。它們的位置與塊範圍編號相關。
其餘頁面包含索引頁。它們包含每個塊範圍的最小值和最大值（值範圍）。

工作原理

選擇

如果 SQL 命令的 WHERE 條件為具有 BRIN 的列指定了標準，則會執行以下步驟

完全掃描所有範圍對映頁面。
逐個讀取相關的索引頁。如果搜尋值適合其任何索引項的值範圍，則認為所有此塊範圍的頁面都是結果集的一部分。
從堆中讀取識別到的所有頁面中的所有行，並評估它們的列。這是必要的，因為 BRIN 只知道每一行的值必須在特定範圍內，例如在 11 到 25 之間。但是，如果搜尋標準是 WHERE col = 20，則識別到的行可能包含其他值，如 11 或 15。

總結：BRIN 不包含指向堆檔案中的特定行的精確指標。它只包含有關塊範圍和值範圍的資訊。儘管如此，在特定條件下（大量行、物理行順序與列值之間的相關性、很少的資料更改），這些資訊足以大幅減少需要讀取以評估搜尋條件的行數。BRIN 的不確定性與其微小的大小相關。

更新

如果新增一行或行的 BRIN 列發生更改，則會執行以下步驟

識別行在資料檔案中物理儲存的頁面編號。
根據頁面編號，計算塊範圍編號（頁面編號除以塊範圍大小）。
塊範圍編號決定了範圍對映中的位置。
讀取索引頁上的相關索引項。
如果行的新的值適合此索引項的值範圍，則無需執行任何操作。如果值超出值範圍，則會在下限或上限更新（擴大）值範圍。

值範圍可能重疊。這會降低 BRIN 的效率。

SQL 語法 - 更多

DROP TABLE IF EXISTS t4;

-- create a table whose rows occupy about 200 byte each
CREATE TABLE t4 (
  id INTEGER,
  ts TIMESTAMP,
  some_space TEXT NOT NULL DEFAULT
              'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ ' ||
              'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ ' ||
              'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ ')
;

-- insert 1 million rows
INSERT INTO t4 (id, ts)
  (SELECT generate_series(1, 1000000, 1),
          generate_series(now(), now() + '1000000 second', '1 second'));

-- size of heap: about 200 MB
SELECT pg_size_pretty(pg_total_relation_size('t4'));

-- create BRIN and show its size: about 48 kB
CREATE INDEX t4_brin_idx ON t4 USING BRIN(ts);
SELECT pg_size_pretty(pg_total_relation_size('t4_brin_idx'));

-- create BTREE and show its size: about 21 MB
CREATE INDEX t4_btree_idx ON t4 USING BTREE(ts);
SELECT pg_size_pretty(pg_total_relation_size('t4_btree_idx'));

-- size of BRIN to BTREE is about 1 : 400

-- ----------------------------------------------------------------

-- show the meta page of BRIN (page #0)
SELECT * FROM brin_metapage_info(get_raw_page('t4_brin_idx', 0));

-- show (the only) revmap page of BRIN (page #1)
SELECT * FROM brin_revmap_data(get_raw_page('t4_brin_idx', 1)) where pages !='(0,0)';

-- show index pages (in this example there is only a single one: page #2)
SELECT itemoffset, blknum, value
FROM   brin_page_items(get_raw_page('t4_brin_idx', 2), 't4_brin_idx') 
ORDER BY itemoffset;

外部連結

關於 BRIN 的 PostgreSQL 文件

索引 GIN

PostgreSQL
索引 BRIN

索引 Hash