will CTE run again if we join in other CTE or with a main query?

33 Views Asked by At

I have a question regarding whether CTE in a query runs only once or every time it is getting joined.

Causing we are facing a CPU consumption issue in postgres metrics.

if it runs only once then fine. if not we need to optimize the query. is there any way in postgres SQL to make a CTE run only once?

below is the example query. removed complex logic in each of cte's for brevity and avoid confusion in question. the main CTE (i.e, current_student_temp in this case) is crucial one it needs to be used for further processing.

with 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
),

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 
 --will current_student_temp run again here? breakpoint-1
),

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 
 --will current_student_temp run again here? breakpoint-2
), 

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 
 --will current_student_temp run again here? breakpoint-3
)


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 

Any input is highly appreciated.

0

There are 0 best solutions below