explain.depesz.com

PostgreSQL's explain analyze made readable

Result: As8t

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1,623.532 ↓ 0.0 0 1

Nested Loop Left Join (cost=121,305.43..121,887.02 rows=1 width=82) (actual time=1,623.532..1,623.532 rows=0 loops=1)

  • Output: fbc.real_case_id, fbc.item_id_arr, ('№'::text || (COALESCE(mc.uid, 'без номера'::character varying))::text), mc.id, mc.clinic_id, mc.open_date, mc.close_date
  • Filter: (((NOT (SubPlan 2)) AND (NOT (COALESCE(fbs.srv_code_arr, '{0}'::character varying[]) && '{023001,023002,023014,023017,123001,123002,123014,123017}'::character varying[]))) OR (COALESCE(mari_get_diagnos_class(fbc.main_diagnosis_id), 0) = COALESCE(mari_get_diagnos_class(mcd.diagnos_id), '-1'::integer)))
  • Buffers: shared hit=230558
2. 0.002 1,623.532 ↓ 0.0 0 1

Nested Loop (cost=121,304.99..121,876.28 rows=1 width=96) (actual time=1,623.532..1,623.532 rows=0 loops=1)

  • Output: fbc.real_case_id, fbc.item_id_arr, fbc.main_diagnosis_id, fbs.srv_code_arr, mc.uid, mc.id, mc.clinic_id, mc.open_date, mc.close_date, mc.main_diagnos_id
  • Join Filter: ((1021840 = mc.clinic_id) OR (NOT (COALESCE(fbs.srv_code_arr, '{0}'::character varying[]) && '{023012,123012,023013,123013,023004,123004}'::character varying[])))
  • Buffers: shared hit=230558
3. 51.683 1,623.530 ↓ 0.0 0 1

Merge Join (cost=121,304.00..121,857.41 rows=1 width=68) (actual time=1,623.530..1,623.530 rows=0 loops=1)

  • Output: fbc.real_case_id, fbc.item_id_arr, fbc.case_id, fbc.main_diagnosis_id, mc.uid, mc.id, mc.clinic_id, mc.open_date, mc.close_date, mc.main_diagnos_id
  • Merge Cond: ((fbc.patient_id = mc.patient_id) AND ((((fbc.region_data -> 'funding_id'::text))::integer) = mc.funding_id))
  • Join Filter: ((mc.id <> fbc.real_case_id) AND (daterange(mc.open_date, COALESCE(mc.close_date, 'infinity'::date), '()'::text) && daterange(fbc.open_date, fbc.close_date, '[]'::text)) AND ((1021840 = mc.clinic_id) OR (NOT (SubPlan 1))))
  • Rows Removed by Join Filter: 32
  • Buffers: shared hit=230558
4. 0.020 0.067 ↑ 123.6 7 1

Sort (cost=11,986.29..11,988.45 rows=865 width=659) (actual time=0.061..0.067 rows=7 loops=1)

  • Output: fbc.real_case_id, fbc.item_id_arr, fbc.case_id, fbc.patient_id, fbc.open_date, fbc.close_date, fbc.region_data, fbc.main_diagnosis_id, (((fbc.region_data -> 'funding_id'::text))::integer)
  • Sort Key: fbc.patient_id, (((fbc.region_data -> 'funding_id'::text))::integer)
  • Sort Method: quicksort Memory: 32kB
  • Buffers: shared hit=7
5. 0.047 0.047 ↑ 123.6 7 1

Index Scan using new_cases_pkey on billing.fin_bill_cases fbc (cost=0.43..11,944.10 rows=865 width=659) (actual time=0.030..0.047 rows=7 loops=1)

  • Output: fbc.real_case_id, fbc.item_id_arr, fbc.case_id, fbc.patient_id, fbc.open_date, fbc.close_date, fbc.region_data, fbc.main_diagnosis_id, ((fbc.region_data -> 'funding_id'::text))::integer
  • Index Cond: (fbc.bill_id = 12554)
  • Filter: ((fbc.open_date <= fbc.close_date) AND (fbc.care_regimen_id = 1) AND ((fbc.case_type_code)::text = '1'::text))
  • Buffers: shared hit=7
