QUERY PLAN                                                                                                                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=387141.77..427213.64 rows=12203 width=16)
   ->  Nested Loop Left Join  (cost=387141.34..421113.48 rows=12203 width=27)
         ->  Hash Right Join  (cost=387140.91..413114.65 rows=12203 width=33)
               Hash Cond: (cr.ctc_id = c.ctc_id)
               ->  Seq Scan on ctc_registry cr  (cost=0.00..21852.25 rows=1066525 width=11)
               ->  Hash  (cost=386988.37..386988.37 rows=12203 width=28)
                     ->  Hash Join  (cost=257008.52..386988.37 rows=12203 width=28)
                           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)))
                           ->  Bitmap Heap Scan on ctc c  (cost=3409.61..128585.92 rows=12598 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))
                           ->  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
                             ->  Nested Loop Semi Join  (cost=2130.07..196041.01 rows=12424 width=6)
                                   ->  Bitmap Heap Scan on ctc  (cost=2129.64..121538.44 rows=12424 width=6)
                                         Recheck Cond: (behavior_icdo3 = '2'::numeric)
                                         Filter: ((NOT deleted) AND ((sequence_number)::text = '01'::text))
                                         ->  Bitmap Index Scan on xie4ctc  (cost=0.00..2126.54 rows=114948 width=0)
                                               Index Cond: (behavior_icdo3 = '2'::numeric)
                                   ->  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 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)
   ->  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)