explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SwRA

Settings
# exclusive inclusive rows x rows loops node
1. 203,076.001 203,076.001 ↓ 13.0 13 1

CTE Scan on str (cost=54,093.52..54,093.58 rows=1 width=200) (actual time=203,062.498..203,076.001 rows=13 loops=1)

  • Output: str.profile, str.fio, str.gr1, str.gr1_som, str.gr2, str.gr3, str.gr3_som, str.gr4, str.gr4_som, CASE WHEN (str.gr1 = 0) THEN '0'::numeric ELSE round((((str.prof)::numeric / (str.gr1)::numeric) * '100'::numeric), 2) END, (COALESCE(str.gr1, '0'::bigint) + COALESCE(str.prof, '0'::bigint)), CASE WHEN ((COALESCE(str.gr1, '0'::bigint) + COALESCE(str.prof, '0'::bigint)) = 0) THEN '0'::numeric ELSE round((((str.gr1)::numeric / ((COALESCE(str.gr1, '0'::bigint) + COALESCE(str.prof, '0'::bigint)))::numeric) * '100'::numeric), 2) END
  • Buffers: shared hit=180598574 read=1099484 dirtied=12211
  • I/O Timings: read=56163.101
2.          

CTE prm

3. 0.006 0.006 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.006 rows=1 loops=1)

  • Output: '2019-09-01'::date, '2019-10-30'::date, 5020, false, false, 1, NULL::integer, false, NULL::integer
4.          

CTE tt1

5. 2.355 203,038.771 ↓ 18,384.0 18,384 1

Nested Loop (cost=2.97..54,093.39 rows=1 width=140) (actual time=45,990.828..203,038.771 rows=18,384 loops=1)

  • Output: mp.name, CASE WHEN (prm.doc_list IS TRUE) THEN concat(ind.surname, ' ', ind.name, ' ', ind.patr_name) ELSE NULL::text END, mc.init_goal_id, mc.id, ms.id, ms.plc_place_id, mc.case_type_id, fbsi.price
  • Buffers: shared hit=180598574 read=1099484 dirtied=12211
  • I/O Timings: read=56163.101
6. 14.435 202,999.648 ↓ 18,384.0 18,384 1

Nested Loop (cost=2.54..54,087.57 rows=1 width=94) (actual time=45,990.801..202,999.648 rows=18,384 loops=1)

  • Output: prm.doc_list, mc.init_goal_id, mc.id, mc.case_type_id, ms.id, ms.plc_place_id, mp.name, fbsi.price, pe.individual_id
  • Buffers: shared hit=180525004 read=1099484 dirtied=12211
  • I/O Timings: read=56163.101
7. 16.851 202,966.829 ↓ 18,384.0 18,384 1

Nested Loop Left Join (cost=2.25..54,087.21 rows=1 width=94) (actual time=45,990.772..202,966.829 rows=18,384 loops=1)

  • Output: prm.doc_list, mc.init_goal_id, mc.id, mc.case_type_id, ms.id, ms.plc_place_id, mp.name, fbsi.price, pep.employee_id
  • Join Filter: ((prm.status IS FALSE) OR (fbsi.status_id = 2))
  • Filter: ((prm.bill IS FALSE) OR (prm.status IS FALSE) OR (fbsi.id IS NOT NULL))
  • Buffers: shared hit=180469816 read=1099484 dirtied=12211
  • I/O Timings: read=56163.101
8. 3.475 201,755.018 ↓ 18,384.0 18,384 1

Nested Loop (cost=1.69..54,086.52 rows=1 width=94) (actual time=45,990.744..201,755.018 rows=18,384 loops=1)

  • Output: prm.doc_list, prm.status, prm.bill, mc.init_goal_id, mc.id, mc.case_type_id, ms.id, ms.plc_place_id, mp.name, ssr.id, pep.employee_id
  • Buffers: shared hit=180385974 read=1091105 dirtied=11695
  • I/O Timings: read=55047.082
9. 228.973 200,000.509 ↓ 17,167.0 17,167 1

Nested Loop (cost=1.12..54,075.98 rows=1 width=90) (actual time=45,990.708..200,000.509 rows=17,167 loops=1)

  • Output: prm.doc_list, prm.status, prm.bill, mc.init_goal_id, mc.id, mc.case_type_id, ms.id, ms.plc_place_id, mp.name, pep.employee_id
  • Join Filter: (ms.profile_id = mp.id)
  • Rows Removed by Join Filter: 2849722
  • Buffers: shared hit=180307441 read=1081581 dirtied=11550
  • I/O Timings: read=53389.790
