B-tree 之外：GIN、GiST、BRIN 与它们的适用面

准备中…

B-tree 能处理「可全序排列的标量值」。一旦超出这个范围——数组、全文、几何、范围类型——就需要别的索引结构。

先看一个极端对比：BRIN

造一张三十万行的时序表：

DROP TABLE IF EXISTS ts;
CREATE TABLE ts (id bigserial, at timestamptz, v int);
INSERT INTO ts (at, v)
SELECT timestamptz '2024-01-01' + (i * interval '1 minute'), i % 1000
FROM generate_series(1, 300000) i;

⌘/Ctrl + Enter

CREATE INDEX ts_brin  ON ts USING brin (at);
CREATE INDEX ts_btree ON ts USING btree (at);
ANALYZE ts;
SELECT pg_size_pretty(pg_relation_size('ts'))       AS 表,
     pg_size_pretty(pg_relation_size('ts_brin'))  AS BRIN索引,
     pg_size_pretty(pg_relation_size('ts_btree')) AS BTree索引;

⌘/Ctrl + Enter

24 kB 对 6600 kB。 BRIN 是 B-tree 的 1/275。

原理很简单：BRIN（Block Range INdex）不记录每一行，只记录每一段连续页面（默认 128 页）里的最小值和最大值。查询时先用这个摘要排除掉整段整段的页面，剩下的段再逐行检查。

SELECT attname, correlation
FROM pg_stats WHERE tablename = 'ts' ORDER BY attname;

⌘/Ctrl + Enter

at 的相关性接近 1，v 接近 0——所以 at 适合 BRIN，v 不适合。

索引类型速查

类型	索引什么	典型场景	代价
B-tree	可全序的标量	等值、范围、排序、唯一约束	默认选择
Hash	标量的哈希值	只有等值查询	比 B-tree 小一点，但不支持范围/排序，很少值得
GIN	一个值里的多个元素	jsonb、数组、全文检索、trigram 模糊匹配	写入慢、索引大
GiST	可「包含/重叠」的类型	范围类型、几何、最近邻搜索、排他约束	通用但每种类型要有操作符类
SP-GiST	空间可分割的数据	点、IP 前缀、电话号码前缀	适用面窄
BRIN	页面段的摘要	物理有序的大表（时序、日志）	极小，但依赖物理有序

GIN：一个值里有多个可搜索元素

jsonb 一节已经用过 GIN。它的本质是倒排索引：把一个值拆成多个键，每个键指向包含它的所有行。

数组是最直观的例子：

DROP TABLE IF EXISTS articles;
CREATE TABLE articles (id int PRIMARY KEY, tags text[], body text);
INSERT INTO articles
SELECT i,
     ARRAY['tag' || (i % 50), 'tag' || (i % 17), 'tag' || (i % 7)],
     'article body number ' || i
FROM generate_series(1, 20000) i;
CREATE INDEX articles_tags ON articles USING gin (tags);
ANALYZE articles;

⌘/Ctrl + Enter

SELECT count(*) FROM articles WHERE tags @> ARRAY['tag3']

GIN 的更新很贵：改一行意味着要更新这一行拆出的所有键的倒排链表。

Postgres 的缓解办法是 fastupdate（默认开启）——新条目先追加到一个无序的 pending list，攒够 gin_pending_list_limit（默认 4MB）再批量合并进主索引。

代价是：pending list 里的条目查询时必须线性扫描。所以一次大批量写入之后，紧接着的查询会莫名其妙地慢，直到合并完成。表现是「平时很快的查询偶尔飙到几百毫秒」。

对读延迟敏感的表可以关掉：

ALTER INDEX articles_tags SET (fastupdate = off);

trigram：让 LIKE ‘%xxx%’ 走索引

B-tree 能加速 LIKE 'abc%'（前缀匹配），但对 LIKE '%abc%' 无能为力。pg_trgm 把字符串拆成三字符片段，用 GIN 索引它们：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT show_trgm('hello') AS hello拆出的三元组;

⌘/Ctrl + Enter

CREATE INDEX articles_body_trgm ON articles USING gin (body gin_trgm_ops);
ANALYZE articles;

⌘/Ctrl + Enter

SELECT count(*) FROM articles WHERE body LIKE '%number 1234%'

