硬件不同但配置相同的PostgreSQL服务器执行计划及缓冲区差异问询

环境信息 #

• 服务器硬件：
  • prod：CPU 2.3GHz 4核，8GB内存
  • dev：CPU 2.6GHz 2核，8GB内存
• 数据库配置：两台服务器均部署PostgreSQL 14和PostGIS 3.2，postgresql.conf关键配置完全一致：
  • work_mem = 100MB
  • shared_buffers = 2GB
  • max_connection = 70
  • effective_cache_size = 6GB

问题现象 #

在两台服务器的相同数据库（dev为prod的完整副本）上执行同一不可修改的自动生成查询，通过EXPLAIN (ANALYZE, COSTS, BUFFERS) very_long_query分析发现性能差异显著：

• prod：查询执行时间≥6分钟
• dev：查询执行时间约45秒

性能差异的核心来源于多个Seq Scan节点，以#21节点为例：

• Seq Scan耗时：prod 38秒，dev 8秒
• Shared Buffers命中/读取：prod 1/299596，dev 8074/117548

此类存在明显差异的Seq Scan节点在prod服务器上至少有7个：#21、#120、#223、#245、#306、#339、#363

问题解答 #

1. Shared Buffers命中/读取差异的核心原因 #

Shared Buffers的命中比例取决于数据的缓存热度：

• dev作为测试环境，大概率近期执行过相同或相似查询，目标表的数据已经被缓存到PostgreSQL的shared_buffers甚至操作系统的页缓存中，因此读取时命中比例高，避免了大量磁盘IO开销。
• prod作为生产环境，承载了更多业务请求，shared_buffers被其他业务的数据占用，目标表的数据几乎不在缓存内，只能从磁盘读取，导致命中极低、磁盘读取量极高。

磁盘IO的速度远慢于内存读取，这直接导致prod的Seq Scan耗时大幅增加。

2. 是否仅由硬件配置差异导致？ #

不是。硬件配置（CPU主频、核数）会影响计算效率，但这里的性能差异核心在于缓存命中率和磁盘IO性能：

• prod虽为4核，但大部分时间处于等待磁盘IO的状态，多核优势无法发挥；dev仅2核，但数据在缓存中，CPU可以持续处理，耗时自然更短。
• 生产环境的磁盘可能承载了其他业务的IO请求，磁盘负载更高，进一步加剧了prod的读取延迟。

验证建议 #

• 在prod服务器上执行SELECT pg_prewarm('目标表名');（替换为Seq Scan涉及的表名），将目标表数据预热到shared_buffers后，重新执行查询，观察性能是否提升。
• 使用iostat等工具查看prod服务器的磁盘IO使用率，确认是否存在IO瓶颈。

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