索引类型大全 · 六种索引怎么选 · PostgreSQL 特性详解

PostgreSQL 内置 6 种索引——B-tree、Hash、GIN、GiST、SP-GiST、BRIN，各擅长不同的访问模式。再加上部分索引、表达式索引、覆盖索引这些「修饰」。选对索引类型，是让大表查得快的第一步；BRIN 在时序大表上能小到只有 B-tree 的几分之一。

索引类型大全 · 六种索引怎么选

大多数数据库，「索引」基本等于「B-tree」。PostgreSQL 不一样：它内置 6 种索引，每一种针对不同的数据形状与访问模式。选对类型，同样的查询可能快几十倍、索引还小几倍。这一章把六种索引 + 三种「索引修饰」讲清楚，并给出选择的判断标准。

六种索引，各管一摊

索引	擅长	一句话原理
B-tree	`=` `<` `>` `BETWEEN` `ORDER BY`	默认；有序平衡树，范围与排序天然友好
Hash	只做 `=` 等值	哈希桶；PG 10 起写 WAL 后才生产可用
GIN	数组 / JSONB / 全文检索等多值	倒排索引：值 → 含它的行
GiST	几何 / 范围 / 最近邻 / 自定义类型	可扩展的平衡树，存的是「谓词」
SP-GiST	非均匀分布（前缀树 / 四叉树）	空间分区、非平衡结构
BRIN	物理顺序≈值顺序的超大表	每个块区间只记 min/max，索引极小

B-tree 是默认，90% 的场景够用。真正拉开差距的，是知道另外五种什么时候该上。

B-tree：默认，但别小看

有序、平衡、支持范围与排序——= < > BETWEEN ORDER BY IS NULL 全走它。它是 Lehman & Yao 那套高并发 B-tree 的工程实现，几乎所有主键、外键、常规查询列都靠它。你不指定索引类型时，建的就是 B-tree。

前面数组、JSONB、全文检索三章的加速，靠的都是 GIN。它是倒排索引：不为「整行」建一条目，而是为字段里的每个元素 / 键 / 词元建「元素 → 行列表」的映射。所以 tags @> ARRAY['eng']、doc @> '...'、tsv @@ '...' 这类「含某值」的查询，直接查元素对应的行列表，不必全表扫。凡是一列里装了多个可检索单元（数组、JSONB、tsvector），就是 GIN 的地盘。

GiST / SP-GiST：空间、范围、最近邻

GiST 是一棵可扩展的平衡树，节点里存的是「谓词」（比如「这棵子树的所有几何图形都落在这个框里」）。它是 PostGIS 地理索引、范围类型 EXCLUDE 约束（见范围与生成列）、以及「最近邻」查询（ORDER BY point <-> target LIMIT 5，找最近的 5 个）的底座。

SP-GiST(Space-Partitioned GiST)是它的非平衡版，适合天然分区、分布不均的数据：前缀树（文本 / IP）、四叉树（点）、k-d 树。数据分布越不均匀，它相对 GiST 越有优势。

BRIN：时序大表的「省钱利器」

BRIN(Block Range Index)是最该认识的一个「非主流」索引。它不为每一行建条目，而是把表按物理块分成一段段，每段只记这段里的 min/max。查 WHERE ts BETWEEN ... 时，它快速跳过「min/max 完全不沾边」的块区间。

它的杀手锏是小。看实测里同一张日志表上各索引的物理大小：

600 kB  logs_payload_gin           (JSONB GIN)
216 kB  logs_level_time_covering   (覆盖索引)
128 kB  logs_occurred_btree        (时间列 B-tree)
 40 kB  logs_errors_only           (部分索引)
 24 kB  logs_occurred_brin         (时间列 BRIN)   ← 同一列,只有 B-tree 的 1/5
616 kB  logs                       (表本体)

同样是给 occurred_at 列建索引，BRIN 只有 B-tree 的约 1/5，数据量越大差距越夸张（几十 GB 的表可能是几百倍）。

BRIN 生效有个前提：物理存储顺序要和列值顺序大致相关。时间序列天然满足——日志按时间追加，磁盘上早的在前、晚的在后。这时 BRIN 用极小的体积换来「跳过大片无关块」的能力。但如果数据是乱序插入的（比如按随机 UUID），BRIN 就几乎没用——每个块的 min/max 都横跨全域，跳不掉任何块。时序、单调追加的大表用 BRIN；乱序数据别用。

