相同数据结构下PostgreSQL查询生产较本地慢百倍问题排查

核心结论 #

从两个环境的执行计划可以明确，PostgreSQL内核层面的查询执行效率生产环境远优于本地（生产执行仅需129ms，本地需要2.9s），8分钟的耗时完全来自结果传输、写入文件环节，和数据库查询本身无关。

可能的瓶颈点与排查方案 #

• 网络传输瓶颈
  如果你是在本地设备通过远程连接访问生产PostgreSQL实例，再将结果写入本地磁盘，12万行大体积结果的传输耗时会占绝大多数。排查方式：直接登录生产服务器本地执行pgsql命令，将结果写入服务器本地磁盘，若耗时降至秒级即可确认是网络问题。
• 磁盘IO性能受限
  4GB规格的生产实例通常配备的是低阶云盘，存在IOPS、吞吐量限制，若查询结果体积达数百MB以上，写入会被限速。此外如果写入路径是网络挂载盘（NAS、共享存储），IO性能会进一步下降。可以执行导出时通过iostat工具查看磁盘IO使用率、iowait指标验证。
• 客户端写入配置不合理
  默认的pgsql交互式输出是逐行刷新缓冲，且带格式化排版，会大幅降低写入效率。建议改用\copy命令做导出：

  \copy (select * from product p inner join product_manufacturers pm on pm.product_id = p.id inner join manufacturers m on pm.manufacturer_id = m.id inner join brand b on p.brand_id = b.id inner join tags t on t.product_id = p.id inner join groups g on g.manufacturer_id = pm.id inner join group_options gp on g.id = gp.group_id inner join images i on i.product_id = p.id where pm.enabled = true and pm.available = true and pm.launched = true and p.available = true and p.enabled = true and p.id in (49, 77, 6, 12, 36) order by b.id) to '/你的本地路径/result.csv' with csv;
  

  该命令采用批量写入逻辑，且跳过不必要的输出格式化，效率提升可达数十倍。
• 系统资源抢占
  执行导出时生产实例可能有其他任务占用IO/CPU资源，比如定时备份、慢查询、日志归档等，导致写入任务排队等待资源，可以通过top、iotop工具查看实时资源占用情况确认。

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