PostgreSQL从9.6升级至13.4后查询执行计划差异及性能下降问题

核心问题分析 #

从两个版本的执行计划差异可以直接定位问题根源是PostgreSQL 13.4实例的优化器出现了明显的判断错误：

• 9.6版本正确将baz、id的过滤条件下推到索引扫描层，通过BitmapOr快速命中符合条件的190万行，再通过topN排序取第一行，总耗时仅1s左右。
• 13.4版本仅将foo、bar作为索引查询条件，把baz、id的过滤逻辑放到了回表后的Filter层，导致需要扫描1874万条不符合时间条件的行才能找到第一个符合要求的结果，耗时直接上升到32s。

迁移遗漏的标准步骤 #

以上问题几乎都是跨大版本升级时未执行官方要求的必做步骤导致，你大概率遗漏了前两项操作：

1. 升级完成后执行全库统计信息更新 #

PostgreSQL跨大版本升级时，旧版本的统计信息格式无法直接兼容新版本，无论使用pg_dump/pg_restore还是pg_upgrade工具迁移，新实例初始状态下都没有有效的统计信息。优化器缺失表、索引的数值分布统计，就会出现成本计算错误，选错执行计划。
可先针对出问题的表执行统计信息更新验证效果：

ANALYZE VERBOSE my_table;

全库更新可直接使用系统工具：

vacuumdb --all --analyze-only -j [并行任务数]

2. 对齐新旧实例的优化器配置参数 #

PostgreSQL 9.6到13.4之间有多个默认优化器参数发生了变化，包括但不限于random_page_cost、effective_cache_size、cpu_index_tuple_cost等，如果新实例的参数没有和旧实例对齐，也会导致成本计算逻辑偏差，选错执行计划。你可以通过以下命令对比两边参数差异：

SELECT name, setting FROM pg_settings 
WHERE name IN ('random_page_cost','seq_page_cost','effective_cache_size','work_mem','cpu_tuple_cost','cpu_index_tuple_cost');

3. 全库重建索引 #

跨4个大版本升级后，旧版本生成的索引存储格式和新版本不完全兼容，可能出现索引膨胀、扫描效率下降的问题，PostgreSQL官方建议跨大版本升级后执行全库索引重建：
针对单表重建的命令为：

REINDEX TABLE CONCURRENTLY my_table;

全库重建可使用系统工具：

reindexdb --all -j [并行任务数]

临时应急方案 #

如果统计信息更新后执行计划仍未恢复，你可以通过改写查询强制优化器走Bitmap扫描逻辑，或者使用pg_hint_plan插件指定正确的执行计划。

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