explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MuLw

Settings
# exclusive inclusive rows x rows loops node
1. 5.976 21,661.954 ↓ 4,511.0 4,511 1

Sort (cost=19,577.87..19,577.88 rows=1 width=1,906) (actual time=21,661.100..21,661.954 rows=4,511 loops=1)

  • Output: patients.patient_id, patients.fio, patients.birth_dt, patients.adr, patients.tel, patients.polis_number, patients.polis_org, patients.reg_date, patients.reg_from, patients.school_name, patients.study_group, patients.unreg_date, patients.unreg_to, patients.dt, patients.rn
  • Sort Key: patients.rn
  • Sort Method: quicksort Memory: 2485kB
  • Buffers: shared hit=365405
2.          

CTE prm

3. 0.018 0.018 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=20) (actual time=0.018..0.018 rows=1 loops=1)

  • Output: 3311022, NULL::integer, to_date('01.01.2019'::text, 'dd.mm.yyyy'::text), to_date('01.01.2019'::text, 'dd.mm.yyyy'::text), NULL::integer
4.          

CTE districts

5. 0.001 0.149 ↓ 7.0 7 1

Nested Loop (cost=0.44..7.09 rows=1 width=4) (actual time=0.120..0.149 rows=7 loops=1)

  • Output: cd.id
  • Buffers: shared hit=44
6. 0.010 0.116 ↓ 16.0 16 1

Nested Loop (cost=0.31..6.91 rows=1 width=8) (actual time=0.087..0.116 rows=16 loops=1)

  • Output: cd.id, cd.type_id
  • Join Filter: ((cd.id = prm.distr) OR (prm.distr IS NULL))
  • Buffers: shared hit=12
7. 0.037 0.088 ↑ 2.0 2 1

Hash Join (cost=0.03..3.42 rows=4 width=8) (actual time=0.077..0.088 rows=2 loops=1)

  • Output: prm.distr, cs.id
  • Hash Cond: (cs.clinic_id = prm.cln)
  • Buffers: shared hit=2
8. 0.025 0.025 ↑ 1.0 98 1

Seq Scan on public.md_clinic_separation cs (cost=0.00..2.98 rows=98 width=8) (actual time=0.009..0.025 rows=98 loops=1)

  • Output: cs.id, cs.name, cs.clinic_id, cs.profile_id, cs.reg_type_id, cs.from_dt, cs.to_dt, cs.aud_who, cs.aud_when, cs.aud_source, cs.aud_who_create, cs.aud_when_create, cs.aud_source_create, cs.department_id
  • Buffers: shared hit=2
9. 0.005 0.026 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=1)

  • Output: prm.cln, prm.distr
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.021 0.021 ↑ 1.0 1 1

CTE Scan on prm (cost=0.00..0.02 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=1)

  • Output: prm.cln, prm.distr
11. 0.018 0.018 ↓ 1.6 8 2

Index Scan using idx_md_clinic_district on public.md_clinic_district cd (cost=0.27..0.81 rows=5 width=12) (actual time=0.004..0.009 rows=8 loops=2)

  • Output: cd.id, cd.separation_id, cd.type_id
  • Index Cond: (cd.separation_id = cs.id)
  • Buffers: shared hit=10
12. 0.032 0.032 ↓ 0.0 0 16

