PostgreSQL中Union结合jsonb_path_query的性能优化方法
2026-8-13
PostgreSQL 13.6 JSONB Union查询性能优化问题
我有一张PostgreSQL 13.6版本的表，包含jsonb类型列。为了从JSON的不同路径提取属性，我用Union合并三个针对不同路径的查询结果以获取完整数据集。但三个单独查询仅耗时80-170毫秒，合并后的Union查询却需要50秒至1分钟。由于要创建包含所有符合条件数据的视图，必须使用Union。
表约有20万行，实际JSON结构超4000行，示例结构如下：
{"node1": { "node2": { "node3": [ {"node4": { "node5": { "Code": "34","Image": "ffffffff-ffff-ffff-ffff-ffffffffffff","System": "2","PercentageAddup": "True"}},"node6": { "RoleID": "00000000-0000-0000-0000-000000000033","UserID": "WebServices","PartyID": "cc6ef1d8-d0ad-4044-9bd8-6c34c16eec5f","Percentage": "1.00"}},{"node4": { "node5": { "Code": "32","Image": "ffffffff-ffff-ffff-ffff-ffffffffffff","System": "2","PercentageAddup": "False"}},"node6": { "RoleID": "00000000-0000-0000-0000-000000000118","UserID": "WebServices","PartyID": "10d8e781-a4d7-4a17-a4a0-eb7ac71b75b4","Percentage": "1"}}]},"node7": [ {"node8": { "node9": { "Code": "8","Image": "ffffffff-ffff-ffff-ffff-ffffffffffff","System": "2","PercentageAddup": "True"}},"node10": { "RoleID": "00000000-0000-0000-0000-000000000143","UserID": "WebServices","PartyID": "10d8e781-a4d7-4a17-a4a0-eb7ac71b75b4","Relationship": "Self"}},{"node8": { "node9": { "Code": "31","Image": "ffffffff-ffff-ffff-ffff-ffffffffffff","System": "2","PercentageAddup": "False"}},"node10": { "RoleID": "00000000-0000-0000-0000-000000000156","UserID": "WebServices","PartyID": "10d8e781-a4d7-4a17-a4a0-eb7ac71b75b4","Relationship": "Self"}}]},"node11": { "node12": { "node13": { "Code": "38","Image": "ffffffff-ffff-ffff-ffff-ffffffffffff","System": "2","PercentageAddup": "False"}},"node14": { "RoleID": "00000000-0000-0000-0000-000000000170","UserID": "WebServices","Percentage": "1"}}}}
Union查询语句如下：
select polnum, codepath1->>'Code' as code, pid_path1->>'PartyID' as party_id from( select polnum, jsonb_path_query( payload, '$.node1[*].node2[*].node3[*].node4[*].node5[*]' ) as codepath1, jsonb_path_query( payload, '$.node1[*].node2[*].node3[*].node6[*]' ) as pid_path1 from json_table ) sub1 union select polnum, codepath2->>'Code' as code, pid_path2->>'PartyID' as party_id from( select polnum, jsonb_path_query( payload, '$.node1[*].node7[*].node8[*].node9[*]' ) as codepath2, jsonb_path_query( payload, '$.node1[*].node7[*].node10[*]' ) as pid_path2 from json_table ) sub2 union select polnum, codepath3->>'Code' as code, pid_path3->>'PartyID' as party_id from( select polnum, jsonb_path_query( payload, '$.node1[*].node11[*].node12[*].node13[*]' ) as codepath3, jsonb_path_query( payload, '$.node1[*].node11[*].node14[*]' ) as pid_path3 from json_table ) sub3
更新 - 补充执行计划
Unique (cost=1266592.80..1272296.78 rows=380265 width=138) (actual time=30354.491..30910.817 rows=530882 loops=1) Buffers: shared hit=2271534, temp read=6422 written=6443 -> Sort (cost=1266592.80..1267543.46 rows=380265 width=138) (actual time=30354.489..30775.806 rows=532900 loops=1) Sort Key: subsel1.polnum, subsel1.lob, subsel1.eventoccurredtime, ((subsel1.codepatha ->> 'Code'::text)), ((subsel1.partyidpatha ->> 'PartyID'::text)) Sort Method: external merge Disk: 37256kB Buffers: shared hit=2271534, temp read=6422 written=6443 -> Gather (cost=1000.00..1176755.61 rows=380265 width=138) (actual time=0.585..28253.046 rows=532900 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=2271534 -> Parallel Append (cost=0.00..1137729.11 rows=269355 width=138) (actual time=0.250..23892.726 rows=177633 loops=3) Buffers: shared hit=2271534 -> Subquery Scan on subsel1 (cost=0.00..377526.56 rows=126755 width=85) (actual time=0.222..12559.211 rows=131748 loops=2) Buffers: shared hit=757198 -> ProjectSet (cost=0.00..375625.24 rows=74562000 width=85) (actual time=0.219..12453.920 rows=131748 loops=2) Buffers: shared hit=757198 -> Parallel Seq Scan on json_table fp (cost=0.00..2069.62 rows=74562 width=42) (actual time=0.004..25.233 rows=63138 loops=2) Buffers: shared hit=1324 -> Subquery Scan on subsel2 (cost=0.00..377526.56 rows=126755 width=85) (actual time=0.214..12306.081 rows=101788 loops=2) Buffers: shared hit=757198 -> ProjectSet (cost=0.00..375625.24 rows=74562000 width=85) (actual time=0.211..12252.180 rows=101788 loops=2) Buffers: shared hit=757198 -> Parallel Seq Scan on json_table fp_1 (cost=0.00..2069.62 rows=74562 width=42) (actual time=0.004..26.024 rows=63138 loops=2) Buffers: shared hit=1324 -> Subquery Scan on subsel3 (cost=0.00..377526.56 rows=126755 width=85) (actual time=0.176..21849.856 rows=65829 loops=1) Buffers: shared hit=757138 -> ProjectSet (cost=0.00..375625.24 rows=74562000 width=85) (actual time=0.173..21797.195 rows=65829 loops=1) Buffers: shared hit=757138 -> Parallel Seq Scan on json_table fp_2 (cost=0.00..2069.62 rows=74562 width=42) (actual time=0.010..34.069 rows=126277 loops=1) Buffers: shared hit=1324 Planning Time: 0.178 ms Execution Time: 30949.884 ms
更新2 - 单个查询执行计划
Subquery Scan on subsel1 (cost=1000.00..391202.06 rows=126755 width=85) (actual time=0.433..10203.467 rows=263496 loops=1) Buffers: shared hit=757198 -> Gather (cost=1000.00..389300.74 rows=126755 width=85) (actual time=0.430..10095.102 rows=263496 loops=1) Workers Planned: 1 Workers Launched: 1 Buffers: shared hit=757198 -> ProjectSet (cost=0.00..375625.24 rows=74562000 width=85) (actual time=0.234..8649.662 rows=131748 loops=2) Buffers: shared hit=757198 -> Parallel Seq Scan on json_table fp (cost=0.00..2069.62 rows=74562 width=42) (actual time=0.006..12.867 rows=63138 loops=2) Buffers: shared hit=1324 Planning Time: 0.073 ms Execution Time: 10221.966 ms
性能优化方案
1. 用UNION ALL替代UNION
从执行计划可以看到，UNION的Sort和Unique步骤是主要耗时点——UNION会自动对所有结果排序去重，而如果你的三个子查询结果本身没有重复数据，直接用UNION ALL可以省去排序和去重的开销，性能会大幅提升。修改后的查询：
select polnum, codepath1->>'Code' as code, pid_path1->>'PartyID' as party_id from( select polnum, jsonb_path_query(payload, '$.node1[*].node2[*].node3[*].node4[*].node5[*]') as codepath1, jsonb_path_query(payload, '$.node1[*].node2[*].node3[*].node6[*]') as pid_path1 from json_table ) sub1 union all select polnum, codepath2->>'Code' as code, pid_path2->>'PartyID' as party_id from( select polnum, jsonb_path_query(payload, '$.node1[*].node7[*].node8[*].node9[*]') as codepath2, jsonb_path_query(payload, '$.node1[*].node7[*].node10[*]') as pid_path2 from json_table ) sub2 union all select polnum, codepath3->>'Code' as code, pid_path3->>'PartyID' as party_id from( select polnum, jsonb_path_query(payload, '$.node1[*].node11[*].node12[*].node13[*]') as codepath3, jsonb_path_query(payload, '$.node1[*].node11[*].node14[*]') as pid_path3 from json_table ) sub3
2. 优化JSON路径查询，减少全表扫描次数
当前每个子查询都要全表扫描一次json_table，三次查询就是三次全表扫描。可以改成一次扫描表，同时处理三个路径，用横向连接（LATERAL）一次性提取所有需要的数据：
SELECT polnum, code, party_id FROM json_table, LATERAL ( -- 第一条路径 SELECT j1->>'Code' AS code, j2->>'PartyID' AS party_id FROM jsonb_path_query(payload, '$.node1.node2.node3[*].node4.node5') j1, jsonb_path_query(payload, '$.node1.node2.node3[*].node6') j2 UNION ALL -- 第二条路径 SELECT j1->>'Code' AS code, j2->>'PartyID' AS party_id FROM jsonb_path_query(payload, '$.node1.node7[*].node8.node9') j1, jsonb_path_query(payload, '$.node1.node7[*].node10') j2 UNION ALL -- 第三条路径 SELECT j1->>'Code' AS code, j2->>'PartyID' AS party_id FROM jsonb_path_query(payload, '$.node1.node11.node12.node13') j1, jsonb_path_query(payload, '$.node1.node11.node14') j2 ) AS extracted_data;
注意：你的JSON路径中部分[*]是多余的（比如$.node1[*].node2[*]，node1和node2是对象而非数组），去掉这些多余的[*]可以减少路径查询的计算量。
3. 添加JSONB索引加速路径查询
如果需要频繁从这些固定路径提取数据，可以创建索引来加速查询：
- GIN索引：适合jsonb的多种查询场景，包括路径查询
CREATE INDEX idx_json_table_payload_gin ON json_table USING GIN (payload);
- 函数索引：针对特定路径创建，适合数组长度固定的场景
CREATE INDEX idx_json_table_code_paths ON json_table USING btree ( (payload #> '{node1,node2,node3,0,node4,node5,Code}'), (payload #> '{node1,node7,0,node8,node9,Code}'), (payload #> '{node1,node11,node12,node13,Code}') );
4. 预处理JSON数据（长期优化方案）
如果这个视图是频繁访问的，考虑将JSON中需要的属性提前提取到单独的列中（比如添加code、party_id列），用触发器或ETL工具定期同步数据。这样查询时直接访问普通列，彻底避免每次查询都解析JSON的开销。
内容的提问来源于stack exchange，提问作者adbdkb
相关产品推荐
相关产品推荐