6. 447.119 1,571.780 ↓ 41.1 364,200 1

Sort (cost=109,317.66..109,339.83 rows=8,868 width=34) (actual time=1,466.314..1,571.780 rows=364,200 loops=1)

  • Output: mc.uid, mc.id, mc.clinic_id, mc.open_date, mc.close_date, mc.main_diagnos_id, mc.patient_id, mc.funding_id
  • Sort Key: mc.patient_id, mc.funding_id
  • Sort Method: quicksort Memory: 51569kB
  • Buffers: shared hit=230551
7. 756.771 1,124.661 ↓ 56.7 502,662 1

Bitmap Heap Scan on public.mc_case mc (cost=24,814.73..108,736.17 rows=8,868 width=34) (actual time=441.496..1,124.661 rows=502,662 loops=1)

  • Output: mc.uid, mc.id, mc.clinic_id, mc.open_date, mc.close_date, mc.main_diagnos_id, mc.patient_id, mc.funding_id
  • Recheck Cond: ((mc.care_regimen_id = 2) AND (mc.case_type_id = 2))
  • Filter: ((mc.closing_step_id IS NOT NULL) AND (mc.open_date <= mc.close_date))
  • Rows Removed by Filter: 6793
  • Heap Blocks: exact=226097
  • Buffers: shared hit=230551
8. 68.386 367.890 ↓ 0.0 0 1

BitmapAnd (cost=24,814.73..24,814.73 rows=26,762 width=0) (actual time=367.890..367.890 rows=0 loops=1)

  • Buffers: shared hit=4454
9. 134.646 134.646 ↑ 1.0 521,924 1

Bitmap Index Scan on mc_case_care_regimen_id_id_ix (cost=0.00..10,769.24 rows=525,441 width=0) (actual time=134.646..134.646 rows=521,924 loops=1)

  • Index Cond: (mc.care_regimen_id = 2)
  • Buffers: shared hit=1949
10. 164.858 164.858 ↑ 1.0 677,788 1

Bitmap Index Scan on mc_case_case_type_id_clinic_id_idx (cost=0.00..14,040.80 rows=682,716 width=0) (actual time=164.858..164.858 rows=677,788 loops=1)

  • Index Cond: (mc.case_type_id = 2)
  • Buffers: shared hit=2505
11.          

SubPlan (for Merge Join)

12. 0.000 0.000 ↓ 0.0 0

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

13. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.99..395.10 rows=38 width=7) (never executed)

  • Output: ss.code
14. 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..73.90 rows=38 width=4) (never executed)

  • Output: ssr.service_id
  • Index Cond: (ssr.md_case_id = fbc.real_case_id)
15. 0.000 0.000 ↓ 0.0 0

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

  • Output: ss.id, ss.code
  • Index Cond: (ss.id = ssr.service_id)
16. 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(fbc.close_date, fbc.open_date) >= COALESCE(mk.from_dt, '-infinity'::date)) AND (COALESCE(fbc.close_date, fbc.open_date) <= COALESCE(mk.to_dt, 'infinity'::date)) AND (mk.pos = 11))
17. 0.000 0.000 ↓ 0.0 0

Subquery Scan on fbs (cost=0.99..18.85 rows=1 width=32) (never executed)

  • Output: fbs.case_id, fbs.srv_code_arr, fbs.is_stomat_arr
  • Filter: (NOT (SubPlan 3))
18. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=0.99..17.09 rows=1 width=68) (never executed)

  • Output: fbs_1.case_id, array_agg(("left"((fbs_1.service_code)::text, 6))::character varying), NULL::boolean[]
  • Group Key: fbs_1.case_id
  • Filter: ((NOT (COALESCE(array_agg(("left"((fbs_1.service_code)::text, 6))::character varying), '{0}'::character varying[]) && '{049021,049022,149019,149018,023011,123011,023003,123003}'::character varying[])) AND (NOT (COALESCE(array_agg(("left"((fbs_1.service_code)::text, 6))::character varying), '{0}'::character varying[]) && '{023016,123016,123057,023006,023007,123006,123007,023008,123008,023005,123005,023015,123015}'::character varying[])) AND (NOT (COALESCE(array_agg(("left"((fbs_1.service_code)::text, 6))::character varying), '{0}'::character varying[]) && '{001950,101950}'::character varying[])) AND (true <> ALL (array_agg(COALESCE(v.is_stomat, false)))))
19. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.99..17.04 rows=1 width=11) (never executed)

  • Output: fbs_1.case_id, fbs_1.service_code, v.is_stomat
20. 0.000 0.000 ↓ 0.0 0

Index Scan using new_steps_bill_id_case_id_idx on billing.fin_bill_steps fbs_1 (cost=0.56..8.58 rows=1 width=14) (never executed)

  • Output: fbs_1.srv_rendered_id, fbs_1.srv_rendered_bdate, fbs_1.srv_rendered_edate, fbs_1.srv_rendered_quantity, fbs_1.srv_rendered_comment, fbs_1.srv_diagnosis_code, fbs_1.tooth_number, fbs_1.service_id, fbs_1.service_code, fbs_1.service_name, fbs_1.srv_prototype_id, fbs_1.srv_prototype_code, fbs_1.patient_id, fbs_1.id_pac, fbs_1.res_group_id, fbs_1.n_zap, fbs_1.case_id, fbs_1.step_id, fbs_1.step_main_diagnosis_id, fbs_1.step_diagnosis_main, fbs_1.step_diagnosis_other, fbs_1.step_admission_date, fbs_1.step_outcome_date, fbs_1.step_profile_code, fbs_1.vmp_type_code, fbs_1.vmp_method_code, fbs_1.hsp_department_code, fbs_1.hsp_department_name, fbs_1.bed_days_amount, fbs_1.doctor_code, fbs_1.doctor_code_regional, fbs_1.doctor_snils, fbs_1.speciality_code_arr, fbs_1.pos_role_code, fbs_1.position_id, fbs_1.department_code, fbs_1.department_name, fbs_1.visit_type_code, fbs_1.visit_place_code, fbs_1.visit_inc_code, fbs_1.visit_goal_code_arr, fbs_1.visit_goal_name, fbs_1.bill_id, fbs_1.spec_item_id, fbs_1.spec_err_exists, fbs_1.tariff, fbs_1.tariff_code, fbs_1.quantity, fbs_1.cul, fbs_1.price, fbs_1.price_pos_id, fbs_1.price_pos_code, fbs_1.price_pos_name, fbs_1.region_data, fbs_1.real_case_id
  • Index Cond: ((fbs_1.bill_id = 12554) AND (fbs_1.case_id = fbc.case_id))
21. 0.000 0.000 ↓ 0.0 0

Index Scan using sr_service_pk on public.sr_service v (cost=0.42..8.44 rows=1 width=5) (never executed)

  • Output: v.is_stomat, v.id
  • Index Cond: (v.id = fbs_1.service_id)
22.          

SubPlan (for Subquery Scan)

23. 0.000 0.000 ↓ 0.0 0

Subquery Scan on g_1 (cost=0.00..1.76 rows=1 width=0) (never executed)

  • Filter: ((g_1.srv_code)::text ~ '^[01]70'::text)
24. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.51 rows=100 width=32) (never executed)

  • Output: unnest(COALESCE(fbs.srv_code_arr, '{0}'::character varying[]))
25. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.44..8.46 rows=1 width=4) (never executed)

  • Output: mcd.diagnos_id
26. 0.000 0.000 ↓ 0.0 0

Index Scan using mc_diagnosis_pk on public.mc_diagnosis mcd (cost=0.44..8.46 rows=1 width=4) (never executed)

  • Output: mcd.diagnos_id
  • Index Cond: (mcd.id = mc.main_diagnos_id)
27.          

SubPlan (for Nested Loop Left Join)

28. 0.000 0.000 ↓ 0.0 0

Subquery Scan on g (cost=0.00..1.76 rows=1 width=0) (never executed)

  • Filter: ((g.srv_code)::text ~ '^[01]37'::text)
29. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.51 rows=100 width=32) (never executed)

  • Output: unnest(COALESCE(fbs.srv_code_arr, '{0}'::character varying[]))
Planning time : 8.014 ms
Execution time : 1,632.108 ms