Index Scan using md_clinic_district_type_pk on public.md_clinic_district_type dt (cost=0.13..0.17 rows=1 width=4) (actual time=0.001..0.002 rows=0 loops=16)

  • Output: dt.id, dt.code, dt.name, dt.aud_who, dt.aud_when, dt.aud_source, dt.aud_who_create, dt.aud_when_create, dt.aud_source_create
  • Index Cond: (dt.id = cd.type_id)
  • Filter: ((dt.code)::text = '2'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=32
13.          

CTE registrations

14. 3.370 107.313 ↓ 4,511.0 4,511 1

Nested Loop (cost=955.26..19,300.08 rows=1 width=60) (actual time=6.195..107.313 rows=4,511 loops=1)

  • Output: r.id, r.patient_id, r.reg_dt, r.unreg_dt, i.fio, i.birth_dt, i.age
  • Buffers: shared hit=28078
15. 5.890 55.103 ↓ 4,884.0 4,884 1

Hash Join (cost=954.84..19,291.59 rows=1 width=20) (actual time=5.888..55.103 rows=4,884 loops=1)

  • Output: prm_1.d2, r.id, r.patient_id, r.reg_dt, r.unreg_dt
  • Hash Cond: (COALESCE(r.district_by_addr_id, r.district_id) = districts.id)
  • Buffers: shared hit=8542
16. 6.125 49.055 ↓ 481.8 22,164 1

Nested Loop (cost=954.80..19,291.38 rows=46 width=28) (actual time=5.701..49.055 rows=22,164 loops=1)

  • Output: prm_1.d2, r.id, r.patient_id, r.reg_dt, r.unreg_dt, r.district_by_addr_id, r.district_id
  • Buffers: shared hit=8498
17. 0.001 0.001 ↑ 1.0 1 1

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

  • Output: prm_1.cln, prm_1.distr, prm_1.d1, prm_1.d2, prm_1.dep
18. 38.880 42.929 ↓ 481.8 22,164 1

Bitmap Heap Scan on public.pci_patient_reg r (cost=954.80..19,290.90 rows=46 width=36) (actual time=5.698..42.929 rows=22,164 loops=1)

  • Output: r.id, r.reg_dt, r.request_dt, r.request_uid, r.unreg_dt, r.clinic_id, r.district_id, r.district_by_addr_id, r.address_id, r.patient_id, r.state_id, r.type_id, r.unreg_cause_id, r.is_assertion, r.automatic_district_dt, r.number_attach, r.aud_who, r.aud_when, r.aud_source, r.aud_who_create, r.aud_when_create, r.aud_source_create, r.department_id, r.pat_selection, r.note
  • Recheck Cond: (r.clinic_id = prm_1.cln)
  • Filter: ((r.type_id = 1) AND (COALESCE(r.reg_dt, r.request_dt, prm_1.d1) <= prm_1.d2) AND (COALESCE(r.unreg_dt, prm_1.d2) >= prm_1.d1) AND ((r.department_id = prm_1.dep) OR (prm_1.dep IS NULL)))
  • Rows Removed by Filter: 4377
  • Heap Blocks: exact=8414
  • Buffers: shared hit=8498
19. 4.049 4.049 ↑ 1.6 26,595 1

Bitmap Index Scan on pci_patient_reg_clinic_id_idx (cost=0.00..954.79 rows=42,982 width=0) (actual time=4.049..4.049 rows=26,595 loops=1)

  • Index Cond: (r.clinic_id = prm_1.cln)
  • Buffers: shared hit=84
20. 0.005 0.158 ↓ 7.0 7 1

Hash (cost=0.02..0.02 rows=1 width=4) (actual time=0.158..0.158 rows=7 loops=1)

  • Output: districts.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=44
21. 0.153 0.153 ↓ 7.0 7 1

CTE Scan on districts (cost=0.00..0.02 rows=1 width=4) (actual time=0.122..0.153 rows=7 loops=1)

  • Output: districts.id
  • Buffers: shared hit=44
22. 4.884 48.840 ↑ 1.0 1 4,884

Subquery Scan on i (cost=0.42..8.47 rows=1 width=44) (actual time=0.009..0.010 rows=1 loops=4,884)

  • Output: i.fio, i.birth_dt, i.age
  • Filter: (i.age < '18'::double precision)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=19536
23. 4.884 43.956 ↑ 1.0 1 4,884

Limit (cost=0.42..8.46 rows=1 width=44) (actual time=0.009..0.009 rows=1 loops=4,884)

  • Output: (concat(i_1.surname, (' '::text || (i_1.name)::text), (' '::text || (i_1.patr_name)::text))), i_1.birth_dt, (date_part('year'::text, age((prm_1.d2)::timestamp with time zone, (i_1.birth_dt)::timestamp with time zone)))
  • Buffers: shared hit=19536
24. 39.072 39.072 ↑ 1.0 1 4,884

Index Scan using pim_individual_pk on public.pim_individual i_1 (cost=0.42..8.46 rows=1 width=44) (actual time=0.008..0.008 rows=1 loops=4,884)

  • Output: concat(i_1.surname, (' '::text || (i_1.name)::text), (' '::text || (i_1.patr_name)::text)), i_1.birth_dt, date_part('year'::text, age((prm_1.d2)::timestamp with time zone, (i_1.birth_dt)::timestamp with time zone))
  • Index Cond: (i_1.id = r.patient_id)
  • Buffers: shared hit=19536
25.          

CTE cases

26. 0.002 370.873 ↓ 0.0 0 1

GroupAggregate (cost=134.93..134.95 rows=1 width=36) (actual time=370.873..370.873 rows=0 loops=1)

  • Output: r_1.patient_id, string_agg(DISTINCT to_char((c.close_date)::timestamp with time zone, 'dd.mm.yyyy'::text), ';'::text)
  • Group Key: r_1.patient_id
  • Buffers: shared hit=148787
27. 0.005 370.871 ↓ 0.0 0 1

Sort (cost=134.93..134.93 rows=1 width=8) (actual time=370.871..370.871 rows=0 loops=1)

  • Output: r_1.patient_id, c.close_date
  • Sort Key: r_1.patient_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=148787
28. 0.001 370.866 ↓ 0.0 0 1

Nested Loop (cost=0.57..134.92 rows=1 width=8) (actual time=370.865..370.866 rows=0 loops=1)

  • Output: r_1.patient_id, c.close_date
  • Buffers: shared hit=148787
29. 0.017 370.865 ↓ 0.0 0 1

Nested Loop (cost=0.43..134.75 rows=1 width=12) (actual time=370.864..370.865 rows=0 loops=1)

  • Output: r_1.patient_id, c.close_date, c.init_goal_id
  • Join Filter: (c.care_regimen_id = mcr.id)
  • Rows Removed by Join Filter: 6
  • Buffers: shared hit=148787
30. 24.943 370.806 ↓ 3.0 3 1

Nested Loop (cost=0.43..133.63 rows=1 width=16) (actual time=108.328..370.806 rows=3 loops=1)

  • Output: r_1.patient_id, c.close_date, c.care_regimen_id, c.init_goal_id
  • Join Filter: ((c.close_date >= prm_2.d1) AND (c.close_date <= prm_2.d2) AND (c.clinic_id = prm_2.cln))
  • Rows Removed by Join Filter: 135617
  • Buffers: shared hit=148784
31. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on prm prm_2 (cost=0.00..0.02 rows=1 width=12) (actual time=0.001..0.002 rows=1 loops=1)

  • Output: prm_2.cln, prm_2.distr, prm_2.d1, prm_2.d2, prm_2.dep
32. 37.403 345.861 ↓ 4,238.1 135,620 1

Nested Loop (cost=0.43..133.05 rows=32 width=20) (actual time=0.032..345.861 rows=135,620 loops=1)

  • Output: r_1.patient_id, c.close_date, c.clinic_id, c.care_regimen_id, c.init_goal_id
  • Buffers: shared hit=148784
33. 1.710 1.710 ↓ 4,511.0 4,511 1

CTE Scan on registrations r_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..1.710 rows=4,511 loops=1)

  • Output: r_1.id, r_1.patient_id, r_1.reg_dt, r_1.unreg_dt, r_1.fio, r_1.birth_dt, r_1.age
34. 306.748 306.748 ↑ 1.1 30 4,511

