PostgreSQL / 系统课时

为热点字段建表达式索引

为热点字段建表达式索引

整列 JSONB GIN 擅长包含和存在查询,但业务经常把某个 JSON 路径当普通字段使用:按金额做范围过滤、按时间排序、按编号等值查询。此时把热点路径提取成表达式并建立 B-tree 索引,通常比整列 GIN 更贴合访问方式。

学习目标

  • 为 JSONB 热点路径建立可用于等值、范围和排序的表达式索引
  • 正确选择 ->> 后的目标数据类型,避免按文本语义比较数字
  • 判断查询表达式能否与表达式索引匹配

把热点路径当成可索引表达式

假设订单扩展属性保存在 payload 中,其中 amount 是 JSON 数字。业务经常执行“金额大于某个值并按金额排序”的查询。直接对整个 payload 建 GIN 并不能替代适合范围扫描的 B-tree。

表达式索引可以直接索引转换后的结果:

sql
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;

预期结果

text
执行计划应出现对 idx_order_amount 的 Index Scan 或等价的索引访问;具体计划细节可能因 PostgreSQL 版本和统计信息不同而变化。

这里索引保存的不是完整 JSONB,而是表达式 (payload ->> 'amount')::numeric 的计算结果,因此它具有普通数值 B-tree 的排序语义,可以服务等值、范围和有序扫描。

类型转换决定比较语义

->> 返回 text。如果金额直接按文本建立索引,比较和排序遵循字符串语义,结果可能与数值大小完全不同。例如字符串 '100' 在词典顺序上并不等价于数值 100 的排序规则。

因此查询什么业务类型,就应尽量把表达式转换成对应 PostgreSQL 类型。数值可以转换为 numeric 或合适的整数类型,时间可以转换成与数据格式匹配的时间类型,布尔值也应转换为 boolean,而不是把所有内容长期当作文本。

查询表达式要与索引设计一致

表达式索引不是“只要访问同一路径就一定可用”。查询条件需要形成优化器能够与索引表达式对应的形式。已经把金额索引为 numeric 后,查询也应按相同数值表达式过滤。

下面这个查询保持了相同表达式,可以直接验证实际结果:

sql
SELECT id, (payload ->> 'amount')::numeric AS amount
FROM jsonb_order_demo
WHERE (payload ->> 'amount')::numeric >= 100
ORDER BY (payload ->> 'amount')::numeric;

预期结果

text
 id | amount
----+--------
  2 | 120.00
  4 | 260.00

如果改成 payload ->> 'amount' >= '100',比较类型已经变成文本,不应期待它等价使用上述数值表达式索引。索引设计和业务 SQL 必须采用一致的类型语义。

只索引真正稳定的热点路径

表达式索引适合字段语义稳定、查询频率高并且访问模式明确的 JSON 路径,例如订单号等值查询、金额范围过滤、事件时间排序。若今天查询 amount、明天查询 device.os.version、后天又随机访问几十个路径,为每个键建立表达式索引会迅速增加写放大和治理成本。

设计时应先从慢 SQL 和查询统计中找到热点,再决定索引表达式、类型和排序方式。对于一个已经稳定成为核心查询维度的 JSONB 属性,如果它长期承担业务字段职责,还应进一步评估是否应该把它提升为真正的关系列,而不是无限扩张表达式索引。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

为热点字段建表达式索引

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通