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 的访问方式;这不是生产环境调优手段。
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;预期结果
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 处理预约时间段是否发生重叠。
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;预期结果
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 不为每一行保存传统索引项,而是为连续的一组数据页维护最小值、最大值等摘要。它特别适合按时间持续追加的超大表:当旧数据主要位于前部物理块、新数据主要位于后部物理块时,时间条件可以快速排除大量无关块。
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';预期结果
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 做真实基准比较后再决定是否采用。
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';预期结果
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。