Index Scan using idx_mc_case on public.mc_case c (cost=0.43..132.71 rows=32 width=20) (actual time=0.007..0.068 rows=30 loops=4,511)

  • Output: c.close_date, c.patient_id, c.clinic_id, c.care_regimen_id, c.init_goal_id
  • Index Cond: (c.patient_id = r_1.patient_id)
  • Buffers: shared hit=148784
35. 0.042 0.042 ↑ 1.0 2 3

Seq Scan on public.mc_care_regimen mcr (cost=0.00..1.09 rows=2 width=4) (actual time=0.013..0.014 rows=2 loops=3)

  • Output: mcr.id, mcr.bdate, mcr.is_bed_required, mcr.case_mode_id, mcr.med_cert_name, mcr.code, mcr.edate, mcr.name, mcr.e_code, mcr.from_dt, mcr.to_dt, mcr.aud_who, mcr.aud_when, mcr.aud_source, mcr.aud_who_create, mcr.aud_when_create, mcr.aud_source_create, mcr.is_not_admission, mcr.billing_code
  • Filter: ((mcr.e_code)::text = '1'::text)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=3
36. 0.000 0.000 ↓ 0.0 0

Index Scan using mc_case_init_goal_pk on public.mc_case_init_goal mcig (cost=0.14..0.16 rows=1 width=4) (never executed)

  • Output: mcig.id, mcig.code, mcig.name, mcig.visit_type_id, mcig.e_code, mcig.from_dt, mcig.to_dt, mcig.aud_who, mcig.aud_when, mcig.aud_source, mcig.aud_who_create, mcig.aud_when_create, mcig.aud_source_create, mcig.billing_code
  • Index Cond: (mcig.id = c.init_goal_id)
  • Filter: ((mcig.e_code)::text = '19'::text)
37.          

CTE addresses

38. 14.010 219.575 ↓ 4,315.0 4,315 1

WindowAgg (cost=9.96..9.98 rows=1 width=44) (actual time=203.800..219.575 rows=4,315 loops=1)

  • Output: r_2.patient_id, split_part((d.search_name)::text, 'Страна Российская Федерация, '::text, 2), row_number() OVER (?)
  • Buffers: shared hit=85095
39. 6.745 205.565 ↓ 4,315.0 4,315 1

Sort (cost=9.96..9.96 rows=1 width=205) (actual time=203.780..205.565 rows=4,315 loops=1)

  • Output: r_2.patient_id, d.search_name
  • Sort Key: r_2.patient_id
  • Sort Method: quicksort Memory: 1911kB
  • Buffers: shared hit=85095
40. 4.490 198.820 ↓ 4,315.0 4,315 1

Nested Loop (cost=1.28..9.95 rows=1 width=205) (actual time=0.035..198.820 rows=4,315 loops=1)

  • Output: r_2.patient_id, d.search_name
  • Buffers: shared hit=85095
41. 1.793 172.755 ↓ 4,315.0 4,315 1

Nested Loop (cost=0.85..9.06 rows=1 width=8) (actual time=0.027..172.755 rows=4,315 loops=1)

  • Output: r_2.patient_id, a.addr_id
  • Buffers: shared hit=67792
42. 3.077 135.829 ↓ 5,019.0 5,019 1

Nested Loop (cost=0.42..8.47 rows=1 width=12) (actual time=0.016..135.829 rows=5,019 loops=1)

  • Output: r_2.patient_id, a.id, a.addr_id
  • Buffers: shared hit=46529
43. 105.686 105.686 ↓ 4,511.0 4,511 1

CTE Scan on registrations r_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..105.686 rows=4,511 loops=1)

  • Output: r_2.id, r_2.patient_id, r_2.reg_dt, r_2.unreg_dt, r_2.fio, r_2.birth_dt, r_2.age
  • Buffers: shared hit=27923
44. 27.066 27.066 ↑ 1.0 1 4,511

Index Scan using pim_party_address_party_id_idx on public.pim_party_address a (cost=0.42..8.44 rows=1 width=12) (actual time=0.005..0.006 rows=1 loops=4,511)

  • Output: a.id, a.from_date, a.note, a.text_addr, a.to_date, a.is_valid, a.addr_id, a.party_id, a.register_type_id, a.aud_who, a.aud_when, a.aud_source, a.aud_who_create, a.aud_when_create, a.aud_source_create
  • Index Cond: (a.party_id = r_2.patient_id)
  • Filter: a.is_valid
  • Rows Removed by Filter: 0
  • Buffers: shared hit=18606
45. 35.133 35.133 ↑ 1.0 1 5,019

Index Scan using pim_party_addr_to_addr_type_party_address_id_idx on public.pim_party_addr_to_addr_type tt (cost=0.43..0.58 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=5,019)

  • Output: tt.id, tt.address_type_id, tt.party_address_id, tt.aud_who, tt.aud_when, tt.aud_source, tt.aud_who_create, tt.aud_when_create, tt.aud_source_create
  • Index Cond: (tt.party_address_id = a.id)
  • Filter: (tt.address_type_id = 3)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=21263
46. 21.575 21.575 ↑ 1.0 1 4,315

Index Scan using address_element_data_pkey on public.address_element_data d (cost=0.43..0.88 rows=1 width=205) (actual time=0.005..0.005 rows=1 loops=4,315)

  • Output: d.id, d.search_name, d.path, d.is_city, d.aud_who, d.aud_when, d.aud_source, d.aud_who_create, d.aud_when_create, d.aud_source_create
  • Index Cond: (d.id = a.addr_id)
  • Buffers: shared hit=17303
47.          

CTE phones

48. 4.832 37.338 ↓ 1,868.0 1,868 1

GroupAggregate (cost=12.82..12.85 rows=1 width=36) (actual time=32.018..37.338 rows=1,868 loops=1)

  • Output: r_3.patient_id, string_agg(DISTINCT (c_1.value)::text, '; '::text)
  • Group Key: r_3.patient_id
  • Buffers: shared hit=20138
