PostgreSQL / 系统课时
选择 GIN 操作符类
选择 GIN 操作符类
给 JSONB 建 GIN 索引时,真正需要选择的不只是“是否使用 GIN”,还包括操作符类。jsonb_ops 与 jsonb_path_ops 支持的操作符、索引项组织方式和适用查询不同,本课用一组查询把“操作符支持”与“具体 JSONPath 能否提取有效索引条件”分开验证。
学习目标
- 区分
jsonb_ops与jsonb_path_ops支持的 JSONB 操作符 - 理解“支持 JSONPath”与“具体 JSONPath 可被有效索引化”的区别
- 用执行计划和索引尺寸验证操作符类选择
两种操作符类解决的问题不同
jsonb_ops 是 JSONB GIN 的默认操作符类,覆盖范围更广。它支持 @> 包含查询、?、?|、?& 键存在查询,以及 @?、@@ JSONPath 查询。
jsonb_path_ops 覆盖范围更窄,不支持 ?、?|、?&,但支持 @>、@? 和 @@。对于主要按路径和值做包含或等值匹配的数据,它通常能形成更紧凑、更有针对性的索引项。
| 查询需求 | jsonb_ops |
jsonb_path_ops |
|---|---|---|
doc @> '{...}' |
支持 | 支持 |
doc ? 'key' |
支持 | 不支持 |
| `doc ? | array[...]` | 支持 |
doc ?& array[...] |
支持 | 不支持 |
@?、@@ JSONPath |
支持 | 支持,但仍取决于具体表达式能否提取有效索引条件 |
| 访问模式以包含查询为主 | 可用 | 通常值得优先测试 |
因此不能因为 jsonb_path_ops 往往更小就直接替换默认操作符类。查询实际使用什么操作符,是第一层约束。
把操作符支持与路径可提取性分开判断
jsonb_ops 和 jsonb_path_ops 都声明支持 @?、@@,这只代表操作符类能够参与这类查询的 GIN 索引搜索,并不代表任意 JSONPath 表达式都会得到同样有效的索引查找。
处理 JSONPath 时,GIN 会尝试从表达式中提取类似 accessor_chain == constant 的可索引子句,再根据路径和值构造搜索条件。例如 $.status == "paid" 同时给出了明确访问链和常量等值,通常能够形成有效的索引条件;只有正则、复杂计算或其他无法提取等值条件的表达式,则不能仅凭使用了 @? 或 @@ 就推断会得到同等索引收益。
可提取的访问链可以包含 .key、[*] 和 [index]。此外,jsonb_ops 还能处理 JSONPath 中的 .* 和 .** 访问器,而 jsonb_path_ops 不支持用这两种访问器提取索引条件。
用一组查询验证操作符类
下面同时建立两种 GIN 索引,分别验证键存在、包含和一个可提取等值条件的 JSONPath。为了让小型演示数据更容易展示可用索引,只在事务内临时关闭顺序扫描;生产环境不要把 enable_seqscan = off 当成优化手段。
DROP TABLE IF EXISTS jsonb_gin_demo;
CREATE TABLE jsonb_gin_demo (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload jsonb NOT NULL
);
INSERT INTO jsonb_gin_demo (payload) VALUES
('{"type":"payment","status":"paid","region":"cn"}'),
('{"type":"payment","status":"pending","region":"us"}'),
('{"type":"refund","status":"done"}');
CREATE INDEX idx_payload_ops
ON jsonb_gin_demo USING gin (payload jsonb_ops);
CREATE INDEX idx_payload_path_ops
ON jsonb_gin_demo USING gin (payload jsonb_path_ops);
BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id
FROM jsonb_gin_demo
WHERE payload ? 'region';
EXPLAIN (COSTS OFF)
SELECT id
FROM jsonb_gin_demo
WHERE payload @> '{"type":"payment","status":"paid"}';
EXPLAIN (COSTS OFF)
SELECT id
FROM jsonb_gin_demo
WHERE payload @@ '$.status == "paid"';
ROLLBACK;
SELECT
pg_size_pretty(pg_relation_size('idx_payload_ops')) AS ops_size,
pg_size_pretty(pg_relation_size('idx_payload_path_ops')) AS path_ops_size;预期结果
? 'region':只有 idx_payload_ops 能为该键存在操作符提供 GIN 索引条件。
@>:两种操作符类都具备支持能力,最终使用哪个索引由优化器按成本决定。
@@ '$.status == "paid"':表达式含可提取的路径与常量等值条件,两种操作符类都具备索引支持能力。
ops_size | path_ops_size
---------+--------------
<VALUE> | <VALUE>具体计划中的索引名称、扫描节点和尺寸会随 PostgreSQL 版本、统计信息与数据分布变化。这里验证的是三件事:操作符是否兼容、JSONPath 是否含可提取条件,以及优化器最终如何使用候选索引;不能把其中任意一项单独等同于“这条 JSONPath 一定高效命中 GIN”。
用真实访问模式做最终选择
如果业务几乎都是固定结构的 @> 查询,或使用能够提取路径和值等值条件的 JSONPath,而很少判断任意键是否存在,jsonb_path_ops 通常值得优先基准测试。它往往更紧凑,在大量文档共享相同键名时也可能提供更具体的搜索条件。
如果查询平台大量使用 ?、?|、?&,或 JSONPath 依赖 .*、.** 等 jsonb_path_ops 无法用于索引提取的访问方式,应优先保留 jsonb_ops。
同时维护两套 GIN 虽然能扩大查询覆盖面,但也会增加磁盘占用以及插入、更新时的索引维护成本。只有两类访问模式都足够重要,并且真实数据上的 EXPLAIN (ANALYZE, BUFFERS)、索引尺寸和写入基准证明收益大于成本时,才值得长期保留两套索引。