QUERY PLAN                                                                                                                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=215599.42..373713.81 rows=12203 width=16)
   ->  Hash Join  (cost=215598.99..367613.65 rows=12203 width=27)
         Hash Cond: (c.pat_id = p.pat_id)
         Join Filter: (((c.sequence_number)::text = '00'::text) OR (((c.sequence_number)::text = '02'::text) AND (hashed SubPlan 1)))
         ->  Gather  (cost=4410.46..151621.59 rows=12598 width=27)
               Workers Planned: 2
               ->  Nested Loop Left Join  (cost=3410.46..149361.79 rows=5249 width=27)
                     ->  Nested Loop Left Join  (cost=3410.03..145921.17 rows=5249 width=33)
                           ->  Parallel Bitmap Heap Scan on ctc c  (cost=3409.61..125274.28 rows=5249 width=28)
                                 Recheck Cond: (((primary_site)::text >= 'C500'::text) AND ((primary_site)::text <= 'C509'::text))
                                 Filter: ((((sequence_number)::text = '00'::text) OR ((sequence_number)::text = '02'::text)) AND (date_of_diagnosis_mm >= (CASE WHEN (date_of_diagnosis_yyyy = '2006'::numeric) THEN 7 ELSE 1 END)::numeric) AND (date_of_diagnosis_mm <= (CASE WHEN (date_of_diagnosis_yyyy = '2015'::numeric) THEN 6 ELSE 12 END)::numeric))
                                 ->  Bitmap Index Scan on x_ctc_p_site  (cost=0.00..3406.46 rows=162203 width=0)
                                       Index Cond: (((primary_site)::text >= 'C500'::text) AND ((primary_site)::text <= 'C509'::text))
                           ->  Index Scan using ctc_registry_pkey on ctc_registry cr  (cost=0.43..3.93 rows=1 width=11)
                                 Index Cond: (c.ctc_id = ctc_id)
                     ->  Index Scan using xie2facility_admission on facility_admission fa  (cost=0.43..0.65 rows=1 width=11)
                           Index Cond: (ctc_id = cr.ctc_id)
                           Filter: (fac_id = cr.follow_up_hospital)
         ->  Hash  (cost=41948.71..41948.71 rows=896171 width=9)
               ->  Seq Scan on patient p  (cost=0.00..41948.71 rows=896171 width=9)
         SubPlan 1
           ->  Gather  (cost=1000.43..153630.62 rows=12424 width=6)
                 Workers Planned: 2
                 ->  Nested Loop Semi Join  (cost=0.43..151388.22 rows=5177 width=6)
                       ->  Parallel Seq Scan on ctc  (cost=0.00..120343.49 rows=5177 width=6)
                             Filter: ((NOT deleted) AND (behavior_icdo3 = '2'::numeric) AND ((sequence_number)::text = '01'::text))
                       ->  Index Scan using xie2ctc on ctc ctc_1  (cost=0.43..8.60 rows=1 width=6)
                             Index Cond: (pat_id = ctc.pat_id)
                             Filter: (NOT deleted)
   ->  Index Only Scan using idx_pri_pifi on patient_reference_id pr  (cost=0.43..0.49 rows=1 width=11)
         Index Cond: ((pat_id = p.pat_id) AND (fac_id = cr.follow_up_hospital))
(31 rows)