三种「索引修饰」：同样的类型，更省更快

索引类型之外，还有几种正交的「修饰」，常常比换类型更立竿见影：

① 部分索引(Partial) —— 只索引满足条件的行：

-- 只给 error 日志建索引:99% 的 info 日志根本不进索引
CREATE INDEX logs_errors_only ON logs (occurred_at) WHERE level = 'error';

实测里它只有 40 kB（全量 B-tree 是 128 kB）。当你总是只查一小部分行（未处理的任务、错误日志、未删除的记录），部分索引又小又快，是性价比极高的一招。

② 表达式索引(Expression) —— 给「函数的结果」建索引：

CREATE INDEX logs_msg_lower ON logs (lower(message));
-- 于是 WHERE lower(message) = '...' 能走索引(大小写不敏感查询)

③ 覆盖索引(Covering，INCLUDE) —— 把查询要读的列一起塞进索引，实现索引-only 扫描（不用再回表）：

CREATE INDEX logs_covering ON logs (level, occurred_at) INCLUDE (user_id);
-- 查 level+时间排序、只要 user_id 时,全程读索引,不碰表

「回表」是性能的隐形杀手：普通索引扫描找到匹配行的位置后，还要去堆表(heap) 把整行捞出来读需要的列。覆盖索引把那几列直接放进索引页，于是「索引-only 扫描」全程不碰堆表，I/O 少一大截。代价是索引更大、写入更慢——用在读多、列少的热点查询上最划算。

实测最后验证了部分索引真的被选中：查「error 日志按时间倒序取 10 条」，planner 走的正是那个 40 kB 的 logs_errors_only——Index Scan Backward using logs_errors_only。

横向对比

能力	PostgreSQL	MySQL(InnoDB)	Oracle	SQL Server
B-tree	有	有（主力）	有	有
倒排（多值）	GIN	有限（多值索引 8.0+）	域索引	全文专用
空间	GiST / SP-GiST	R-tree（空间）	R-tree	空间索引
块范围	BRIN	❌ 无	有（存储索引，商业版特性）	Columnstore（不同思路）
部分索引	有	❌ 无	有（基于函数变通）	有（筛选索引）
表达式索引	有	8.0.13+	有	计算列 + 索引
覆盖索引 `INCLUDE`	有	靠联合索引	有	有

PostgreSQL 的索引「工具箱」是这几家里最全的：BRIN、部分索引、GIN 的多值倒排是 MySQL 明显缺的三块，分别对应时序大表、稀疏查询、数组/JSON/全文——都是实打实能省一个量级资源的场景。

应用场景

时序 / 日志大表：主查询列用 BRIN（省几十倍空间），高频精确过滤列再叠一个部分索引。
状态机 / 任务队列：WHERE status='pending' 的部分索引，只索引待处理的少数行。
大小写 / 格式不敏感查询：lower(email)、(data->>'city') 用表达式索引。
热点只读查询：高频的 SELECT a,b WHERE c=? 用覆盖索引做索引-only 扫描。
数组 / JSONB / 全文：一律 GIN。

小结与下一章

「加索引」不该只是无脑 CREATE INDEX。选对类型(B-tree / GIN / BRIN / GiST)匹配数据形状，再叠上修饰（部分 / 表达式 / 覆盖）贴合查询模式，才是让大表查得快又不浪费空间的正解。PostgreSQL 给的这套工具箱，比大多数数据库都全。

索引让「查得快」，但当单表大到几亿行，光靠索引也扛不住——这时要把大表物理拆小。下一章看声明式分区。👉 表分区：把一张大表拆成很多小表。

参考文献

[1] PostgreSQL 官方文档.「Chapter 11. Indexes.」https://www.postgresql.org/docs/current/indexes.html —— 索引类型、部分 / 表达式 / 覆盖索引、索引-only 扫描。
[2] PostgreSQL 官方文档.「11.2. Index Types.」https://www.postgresql.org/docs/current/indexes-types.html —— 六种索引类型对照。
[3] PostgreSQL 官方文档.「64.5. BRIN Indexes.」https://www.postgresql.org/docs/current/brin.html —— BRIN 的块范围原理与适用前提。

索引类型大全 · 六种索引怎么选