PostgreSQL / 系统课时

GIN、GiST、BRIN 与 Hash

GIN、GiST、BRIN 与 Hash

这节课解决“B-tree 不合适时该选什么索引”的问题。你将比较 GIN、GiST、BRIN 与 Hash 的核心特点,并通过可执行 SQL 观察它们如何分别服务数组包含、范围重叠、超大顺序表过滤和纯等值查询。

学习目标

  • 区分 GIN、GiST、BRIN 与 Hash 的索引组织方式和典型查询
  • 能根据操作符、数据分布和表规模选择索引方法
  • 能用 EXPLAIN 验证查询是否命中预期索引

四种索引解决的问题不同

这些索引不是 B-tree 的简单快慢替代品,而是针对不同搜索模型设计。选型时首先看查询使用什么操作符,其次看一行是否包含多个可检索元素、数据与物理顺序是否相关,以及是否需要范围扫描或排序。

索引方法 核心思路 典型场景 关键限制
GIN 为一行中的多个元素建立倒排项 数组、jsonb、全文检索 更新成本和索引体积通常较高
GiST 用可扩展搜索树表示区域或距离关系 range、几何、邻近搜索 能力取决于数据类型和操作符类
BRIN 为一组连续数据页保存摘要 超大时序表、递增数据 依赖列值与物理位置存在相关性
Hash 保存哈希值定位等值键 长期只使用 = 的查询 不支持范围扫描和排序

用 GIN 查包含关系

GIN 很适合“一行中有多个可检索元素”的数据。下面用数组字段保存标签,并查询包含 postgresql 标签的文章。由于演示表只有三行,暂时关闭顺序扫描,使执行计划明确展示 GIN 的访问方式;这不是生产环境调优手段。

sql
DROP TABLE IF EXISTS demo_articles;

CREATE TABLE demo_articles (
    id integer PRIMARY KEY,
    tags text[] NOT NULL
);

INSERT INTO demo_articles VALUES
    (1, ARRAY['postgresql', 'index']),
    (2, ARRAY['redis', 'cache']),
    (3, ARRAY['postgresql', 'jsonb']);

CREATE INDEX idx_demo_articles_tags_gin
ON demo_articles USING gin (tags);

SET enable_seqscan = off;

EXPLAIN (COSTS OFF)
SELECT id
FROM demo_articles
WHERE tags @> ARRAY['postgresql'];

RESET enable_seqscan;

预期结果

text
Bitmap Heap Scan on demo_articles
  Recheck Cond: (tags @> '{postgresql}'::text[])
  ->  Bitmap Index Scan on idx_demo_articles_tags_gin
        Index Cond: (tags @> '{postgresql}'::text[])

GIN 的重点不是维护一行一个排序键,而是从元素反查包含这些元素的行,因此特别适合数组、全文词项和 jsonb 中的成员查询。

GiST 适合范围关系

GiST 是可扩展搜索树框架,常用于重叠、包含、邻近等不只是简单大小比较的关系。PostgreSQL 的 range 类型可以直接配合 GiST 处理预约时间段是否发生重叠。

sql
DROP TABLE IF EXISTS demo_bookings;

CREATE TABLE demo_bookings (
    id integer PRIMARY KEY,
    room_id integer NOT NULL,
    during tsrange NOT NULL
);

INSERT INTO demo_bookings VALUES
    (1, 101, tsrange('2026-09-03 09:00', '2026-09-03 10:00')),
    (2, 101, tsrange('2026-09-03 11:00', '2026-09-03 12:00')),
    (3, 102, tsrange('2026-09-03 09:30', '2026-09-03 10:30'));

CREATE INDEX idx_demo_bookings_during_gist
ON demo_bookings USING gist (during);

SET enable_seqscan = off;

EXPLAIN (COSTS OFF)
SELECT id
FROM demo_bookings
WHERE during && tsrange('2026-09-03 09:45', '2026-09-03 10:15');