10. 8,881.516 199,599.866 ↓ 17,167.0 17,167 1

Nested Loop (cost=1.12..54,068.22 rows=1 width=31) (actual time=45,990.656..199,599.866 rows=17,167 loops=1)

  • Output: prm.doc_list, prm.status, prm.bill, mc.init_goal_id, mc.id, mc.case_type_id, ms.id, ms.plc_place_id, ms.profile_id, pep.employee_id
  • Join Filter: ((mc.close_date >= prm.d1) AND (mc.close_date <= prm.d2) AND ((prm.fund IS NULL) OR (mc.funding_id = prm.fund)) AND (prm.clin = mc.clinic_id))
  • Rows Removed by Join Filter: 26881605
  • Buffers: shared hit=180238773 read=1081581 dirtied=11550
  • I/O Timings: read=53389.790
11. 12,448.767 67,453.598 ↓ 7,556.7 30,816,188 1

Nested Loop (cost=0.56..51,027.31 rows=4,078 width=39) (actual time=0.409..67,453.598 rows=30,816,188 loops=1)

  • Output: prm.doc_list, prm.clin, prm.d1, prm.d2, prm.status, prm.bill, prm.fund, ms.id, ms.plc_place_id, ms.case_id, ms.profile_id, pep.employee_id
  • Join Filter: ((prm.init_goal IS NULL) OR (ms.plc_goal_id = prm.init_goal))
  • Buffers: shared hit=26141794 read=697416 dirtied=4159
  • I/O Timings: read=24788.681
12. 14.520 22.576 ↓ 200.4 40,879 1

Nested Loop (cost=0.00..1,564.61 rows=204 width=31) (actual time=0.015..22.576 rows=40,879 loops=1)

  • Output: prm.doc_list, prm.clin, prm.d1, prm.d2, prm.status, prm.bill, prm.fund, prm.init_goal, pep.id, pep.employee_id
  • Join Filter: ((prm.doc IS NULL) OR (pep.id = prm.doc))
  • Buffers: shared hit=649
13. 0.010 0.010 ↑ 1.0 1 1

CTE Scan on prm (cost=0.00..0.02 rows=1 width=27) (actual time=0.004..0.010 rows=1 loops=1)

  • Output: prm.d1, prm.d2, prm.clin, prm.bill, prm.status, prm.fund, prm.doc, prm.doc_list, prm.init_goal
14. 8.046 8.046 ↓ 1.0 40,879 1

Seq Scan on public.pim_employee_position pep (cost=0.00..1,055.93 rows=40,693 width=8) (actual time=0.007..8.046 rows=40,879 loops=1)

  • Output: pep.id, pep.dismissal_order_code, pep.start_date, pep.hiring_order_code, pep.rate, pep.end_date, pep.dismissal_reason_id, pep.employee_id, pep.employment_type_id, pep.hiring_type_id, pep.position_id, pep.position_type_id, pep.unit_id, pep.code, pep.aud_who, pep.aud_when, pep.aud_source, pep.aud_who_create, pep.aud_when_create, pep.aud_source_create, pep.target_training, pep.leaving_reason_id, pep.pref_prescription, pep.extra_payment
  • Buffers: shared hit=649
15. 54,982.255 54,982.255 ↑ 6.6 754 40,879

Index Scan using mc_step_responsible_id_idx on public.mc_step ms (cost=0.56..180.41 rows=4,964 width=24) (actual time=0.031..1.345 rows=754 loops=40,879)

  • Output: ms.id, ms.admission_date, ms.admission_time, ms.death_date, ms.death_time, ms.outcome_date, ms.outcome_time, ms.death_employee_id, ms.main_diagnosis_id, ms.case_id, ms.outcome_id, ms.outcome_clinic_id, ms.outcome_regimen_id, ms.regimen_id, ms.res_group_id, ms.result_id, ms.reason_id, ms.profile_id, ms.mes_id, ms.mes_quality_criterion_id, ms.is_continue, ms.is_continue_editable, ms.standard_id, ms.csg_id, ms.vmp_type_id, ms.vmp_method_id, ms.deviation_reason_id, ms.aud_who, ms.aud_when, ms.aud_source, ms.aud_who_create, ms.aud_when_create, ms.aud_source_create, ms._clinic_id, ms._patient_id, ms._case_mode_id, ms._is_last, ms._responsible_id, ms._department_id, ms.hsp_bed_days_amount, ms.hsp_bed_profile_id, ms.hsp_complexity_level_id, ms.hsp_days_comp_algo_id, ms.hsp_department_id, ms.hsp_funding_id, ms.hsp_is_admission_day_counts, ms.hsp_is_diag_not_eq, ms.hsp_is_set_diagnosis, ms.hsp_issue_planned_date, ms.hsp_mes_id, ms.hsp_missed_days_amount, ms.hsp_plan_department_id, ms.hsp_previous_id, ms.hsp_refusal_employee_id, ms.plc_goal_id, ms.plc_initiator_id, ms.plc_is_needed, ms.plc_is_sanitized, ms.plc_is_viewed, ms.plc_place_id, ms.plc_type_id, ms.plc_appointment_id, ms.plc_planned_date, ms.is_autopsy, ms.planned_date_closing_case, ms.gestational_age, ms.onko_stage, ms.is_first_step
  • Index Cond: (ms._responsible_id = pep.id)
  • Buffers: shared hit=26141145 read=697416 dirtied=4159
  • I/O Timings: read=24788.681
