PostgreSQL 10视图查询高负载变慢性能优化咨询

这类闲时运行正常、高负载下耗时暴涨的场景，核心原因多为复杂视图的执行计划不稳定、高负载下资源不足触发磁盘IO，或者统计信息偏差导致规划器选错执行路径，可直接落地的优化方向如下：

• 改造复杂视图为物化视图：如果moa.esn_part_view依赖的基表数据实时性要求不高（允许1~5分钟延迟），直接创建物化视图替代原视图，按业务容忍的延迟周期定时刷新，同时给物化视图添加查询过滤条件对应的索引，可直接砍掉每次查询时的视图逻辑计算开销，高负载下性能提升效果最显著。
• 修正统计信息避免执行计划跳变：PostgreSQL 10默认统计信息采样率较低，复杂视图涉及多表关联时，高负载下规划器极易因统计信息不准选错关联顺序、扫描方式。先对视图依赖的所有基表执行ANALYZE VERBOSE 表名;更新统计信息；如果单表数据量超过千万级，调整对应表的统计信息采样阈值ALTER TABLE 表名 SET STATISTICS 1000;，提升统计信息精度，确保规划器生成稳定的执行计划，避免高负载下偶发走全表扫描的情况。
• 新增查询匹配的覆盖索引：如果查询有固定的WHERE过滤条件、JOIN关联列，直接创建覆盖索引，把查询需要返回的所有字段都包含在索引中，实现索引-only扫描，完全规避扫表开销。比如查询过滤条件为esn、create_time，返回字段为part_id、status，可执行CREATE INDEX CONCURRENTLY idx_esn_part_cover ON 基表名(esn, create_time, part_id, status);；PostgreSQL 10暂不支持INCLUDE语法，直接把返回字段追加到索引列末尾即可实现覆盖扫描效果，CONCURRENTLY参数不会锁表，可在生产环境直接执行。
• 固定执行路径避免关联劣化：高负载下内存不足时，嵌套循环（Nest Loop）如果关联的内表为大表，极易产生大量随机磁盘IO导致耗时暴涨。可以在查询级别临时关闭嵌套循环，强制走哈希关联：在查询开头加SET enable_nestloop = off;，确保高负载下执行计划稳定。
• 调整work_mem减少磁盘临时文件：PostgreSQL默认work_mem参数通常为4MB，查询涉及排序、哈希关联时，高负载下很容易将临时数据写到磁盘，导致耗时暴涨。可以针对这类大查询单独调整work_mem：SET work_mem = '64MB';，也可根据服务器配置全局调整，建议值为 总内存/32/最大连接数，避免内存溢出的同时减少磁盘临时文件开销。

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