Aurora Postgres 13.4升14.8后执行计划变更致性能问题咨询

安全阿岳

2026-7-15

Aurora Postgres 14.8 查询优化器变更导致SQL性能骤降问题

我们将Aurora Postgres从13.4版本升级至14.8版本后，多条SQL语句的执行计划发生变更，引发严重性能问题。现咨询：14.8版本的查询优化器是否存在相关更新？

以下为出现问题的SQL语句及两个版本的执行计划详情：

SQL 查询语句

select
    row_number() over (
    order by MIN(mpfdh.MODULE_SEQNO) asc,
    MIN(mpfdh.OPERATION_SEQNO) asc ) as sequence,
    mpfdh.pd_id as PdId ,
    case
        when SUM(mpfdh.MANDATORY_FLAG) > 0 then true
        else false
    end as SomePDsMandatory ,
    case
        when SUM(mpfdh.MANDATORY_FLAG) = COUNT(mpfdh.pd_id) then true
        else false
    end as AllPDsMandatory ,
    case
        when COUNT(*) = COUNT(mpfdh.pd_id) then true
        else false
    end as UsedByAllMainPDs ,
    BOOL_OR( case when mpfdh.DEPARTMENT = 'TST' and mpfdh.MANDATORY_FLAG = 1 then true else false end) as IsTestPD,
    STRING_AGG(p.MAINPD_ID, '; ' order by p.MAINPD_ID ) as MainPDs
from
    pdwh_ll.LOT_STATE ls
join pdwh_ll.PRODUCT p on
    p.PRODSPEC_ID = ls.PRODSPEC_ID
join pdwh_ll.MAINPD_FLOW_HIST mpfh on
    ( mpfh.mainpd_id = p.mainpd_id
    and mpfh.valid_to > current_date )
join pdwh_ll.MAINPD_FLOW_DET_HIST mpfdh
        using (mainpd_flow_hist_sk)
where
    p.DELETED_FLAG = 'N' /* AND p.STATE != 'Obsolete' */
        and ls.orig_site_name =  'FXXX'
    and p.orig_site_name = 'FXXX'
    and mpfh.orig_site_name = 'FXXX'
    and mpfdh.orig_site_name = 'FXXX'
    and ls.LOT_ID in ('xxxxx.00')
group by
    mpfdh.pd_id
order by
    MIN(mpfdh.MODULE_SEQNO) asc;

13.4版本执行计划

