PostgreSQL生产环境row_number()致查询缓慢及版本升级相关问题

问题背景 #

我有一个仅含约225行数据的新数据库，仅在Ubuntu服务器的生产环境PostgreSQL中遇到查询缓慢问题，执行时间达423.460ms，但开发环境Mac的PostgreSQL执行仅需约1.5ms。

已做排查步骤 #

• 版本差异：Mac为PostgreSQL 15.3，服务器为14.8；
• 执行计划对比：服务器外层Sort的Sort Key为row_number() OVER (?)，成本和预估行数极高，而Mac为Sort Key: o.row_number；且服务器执行计划中出现别名替换（如p.kind = 'community'替代查询中的o.kind = 'community'）；
• 将服务器PostgreSQL升级至15.4后，性能大幅提升，但外层Sort仍显示row_number() OVER (?)，成本和预估行数仍高于Mac。

待解问题 #

1. 为何外层Sort的Sort Key为row_number() OVER (?)，而row_number() over ()仅在WITH CTE的第二部分使用？
2. 升级后服务器与Mac的执行计划仍存在差异的原因是什么？
3. 仅版本升级为何能解决问题？是否未触及根因？
4. 如何降低成本和预估行数，使服务器性能与Mac一致？

问题解答 #

1. 关于Sort Key显示row_number() OVER (?)的原因 #

PostgreSQL 14及更早版本的优化器在处理CTE中的窗口函数时存在解析缺陷：当CTE中定义的row_number() over ()被外层查询引用时，优化器无法正确识别这是一个已计算完成的别名，反而会重复解析窗口函数的表达式结构，导致执行计划中显示row_number() OVER (?)（?代表无法正确解析的窗口定义）。

虽然PostgreSQL 15对CTE的优化逻辑做了调整，但部分边角场景下的旧解析逻辑可能仍未完全覆盖，或者你的查询写法恰好触发了未完全修复的遗留问题，导致升级后仍能看到该现象。

2. 升级后执行计划仍有差异的原因 #

执行计划的差异由多方面因素共同导致：

• 统计信息不一致：服务器和Mac的数据库统计信息（通过ANALYZE生成）可能存在偏差，比如采样率、更新频率不同，导致优化器对数据行数、分布的预估出现差异；
• 配置参数差异：两者的PostgreSQL核心配置（如work_mem、effective_cache_size、optimizer_cost_params等）不同，优化器会根据这些参数计算不同执行路径的成本，进而选择不同计划；
• 平台底层差异：Ubuntu和Mac的CPU架构、磁盘IO性能、操作系统调度机制不同，优化器的成本估算模型会适配不同平台的硬件特性，导致计划选择差异；
• 版本细微差异：PostgreSQL 15.3和15.4之间存在优化器的小幅度调整，这些细节变化也可能导致执行计划的差异。

3. 版本升级解决性能问题的本质 #

版本升级直接修复了PostgreSQL 14中存在的CTE优化Bug，这就是性能问题的根因：

• 在14版本中，优化器默认会物化所有CTE结果，即使数据量极小，也会额外生成物化步骤，并且重复计算窗口函数，导致不必要的性能损耗；
• 15版本引入了CTE内联优化，当CTE满足内联条件时，会被直接合并到主查询中执行，避免了物化的额外开销，同时修复了窗口函数表达式的解析缺陷，减少了重复计算。

升级已经触及了核心根因，执行计划中残留的差异属于非核心的细节问题，不会影响整体性能表现。

4. 让服务器性能与Mac一致的具体措施 #

可以通过以下步骤消除性能差异、降低成本预估偏差：

1. 更新统计信息：在服务器上执行ANALYZE <目标表名>;，确保优化器获取到准确的数据分布，修正行数预估偏差；
2. 调整核心配置参数：
   • 增大work_mem（例如设置为64MB），让Sort操作在内存中完成，避免磁盘排序带来的性能损耗；
   • 将effective_cache_size设置为服务器可用内存的70%-80%，帮助优化器更准确地估算内存资源；
3. 优化查询写法：
   • 给row_number()显式指定排序键（例如基于主键或唯一索引列），即使逻辑上不需要排序，也能帮助优化器识别窗口函数的计算结果；
   • 尝试将CTE改写为子查询，触发优化器的内联逻辑，避免不必要的物化；
4. 强制优化器行为（可选）：
   • 如果优化器仍未选择最优计划，可以使用查询提示/*+ INLINE(cte_name) */强制内联指定CTE；
   • 临时设置SET enable_sort = off;测试是否能避免不必要的Sort操作（仅用于调试，不建议长期开启）；
5. 检查索引适配：确保查询中过滤条件（如kind = 'community'）有对应的索引，即使数据量小，索引也能帮助优化器更准确地预估行数和选择执行路径。

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