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