WindowAgg  (cost=1882.18..1947.37 rows=2173 width=125)
   ->  Sort  (cost=1882.18..1887.61 rows=2173 width=162)
         Sort Key: (min(mpfdh.module_seqno)), (min(mpfdh.operation_seqno))
         ->  GroupAggregate  (cost=1653.09..1761.74 rows=2173 width=162)
               Group Key: mpfdh.pd_id
               ->  Sort  (cost=1653.09..1658.52 rows=2173 width=53)
                     Sort Key: mpfdh.pd_id
                     ->  Nested Loop  (cost=1.98..1532.64 rows=2173 width=53)
                           ->  Nested Loop  (cost=1.41..23.19 rows=1 width=29)
                                 ->  Nested Loop  (cost=0.98..17.03 rows=1 width=21)
                                       ->  Index Scan using part_lot_state_1_orig_site_name_lot_id_key on part_lot_state_1 ls  (cost=0.57..8.59 rows=1
                                             Index Cond: ((orig_site_name = 'FXXX'::bpchar) AND ((lot_id)::text = 'xxxxx.00'::text))
                                       ->  Index Scan using part_product_1_orig_site_name_prodspec_id_key on part_product_1 p  (cost=0.41..8.44 rows=1
                                             Index Cond: ((orig_site_name = 'FXXX'::bpchar) AND ((prodspec_id)::text = (ls.prodspec_id)::text))
                                             Filter: ((deleted_flag)::text = 'N'::text)
                                 ->  Index Scan using part_mainpd_flow_hist_1_orig_site_name_mainpd_id_valid_from_key on part_mainpd_flow_hist_1 mpfh
                                       Index Cond: ((orig_site_name = 'FXXX'::bpchar) AND ((mainpd_id)::text = (p.mainpd_id)::text))
                                       Filter: (valid_to > CURRENT_DATE)
                           ->  Index Scan using part_mainpd_flow_det_hist_1_orig_site_name_mainpd_flow_hist_key on part_mainpd_flow_det_hist_1 mpfdh  (
                                 Index Cond: ((orig_site_name = 'FXXX'::bpchar) AND (mainpd_flow_hist_sk = mpfh.mainpd_flow_hist_sk))

14.8版本执行计划

WindowAgg  (cost=850385061.80..850385067.80 rows=200 width=125)
   ->  Sort  (cost=850385061.80..850385062.30 rows=200 width=162)
         Sort Key: (min(mpfdh.module_seqno)), (min(mpfdh.operation_seqno))
         ->  GroupAggregate  (cost=1.99..850385054.16 rows=200 width=162)
               Group Key: mpfdh.pd_id
               ->  Nested Loop  (cost=1.99..594399180.76 rows=10239434816 width=53)
                     Join Filter: ((mpfh.mainpd_id)::text = (p.mainpd_id)::text)
                     ->  Nested Loop  (cost=1.57..136541848.72 rows=16649356915 width=76)
                           ->  Nested Loop  (cost=1.14..49002313.72 rows=188971760 width=65)
                                 ->  Index Scan using part_mainpd_flow_det_hist_1_pd_id_idx on part_mainpd_flow_det_hist_1 mpfdh  (cost=0.57..46640158.13 rows=188971760 width=40)
                                       Filter: (orig_site_name = 'FXXX'::bpchar)
                                 ->  Materialize  (cost=0.57..8.59 rows=1 width=25)
                                       ->  Index Scan using part_lot_state_1_orig_site_name_lot_id_key on part_lot_state_1 ls  (cost=0.57..8.59 rows=1 width=25)
                                             Index Cond: ((orig_site_name = 'FXXX'::bpchar) AND ((lot_id)::text = 'xxxxx.00'::text))
                           ->  Index Scan using part_mainpd_flow_hist_1_pkey on part_mainpd_flow_hist_1 mpfh  (cost=0.43..0.45 rows=1 width=27)
                                 Index Cond: ((orig_site_name = 'FXXX'::bpchar) AND (mainpd_flow_hist_sk = mpfdh.mainpd_flow_hist_sk))
                                 Filter: (valid_to > CURRENT_DATE)
                     ->  Memoize  (cost=0.42..8.45 rows=1 width=47)
                           Cache Key: ls.prodspec_id
                           Cache Mode: logical
                           ->  Index Scan using part_product_1_orig_site_name_prodspec_id_key on part_product_1 p  (cost=0.41..8.44 rows=1 width=47)
                                 Index Cond: ((orig_site_name = 'FXXX'::bpchar) AND ((prodspec_id)::text = (ls.prodspec_id)::text))
                                 Filter: ((deleted_flag)::text = 'N'::text)

问题分析与解答

1. Postgres 14查询优化器的关键变更

Postgres 14对查询优化器做了多项调整，直接影响这类查询的包括：

连接顺序评估逻辑更新：优化器调整了表连接顺序的权重计算，更倾向于尝试不同的连接组合，若统计信息不准确，容易选择低效路径。
新增Memoize算子：用于缓存重复子查询结果，但如果优化器错误判断缓存收益，可能导致连接顺序错位。
统计信息估算规则调整：对分组、聚合操作的行数估算逻辑有更新，过时的统计信息会导致优化器做出错误决策。

2. 执行计划差异核心原因

对比两个版本的计划：

13.4版本：采用高效的小表驱动大表逻辑，先通过LOT_STATE的精准索引过滤（仅返回1行），再依次关联PRODUCT、MAINPD_FLOW_HIST，最后关联MAINPD_FLOW_DET_HIST，整体行数估算合理（2173行），执行成本极低。
14.8版本：优化器错误选择从大表MAINPD_FLOW_DET_HIST开始扫描（估算返回1.8亿行），再反向关联小表，导致后续连接行数估算暴增至102亿，执行成本飙升。这大概率是统计信息过时，或是优化器对新连接顺序的评估出现偏差导致的。

3. 解决建议

更新统计信息：执行ANALYZE pdwh_ll.MAINPD_FLOW_DET_HIST;及其他关联表的ANALYZE操作，确保优化器获取最新的数据分布。
强制连接顺序：在SQL中加入/*+ LEADING(ls p mpfh mpfdh) */提示，强制优化器沿用13.4版本的高效连接顺序：
```
select /*+ LEADING(ls p mpfh mpfdh) */
    row_number() over (...) as sequence,
    -- 其余SQL内容不变
```
禁用Memoize算子：若Memoize是问题诱因，可临时执行set enable_memoize = off;，或在SQL中加入/*+ NO_MEMOIZE */提示。
检查索引有效性：确认MAINPD_FLOW_DET_HIST上的part_mainpd_flow_det_hist_1_orig_site_name_mainpd_flow_hist_key索引状态正常，能被优化器正确识别。
调整优化器参数：临时设置optimizer_join_order = greedy;（Postgres 14默认是genetic），测试是否能恢复原有执行计划。

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

Aurora Postgres 13.4升14.8后执行计划变更致性能问题咨询

SQL 查询语句 #

13.4版本执行计划 #

14.8版本执行计划 #

问题分析与解答 #