PostgreSQL物化视图REFRESH MATERIALIZED VIEW执行耗时突增问题咨询

• PostgreSQL层面：
  • 开启log_statement = 'all'和log_temp_files = 0，抓取刷新时段的全量执行日志，确认是否有临时文件生成、执行计划是否和预期一致
  • 执行pg_stat_user_tables查询物化视图的死元组占比、膨胀率，执行pg_locks查看刷新操作是否存在锁等待
  • 核对work_mem、maintenance_work_mem参数配置，确认是否满足该查询的内存使用需求
• RDS层面：
  • 查看CloudWatch对应时段的CPU使用率（重点关注用户态CPU占比）、磁盘IOPS、吞吐量、队列深度指标，排除硬件资源瓶颈
  • 从RDS Performance Insights中提取刷新进程的等待事件，确认等待类型是CPU运算、磁盘IO还是其他类型
  • 核对RDS实例的网络带宽配额，确认刷新时段有没有达到带宽上限

该耗时不属于7万行小数据量的正常持久化耗时：15列纯数值/UUID类型的7万行数据总大小通常不超过20MB，正常PostgreSQL写入该量级数据的耗时不会超过1秒。
耗时突然上涨的常见原因包括：

• 基础表统计信息过期：自动ANALYZE执行异常导致统计信息不准，查询计划器对关联行数的估算偏差过大，选择了效率更低的执行计划
• 物化视图表膨胀：多次全量刷新未清理死元组，导致写入新数据时需要扫描更多碎片化的空闲空间，占用额外CPU资源
• 执行计划回退：PostgreSQL 11版本的查询计划器存在小概率的计划回退问题，原本执行高效的hash join/merge join被替换为嵌套循环（nestloop），在关联量级较大时会消耗大量CPU，和你观测到的CPU资源占比高的特征完全匹配
  你此前猜测的网络传输延迟大概率是误判，拉取全量结果慢的本质是查询在结果输出阶段的CPU消耗过高，并非网络传输瓶颈。

设置enable_nestloop = false后性能恢复，可以确认问题根因是查询计划器选择了错误的嵌套循环执行计划，重构底层查询完全可以解决该问题，可选的重构方案包括：

• 在查询中添加join提示，强制关联逻辑走hash join或merge join，避免计划器误选nestloop
• 对视图依赖的基础表定期执行手动ANALYZE，保证统计信息准确性，避免执行计划异常
• 适当调大work_mem参数，让关联运算的中间结果全部落在内存中处理，避免生成临时磁盘文件
  你观测到的少选字段的新物化视图刷新性能正常，也和该根因吻合：嵌套循环的CPU开销和每次循环加载的字段数量正相关，字段越少运算开销越低。

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