explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5lL3

Settings
# exclusive inclusive rows x rows loops node
1. 3.880 2,145.736 ↓ 1.3 2,182 1

Nested Loop Left Join (cost=13,837.97..114,129.03 rows=1,643 width=0) (actual time=34.116..2,145.736 rows=2,182 loops=1)

  • Filter: CASE WHEN ($78 IS NOT NULL) THEN (alternatives: SubPlan 54 or hashed SubPlan 55) ELSE true END
  • Buffers: shared hit=5219 read=1070
  • I/O Timings: read=2078.802
2.          

CTE _prm

3. 0.016 0.016 ↑ 1.0 1 1

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

  • Output: 33693, to_date('01.01.2019'::text, 'dd.mm.yyyy'::text), to_date('10.01.2019'::text, 'dd.mm.yyyy'::text), 'false'::text, 4664, NULL::integer
4.          

CTE prm

5. 0.029 0.029 ↑ 1.0 1 1

CTE Scan on _prm (cost=0.00..0.03 rows=1 width=48) (actual time=0.028..0.029 rows=1 loops=1)

  • Output: _prm.cln, _prm.d1, _prm.d2, _prm.is_detail, _prm.dep, ((_prm.d1 - '1 year'::interval))::date, ((_prm.d2 - '1 year'::interval))::date
6.          

CTE sec_org

7. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.43..10.65 rows=2 width=95) (never executed)

  • Output: suo.org_id, CASE WHEN (_prm_1.is_detail = 'true'::text) THEN mc_1.short_name ELSE NULL::character varying END
8. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.29..10.30 rows=2 width=36) (never executed)

  • Output: _prm_1.is_detail, suo.org_id
9. 0.000 0.000 ↓ 0.0 0

CTE Scan on _prm _prm_1 (cost=0.00..0.02 rows=1 width=36) (never executed)

  • Output: _prm_1.cln, _prm_1.d1, _prm_1.d2, _prm_1.is_detail, _prm_1.us, _prm_1.dep
  • Filter: (_prm_1.cln IS NULL)
10. 0.000 0.000 ↓ 0.0 0

Index Scan using sec_user_org_user_id_idx on public.sec_user_org suo (cost=0.29..10.26 rows=2 width=8) (never executed)

  • Output: suo.id, suo.user_id, suo.org_id, suo.aud_who, suo.aud_when, suo.aud_source, suo.aud_who_create, suo.aud_when_create, suo.aud_source_create
  • Index Cond: (suo.user_id = _prm_1.us)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using md_clinic_pk on public.md_clinic mc_1 (cost=0.14..0.16 rows=1 width=63) (never executed)

  • Output: mc_1.short_name, mc_1.id
  • Index Cond: (mc_1.id = suo.org_id)
12.          

CTE all_deps

13. 0.000 0.000 ↓ 0.0 0

Recursive Union (cost=0.28..2,073.26 rows=731 width=4) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..8.33 rows=1 width=4) (never executed)

  • Output: pd.id
15. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm (cost=0.00..0.02 rows=1 width=8) (never executed)

  • Output: prm.cln, prm.d1, prm.d2, prm.is_detail, prm.dep, prm.d1_last, prm.d2_last
16. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_department_pk on public.pim_department pd (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Output: pd.id, pd.is_available_diagnosis, pd.code, pd.from_dt, pd.name, pd.to_dt, pd.accounting_center_id, pd.type_id, pd.funding_id, pd.org_id, pd.parent_id, pd.sphere_id, pd.is_payment, pd.unit_id, pd.kind_id, pd.e_code, pd.scope_id, pd.aud_who, pd.aud_when, pd.aud_source, pd.aud_who_create, pd.aud_when_create, pd.aud_source_create, pd.is_separate, pd.is_social_significant, pd.age_group_id, pd.is_branch_type, pd.visits_per_shift, pd.departures_per_shift, pd.visits_per_day, pd.necropsies_per_day, pd.clinical_trials_per_shift, pd.brigades_amount, pd.ose, pd.ose_reject, pd.at_home, pd.longitude, pd.latitude, pd.amb_reception_exists, pd.cadastral_number, pd.hospital_mode_id, pd.type_profile_id
  • Index Cond: (pd.id = prm.dep)
  • Filter: (prm.cln = pd.org_id)
17. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..205.03 rows=73 width=4) (never executed)

  • Output: pd_1.id
18. 0.000 0.000 ↓ 0.0 0

WorkTable Scan on all_deps (cost=0.00..0.20 rows=10 width=4) (never executed)

  • Output: all_deps.id
19. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_department_parent_id_idx on public.pim_department pd_1 (cost=0.28..20.41 rows=7 width=8) (never executed)

  • Output: pd_1.id, pd_1.is_available_diagnosis, pd_1.code, pd_1.from_dt, pd_1.name, pd_1.to_dt, pd_1.accounting_center_id, pd_1.type_id, pd_1.funding_id, pd_1.org_id, pd_1.parent_id, pd_1.sphere_id, pd_1.is_payment, pd_1.unit_id, pd_1.kind_id, pd_1.e_code, pd_1.scope_id, pd_1.aud_who, pd_1.aud_when, pd_1.aud_source, pd_1.aud_who_create, pd_1.aud_when_create, pd_1.aud_source_create, pd_1.is_separate, pd_1.is_social_significant, pd_1.age_group_id, pd_1.is_branch_type, pd_1.visits_per_shift, pd_1.departures_per_shift, pd_1.visits_per_day, pd_1.necropsies_per_day, pd_1.clinical_trials_per_shift, pd_1.brigades_amount, pd_1.ose, pd_1.ose_reject, pd_1.at_home, pd_1.longitude, pd_1.latitude, pd_1.amb_reception_exists, pd_1.cadastral_number, pd_1.hospital_mode_id, pd_1.type_profile_id
  • Index Cond: (pd_1.parent_id = all_deps.id)
20.          

CTE _pd

21. 0.000 0.000 ↓ 0.0 0

Recursive Union (cost=4.32..2,685.43 rows=505 width=66) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.32..21.86 rows=5 width=66) (never executed)

  • Output: pd_2.id, pd_2.parent_id, pd_2.name
23. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_1 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_1.cln, prm_1.d1, prm_1.d2, prm_1.is_detail, prm_1.dep, prm_1.d1_last, prm_1.d2_last
24. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.pim_department pd_2 (cost=4.32..21.79 rows=5 width=70) (never executed)

  • Output: pd_2.id, pd_2.is_available_diagnosis, pd_2.code, pd_2.from_dt, pd_2.name, pd_2.to_dt, pd_2.accounting_center_id, pd_2.type_id, pd_2.funding_id, pd_2.org_id, pd_2.parent_id, pd_2.sphere_id, pd_2.is_payment, pd_2.unit_id, pd_2.kind_id, pd_2.e_code, pd_2.scope_id, pd_2.aud_who, pd_2.aud_when, pd_2.aud_source, pd_2.aud_who_create, pd_2.aud_when_create, pd_2.aud_source_create, pd_2.is_separate, pd_2.is_social_significant, pd_2.age_group_id, pd_2.is_branch_type, pd_2.visits_per_shift, pd_2.departures_per_shift, pd_2.visits_per_day, pd_2.necropsies_per_day, pd_2.clinical_trials_per_shift, pd_2.brigades_amount, pd_2.ose, pd_2.ose_reject, pd_2.at_home, pd_2.longitude, pd_2.latitude, pd_2.amb_reception_exists, pd_2.cadastral_number, pd_2.hospital_mode_id, pd_2.type_profile_id
  • Recheck Cond: (pd_2.org_id = prm_1.cln)
25. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pim_department_org_id_idx (cost=0.00..4.32 rows=5 width=0) (never executed)

  • Index Cond: (pd_2.org_id = prm_1.cln)
26. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.62..265.35 rows=50 width=66) (never executed)

  • Output: _pd.id, d.parent_id, d.name
  • Hash Cond: (d.id = _pd.parent_id)
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.pim_department d (cost=0.00..236.98 rows=6,998 width=66) (never executed)

  • Output: d.id, d.is_available_diagnosis, d.code, d.from_dt, d.name, d.to_dt, d.accounting_center_id, d.type_id, d.funding_id, d.org_id, d.parent_id, d.sphere_id, d.is_payment, d.unit_id, d.kind_id, d.e_code, d.scope_id, d.aud_who, d.aud_when, d.aud_source, d.aud_who_create, d.aud_when_create, d.aud_source_create, d.is_separate, d.is_social_significant, d.age_group_id, d.is_branch_type, d.visits_per_shift, d.departures_per_shift, d.visits_per_day, d.necropsies_per_day, d.clinical_trials_per_shift, d.brigades_amount, d.ose, d.ose_reject, d.at_home, d.longitude, d.latitude, d.amb_reception_exists, d.cadastral_number, d.hospital_mode_id, d.type_profile_id
28. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.00..1.00 rows=50 width=8) (never executed)

  • Output: _pd.id, _pd.parent_id
29. 0.000 0.000 ↓ 0.0 0

WorkTable Scan on _pd (cost=0.00..1.00 rows=50 width=8) (never executed)

  • Output: _pd.id, _pd.parent_id
30.          

CTE pd

31. 0.000 0.000 ↓ 0.0 0

CTE Scan on _pd _pd_1 (cost=0.00..10.10 rows=3 width=520) (never executed)

  • Output: _pd_1.id, _pd_1.dep_name
  • Filter: (_pd_1.parent_id IS NULL)
32.          

CTE emps1

33. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=29.77..4,728.16 rows=94 width=590) (never executed)

  • Output: pep_1.employee_id, (SubPlan 7), $16, concat_ws(' '::text, CASE WHEN ($17 IS NULL) THEN (('<i>'::text || (COALESCE(pd_3.dep_name, 'Hе указано'::character varying))::text) || '</i>,'::text) ELSE NULL::text END, i.surname, i.name, i.patr_name, (', ТН: '::text || NULLIF((pe.number)::text, ''::text))), (((cat.e_code)::text = '1'::text) OR (((cat.e_code)::text = '10'::text) AND ((r.e_code)::text = ANY ('{10002,10003,10233,10235}'::text[])))), ((cat.e_code)::text = ANY ('{2,3}'::text[])), (($18 >= pep_1.start_date) AND ($19 <= COALESCE((pep_1.end_date - 1), $20))), (($21 >= pep_1.start_date) AND ($22 <= COALESCE((pep_1.end_date - 1), $23)))
34.          

Initplan (for Nested Loop)

35. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..0.02 rows=1 width=32) (never executed)

  • Output: sec_org.short_name
36. 0.000 0.000 ↓ 0.0 0

CTE Scan on sec_org (cost=0.00..0.04 rows=2 width=32) (never executed)

  • Output: sec_org.short_name
37. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_2 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_2.dep
38. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_3 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_3.d2
39. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_4 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_4.d2
40. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_5 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_5.d2
41. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_6 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_6.d2_last
42. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_7 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_7.d2_last
43. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_8 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_8.d2_last
44. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_9 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_9.cln
45. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: sec_org_1.org_id
46. 0.000 0.000 ↓ 0.0 0

CTE Scan on sec_org sec_org_1 (cost=0.00..0.04 rows=2 width=4) (never executed)

  • Output: sec_org_1.org_id
47. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_10 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_10.dep
48. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_11 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_11.dep
49. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=29.10..2,683.29 rows=94 width=541) (never executed)

  • Output: r.e_code, cat.e_code, pep_1.employee_id, pep_1.start_date, pep_1.end_date, pe.individual_id, pe.number, pd_3.dep_name
50. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=28.81..2,649.41 rows=94 width=534) (never executed)

  • Output: r.e_code, cat.e_code, pep_1.employee_id, pep_1.start_date, pep_1.end_date, pd_3.dep_name
51. 0.000 0.000 ↓ 0.0 0

Hash Left Join (cost=28.52..2,049.90 rows=43 width=526) (never executed)

  • Output: pp_1.id, r.e_code, cat.e_code, pd_3.dep_name
  • Hash Cond: (pp_1.department_id = pd_3.id)
52. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=28.42..2,049.63 rows=43 width=14) (never executed)

  • Output: pp_1.id, pp_1.department_id, r.e_code, cat.e_code
  • Join Filter: ((r.category_id = cat.id) AND (((cat.e_code)::text = ANY ('{1,2,3}'::text[])) OR (((cat.e_code)::text = '10'::text) AND ((r.e_code)::text = ANY ('{10002,10003,10233,10235}'::text[])))))
53. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=28.42..2,031.52 rows=161 width=16) (never executed)

  • Output: pp_1.id, pp_1.department_id, r.e_code, r.category_id
  • Hash Cond: (pp_1.role_id = r.id)
54. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.pim_position pp_1 (cost=14.75..2,015.63 rows=161 width=12) (never executed)

  • Output: pp_1.id, pp_1.code, pp_1.start_date, pp_1.name, pp_1.end_date, pp_1.department_id, pp_1.organization_id, pp_1.role_id, pp_1.speciality_id, pp_1.rate, pp_1.aud_who, pp_1.aud_when, pp_1.aud_source, pp_1.aud_who_create, pp_1.aud_when_create, pp_1.aud_source_create, pp_1.pref_prescription, pp_1.payment_fond, pp_1.staff_event1, pp_1.staff_event2, pp_1.note, pp_1.fact_employment, pp_1.external_rate, pp_1.billing_code
  • Recheck Cond: (pp_1.organization_id = COALESCE($24, $25))
  • Filter: ((($26 IS NOT NULL) AND (alternatives: SubPlan 19 or hashed SubPlan 20)) OR ($29 IS NULL))
55. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pim_position_organization_id_idx (cost=0.00..14.71 rows=323 width=0) (never executed)

  • Index Cond: (pp_1.organization_id = COALESCE($24, $25))
56.          

SubPlan (for Bitmap Heap Scan)

57. 0.000 0.000 ↓ 0.0 0

CTE Scan on all_deps all_deps_1 (cost=0.00..16.45 rows=4 width=0) (never executed)

  • Filter: (all_deps_1.id = pp_1.department_id)
58. 0.000 0.000 ↓ 0.0 0

CTE Scan on all_deps all_deps_2 (cost=0.00..14.62 rows=731 width=4) (never executed)

  • Output: all_deps_2.id
59. 0.000 0.000 ↓ 0.0 0

Hash (cost=9.41..9.41 rows=341 width=12) (never executed)

  • Output: r.e_code, r.id, r.category_id
60. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.pim_position_role r (cost=0.00..9.41 rows=341 width=12) (never executed)

  • Output: r.e_code, r.id, r.category_id
61. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.22 rows=4 width=6) (never executed)

  • Output: cat.e_code, cat.id
62. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.pim_position_category cat (cost=0.00..1.20 rows=4 width=6) (never executed)

  • Output: cat.e_code, cat.id
  • Filter: (((cat.e_code)::text = ANY ('{1,2,3}'::text[])) OR ((cat.e_code)::text = '10'::text))
63. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.06..0.06 rows=3 width=520) (never executed)

  • Output: pd_3.dep_name, pd_3.id
64. 0.000 0.000 ↓ 0.0 0

CTE Scan on pd pd_3 (cost=0.00..0.06 rows=3 width=520) (never executed)

  • Output: pd_3.dep_name, pd_3.id
65. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_employee_position_position_id_idx on public.pim_employee_position pep_1 (cost=0.29..13.90 rows=4 width=16) (never executed)

  • Output: pep_1.id, pep_1.dismissal_order_code, pep_1.start_date, pep_1.hiring_order_code, pep_1.rate, pep_1.end_date, pep_1.dismissal_reason_id, pep_1.employee_id, pep_1.employment_type_id, pep_1.hiring_type_id, pep_1.position_id, pep_1.position_type_id, pep_1.unit_id, pep_1.code, pep_1.aud_who, pep_1.aud_when, pep_1.aud_source, pep_1.aud_who_create, pep_1.aud_when_create, pep_1.aud_source_create, pep_1.pref_prescription, pep_1.extra_payment, pep_1.target_training, pep_1.leaving_reason_id
  • Index Cond: (pep_1.position_id = pp_1.id)
66. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_employee_pk on public.pim_employee pe (cost=0.29..0.35 rows=1 width=11) (never executed)

  • 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_1.employee_id)
67. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_individual_pk on public.pim_individual i (cost=0.43..5.04 rows=1 width=53) (never executed)

  • Output: i.id, i.birth_dt, i.death_dt, i.has_citizenship, i.name, i.patr_name, i.surname, i.gender_id, i.nationality_id, i.list_identity_doc, i.list_oms_doc, i.list_job_org, i.list_reg_name, i.list_snils, i.list_uid, i.aud_who, i.aud_when, i.aud_source, i.aud_who_create, i.aud_when_create, i.aud_source_create, i.birth_place, i.age_year, i.age_month, i.age_day, i.list_main_contact, i.is_only_birth_year
  • Index Cond: (i.id = pe.individual_id)
68.          

SubPlan (for Nested Loop)

69. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=16.65..16.66 rows=1 width=11) (never executed)

  • Output: array_agg(lower((su.login)::text))
70. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..16.64 rows=1 width=11) (never executed)

  • Output: su.login
71. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.28..8.33 rows=1 width=4) (never executed)

  • Output: sup.id
72. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm t (cost=0.00..0.02 rows=1 width=0) (never executed)

  • Output: t.cln, t.d1, t.d2, t.is_detail, t.dep, t.d1_last, t.d2_last
73. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_sec_user_party on public.sec_user_party sup (cost=0.28..8.30 rows=1 width=4) (never executed)

  • 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
  • Index Cond: (sup.party_id = pe.individual_id)
74. 0.000 0.000 ↓ 0.0 0

Index Scan using sec_user_pk on public.sec_user su (cost=0.28..8.30 rows=1 width=15) (never executed)

  • Output: su.id, su.close_dt, su.comment, su.cr_dt, su.email, su.login, su.password, su.version, su.blocked, su.scope_id, su.use_global_context, su.aud_who, su.aud_when, su.aud_source, su.aud_who_create, su.aud_when_create, su.aud_source_create
  • Index Cond: (su.id = sup.id)
75.          

CTE _emps2

76. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..1,285.07 rows=70 width=156) (never executed)

  • Output: emps1.employee_id, emps1.login, emps1.short_name, emps1.doctor, emps1.is_dctrs, emps1.is_sred_med, emps1.is_in, emps1.is_last_in, prm_12.is_detail, prm_12.cln, (SubPlan 23), (SubPlan 24), (SubPlan 25), (SubPlan 26)
77. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_12 (cost=0.00..0.02 rows=1 width=52) (never executed)

  • Output: prm_12.cln, prm_12.d1, prm_12.d2, prm_12.is_detail, prm_12.dep, prm_12.d1_last, prm_12.d2_last
78. 0.000 0.000 ↓ 0.0 0

