PostgreSQL查询性能异常排查：复杂与简单查询耗时过长

第一个查询（多表关联+JSON聚合+标量子查询）耗时原因 #

• 关联操作无索引支撑：如果prj_projet、prj_projet_group、utl_utilisateur_groupe之间的关联字段（如projet_id、group_id、utilisateur_id）未建立索引，会触发全表扫描。当返回大量数据时，关联过程的磁盘IO和计算量会急剧上升，直接拉长执行时间。
• JSON聚合的CPU/内存开销：json_agg()需要逐行构建JSON对象，当结果集很大时，这个过程会持续占用CPU和内存，成为性能瓶颈。如果数据量远超内存缓存，还会产生磁盘交换，进一步拖慢速度。
• 标量子查询重复执行：如果标量子查询是行级触发（即主查询每返回一行就执行一次子查询），当主查询返回数万甚至数十万行时，相当于重复执行成千上万次子查询，累积的耗时会非常可观。
• 结果集过大的传输/处理开销：即使表结构简单，大量数据的读取、内存中处理以及网络传输（如果是远程连接）都会消耗大量时间，尤其是数据不在PostgreSQL缓存中时，磁盘IO的耗时会占比很高。

第二个查询（多表关联分组）耗时原因 #

• 分组操作无索引优化：如果分组字段未建立索引，PostgreSQL需要先全表扫描获取数据，再通过排序或哈希分组完成聚合。当数据量较大时，排序的磁盘IO或哈希表的内存占用都会成为瓶颈。
• 连接策略不合理：执行计划若选择了低效的连接方式（比如在大表上使用嵌套循环连接），或连接顺序错误导致先处理大表，会产生庞大的中间结果集，后续分组操作的开销也会随之暴涨。
• 统计信息过时：如果表的统计信息未及时更新，优化器会基于错误的数据分布选择执行计划，比如低估数据量而选择不合适的连接/分组策略，导致实际执行效率极低。
• 缺少覆盖索引：如果查询需要的字段无法通过索引直接获取，必须回表读取数据，会额外增加磁盘IO开销。覆盖索引可以让数据库直接从索引中拿到所有需要的数据，避免回表。

优化建议 #

• 为关联字段、分组字段创建联合索引，比如prj_projet_group(projet_id, group_id)、utl_utilisateur_groupe(group_id, utilisateur_id)，减少全表扫描的概率。
• 将标量子查询改写为JOIN操作，避免重复执行子查询。
• 若业务允许，使用jsonb_agg()替代json_agg()，JSONB的聚合性能通常更优。
• 定期执行ANALYZE命令更新表统计信息，让优化器生成更合理的执行计划。
• 检查执行计划中的Seq Scan节点，针对对应的表添加索引优化。

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