PostgreSQL 10.4 如何为jsonb SQL函数而非操作符创建可用索引
PostgreSQL jsonb_exists函数索引适配方案
现象说明
你遇到的执行效率差异是PostgreSQL查询规划器的固有逻辑导致:
?是jsonb类型的内置操作符,底层确实调用jsonb_exists函数实现逻辑,但规划器默认会将?操作符匹配到对应GIN索引的操作符类,因此可以正常命中索引- 显式调用
jsonb_exists函数时,规划器不会自动将其等价转换为?操作符调用,因此无法匹配你创建的普通GIN索引,只能走全表扫描
适配方案(PostgreSQL 10.4及所有更高版本均支持)
方案1:改写查询(成本最低,官方推荐)
直接将查询中的jsonb_exists函数调用等价替换为?操作符写法,无需修改现有索引,即可达到第二笔查询的性能,从你的测试数据看性能提升可达10000倍以上。
改写后逻辑完全等价,没有兼容性问题,是官方推荐的标准写法。
方案2:不修改原有查询的适配方法
如果受限于老业务代码无法修改查询语句,可以通过自定义同名函数覆盖系统函数的方式实现索引命中:
- 在业务使用的schema下创建同名函数:
CREATE OR REPLACE FUNCTION jsonb_exists(jsonb, text) RETURNS boolean AS $$ SELECT $1 ? $2; $$ LANGUAGE sql IMMUTABLE STRICT;
- 确保业务用户的schema搜索路径优先查找当前schema的函数,即可让原有
jsonb_exists查询自动匹配现有的test_idxGIN索引,无需额外创建新索引。
补充说明
如果不愿意覆盖系统函数,也可以通过创建触发器维护冗余字段的方式适配:
- 新增
package_numbers字段,类型为jsonb,通过INSERT/UPDATE触发器自动同步json_delivery->'packageNumbers'的值 - 给
package_numbers字段创建GIN索引,原有查询修改为jsonb_exists(package_numbers, '目标值')也可以命中索引,不过该方案需要修改表结构,成本高于前两种方案。
内容的提问来源于stack exchange,提问作者Tomasz Zadora
相关产品推荐
相关产品推荐

