引言

从 MySQL 转 PostgreSQL 的团队，第一个月往往觉得"索引嘛，还不都是 B+ 树那一套"，直到在 SQL 里写下 WHERE tags @> ARRAY['新品'] 和 WHERE detail @> '{"city":"上海"}' 才发现：B-tree 对数组、JSONB、全文检索、几何范围这些条件根本无能为力——你可以给数组列建 B-tree，但它只索引整个数组字面值，永远不会为"数组包含某个元素"这类查询服务。

PostgreSQL 与 MySQL 在索引上最大的不同，是它把索引框架做成了可扩展的：不同的数据形态和查询模式，配不同物理结构的索引。这篇文章讲清生产中最常用的四类——B-tree（默认主力）、GIN（倒排，JSONB/数组/全文检索）、GiST（几何/模糊/最近邻）、BRIN（超大有序表的极简索引），每个都给建索引语句、匹配的查询操作符、EXPLAIN 验证方法，以及体积/写入代价的实测量级。选错索引类型时"明明建了索引却走全表扫"的困惑，看完这篇就没了。

一、先有全局观：四种索引解决四种不同的问题

选型第一直觉：

标量列的 = / BETWEEN / ORDER BY          → B-tree（90% 的日常索引）
列里"装着多个值"，查询问"包不包含"       → GIN（jsonb、数组、全文）
问"相交吗/多远/最近的是谁"               → GiST（地理、范围类型、相似度）
亿级超大表、列值天然按物理顺序排列        → BRIN（时序、日志、自增）

核心心法：索引不是给列建的，是给"查询谓词的形态"建的。先写清楚 WHERE 长什么样，再决定索引类型。

二、B-tree：被小看的默认主力（它不只是 MySQL B+ 树的翻版）

2.1 适用场景

CREATE INDEX idx_order_user_time ON t_order(user_id, created_at DESC);

• 等值 =、IN、范围 > < BETWEEN、IS NULL、ORDER BY 排序、DISTINCT、合并连接；
• 多列复合索引遵循最左前缀；PG 还支持 IN 条件下的 index skip scan 变体（PG 16+ 增强较多）。

2.2 PG B-tree 的两个特色用法

表达式索引（对函数计算结果建索引，解决"对列用函数导致索引失效"）：

-- 需求：按手机号后四位查用户；LOWER 邮箱不区分大小写登录
CREATE INDEX idx_user_phone4 ON t_user(right(mobile, 4));
CREATE INDEX idx_user_email_lower ON t_user(lower(email));

SELECT * FROM t_user WHERE lower(email) = lower('Zhang@Example.com');  -- 走索引

部分索引（带 WHERE 的索引，只索引感兴趣的行，体积小、选择性高）：

-- 90% 的订单状态是已完成，业务只频繁查"待处理"订单
CREATE INDEX idx_order_pending ON t_order(created_at)
WHERE status IN ('PENDING', 'PAID');

-- 只给未软删的数据建索引
CREATE INDEX idx_user_name_alive ON t_user(name) WHERE deleted = false;

这两个是 MySQL 没有（或支持很弱）的能力，迁移过来的团队尤其值得用起来。

2.3 B-tree 管不了的形态

WHERE tags @> ARRAY['新品']            -- 数组包含：B-tree 无效
WHERE detail @> '{"city":"上海"}'::jsonb  -- JSONB 包含：B-tree 无效
WHERE to_tsvector(body) @@ to_tsquery('数据库 & 调优')  -- 全文：B-tree 无效
WHERE location <-> ST_MakePoint(121.47,31.23) < 3000    -- 距离：B-tree 无效

下面三种索引依次登场。

三、GIN：倒排索引——给"一个格子装多个值"的列用

3.1 原理：与 B-tree 方向相反的索引

B-tree：行 → 值（从行出发找它的值）
GIN（Generalized Inverted Index，倒排）：值 → 行列表

tags 列：
  行1 {新品, 数码, 促销}
  行2 {数码, 手机}
  行3 {新品, 食品}

GIN 倒排表：
  "新品" → [行1, 行3]
  "数码" → [行1, 行2]
  "促销" → [行1]
  ...
查询 tags @> {新品}：直接取"新品"的行列表 → [行1, 行3]，无需扫描

这与搜索引擎的倒排索引是同一思想：全文检索里"词 → 包含词的文档"，数组/JSONB 里"元素/键值 → 包含它的行"。