RESET enable_seqscan;

预期结果

text
Index Scan using idx_demo_bookings_during_gist on demo_bookings
  Index Cond: (during && '["2026-09-03 09:45:00","2026-09-03 10:15:00")'::tsrange)

不同版本可能以略有差异的格式显示 range 字面量,但关键计划节点应体现 GiST 索引以及重叠操作符 &&。如果还要把 room_id 一并纳入排他约束或复合索引,则需要根据具体操作符类进一步设计。

BRIN 用小索引完成块级裁剪

BRIN 不为每一行保存传统索引项,而是为连续的一组数据页维护最小值、最大值等摘要。它特别适合按时间持续追加的超大表:当旧数据主要位于前部物理块、新数据主要位于后部物理块时,时间条件可以快速排除大量无关块。

sql
DROP TABLE IF EXISTS demo_events;

CREATE TABLE demo_events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    created_at timestamp NOT NULL,
    payload text NOT NULL
);

INSERT INTO demo_events (created_at, payload)
SELECT
    timestamp '2026-01-01' + g * interval '1 minute',
    'event-' || g
FROM generate_series(1, 100000) AS g;

CREATE INDEX idx_demo_events_created_brin
ON demo_events USING brin (created_at);

ANALYZE demo_events;

EXPLAIN (COSTS OFF)
SELECT count(*)
FROM demo_events
WHERE created_at >= timestamp '2026-03-10'
  AND created_at < timestamp '2026-03-11';

预期结果

text
Aggregate
  ->  Bitmap Heap Scan on demo_events
        Recheck Cond: ((created_at >= '2026-03-10 00:00:00'::timestamp without time zone) AND (created_at < '2026-03-11 00:00:00'::timestamp without time zone))
        ->  Bitmap Index Scan on idx_demo_events_created_brin
              Index Cond: ((created_at >= '2026-03-10 00:00:00'::timestamp without time zone) AND (created_at < '2026-03-11 00:00:00'::timestamp without time zone))

具体环境仍可能因成本估算选择顺序扫描。BRIN 的价值不在于“每次都必须走索引”,而是在规模足够大、数据物理顺序与过滤列高度相关时,用很小的索引完成粗粒度块排除。

Hash 只服务等值匹配

Hash 索引主要服务 = 等值查询,不保存可用于范围扫描的键顺序,也不能帮助 ORDER BY。如果访问模式长期固定为按照一个键精确查找,可以把它与 B-tree 做真实基准比较后再决定是否采用。

sql
DROP TABLE IF EXISTS demo_sessions;

CREATE TABLE demo_sessions (
    id bigint GENERATED ALWAYS AS IDENTITY,
    token text NOT NULL,
    user_id bigint NOT NULL
);

INSERT INTO demo_sessions (token, user_id)
SELECT 'token-' || g, g
FROM generate_series(1, 50000) AS g;

CREATE INDEX idx_demo_sessions_token_hash
ON demo_sessions USING hash (token);

ANALYZE demo_sessions;

EXPLAIN (COSTS OFF)
SELECT user_id
FROM demo_sessions
WHERE token = 'token-42000';

预期结果

text
Index Scan using idx_demo_sessions_token_hash on demo_sessions
  Index Cond: (token = 'token-42000'::text)

现代 PostgreSQL 的 Hash 索引具备 WAL 记录和崩溃恢复能力,但 B-tree 同样可以高效处理等值条件,并且用途更广,因此不能仅凭“查询是等值”就断定 Hash 更快。

选型时先确认操作符和数据分布

元素包含、全文词项或 jsonb 成员检索优先考察 GIN;范围重叠、几何和距离关系优先考察 GiST;超大且物理排列与过滤列高度相关的数据适合评估 BRIN;只有纯等值访问模式才值得专门比较 Hash。随后还要衡量索引大小和写入维护成本,尤其是更新频繁的 GIN。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

GIN、GiST、BRIN 与 Hash

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通