索引设计实战：pgvector HNSW × JSONB GIN × 复合索引

在 70 张表的 PostgreSQL 上做索引选型，三类问题会同时压过来：向量召回怎么加速？JSONB 字段怎么查？账期范围怎么锁？本文以一处生产系统（70 张业务表 + pgvector + JSONB）的真实索引取舍为例，把 HNSW、JSONB GIN、B-Tree 复合索引三类索引的"选 / 不选 / 怎么建"一次讲透。

一、为什么这篇文章不是"索引教程"

DBA 群里常有人问：「我们的向量字段要不要上 HNSW？」——问得对，但问错了阶段。

向量召回只是索引决策的一个分支，真正的决策树是这样的：

问：你的热点查询是什么？
├── Top-K 相似度匹配        →  HNSW / IVFFlat（pgvector）
├── JSONB 子树包含 / 路径    →  GIN（含 jsonb_path_ops / tsvector）
└── 等值 + 范围 + 排序      →  B-Tree 复合索引

三个分支没有先后，但工程取舍完全不同：

• HNSW：写放大（每条 INSERT 都更新多层图）、建索引贵、内存吃得多，但 P95 < 10ms
• GIN on JSONB：写入开销中等、对 JSONB 结构依赖强，仅对路径表达式最优
• B-Tree 复合索引：最朴素，但列序决定生死——选错前缀等于没建

更麻烦的是：它们不能用同一个工具管。HNSW 没有 Prisma schema 表达，GIN 写不进 @@index，复合索引又要靠业务查询反推列序。下面用一处生产环境的真实决策，把三类索引的取舍一次讲透。

二、HNSW 实战：从"必建"到"暂缓"的一段真实决策

2.1 一段反向决策的来龙去脉

PG 17 + pgvector 0.7 之后，HNSW 已经是默认推荐选项。一份典型的迁移 SQL 大概长这样：

CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE knowledge_chunks
    ADD COLUMN embedding vector(1536);

CREATE INDEX knowledge_chunks_embedding_hnsw
    ON knowledge_chunks USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

迁移落库后，仓库里这段代码却被刻意从迁移历史中拿掉了——不是「忘了」，是主动决定。

[来源：apps/api/prisma/migrations/20260725000000_add_pgvector_knowledge/migration.sql 头注（2026-08-16 决策）]

原文（节选）： "hnsw 向量索引不在迁移历史中创建（2026-08-16 移除）：Prisma schema 无法表达 hnsw，留在迁移里会造成永久的 migrations↔schema 漂移（migrate dev 新环境必提示建迁移）。线上库已存在该索引、不受影响；新环境数据量小，向量检索走精确扫描即可。"

这是反直觉干货：HNSW 不是「必建」，而是「看你有多少数据 + 多在意延迟」。

[来源：轻易云生产环境实测（2026-08-16）]

2.2 真实查询：召回 SQL 怎么写才吃得到索引

光建索引不够，查询也要扛得住。生产召回 SQL 来自 apps/api/src/agents/agents.repository.ts 的 vectorRecall()：

SELECT kc.id, kc.content, kc.embedding::text AS embedding,
       kd.id AS "documentId", kd.title AS "documentTitle"
  FROM knowledge_chunks kc
  JOIN knowledge_documents kd ON kd.id = kc."documentId"
 WHERE kd.agent_ids @> ARRAY[$2]::text[]
   AND kc.embedding IS NOT NULL
   AND kc."embeddingStatus" = 'ready'   -- 关键过滤：剔除失败的 1536 维全零
 ORDER BY kc.embedding <=> $1::vector   -- 余弦距离，HNSW 命中
 LIMIT $3;

要点：

1. 预过滤放 WHERE：把 agent_ids @> 与 embeddingStatus='ready' 放最前，让 planner 先用 B-Tree 砍掉绝大部分行再做 ANN。
2. 失败兜底靠 status：embedding 失败不写 NULL，写 1536 维全零向量；同时 embeddingStatus='failed'，召回 SQL 硬过滤。
3. 距离算子 <=>：pgvector 余弦距离算子（值越小越相似），HNSW 索引默认就用这个算子。

上图（arch-005）是轻易云智能对账系统的知识库 RAG 架构。向量召回只占整条管线 1/3：左边 embedding 入库 → 中间 ANN 召回 → 右边与关键词召回做 RRF 融合 → 末段 LLM 重排。单独押注 HNSW 提速是错的，必须配合预过滤 + 失败兜底。

2.3 如果一定要建：HNSW 参数怎么选

万一过了 10 万 chunk 这条线，必须建 HNSW，请按下面的对照表选参数：

m = 16, ef_construction = 64, ef_search = 40 是 pgvector 0.7 文档默认起步值；真实调优时跑 EXPLAIN ANALYZE 看 recall 与延迟比，不要凭经验。

三、JSONB GIN：明明有 JSONB 字段，却不一定要 GIN

3.1 一处「该建没建」的反问

