explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mmis

Settings
# exclusive inclusive rows x rows loops node
1. 101,171.986 101,171.986 ↑ 343.3 9 1

CTE Scan on tt6 (cost=142,987,768.82..142,987,830.62 rows=3,090 width=224) (actual time=101,171.983..101,171.986 rows=9 loops=1)

  • Output: tt6.login, tt6.d, tt6.mcid, tt6.msid, tt6.nesov, tt6.vzr, tt6.sir, tt6.ssrid, tt6.pro, tt6.rol, tt6.rol2, tt6.fio, tt6.suid, tt6.aktinost
  • Buffers: shared hit=591093 read=719371 dirtied=3850
  • I/O Timings: read=92842.343
2.          

CTE prm

3. 0.013 0.013 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.005..0.013 rows=1 loops=1)

  • Output: '2019-10-01'::date, '2019-10-01'::date, 5028, false, true, (NULLIF(''::text, ''::text))::time without time zone, (NULLIF(''::text, ''::text))::time without time zone, false, true, false, false, false, NULL::integer, false, false
4.          

CTE tt1

5. 0.182 7,153.740 ↑ 1.8 1,339 1

Append (cost=13,492.32..772,416.71 rows=2,399 width=56) (actual time=1,441.276..7,153.740 rows=1,339 loops=1)

  • Buffers: shared hit=352678 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
6. 1.705 7,135.828 ↓ 1.1 1,339 1

Nested Loop (cost=13,492.32..622,645.84 rows=1,185 width=51) (actual time=1,441.276..7,135.828 rows=1,339 loops=1)

  • Output: CASE WHEN ((mc.case_type_id = 4) AND ((CASE WHEN (prm.edit IS TRUE) THEN mc.aud_who ELSE mc.aud_who_create END)::text = 's.n.gritchina'::text)) THEN 'n.t.dzhioeva'::character varying ELSE CASE WHEN (prm.edit IS TRUE) THEN mc.aud_who ELSE mc.aud_who_create END END, CASE WHEN (prm.dat IS TRUE) THEN (mc.aud_when_create)::date ELSE NULL::date END, mc.id, ms.id, CASE WHEN ((mc.case_type_id = 4) AND (mc.init_goal_id = ANY ('{61,60,14,13,12,100,101}'::integer[]))) THEN mc.id ELSE NULL::integer END, CASE WHEN ((mc.case_type_id = 4) AND (mc.init_goal_id = ANY ('{9,8,7}'::integer[]))) THEN mc.id ELSE NULL::integer END, CASE WHEN ((mc.case_type_id = 4) AND (mc.init_goal_id = ANY ('{59,58,11,10,103,102}'::integer[]))) THEN mc.id ELSE NULL::integer END
  • Buffers: shared hit=351748 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
7. 0.803 7,128.767 ↓ 1.1 1,339 1

Nested Loop (cost=13,491.89..622,058.85 rows=1,185 width=55) (actual time=1,441.233..7,128.767 rows=1,339 loops=1)

  • Output: prm.edit, prm.dat, mc.case_type_id, mc.aud_who, mc.aud_who_create, mc.aud_when_create, mc.id, mc.init_goal_id, mc.patient_id, ms.id
  • Join Filter: CASE WHEN (prm.dep IS NOT NULL) THEN (ARRAY[pp.department_id] && supp.komi_dep_parent_arr(prm.dep)) ELSE true END
  • Buffers: shared hit=346979 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
8. 0.672 7,125.286 ↑ 1.8 1,339 1

Nested Loop (cost=13,491.60..620,644.79 rows=2,370 width=63) (actual time=1,441.221..7,125.286 rows=1,339 loops=1)

  • Output: prm.edit, prm.dat, prm.dep, mc.case_type_id, mc.aud_who, mc.aud_who_create, mc.aud_when_create, mc.id, mc.init_goal_id, mc.patient_id, ms.id, pep.position_id
  • Buffers: shared hit=342962 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
9. 1.444 7,121.660 ↑ 1.7 1,477 1

Nested Loop (cost=13,491.31..619,859.36 rows=2,473 width=63) (actual time=1,441.212..7,121.660 rows=1,477 loops=1)

  • Output: prm.edit, prm.dat, prm.dep, mc.case_type_id, mc.aud_who, mc.aud_who_create, mc.aud_when_create, mc.id, mc.init_goal_id, mc.patient_id, ms.id, ms._responsible_id
  • Buffers: shared hit=338944 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
10. 0.289 7,109.488 ↑ 2.2 894 1

Nested Loop (cost=13,490.75..617,772.39 rows=1,972 width=55) (actual time=1,441.192..7,109.488 rows=894 loops=1)

  • Output: prm.edit, prm.dat, prm.dep, mc.case_type_id, mc.aud_who, mc.aud_who_create, mc.aud_when_create, mc.id, mc.init_goal_id, mc.patient_id
  • Buffers: shared hit=334130 read=127192 dirtied=3579
  • I/O Timings: read=5467.500
11. 0.018 0.018 ↑ 1.0 1 1

CTE Scan on prm (cost=0.00..0.02 rows=1 width=37) (actual time=0.001..0.018 rows=1 loops=1)

  • Output: prm.d1, prm.d2, prm.clinicid, prm.step, prm.clos, prm.dtime1, prm.dtime2, prm.iskl, prm.iskl2, prm.stac, prm.dat, prm.medsis, prm.dep, prm.edit, prm.nstac
  • Filter: (prm.step IS FALSE)
12. 6,925.996 7,109.181 ↑ 2.2 894 1

Bitmap Heap Scan on public.mc_case mc (cost=13,490.75..617,752.65 rows=1,972 width=61) (actual time=1,441.187..7,109.181 rows=894 loops=1)

  • Output: mc.case_type_id, mc.aud_who, mc.aud_who_create, mc.aud_when_create, mc.id, mc.init_goal_id, mc.clinic_id, mc.closing_step_id, mc.care_regimen_id, mc.patient_id
  • Recheck Cond: (mc.clinic_id = prm.clinicid)
  • Filter: (CASE WHEN (prm.clos IS FALSE) THEN (mc.closing_step_id IS NOT NULL) ELSE true END AND CASE WHEN (prm.nstac IS TRUE) THEN (mc.case_type_id <> 2) ELSE true END AND ((mc.aud_when_create)::date >= prm.d1) AND ((mc.aud_when_create)::date <= prm.d2) AND CASE WHEN (prm.stac IS TRUE) THEN (mc.care_regimen_id = ANY ('{2,3,5,6,7}'::integer[])) ELSE true END AND CASE WHEN ((prm.dtime1 IS NOT NULL) AND (prm.dtime2 IS NOT NULL)) THEN (((mc.aud_when_create)::time without time zone >= prm.dtime1) AND ((mc.aud_when_create)::time without time zone <= prm.dtime2)) ELSE true END)
  • Rows Removed by Filter: 671222
  • Heap Blocks: exact=454586
  • Buffers: shared hit=334130 read=127192 dirtied=3579
  • I/O Timings: read=5467.500