3.2 场景一：商品标签数组查询

CREATE TABLE product (
    id    BIGINT PRIMARY KEY,
    name  TEXT,
    tags  TEXT[] NOT NULL DEFAULT '{}'
);

-- 建 GIN 索引
CREATE INDEX idx_product_tags_gin ON product USING gin (tags);

-- 包含全部标签（@>）：同时含"新品"和"数码"
SELECT id, name FROM product
WHERE tags @> ARRAY['新品', '数码'];

-- 包含任意一个（&&）
SELECT id, name FROM product
WHERE tags && ARRAY['清仓', '促销'];

-- 是否包含某个键（数组对应 @>，JSONB 用 ?）
SELECT count(*) FROM product WHERE tags @> ARRAY['爆款'];

EXPLAIN 验证：

Bitmap Heap Scan on product
   Recheck Cond: (tags @> '{新品,数码}'::text[])
   -> Bitmap Index Scan on idx_product_tags_gin   ← 走了 GIN，成本骤降

3.3 场景二：JSONB 任意键查询（配合上一篇工具链里的 JSONB）

CREATE TABLE orders (id BIGINT PRIMARY KEY, detail JSONB NOT NULL);

-- 整个 JSONB 建 GIN（jsonb_ops，默认）：任意路径的 @> ? ?| 都能用
CREATE INDEX idx_orders_detail_gin ON orders USING gin (detail);

SELECT * FROM orders WHERE detail @> '{"address":{"city":"上海"}}';
SELECT * FROM orders WHERE detail ? 'couponCode';

-- 只按固定表达式查询时，用 jsonb_path_ops：索引更小更快，但只支持 @>
CREATE INDEX idx_orders_detail_path ON orders
    USING gin (detail jsonb_path_ops);

经验：查询模式固定用 path_ops；需要存在性判断（?）或键不可预测用默认 ops。

3.4 场景三：全文检索 GIN + tsvector

CREATE TABLE article (
    id    BIGINT PRIMARY KEY,
    title TEXT,
    body  TEXT
);

-- 英文：直接对 tsvector 建 GIN
CREATE INDEX idx_article_fts ON article
    USING gin (to_tsvector('english', title || ' ' || body));

