PostgreSQL JSON数组中高效批量ID查询优化方案

问题背景 #

我有一个包含数百万条记录的数据库，每条记录的JSONB字段blob里有identifiers数组（已创建索引），结构示例如下：

{
     "identifiers": [
      {"isin": "XS3", "primary": true},
      {"valor": 456},
      {"isin": "XS4"}
    ],
    "workflows": {},
    "slots": {}
}

原本单条查询效率很高：只要identifiers数组中存在任意一个对象匹配指定的键值对（比如{"isin": "XS3", "primary": true}），就返回该行。但现在需要改成批量查询100个ID，且不能硬编码标识符类型（如isin/valor）。

当前问题 #

尝试通过jsonb_object_keys(scheme_and_id.json[0])提取标识符类型时，查询成本大幅上升。之前尝试提前在WITH子句提取类型，但会破坏ID参数的数组结构——原本需要保持[{"isin": "XS3", "primary": true}]的数组格式才能使用@>操作符，处理后变成了单个对象{"isin": "XS3", "primary": true}，无法匹配索引。

当前使用的查询示例：

WITH scheme_and_id AS (
    -- 将输入JSON转为行
    SELECT jsonb_array_elements('[
      [{"isin": "XS1"}],
      [{"valor": 456}]
    ]'::jsonb) as json
),
resolve_id as (
    SELECT
        "id",
        (blob -> 'identifiers') as "ids",
        jsonb_object_keys(scheme_and_id.json[0]) as id_type
    FROM blobstable,
         scheme_and_id
    WHERE (blob -> 'identifiers') @> scheme_and_id.json
)
select "id", id_type
from resolve_id

优化方案 #

1. 提前预解析输入参数，保留数组结构 #

先把每个输入的ID参数解析出类型和值，同时保留用于索引匹配的数组结构，避免重复解析带来的性能损耗：

WITH input_params AS (
    SELECT
        -- 保留用于@>匹配的数组结构，确保命中索引
        arr.json AS match_arr,
        -- 一次性提取标识符类型和对应值
        (jsonb_each_text(arr.json->0)).key AS id_type,
        (jsonb_each_text(arr.json->0)).value AS id_value
    FROM jsonb_array_elements('[
      [{"isin": "XS1"}],
      [{"valor": 456}]
    ]'::jsonb) AS arr(json)
),
matched_rows AS (
    SELECT
        b.id,
        p.id_type
    FROM blobstable b
    JOIN input_params p ON b.blob->'identifiers' @> p.match_arr
)
SELECT id, id_type FROM matched_rows;

2. 改用JSON路径匹配（忽略额外字段场景） #

如果只需要匹配类型:值、可以忽略primary这类额外字段，用jsonb_path_match简化逻辑，同时支持批量匹配：

WITH input_params AS (
    SELECT
        (jsonb_each_text(arr.json->0)).key AS id_type,
        (jsonb_each_text(arr.json->0)).value AS id_value
    FROM jsonb_array_elements('[
      [{"isin": "XS1"}],
      [{"valor": 456}]
    ]'::jsonb) AS arr(json)
)
SELECT
    b.id,
    p.id_type
FROM blobstable b
JOIN input_params p ON jsonb_path_match(
    b.blob->'identifiers',
    '$[*].' || p.id_type || ' == $val',
    jsonb_build_object('val', p.id_value)
);

注意：若要让该方式命中索引，需创建jsonb_path_ops类型的索引：

CREATE INDEX idx_identifiers_path ON blobstable USING gin ((blob->'identifiers') jsonb_path_ops);

3. 避免笛卡尔积损耗 #

原查询中FROM blobstable, scheme_and_id会产生笛卡尔积，当输入参数较多时会大幅增加计算量。改用JOIN ... ON的方式明确关联条件，只保留匹配的行，减少无效计算。

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