CTE Scan on emps1 (cost=0.00..1.88 rows=70 width=104) (never executed)

  • Output: emps1.employee_id, emps1.login, emps1.short_name, emps1.doctor, emps1.is_dctrs, emps1.is_sred_med, emps1.is_in, emps1.is_last_in
  • Filter: (emps1.is_in OR emps1.is_last_in)
79.          

SubPlan (for Nested Loop)

80. 0.000 0.000 ↓ 0.0 0

Index Scan using mc_step_lower_aud_who_create_admission_date_idx on public.mc_step steps (cost=0.56..25,745.85 rows=6,410 width=0) (never executed)

  • Index Cond: ((lower((steps.aud_who_create)::text) = ANY (emps1.login)) AND (steps.admission_date >= prm_12.d1) AND (steps.admission_date <= prm_12.d2))
81. 0.000 0.000 ↓ 0.0 0

Index Scan using mc_step_lower_aud_who_create_admission_date_idx on public.mc_step steps_last (cost=0.56..25,745.85 rows=6,410 width=0) (never executed)

  • Index Cond: ((lower((steps_last.aud_who_create)::text) = ANY (emps1.login)) AND (steps_last.admission_date >= prm_12.d1_last) AND (steps_last.admission_date <= prm_12.d2_last))
82. 0.000 0.000 ↓ 0.0 0

Index Scan using sr_srv_rendered_lower_aud_who_create_bdate_idx on public.sr_srv_rendered srvs (cost=0.56..59,257.56 rows=14,753 width=0) (never executed)

  • Index Cond: ((lower((srvs.aud_who_create)::text) = ANY (emps1.login)) AND (srvs.bdate >= prm_12.d1) AND (srvs.bdate <= prm_12.d2))
83. 0.000 0.000 ↓ 0.0 0

Index Scan using sr_srv_rendered_lower_aud_who_create_bdate_idx on public.sr_srv_rendered srvs_last (cost=0.56..59,257.56 rows=14,753 width=0) (never executed)

  • Index Cond: ((lower((srvs_last.aud_who_create)::text) = ANY (emps1.login)) AND (srvs_last.bdate >= prm_12.d1_last) AND (srvs_last.bdate <= prm_12.d2_last))
84.          

CTE emps2

85. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=3.55..9.50 rows=70 width=112) (never executed)

  • Output: _emps2.short_name, NULLIF(count(DISTINCT CASE WHEN (_emps2.is_dctrs AND _emps2.is_in) THEN _emps2.employee_id ELSE NULL::integer END), 0), string_agg(DISTINCT CASE WHEN ((_emps2.is_detail = 'true'::text) AND _emps2.is_dctrs AND _emps2.is_in AND (_emps2.cln IS NOT NULL)) THEN _emps2.doctor ELSE NULL::text END, '<br>'::text), NULLIF(count(DISTINCT CASE WHEN (_emps2.is_dctrs AND _emps2.is_last_in) THEN _emps2.employee_id ELSE NULL::integer END), 0), NULLIF(count(DISTINCT CASE WHEN (_emps2.is_dctrs AND _emps2.is_in AND (_emps2.steps OR _emps2.srvs)) THEN _emps2.employee_id ELSE NULL::integer END), 0), string_agg(DISTINCT CASE WHEN ((_emps2.is_detail = 'true'::text) AND _emps2.is_dctrs AND _emps2.is_in AND (_emps2.cln IS NOT NULL) AND (_emps2.steps OR _emps2.srvs)) THEN _emps2.doctor ELSE NULL::text END, '<br>'::text), NULLIF(count(DISTINCT CASE WHEN (_emps2.is_dctrs AND _emps2.is_last_in AND (_emps2.steps_last OR _emps2.srvs_last)) THEN _emps2.employee_id ELSE NULL::integer END), 0), NULLIF(count(DISTINCT CASE WHEN (_emps2.is_sred_med AND _emps2.is_in) THEN _emps2.employee_id ELSE NULL::integer END), 0), string_agg(DISTINCT CASE WHEN ((_emps2.is_detail = 'true'::text) AND _emps2.is_sred_med AND _emps2.is_in AND (_emps2.cln IS NOT NULL)) THEN _emps2.doctor ELSE NULL::text END, '<br>'::text), NULLIF(count(DISTINCT CASE WHEN (_emps2.is_sred_med AND _emps2.is_last_in) THEN _emps2.employee_id ELSE NULL::integer END), 0), NULLIF(count(DISTINCT CASE WHEN (_emps2.is_sred_med AND _emps2.is_in AND (_emps2.steps OR _emps2.srvs)) THEN _emps2.employee_id ELSE NULL::integer END), 0), string_agg(DISTINCT CASE WHEN ((_emps2.is_detail = 'true'::text) AND _emps2.is_sred_med AND _emps2.is_in AND (_emps2.cln IS NOT NULL) AND COALESCE(_emps2.steps, _emps2.srvs)) THEN _emps2.doctor ELSE NULL::text END, '<br>'::text), NULLIF(count(DISTINCT CASE WHEN (_emps2.is_sred_med AND _emps2.is_last_in AND (_emps2.steps_last OR _emps2.srvs_last)) THEN _emps2.employee_id ELSE NULL::integer END), 0)
  • Group Key: _emps2.short_name
86. 0.000 0.000 ↓ 0.0 0

Sort (cost=3.55..3.72 rows=70 width=112) (never executed)

  • Output: _emps2.short_name, _emps2.is_dctrs, _emps2.is_in, _emps2.employee_id, _emps2.is_detail, _emps2.cln, _emps2.doctor, _emps2.is_last_in, _emps2.steps, _emps2.srvs, _emps2.steps_last, _emps2.srvs_last, _emps2.is_sred_med
  • Sort Key: _emps2.short_name
87. 0.000 0.000 ↓ 0.0 0

CTE Scan on _emps2 (cost=0.00..1.40 rows=70 width=112) (never executed)

  • Output: _emps2.short_name, _emps2.is_dctrs, _emps2.is_in, _emps2.employee_id, _emps2.is_detail, _emps2.cln, _emps2.doctor, _emps2.is_last_in, _emps2.steps, _emps2.srvs, _emps2.steps_last, _emps2.srvs_last, _emps2.is_sred_med
88.          

CTE emps3

89. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=3.54..3.57 rows=1 width=64) (never executed)

  • Output: NULL::text, NULLIF(sum(emps2.main_emp), '0'::numeric), NULLIF(sum(emps2.last_main_emp), '0'::numeric), NULLIF(sum(emps2.work_main_emp), '0'::numeric), NULLIF(sum(emps2.last_work_main_emp), '0'::numeric), NULLIF(sum(emps2.middle_emp), '0'::numeric), NULLIF(sum(emps2.last_middle_emp), '0'::numeric), NULLIF(sum(emps2.work_middle_emp), '0'::numeric), NULLIF(sum(emps2.last_work_middle_emp), '0'::numeric)
90. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.12 rows=70 width=64) (never executed)

  • Output: emps2.main_emp, emps2.last_main_emp, emps2.work_main_emp, emps2.last_work_main_emp, emps2.middle_emp, emps2.last_middle_emp, emps2.work_middle_emp, emps2.last_work_middle_emp
91. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_13 (cost=0.00..0.02 rows=1 width=0) (never executed)

  • Output: prm_13.cln, prm_13.d1, prm_13.d2, prm_13.is_detail, prm_13.dep, prm_13.d1_last, prm_13.d2_last
  • Filter: ((prm_13.cln IS NULL) AND (prm_13.is_detail = 'true'::text))
92. 0.000 0.000 ↓ 0.0 0

CTE Scan on emps2 (cost=0.00..1.40 rows=70 width=64) (never executed)

  • Output: emps2.short_name, emps2.main_emp, emps2.main_emp_txt, emps2.last_main_emp, emps2.work_main_emp, emps2.work_main_emp_txt, emps2.last_work_main_emp, emps2.middle_emp, emps2.middle_emp_txt, emps2.last_middle_emp, emps2.work_middle_emp, emps2.work_middle_emp_txt, emps2.last_work_middle_emp
93.          

CTE emps4

94. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=7.03..7.06 rows=1 width=64) (never executed)

  • Output: (('<table>'::text || string_agg((((('<tr><td colspan="13">'::text || (emps2_1.short_name)::text) || '</td><td colspan="3">'::text) || (emps2_1.main_emp)::text) || '</td>'::text), '</tr>'::text)) || '</table>'::text), (('<table>'::text || string_agg((((('<tr><td colspan="13">'::text || (emps2_1.short_name)::text) || '</td><td colspan="3">'::text) || (emps2_1.work_main_emp)::text) || '</td>'::text), '</tr>'::text)) || '</table>'::text), (('<table>'::text || string_agg((((('<tr><td colspan="13">'::text || (emps2_1.short_name)::text) || '</td><td colspan="3">'::text) || (emps2_1.middle_emp)::text) || '</td>'::text), '</tr>'::text)) || '</table>'::text), (('<table>'::text || string_agg((((('<tr><td colspan="13">'::text || (emps2_1.short_name)::text) || '</td><td colspan="3">'::text) || (emps2_1.work_middle_emp)::text) || '</td>'::text), '</tr>'::text)) || '</table>'::text)
95. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.12 rows=70 width=64) (never executed)

  • Output: emps2_1.short_name, emps2_1.main_emp, emps2_1.work_main_emp, emps2_1.middle_emp, emps2_1.work_middle_emp
96. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_14 (cost=0.00..0.02 rows=1 width=0) (never executed)

  • Output: prm_14.cln, prm_14.d1, prm_14.d2, prm_14.is_detail, prm_14.dep, prm_14.d1_last, prm_14.d2_last
  • Filter: ((prm_14.cln IS NULL) AND (prm_14.is_detail = 'true'::text))
97. 0.000 0.000 ↓ 0.0 0

CTE Scan on emps2 emps2_1 (cost=0.00..1.40 rows=70 width=64) (never executed)

  • Output: emps2_1.short_name, emps2_1.main_emp, emps2_1.main_emp_txt, emps2_1.last_main_emp, emps2_1.work_main_emp, emps2_1.work_main_emp_txt, emps2_1.last_work_main_emp, emps2_1.middle_emp, emps2_1.middle_emp_txt, emps2_1.last_middle_emp, emps2_1.work_middle_emp, emps2_1.work_middle_emp_txt, emps2_1.last_work_middle_emp
98.          

CTE close_cases0

99. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=2,981.10..2,981.64 rows=2 width=72) (never executed)

  • Output: (COALESCE(sec_org_3.short_name, ((concat_ws(' '::text, CASE WHEN ($60 IS NULL) THEN (('<i>'::text || (COALESCE(pd_4.dep_name, 'Hе указано'::character varying))::text) || '</i>,'::text) ELSE NULL::text END, i_1.surname, i_1.name, i_1.patr_name, (', ТН: '::text || NULLIF((pe_1.number)::text, ''::text)))))::character varying)), NULLIF(count(DISTINCT CASE WHEN (mc_2.care_regimen_id = 4) THEN mc_2.patient_id ELSE NULL::integer END), 0), NULLIF(count(DISTINCT CASE WHEN (mc_2.care_regimen_id = 2) THEN mc_2.patient_id ELSE NULL::integer END), 0)
  • Group Key: (COALESCE(sec_org_3.short_name, ((concat_ws(' '::text, CASE WHEN ($60 IS NULL) THEN (('<i>'::text || (COALESCE(pd_4.dep_name, 'Hе указано'::character varying))::text) || '</i>,'::text) ELSE NULL::text END, i_1.surname, i_1.name, i_1.patr_name, (', ТН: '::text || NULLIF((pe_1.number)::text, ''::text)))))::character varying))
100.          

Initplan (for GroupAggregate)

101. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_15 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_15.cln
102. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_16 (cost=0.00..0.02 rows=1 width=32) (never executed)

  • Output: prm_16.is_detail
103. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_17 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_17.dep
104. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_18 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_18.cln
105. 0.000 0.000 ↓ 0.0 0

CTE Scan on sec_org sec_org_2 (cost=0.00..0.04 rows=2 width=4) (never executed)

  • Output: sec_org_2.org_id
106. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_19 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_19.d1
107. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_20 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_20.d2
108. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_21 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_21.dep
109. 0.000 0.000 ↓ 0.0 0