13. 183.185 183.185 ↓ 2.4 678,325 1

Bitmap Index Scan on mc_case_clinic_id_idx (cost=0.00..13,490.26 rows=283,959 width=0) (actual time=183.185..183.185 rows=678,325 loops=1)

  • Index Cond: (mc.clinic_id = prm.clinicid)
  • Buffers: shared hit=14 read=6722
  • I/O Timings: read=36.363
14. 10.728 10.728 ↑ 6.0 2 894

Index Scan using mc_step_case_ix on public.mc_step ms (cost=0.56..0.94 rows=12 width=12) (actual time=0.011..0.012 rows=2 loops=894)

  • 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.case_id = mc.id)
  • Buffers: shared hit=4814 read=87 dirtied=23
  • I/O Timings: read=5.596
15. 2.954 2.954 ↑ 1.0 1 1,477

Index Scan using pim_employee_position_pk on public.pim_employee_position pep (cost=0.29..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,477)

  • 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
  • Index Cond: (pep.id = ms._responsible_id)
  • Buffers: shared hit=4018
16. 2.678 2.678 ↑ 1.0 1 1,339

Index Scan using pim_position_pk on public.pim_position pp (cost=0.29..0.33 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,339)

  • Output: pp.id, pp.department_id
  • Index Cond: (pp.id = pep.position_id)
  • Buffers: shared hit=4017
17. 5.356 5.356 ↑ 1.0 1 1,339

Index Only Scan using pim_individual_pk on public.pim_individual ind (cost=0.43..0.45 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1,339)

  • Output: ind.id
  • Index Cond: (ind.id = mc.patient_id)
  • Heap Fetches: 172
  • Buffers: shared hit=4769
18. 0.014 17.730 ↓ 0.0 0 1

Nested Loop (cost=4,281.35..149,746.88 rows=1,214 width=47) (actual time=17.730..17.730 rows=0 loops=1)

  • Output: CASE WHEN ((mc_1.case_type_id = 4) AND ((CASE WHEN (prm_1.edit IS TRUE) THEN mc_1.aud_who ELSE mc_1.aud_who_create END)::text = 's.n.gritchina'::text)) THEN 'n.t.dzhioeva'::character varying ELSE CASE WHEN (prm_1.edit IS TRUE) THEN mc_1.aud_who ELSE mc_1.aud_who_create END END, CASE WHEN (prm_1.dat IS TRUE) THEN ms_1.admission_date ELSE NULL::date END, mc_1.id, ms_1.id, CASE WHEN ((mc_1.case_type_id = 4) AND (mc_1.init_goal_id = ANY ('{61,60,14,13,12,100,101}'::integer[]))) THEN mc_1.id ELSE NULL::integer END, CASE WHEN ((mc_1.case_type_id = 4) AND (mc_1.init_goal_id = ANY ('{9,8,7}'::integer[]))) THEN mc_1.id ELSE NULL::integer END, CASE WHEN ((mc_1.case_type_id = 4) AND (mc_1.init_goal_id = ANY ('{59,58,11,10,102,103}'::integer[]))) THEN mc_1.id ELSE NULL::integer END
  • Buffers: shared hit=930
19. 0.009 17.716 ↓ 0.0 0 1

Nested Loop (cost=4,280.92..149,148.57 rows=1,214 width=51) (actual time=17.716..17.716 rows=0 loops=1)

  • Output: prm_1.edit, prm_1.dat, ms_1.admission_date, ms_1.id, mc_1.case_type_id, mc_1.aud_who, mc_1.aud_who_create, mc_1.id, mc_1.init_goal_id, mc_1.patient_id
  • Join Filter: (CASE WHEN (prm_1.clos IS FALSE) THEN (mc_1.closing_step_id IS NOT NULL) ELSE true END AND CASE WHEN (prm_1.nstac IS TRUE) THEN (mc_1.case_type_id <> 2) ELSE true END AND CASE WHEN (prm_1.stac IS TRUE) THEN (mc_1.care_regimen_id = ANY ('{2,3,5,6,7}'::integer[])) ELSE true END)
  • Buffers: shared hit=930
20. 0.195 17.707 ↓ 0.0 0 1

Hash Join (cost=4,280.36..141,944.38 rows=9,710 width=17) (actual time=17.707..17.707 rows=0 loops=1)

  • Output: prm_1.edit, prm_1.dat, prm_1.clos, prm_1.stac, prm_1.nstac, ms_1.admission_date, ms_1.id, ms_1.case_id
  • Hash Cond: (pep_1.position_id = pp_1.id)
  • Join Filter: CASE WHEN (prm_1.dep IS NOT NULL) THEN (ARRAY[pp_1.department_id] && supp.komi_dep_parent_arr(prm_1.dep)) ELSE true END
  • Buffers: shared hit=930
21. 0.326 13.348 ↓ 0.0 0 1

Hash Join (cost=3,688.75..136,182.46 rows=19,419 width=25) (actual time=13.348..13.348 rows=0 loops=1)

  • Output: prm_1.edit, prm_1.dat, prm_1.clos, prm_1.stac, prm_1.nstac, prm_1.dep, ms_1.admission_date, ms_1.id, ms_1.case_id, pep_1.position_id
  • Hash Cond: (ms_1._responsible_id = pep_1.id)
  • Buffers: shared hit=649
22. 0.011 0.022 ↓ 0.0 0 1

Nested Loop (cost=2,124.15..134,347.70 rows=20,261 width=25) (actual time=0.022..0.022 rows=0 loops=1)

  • Output: prm_1.edit, prm_1.dat, prm_1.clos, prm_1.stac, prm_1.nstac, prm_1.dep, ms_1.admission_date, ms_1.id, ms_1.case_id, ms_1._responsible_id
23. 0.011 0.011 ↓ 0.0 0 1

CTE Scan on prm prm_1 (cost=0.00..0.02 rows=1 width=37) (actual time=0.011..0.011 rows=0 loops=1)

  • Output: prm_1.d1, prm_1.d2, prm_1.clinicid, prm_1.step, prm_1.clos, prm_1.dtime1, prm_1.dtime2, prm_1.iskl, prm_1.iskl2, prm_1.stac, prm_1.dat, prm_1.medsis, prm_1.dep, prm_1.edit, prm_1.nstac
  • Filter: (prm_1.step IS TRUE)
  • Rows Removed by Filter: 1
24. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.mc_step ms_1 (cost=2,124.15..134,145.07 rows=20,261 width=33) (never executed)

  • Output: ms_1.id, ms_1.admission_date, ms_1.admission_time, ms_1.death_date, ms_1.death_time, ms_1.outcome_date, ms_1.outcome_time, ms_1.death_employee_id, ms_1.main_diagnosis_id, ms_1.case_id, ms_1.outcome_id, ms_1.outcome_clinic_id, ms_1.outcome_regimen_id, ms_1.regimen_id, ms_1.res_group_id, ms_1.result_id, ms_1.reason_id, ms_1.profile_id, ms_1.mes_id, ms_1.mes_quality_criterion_id, ms_1.is_continue, ms_1.is_continue_editable, ms_1.standard_id, ms_1.csg_id, ms_1.vmp_type_id, ms_1.vmp_method_id, ms_1.deviation_reason_id, ms_1.aud_who, ms_1.aud_when, ms_1.aud_source, ms_1.aud_who_create, ms_1.aud_when_create, ms_1.aud_source_create, ms_1._clinic_id, ms_1._patient_id, ms_1._case_mode_id, ms_1._is_last, ms_1._responsible_id, ms_1._department_id, ms_1.hsp_bed_days_amount, ms_1.hsp_bed_profile_id, ms_1.hsp_complexity_level_id, ms_1.hsp_days_comp_algo_id, ms_1.hsp_department_id, ms_1.hsp_funding_id, ms_1.hsp_is_admission_day_counts, ms_1.hsp_is_diag_not_eq, ms_1.hsp_is_set_diagnosis, ms_1.hsp_issue_planned_date, ms_1.hsp_mes_id, ms_1.hsp_missed_days_amount, ms_1.hsp_plan_department_id, ms_1.hsp_previous_id, ms_1.hsp_refusal_employee_id, ms_1.plc_goal_id, ms_1.plc_initiator_id, ms_1.plc_is_needed, ms_1.plc_is_sanitized, ms_1.plc_is_viewed, ms_1.plc_place_id, ms_1.plc_type_id, ms_1.plc_appointment_id, ms_1.plc_planned_date, ms_1.is_autopsy, ms_1.planned_date_closing_case, ms_1.gestational_age, ms_1.onko_stage, ms_1.is_first_step
  • Recheck Cond: ((ms_1._clinic_id = prm_1.clinicid) AND (ms_1.admission_date >= prm_1.d1) AND (ms_1.admission_date <= prm_1.d2))
  • Filter: CASE WHEN ((prm_1.dtime1 IS NOT NULL) AND (prm_1.dtime2 IS NOT NULL)) THEN (((ms_1.aud_who_create)::time without time zone >= prm_1.dtime1) AND ((ms_1.aud_who_create)::time without time zone <= prm_1.dtime2)) ELSE true END
25. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on mc_step__clinic_id_admission_date_outcome_date_ix (cost=0.00..2,119.09 rows=40,522 width=0) (never executed)

  • Index Cond: ((ms_1._clinic_id = prm_1.clinicid) AND (ms_1.admission_date >= prm_1.d1) AND (ms_1.admission_date <= prm_1.d2))
26. 5.498 13.000 ↓ 1.0 40,879 1

Hash (cost=1,055.93..1,055.93 rows=40,693 width=8) (actual time=13.000..13.000 rows=40,879 loops=1)

  • Output: pep_1.id, pep_1.position_id
  • Buckets: 65536 Batches: 1 Memory Usage: 2109kB
  • Buffers: shared hit=649
27. 7.502 7.502 ↓ 1.0 40,879 1

Seq Scan on public.pim_employee_position pep_1 (cost=0.00..1,055.93 rows=40,693 width=8) (actual time=0.005..7.502 rows=40,879 loops=1)

  • Output: pep_1.id, pep_1.position_id
  • Buffers: shared hit=649
28. 1.662 4.164 ↓ 1.0 13,910 1

Hash (cost=419.05..419.05 rows=13,805 width=8) (actual time=4.164..4.164 rows=13,910 loops=1)

  • Output: pp_1.id, pp_1.department_id
  • Buckets: 16384 Batches: 1 Memory Usage: 672kB
  • Buffers: shared hit=281
29. 2.502 2.502 ↓ 1.0 13,910 1

Seq Scan on public.pim_position pp_1 (cost=0.00..419.05 rows=13,805 width=8) (actual time=0.010..2.502 rows=13,910 loops=1)

  • Output: pp_1.id, pp_1.department_id
  • Buffers: shared hit=281
30. 0.000 0.000 ↓ 0.0 0

Index Scan using mc_case_pk on public.mc_case mc_1 (cost=0.56..0.72 rows=1 width=49) (never executed)

  • Output: mc_1.case_type_id, mc_1.aud_who, mc_1.aud_who_create, mc_1.id, mc_1.init_goal_id, mc_1.closing_step_id, mc_1.care_regimen_id, mc_1.patient_id
  • Index Cond: (mc_1.id = ms_1.case_id)
31. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pim_individual_pk on public.pim_individual ind_1 (cost=0.43..0.45 rows=1 width=4) (never executed)

  • Output: ind_1.id
  • Index Cond: (ind_1.id = mc_1.patient_id)
  • Heap Fetches: 0
32.          

CTE tt3

33. 0.038 92,883.880 ↑ 512.4 48 1

Append (cost=1,223,720.47..1,715,013.07 rows=24,594 width=44) (actual time=92,878.741..92,883.880 rows=48 loops=1)

  • Buffers: shared hit=171692 read=585229 dirtied=210
  • I/O Timings: read=86352.709
34. 0.704 92,879.558 ↑ 170.8 48 1

GroupAggregate (cost=1,223,720.47..1,223,904.92 rows=8,198 width=39) (actual time=92,878.711..92,879.558 rows=48 loops=1)

  • Output: (CASE WHEN (prm_2.edit IS TRUE) THEN ssr.aud_who ELSE ssr.aud_who_create END), (CASE WHEN (prm_2.dat IS TRUE) THEN (ssr.aud_when_create)::date ELSE NULL::date END), count(DISTINCT ssr.id)
  • Group Key: (CASE WHEN (prm_2.edit IS TRUE) THEN ssr.aud_who ELSE ssr.aud_who_create END), (CASE WHEN (prm_2.dat IS TRUE) THEN (ssr.aud_when_create)::date ELSE NULL::date END)
  • Buffers: shared hit=171411 read=585229 dirtied=210
  • I/O Timings: read=86352.709
35. 1.641 92,878.854 ↑ 2.7 3,070 1

Sort (cost=1,223,720.47..1,223,740.96 rows=8,198 width=39) (actual time=92,878.655..92,878.854 rows=3,070 loops=1)

  • Output: (CASE WHEN (prm_2.edit IS TRUE) THEN ssr.aud_who ELSE ssr.aud_who_create END), (CASE WHEN (prm_2.dat IS TRUE) THEN (ssr.aud_when_create)::date ELSE NULL::date END), ssr.id
  • Sort Key: (CASE WHEN (prm_2.edit IS TRUE) THEN ssr.aud_who ELSE ssr.aud_who_create END), (CASE WHEN (prm_2.dat IS TRUE) THEN (ssr.aud_when_create)::date ELSE NULL::date END)
  • Sort Method: quicksort Memory: 320kB
  • Buffers: shared hit=171411 read=585229 dirtied=210
  • I/O Timings: read=86352.709