49. 1.632 32.506 ↓ 2,197.0 2,197 1

Sort (cost=12.82..12.83 rows=1 width=17) (actual time=31.999..32.506 rows=2,197 loops=1)

  • Output: r_3.patient_id, c_1.value
  • Sort Key: r_3.patient_id
  • Sort Method: quicksort Memory: 261kB
  • Buffers: shared hit=20138
50. 2.469 30.874 ↓ 2,197.0 2,197 1

Nested Loop (cost=0.56..12.81 rows=1 width=17) (actual time=0.374..30.874 rows=2,197 loops=1)

  • Output: r_3.patient_id, c_1.value
  • Buffers: shared hit=20138
51. 4.494 23.985 ↓ 1,105.0 2,210 1

Nested Loop (cost=0.42..12.48 rows=2 width=21) (actual time=0.115..23.985 rows=2,210 loops=1)

  • Output: r_3.patient_id, c_1.value, c_1.type_id
  • Buffers: shared hit=15718
52. 1.447 1.447 ↓ 4,511.0 4,511 1

CTE Scan on registrations r_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..1.447 rows=4,511 loops=1)

  • Output: r_3.id, r_3.patient_id, r_3.reg_dt, r_3.unreg_dt, r_3.fio, r_3.birth_dt, r_3.age
53. 18.044 18.044 ↓ 0.0 0 4,511

Index Scan using pim_indiv_contact_indiv_id_idx on public.pim_indiv_contact c_1 (cost=0.42..12.44 rows=2 width=21) (actual time=0.004..0.004 rows=0 loops=4,511)

  • Output: c_1.id, c_1.is_main, c_1.note, c_1.value, c_1.indiv_id, c_1.type_id, c_1.aud_who, c_1.aud_when, c_1.aud_source, c_1.aud_who_create, c_1.aud_when_create, c_1.aud_source_create
  • Index Cond: (c_1.indiv_id = r_3.patient_id)
  • Buffers: shared hit=15718
54. 4.420 4.420 ↑ 1.0 1 2,210

Index Scan using pim_party_contact_type_pk on public.pim_party_contact_type t (cost=0.14..0.16 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,210)

  • Output: t.id, t.name, t.notice, t.category_id, t.party_type_id, t.code, t.e_code, t.from_dt, t.to_dt, t.aud_who, t.aud_when, t.aud_source, t.aud_who_create, t.aud_when_create, t.aud_source_create
  • Index Cond: (t.id = c_1.type_id)
  • Filter: (lower((t.name)::text) ~~ '%телефон%'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4420
55.          

CTE docs

56. 0.037 0.078 ↑ 1.0 4 1

Hash Join (cost=1.66..6.41 rows=4 width=9) (actual time=0.050..0.078 rows=4 loops=1)

  • Output: t_1.id, ((t_1.code)::text = 'MHI_UNIFORM'::text), t_1.priority
  • Hash Cond: (t_1.id = cat.type_id)
  • Buffers: shared hit=5
57. 0.022 0.022 ↑ 1.0 51 1

Seq Scan on public.pim_doc_type t_1 (cost=0.00..4.51 rows=51 width=19) (actual time=0.009..0.022 rows=51 loops=1)

  • Output: t_1.id, t_1.code, t_1.is_contract_permitted, t_1.from_dt, t_1.name, t_1.note, t_1.occurrence, t_1.priority, t_1.to_dt, t_1.party_type_id, t_1.code_type_id, t_1.e_code, t_1.aud_who, t_1.aud_when, t_1.aud_source, t_1.aud_who_create, t_1.aud_when_create, t_1.aud_source_create, t_1.is_series_hidden, t_1.is_number_hidden, t_1.series_format, t_1.series_message_pattern, t_1.number_format, t_1.number_message_pattern, t_1.enum_code, t_1.is_not_unique, t_1.scope_id, t_1.billing_code
  • Buffers: shared hit=4
58. 0.005 0.019 ↑ 1.0 4 1

Hash (cost=1.61..1.61 rows=4 width=4) (actual time=0.019..0.019 rows=4 loops=1)

  • Output: cat.type_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
59. 0.014 0.014 ↑ 1.0 4 1

Seq Scan on public.pim_doc_type_category cat (cost=0.00..1.61 rows=4 width=4) (actual time=0.009..0.014 rows=4 loops=1)

  • Output: cat.type_id
  • Filter: (cat.category_id = 2)
  • Rows Removed by Filter: 48
  • Buffers: shared hit=1
60.          

CTE polices

61. 7.201 144.153 ↓ 1,508.0 4,524 1

WindowAgg (cost=55.40..55.49 rows=3 width=80) (actual time=135.732..144.153 rows=4,524 loops=1)

  • Output: r_4.patient_id, CASE WHEN docs.is_enp THEN (c_2.code)::text ELSE concat(d_1.series, (' '::text || (d_1.number)::text)) END, concat(o.short_name, (' '::text || (d_1.issuer_text)::text)), row_number() OVER (?), docs.priority
  • Buffers: shared hit=64573
62. 6.619 136.952 ↓ 1,508.0 4,524 1

Sort (cost=55.40..55.41 rows=3 width=90) (actual time=135.707..136.952 rows=4,524 loops=1)

  • Output: r_4.patient_id, docs.priority, docs.is_enp, c_2.code, d_1.series, d_1.number, o.short_name, d_1.issuer_text
  • Sort Key: r_4.patient_id, docs.priority
  • Sort Method: quicksort Memory: 1366kB
  • Buffers: shared hit=64573
63. 5.312 130.333 ↓ 1,508.0 4,524 1

Nested Loop Left Join (cost=1.15..55.38 rows=3 width=90) (actual time=0.154..130.333 rows=4,524 loops=1)

  • Output: r_4.patient_id, docs.priority, docs.is_enp, c_2.code, d_1.series, d_1.number, o.short_name, d_1.issuer_text
  • Buffers: shared hit=64573
64. 4.301 115.973 ↓ 1,508.0 4,524 1

Nested Loop Left Join (cost=0.86..30.41 rows=3 width=44) (actual time=0.136..115.973 rows=4,524 loops=1)

  • Output: r_4.patient_id, d_1.series, d_1.number, d_1.issuer_text, d_1.issuer_id, docs.is_enp, docs.priority, c_2.code
  • Join Filter: docs.is_enp
  • Buffers: shared hit=51082
65. 8.416 89.052 ↓ 1,508.0 4,524 1

Nested Loop (cost=0.43..28.77 rows=3 width=33) (actual time=0.118..89.052 rows=4,524 loops=1)

  • Output: r_4.patient_id, d_1.series, d_1.number, d_1.issuer_text, d_1.issuer_id, d_1.code_id, docs.is_enp, docs.priority
  • Buffers: shared hit=33193
66. 6.512 66.847 ↓ 4,596.3 13,789 1

Nested Loop (cost=0.43..28.44 rows=3 width=32) (actual time=0.022..66.847 rows=13,789 loops=1)

  • Output: r_4.patient_id, d_1.series, d_1.number, d_1.issuer_text, d_1.type_id, d_1.issuer_id, d_1.code_id
  • Buffers: shared hit=33188
67. 1.692 1.692 ↓ 4,511.0 4,511 1

CTE Scan on registrations r_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..1.692 rows=4,511 loops=1)

  • Output: r_4.id, r_4.patient_id, r_4.reg_dt, r_4.unreg_dt, r_4.fio, r_4.birth_dt, r_4.age
