PostgreSQL / 系统课时
设计复合查询索引
设计复合查询索引
真实 JSONB 查询很少只过滤 JSON 字段,通常还会同时带租户、时间和业务状态。本课用两类索引方案处理这种混合条件:能落到同一种 B-tree 访问路径时使用复合表达式索引,需要 JSONB 包含查询时则让普通索引与 GIN 通过位图扫描协同。
学习目标
- 根据等值条件、范围条件和 JSONB 表达式安排复合 B-tree 索引列顺序
- 判断何时应将普通列索引与 JSONB GIN 索引拆开
- 使用执行计划验证复合索引或 BitmapAnd 是否真正参与查询
先区分两类 JSONB 条件
假设事件表按租户查询最近一段时间的数据,同时过滤 JSONB 中的状态。若 JSONB 条件最终是一个可比较的标量,例如 payload ->> 'status' = 'failed',它可以作为 B-tree 表达式参与复合索引。
如果查询使用 @> 等 JSONB 包含运算符,则通常需要 GIN。GIN 与普通 B-tree 不能直接混成一个传统多列索引来同时承担这些不同访问方式,此时更常见的策略是建立独立索引,让 PostgreSQL 根据成本选择位图合并。

用复合表达式索引承载标量条件
对于“租户等值 + 状态等值 + 时间范围”查询,通常把等值过滤列放在范围列之前。这样 B-tree 可以先缩小租户和状态,再在较小范围内扫描时间区间。
下面建立示例表和索引:
CREATE TABLE tenant_event (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id bigint NOT NULL,
created_at timestamptz NOT NULL,
payload jsonb NOT NULL
);
CREATE INDEX idx_tenant_event_status_time
ON tenant_event (
tenant_id,
(payload ->> 'status'),
created_at DESC
);预期结果
CREATE TABLE
CREATE INDEX对应查询如下:
EXPLAIN (COSTS OFF)
SELECT id, created_at, payload
FROM tenant_event
WHERE tenant_id = 42
AND payload ->> 'status' = 'failed'
AND created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'
ORDER BY created_at DESC;预期结果
Index Scan using idx_tenant_event_status_time on tenant_event
Index Cond: ((tenant_id = 42) AND ((payload ->> 'status'::text) = 'failed'::text) AND (created_at >= '2026-09-01 00:00:00+00'::timestamp with time zone))实际是否采用该索引取决于数据量和统计信息,但列顺序体现了关键原则:连续的前导等值条件能够有效缩小扫描区间,而范围条件通常放在其后。如果把 created_at 提前到状态表达式之前,时间范围一旦命中大量记录,后面的状态条件对缩小 B-tree 扫描边界的帮助会下降。
让 B-tree 与 GIN 通过位图协同
当查询需要 JSONB 包含语义时,例如 payload @> '{"labels":["urgent"]}',可以分别建立普通列 B-tree 和 JSONB GIN 索引。规划器在合适的数据分布下能够把多个索引结果转换为位图并执行交集。
CREATE INDEX idx_tenant_event_tenant_time
ON tenant_event (tenant_id, created_at);
CREATE INDEX idx_tenant_event_payload_gin
ON tenant_event USING GIN (payload);
EXPLAIN (COSTS OFF)
SELECT id, created_at, payload
FROM tenant_event
WHERE tenant_id = 42
AND created_at >= TIMESTAMPTZ '2026-09-01 00:00:00+00'
AND payload @> '{"labels":["urgent"]}'::jsonb;预期结果(当两个条件都具有足够选择性时)
Bitmap Heap Scan on tenant_event
Recheck Cond: (...)
-> BitmapAnd
-> Bitmap Index Scan on idx_tenant_event_tenant_time
-> Bitmap Index Scan on idx_tenant_event_payload_ginBitmapAnd 不是保证出现的固定计划。若某个条件选择性很弱,规划器可能只使用一个索引,再在堆表阶段过滤剩余条件;如果命中行过多,也可能直接选择顺序扫描。因此不能把“已经建立两个索引”等同于“数据库一定会合并两个索引”。
用执行计划决定合并还是拆分
设计混合索引时,不要机械地把所有过滤字段塞入一个巨大索引。先按真实查询判断 JSONB 条件属于标量比较还是结构包含,再观察等值列、范围列和 JSONB 条件各自的选择性。
复合 B-tree 更适合同一组高频查询稳定地使用“租户 + JSON 标量状态 + 时间范围”;独立 B-tree 与 GIN 更适合 JSONB 结构查询种类较多、普通列条件也能独立复用的场景。最终应通过 EXPLAIN 或 EXPLAIN ANALYZE 验证访问路径,并结合索引体积和写入成本决定是否保留,而不是只追求执行计划里出现更多索引。