PostgreSQL / 系统课时
为热点字段建表达式索引
为热点字段建表达式索引
整列 JSONB GIN 擅长包含和存在查询,但业务经常把某个 JSON 路径当普通字段使用:按金额做范围过滤、按时间排序、按编号等值查询。此时把热点路径提取成表达式并建立 B-tree 索引,通常比整列 GIN 更贴合访问方式。
学习目标
- 为 JSONB 热点路径建立可用于等值、范围和排序的表达式索引
- 正确选择
->>后的目标数据类型,避免按文本语义比较数字 - 判断查询表达式能否与表达式索引匹配
把热点路径当成可索引表达式
假设订单扩展属性保存在 payload 中,其中 amount 是 JSON 数字。业务经常执行“金额大于某个值并按金额排序”的查询。直接对整个 payload 建 GIN 并不能替代适合范围扫描的 B-tree。
表达式索引可以直接索引转换后的结果:
DROP TABLE IF EXISTS jsonb_order_demo;
CREATE TABLE jsonb_order_demo (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload jsonb NOT NULL
);
INSERT INTO jsonb_order_demo (payload) VALUES
('{"order_no":"A001","amount":29.90,"channel":"web"}'),
('{"order_no":"A002","amount":120.00,"channel":"app"}'),
('{"order_no":"A003","amount":58.50,"channel":"web"}'),
('{"order_no":"A004","amount":260.00,"channel":"app"}');
CREATE INDEX idx_order_amount
ON jsonb_order_demo (((payload ->> 'amount')::numeric));
SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id, (payload ->> 'amount')::numeric AS amount
FROM jsonb_order_demo
WHERE (payload ->> 'amount')::numeric >= 100
ORDER BY (payload ->> 'amount')::numeric;预期结果
执行计划应出现对 idx_order_amount 的 Index Scan 或等价的索引访问;具体计划细节可能因 PostgreSQL 版本和统计信息不同而变化。这里索引保存的不是完整 JSONB,而是表达式 (payload ->> 'amount')::numeric 的计算结果,因此它具有普通数值 B-tree 的排序语义,可以服务等值、范围和有序扫描。
类型转换决定比较语义
->> 返回 text。如果金额直接按文本建立索引,比较和排序遵循字符串语义,结果可能与数值大小完全不同。例如字符串 '100' 在词典顺序上并不等价于数值 100 的排序规则。
因此查询什么业务类型,就应尽量把表达式转换成对应 PostgreSQL 类型。数值可以转换为 numeric 或合适的整数类型,时间可以转换成与数据格式匹配的时间类型,布尔值也应转换为 boolean,而不是把所有内容长期当作文本。
查询表达式要与索引设计一致
表达式索引不是“只要访问同一路径就一定可用”。查询条件需要形成优化器能够与索引表达式对应的形式。已经把金额索引为 numeric 后,查询也应按相同数值表达式过滤。
下面这个查询保持了相同表达式,可以直接验证实际结果:
SELECT id, (payload ->> 'amount')::numeric AS amount
FROM jsonb_order_demo
WHERE (payload ->> 'amount')::numeric >= 100
ORDER BY (payload ->> 'amount')::numeric;预期结果
id | amount
----+--------
2 | 120.00
4 | 260.00如果改成 payload ->> 'amount' >= '100',比较类型已经变成文本,不应期待它等价使用上述数值表达式索引。索引设计和业务 SQL 必须采用一致的类型语义。
只索引真正稳定的热点路径
表达式索引适合字段语义稳定、查询频率高并且访问模式明确的 JSON 路径,例如订单号等值查询、金额范围过滤、事件时间排序。若今天查询 amount、明天查询 device.os.version、后天又随机访问几十个路径,为每个键建立表达式索引会迅速增加写放大和治理成本。
设计时应先从慢 SQL 和查询统计中找到热点,再决定索引表达式、类型和排序方式。对于一个已经稳定成为核心查询维度的 JSONB 属性,如果它长期承担业务字段职责,还应进一步评估是否应该把它提升为真正的关系列,而不是无限扩张表达式索引。