68. 58.643 58.643 ↑ 1.0 3 4,511

Index Scan using pim_individual_doc_indiv_id_idx on public.pim_individual_doc d_1 (cost=0.43..28.39 rows=3 width=32) (actual time=0.007..0.013 rows=3 loops=4,511)

  • Output: d_1.id, d_1.type_id, d_1.series, d_1.number, d_1.sha256, d_1.code_id, d_1.indiv_id, d_1.is_active, d_1.expire_dt, d_1.issue_dt, d_1.issuer_text, d_1.note, d_1.birth_dt, d_1.contract_number, d_1.name, d_1.patr_name, d_1.surname, d_1.issuer_id, d_1.verific_dt, d_1.birth_place, d_1.check_dt, d_1.check_status_id, d_1.aud_who, d_1.aud_when, d_1.aud_source, d_1.aud_who_create, d_1.aud_when_create, d_1.aud_source_create
  • Index Cond: (d_1.indiv_id = r_4.patient_id)
  • Filter: d_1.is_active
  • Rows Removed by Filter: 2
  • Buffers: shared hit=33188
69. 0.000 13.789 ↓ 0.0 0 13,789

Limit (cost=0.00..0.09 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=13,789)

  • Output: docs.is_enp, docs.priority
  • Buffers: shared hit=5
70. 13.789 13.789 ↓ 0.0 0 13,789

CTE Scan on docs (cost=0.00..0.09 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=13,789)

  • Output: docs.is_enp, docs.priority
  • Filter: (docs.id = d_1.type_id)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=5
71. 22.620 22.620 ↑ 1.0 1 4,524

Index Scan using pim_indiv_code_pk on public.pim_indiv_code c_2 (cost=0.43..0.53 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=4,524)

  • Output: c_2.id, c_2.code, c_2.issue_dt, c_2.type_id, c_2.indiv_id, c_2.sha256, c_2.aud_who, c_2.aud_when, c_2.aud_source, c_2.aud_who_create, c_2.aud_when_create, c_2.aud_source_create
  • Index Cond: (c_2.id = d_1.code_id)
  • Buffers: shared hit=17889
72. 0.000 9.048 ↑ 1.0 1 4,524

Limit (cost=0.29..8.30 rows=1 width=50) (actual time=0.002..0.002 rows=1 loops=4,524)

  • Output: o.short_name
  • Buffers: shared hit=13491
73. 9.048 9.048 ↑ 1.0 1 4,524

Index Scan using pim_organization_pk on public.pim_organization o (cost=0.29..8.30 rows=1 width=50) (actual time=0.002..0.002 rows=1 loops=4,524)

  • Output: o.short_name
  • Index Cond: (o.id = d_1.issuer_id)
  • Buffers: shared hit=13491
74.          

CTE job

75. 0.040 9.423 ↓ 25.0 25 1

WindowAgg (cost=9.18..9.20 rows=1 width=67) (actual time=9.390..9.423 rows=25 loops=1)

  • Output: r_5.patient_id, o_1.short_name, j.study_group, row_number() OVER (?), (CASE WHEN j.is_main_job THEN 1 ELSE 2 END)
  • Buffers: shared hit=9138
76. 0.030 9.383 ↓ 25.0 25 1

Sort (cost=9.18..9.18 rows=1 width=59) (actual time=9.379..9.383 rows=25 loops=1)

  • Output: r_5.patient_id, (CASE WHEN j.is_main_job THEN 1 ELSE 2 END), o_1.short_name, j.study_group
  • Sort Key: r_5.patient_id, (CASE WHEN j.is_main_job THEN 1 ELSE 2 END)
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=9138
77. 0.034 9.353 ↓ 25.0 25 1

Nested Loop (cost=0.58..9.17 rows=1 width=59) (actual time=0.469..9.353 rows=25 loops=1)

  • Output: r_5.patient_id, CASE WHEN j.is_main_job THEN 1 ELSE 2 END, o_1.short_name, j.study_group
  • Buffers: shared hit=9138
78. 0.051 9.219 ↓ 25.0 25 1

