PostgreSQL 14中GIN索引下JSONPath查键为何慢？如何优化？

你遇到的性能差异，核心原因是默认的GIN索引（无论是jsonb_ops还是jsonb_path_ops类型）对检查深层级键存在的JSONPath查询优化有限。$.** ? ( exists(@."abc") )需要遍历JSON结构的所有层级来验证键是否存在，优化器无法通过常规GIN索引快速定位符合条件的行；而$.** ? ( @.* == "abc" )这类值匹配查询，刚好适配jsonb_path_ops索引的路径值存储逻辑，所以能高效命中索引。

以下是几种可行的优化方案：

• 方案1：创建基于全层级键数组的GIN索引
  先定义一个不可变函数，提取JSONB中所有层级的键并去重：

  CREATE OR REPLACE FUNCTION jsonb_extract_all_keys(j jsonb) RETURNS text[] AS $$
  SELECT ARRAY(
    SELECT DISTINCT key
    FROM jsonb_each_text(j)
    UNION ALL
    SELECT DISTINCT jsonb_extract_all_keys(value)
    FROM jsonb_each(j)
    WHERE jsonb_typeof(value) = 'object'
  )
  $$ LANGUAGE sql IMMUTABLE;
  

  然后基于该函数创建GIN索引：

  CREATE INDEX idx_t_mycolumn_all_keys ON t USING gin (jsonb_extract_all_keys("myColumn"));
  

  修改查询语句为：

  SELECT * FROM t WHERE jsonb_extract_all_keys("myColumn") @> ARRAY['abc'];
  

  索引会将每行JSON的所有键存储为数组，GIN索引能快速匹配包含目标键的行，避免全表遍历。

• 方案2：使用jsonb_has_path函数+表达式索引
  PostgreSQL 12+支持jsonb_has_path函数，可直接检查JSON中是否存在指定路径，结合表达式索引能大幅提升性能：
  创建索引：

  CREATE INDEX idx_t_mycolumn_has_abc ON t USING gin (jsonb_has_path("myColumn", '{**,abc}'));
  

  修改查询语句为：

  SELECT * FROM t WHERE jsonb_has_path("myColumn", '{**,abc}');
  

  这个方式无需自定义函数，{**,abc}路径表示匹配任意层级下的abc键。

• 方案3：调整JSON结构（业务允许时）
  如果业务逻辑允许，尽量把需要频繁查询的键放到顶级层级，直接使用?操作符配合默认的jsonb_opsGIN索引，性能最优：

  -- 已创建jsonb_ops类型GIN索引的情况下
  SELECT * FROM t WHERE "myColumn" ? 'abc';
  

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