PostgreSQL 索引类型怎么选：B-tree/GIN/GiST/SP-GiST/BRIN
更新时间：2026-09-18。B+ 树为什么是关系库默认结构，见 MySQL 索引原理。PostgreSQL 比 MySQL 多了一整套「可扩展索引框架」，针对数组、JSONB、全文、向量、时序等不同数据形态提供不同访问方法。本篇用「数据长什么样、查询怎么比」反推该建哪种索引。JSONB 专题见 JSONB 存储与 GIN 索引性能。
本文要回答的问题
- 六类索引各自的数据结构和擅长的比较方式是什么？
- JSONB、数组、全文检索为什么选 GIN？
jsonb_path_ops和默认 GIN 差在哪？ - 空间数据、最近邻（KNN）、范围+几何为什么用 GiST？
- BRIN 为什么在时序大表上极小又极快？它的前提是什么？
- 复合/部分/表达式索引在 PostgreSQL 里怎么用才不踩坑？
一、六类索引速览：先按「比较方式」选型
| 索引类型 | 数据结构直觉 | 擅长的操作 | 典型场景 | 代价 |
|---|---|---|---|---|
| B-tree | 有序多路平衡树 | = < > BETWEEN IN、排序、前缀 LIKE | 绝大多数字段（默认） | 写入放大中等 |
| Hash | 哈希桶 | 仅 = | 基本不用（PG10 前不 WAL、不安全） | —— |
| GIN | 倒排索引（词→行集合） | 包含/重叠/全文：@> <@ @? @@ | JSONB、数组、全文检索、pg_trgm | 索引大、写放大高 |
| GiST | 平衡的「签名/边界」树 | 几何包含/距离、最近邻、范围类型 | 空间 PostGIS、排他约束、KNN | 有损、需 recheck |
| SP-GiST | 空间分区树（不均分） | 电话前缀、四叉树、非平衡分布 | 长前缀、地理网格 | 场景窄 |
| BRIN | 每段存最小/最大摘要 | 物理有序列上的范围扫描 | 时序、追加型大表 | 仅在数据物理有序时有效 |
一条核心判据：B-tree 回答「这个值在哪」，GIN 回答「哪些行包含这些元素」，GiST 回答「谁和谁相交/离得近」，BRIN 回答「这一段数据的值在不在目标范围」。
二、B-tree：默认选择与三个进阶用法
等值、范围、排序、唯一性，B-tree 全包。PostgreSQL B-tree 还能直接服务 ORDER BY（索引有序，避免排序）和 IS NULL。
-- 复合索引：遵循最左前缀，列顺序按 等值列在前 + 排序/范围列在后
CREATE INDEX idx_orders_user_time ON orders(user_id, created_at DESC);
-- 可服务：WHERE user_id=? ；... AND created_at>? ；ORDER BY created_at DESC
-- 部分索引：只索引满足条件的行，体积小、写放大低（热数据/软删场景神器）
CREATE INDEX idx_orders_open ON orders(created_at) WHERE status = 'open';
-- 表达式索引：必须在查询里“原样”写同一表达式才能命中
CREATE INDEX idx_users_lower_email ON users(lower(email));
SELECT * FROM users WHERE lower(email) = 'a@b.com'; -- 命中；WHERE email=... 不命中第一个坑：索引列上用函数/隐式类型转换会让 B-tree 失效。 WHERE date(created_at)=... 命不中 created_at 索引，要改成范围 created_at >= ... AND created_at < ...，或建对应的表达式索引。字符串与数字比较触发隐式 cast 同理。
三、GIN：倒排索引，JSONB/数组/全文的主力
GIN（Generalized Inverted Index）是倒排表：把每个「元素」（JSON 的 key/value、数组元素、全文 token）映射到包含它的行集合（bitmap）。查询包含关系时做集合合并，天然适合「一个字段里有很多可检索元素」。
-- JSONB：默认 GIN（支持 @> <@ ? ?| ?& 等绝大部分操作符）
CREATE INDEX idx_docs_data_gin ON docs USING gin (data);
-- jsonb_path_ops：只支持 @>/<@，但索引项更小、查询更快（对路径做哈希）
CREATE INDEX idx_docs_data_path ON docs USING gin (data jsonb_path_ops);
-- 数组包含/重叠
CREATE INDEX idx_tags ON articles USING gin (tags);
SELECT * FROM articles WHERE tags @> ARRAY['pg','gin']; -- 同时包含
SELECT * FROM articles WHERE tags && ARRAY['pg','mysql']; -- 任一包含
-- 全文检索
CREATE INDEX idx_fts ON articles USING gin (to_tsvector('simple', title||body));
SELECT * FROM articles WHERE to_tsvector('simple', title||body) @@ to_tsquery('pg & gin');第二个坑：GIN 索引大且写放大高。 一条含多个元素的文档要维护很多倒排项，批量 COPY/UPDATE 时写放大明显。两个缓解手段：① 用 GIN_FAST_UPDATE=on（默认开）先把增量写进小的 pending list，再 autovacuum 合并；② 大批量导入前先 DROP INDEX、导完再 CREATE INDEX，比边写边维护快得多。深入性能数据见 JSONB 存储与 GIN 索引性能。
-- 调 pending list（在内存/待合并列表积累多少后强制合并）
CREATE INDEX idx_docs_data_gin ON docs USING gin (data) WITH (gin_pending_list_limit = 2048);
-- 或全局调大，减少高频小合并
ALTER SYSTEM SET gin_pending_list_limit = '2MB';四、GiST：几何、范围类型与最近邻
GiST 是一棵「带边界信息」的平衡树，节点存的是子树的边界盒（bounding box）或签名，可能有损失（命中后要 recheck）。它擅长判断相交、包含、距离，并能用 <->（距离）直接做索引化的 ORDER BY KNN 最近邻：
-- PostGIS：空间 GiST
CREATE INDEX idx_geo_geom ON geo USING gist (geom);
SELECT id FROM geo
WHERE ST_DWithin(geom, ST_MakePoint(116.4,39.9)::geography, 1000);
-- KNN：离某点最近的 10 个（配合 LIMIT 才能走索引 KNN）
SELECT id, geom <-> ST_MakePoint(116.4,39.9) AS dist
FROM geo ORDER BY geom <-> ST_MakePoint(116.4,39.9) LIMIT 10;
-- 排他约束（btree_gist 后可用）：防止同一房间时间段重叠
ALTER TABLE booking ADD CONSTRAINT excl_room_time
EXCLUDE USING gist (room_id WITH =, tstzrange(start_at,end_at) WITH &&);第三个坑：KNN 必须 ORDER BY 距离 + LIMIT 才走索引。 只写 WHERE dist < x 而没有距离排序，优化器可能退化成普通过滤；几何函数能否用索引取决于有没有对应的 GiST 操作符族（opclass），自定义类型要先装扩展。
五、BRIN：物理有序时序大表的「极简索引」
BRIN（Block Range INdex）不为每行建项，而是每一段（默认每 128 个页，约 1MB）只记该段的最小值/最大值（摘要）。查询范围条件时，先看哪些段的 min~max 与目标范围不相交，直接跳过整段。
-- 时序表：created_at 与物理写入顺序强相关
CREATE INDEX idx_events_time_brin ON events USING brin (created_at)
WITH (pages_per_range = 32, autosummarize = on);
SELECT * FROM events
WHERE created_at >= '2026-09-18 00:00' AND created_at < '2026-09-18 01:00';| 对比 | B-tree | BRIN |
|---|
六、怎么知道索引有没有被用：先看统计再决定删
-- 各索引的使用次数（seq_scan 高、idx_scan 长期为 0 的索引是浪费）
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- 未使用索引（占用写放大与空间，考虑下线）
SELECT schemaname, relname, indexrelname
FROM pg_stat_user_indexes ui
JOIN pg_index i ON i.indexrelid = ui.indexrelid
WHERE idx_scan = 0 AND NOT indisunique;删冗余索引能显著降低写放大，尤其对 GIN 这类重型索引收益明显，但删除前要确认统计不是刚重置（pg_stat_reset()），并跨过一个完整业务周期。
七、一张决策表收尾
| 你的查询/数据 | 选什么 | 备注 |
|---|---|---|
| 主键、外键、等值+范围+排序 | B-tree | 默认，唯一约束也靠它 |
| 只查等值且数据类型不支持 btree | Hash | 一般仍优先 btree |
| JSONB 包含查询 | GIN（jsonb_ops/jsonb_path_ops） | 只查包含用 path_ops 更小 |
| 标签数组、全文检索、相似搜索 | GIN（pg_trgm/tsvector） | 注意写放大与批量导入 |
| 地理位置、范围相交、排他约束、KNN | GiST | 有损需 recheck |
| 前缀/电话号、空间分区不均 | SP-GiST | 场景窄但点查很省 |
| 时序/日志等物理有序追加表 | BRIN | 先确认 correlation≈±1 |
| 只查少数状态行 | B-tree 部分索引 | 体积小、维护便宜 |
八、相关与延伸
B+ 树通用原理见 MySQL 索引原理；GIN 在半结构化数据上的实战与压测见 PostgreSQL JSONB 存储与 GIN 索引性能；执行计划里如何确认走了哪种索引见 EXPLAIN (ANALYZE, BUFFERS)；索引与 MVCC/HOT、膨胀的联动见 架构与 MVCC 与 VACUUM 与表膨胀治理。
总结
PostgreSQL 索引类型：等值/范围/排序用 B-tree，JSONB/数组/全文这类「包含多元素」用 GIN（批量导入先删后建、只查包含用 jsonb_path_ops），几何相交/排他约束/KNN 最近邻用 GiST，物理有序的时序大表用 BRIN（先看 correlation≈±1，索引极小只做段裁剪）；配合部分索引、表达式索引缩小体积，并用 pg_stat_user_indexes 识别零命中索引，按「比较方式」而不是「字段类型」选型。
| 索引体积 | 可达表的 10~30% | 通常只有表的 0.01%~1% | | 前提 | 无 | 列值与物理存储顺序强相关 | | 擅长 | 任意等值/范围、高基数点查 | 大范围顺序扫描裁剪 | | 维护 | 每条 DML 维护 | 靠 summarizer 对新区间建摘要 |
第四个坑：BRIN 的命门是物理相关性。 若该列在磁盘上是乱序写入（相关性 corr 接近 0），每段 min~max 都覆盖很宽，裁剪不掉任何段，BRIN 形同虚设。先查相关性：
-- 接近 1 或 -1 才适合 BRIN；接近 0 请用 B-tree
SELECT attname, correlation FROM pg_stats
WHERE tablename='events' AND attname IN ('created_at','user_id');