-- 中文：先装 zhparser 扩展，创建中文配置后使用
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese_zh (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese_zh ADD MAPPING FOR n,v,a,i,e,l WITH simple;

CREATE INDEX idx_article_fts_zh ON article
    USING gin (to_tsvector('chinese_zh', title || ' ' || body));

-- 查询：包含"数据库"与"调优"两个词
SELECT id, title,
       ts_rank(to_tsvector('chinese_zh', title || ' ' || body),
               to_tsquery('chinese_zh', '数据库 & 调优')) AS rank
FROM article
WHERE to_tsvector('chinese_zh', title || ' ' || body)
      @@ to_tsquery('chinese_zh', '数据库 & 调优')
ORDER BY rank DESC
LIMIT 20;

GIN 的位图天然适合全文检索的"多词组合"（& 与 / | 或 / ! 非）——把词对应的行列表做位运算即可。

3.5 GIN 的代价与 fastupdate 参数

GIN 不是免费的：

-- 写多读少/批量导入场景显式开启缓冲；读延迟极敏感可关闭
CREATE INDEX idx_product_tags_gin ON product USING gin (tags)
    WITH (fastupdate = on);

-- 大批量灌数据时可以先 DROP 索引、灌完再重建（比带索引插入快数倍）

待办列表（pending list）过大时查询反而变慢，autovacuum 会负责合并——GIN 表不要关 autovacuum，高频写入表适当调低 autovacuum 参数。

四、GiST：平衡通用树——几何、范围、相似度最近邻

4.1 原理：节点不存精确键，存"概括信息（signature）"

GIN 是倒排，GiST（Generalized Search Tree）是一棵平衡树，但节点里存的不是等值键，而是谓词/边界——例如二维矩形的最小外接矩形（bounding box）、范围类型的区间。查询时用"查询条件与节点的概括信息是否可能相交"来剪枝：不可能相交的整棵子树跳过，可能相交的继续向下。这让它能支持 B-tree 表达不了的算子：

操作符类：
  btree_gist     —— 让标量/区间类型也能进 GiST（排他约束必备）
  PostGIS geometry/geography —— 二维空间（&&、ST_DWithin、<->）
  pg_trgm        —— 三元组相似度（ILIKE、相似度排序）
  range 类型     —— 区间包含/相交（@>、&&）

4.2 场景一：地理位置（PostGIS）——附近的门店

CREATE EXTENSION postgis;

CREATE TABLE store (
    id       BIGINT PRIMARY KEY,
    name     TEXT,
    location GEOMETRY(Point, 4326)
);

-- GiST 空间索引
CREATE INDEX idx_store_gist ON store USING gist (location);

-- 3 公里内门店，按距离排序（KNN 最近邻算子 <-> 直接用索引排序）
SELECT id, name,
       ST_DistanceSphere(location, ST_MakePoint(121.47, 31.23)) AS dist_m
FROM store
WHERE ST_DWithin(location::geography,
                 ST_MakePoint(121.47, 31.23)::geography, 3000)
ORDER BY location <-> ST_MakePoint(121.47, 31.23)
LIMIT 10;

PostGIS 也支持 GIN 地理索引，但 GiST 在空间查询上更通用且支持 <-> KNN 距离排序，是 PostGIS 的默认推荐。

4.3 场景二：模糊匹配（pg_trgm + GiST，比 ILIKE '%xx%' 快几个数量级）

CREATE EXTENSION pg_trgm;

-- trigram：把字符串切成连续三字符集合，'postgres' → {pos, ost, stg, tgr, gre, res}
CREATE INDEX idx_company_name_trgm ON company USING gist (name gist_trgm_ops);
-- 等价的 GIN 写法（读更快、写更重、体积更大）：USING gin (name gin_trgm_ops)

-- 前后都带 % 的模糊查（普通 B-tree 完全无法支持）
SELECT * FROM company WHERE name ILIKE '%有限责任公司%';

-- 相似度查询：拼错字也能召回（similarity 阈值默认 0.3）
SELECT name, similarity(name, '华东数技') AS sim
FROM company
WHERE name % '华东数技'
ORDER BY name <-> '华东数技'    -- <-> 是 trigram 距离，越小越相似
LIMIT 10;

GIN 与 GiST 选谁做 trigram：读多写少选 GIN（gin_trgm_ops 查询快）；写入频繁、想要更小索引选 GiST。

4.4 场景三：区间排他约束（GiST 的独门绝技）

CREATE EXTENSION btree_gist;

-- 会议室预订：同一房间的时间段不允许重叠（数据库层强制，不靠应用加锁）
CREATE TABLE room_booking (
    room_id  BIGINT,
    during   TSTZRANGE,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

INSERT INTO room_booking VALUES
    (1, tstzrange('2026-09-19 10:00+08', '2026-09-19 11:00+08'));
-- 再插重叠时段 → 直接报错拒绝

这种"用索引保证数据约束"的能力是 GiST 独有的（排他约束 EXCLUDE），排班、库存锁定、IP 段去重都适用。

4.5 GIN vs GiST 怎么选（同时支持某类型时）

五、BRIN：亿级时序大表的"极简主义"索引

5.1 原理：只存块摘要，小到忽略不计

B-tree/GIN 都为每行建立索引条目，亿级表上索引本身可能几十 GB。BRIN（Block Range Index）思路完全不同：数据在磁盘上按块（128 个页一个 range，默认 1MB）组织，BRIN 只为每个块范围存一行摘要（min/max，有些类型还有 null 计数）：

数据（物理顺序按时间追加）：
  块1:  ts 00:00:00 ~ 00:01:39 ...
  块2:  ts 00:01:40 ~ 00:03:19 ...
  ...
BRIN 索引（每个块一行）：
  range 1: min=00:00:00 max=00:01:39
  range 2: min=00:01:40 max=00:03:19
  ...
查 12:00 的数据：摘要不覆盖 12:00 的块整块跳过，只读少数块

5.2 场景：物联网设备上报时序表

CREATE TABLE iot_metric (
    ts        TIMESTAMPTZ NOT NULL,
    device_id BIGINT NOT NULL,
    value     DOUBLE PRECISION
);

-- 按时间追加写入，物理顺序天然与 ts 相关
-- BRIN 索引
CREATE INDEX idx_iot_ts_brin ON iot_metric USING brin (ts)
    WITH (pages_per_range = 128);

SELECT * FROM iot_metric
WHERE ts BETWEEN '2026-09-19 12:00+08' AND '2026-09-19 12:05+08'
  AND device_id = 1001;

5.3 体积与效果的惊人差距

某客户实测（2.4 亿行设备数据，约 38GB）：

BRIN 慢一点点（要回表确认行），但体积差三个数量级、写入几乎零成本——时序归档表非常划算。

5.4 使用前提（不满足则 BRIN 完全无效）

1. 列的物理相关性必须高（pg_stats.correlation 接近 1 或 -1）：

-- 查看列值与物理顺序的相关性
SELECT attname, correlation FROM pg_stats
WHERE tablename = 'iot_metric' ORDER BY correlation DESC;
-- ts 列 correlation ≈ 1.0 → BRIN 完美
-- 随机写入/频繁更新导致相关性 ≈ 0 → BRIN 摘要全是宽区间，剪不掉任何块，退化为全表扫

2. 数据以追加为主（日志、事件、时序、自增 ID）；频繁 UPDATE 导致行迁移会破坏相关性。
3. 查询是大范围过滤（命中少量块），点查仍优先 B-tree。
4. 数据乱序写入后可用 VACUUM FULL/CLUSTER 重排恢复相关性，成本高，靠"只追加"模式预防最划算。

六、实战决策：四个业务场景的索引方案总览

注意可以组合：时序表上 BRIN(ts) 负责时间大范围 + B-tree(device_id, ts) 负责设备维度查询，各取所长。

七、索引建好后必做的两件事

7.1 用 EXPLAIN 确认真的走了

EXPLAIN (ANALYZE, BUFFERS)
SELECT ... ;

常见不生效原因：① GIN 查询用了索引不支持的操作符（如 path_ops 用 ?）；② BRIN 列相关性差；③ GiST 忘装扩展导致操作符类不存在；④ 函数表达式与索引表达式不完全一致。

7.2 建索引不要锁表

-- 生产表建索引用 CONCURRENTLY：不阻塞写入（代价是构建慢、不能在事务块里执行）
CREATE INDEX CONCURRENTLY idx_product_tags_gin ON product USING gin (tags);
-- 失败后可能留下 invalid 索引，需要 DROP INDEX 后重建

灌数据阶段则反过来：先 DROP 索引、批量 COPY、再统一重建，总时间最短。

八、常见问题

8.1 GIN 和 GiST 都能做全文/trigram，到底怎么选？

记住一句话：GIN 用空间和写入换查询速度，GiST 用一点点查询精度换空间和写入友好。只读/读多写少的搜索、报表库选 GIN；写入持续不断、索引体积敏感、或需要 KNN 距离排序和排他约束选 GiST。GIN 查询不需要 recheck（倒排精确），GiST 可能带 Recheck Cond（节点摘要有损失，回表复核）。

8.2 JSONB 查询能不能用 B-tree？

只能对"固定路径提取出的标量值"用 B-tree 表达式索引：((detail->>'userId')::bigint) 适合高频固定路径；任意键、深层嵌套、存在性查询才需要 GIN。建议：固定 2~3 个高频路径用 B-tree 表达式索引（小而快），兜底的灵活查询再加一个 GIN，别一上来给整个 JSONB 建大 GIN。

8.3 BRIN 这么省，为什么不所有大表都建？

因为 BRIN 的剪枝能力 100% 依赖数据物理顺序与列值的相关性（correlation）。只有追加型/聚簇型数据满足；普通业务表按主键插入但查询列是更新频繁的状态列，相关性接近 0，BRIN 摘要每个块都是全域 min~max，一个块都剪不掉，纯浪费。建之前一定查 pg_stats.correlation。

8.4 一张表建很多 GIN 会不会有问题？

会。GIN 写入代价高，每多一个 GIN，INSERT/UPDATE 就要多维护一棵倒排树；同时 GIN 依赖 vacuum 合并 pending list，vacuum 不及时会出现"索引突然变慢"。原则：GIN 只建给真正需要包含/全文语义的列，写入热表上控制数量（经验上不超过 2~3 个），批量写走"先删后建"。

8.5 GiST 的 Recheck Cond 是索引不准吗？需要管吗？

不是不准，是 GiST 的有损特性：节点存的是概括信息，索引层只能判定"可能满足"，所以拿回候选行后要按原条件再过滤一遍（Recheck Cond）。这是正常执行流程，不需要干预；如果发现 recheck 过滤掉的行特别多（Index Scan 返回行远多于最终行），通常是数据分布导致摘要区分度差，考虑换 GIN 或调整 fillfactor。

8.6 向量检索（pgvector 的 HNSW/IVFFlat）算这四类里的哪种？

都不算，它是第五类：ANN 近似最近邻索引（HNSW 是多层近邻图，IVFFlat 是聚类倒排），服务的是高维 embedding 的 <=> 距离搜索。选型口诀可以更新为：标量 B-tree、多值包含 GIN、几何/相似 GiST、有序大表 BRIN、高维向量 HNSW——pgvector 篇里有详细参数（m、ef_construction、ef_search）和走索引验证方法。

九、总结

索引选型速查卡

先问：WHERE 谓词长什么样？
  = / BETWEEN / ORDER BY / 复合最左前缀      → B-tree（+表达式/部分索引）
  列内含多值：@>  &&  ?  @@（数组/JSONB/全文）→ GIN（读快、体积大、写重）
  相交/包含/距离最近：几何、区间、trigram      → GiST（+排他约束 EXCLUDE）
  超大追加表按物理有序列做大范围过滤           → BRIN（先查 correlation≈±1）
  高维 embedding 余弦距离                     → HNSW/IVFFlat（pgvector）
验证：EXPLAIN (ANALYZE, BUFFERS) 看到对应 Index/Bitmap Scan
上线：CREATE INDEX CONCURRENTLY；批量导入先删后建

一句话

PostgreSQL 高级索引的精髓只有一句话：索引是为查询谓词的形态服务的，不是为列服务的。B-tree 是默认主力，管 90% 的等值、范围、排序场景，再配上 PG 独有的表达式索引和部分索引解决函数包裹和低基数过滤；但当 WHERE 问的是"这个数组/JSONB 里包不包含某个值"时，B-tree 从物理上就无法回答，需要 GIN 倒排索引——它把"值→行列表"的映射建好，商品标签的 @>、&&，订单快照 JSONB 的 @>、?，文章 tsvector 的全文词组布尔匹配，全部变成一次倒排表查找，代价是索引体积大、写入放大重，所以要用 jsonb_path_ops 控制体积、用 fastupdate 和 autovacuum 扛写入、批量导入先删索引后重建；当问题变成"两个几何对象相交吗、最近的是谁、两个时间段重不重叠"时，轮到 GiST——节点存包围盒和签名做剪枝，PostGIS 的 KNN 距离排序、pg_trgm 的前后模糊与错别字召回、btree_gist 的会议室时段排他约束都是它的独门场景，GIN 与 GiST 同时可用时按"读多选 GIN、写多选 GiST、要距离和排他选 GiST"决策；而面对几十 GB、百亿行的物联网时序表，B-tree 自身几个 GB 的体积已经成为存储和写入负担，BRIN 反其道而行，只记录每个数据块的 min/max 摘要，用约千分之一的体积（38GB 表上 6.8MB 对 5.2GB）让按时间追加的数据在范围查询时跳过绝大多数块——但它的唯一命门是列值与物理顺序的相关性，pg_stats.correlation 不接近 ±1 时 BRIN 立刻退化成全表扫。落地只记两个动作：所有索引用 EXPLAIN (ANALYZE, BUFFERS) 验证真实命中（Bitmap Index Scan 对 GIN、Recheck Cond 对 GiST），生产建索引一律 CONCURRENTLY 不阻塞写入。选对类型的收益常常是数量级的：一个 GIN 让 JSONB 查询从全表 8 秒到 20 毫秒，一个 BRIN 用 7MB 替代 5GB 索引——前提是你先想清楚查询的形状，而不是习惯性地给每列都建一个 B-tree。

给团队的建议

互动话题：你们在 PG 里用过最"救命"的索引是哪种？用 BRIN 省下过多少磁盘空间，或者 GIN 让哪类查询起死回生？评论区聊聊。

参考资料

• PostgreSQL 官方文档：索引类型（B-tree/Hash/GiST/GIN/BRIN/SP-GiST）
• PostgreSQL 文档：GIN 索引与 fastupdate/pending list
• PostgreSQL 文档：GiST 索引
• PostgreSQL 文档：BRIN 索引与 pages_per_range
• pg_trgm 官方文档（三元组相似度）
• PostGIS 官方文档（Using PostGIS Indexes）