36. 1.586 92,877.213 ↑ 2.7 3,070 1

Hash Join (cost=17,603.30..1,223,187.55 rows=8,198 width=39) (actual time=48,955.764..92,877.213 rows=3,070 loops=1)

  • Output: CASE WHEN (prm_2.edit IS TRUE) THEN ssr.aud_who ELSE ssr.aud_who_create END, CASE WHEN (prm_2.dat IS TRUE) THEN (ssr.aud_when_create)::date ELSE NULL::date END, ssr.id
  • Hash Cond: (pep_2.position_id = pp_2.id)
  • Join Filter: CASE WHEN (prm_2.dep IS NOT NULL) THEN (ARRAY[pp_2.department_id] && supp.komi_dep_parent_arr(prm_2.dep)) ELSE true END
  • Buffers: shared hit=171411 read=585229 dirtied=210
  • I/O Timings: read=86352.709
37. 2.168 92,871.531 ↑ 5.3 3,070 1

Nested Loop (cost=17,011.69..1,218,210.01 rows=16,396 width=47) (actual time=48,951.564..92,871.531 rows=3,070 loops=1)

  • Output: prm_2.edit, prm_2.dat, prm_2.dep, ssr.aud_who, ssr.aud_who_create, ssr.aud_when_create, ssr.id, pep_2.position_id
  • Buffers: shared hit=171130 read=585229 dirtied=210
  • I/O Timings: read=86352.709
38. 2.647 92,866.291 ↑ 5.5 3,072 1

Nested Loop Left Join (cost=17,011.40..1,212,882.06 rows=16,778 width=47) (actual time=48,951.491..92,866.291 rows=3,072 loops=1)

  • Output: prm_2.edit, prm_2.dat, prm_2.dep, ssr.aud_who, ssr.aud_who_create, ssr.aud_when_create, ssr.id, ssr._responsible_id
  • Filter: CASE WHEN (prm_2.nstac IS TRUE) THEN ((mc_2.case_type_id IS NULL) OR (mc_2.case_type_id <> 2)) ELSE true END
  • Buffers: shared hit=161910 read=585229 dirtied=210
  • I/O Timings: read=86352.709
39. 0.653 92,854.428 ↑ 10.9 3,072 1

Nested Loop (cost=17,010.83..1,190,355.55 rows=33,555 width=52) (actual time=48,951.436..92,854.428 rows=3,072 loops=1)

  • Output: prm_2.edit, prm_2.dat, prm_2.dep, prm_2.nstac, ssr.aud_who, ssr.aud_who_create, ssr.aud_when_create, ssr.id, ssr._responsible_id, ssr.md_case_id
  • Buffers: shared hit=147833 read=585042 dirtied=167
  • I/O Timings: read=86349.560
40. 0.016 0.016 ↑ 1.0 1 1

CTE Scan on prm prm_2 (cost=0.00..0.02 rows=1 width=35) (actual time=0.007..0.016 rows=1 loops=1)

  • Output: prm_2.d1, prm_2.d2, prm_2.clinicid, prm_2.step, prm_2.clos, prm_2.dtime1, prm_2.dtime2, prm_2.iskl, prm_2.iskl2, prm_2.stac, prm_2.dat, prm_2.medsis, prm_2.dep, prm_2.edit, prm_2.nstac
  • Filter: (prm_2.step IS FALSE)
41. 91,603.627 92,853.759 ↑ 10.9 3,072 1

Bitmap Heap Scan on public.sr_srv_rendered ssr (cost=17,010.83..1,190,019.98 rows=33,555 width=49) (actual time=48,951.361..92,853.759 rows=3,072 loops=1)

  • 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
  • Recheck Cond: (ssr.org_id = prm_2.clinicid)
  • Filter: (((ssr.aud_when_create)::date >= prm_2.d1) AND ((ssr.aud_when_create)::date <= prm_2.d2) AND CASE WHEN ((prm_2.dtime1 IS NOT NULL) AND (prm_2.dtime2 IS NOT NULL)) THEN (((ssr.aud_when_create)::time without time zone >= prm_2.dtime1) AND ((ssr.aud_when_create)::time without time zone <= prm_2.dtime2)) ELSE true END)
  • Rows Removed by Filter: 1530716
  • Heap Blocks: exact=724487
  • Buffers: shared hit=147833 read=585042 dirtied=167
  • I/O Timings: read=86349.560
42. 1,250.132 1,250.132 ↓ 2.5 1,538,354 1

Bitmap Index Scan on sr_srv_rendered_pim_organization_fk (cost=0.00..17,002.44 rows=603,984 width=0) (actual time=1,250.132..1,250.132 rows=1,538,354 loops=1)

  • Index Cond: (ssr.org_id = prm_2.clinicid)
  • Buffers: shared hit=13 read=8375
  • I/O Timings: read=955.993
43. 9.216 9.216 ↑ 1.0 1 3,072

Index Scan using mc_case_pk on public.mc_case mc_2 (cost=0.56..0.66 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=3,072)

  • Output: mc_2.id, mc_2.case_type_id
  • Index Cond: (mc_2.id = ssr.md_case_id)
  • Buffers: shared hit=14077 read=187 dirtied=43
  • I/O Timings: read=3.149
44. 3.072 3.072 ↑ 1.0 1 3,072

Index Scan using pim_employee_position_pk on public.pim_employee_position pep_2 (cost=0.29..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=3,072)

  • Output: pep_2.id, pep_2.dismissal_order_code, pep_2.start_date, pep_2.hiring_order_code, pep_2.rate, pep_2.end_date, pep_2.dismissal_reason_id, pep_2.employee_id, pep_2.employment_type_id, pep_2.hiring_type_id, pep_2.position_id, pep_2.position_type_id, pep_2.unit_id, pep_2.code, pep_2.aud_who, pep_2.aud_when, pep_2.aud_source, pep_2.aud_who_create, pep_2.aud_when_create, pep_2.aud_source_create, pep_2.target_training, pep_2.leaving_reason_id, pep_2.pref_prescription, pep_2.extra_payment
  • Index Cond: (pep_2.id = ssr._responsible_id)
  • Buffers: shared hit=9220
45. 1.696 4.096 ↓ 1.0 13,910 1

Hash (cost=419.05..419.05 rows=13,805 width=8) (actual time=4.096..4.096 rows=13,910 loops=1)

  • Output: pp_2.id, pp_2.department_id
  • Buckets: 16384 Batches: 1 Memory Usage: 672kB
  • Buffers: shared hit=281
46. 2.400 2.400 ↓ 1.0 13,910 1

Seq Scan on public.pim_position pp_2 (cost=0.00..419.05 rows=13,805 width=8) (actual time=0.011..2.400 rows=13,910 loops=1)

  • Output: pp_2.id, pp_2.department_id
  • Buffers: shared hit=281
