如何让PostgreSQL GIN索引与jsonb_*函数配合生效？

我发现当在查询中使用jsonb_*函数时，PostgreSQL无法使用GIN索引，这个问题在jsonb_ops和jsonb_path_ops操作类中都存在。

准备工作 #

1. 创建表及GIN索引 #

CREATE TABLE applications
(
    id          TEXT PRIMARY KEY,
    application JSONB
);
CREATE INDEX ON applications USING gin (application jsonb_path_ops);

2. 插入大量测试数据 #

为了让PostgreSQL更倾向于使用索引，插入10万条测试数据：

INSERT INTO applications(id, application)
VALUES ('1', '{
  "type_code": 1,
  "persons": [
    {
      "type_code": 4,
      "firstname": "John",
      "lastname": "Doe"
    }
  ]
}');
INSERT INTO applications (SELECT i, a.application FROM applications a, generate_series(2, 100000) i);

测试查询对比 #

1. 使用@?操作符（索引生效） #

执行以下查询：

EXPLAIN ANALYZE
SELECT * FROM applications
WHERE applications.application @? '$.persons[*] ? (@.type_code == 3)';

执行计划显示GIN索引被成功使用：

-- Bitmap Heap Scan on applications  (cost=64.00..68.01 rows=1 width=130) (actual time=0.410..0.419 rows=0 loops=1)
-- "  Recheck Cond: (application @? '$.\"\"persons\"\"[*]?(@.\"\"type_code\"\" == 3)'::jsonpath)"
--   ->  Bitmap Index Scan on applications_application_idx  (cost=0.00..64.00 rows=1 width=0) (actual time=0.095..0.096 rows=0 loops=1)
-- "        Index Cond: (application @? '$.\"\"persons\"\"[*]?(@.\"\"type_code\"\" == 3)'::jsonpath)"
-- Planning Time: 1.493 ms
-- Execution Time: 0.861 ms

2. 使用jsonb_path_exists函数（索引未生效） #

执行以下查询：

EXPLAIN ANALYZE
SELECT * FROM applications
WHERE jsonb_path_exists(
      applications.application,
      '$.persons[*] ? (@.type_code == 3)'
);

执行计划显示采用全表扫描，未使用GIN索引：

-- Aggregate  (cost=3374.33..3374.34 rows=1 width=8) (actual time=114.048..114.055 rows=1 loops=1)
--   ->  Seq Scan on applications  (cost=0.00..3291.00 rows=33333 width=0) (actual time=0.388..109.580 rows=100000 loops=1)
-- "        Filter: jsonb_path_exists(application, '$.\"\"persons\"\"[*]?(@.\"\"type_code\"\" == 3)'::jsonpath, '{}'::jsonb, false)"
-- Planning Time: 1.514 ms
-- Execution Time: 114.674 ms

疑问与需求 #

能否让第二个使用jsonb_path_exists函数的查询也用上GIN索引？我更倾向于使用jsonb_*函数，因为可以通过位置参数构建动态查询，比如：

SELECT * FROM applications
WHERE jsonb_path_exists(
      applications.application,
      '$.persons[*] ? (@.type_code == $person_type_code)',
      jsonb_build_object('person_type_code', $1)
);

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