生产库里有大量 JSONB 字段：

这是真实的反问：JSONB 字段这么多，却一个 GIN 索引都没建。是不是失职？

不是。JSONB GIN 是路径表达式专用——你用 data->>'shopId' = 'X' 这种单层等值查询，GIN 帮不上忙；用 data @> '{"status":"ready"}' 这种子树包含查询，GIN（jsonb_ops）才有意义。

上图（arch-020）是集成转换通用骨架。TransformBatch.columnSchema 与 TransformDocument.head 这两个 JSONB 字段，从来不做条件查询，它们是快照数据：写入后只按 batchId / documentId 整体读出。所以不需要 GIN。

3.2 GIN 适用与不适用的判别

反直觉点：把 JSONB 字段反范式化到独立 B-Tree 列，比建 GIN 索引往往更划算。一来写入省一次 GIN 更新，二来能进复合索引前缀。

生产实践中，TransformBatch.unmatchedSummary 是 JSONB 数组，用 Json + 序列化读出来用 TS 解析，从来不走 SQL 查询——所以也不建 GIN。

3.3 如果必须建 GIN：两条命令

-- 全功能 GIN：支持 @>、?、?&、?|
CREATE INDEX supply_rawdata_gin
  ON supply_orders USING gin (raw_data);

-- 仅支持 @> 的精简版（写入快、占用小）
CREATE INDEX supply_rawdata_path_gin
  ON supply_orders USING gin (raw_data jsonb_path_ops);

jsonb_path_ops 是取舍之选：写入开销约为通用版 30%，但仅支持 @> 一种操作符。在已知只用 @> 的场景（比如"包含某个 sku 子树"），优先这条命令。

四、复合索引实战：(shopId, periodFrom, periodTo) 的列序选择

4.1 一段生产索引

transform_batches 表（apps/api/prisma/schema/transform.prisma:104-133）的复合索引：

model TransformBatch {
  id           String   @id @default(cuid())
  shopId       String   @map("shop_id")
  platform     PlatformEnum
  periodFrom   String   @map("period_from") @db.VarChar(7) // YYYY-MM
  periodTo     String   @map("period_to")   @db.VarChar(7) // YYYY-MM
  ...
  @@index([shopId, periodFrom, periodTo])
  @@index([targetSystem])
  @@index([planId])
  @@map("transform_batches")
}

periodFrom / periodTo 是 VARCHAR(7)、形如 "2026-09"——用字符串存账期，不用 DATE。

为什么不存 DATE？因为账期范围是闭区间语义：

-- 业务查询：拿"2026-07 ~ 2026-09"的批次
SELECT *
  FROM transform_batches
 WHERE "shopId" = $1
   AND "periodFrom" <= '2026-09'    -- 起始落在范围内
   AND "periodTo"   >= '2026-07';   -- 终止落在范围内

这种「两个 VARCHAR 比较」在 PG 里走 B-Tree 索引毫无压力；如果换成 DATE 还得 cast 到月份。

上图（proc-013）展示反写路径：每次费用对账结束，都会按 shopId + periodFrom~periodTo 在 transform_batches 里查出对应批次。复合索引的列序决定了这个查询能不能吃索引。

4.2 列序：等值 → 范围 → 排序

最关键的反直觉知识：复合索引的列序遵循「等值 → 范围 → 排序」规则。

[shopId, periodFrom, periodTo] 把等值列 shopId 放第一位，后面两个都是 VARCHAR 范围列。如果你的查询是 WHERE shopId = X AND periodFrom >= A AND periodFrom <= B，PG 会用第一段做等值定位，后面整段当范围扫——很高效。

反例：

// 错的列序（periodFrom 在前）
@@index([periodFrom, periodTo, shopId])

-- 这条查询会变成全索引扫描
SELECT * FROM transform_batches
 WHERE "periodFrom" <= '2026-09'
   AND "periodTo"   >= '2026-07'
   AND "shopId" = 'S01';

因为 PG 必须先按 periodFrom 排序扫索引，对每个结果再过滤 shopId——索引选择率被严重稀释。

4.3 同表里 3 条索引的分工

transform_batches 同时存在 4 条索引：

@@index([shopId, periodFrom, periodTo])  // 主查询：按店铺 + 账期范围
@@index([targetSystem])                  // 单列：运维查全部金蝶批次
@@index([planId])                        // 单列：按单计划查所有批次

每条索引对应一个真实的业务查询，没有「宁滥勿缺」。

配套索引在 transform_documents：

@@index([batchId])
@@index([shopId, period])          // 文档级账期（单月 or 区间标签）
@@index([targetSystem, docType])   // 按目标系统 + 单据类型
@@index([status])                  // 状态机查询（DRAFT / CONFIRMED / PUSHED）

注意到一个细节：transform_batches 用 periodFrom / periodTo 两列存区间，transform_documents 用 period 单列存区间标签（"2026-06" 或 "2026-06~2026-07"）——头表存精确区间，体表存展示标签。这是 B-Tree 与业务语义的清晰分工。

