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.