                                                                                                                                                                                                                                                                                                                                                                                                QUERY PLAN                                                                                                                                                                                                                                                                                                                                                                                                 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.tbl_residence_own tro  (cost=11.49..218358.72 rows=6977 width=8) (actual time=0.837..2529.609 rows=6983 loops=1)
   Output: tro.client_id, CASE WHEN ((SubPlan 1) ~~* 'SSP%'::text) THEN COALESCE($1, $2) ELSE (SubPlan 4) END
   Buffers: shared hit=99655
   InitPlan 2 (returns $1)
     ->  Limit  (cost=4.81..5.75 rows=1 width=4) (actual time=0.507..0.507 rows=0 loops=1)
           Output: staff_employment_current.staff_id
           Buffers: shared hit=2
           ->  Subquery Scan on staff_employment_current  (cost=4.81..5.75 rows=1 width=4) (actual time=0.505..0.505 rows=0 loops=1)
                 Output: staff_employment_current.staff_id
                 Filter: (((staff_employment_current.staff_position_code)::text = 'COORD_PROP'::text) AND ((staff_employment_current.agency_project_code)::text = 'SSP'::text))
                 Rows Removed by Filter: 54
                 Buffers: shared hit=2
                 ->  Unique  (cost=4.81..5.04 rows=47 width=437) (actual time=0.250..0.434 rows=54 loops=1)
                       Output: tbl_staff_employment.staff_employment_id, tbl_staff_employment.staff_id, NULL::text, NULL::character varying(10), tbl_staff_employment.agency_project_code, tbl_staff_employment.staff_position_code, NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::integer, NULL::integer, NULL::date, NULL::date, NULL::integer, NULL::text, NULL::integer, NULL::timestamp(0) without time zone, NULL::integer, NULL::timestamp(0) without time zone, NULL::boolean, NULL::timestamp(0) without time zone, NULL::integer, NULL::text, NULL::text
                       Buffers: shared hit=2
                       ->  Sort  (cost=4.81..4.92 rows=47 width=437) (actual time=0.248..0.310 rows=54 loops=1)
                             Output: tbl_staff_employment.staff_employment_id, tbl_staff_employment.staff_id, NULL::text, NULL::character varying(10), tbl_staff_employment.agency_project_code, tbl_staff_employment.staff_position_code, NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::integer, NULL::integer, NULL::date, NULL::date, NULL::integer, NULL::text, NULL::integer, NULL::timestamp(0) without time zone, NULL::integer, NULL::timestamp(0) without time zone, NULL::boolean, NULL::timestamp(0) without time zone, NULL::integer, NULL::text, NULL::text
                             Sort Key: tbl_staff_employment.staff_id, tbl_staff_employment.staff_employment_id
                             Sort Method: quicksort  Memory: 29kB
                             Buffers: shared hit=2
                             ->  Seq Scan on public.tbl_staff_employment  (cost=0.00..3.50 rows=47 width=437) (actual time=0.014..0.153 rows=54 loops=1)
                                   Output: tbl_staff_employment.staff_employment_id, tbl_staff_employment.staff_id, NULL::text, NULL::character varying(10), tbl_staff_employment.agency_project_code, tbl_staff_employment.staff_position_code, NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::integer, NULL::integer, NULL::date, NULL::date, NULL::integer, NULL::text, NULL::integer, NULL::timestamp(0) without time zone, NULL::integer, NULL::timestamp(0) without time zone, NULL::boolean, NULL::timestamp(0) without time zone, NULL::integer, NULL::text, NULL::text
                                   Filter: ((NOT tbl_staff_employment.is_deleted) AND (tbl_staff_employment.hired_on <= ('now'::cstring)::date) AND ((tbl_staff_employment.terminated_on IS NULL) OR (tbl_staff_employment.terminated_on > ('now'::cstring)::date)))
                                   Rows Removed by Filter: 21
                                   Buffers: shared hit=2
   InitPlan 3 (returns $2)
     ->  Limit  (cost=4.81..5.75 rows=1 width=4) (actual time=0.509..0.509 rows=0 loops=1)
           Output: staff_employment_current_1.staff_id
           Buffers: shared hit=2
           ->  Subquery Scan on staff_employment_current_1  (cost=4.81..5.75 rows=1 width=4) (actual time=0.497..0.497 rows=0 loops=1)
                 Output: staff_employment_current_1.staff_id
                 Filter: (((staff_employment_current_1.staff_position_code)::text = 'MGRPROJ'::text) AND ((staff_employment_current_1.agency_project_code)::text = 'SSP'::text))
                 Rows Removed by Filter: 54
                 Buffers: shared hit=2
                 ->  Unique  (cost=4.81..5.04 rows=47 width=437) (actual time=0.244..0.428 rows=54 loops=1)
                       Output: tbl_staff_employment_1.staff_employment_id, tbl_staff_employment_1.staff_id, NULL::text, NULL::character varying(10), tbl_staff_employment_1.agency_project_code, tbl_staff_employment_1.staff_position_code, NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::integer, NULL::integer, NULL::date, NULL::date, NULL::integer, NULL::text, NULL::integer, NULL::timestamp(0) without time zone, NULL::integer, NULL::timestamp(0) without time zone, NULL::boolean, NULL::timestamp(0) without time zone, NULL::integer, NULL::text, NULL::text
                       Buffers: shared hit=2
                       ->  Sort  (cost=4.81..4.92 rows=47 width=437) (actual time=0.241..0.304 rows=54 loops=1)
                             Output: tbl_staff_employment_1.staff_employment_id, tbl_staff_employment_1.staff_id, NULL::text, NULL::character varying(10), tbl_staff_employment_1.agency_project_code, tbl_staff_employment_1.staff_position_code, NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::integer, NULL::integer, NULL::date, NULL::date, NULL::integer, NULL::text, NULL::integer, NULL::timestamp(0) without time zone, NULL::integer, NULL::timestamp(0) without time zone, NULL::boolean, NULL::timestamp(0) without time zone, NULL::integer, NULL::text, NULL::text
                             Sort Key: tbl_staff_employment_1.staff_id, tbl_staff_employment_1.staff_employment_id
                             Sort Method: quicksort  Memory: 29kB
                             Buffers: shared hit=2
                             ->  Seq Scan on public.tbl_staff_employment tbl_staff_employment_1  (cost=0.00..3.50 rows=47 width=437) (actual time=0.011..0.150 rows=54 loops=1)
                                   Output: tbl_staff_employment_1.staff_employment_id, tbl_staff_employment_1.staff_id, NULL::text, NULL::character varying(10), tbl_staff_employment_1.agency_project_code, tbl_staff_employment_1.staff_position_code, NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::character varying(10), NULL::integer, NULL::integer, NULL::date, NULL::date, NULL::integer, NULL::text, NULL::integer, NULL::timestamp(0) without time zone, NULL::integer, NULL::timestamp(0) without time zone, NULL::boolean, NULL::timestamp(0) without time zone, NULL::integer, NULL::text, NULL::text
                                   Filter: ((NOT tbl_staff_employment_1.is_deleted) AND (tbl_staff_employment_1.hired_on <= ('now'::cstring)::date) AND ((tbl_staff_employment_1.terminated_on IS NULL) OR (tbl_staff_employment_1.terminated_on > ('now'::cstring)::date)))
                                   Rows Removed by Filter: 21
                                   Buffers: shared hit=2
   SubPlan 1
     ->  Limit  (cost=0.28..9.31 rows=1 width=32) (actual time=0.114..0.114 rows=0 loops=6983)
           Output: ((program_type(tbl_reg_spc.grant_number_code))::text)
           Buffers: shared hit=39218
           ->  Index Scan using index_tbl_reg_spc_client_id on public.tbl_reg_spc  (cost=0.28..9.31 rows=1 width=32) (actual time=0.111..0.111 rows=0 loops=6983)
                 Output: (program_type(tbl_reg_spc.grant_number_code))::text
                 Index Cond: (tbl_reg_spc.client_id = tro.client_id)
                 Filter: ((NOT tbl_reg_spc.is_deleted) AND (target_date() >= tbl_reg_spc.reg_spc_date) AND (target_date() <= COALESCE(tbl_reg_spc.reg_spc_date_end, target_date())))
                 Rows Removed by Filter: 1
                 Buffers: shared hit=39218
   SubPlan 4
     ->  Limit  (cost=8.59..21.95 rows=1 width=4) (actual time=0.253..0.253 rows=0 loops=6670)
           Output: (COALESCE(lhp.staff_inspector_manual, tbl_inspection_zipcode.staff_assigned_id))
           Buffers: shared hit=60257
           ->  Nested Loop Left Join  (cost=8.59..21.95 rows=1 width=4) (actual time=0.250..0.250 rows=0 loops=6670)
                 Output: (COALESCE(lhp.staff_inspector_manual, tbl_inspection_zipcode.staff_assigned_id))
                 Buffers: shared hit=60257
                 ->  Index Scan using tbl_residence_own_client_id on public.tbl_residence_own ro  (cost=0.28..10.50 rows=1 width=5) (actual time=0.215..0.215 rows=0 loops=6670)
                       Output: ro.residence_own_id, ro.client_id, ro.housing_project_code, ro.housing_unit_code, ro.residence_date, ro.residence_date_end, ro.unit_rent_manual, ro.utility_allowance_manual, ro.is_active_manual, ro.was_received_hap, ro.was_received_compliance, ro.moved_from_code, ro.chronic_homeless_status_code, ro.lease_on_file, ro.moved_to_code, ro.departure_type_code, ro.departure_reason_code, ro.move_out_was_code, ro.returned_homeless, ro.was_deposit_returned, ro.comment_damage, ro.comment_deposit, ro.comment, ro.old_access_id, ro.old_utility_allowance, ro.added_by, ro.added_at, ro.changed_by, ro.changed_at, ro.is_deleted, ro.deleted_at, ro.deleted_by, ro.deleted_comment, ro.sys_log, ro.tenant_pays_deposit, ro.is_coordinated_entry, ro.referral_source
                       Index Cond: (ro.client_id = tro.client_id)
                       Filter: ((NOT ro.is_deleted) AND (target_date() >= ro.residence_date) AND (target_date() <= COALESCE(ro.residence_date_end, target_date())))
                       Rows Removed by Filter: 2
                       Buffers: shared hit=53918
                 ->  Hash Right Join  (cost=8.31..11.44 rows=1 width=9) (actual time=0.135..0.135 rows=1 loops=1539)
                       Output: lhp.housing_project_code, COALESCE(lhp.staff_inspector_manual, tbl_inspection_zipcode.staff_assigned_id)
                       Hash Cond: ((tbl_inspection_zipcode.zipcode)::text = (lhp.zipcode)::text)
                       Buffers: shared hit=6339
                       ->  Seq Scan on public.tbl_inspection_zipcode  (cost=0.00..2.82 rows=81 width=10) (actual time=0.003..0.055 rows=43 loops=1539)
                             Output: tbl_inspection_zipcode.inspection_zipcode_id, tbl_inspection_zipcode.zipcode, tbl_inspection_zipcode.description, tbl_inspection_zipcode.inspection_month_code, tbl_inspection_zipcode.staff_assigned_id, tbl_inspection_zipcode.comment, tbl_inspection_zipcode.added_by, tbl_inspection_zipcode.added_at, tbl_inspection_zipcode.changed_by, tbl_inspection_zipcode.changed_at, tbl_inspection_zipcode.is_deleted, tbl_inspection_zipcode.deleted_at, tbl_inspection_zipcode.deleted_by, tbl_inspection_zipcode.deleted_comment, tbl_inspection_zipcode.sys_log
                             Filter: (NOT tbl_inspection_zipcode.is_deleted)
                             Rows Removed by Filter: 0
                             Buffers: shared hit=1722
                       ->  Hash  (cost=8.29..8.29 rows=1 width=15) (actual time=0.015..0.015 rows=1 loops=1539)
                             Output: lhp.zipcode, lhp.housing_project_code, lhp.staff_inspector_manual
                             Buckets: 1024  Batches: 1  Memory Usage: 9kB
                             Buffers: shared hit=4617
                             ->  Index Scan using tbl_l_housing_project_pkey on public.tbl_l_housing_project lhp  (cost=0.28..8.29 rows=1 width=15) (actual time=0.008..0.010 rows=1 loops=1539)
                                   Output: lhp.zipcode, lhp.housing_project_code, lhp.staff_inspector_manual
                                   Index Cond: ((ro.housing_project_code)::text = (lhp.housing_project_code)::text)
                                   Filter: (NOT lhp.is_deleted)
                                   Buffers: shared hit=4617
 Planning time: 2.741 ms
 Execution time: 2538.277 ms
(90 rows)