47. 0.019 4.284 ↓ 0.0 0 1

GroupAggregate (cost=490,534.28..490,862.20 rows=16,396 width=35) (actual time=4.284..4.284 rows=0 loops=1)

  • Output: (CASE WHEN (prm_3.edit IS TRUE) THEN ssr_1.aud_who ELSE ssr_1.aud_who_create END), (CASE WHEN (prm_3.dat IS TRUE) THEN ssr_1.bdate ELSE NULL::date END), count(DISTINCT ssr_1.id)
  • Group Key: (CASE WHEN (prm_3.edit IS TRUE) THEN ssr_1.aud_who ELSE ssr_1.aud_who_create END), (CASE WHEN (prm_3.dat IS TRUE) THEN ssr_1.bdate ELSE NULL::date END)
  • Buffers: shared hit=281
48. 0.020 4.265 ↓ 0.0 0 1

Sort (cost=490,534.28..490,575.27 rows=16,396 width=35) (actual time=4.265..4.265 rows=0 loops=1)

  • Output: (CASE WHEN (prm_3.edit IS TRUE) THEN ssr_1.aud_who ELSE ssr_1.aud_who_create END), (CASE WHEN (prm_3.dat IS TRUE) THEN ssr_1.bdate ELSE NULL::date END), ssr_1.id
  • Sort Key: (CASE WHEN (prm_3.edit IS TRUE) THEN ssr_1.aud_who ELSE ssr_1.aud_who_create END), (CASE WHEN (prm_3.dat IS TRUE) THEN ssr_1.bdate ELSE NULL::date END)
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=281
49. 0.009 4.245 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,157.33..489,386.48 rows=16,396 width=35) (actual time=4.245..4.245 rows=0 loops=1)

  • Output: CASE WHEN (prm_3.edit IS TRUE) THEN ssr_1.aud_who ELSE ssr_1.aud_who_create END, CASE WHEN (prm_3.dat IS TRUE) THEN ssr_1.bdate ELSE NULL::date END, ssr_1.id
  • Filter: CASE WHEN (prm_3.nstac IS TRUE) THEN ((mc_3.case_type_id IS NULL) OR (mc_3.case_type_id <> 2)) ELSE true END
  • Buffers: shared hit=281
50. 0.108 4.236 ↓ 0.0 0 1

Hash Join (cost=2,156.77..467,371.51 rows=32,793 width=40) (actual time=4.236..4.236 rows=0 loops=1)

  • Output: prm_3.edit, prm_3.dat, prm_3.nstac, ssr_1.aud_who, ssr_1.aud_who_create, ssr_1.bdate, ssr_1.id, ssr_1.md_case_id
  • Hash Cond: (pep_3.position_id = pp_3.id)
  • Join Filter: CASE WHEN (prm_3.dep IS NOT NULL) THEN (ARRAY[pp_3.department_id] && supp.komi_dep_parent_arr(prm_3.dep)) ELSE true END
  • Buffers: shared hit=281
51. 0.019 0.039 ↓ 0.0 0 1

Hash Join (cost=1,565.16..449,317.63 rows=65,586 width=48) (actual time=0.039..0.039 rows=0 loops=1)

  • Output: prm_3.edit, prm_3.dat, prm_3.dep, prm_3.nstac, ssr_1.aud_who, ssr_1.aud_who_create, ssr_1.bdate, ssr_1.id, ssr_1.md_case_id, pep_3.position_id
  • Hash Cond: (ssr_1._responsible_id = pep_3.id)
52. 0.004 0.020 ↓ 0.0 0 1

Nested Loop (cost=0.56..446,845.52 rows=67,109 width=48) (actual time=0.020..0.020 rows=0 loops=1)

  • Output: prm_3.edit, prm_3.dat, prm_3.dep, prm_3.nstac, ssr_1.aud_who, ssr_1.aud_who_create, ssr_1.bdate, ssr_1.id, ssr_1._responsible_id, ssr_1.md_case_id
53. 0.016 0.016 ↓ 0.0 0 1

CTE Scan on prm prm_3 (cost=0.00..0.02 rows=1 width=19) (actual time=0.016..0.016 rows=0 loops=1)

  • Output: prm_3.d1, prm_3.d2, prm_3.clinicid, prm_3.step, prm_3.clos, prm_3.dtime1, prm_3.dtime2, prm_3.iskl, prm_3.iskl2, prm_3.stac, prm_3.dat, prm_3.medsis, prm_3.dep, prm_3.edit, prm_3.nstac
  • Filter: (prm_3.step IS TRUE)
  • Rows Removed by Filter: 1
54. 0.000 0.000 ↓ 0.0 0

Index Scan using sr_srv_rendered_bdate_edate_org_ix on public.sr_srv_rendered ssr_1 (cost=0.56..446,174.41 rows=67,109 width=45) (never executed)

  • Output: ssr_1.id, ssr_1.bdate, ssr_1.comment, ssr_1.cost, ssr_1.duration, ssr_1.edate, ssr_1.is_rendered, ssr_1.quantity, ssr_1.total_cost, ssr_1.contract_id, ssr_1.customer_id, ssr_1.funding_id, ssr_1.res_group_id, ssr_1.service_id, ssr_1.duration_measure_unit_id, ssr_1.begin_time, ssr_1.prototype_id, ssr_1.org_id, ssr_1.price_list_id, ssr_1.cul, ssr_1.parent_id, ssr_1.payment_status_id, ssr_1.root_service_id, ssr_1.is_wholly_rendered, ssr_1.planned_date, ssr_1.planned_time, ssr_1.tooth_number, ssr_1.is_refused, ssr_1.aud_who, ssr_1.aud_when, ssr_1.aud_source, ssr_1.aud_who_create, ssr_1.aud_when_create, ssr_1.aud_source_create, ssr_1.is_amalgam_filling, ssr_1.is_mobile_medical_teams, ssr_1._responsible_id, ssr_1._department_id, ssr_1._created_by_dep_id, ssr_1.entity_type, ssr_1.md_is_urgent, ssr_1.md_is_use_cryogenic, ssr_1.md_is_use_endoscopic, ssr_1.md_is_use_laser, ssr_1.md_anesthesia_type_id, ssr_1.md_step_id, ssr_1.md_complication_type_id, ssr_1.md_diagnosis_id, ssr_1.md_case_id, ssr_1.md_referral_id, ssr_1.md_result_category_id, ssr_1.md_patient_prescription_id, ssr_1.md_health_group_id, ssr_1.md_vmp_type_id, ssr_1.md_vmp_method_id, ssr_1.md_anatomic_zone_id, ssr_1.md_prescription_id, ssr_1.entity_sync_num, ssr_1.entity_type_aware, ssr_1.cancel_reason, ssr_1.is_vmp, ssr_1.close_date, ssr_1.complex_srv_id, ssr_1.for_upload, ssr_1.uid, ssr_1.is_need_consulting
  • Index Cond: ((ssr_1.bdate >= prm_3.d1) AND (ssr_1.bdate <= prm_3.d2) AND (ssr_1.org_id = prm_3.clinicid))