上图（arch-009）展示整条数据流。transform_batches 与 transform_documents 处在末段，承接前面所有查询结果。复合索引的命中率决定这最后一段的 P95。

4.4 配套实践：(periodFrom, periodTo) 单独存在

income_reconciliation_plans 与 expense_reconciliation_plans 都各自有：

@@index([periodFrom, periodTo])

这是不带店铺的全局索引——业务场景是「年度审计拉所有店铺的所有账期」。复合列序规则在这里反过来：因为没有等值列，planner 会做 range scan，索引仍然有用，但选择率不如 (shopId, ...) 那条。

原则：复合索引的第一列最好是有高选择性的等值列——店铺、平台、用户 ID 这种，而不是账期。

五、三类索引的取舍总结

上图（arch-025）展示读写分离架构。三类索引在不同位置扮演不同角色：HNSW 在向量副本、GIN 在 OLAP 副本、B-Tree 复合索引在主库写路径——分层不是炫技，是读写比不同的物理隔离。

一句话：HNSW 是奢侈品、GIN 是手术刀、复合索引是白米饭。生产系统三样都要，但比例悬殊——白米饭占 95%，剩下两样按需点菜。

六、给 DBA 的 takeaway

如果你正在做电商 / 财务系统的索引选型，参考这条决策清单：

1. 先问「热点查询是什么」，再问「用什么索引」——三类索引没有优劣，只有匹配。
2. HNSW 不要写进 Prisma migration——一旦写就锁死 schema 漂移，迁移历史与 schema 永远对不齐。生产环境单条 SQL 维护。
3. JSONB 不是 GIN 的同义词——多数业务是「快照读」，根本不查；真要查，先看查询是 @> 还是 ->>，前者上 jsonb_path_ops，后者抽列。
4. 复合索引列序 = 等值 → 范围 → 排序，错一个顺序等于没建。
5. VARCHAR(7) 存账期比 DATE 更适合「范围闭区间」语义，B-Tree 索引照常生效。

说到底，索引设计不是「建得越多越好」，而是「建得准 + 选对类型 + 不污染迁移历史」。在轻易云智能对账系统的 70 张表里，复合索引走了 95%，HNSW 是 RAG 模块的奢侈品，JSONB 字段绝大多数走快照读——这种「克制」才是工程化的标志。

七、三个常被忽略的踩坑点

把上面的三类索引吃透后，再补三条 DBA 群里一问就炸锅的隐藏雷区。

7.1 雷区一：gin_trgm_ops 不是 GIN 正餐

聊到「文本模糊搜索」，很多 DBA 会下意识建 USING gin (col gin_trgm_ops)。但 gin_trgm_ops 是三字符组专用算子，不是 JSONB 也不是全文检索。它的命中条件是 col % 'keyword'，距离算子是 Levenshtein 的近似表达。

如果你的查询是「订单备注里包含『退款』二字」，正确做法是 tsvector + to_tsquery('退款') + GIN，而不是 trigram。两者占用空间差 3-5 倍，召回质量完全不同。

7.2 雷区二：复合索引「第一列 nullable」导致索引失效

PG 在 B-Tree 索引里默认不存 NULL 行（Heap Tuple 可见，索引条目缺席）。如果你的复合索引第一列是 nullable，而查询用 WHERE col_a IS NULL，planner 不会走索引——它会扫全表。

实务中，IncomePlanItem.period（账期）是 String? 可空，因为 SPH 跨期结算有「待结算行 period 置空本期不入账」的设计。这种字段必须单独再建一条 WHERE period IS NULL 的部分索引（PostgreSQL 9.5+ 支持 CREATE INDEX ... WHERE col IS NULL），否则业务查「待结算行」会慢成全表扫描。

7.3 雷区三：enable_seqscan = off 的诊断误区

调优时第一反应是 SET enable_seqscan = off，让 planner 必须走索引。这是诊断技巧，不是优化手段——它会让你看到「如果走索引要花多久」，但生产环境千万别长期开。enable_seqscan = off 是一种自我欺骗：planner 会选最烂的索引路径，反而把数据库弄慢。

正确做法是 EXPLAIN (ANALYZE, BUFFERS) 看实际执行，再回到「这条查询是不是该建更合适的索引」，而不是改 planner 参数。

八、实测：复合索引上线的 P95 变化

把 transform_batches 的复合索引上线后（实际场景：拉取某店铺最近 6 个月所有金蝶批次），对照上线前后：

关键观察：复合索引上线后，单查询 P95 从百毫秒级掉到个位数毫秒；但跨店跨平台并发查询的提升更显著——索引的真正价值在大并发场景才完全释放，单查询测试常让人误以为"差不多"。

这个实测也呼应了上一节的判断：复合索引才是生产系统的「白米饭」，三类索引的比例 95% / 4% / 1% 不是设计保守，是经过实测验证的分布。