同一个索引还能做相似度搜索（拼写容错）：

SELECT id, body, similarity(body, 'article body number 12345') AS 相似度
FROM articles
WHERE body % 'article body number 12345'
ORDER BY 相似度 DESC
LIMIT 5;

⌘/Ctrl + Enter

GiST：处理「包含」和「重叠」

GiST 是一个框架而不是单一结构——它定义了「如何把数据组织成有层次的边界框」，具体的边界框怎么算由每种数据类型的操作符类提供。

范围类型是最常用的场景（约束一节的排他约束就靠它）：

DROP TABLE IF EXISTS reservation;
CREATE TABLE reservation (id int PRIMARY KEY, during tsrange);
INSERT INTO reservation
SELECT i, tsrange(timestamp '2024-01-01' + (i * interval '1 hour'),
                timestamp '2024-01-01' + (i * interval '1 hour') + interval '45 min')
FROM generate_series(1, 20000) i;
CREATE INDEX res_gist ON reservation USING gist (during);
ANALYZE reservation;

⌘/Ctrl + Enter

SELECT count(*) FROM reservation WHERE during && tsrange('2024-03-01', '2024-03-02')

GiST 还能做 KNN（最近邻）搜索——ORDER BY 距离 LIMIT n 直接走索引，不需要先算出所有距离再排序。这是 pgvector 做向量检索的基础。

表达式索引与部分索引

这两个不是新的索引类型，而是任何索引类型都能用的修饰，且往往是最划算的优化。

CREATE INDEX IF NOT EXISTS idx_u_lower_name ON users (lower(name));
ANALYZE users;

⌘/Ctrl + Enter

SELECT * FROM users WHERE lower(name) = 'user_42'

怎么选

按这个顺序问自己：

是等值或范围查询、需要排序、或者要做唯一约束吗 → B-tree。绝大多数情况到这里就结束了。
要查的是一个值内部的元素（数组包含、jsonb 键值、全文词、子串）→ GIN。
要查的是重叠、包含、距离 → GiST。
表非常大且物理有序，查询是宽范围过滤 → BRIN。
只有一部分行会被查 → 在上面任意一种基础上加 WHERE 变成部分索引。
查的是列的某种变换 → 表达式索引。

每个索引都是持续的成本：每次 INSERT / DELETE 都要写它，每次改到索引列的 UPDATE 都会破坏 HOT（阶段三），还要占内存和备份空间。

在真实服务器上，用这条查询找出从没被用过的索引：

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelid NOT IN (
  SELECT conindid FROM pg_constraint WHERE contype IN ('p','u')
)
ORDER BY pg_relation_size(indexrelid) DESC;

（PGlite 里 idx_scan 恒为 0，因为没有统计收集器——这条查询留给你在真实环境用。）

本节验收

先自己回答，再点开对照。

BRIN 为什么能小到 B-tree 的 1/275？这个优势的前提是什么，怎么判断前提是否成立？

因为 BRIN 不记录每一行，只记录每一段连续页面（默认 128 页）里的最小值和最大值。三十万行的时序表上，B-tree 6600 kB，BRIN 24 kB。查询时先用这些摘要整段整段地排除页面，剩下的段再逐行检查。

前提是数据在物理上按索引列有序存放。时序表天然满足——按时间追加，磁盘顺序就是时间顺序，每段的 min/max 区间很窄，排除效率极高。

判断方法是看 pg_stats.correlation：接近 ±1 说明物理顺序和值顺序高度一致，BRIN 有效；接近 0 就别用。实验里 ts.at 接近 1、ts.v 接近 0，所以只有 at 适合。

常见错误：以为「这张表是按时间顺序插进去的，所以永远物理有序」。物理顺序不是建表时定下的属性，它会被后续写入不断侵蚀——乱序回填历史数据、大量 UPDATE 把行搬到别的页面（UPDATE 从来不是原地修改），都会让 correlation 掉下来。而 BRIN 失效的方式特别隐蔽：它不报错、也不是慢一点点，而是一个页面段都排除不掉——索引还老老实实躺在执行计划里，实际等于全表扫。上线前跑一次 correlation 不够，它需要被持续监控。

GIN 的 fastupdate 解决了什么问题？又带来了什么问题？