55. 0.000 0.000 ↓ 0.0 0

Hash (cost=1,055.93..1,055.93 rows=40,693 width=8) (never executed)

  • Output: pep_3.id, pep_3.position_id
56. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.pim_employee_position pep_3 (cost=0.00..1,055.93 rows=40,693 width=8) (never executed)

  • Output: pep_3.id, pep_3.position_id
57. 1.614 4.089 ↓ 1.0 13,910 1

Hash (cost=419.05..419.05 rows=13,805 width=8) (actual time=4.089..4.089 rows=13,910 loops=1)

  • Output: pp_3.id, pp_3.department_id
  • Buckets: 16384 Batches: 1 Memory Usage: 672kB
  • Buffers: shared hit=281
58. 2.475 2.475 ↓ 1.0 13,910 1

Seq Scan on public.pim_position pp_3 (cost=0.00..419.05 rows=13,805 width=8) (actual time=0.010..2.475 rows=13,910 loops=1)

  • Output: pp_3.id, pp_3.department_id
  • Buffers: shared hit=281
59. 0.000 0.000 ↓ 0.0 0

Index Scan using mc_case_pk on public.mc_case mc_3 (cost=0.56..0.66 rows=1 width=8) (never executed)

  • Output: mc_3.id, mc_3.case_type_id
  • Index Cond: (mc_3.id = ssr_1.md_case_id)
60.          

CTE tt_step

61. 0.602 7,155.133 ↑ 1.0 1 1

Aggregate (cost=53.98..53.99 rows=1 width=4) (actual time=7,155.133..7,155.133 rows=1 loops=1)

  • Output: count(DISTINCT tt1.msid)
  • Buffers: shared hit=352686 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
62. 7,154.531 7,154.531 ↑ 1.8 1,339 1

CTE Scan on tt1 (cost=0.00..47.98 rows=2,399 width=4) (actual time=1,441.279..7,154.531 rows=1,339 loops=1)

  • Output: tt1.login, tt1.d, tt1.mcid, tt1.msid, tt1.nesov, tt1.vzr, tt1.sir
  • Buffers: shared hit=352678 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
63.          

CTE tt2

64. 0.757 7,156.958 ↑ 6.3 38 1

GroupAggregate (cost=206.67..273.25 rows=240 width=64) (actual time=7,156.286..7,156.958 rows=38 loops=1)

  • Output: tt1_1.login, tt1_1.d, count(DISTINCT tt1_1.mcid), count(DISTINCT tt1_1.msid), count(DISTINCT tt1_1.nesov), count(DISTINCT tt1_1.vzr), count(DISTINCT tt1_1.sir), CASE WHEN ((tt_step.msid_all)::numeric <> '0'::numeric) THEN round((((count(DISTINCT tt1_1.msid))::numeric / (tt_step.msid_all)::numeric) * '100'::numeric), 2) ELSE NULL::numeric END, tt1_1.login, tt_step.msid_all
  • Group Key: tt1_1.login, tt1_1.d, tt_step.msid_all
  • Buffers: shared hit=352689 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
65. 0.796 7,156.201 ↑ 1.8 1,339 1

Sort (cost=206.67..212.67 rows=2,399 width=64) (actual time=7,156.117..7,156.201 rows=1,339 loops=1)

  • Output: tt1_1.d, tt1_1.login, tt_step.msid_all, tt1_1.mcid, tt1_1.msid, tt1_1.nesov, tt1_1.vzr, tt1_1.sir
  • Sort Key: tt1_1.login, tt1_1.d, tt_step.msid_all
  • Sort Method: quicksort Memory: 153kB
  • Buffers: shared hit=352689 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
66. 0.145 7,155.405 ↑ 1.8 1,339 1

Nested Loop (cost=0.00..71.99 rows=2,399 width=64) (actual time=7,155.162..7,155.405 rows=1,339 loops=1)

  • Output: tt1_1.d, tt1_1.login, tt_step.msid_all, tt1_1.mcid, tt1_1.msid, tt1_1.nesov, tt1_1.vzr, tt1_1.sir
  • Buffers: shared hit=352686 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
67. 7,155.143 7,155.143 ↑ 1.0 1 1

CTE Scan on tt_step (cost=0.00..0.02 rows=1 width=8) (actual time=7,155.143..7,155.143 rows=1 loops=1)

  • Output: tt_step.msid_all
  • Buffers: shared hit=352686 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
68. 0.117 0.117 ↑ 1.8 1,339 1

CTE Scan on tt1 tt1_1 (cost=0.00..47.98 rows=2,399 width=56) (actual time=0.003..0.117 rows=1,339 loops=1)

  • Output: tt1_1.login, tt1_1.d, tt1_1.mcid, tt1_1.msid, tt1_1.nesov, tt1_1.vzr, tt1_1.sir
69.          

CTE tt4

70. 0.078 100,041.032 ↑ 447.2 55 1

Hash Full Join (cost=10.22..1,670.32 rows=24,594 width=152) (actual time=100,035.846..100,041.032 rows=55 loops=1)

  • Output: COALESCE(tt2.login, tt3.login), COALESCE(tt2.d, tt3.d), tt2.mcid, tt2.msid, tt2.nesov, tt2.vzr, tt2.sir, tt3.ssrid, tt2.pro
  • Hash Cond: ((tt3.login)::text = (tt2.login)::text)
  • Join Filter: CASE WHEN (prm_4.dat IS TRUE) THEN (tt2.d = tt3.d) ELSE true END
  • Buffers: shared hit=524381 read=712508 dirtied=3812
  • I/O Timings: read=91825.805
71. 92,883.901 92,883.901 ↑ 512.4 48 1

CTE Scan on tt3 (cost=0.00..491.88 rows=24,594 width=44) (actual time=92,878.747..92,883.901 rows=48 loops=1)

  • Output: tt3.login, tt3.d, tt3.ssrid
  • Buffers: shared hit=171692 read=585229 dirtied=210
  • I/O Timings: read=86352.709
72. 0.046 7,157.053 ↑ 6.3 38 1

Hash (cost=7.22..7.22 rows=240 width=109) (actual time=7,157.053..7,157.053 rows=38 loops=1)

  • Output: prm_4.dat, tt2.login, tt2.d, tt2.mcid, tt2.msid, tt2.nesov, tt2.vzr, tt2.sir, tt2.pro
  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=352689 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
73. 0.019 7,157.007 ↑ 6.3 38 1

Nested Loop (cost=0.00..7.22 rows=240 width=109) (actual time=7,156.314..7,157.007 rows=38 loops=1)

  • Output: prm_4.dat, tt2.login, tt2.d, tt2.mcid, tt2.msid, tt2.nesov, tt2.vzr, tt2.sir, tt2.pro
  • Buffers: shared hit=352689 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
74. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on prm prm_4 (cost=0.00..0.02 rows=1 width=1) (actual time=0.000..0.001 rows=1 loops=1)

  • Output: prm_4.d1, prm_4.d2, prm_4.clinicid, prm_4.step, prm_4.clos, prm_4.dtime1, prm_4.dtime2, prm_4.iskl, prm_4.iskl2, prm_4.stac, prm_4.dat, prm_4.medsis, prm_4.dep, prm_4.edit, prm_4.nstac
75. 7,156.987 7,156.987 ↑ 6.3 38 1

CTE Scan on tt2 (cost=0.00..4.80 rows=240 width=108) (actual time=7,156.302..7,156.987 rows=38 loops=1)

  • Output: tt2.login, tt2.d, tt2.mcid, tt2.msid, tt2.nesov, tt2.vzr, tt2.sir, tt2.pro
  • Buffers: shared hit=352689 read=127279 dirtied=3602
  • I/O Timings: read=5473.096
76.          

CTE tt5

77. 0.299 101,171.500 ↑ 447.2 55 1

Nested Loop Left Join (cost=1,530.78..140,497,232.33 rows=24,594 width=234) (actual time=100,054.498..101,171.500 rows=55 loops=1)

  • Output: tt4.login, tt4.d, tt4.mcid, tt4.msid, tt4.nesov, tt4.vzr, tt4.sir, tt4.ssrid, tt4.pro, (array_to_string(array_agg(sr.name ORDER BY sur.role_id), ','::text)), (array_agg(sur.role_id ORDER BY sur.role_id)), (((((ind_2.surname)::text || ' '::text) || (ind_2.name)::text) || ' '::text) || (COALESCE(ind_2.patr_name, ''::character varying))::text), su.id, (SubPlan 7)
  • Buffers: shared hit=591087 read=719371 dirtied=3850
  • I/O Timings: read=92842.343
78. 1.643 100,076.591 ↑ 447.2 55 1

Hash Right Join (cost=1,520.84..65,927.82 rows=24,594 width=170) (actual time=100,045.458..100,076.591 rows=55 loops=1)

  • Output: tt4.login, tt4.d, tt4.mcid, tt4.msid, tt4.nesov, tt4.vzr, tt4.sir, tt4.ssrid, tt4.pro, su.id, ind_2.surname, ind_2.name, ind_2.patr_name
  • Hash Cond: (sup.id = su.id)
  • Buffers: shared hit=559828 read=712508 dirtied=3812
  • I/O Timings: read=91825.805
79. 3.226 30.303 ↑ 1.0 8,814 1

Nested Loop Left Join (cost=0.43..64,106.11 rows=8,858 width=54) (actual time=0.078..30.303 rows=8,814 loops=1)

  • Output: sup.id, ind_2.surname, ind_2.name, ind_2.patr_name
  • Buffers: shared hit=35262
80. 0.635 0.635 ↑ 1.0 8,814 1

Seq Scan on public.sec_user_party sup (cost=0.00..177.58 rows=8,858 width=8) (actual time=0.017..0.635 rows=8,814 loops=1)

  • Output: sup.id, sup.party_id, sup.aud_who, sup.aud_when, sup.aud_source, sup.aud_who_create, sup.aud_when_create, sup.aud_source_create
  • Buffers: shared hit=89
81. 26.442 26.442 ↑ 1.0 1 8,814

Index Scan using pim_individual_pk on public.pim_individual ind_2 (cost=0.43..7.21 rows=1 width=54) (actual time=0.003..0.003 rows=1 loops=8,814)

  • Output: ind_2.surname, ind_2.name, ind_2.patr_name, ind_2.id
  • Index Cond: (ind_2.id = sup.party_id)
  • Buffers: shared hit=35173
82. 0.222 100,044.645 ↑ 447.2 55 1

Hash (cost=1,212.98..1,212.98 rows=24,594 width=120) (actual time=100,044.645..100,044.645 rows=55 loops=1)

  • Output: tt4.login, tt4.d, tt4.mcid, tt4.msid, tt4.nesov, tt4.vzr, tt4.sir, tt4.ssrid, tt4.pro, su.id
  • Buckets: 32768 Batches: 1 Memory Usage: 262kB
  • Buffers: shared hit=524566 read=712508 dirtied=3812
  • I/O Timings: read=91825.805
83. 0.156 100,044.423 ↑ 447.2 55 1

Hash Left Join (cost=382.93..1,212.98 rows=24,594 width=120) (actual time=100,039.191..100,044.423 rows=55 loops=1)

  • Output: tt4.login, tt4.d, tt4.mcid, tt4.msid, tt4.nesov, tt4.vzr, tt4.sir, tt4.ssrid, tt4.pro, su.id
  • Hash Cond: ((tt4.login)::text = (su.login)::text)
  • Buffers: shared hit=524566 read=712508 dirtied=3812
  • I/O Timings: read=91825.805
84. 100,041.071 100,041.071 ↑ 447.2 55 1

CTE Scan on tt4 (cost=0.00..491.88 rows=24,594 width=116) (actual time=100,035.866..100,041.071 rows=55 loops=1)

  • Output: tt4.login, tt4.d, tt4.mcid, tt4.msid, tt4.nesov, tt4.vzr, tt4.sir, tt4.ssrid, tt4.pro
  • Buffers: shared hit=524381 read=712508 dirtied=3812
  • I/O Timings: read=91825.805
85. 1.565 3.196 ↓ 1.0 8,815 1

Hash (cost=272.97..272.97 rows=8,797 width=17) (actual time=3.196..3.196 rows=8,815 loops=1)

  • Output: su.id, su.login
  • Buckets: 16384 Batches: 1 Memory Usage: 551kB
  • Buffers: shared hit=185
86. 1.631 1.631 ↓ 1.0 8,815 1

Seq Scan on public.sec_user su (cost=0.00..272.97 rows=8,797 width=17) (actual time=0.009..1.631 rows=8,815 loops=1)

  • Output: su.id, su.login
  • Buffers: shared hit=185
87. 0.605 2.145 ↑ 1.0 1 55

Aggregate (cost=9.95..9.96 rows=1 width=37) (actual time=0.039..0.039 rows=1 loops=55)

  • Output: array_to_string(array_agg(sr.name ORDER BY sur.role_id), ','::text), array_agg(sur.role_id ORDER BY sur.role_id)
  • Buffers: shared hit=172 read=3
  • I/O Timings: read=0.311
88. 0.110 1.540 ↑ 1.0 2 55

Nested Loop (cost=8.35..9.93 rows=2 width=37) (actual time=0.023..0.028 rows=2 loops=55)

  • Output: sur.role_id, sr.name
  • Buffers: shared hit=172 read=3
  • I/O Timings: read=0.311
89. 0.055 0.055 ↑ 1.0 1 55