Sort (cost=2,980.92..2,981.01 rows=34 width=72) (never executed)

  • Output: (COALESCE(sec_org_3.short_name, ((concat_ws(' '::text, CASE WHEN ($60 IS NULL) THEN (('<i>'::text || (COALESCE(pd_4.dep_name, 'Hе указано'::character varying))::text) || '</i>,'::text) ELSE NULL::text END, i_1.surname, i_1.name, i_1.patr_name, (', ТН: '::text || NULLIF((pe_1.number)::text, ''::text)))))::character varying)), mc_2.care_regimen_id, mc_2.patient_id
  • Sort Key: (COALESCE(sec_org_3.short_name, ((concat_ws(' '::text, CASE WHEN ($60 IS NULL) THEN (('<i>'::text || (COALESCE(pd_4.dep_name, 'Hе указано'::character varying))::text) || '</i>,'::text) ELSE NULL::text END, i_1.surname, i_1.name, i_1.patr_name, (', ТН: '::text || NULLIF((pe_1.number)::text, ''::text)))))::character varying))
110. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=2.74..2,980.06 rows=34 width=72) (never executed)

  • Output: COALESCE(sec_org_3.short_name, ((concat_ws(' '::text, CASE WHEN ($60 IS NULL) THEN (('<i>'::text || (COALESCE(pd_4.dep_name, 'Hе указано'::character varying))::text) || '</i>,'::text) ELSE NULL::text END, i_1.surname, i_1.name, i_1.patr_name, (', ТН: '::text || NULLIF((pe_1.number)::text, ''::text)))))::character varying), mc_2.care_regimen_id, mc_2.patient_id
  • Filter: CASE WHEN ($57 IS NOT NULL) THEN (alternatives: SubPlan 39 or hashed SubPlan 40) ELSE true END
111. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.42..650.67 rows=68 width=44) (never executed)

  • Output: mc_2.care_regimen_id, mc_2.patient_id, sec_org_3.short_name, srg_1.responsible_id
112. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.42..649.29 rows=34 width=12) (never executed)

  • Output: mc_2.care_regimen_id, mc_2.patient_id, srg_1.responsible_id
113. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.86..404.00 rows=34 width=12) (never executed)

  • Output: mc_2.care_regimen_id, mc_2.patient_id, ms_1.res_group_id
  • Join Filter: (($50 IS NOT NULL) AND (($51 = 'true'::text) OR ($52 IS NOT NULL)))
114. 0.000 0.000 ↓ 0.0 0

Index Scan using mc_case_close_date_care_regimen_24_idx on public.mc_case mc_2 (cost=0.42..116.10 rows=34 width=12) (never executed)

  • Output: mc_2.care_regimen_id, mc_2.patient_id, mc_2.closing_step_id
  • Index Cond: ((mc_2.close_date >= $55) AND (mc_2.close_date <= $56) AND (mc_2.clinic_id = COALESCE($53, $54)))
  • Filter: (upper((COALESCE(mc_2.note, ''::character varying))::text) !~ 'НАБИВАЛКА'::text)
115. 0.000 0.000 ↓ 0.0 0

Index Scan using mc_step_pk on public.mc_step ms_1 (cost=0.44..8.46 rows=1 width=8) (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.planned_date_closing_case, ms_1.gestational_age, ms_1.onko_stage, ms_1.hsp_bed_profile_id, ms_1.is_first_step
  • Index Cond: (ms_1.id = mc_2.closing_step_id)
116. 0.000 0.000 ↓ 0.0 0

Index Scan using sr_res_group_pk on public.sr_res_group srg_1 (cost=0.56..7.20 rows=1 width=8) (never executed)

  • Output: srg_1.id, srg_1.bdate, srg_1.edate, srg_1.is_system, srg_1.name, srg_1.department_id, srg_1.org_id, srg_1.responsible_id, srg_1.is_available_in_electronic_queue, srg_1.label_id, srg_1.ignore_regclinic_check, srg_1.note, srg_1.blocked, srg_1.block_comment, srg_1.template_res_group_id, srg_1.aud_who, srg_1.aud_when, srg_1.aud_source, srg_1.aud_who_create, srg_1.aud_when_create, srg_1.aud_source_create, srg_1.emergency, srg_1.ignore_districts, srg_1.service_template_id
  • Index Cond: (srg_1.id = ms_1.res_group_id)
117. 0.000 0.000 ↓ 0.0 0

CTE Scan on sec_org sec_org_3 (cost=0.00..0.04 rows=2 width=32) (never executed)

  • Output: sec_org_3.org_id, sec_org_3.short_name
118. 0.000 0.000 ↓ 0.0 0

Limit (cost=1.31..30.12 rows=1 width=572) (never executed)

  • Output: (concat_ws(' '::text, CASE WHEN ($60 IS NULL) THEN (('<i>'::text || (COALESCE(pd_4.dep_name, 'Hе указано'::character varying))::text) || '</i>,'::text) ELSE NULL::text END, i_1.surname, i_1.name, i_1.patr_name, (', ТН: '::text || NULLIF((pe_1.number)::text, ''::text)))), pp_2.department_id
119.          

Initplan (for Limit)

120. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_22 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_22.dep
121. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.29..30.10 rows=1 width=572) (never executed)

  • Output: concat_ws(' '::text, CASE WHEN ($60 IS NULL) THEN (('<i>'::text || (COALESCE(pd_4.dep_name, 'Hе указано'::character varying))::text) || '</i>,'::text) ELSE NULL::text END, i_1.surname, i_1.name, i_1.patr_name, (', ТН: '::text || NULLIF((pe_1.number)::text, ''::text))), pp_2.department_id
  • Join Filter: (pp_2.department_id = pd_4.id)
122. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.29..29.99 rows=1 width=56) (never executed)

  • Output: pe_1.number, i_1.surname, i_1.name, i_1.patr_name, pp_2.department_id
123. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.01..21.68 rows=1 width=56) (never executed)

  • Output: pep_2.position_id, pe_1.number, i_1.surname, i_1.name, i_1.patr_name
124. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.58..16.62 rows=1 width=11) (never executed)

  • Output: pep_2.position_id, pe_1.number, pe_1.individual_id
125. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_employee_position_pk on public.pim_employee_position pep_2 (cost=0.29..8.31 rows=1 width=8) (never executed)

  • 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.pref_prescription, pep_2.extra_payment, pep_2.target_training, pep_2.leaving_reason_id
  • Index Cond: (pep_2.id = srg_1.responsible_id)
126. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_employee_pk on public.pim_employee pe_1 (cost=0.29..8.31 rows=1 width=11) (never executed)

  • Output: pe_1.id, pe_1.note, pe_1.number, pe_1.photo, pe_1.callup_subject_id, pe_1.individual_id, pe_1.organization_id, pe_1.is_dismissed, pe_1.employment_dt, pe_1.dismissal_dt, pe_1.aud_who, pe_1.aud_when, pe_1.aud_source, pe_1.aud_who_create, pe_1.aud_when_create, pe_1.aud_source_create, pe_1.accreditation_id
  • Index Cond: (pe_1.id = pep_2.employee_id)
127. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_individual_pk on public.pim_individual i_1 (cost=0.43..5.04 rows=1 width=53) (never executed)

  • Output: i_1.id, i_1.birth_dt, i_1.death_dt, i_1.has_citizenship, i_1.name, i_1.patr_name, i_1.surname, i_1.gender_id, i_1.nationality_id, i_1.list_identity_doc, i_1.list_oms_doc, i_1.list_job_org, i_1.list_reg_name, i_1.list_snils, i_1.list_uid, i_1.aud_who, i_1.aud_when, i_1.aud_source, i_1.aud_who_create, i_1.aud_when_create, i_1.aud_source_create, i_1.birth_place, i_1.age_year, i_1.age_month, i_1.age_day, i_1.list_main_contact, i_1.is_only_birth_year
  • Index Cond: (i_1.id = pe_1.individual_id)
128. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_position_pk on public.pim_position pp_2 (cost=0.29..8.30 rows=1 width=8) (never executed)

  • Output: pp_2.id, pp_2.code, pp_2.start_date, pp_2.name, pp_2.end_date, pp_2.department_id, pp_2.organization_id, pp_2.role_id, pp_2.speciality_id, pp_2.rate, pp_2.aud_who, pp_2.aud_when, pp_2.aud_source, pp_2.aud_who_create, pp_2.aud_when_create, pp_2.aud_source_create, pp_2.pref_prescription, pp_2.payment_fond, pp_2.staff_event1, pp_2.staff_event2, pp_2.note, pp_2.fact_employment, pp_2.external_rate, pp_2.billing_code
  • Index Cond: (pp_2.id = pep_2.position_id)
129. 0.000 0.000 ↓ 0.0 0

CTE Scan on pd pd_4 (cost=0.00..0.06 rows=3 width=520) (never executed)

  • Output: pd_4.id, pd_4.dep_name
130.          

SubPlan (for Nested Loop Left Join)

131. 0.000 0.000 ↓ 0.0 0

CTE Scan on all_deps all_deps_3 (cost=0.00..16.45 rows=4 width=0) (never executed)

  • Filter: (all_deps_3.id = pp_2.department_id)
132. 0.000 0.000 ↓ 0.0 0

CTE Scan on all_deps all_deps_4 (cost=0.00..14.62 rows=731 width=4) (never executed)

  • Output: all_deps_4.id
133.          

CTE close_cases1

134. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=0.06..0.07 rows=1 width=16) (never executed)

  • Output: NULLIF(sum(close_cases0.daily_stat), '0'::numeric), NULLIF(sum(close_cases0.night_stat), '0'::numeric)
135. 0.000 0.000 ↓ 0.0 0

CTE Scan on close_cases0 (cost=0.00..0.04 rows=2 width=16) (never executed)

  • Output: close_cases0.doctor, close_cases0.daily_stat, close_cases0.night_stat
136.          

CTE close_cases2

137. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=0.12..0.14 rows=1 width=40) (never executed)

  • Output: (('<table>'::text || string_agg((((('<tr><td colspan="13">'::text || (COALESCE(close_cases0_1.doctor, 'Hе указан'::character varying))::text) || '</td><td colspan="3">'::text) || (close_cases0_1.daily_stat)::text) || '</td>'::text), '</tr>'::text ORDER BY close_cases0_1.doctor)) || '</table>'::text)
138. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..0.08 rows=2 width=40) (never executed)

  • Output: close_cases0_1.doctor, close_cases0_1.daily_stat
139. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_23 (cost=0.00..0.02 rows=1 width=0) (never executed)

  • Output: prm_23.cln, prm_23.d1, prm_23.d2, prm_23.is_detail, prm_23.dep, prm_23.d1_last, prm_23.d2_last
  • Filter: (prm_23.is_detail = 'true'::text)
140. 0.000 0.000 ↓ 0.0 0

CTE Scan on close_cases0 close_cases0_1 (cost=0.00..0.04 rows=2 width=40) (never executed)

  • Output: close_cases0_1.doctor, close_cases0_1.daily_stat, close_cases0_1.night_stat
  • Filter: (close_cases0_1.daily_stat IS NOT NULL)
141.          

CTE close_cases3

142. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=0.12..0.14 rows=1 width=40) (never executed)

  • Output: (('<table>'::text || string_agg((((('<tr><td colspan="13">'::text || (COALESCE(close_cases0_2.doctor, 'Hе указан'::character varying))::text) || '</td><td colspan="3">'::text) || (close_cases0_2.night_stat)::text) || '</td>'::text), '</tr>'::text ORDER BY close_cases0_2.doctor)) || '</table>'::text)
143. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..0.08 rows=2 width=40) (never executed)

  • Output: close_cases0_2.doctor, close_cases0_2.night_stat
144. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_24 (cost=0.00..0.02 rows=1 width=0) (never executed)

  • Output: prm_24.cln, prm_24.d1, prm_24.d2, prm_24.is_detail, prm_24.dep, prm_24.d1_last, prm_24.d2_last
  • Filter: (prm_24.is_detail = 'true'::text)
145. 0.000 0.000 ↓ 0.0 0

CTE Scan on close_cases0 close_cases0_2 (cost=0.00..0.04 rows=2 width=40) (never executed)

  • Output: close_cases0_2.doctor, close_cases0_2.daily_stat, close_cases0_2.night_stat
  • Filter: (close_cases0_2.night_stat IS NOT NULL)
146.          

Initplan (for Nested Loop Left Join)

147. 0.006 0.006 ↑ 1.0 1 1

CTE Scan on prm prm_25 (cost=0.00..0.02 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)

  • Output: prm_25.cln
148. 0.001 0.001 ↑ 1.0 1 1

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

  • Output: prm_26.is_detail
149. 0.001 0.001 ↑ 1.0 1 1

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

  • Output: prm_27.dep
150. 0.032 0.032 ↑ 1.0 1 1

CTE Scan on prm prm_28 (cost=0.00..0.02 rows=1 width=4) (actual time=0.031..0.032 rows=1 loops=1)

  • Output: prm_28.cln
151. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: sec_org_4.org_id
152. 0.000 0.000 ↓ 0.0 0

CTE Scan on sec_org sec_org_4 (cost=0.00..0.04 rows=2 width=4) (never executed)

  • Output: sec_org_4.org_id
