PostgreSQL中CTE多次引用是否重复执行及优化方案咨询

一、CTE被多次引用时是否会重复执行？ #

是的，你的示例里current_student_temp会被重复执行。

PostgreSQL的普通CTE（不带MATERIALIZED关键字）默认是优化屏障——优化器不会把它的逻辑合并到引用它的查询中。尤其是你的CTE里用到了ROW_NUMBER()这类窗口函数，优化器完全不会尝试内联它。这意味着每次在race_cd_temp、citizenship_temp、gender_temp这些CTE，或者主查询里引用current_student_temp，PostgreSQL都会重新跑一遍这个CTE的完整查询逻辑，也就是你标注的breakpoint-1、2、3，加上主查询里的引用，前后会执行四次，重复计算直接拉高了CPU占用。

二、如何让CTE仅执行一次？ #

有两种实用方案：

1. 使用物化CTE（推荐）

在定义CTE时加上MATERIALIZED关键字，PostgreSQL会先把这个CTE的查询结果计算出来，临时存储在磁盘或内存里，后续所有引用都会直接复用这个物化后的结果，不会重复执行原查询。修改后的current_student_temp定义如下：

with current_student_temp as materialized (
select * from 
 (select sd.student_id ,sd.school_id,
  sd.first_name ,sd.last_name ,
 ROW_NUMBER() 
 OVER (PARTITION by sd.student_id 
       ORDER BY sd.school_id ) AS rowNum
 from basics.student_appt_details sd
 )school_appts_for_student
where rowNum=1
),

注意：物化CTE会占用临时存储，如果结果集特别大，需要权衡存储资源和CPU消耗，但针对你CPU过高的场景，这个方案的收益通常很明显。

2. 用临时表存储CTE结果

先把current_student_temp的查询结果写入临时表，后续所有查询都引用这个临时表，同样能保证原查询只执行一次：

-- 先创建临时表并插入数据
create temporary table current_student_temp as
select * from 
 (select sd.student_id ,sd.school_id,
  sd.first_name ,sd.last_name ,
 ROW_NUMBER() 
 OVER (PARTITION by sd.student_id 
       ORDER BY sd.school_id ) AS rowNum
 from basics.student_appt_details sd
 )school_appts_for_student
where rowNum=1;

-- 后续的CTE和主查询直接引用临时表即可
with race_cd_temp as (
select rc.student_id ,rc.race_cd  as race
 from basics.race_details rc 
 inner join current_student_temp cst on cst.student_id = rc.student_id 
),
citizenship_temp as (
select ct.student_id ,ct.citizenship  as citizenship
 from basics.citizenship_details ct 
 inner join current_student_temp cst on cst.student_id = ct.student_id 
), 
gender_temp as (
select gt.student_id ,gt.gender  as gender
 from basics.gender_details gt 
 inner join current_student_temp cst on cst.student_id = gt.student_id 
)
select * from basics.person
left join current_student_temp cst on cst.student_id = person.student_id 
left join race_cd_temp rct on rct.student_id = person.student_id 
left join citizenship_temp ct on ct.student_id = person.student_id 
left join gender_temp gt on gt.student_id = person.student_id 

临时表在会话结束后会自动销毁，不需要手动清理，适合一次性的查询需求。

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