16. 123,264.752 123,264.752 ↑ 1.0 1 30,816,188

Index Scan using mc_case_pk on public.mc_case mc (cost=0.56..0.73 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=30,816,188)

  • Output: mc.init_goal_id, mc.id, mc.case_type_id, mc.clinic_id, mc.close_date, mc.funding_id
  • Index Cond: (mc.id = ms.case_id)
  • Filter: (mc.case_type_id = 1)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=154096979 read=384165 dirtied=7391
  • I/O Timings: read=28601.109
17. 171.670 171.670 ↑ 1.0 167 17,167

Seq Scan on public.md_profile mp (cost=0.00..5.67 rows=167 width=67) (actual time=0.001..0.010 rows=167 loops=17,167)

  • Output: mp.id, mp.code, mp.genitive_name, mp.name, mp.e_code, mp.from_dt, mp.to_dt, mp.aud_who, mp.aud_when, mp.aud_source, mp.aud_who_create, mp.aud_when_create, mp.aud_source_create
  • Buffers: shared hit=68668
18. 1,751.034 1,751.034 ↑ 354.0 1 17,167

Index Scan using sr_srv_rendered_md_step_ix on public.sr_srv_rendered ssr (cost=0.56..7.01 rows=354 width=8) (actual time=0.101..0.102 rows=1 loops=17,167)

  • Output: ssr.id, ssr.bdate, ssr.comment, ssr.cost, ssr.duration, ssr.edate, ssr.is_rendered, ssr.quantity, ssr.total_cost, ssr.contract_id, ssr.customer_id, ssr.funding_id, ssr.res_group_id, ssr.service_id, ssr.duration_measure_unit_id, ssr.begin_time, ssr.prototype_id, ssr.org_id, ssr.price_list_id, ssr.cul, ssr.parent_id, ssr.payment_status_id, ssr.root_service_id, ssr.is_wholly_rendered, ssr.planned_date, ssr.planned_time, ssr.tooth_number, ssr.is_refused, ssr.aud_who, ssr.aud_when, ssr.aud_source, ssr.aud_who_create, ssr.aud_when_create, ssr.aud_source_create, ssr.is_amalgam_filling, ssr.is_mobile_medical_teams, ssr._responsible_id, ssr._department_id, ssr._created_by_dep_id, ssr.entity_type, ssr.md_is_urgent, ssr.md_is_use_cryogenic, ssr.md_is_use_endoscopic, ssr.md_is_use_laser, ssr.md_anesthesia_type_id, ssr.md_step_id, ssr.md_complication_type_id, ssr.md_diagnosis_id, ssr.md_case_id, ssr.md_referral_id, ssr.md_result_category_id, ssr.md_patient_prescription_id, ssr.md_health_group_id, ssr.md_vmp_type_id, ssr.md_vmp_method_id, ssr.md_anatomic_zone_id, ssr.md_prescription_id, ssr.entity_sync_num, ssr.entity_type_aware, ssr.cancel_reason, ssr.is_vmp, ssr.close_date, ssr.complex_srv_id, ssr.for_upload, ssr.uid, ssr.is_need_consulting
  • Index Cond: (ssr.md_step_id = ms.id)
  • Buffers: shared hit=78533 read=9524 dirtied=145
  • I/O Timings: read=1657.292
19. 1,194.960 1,194.960 ↑ 1.0 1 18,384