Nested Loop (cost=0.29..8.37 rows=1 width=10) (actual time=0.458..9.219 rows=25 loops=1)

  • Output: r_5.patient_id, j.study_group, j.is_main_job, j.organization_id
  • Join Filter: ((prm_3.d2 >= COALESCE(j.from_dt, prm_3.d2)) AND (prm_3.d2 <= COALESCE(j.to_dt, prm_3.d2)))
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=9063
79. 3.667 9.168 ↓ 26.0 26 1

Nested Loop (cost=0.29..8.34 rows=1 width=18) (actual time=0.453..9.168 rows=26 loops=1)

  • Output: r_5.patient_id, j.study_group, j.is_main_job, j.from_dt, j.to_dt, j.organization_id
  • Buffers: shared hit=9063
80. 0.990 0.990 ↓ 4,511.0 4,511 1

CTE Scan on registrations r_5 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.990 rows=4,511 loops=1)

  • Output: r_5.id, r_5.patient_id, r_5.reg_dt, r_5.unreg_dt, r_5.fio, r_5.birth_dt, r_5.age
81. 4.511 4.511 ↓ 0.0 0 4,511

Index Scan using pci_patient_job_patient_id_idx on public.pci_patient_job j (cost=0.29..8.31 rows=1 width=18) (actual time=0.001..0.001 rows=0 loops=4,511)

  • Output: j.id, j.from_dt, j.job_name, j.is_main_job, j.to_dt, j.organization_id, j.organization_type_id, j.patient_id, j.sphere_id, j.employee_id, j.profession_working_id, j.study_group, j.is_empl_fired, j.aud_who, j.aud_when, j.aud_source, j.aud_who_create, j.aud_when_create, j.aud_source_create, j.department_id, j.pci_rb_econom_act_type_id, j.okved_id
  • Index Cond: (j.patient_id = r_5.patient_id)
  • Filter: (j.organization_type_id <> 1)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=9063
82. 0.000 0.000 ↑ 1.0 1 26

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

  • Output: prm_3.cln, prm_3.distr, prm_3.d1, prm_3.d2, prm_3.dep
83. 0.100 0.100 ↑ 1.0 1 25

Index Scan using pim_organization_pk on public.pim_organization o_1 (cost=0.29..0.78 rows=1 width=54) (actual time=0.004..0.004 rows=1 loops=25)

  • Output: o_1.short_name, o_1.id
  • Index Cond: (o_1.id = j.organization_id)
  • Buffers: shared hit=75
84.          

CTE patients

85. 13.981 21,649.854 ↓ 4,511.0 4,511 1

WindowAgg (cost=41.73..41.77 rows=1 width=1,010) (actual time=21,633.758..21,649.854 rows=4,511 loops=1)

  • Output: r_6.patient_id, r_6.fio, to_char((r_6.birth_dt)::timestamp with time zone, 'dd.mm.yyyy'::text), a_1.adr, ph.tel, p.polis_number, p.polis_org, to_char((r_6.reg_dt)::timestamp with time zone, 'dd.mm.yyyy'::text), o_2.short_name, j_1.school_name, j_1.study_group, to_char((r_6.unreg_dt)::timestamp with time zone, 'dd.mm.yyyy'::text), o_3.short_name, cases.dt, row_number() OVER (?), r_6.birth_dt, r_6.fio
  • Buffers: shared hit=365405
86. 15.189 21,635.873 ↓ 4,511.0 4,511 1

Sort (cost=41.73..41.74 rows=1 width=882) (actual time=21,633.715..21,635.873 rows=4,511 loops=1)

  • Output: r_6.birth_dt, r_6.fio, r_6.patient_id, a_1.adr, ph.tel, p.polis_number, p.polis_org, r_6.reg_dt, o_2.short_name, j_1.school_name, j_1.study_group, r_6.unreg_dt, o_3.short_name, cases.dt
  • Sort Key: r_6.birth_dt DESC, r_6.fio
  • Sort Method: quicksort Memory: 2475kB
  • Buffers: shared hit=365405
87. 2.811 21,620.684 ↓ 4,511.0 4,511 1

Nested Loop Left Join (cost=25.36..41.72 rows=1 width=882) (actual time=791.904..21,620.684 rows=4,511 loops=1)

  • Output: r_6.birth_dt, r_6.fio, r_6.patient_id, a_1.adr, ph.tel, p.polis_number, p.polis_org, r_6.reg_dt, o_2.short_name, j_1.school_name, j_1.study_group, r_6.unreg_dt, o_3.short_name, cases.dt
  • Buffers: shared hit=365405
88. 5.441 21,613.362 ↓ 4,511.0 4,511 1

Nested Loop Left Join (cost=25.07..33.40 rows=1 width=836) (actual time=791.901..21,613.362 rows=4,511 loops=1)

  • Output: r_6.patient_id, r_6.fio, r_6.birth_dt, r_6.reg_dt, r_6.unreg_dt, a_1.adr, ph.tel, p.polis_number, p.polis_org, j_1.school_name, j_1.study_group, cases.dt, to_reg.clinic_id, o_2.short_name
  • Buffers: shared hit=365339
89. 5.322 21,603.410 ↓ 4,511.0 4,511 1

Nested Loop Left Join (cost=24.79..25.07 rows=1 width=790) (actual time=791.896..21,603.410 rows=4,511 loops=1)

  • Output: r_6.patient_id, r_6.fio, r_6.birth_dt, r_6.reg_dt, r_6.unreg_dt, a_1.adr, ph.tel, p.polis_number, p.polis_org, j_1.school_name, j_1.study_group, cases.dt, from_reg.clinic_id, to_reg.clinic_id
  • Buffers: shared hit=364676
90. 6.822 21,571.022 ↓ 4,511.0 4,511 1