解决 GIN 写入太贵：改一行意味着要更新这一行拆出的所有键的倒排链表。fastupdate（默认开启）让新条目先追加到一个无序的 pending list，攒够 gin_pending_list_limit（默认 4MB）再批量合并进主索引。

带来的问题是：pending list 里的条目在查询时必须线性扫描。所以一次大批量写入之后，紧接着的查询会莫名其妙地慢，直到合并完成。表现就是「平时很快的查询偶尔飙到几百毫秒」。对读延迟敏感的表可以 ALTER INDEX ... SET (fastupdate = off)。

常见错误：排查这类偶发的慢，反复重跑那条查询想复现，跑几次都很快，于是归因到网络抖动或缓存未命中。重跑是复现不出来的——等到重跑的时候 pending list 往往已经合并完了。它的触发条件很明确：大批量写入之后、合并完成之前的那个窗口。正确的排查方式是把慢查询的时间点和写入批次的时间线对齐，而不是在查询本身上打转。

为什么 LIKE '%abc%' 用不上 B-tree？trigram 索引怎么解决它？

B-tree 按整个字符串的字节序排列。LIKE 'abc%' 的前缀确定了它在这个有序结构里的位置区间，能定位；LIKE '%abc%' 里 abc 可以出现在字符串的任何位置，有序性提供不了任何定位能力，只能全表扫。

pg_trgm 换了个思路：把字符串拆成三字符片段（show_trgm('hello') 能看到拆法），用 GIN 倒排索引这些片段。查 '%number 1234%' 时把模式也拆成三元组，用倒排链表求出同时包含这些三元组的候选行，再对候选行做一次真正的 LIKE 判定。同一个索引还顺带支持相似度搜索（% 操作符和 similarity()），可以做拼写容错。

常见错误：既然 trigram 连 %abc% 都能索引，就干脆用它统一处理所有字符串查询，把前缀查询的 B-tree 也换掉。前缀匹配 B-tree 一次树定位就完事；trigram 要拆词、合并多个倒排链表、再逐行校验候选，还要额外承担 GIN 的写入代价和 pending list 那套麻烦。能用不等于该用——trigram 是为 B-tree 做不到的那部分准备的。

CREATE INDEX ON t (created_at::date) 为什么会失败？正确写法是什么？

因为索引表达式必须是 IMMUTABLE，而 timestamptz → date 的转换依赖会话时区，是 STABLE 而非 IMMUTABLE。索引里存的是计算结果，表达式的行为一变，索引里的每一项就都错了。

正确写法是把时区显式钉死：

CREATE INDEX ON t ((created_at AT TIME ZONE 'UTC')::date);

常见错误一：被拒绝之后，把转换包进一个自己写的函数、给它标上 IMMUTABLE，绕过检查。标记只是一句声明，它不改变函数的实际行为。 索引里存下来的仍然是建索引时那个时区下的结果，换个会话时区来查就会漏掉本该命中的行，或者查出错的行——而且全程不报任何错。这类静默损坏是最难排查的一种，因为一切看起来都在正常工作。

常见错误二：以为表达式索引会「智能匹配」等价写法。它要求一字不差：索引建在 lower(name) 上，查询就必须写 WHERE lower(name) = ...；写成 name ILIKE ... 或 upper(name) = ... 都用不上，优化器不会替你做代数变换。

列出选择索引类型的判断顺序。

等值、范围、排序、唯一约束 → B-tree。绝大多数情况到这里就结束了。
要查的是一个值内部的元素（数组包含、jsonb 键值、全文词、子串）→ GIN。
要查的是重叠、包含、距离 → GiST。
表非常大且物理有序，查询是宽范围过滤 → BRIN。
只有一部分行会被查 → 在上面任意一种基础上加 WHERE，变成部分索引。
查的是列的某种变换 → 表达式索引。

最后两条是修饰而不是新类型，可以叠加在前四条上。

常见错误：把这个顺序理解成「按列的数据类型选」——看到 jsonb 就 GIN，看到时间戳就 BRIN。前四条问的全是查询形状。同一个 jsonb 列，如果只按 data->>'tenant_id' 做等值查询，正确答案是在这个表达式上建 B-tree，GIN 又大又慢；同一个 timestamptz 列，宽范围过滤大表用 BRIN，点查用 B-tree。索引类型由「怎么查」决定，不由「存了什么」决定。