PostgreSQL jsonb查询优化：批量判断指定键值非零的高效写法

我现有一个PostgreSQL查询，原本仅需判断jsonb字段里2个指定键的值不为0，现在要扩展为判断20个指定键至少有一个不为0，但不想手动逐个编写判断条件。性能是核心要求，希望采用和当前最快写法同类型的高效表达式。

当前查询（判断2个键）：

SELECT *
FROM my_schema.my_table
where updated_at > 'now'::timestamp - '1 day'::INTERVAL
AND jsonb_column @? '$ ? (@.key1 != 0 || @.key2!= 0 )'

预期扩展后的逻辑（但不想手动写20个OR条件）：

SELECT *
FROM my_schema.my_table
where updated_at > 'now'::timestamp - '1 day'::INTERVAL
AND jsonb_column @? '$ ? (@.key1 != 0 || @.key2 != 0 || @.key3 != 0 || @.key4 != 0 || @.key5 != 0)'

我试过用jsonb_each的EXISTS子句，但大表上性能比当前写法慢一倍：

SELECT *
FROM my_schema.my_table
WHERE updated_at > 'now'::timestamp - '1 day'::INTERVAL and not EXISTS (
  SELECT 1
  FROM jsonb_each(jsonb_column) AS j(k, v)
  WHERE j.v::text = '0'
  AND j.k=ANY(ARRAY['key1', 'key2'])
)

目前最快的写法如下，但要写20个条件太繁琐，求更简洁的高效写法：

SELECT *
FROM my_schema.my_table
WHERE updated_at > 'now'::timestamp - '1 day'::INTERVAL and    
(abs((jsonb_column->>'key1')::float) > 0
    or abs((jsonb_column->>'key2')::float) > 0)

方案1：动态生成JSON路径表达式（简洁且性能接近原生） #

通过字符串拼接生成JSON路径表达式，避免手动编写20个OR条件，同时保留@?操作符的高效性（可利用jsonb索引）：

WITH keys AS (
  SELECT ARRAY['key1', 'key2', 'key3', ..., 'key20'] AS key_list
)
SELECT t.*
FROM my_schema.my_table t
CROSS JOIN keys
WHERE updated_at > now() - INTERVAL '1 day'
AND jsonb_column @? (
  SELECT format('$ ? (%s)', string_agg('@.' || quote_ident(k) || ' != 0', ' || '))::jsonpath
  FROM unnest(key_list) k
)

性能说明：该写法最终生成的路径表达式和手动编写的完全一致，能复用@?操作符的优化逻辑，若jsonb_column上建有GIN索引，性能和原生写法无差异。

方案2：数组提取+存在性检查（性能接近原生字段判断） #

若键对应的值均为数字类型，可将指定键的值提取为数组，再检查数组中是否存在非0值：

SELECT *
FROM my_schema.my_table
WHERE updated_at > now() - INTERVAL '1 day'
AND EXISTS (
  SELECT 1
  FROM unnest(ARRAY[
    (jsonb_column->>'key1')::float,
    (jsonb_column->>'key2')::float,
    ...,
    (jsonb_column->>'key20')::float
  ]) val
  WHERE abs(val) > 0
)

性能说明：此写法和你当前最快的原生判断逻辑本质一致，仅用数组和unnest简化了重复代码，无额外jsonb遍历开销，性能与手动写20个OR条件相当。

方案3：预生成动态SQL（适合固定键列表场景） #

如果键列表固定，可通过脚本（Python/Shell等）自动生成判断条件片段，再嵌入主查询：
比如生成的条件片段：

abs((jsonb_column->>'key1')::float) > 0 OR
abs((jsonb_column->>'key2')::float) > 0 OR
...
abs((jsonb_column->>'key20')::float) > 0

嵌入后既保留原生判断的最高性能，又无需手动编写重复代码。

• 若使用JSON路径查询，确保jsonb_column上建有GIN索引；若常用特定键，可建部分索引，例如：

  CREATE INDEX idx_my_table_jsonb_keys ON my_schema.my_table USING btree (
    (jsonb_column->>'key1')::float,
    (jsonb_column->>'key2')::float
  ) WHERE updated_at > now() - INTERVAL '7 days';
  

• 避免用jsonb_each全量遍历jsonb字段，大表中会产生大量行膨胀，导致性能下降。

内容的提问来源于stack exchange，提问作者Guyon Van Rooij