PostgreSQL 11报使用陈旧统计信息错误查询变慢的原因及解决办法

LOG : using stale statistics instead of current ones because stats collector is not responding

• 统计收集器进程通信异常：PostgreSQL 11版本的统计收集器（stats collector）是独立后台进程，依赖UDP本地套接字和其他后台进程/服务进程通信。如果本地环回接口配置异常、系统UDP缓冲区过小导致消息丢包，或者进程被系统OOM杀手终止、CPU/内存资源耗尽卡住，都会导致统计收集器无法响应统计更新请求。
• 统计临时目录IO或权限异常：统计收集器会将收集到的统计数据写入pg_stat_tmp目录下的临时文件，如果该目录所在磁盘IO利用率长期饱和、inode耗尽，或者目录权限被误修改导致postgres用户无读写权限，就会导致统计数据无法正常更新，只能读取过期的历史统计数据。
• 统计相关参数配置过载：如果开启了过多非必要的统计采集项（如track_io_timing、track_functions = all等），会大幅提升统计收集器的负载，高并发场景下很容易出现进程响应超时。
• 查询变慢的关联逻辑：PostgreSQL优化器生成执行计划完全依赖统计数据做成本估算，如果使用过时的统计数据，很容易生成错误的执行计划（如选错索引、关联顺序错误、行数估算偏差过大），直接导致查询执行效率骤降。

临时快速修复（无需重启数据库实例） #

• 手动重启统计收集器进程：执行ps aux | grep postgres | grep stats找到统计收集器进程PID，执行kill -TERM <stats_collector_pid>，PostgreSQL会自动拉起新的统计收集器进程，短时间内即可恢复统计数据更新。
• 清理pg_stat_tmp目录下的过期残留文件，避免无效文件占用IO资源。

永久修复方案 #

• 优化系统资源配置：
  • 检查pg_stat_tmp目录所在磁盘的IO性能，若长期IO利用率超过80%，可以将stats_temp_directory参数配置到tmpfs内存文件系统，大幅降低统计数据写入的IO开销。
  • 调大系统UDP缓冲区参数：修改/etc/sysctl.conf文件，新增如下配置：

    net.core.rmem_default = 262144
    net.core.rmem_max = 4194304
    net.core.wmem_default = 262144
    net.core.wmem_max = 4194304
    

    执行sysctl -p生效，避免UDP消息丢包。
• 修正目录权限配置：执行chown -R postgres:postgres <pg_stat_tmp目录绝对路径> && chmod 700 <pg_stat_tmp目录绝对路径>，确保postgres用户对该目录有完整读写权限。
• 优化统计采集参数：如果不需要细粒度的运行统计，可以关闭非必要的统计项，仅保留必要的track_counts = on用于优化器统计，降低统计收集器负载。
• 排查系统资源限制：检查postgres用户的ulimit配置，确保open files、max user processes等参数配置足够，避免触发资源限制导致进程被阻塞。
• 条件允许的情况下建议升级到PostgreSQL 12及以上版本，该版本重构了统计收集器架构，改用共享内存替代UDP通信，从根本上解决了统计收集器无响应的问题。

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