Nested Loop Left Join (cost=12.39..12.65 rows=1 width=786) (actual time=791.865..21,571.022 rows=4,511 loops=1)

  • Output: r_6.patient_id, r_6.fio, r_6.birth_dt, r_6.reg_dt, r_6.unreg_dt, a_1.adr, ph.tel, p.polis_number, p.polis_org, j_1.school_name, j_1.study_group, cases.dt, from_reg.clinic_id
  • Buffers: shared hit=346281
91. 4.765 21,469.469 ↓ 4,511.0 4,511 1

Nested Loop Left Join (cost=0.00..0.24 rows=1 width=782) (actual time=791.808..21,469.469 rows=4,511 loops=1)

  • Output: r_6.patient_id, r_6.fio, r_6.birth_dt, r_6.reg_dt, r_6.unreg_dt, a_1.adr, ph.tel, p.polis_number, p.polis_org, j_1.school_name, j_1.study_group, cases.dt
  • Join Filter: (cases.patient_id = r_6.patient_id)
  • Buffers: shared hit=327886
92. 22.137 21,094.802 ↓ 4,511.0 4,511 1

Nested Loop Left Join (cost=0.00..0.20 rows=1 width=750) (actual time=420.932..21,094.802 rows=4,511 loops=1)

  • Output: r_6.patient_id, r_6.fio, r_6.birth_dt, r_6.reg_dt, r_6.unreg_dt, a_1.adr, ph.tel, p.polis_number, p.polis_org, j_1.school_name, j_1.study_group
  • Join Filter: (r_6.patient_id = j_1.patient_id)
  • Rows Removed by Join Filter: 112750
  • Buffers: shared hit=179099
93. 3,224.908 21,023.044 ↓ 4,511.0 4,511 1

Nested Loop Left Join (cost=0.00..0.17 rows=1 width=176) (actual time=411.478..21,023.044 rows=4,511 loops=1)

  • Output: r_6.patient_id, r_6.fio, r_6.birth_dt, r_6.reg_dt, r_6.unreg_dt, a_1.adr, ph.tel, p.polis_number, p.polis_org
  • Join Filter: (r_6.patient_id = p.patient_id)
  • Rows Removed by Join Filter: 20245390
  • Buffers: shared hit=169961
94. 1,524.274 11,793.995 ↓ 4,511.0 4,511 1

Nested Loop Left Join (cost=0.00..0.09 rows=1 width=112) (actual time=265.327..11,793.995 rows=4,511 loops=1)

  • Output: r_6.patient_id, r_6.fio, r_6.birth_dt, r_6.reg_dt, r_6.unreg_dt, a_1.adr, ph.tel
  • Join Filter: (r_6.patient_id = ph.patient_id)
  • Rows Removed by Join Filter: 8424680
  • Buffers: shared hit=105388
95. 3,129.145 8,672.827 ↓ 4,511.0 4,511 1

Nested Loop Left Join (cost=0.00..0.05 rows=1 width=80) (actual time=226.824..8,672.827 rows=4,511 loops=1)

  • Output: r_6.patient_id, r_6.fio, r_6.birth_dt, r_6.reg_dt, r_6.unreg_dt, a_1.adr
  • Join Filter: (r_6.patient_id = a_1.patient_id)
  • Rows Removed by Join Filter: 19460650
  • Buffers: shared hit=85250
96. 8.685 8.685 ↓ 4,511.0 4,511 1

CTE Scan on registrations r_6 (cost=0.00..0.02 rows=1 width=48) (actual time=6.202..8.685 rows=4,511 loops=1)

  • Output: r_6.id, r_6.patient_id, r_6.reg_dt, r_6.unreg_dt, r_6.fio, r_6.birth_dt, r_6.age
  • Buffers: shared hit=155
97. 5,534.997 5,534.997 ↓ 4,315.0 4,315 4,511

CTE Scan on addresses a_1 (cost=0.00..0.02 rows=1 width=36) (actual time=0.046..1.227 rows=4,315 loops=4,511)

  • Output: a_1.patient_id, a_1.adr, a_1.rn
  • Filter: (a_1.rn = 1)
  • Buffers: shared hit=85095
98. 1,596.894 1,596.894 ↓ 1,868.0 1,868 4,511

CTE Scan on phones ph (cost=0.00..0.02 rows=1 width=36) (actual time=0.007..0.354 rows=1,868 loops=4,511)

  • Output: ph.patient_id, ph.tel
  • Buffers: shared hit=20138
99. 6,004.141 6,004.141 ↓ 4,489.0 4,489 4,511

CTE Scan on polices p (cost=0.00..0.07 rows=1 width=68) (actual time=0.031..1.331 rows=4,489 loops=4,511)

  • Output: p.patient_id, p.polis_number, p.polis_org, p.rn
  • Filter: (p.rn = 1)
  • Rows Removed by Filter: 35
  • Buffers: shared hit=64573
100. 49.621 49.621 ↓ 25.0 25 4,511

CTE Scan on job j_1 (cost=0.00..0.02 rows=1 width=578) (actual time=0.003..0.011 rows=25 loops=4,511)

  • Output: j_1.patient_id, j_1.school_name, j_1.study_group, j_1.rn
  • Filter: (j_1.rn = 1)
  • Buffers: shared hit=9138
101. 369.902 369.902 ↓ 0.0 0 4,511

CTE Scan on cases (cost=0.00..0.02 rows=1 width=36) (actual time=0.082..0.082 rows=0 loops=4,511)

  • Output: cases.patient_id, cases.dt
  • Buffers: shared hit=148787
102. 4.511 94.731 ↓ 0.0 0 4,511

Limit (cost=12.39..12.40 rows=1 width=8) (actual time=0.021..0.021 rows=0 loops=4,511)

  • Output: from_reg.clinic_id, from_reg.unreg_dt
  • Buffers: shared hit=18395
103. 18.044 90.220 ↓ 0.0 0 4,511

