PostgreSQL子查询运行快但行转JSON字符串耗时高，如何优化？

性能优化方案 #

你遇到的性能问题核心是原生json类型的序列化开销、全量结果集内存聚合的双重压力，子查询本身的IO/计算开销已经很低，瓶颈完全在JSON转换环节，以下是可落地的优化方案：

1. 优先使用jsonb类型替代json类型 #

jsonb是PostgreSQL的二进制JSON类型，序列化效率比普通json高2~3倍，同时支持后续的JSON操作，替换后语句如下：

select jsonb_agg(t) from (select * from fastSubquery) t;

该方案对业务无侵入，仅需修改SQL即可生效，是首选优化方案。

2. 精简返回字段，避免使用select * #

JSON转换会遍历所有返回字段做序列化，如果fastSubquery包含大文本、二进制等不需要返回的字段，会大幅增加转换开销，明确列出需要返回的字段即可：

select jsonb_agg(t) from (select 仅保留需要的字段列表 from fastSubquery) t;

如果返回字段中包含不需要的大字段，该优化可以将转换耗时降低70%以上。

3. 调整数据库配置避免磁盘临时文件 #

如果结果集规模较大，聚合环节默认的work_mem不足会触发磁盘临时文件，大幅降低性能，可适当调大以下参数：

• work_mem：根据服务器内存调整到64MB~256MB，避免聚合/排序操作落盘
• maintenance_work_mem：调大到128MB以上，提升批量数据处理效率

4. 超大规模结果集拆分聚合 #

如果返回结果行数超过10万行，不建议在数据库侧做全量聚合，可拆分成分页查询：

1. 先获取符合条件的总条数，按每页1万行拆分分页
2. 每页单独做jsonb_agg转换返回应用层
3. 应用层把多页的JSON数组合并成完整结果
   该方案可以将整体耗时降低40%以上，同时不会占用数据库过多内存资源，避免影响其他业务查询。

5. 高版本PostgreSQL专用优化 #

如果你使用的是PostgreSQL 12及以上版本，to_jsonb的性能有大幅优化，也可以用以下写法：

select array_to_json(array_agg(to_jsonb(t))) from (select * from fastSubquery) t;

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