153. 0.001 0.001 ↑ 1.0 1 1

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

  • Output: prm_29.d1_last
154. 0.000 0.000 ↑ 1.0 1 1

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

  • Output: prm_30.d2_last
155. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on prm prm_31 (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1)

  • Output: prm_31.dep
156. 1.343 2,139.630 ↑ 1.5 2,182 1

Nested Loop (cost=42.40..32,135.17 rows=3,286 width=4) (actual time=34.102..2,139.630 rows=2,182 loops=1)

  • Output: srg.responsible_id
  • Buffers: shared hit=5219 read=1070
  • I/O Timings: read=2078.802
157. 2.294 2,137.196 ↑ 1.5 1,091 1

Nested Loop Left Join (cost=42.40..32,093.00 rows=1,643 width=4) (actual time=34.056..2,137.196 rows=1,091 loops=1)

  • Output: srg.responsible_id
  • Buffers: shared hit=5218 read=1070
  • I/O Timings: read=2078.802
158. 3.391 2,133.811 ↑ 1.5 1,091 1

Nested Loop Left Join (cost=41.84..20,239.34 rows=1,643 width=4) (actual time=34.049..2,133.811 rows=1,091 loops=1)

  • Output: ms.res_group_id
  • Join Filter: (($71 IS NOT NULL) AND (($72 = 'true'::text) OR ($73 IS NOT NULL)))
  • Rows Removed by Join Filter: 1091
  • Buffers: shared hit=5218 read=1070
  • I/O Timings: read=2078.802
159. 30.536 31.336 ↑ 1.5 1,091 1

Bitmap Heap Scan on public.mc_case mc (cost=41.40..6,391.24 rows=1,643 width=4) (actual time=1.385..31.336 rows=1,091 loops=1)

  • Output: mc.closing_step_id
  • Recheck Cond: ((mc.clinic_id = COALESCE($74, $75)) AND (mc.close_date >= $76) AND (mc.close_date <= $77))
  • Filter: (upper((COALESCE(mc.note, ''::character varying))::text) !~ 'НАБИВАЛКА'::text)
  • Rows Removed by Filter: 1050
  • Heap Blocks: exact=1910
  • Buffers: shared hit=1918
160. 0.800 0.800 ↓ 1.3 2,141 1

Bitmap Index Scan on mc_case_clinic_close_date_idx (cost=0.00..40.99 rows=1,644 width=0) (actual time=0.800..0.800 rows=2,141 loops=1)

  • Index Cond: ((mc.clinic_id = COALESCE($74, $75)) AND (mc.close_date >= $76) AND (mc.close_date <= $77))
  • Buffers: shared hit=8
161. 2,099.084 2,099.084 ↑ 1.0 1 1,091

Index Scan using mc_step_pk on public.mc_step ms (cost=0.44..8.42 rows=1 width=8) (actual time=1.922..1.924 rows=1 loops=1,091)

  • 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.planned_date_closing_case, ms.gestational_age, ms.onko_stage, ms.hsp_bed_profile_id, ms.is_first_step
  • Index Cond: (ms.id = mc.closing_step_id)
  • Buffers: shared hit=3300 read=1070
  • I/O Timings: read=2078.802
162. 1.091 1.091 ↓ 0.0 0 1,091

Index Scan using sr_res_group_pk on public.sr_res_group srg (cost=0.56..7.20 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1,091)

  • Output: srg.id, srg.bdate, srg.edate, srg.is_system, srg.name, srg.department_id, srg.org_id, srg.responsible_id, srg.is_available_in_electronic_queue, srg.label_id, srg.ignore_regclinic_check, srg.note, srg.blocked, srg.block_comment, srg.template_res_group_id, srg.aud_who, srg.aud_when, srg.aud_source, srg.aud_who_create, srg.aud_when_create, srg.aud_source_create, srg.emergency, srg.ignore_districts, srg.service_template_id
  • Index Cond: (srg.id = ms.res_group_id)
163. 1.051 1.091 ↑ 1.0 2 1,091

Materialize (cost=0.00..1.10 rows=2 width=0) (actual time=0.001..0.001 rows=2 loops=1,091)

  • Buffers: shared hit=1
164. 0.040 0.040 ↑ 1.0 2 1

Seq Scan on public.mc_care_regimen mcr (cost=0.00..1.09 rows=2 width=0) (actual time=0.035..0.040 rows=2 loops=1)

  • Filter: (mcr.e_code = ANY ('{4,2}'::integer[]))
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1
165. 0.000 2.182 ↓ 0.0 0 2,182

Limit (cost=0.58..16.62 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=2,182)

  • Output: pp.department_id
166. 2.182 2.182 ↓ 0.0 0 2,182

Nested Loop (cost=0.58..16.62 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=2,182)

  • Output: pp.department_id
167. 0.000 0.000 ↓ 0.0 0 2,182

Index Scan using pim_employee_position_pk on public.pim_employee_position pep (cost=0.29..8.31 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=2,182)

  • 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.pref_prescription, pep.extra_payment, pep.target_training, pep.leaving_reason_id
  • Index Cond: (pep.id = srg.responsible_id)
168. 0.000 0.000 ↓ 0.0 0

Index Scan using pim_position_pk on public.pim_position pp (cost=0.29..8.30 rows=1 width=8) (never executed)

  • Output: pp.id, pp.code, pp.start_date, pp.name, pp.end_date, pp.department_id, pp.organization_id, pp.role_id, pp.speciality_id, pp.rate, pp.aud_who, pp.aud_when, pp.aud_source, pp.aud_who_create, pp.aud_when_create, pp.aud_source_create, pp.pref_prescription, pp.payment_fond, pp.staff_event1, pp.staff_event2, pp.note, pp.fact_employment, pp.external_rate, pp.billing_code
  • Index Cond: (pp.id = pep.position_id)
169.          

SubPlan (for Nested Loop Left Join)

170. 0.000 0.000 ↓ 0.0 0

CTE Scan on all_deps all_deps_5 (cost=0.00..16.45 rows=4 width=0) (never executed)

  • Filter: (all_deps_5.id = pp.department_id)
171. 0.000 0.000 ↓ 0.0 0

CTE Scan on all_deps all_deps_6 (cost=0.00..14.62 rows=731 width=4) (never executed)

  • Output: all_deps_6.id
Planning time : 13.447 ms
Execution time : 2,148.014 ms