基于ID列表关联查询过慢，求PostgreSQL查询性能优化方案

需求与现状 #

我有document表（包含jsonb类型的content列）和media表，两表都有id::int、application_instance::int字段。需要检查document.content中所有mediaId对应的media记录，其application_instance_id是否和对应document记录不一致。当前查询耗时极长，希望优化运行时长。

已知信息：

• document.content的jsonb结构无固定格式；
• media表约30000行；
• document表约9500行。

当前使用的查询代码：

SELECT doc.application_instance_id = m.application_instance_id as isEqual,
       doc.id,
       doc.application_instance_id,
       m.id,
       m.application_instance_id,
       jsonb_path_query_array(content, '$.**.mediaId') as array_of_media_ids
FROM document doc
INNER JOIN media m on m.id in
(
SELECT nullif(list_of_media_ids, 'null')::int FROM jsonb_path_query(doc.content, '$.**.mediaId') as list_of_media_ids
 ) ORDER BY isEqual
;

优化方案 #

原查询的性能瓶颈在于每行document都重复执行JSON解析子查询，且用IN关联无法有效利用索引，以下是针对性优化：

1. 预提取mediaId再关联 #

先把document中的所有有效mediaId提取出来，统一处理后再关联media表，避免重复解析JSON：

WITH doc_media_ids AS (
    SELECT 
        doc.id AS doc_id,
        doc.application_instance_id AS doc_app_inst_id,
        jsonb_path_query_array(doc.content, '$.**.mediaId') AS all_media_ids,
        -- 提取单个有效mediaId（转整数并排除null）
        (jsonb_path_query(doc.content, '$.**.mediaId')::text)::int AS media_id
    FROM document doc
    -- 过滤无mediaId的文档，减少无效计算
    WHERE jsonb_path_exists(doc.content, '$.**.mediaId')
)
SELECT 
    dmi.doc_app_inst_id = m.application_instance_id AS isEqual,
    dmi.doc_id,
    dmi.doc_app_inst_id,
    m.id AS media_id,
    m.application_instance_id AS media_app_inst_id,
    dmi.all_media_ids
FROM doc_media_ids dmi
JOIN media m ON dmi.media_id = m.id
-- 可选：只筛选不一致的记录，进一步缩小结果集
-- WHERE dmi.doc_app_inst_id != m.application_instance_id
ORDER BY isEqual;

2. 给media表添加必要索引 #

确保media表的id字段有索引（主键默认带索引，若没有则手动添加）：

ALTER TABLE media ADD PRIMARY KEY (id);

如果后续常按application_instance_id过滤，可追加复合索引：

CREATE INDEX idx_media_app_inst_id ON media(application_instance_id, id);

3. 避免重复计算JSON数组 #

原查询中每行都会重新计算jsonb_path_query_array，在CTE中预先计算一次即可，减少CPU开销。

原查询慢的原因 #

• 关联条件中的jsonb_path_query导致每行document都要多次解析JSON并执行子查询；
• IN子查询的关联方式无法利用media表的索引，触发低效的嵌套循环；
• SELECT子句中重复计算jsonb_path_query_array，增加不必要的计算量。

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