CTE Scan on prm prm_6 (cost=0.00..0.02 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=55)

  • Output: prm_6.d1, prm_6.d2, prm_6.clinicid, prm_6.step, prm_6.clos, prm_6.dtime1, prm_6.dtime2, prm_6.iskl, prm_6.iskl2, prm_6.stac, prm_6.dat, prm_6.medsis, prm_6.dep, prm_6.edit, prm_6.nstac
90. 0.444 1.375 ↑ 1.0 2 55

Hash Join (cost=8.35..9.89 rows=2 width=37) (actual time=0.020..0.025 rows=2 loops=55)

  • Output: sur.role_id, sr.name
  • Hash Cond: (sr.id = sur.role_id)
  • Buffers: shared hit=172 read=3
  • I/O Timings: read=0.311
91. 0.216 0.216 ↓ 1.1 39 54

Seq Scan on public.sec_role sr (cost=0.00..1.35 rows=35 width=37) (actual time=0.001..0.004 rows=39 loops=54)

  • Output: sr.id, sr.version, sr.name, sr.note, sr.aud_who, sr.aud_when, sr.aud_source, sr.aud_who_create, sr.aud_when_create, sr.aud_source_create
  • Buffers: shared hit=54
92. 0.110 0.715 ↑ 1.0 2 55

Hash (cost=8.32..8.32 rows=2 width=4) (actual time=0.013..0.013 rows=2 loops=55)

  • Output: sur.role_id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=118 read=3
  • I/O Timings: read=0.311
93. 0.605 0.605 ↑ 1.0 2 55

Index Only Scan using ak_userrole on public.sec_user_role sur (cost=0.29..8.32 rows=2 width=4) (actual time=0.010..0.011 rows=2 loops=55)

  • Output: sur.role_id
  • Index Cond: (sur.user_id = su.id)
  • Heap Fetches: 17
  • Buffers: shared hit=118 read=3
  • I/O Timings: read=0.311
94.          

SubPlan (forNested Loop Left Join)

95. 0.385 1,092.465 ↑ 1.0 1 55

Aggregate (cost=5,699.98..5,699.99 rows=1 width=4) (actual time=19.863..19.863 rows=1 loops=55)

  • Output: count(DISTINCT sus.id)
  • Buffers: shared hit=31087 read=6860 dirtied=38
  • I/O Timings: read=1016.227
96. 8.250 1,092.080 ↓ 0.0 0 55

Nested Loop (cost=0.43..5,699.55 rows=174 width=4) (actual time=19.856..19.856 rows=0 loops=55)

  • Output: sus.id
  • Join Filter: ((sus.from_dt >= prm_5.d1) AND (sus.from_dt <= prm_5.d2))
  • Rows Removed by Join Filter: 663
  • Buffers: shared hit=31087 read=6860 dirtied=38
  • I/O Timings: read=1016.227
97. 0.055 0.055 ↑ 1.0 1 55

CTE Scan on prm prm_5 (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=55)

  • Output: prm_5.d1, prm_5.d2, prm_5.clinicid, prm_5.step, prm_5.clos, prm_5.dtime1, prm_5.dtime2, prm_5.iskl, prm_5.iskl2, prm_5.stac, prm_5.dat, prm_5.medsis, prm_5.dep, prm_5.edit, prm_5.nstac
98. 1,083.775 1,083.775 ↑ 2.4 663 55

Index Scan using sec_user_session_user_id_idx on public.sec_user_session sus (cost=0.43..5,675.98 rows=1,570 width=12) (actual time=0.013..19.705 rows=663 loops=55)

  • Output: sus.id, sus.from_dt, sus.ip, sus.to_dt, sus.user_agent, sus.web_session_uid, sus.term_cause_id, sus.user_id, sus.use_session_context, sus.close_session_context, sus.counter, sus.last_req_dt, sus.show_welcome_page, sus.show_pass_notification, sus.aud_who, sus.aud_when, sus.aud_source, sus.aud_who_create, sus.aud_when_create, sus.aud_source_create
  • Index Cond: (sus.user_id = su.id)
  • Buffers: shared hit=31087 read=6860 dirtied=38
  • I/O Timings: read=1016.227
99.          

CTE tt6

100. 0.117 101,171.972 ↑ 343.3 9 1

Sort (cost=1,101.41..1,109.14 rows=3,090 width=224) (actual time=101,171.970..101,171.972 rows=9 loops=1)

  • Output: tt5.login, tt5.d, tt5.mcid, tt5.msid, tt5.nesov, tt5.vzr, tt5.sir, tt5.ssrid, tt5.pro, tt5.rol, tt5.rol2, tt5.fio, tt5.suid, tt5.aktinost
  • Sort Key: tt5.login, tt5.d
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=591093 read=719371 dirtied=3850
  • I/O Timings: read=92842.343
101. 0.194 101,171.855 ↑ 343.3 9 1

Nested Loop (cost=0.00..922.30 rows=3,090 width=224) (actual time=100,373.372..101,171.855 rows=9 loops=1)

  • Output: tt5.login, tt5.d, tt5.mcid, tt5.msid, tt5.nesov, tt5.vzr, tt5.sir, tt5.ssrid, tt5.pro, tt5.rol, tt5.rol2, tt5.fio, tt5.suid, tt5.aktinost
  • Join Filter: (CASE WHEN (prm_7.iskl IS TRUE) THEN (NOT ('{16,26}'::integer[] && tt5.rol2)) ELSE true END AND CASE WHEN (prm_7.iskl2 IS TRUE) THEN ('{16,26}'::integer[] && tt5.rol2) ELSE true END AND ((prm_7.medsis IS FALSE) OR (tt5.rol2 && '{39}'::integer[])))
  • Rows Removed by Join Filter: 46
  • Buffers: shared hit=591087 read=719371 dirtied=3850
  • I/O Timings: read=92842.343
102. 0.006 0.006 ↑ 1.0 1 1

CTE Scan on prm prm_7 (cost=0.00..0.02 rows=1 width=3) (actual time=0.006..0.006 rows=1 loops=1)

  • Output: prm_7.d1, prm_7.d2, prm_7.clinicid, prm_7.step, prm_7.clos, prm_7.dtime1, prm_7.dtime2, prm_7.iskl, prm_7.iskl2, prm_7.stac, prm_7.dat, prm_7.medsis, prm_7.dep, prm_7.edit, prm_7.nstac
103. 101,171.655 101,171.655 ↑ 447.2 55 1

CTE Scan on tt5 (cost=0.00..491.88 rows=24,594 width=224) (actual time=100,054.514..101,171.655 rows=55 loops=1)

  • Output: tt5.login, tt5.d, tt5.mcid, tt5.msid, tt5.nesov, tt5.vzr, tt5.sir, tt5.ssrid, tt5.pro, tt5.rol, tt5.rol2, tt5.fio, tt5.suid, tt5.aktinost
  • Buffers: shared hit=591087 read=719371 dirtied=3850
  • I/O Timings: read=92842.343
Planning time : 12.084 ms
Execution time : 101,182.097 ms