Sort (cost=12.39..12.40 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=4,511)

  • Output: from_reg.clinic_id, from_reg.unreg_dt
  • Sort Key: from_reg.unreg_dt DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=18395
104. 0.000 72.176 ↓ 0.0 0 4,511

Nested Loop (cost=0.43..12.38 rows=1 width=8) (actual time=0.016..0.016 rows=0 loops=4,511)

  • Output: from_reg.clinic_id, from_reg.unreg_dt
  • Join Filter: (from_reg.clinic_id <> prm_4.cln)
  • Rows Removed by Join Filter: 0
  • Buffers: shared hit=18395
105. 4.511 4.511 ↑ 1.0 1 4,511

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

  • Output: prm_4.cln, prm_4.distr, prm_4.d1, prm_4.d2, prm_4.dep
106. 67.665 67.665 ↓ 0.0 0 4,511

Index Scan using pci_patient_reg_patient_id_idx on public.pci_patient_reg from_reg (cost=0.43..12.35 rows=1 width=8) (actual time=0.015..0.015 rows=0 loops=4,511)

  • Output: from_reg.id, from_reg.reg_dt, from_reg.request_dt, from_reg.request_uid, from_reg.unreg_dt, from_reg.clinic_id, from_reg.district_id, from_reg.district_by_addr_id, from_reg.address_id, from_reg.patient_id, from_reg.state_id, from_reg.type_id, from_reg.unreg_cause_id, from_reg.is_assertion, from_reg.automatic_district_dt, from_reg.number_attach, from_reg.aud_who, from_reg.aud_when, from_reg.aud_source, from_reg.aud_who_create, from_reg.aud_when_create, from_reg.aud_source_create, from_reg.department_id, from_reg.pat_selection, from_reg.note
  • Index Cond: (from_reg.patient_id = r_6.patient_id)
  • Filter: (from_reg.unreg_dt <= r_6.reg_dt)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=18395
107. 4.511 27.066 ↓ 0.0 0 4,511

Limit (cost=12.39..12.40 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=4,511)

  • Output: to_reg.clinic_id, to_reg.reg_dt
  • Buffers: shared hit=18395
108. 4.511 22.555 ↓ 0.0 0 4,511

Sort (cost=12.39..12.40 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=4,511)

  • Output: to_reg.clinic_id, to_reg.reg_dt
  • Sort Key: to_reg.reg_dt
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=18395
109. 4.511 18.044 ↓ 0.0 0 4,511

Nested Loop (cost=0.43..12.38 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=4,511)

  • Output: to_reg.clinic_id, to_reg.reg_dt
  • Join Filter: (to_reg.clinic_id <> prm_5.cln)
  • Buffers: shared hit=18395
110. 0.000 0.000 ↑ 1.0 1 4,511

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

  • Output: prm_5.cln, prm_5.distr, prm_5.d1, prm_5.d2, prm_5.dep
111. 13.533 13.533 ↓ 0.0 0 4,511

Index Scan using pci_patient_reg_patient_id_idx on public.pci_patient_reg to_reg (cost=0.43..12.35 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=4,511)

  • Output: to_reg.id, to_reg.reg_dt, to_reg.request_dt, to_reg.request_uid, to_reg.unreg_dt, to_reg.clinic_id, to_reg.district_id, to_reg.district_by_addr_id, to_reg.address_id, to_reg.patient_id, to_reg.state_id, to_reg.type_id, to_reg.unreg_cause_id, to_reg.is_assertion, to_reg.automatic_district_dt, to_reg.number_attach, to_reg.aud_who, to_reg.aud_when, to_reg.aud_source, to_reg.aud_who_create, to_reg.aud_when_create, to_reg.aud_source_create, to_reg.department_id, to_reg.pat_selection, to_reg.note
  • Index Cond: (to_reg.patient_id = r_6.patient_id)
  • Filter: (to_reg.reg_dt >= r_6.unreg_dt)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=18395
112. 0.000 4.511 ↓ 0.0 0 4,511

Limit (cost=0.29..8.30 rows=1 width=50) (actual time=0.001..0.001 rows=0 loops=4,511)

  • Output: o_2.short_name
  • Buffers: shared hit=663
113. 4.511 4.511 ↓ 0.0 0 4,511

Index Scan using pim_organization_pk on public.pim_organization o_2 (cost=0.29..8.30 rows=1 width=50) (actual time=0.001..0.001 rows=0 loops=4,511)

  • Output: o_2.short_name
  • Index Cond: (o_2.id = from_reg.clinic_id)
  • Buffers: shared hit=663
114. 4.511 4.511 ↓ 0.0 0 4,511

Limit (cost=0.29..8.30 rows=1 width=50) (actual time=0.001..0.001 rows=0 loops=4,511)

  • Output: o_3.short_name
  • Buffers: shared hit=66
115. 0.000 0.000 ↓ 0.0 0 4,511

Index Scan using pim_organization_pk on public.pim_organization o_3 (cost=0.29..8.30 rows=1 width=50) (actual time=0.000..0.000 rows=0 loops=4,511)

  • Output: o_3.short_name
  • Index Cond: (o_3.id = to_reg.clinic_id)
  • Buffers: shared hit=66
116. 21,655.978 21,655.978 ↓ 4,511.0 4,511 1

CTE Scan on patients (cost=0.00..0.02 rows=1 width=1,906) (actual time=21,633.763..21,655.978 rows=4,511 loops=1)

  • Output: patients.patient_id, patients.fio, patients.birth_dt, patients.adr, patients.tel, patients.polis_number, patients.polis_org, patients.reg_date, patients.reg_from, patients.school_name, patients.study_group, patients.unreg_date, patients.unreg_to, patients.dt, patients.rn
  • Buffers: shared hit=365405
Planning time : 8.607 ms
Execution time : 21,665.765 ms