PostgreSQL 16如何避免JSON数字字段的无意义int类型转换？

问题背景 #

现有查询可正常执行：

SELECT COUNT(*) FROM events e WHERE (e.event_data -> 'state')::int = -1;

但数据库中event_data字段存储的JSON对象里，state本身就是int类型：

{
  "eventUID": "3ea6baf7-8772-48a5-a32b-b00901534025",
  "data": {<some-data>},
  "state": 1
}

通过EXPLAIN ANALYZE可见，PostgreSQL会先把state的JSON值转为text，再重新转为int，存在额外的无意义类型转换，需要优化查询以消除这个开销。

优化方案 #

针对JSON类型字段 #

直接让JSON类型值与对应类型的JSON常量比较，跳过多余转换：

SELECT COUNT(*) FROM events e WHERE e.event_data -> 'state' = '-1'::json;

也可以用json_build_object构造更直观的JSON常量：

SELECT COUNT(*) FROM events e WHERE e.event_data -> 'state' = json_build_object('', -1)::json;

针对JSONB类型字段（推荐） #

如果event_data是jsonb类型（PostgreSQL中优先推荐使用jsonb，性能更优），可使用包含操作符@>，这种方式不仅避免类型转换，还能借助GIN索引（若已创建）进一步提升查询速度：

SELECT COUNT(*) FROM events e WHERE e.event_data @> '{"state": -1}'::jsonb;

原理说明 #

• ->操作符返回JSON类型值，直接与JSON常量比较时，PostgreSQL会直接进行JSON值的等价判断，无需经过text中转再转int的步骤。
• 对于jsonb类型，@>操作符检查目标对象是否包含指定键值对，逻辑贴合JSON数据结构，且能利用索引优化查询效率。

内容的提问来源于stack exchange，提问作者devops