Index Scan using fin_bill_spec_item_service_id_idx on public.fin_bill_spec_item fbsi (cost=0.56..0.67 rows=1 width=18) (actual time=0.053..0.065 rows=1 loops=18,384)

  • Output: fbsi.id, fbsi.price, fbsi.tariff, fbsi.bill_id, fbsi.service_id, fbsi.number, fbsi.is_deleted, fbsi.comment, fbsi.doc_id, fbsi.status_id, fbsi.correctional_bill_id, fbsi.aud_who, fbsi.aud_when, fbsi.aud_source, fbsi.aud_who_create, fbsi.aud_when_create, fbsi.aud_source_create
  • Index Cond: (fbsi.service_id = ssr.id)
  • Filter: (NOT COALESCE(fbsi.is_deleted, false))
  • Buffers: shared hit=83842 read=8379 dirtied=516
  • I/O Timings: read=1116.019
20. 18.384 18.384 ↑ 1.0 1 18,384

Index Scan using pim_employee_pk on public.pim_employee pe (cost=0.29..0.35 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=18,384)

  • Output: pe.id, pe.note, pe.number, pe.photo, pe.callup_subject_id, pe.individual_id, pe.organization_id, pe.is_dismissed, pe.employment_dt, pe.dismissal_dt, pe.aud_who, pe.aud_when, pe.aud_source, pe.aud_who_create, pe.aud_when_create, pe.aud_source_create, pe.accreditation_id
  • Index Cond: (pe.id = pep.employee_id)
  • Buffers: shared hit=55188
21. 36.768 36.768 ↑ 1.0 1 18,384

Index Scan using pim_individual_pk on public.pim_individual ind (cost=0.43..5.81 rows=1 width=54) (actual time=0.002..0.002 rows=1 loops=18,384)

  • Output: ind.surname, ind.name, ind.patr_name, ind.id
  • Index Cond: (ind.id = pe.individual_id)
  • Buffers: shared hit=73570
22.          

CTE str

23. 12.585 203,075.924 ↓ 13.0 13 1

GroupAggregate (cost=0.03..0.12 rows=1 width=96) (actual time=203,062.453..203,075.924 rows=13 loops=1)

  • Output: tt1.profile, tt1.fio, count(DISTINCT tt1.msid) FILTER (WHERE (tt1.goal = ANY ('{94,104,106,107}'::integer[]))), sum(tt1.price) FILTER (WHERE (tt1.goal = ANY ('{94,104,106,107}'::integer[]))), count(DISTINCT tt1.msid) FILTER (WHERE (tt1.goal = ANY ('{93,111}'::integer[]))), count(DISTINCT tt1.mcid) FILTER (WHERE (tt1.goal = ANY ('{93,111}'::integer[]))), sum(tt1.price) FILTER (WHERE (tt1.goal = ANY ('{93,111}'::integer[]))), count(DISTINCT tt1.msid) FILTER (WHERE (tt1.goal <> ALL ('{93,94,104,105,106,107,111}'::integer[]))), sum(tt1.price) FILTER (WHERE (tt1.goal <> ALL ('{93,94,104,105,106,107,111}'::integer[]))), count(DISTINCT tt1.msid) FILTER (WHERE (tt1.goal <> ALL ('{93,94,104,105,106,107,111}'::integer[])))
  • Group Key: tt1.profile, tt1.fio
  • Buffers: shared hit=180598574 read=1099484 dirtied=12211
  • I/O Timings: read=56163.101
24. 11.625 203,063.339 ↓ 18,384.0 18,384 1

Sort (cost=0.03..0.04 rows=1 width=96) (actual time=203,062.237..203,063.339 rows=18,384 loops=1)

  • Output: tt1.profile, tt1.fio, tt1.msid, tt1.goal, tt1.price, tt1.mcid
  • Sort Key: tt1.profile, tt1.fio
  • Sort Method: quicksort Memory: 2658kB
  • Buffers: shared hit=180598574 read=1099484 dirtied=12211
  • I/O Timings: read=56163.101
25. 203,051.714 203,051.714 ↓ 18,384.0 18,384 1

CTE Scan on tt1 (cost=0.00..0.02 rows=1 width=96) (actual time=45,990.834..203,051.714 rows=18,384 loops=1)

  • Output: tt1.profile, tt1.fio, tt1.msid, tt1.goal, tt1.price, tt1.mcid
  • Buffers: shared hit=180598574 read=1099484 dirtied=12211
  • I/O Timings: read=56163.101
Planning time : 12.538 ms
Execution time : 203,077.211 ms