RDS Postgres12迁Aurora后CTE运行缓慢相关配置参数问询

1. PostgreSQL针对CTE的专用配置参数说明 #

PostgreSQL 12版本确实存在专门控制CTE行为的参数：cte_materialization。
这个参数是PG12版本新增的，用于控制优化器对CTE的物化策略，可选值有三个：

• default：默认策略，只对有副作用、或者被外层查询引用多次的CTE做物化，其余情况会把CTE和外层查询合并执行
• on：强制所有CTE都做物化，先把CTE的结果集写进临时存储，再供外层查询调用
• off：强制所有CTE都不做物化，直接和外层查询合并执行

注：PG11及更早版本没有这个参数，所有CTE都会强制物化，这也是PG12版本的核心优化点之一。

2. RDS与Aurora性能差异的可能原因 #

二者同主版本但性能表现不同，核心有两个常见原因：

• 参数配置差异：AWS RDS原生PostgreSQL和Aurora PostgreSQL的默认参数模板并不完全一致，很大概率是Aurora侧的cte_materialization被设置为on，导致所有CTE强制物化，大结果集下会产生大量的读写IO，直接拉高IOPS、降低执行速度。
• 存储架构差异：Aurora使用分布式共享存储架构，和RDS原生的EBS挂载存储的IO路径、IO开销计算逻辑都有差异，同样的CTE物化场景下，Aurora产生的实际IO开销会比同配置的RDS更高，也更容易触发IOPS瓶颈。

3. 排查步骤 #

• 先核对两个实例的核心参数，执行以下SQL查看配置：

  show cte_materialization;
  show work_mem;
  show shared_buffers;
  

  重点对比cte_materialization是否一致，同时确认work_mem是否足够：如果work_mem小于CTE结果集的大小，物化时会把临时文件写落磁盘，直接导致IOPS飙升。
• 对比两个环境下同一条SQL的执行计划，执行：

  EXPLAIN ANALYZE <你的CTE查询语句>;
  

  重点检查Aurora侧的执行计划是否出现CTE Scan节点（代表CTE被物化）、是否有Sort Method: External Merge Disk:这类磁盘临时文件的标识，定位性能损耗的具体节点。
• 会话级临时验证：在Aurora的会话中先执行SET cte_materialization = off;，再运行你的CTE语句，确认性能和IOPS是否恢复到和RDS一致的水平，直接验证是否是该参数导致的问题。

4. 解决方案 #

• 如果确认是cte_materialization配置问题：
  1. 全局调整：修改Aurora的参数模板，把cte_materialization改成和原RDS一致的值（通常是default），重启实例生效。
  2. 单语句调整：如果不想修改全局配置，可以在CTE语法中显式指定物化策略，比如：

     WITH cte_name AS NOT MATERIALIZED (SELECT xxx FROM xxx)
     SELECT * FROM cte_name;
     

• 如果是work_mem不足导致的磁盘临时文件：
  适当调大work_mem的配置，建议设置为你常见的CTE结果集大小的1.2倍以上，避免临时数据落盘。
• 如果是Aurora IOPS配额不足：
  核对Aurora实例的IOPS配置是否和原RDS一致，必要时提升IOPS阈值；同时可以针对CTE的查询逻辑加覆盖索引，减少扫描的数据量，降低IO需求。

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