Limit (cost=10248.11..10248.81 rows=1 width=99) CTE t -> Unique (cost=10202.38..10202.40 rows=1 width=92) -> Sort (cost=10202.38..10202.38 rows=1 width=92) Sort Key: t_2.facility_key, t_2.traineeid, e.ndc_emp_id, t_2.facility_eod, (CASE WHEN ((ltia.tia_code ~~ 'CPCIT%'::text) AND (ltia.tia_code <> 'TX-INTRA-FACILITY'::text)) T HEN 'CPC-IT'::text WHEN (ltia.tia_code = 'TX-INTRA-FACILITY'::text) THEN 'INTRA-FACILITY'::text ELSE ltia.tia_code END), t_2.trainee_start_date, (CASE WHEN (t_2.devstatus_date < t_2.trainee _start_date) THEN t_2.trainee_start_date ELSE t_2.devstatus_date END), (CASE WHEN ((NOT lds.tempstat) AND (lds.devstatusid <> 1)) THEN 'Did Not Complete'::text WHEN lds.tempstat THEN 'In Pr ogress'::text ELSE 'Completed'::text END), (CASE WHEN (t_2.devstatus_date < t_2.trainee_start_date) THEN 0 ELSE (t_2.devstatus_date - t_2.trainee_start_date) END) -> Gather (cost=6302.90..10202.37 rows=1 width=92) Workers Planned: 1 -> Nested Loop (cost=5302.90..9202.27 rows=1 width=92) -> Nested Loop (cost=5302.76..9201.91 rows=2 width=33) -> Nested Loop (cost=5302.34..9201.37 rows=1 width=33) -> Nested Loop (cost=5302.21..9201.21 rows=1 width=37) -> Hash Join (cost=5302.07..9201.05 rows=1 width=41) Hash Cond: (t_2.facility_key = history_facility_level.facility_key) -> Hash Join (cost=5257.47..8829.54 rows=86445 width=41) Hash Cond: (ts.traineeid = t_2.traineeid) -> Parallel Seq Scan on trainee_stage ts (cost=0.00..2383.45 rows=86445 width=12) -> Hash (cost=4776.18..4776.18 rows=38503 width=29) -> Hash Join (cost=2272.26..4776.18 rows=38503 width=29) Hash Cond: (h.explid = el.explid) -> Hash Join (cost=2271.10..4612.76 rows=38503 width=33) Hash Cond: (t_2.hiresourceid = h.hiresourceid) -> Hash Join (cost=2269.25..4498.57 rows=38503 width=33) Hash Cond: (t_2.devstatusid = lds.devstatusid) -> Hash Join (cost=2267.62..4380.57 rows=38503 width=32) Hash Cond: (t_2.devstatus_date = dd2.caldate) -> Hash Join (cost=1570.89..3154.43 rows=38503 width=32) Hash Cond: (t_2.facility_eod = dd1.caldate) -> Hash Join (cost=874.16..1928.28 rows=38503 width=32) Hash Cond: (t_2.ntdid = e.ntdid) -> Seq Scan on trainee t_2 (cost=0.00..953.03 rows=38503 width=32) -> Hash (cost=574.07..574.07 rows=24007 width=8) -> Seq Scan on ntd_employee e (cost=0.00..574.07 rows=24007 width=8) -> Hash (cost=472.89..472.89 rows=17907 width=4) -> Index Only Scan using date_dim_caldate_idx on date_dim dd1 (cost=0.29..472.89 rows=17907 width=4) -> Hash (cost=472.89..472.89 rows=17907 width=4) -> Index Only Scan using date_dim_caldate_idx on date_dim dd2 (cost=0.29..472.89 rows=17907width=4) -> Hash (cost=1.28..1.28 rows=28 width=5) -> Seq Scan on status_dev lds (cost=0.00..1.28 rows=28 width=5) -> Hash (cost=1.38..1.38 rows=38 width=8) -> Seq Scan on hire_source h (cost=0.00..1.38 rows=38 width=8) -> Hash (cost=1.07..1.07 rows=7 width=4) -> Seq Scan on experience_label el (cost=0.00..1.07 rows=7 width=4) -> Hash (cost=44.59..44.59 rows=1 width=12) -> Hash Join (cost=26.70..44.59 rows=1 width=12) Hash Cond: ((upper(f.facility_type_number) = upper(lft.facility_type_number)) AND (upper(f.facility_type_number_desc) = upper(lft.facility_type_desc))) -> Nested Loop (cost=25.30..43.14 rows=3 width=32) Join Filter: (f.facility_key = history_facility_level.facility_key) -> Hash Join (cost=25.02..40.29 rows=3 width=8) Hash Cond: ((hfl.facility_key = history_facility_level.facility_key) AND (hfl.facility_atc_level_start_date = (max(history_facility_level.facility_atc_level_start_date)))) -> Seq Scan on history_facility_level hfl (cost=0.00..12.08 rows=608 width=8) -> Hash (cost=19.08..19.08 rows=396 width=8) -> HashAggregate (cost=15.12..19.08 rows=396 width=8) Group Key: history_facility_level.facility_key -> Seq Scan on history_facility_level (cost=0.00..12.08 rows=608 width=8) -> Index Scan using facility_dim_facility_key_key on facility_dim f (cost=0.28..0.94 rows=1 width=24) Index Cond: (facility_key = hfl.facility_key) -> Hash (cost=1.16..1.16 rows=16 width=21) -> Seq Scan on facility_type lft (cost=0.00..1.16 rows=16 width=21) -> Index Only Scan using training_stage_pkey on training_stage lts (cost=0.14..0.16 rows=1 width=4) Index Cond: (stageid = ts.stageid) -> Index Only Scan using stage_status_pkey on status_stage lss (cost=0.13..0.15 rows=1 width=4) Index Cond: (stagestatusid = ts.stagestatusid) -> Index Only Scan using trainee_instance_attributes_traineeid_tiaid_key on trainee_instance_attributes tia (cost=0.42..0.50 rows=4 width=8) Index Cond: (traineeid = ts.traineeid) -> Index Scan using training_instance_attribute_pkey on training_instance_attribute ltia (cost=0.14..0.17 rows=1 width=17) Index Cond: (tiaid = tia.tiaid) Filter: ((tia_code = 'NEWHIRE'::text) OR (tia_code ~~ 'CPCIT%'::text) OR (tia_code = 'TX-INTRA-FACILITY'::text)) -> Nested Loop (cost=45.71..46.41 rows=1 width=99) Join Filter: ((t.facility_key = t_1.facility_key) AND (t.devstatus_date >= CASE WHEN (((max(pef.facility_type_start_date)) IS NOT NULL) AND ((max(pef.facility_type_start_date)) > ( (max(t_1.devstatus_date)) - '1 mon'::interval))) THEN ((max(pef.facility_type_start_date)))::timestamp without time zone ELSE ((max(t_1.devstatus_date)) - '1 mon'::interval) END)) -> Nested Loop Left Join (cost=45.71..46.36 rows=1 width=19) Join Filter: (pef.facility_key = t_1.facility_key) -> GroupAggregate (cost=44.35..44.42 rows=1 width=15) Group Key: t_1.facility_key, fd.facility_code -> Incremental Sort (cost=44.35..44.39 rows=2 width=15) Sort Key: t_1.facility_key, fd.facility_code Presorted Key: t_1.facility_key -> Nested Loop Left Join (cost=28.89..44.34 rows=1 width=15) -> CTE Scan on t t_1 (cost=0.00..0.03 rows=1 width=8) Filter: (((status = 'Completed'::text) OR (status = 'Did Not Complete'::text)) AND (devstatus_date <= CURRENT_DATE)) -> Hash Join (cost=28.89..44.30 rows=1 width=11) Hash Cond: (hft.facility_key = fd.facility_key) -> HashAggregate (cost=20.58..27.39 rows=681 width=8) Group Key: hft.facility_key -> Seq Scan on history_facility_type hft (cost=0.00..18.46 rows=846 width=4) -> Hash (cost=8.30..8.30 rows=1 width=11) -> Index Scan using facility_dim_facility_key_key on facility_dim fd (cost=0.28..8.30 rows=1 width=11) Index Cond: (facility_key = t_1.facility_key) -> HashAggregate (cost=1.36..1.54 rows=18 width=8) Group Key: pef.facility_key -> Seq Scan on ppt_exception_facilities pef (cost=0.00..1.27 rows=18 width=8) Filter: (facility_type_start_date < CURRENT_DATE) -> CTE Scan on t (cost=0.00..0.03 rows=1 width=92) Filter: (devstatus_date <= CURRENT_DATE) (93 rows)