explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fRel

Settings
# exclusive inclusive rows x rows loops node
1. 41.256 41.256 ↓ 0.0 0 1

CTE Scan on t mc (cost=63,129.17..63,514.24 rows=1 width=16) (actual time=41.256..41.256 rows=0 loops=1)

  • Output: mc.id, mc.clinic_id, mc.close_date, mc.open_date
  • Filter: (NOT (SubPlan 2))
  • Buffers: shared hit=26415
2.          

CTE t

3. 0.362 41.254 ↓ 0.0 0 1

Nested Loop (cost=0.87..63,129.17 rows=1 width=16) (actual time=41.254..41.254 rows=0 loops=1)

  • Output: mc_1.id, mc_1.clinic_id, mc_1.close_date, mc_1.open_date
  • Buffers: shared hit=26415
4. 22.614 22.614 ↓ 82.3 247 1

Index Scan using new_cases_pkey on billing.fin_bill_cases fbc (cost=0.43..62,716.97 rows=3 width=622) (actual time=0.030..22.614 rows=247 loops=1)

  • Output: fbc.case_id, fbc.bill_id, fbc.uid, fbc.open_date, fbc.close_date, fbc.case_type_id, fbc.case_type_code, fbc.case_mode_id, fbc.admission_type_id, fbc.repeat_count_code, fbc.care_regimen_id, fbc.care_regimen_code, fbc.care_level_id, fbc.care_level_code, fbc.care_providing_form_code, fbc.provision_condition_code, fbc.ref_clinic_code, fbc.ref_clinic_code_oms, fbc.payment_method_code, fbc.injury_code, fbc.init_goal_id, fbc.init_goal_code_arr, fbc.main_diagnosis_id, fbc.main_diagnosis_code, fbc.disease_type_code, fbc.related_diagnosis_arr, fbc.complication_diagnosis_arr, fbc.step_cnt, fbc.patient_id, fbc.representative_id, fbc.id_pac, fbc.patient_age, fbc.new_born, fbc.new_born_part, fbc.pat_os_sluch, fbc.rep_os_sluch, fbc.pat_birthweight, fbc.birthweight, fbc.item_id_arr, fbc.belonging_type, fbc.active_policy_id, fbc.first_id, fbc.first_diagnosis_id, fbc.first_diagnosis_code, fbc.first_admission_date, fbc.first_outcome_date, fbc.last_id, fbc.last_result_code, fbc.last_result_id, fbc.last_is_closed, fbc.last_care_result, fbc.last_mes_code, fbc.last_csg_code, fbc.last_vmp_type_code, fbc.last_vmp_method_code, fbc.last_outcome_date, fbc.last_outcome_id, fbc.last_department_code, fbc.last_department_name, fbc.last_hsp_department_code, fbc.last_hsp_department_name, fbc.last_speciality_code_arr, fbc.last_doctor_code, fbc.last_doctor_snils, fbc.last_profile_code, fbc.region_data, fbc.real_case_id
  • Index Cond: (fbc.bill_id = 5628)
  • Filter: ((fbc.open_date <= fbc.close_date) AND (fbc.case_type_id = 2) AND (fbc.care_regimen_id = ANY ('{3,4,5}'::integer[])))
  • Rows Removed by Filter: 22814
  • Buffers: shared hit=18232
5. 18.278 18.278 ↓ 0.0 0 247

Index Scan using idx_mc_case on public.mc_case mc_1 (cost=0.43..137.39 rows=1 width=24) (actual time=0.074..0.074 rows=0 loops=247)

  • Output: mc_1.id, mc_1.clinic_id, mc_1.close_date, mc_1.open_date, mc_1.patient_id, mc_1.funding_id
  • Index Cond: (mc_1.patient_id = fbc.patient_id)
  • Filter: ((mc_1.open_date <= mc_1.close_date) AND (mc_1.care_regimen_id = 2) AND (mc_1.id <> fbc.real_case_id) AND (daterange(mc_1.open_date, COALESCE(mc_1.close_date, 'infinity'::date), '()'::text) && daterange(fbc.open_date, fbc.close_date, '()'::text)) AND (((fbc.region_data -> 'funding_id'::text))::integer = mc_1.funding_id))
  • Rows Removed by Filter: 30
  • Buffers: shared hit=8183
6.          

SubPlan (for CTE Scan)

7. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.27..385.05 rows=1 width=0) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.99..373.33 rows=36 width=7) (never executed)

  • Output: ss.code
9. 0.000 0.000 ↓ 0.0 0

Index Scan using sr_srv_rendered_md_case_ix on public.sr_srv_rendered ssr (cost=0.56..68.95 rows=36 width=4) (never executed)

  • Output: ssr.service_id
  • Index Cond: (ssr.md_case_id = mc.id)
10. 0.000 0.000 ↓ 0.0 0

Index Scan using sr_service_pk on public.sr_service ss (cost=0.42..8.45 rows=1 width=11) (never executed)

  • Output: ss.id, ss.code
  • Index Cond: (ss.id = ssr.service_id)
  • Filter: (ss.org_id <> mc.clinic_id)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using mari_kmu_code on billing.mari_kmu mk (cost=0.29..0.32 rows=1 width=7) (never executed)

  • Output: mk.code
  • Index Cond: ((mk.code)::text = (ss.code)::text)
  • Filter: ((COALESCE(mc.close_date, mc.open_date) >= COALESCE(mk.from_dt, '-infinity'::date)) AND (COALESCE(mc.close_date, mc.open_date) <= COALESCE(mk.to_dt, 'infinity'::date)) AND (mk.pos = 11))
Planning time : 5.645 ms
Execution time : 41.361 ms