explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LbEK

Settings
# exclusive inclusive rows x rows loops node
1. 0.356 284,571.032 ↓ 2.0 2 1

GroupAggregate (cost=71,741,551.10..71,741,551.14 rows=1 width=597) (actual time=284,570.878..284,571.032 rows=2 loops=1)

  • Output: array_agg(t.id ORDER BY t.close_date DESC, t.sid DESC), t.spec, ("substring"((t.code)::text, 1, 3)), t.patient_id, t.exeption_flag, t.goal_id_arr
  • Group Key: t.spec, ("substring"((t.code)::text, 1, 3)), t.patient_id, t.exeption_flag, t.goal_id_arr
  • Filter: (count(t.id) > 1)
  • Rows Removed by Filter: 42
  • Buffers: shared hit=348364 read=1860406 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=119972.698
2.          

CTE prm

3. 0.245 4.284 ↑ 1.0 1 1

Index Scan using fin_bill_main_pkey on public.fin_bill_main (cost=0.54..8.56 rows=1 width=12) (actual time=4.281..4.284 rows=1 loops=1)

  • Output: fin_bill_main.from_date, fin_bill_main.to_date, fin_bill_main.clinic_id
  • Index Cond: (fin_bill_main.id = $0)
  • Buffers: shared hit=3 read=3
  • I/O Timings: read=3.906
4.          

Initplan (for Index Scan)

5. 4.039 4.039 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=0) (actual time=4.039..4.039 rows=1 loops=1)

  • Output: fin_bill__get_main_bill(3128)
  • Buffers: shared hit=1 read=2
  • I/O Timings: read=3.683
6.          

CTE x

7. 15.690 10,320.438 ↓ 1.1 26,369 1

Nested Loop (cost=950.44..125,623.45 rows=23,365 width=16) (actual time=120.438..10,320.438 rows=26,369 loops=1)

  • Output: c.id, c.closing_step_id, c.patient_id, c.close_date
  • Buffers: shared hit=6 read=22725
  • I/O Timings: read=9462.371
8. 4.289 4.289 ↑ 1.0 1 1

CTE Scan on prm (cost=0.00..0.02 rows=1 width=12) (actual time=4.285..4.289 rows=1 loops=1)

  • Output: prm.bd, prm.ed, prm.cln
  • Buffers: shared hit=3 read=3
  • I/O Timings: read=3.906
9. 10,204.572 10,300.459 ↓ 1.1 26,369 1

Bitmap Heap Scan on public.mc_case c (cost=950.44..125,389.78 rows=23,365 width=20) (actual time=116.139..10,300.459 rows=26,369 loops=1)

  • Output: c.id, c.closing_step_id, c.patient_id, c.close_date, c.clinic_id
  • Recheck Cond: ((c.clinic_id = prm.cln) AND (c.close_date >= prm.bd) AND (c.close_date <= prm.ed) AND (c.closing_step_id IS NOT NULL))
  • Filter: ((c.care_regimen_id <> ALL ('{3,5}'::integer[])) AND (c.init_goal_id = 1) AND (c.case_type_id = 1))
  • Rows Removed by Filter: 13903
  • Heap Blocks: exact=22612
  • Buffers: shared hit=3 read=22722
  • I/O Timings: read=9458.465
10. 95.887 95.887 ↓ 1.0 40,272 1

Bitmap Index Scan on mc_case_clinic_id_close_date_idx (cost=0.00..944.60 rows=38,413 width=0) (actual time=95.887..95.887 rows=40,272 loops=1)

  • Index Cond: ((c.clinic_id = prm.cln) AND (c.close_date >= prm.bd) AND (c.close_date <= prm.ed))
  • Buffers: shared read=113
  • I/O Timings: read=80.716
11.          

CTE y

12. 1,037.078 263,406.909 ↓ 11.3 26,368 1

GroupAggregate (cost=64,913,959.05..71,615,583.89 rows=2,337 width=158) (actual time=262,332.270..263,406.909 rows=26,368 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, array_agg(m.id), array_agg(v.code), array_agg(CASE WHEN ((v.name)::text ~ 'аппаратное лечение|сурдолога-отор'::text) THEN '1'::text ELSE '0'::text END), array_agg(COALESCE(v.type_id, 2)), array_cat(array_agg(s.res_group_id), array_agg(ms.res_group_id)), array_agg(ms.id)
  • Group Key: x.id, x.closing_step_id, x.patient_id, x.close_date
  • Buffers: shared hit=207254 read=1837179 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=99612.334
13. 70.355 262,369.831 ↑ 2,322.1 88,798 1

Sort (cost=64,913,959.05..65,429,463.26 rows=206,201,684 width=158) (actual time=262,332.106..262,369.831 rows=88,798 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.id, v.code, v.name, v.type_id, s.res_group_id, ms.res_group_id, ms.id
  • Sort Key: x.id, x.closing_step_id, x.patient_id, x.close_date
  • Sort Method: quicksort Memory: 19982kB
  • Buffers: shared hit=207254 read=1837179 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=99612.334
14. 2,645.296 262,299.476 ↑ 2,322.1 88,798 1

Merge Join (cost=16,993,707.73..20,227,847.72 rows=206,201,684 width=158) (actual time=247,979.042..262,299.476 rows=88,798 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.id, v.code, v.name, v.type_id, s.res_group_id, ms.res_group_id, ms.id
  • Merge Cond: (m.case_id = ms.case_id)
  • Buffers: shared hit=207254 read=1837179 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=99612.334
15. 85.272 169,054.002 ↑ 376.7 55,782 1

Sort (cost=11,898,500.34..11,951,032.39 rows=21,012,821 width=154) (actual time=169,036.961..169,054.002 rows=55,782 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.id, m.case_id, s.res_group_id, v.code, v.name, v.type_id
  • Sort Key: x.id
  • Sort Method: quicksort Memory: 12348kB
  • Buffers: shared hit=126225 read=1348947, temp read=248286 written=248256
  • I/O Timings: read=84848.364
16. 56.908 168,968.730 ↑ 376.7 55,782 1

Hash Join (cost=2,476,891.16..7,690,952.52 rows=21,012,821 width=154) (actual time=29,581.250..168,968.730 rows=55,782 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.id, m.case_id, s.res_group_id, v.code, v.name, v.type_id
  • Hash Cond: (s.service_id = v.id)
  • Buffers: shared hit=126225 read=1348947, temp read=248286 written=248256
  • I/O Timings: read=84848.364
17. 53,918.303 168,562.418 ↑ 377.6 55,782 1

Hash Join (cost=2,454,054.93..7,379,005.71 rows=21,061,966 width=32) (actual time=29,230.579..168,562.418 rows=55,782 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.id, m.case_id, s.res_group_id, s.service_id
  • Hash Cond: (s.id = m.id)
  • Buffers: shared hit=113167 read=1348947, temp read=248286 written=248256
  • I/O Timings: read=84848.364
18. 90,243.833 90,243.833 ↓ 1.0 67,746,803 1

Seq Scan on public.sr_srv_rendered s (cost=0.00..1,981,645.61 rows=67,745,861 width=12) (actual time=0.003..90,243.833 rows=67,746,803 loops=1)

  • Output: s.res_group_id, s.id, s.service_id
  • Buffers: shared hit=304 read=1303883
  • I/O Timings: read=61910.820
19. 68.702 24,400.282 ↑ 377.6 55,782 1

Hash (cost=2,067,370.36..2,067,370.36 rows=21,061,966 width=24) (actual time=24,400.282..24,400.282 rows=55,782 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.id, m.case_id
  • Buckets: 2097152 Batches: 16 Memory Usage: 16573kB
  • Buffers: shared hit=112863 read=45064, temp written=272
  • I/O Timings: read=22937.544
20. 73.191 24,331.580 ↑ 377.6 55,782 1

Nested Loop (cost=0.57..2,067,370.36 rows=21,061,966 width=24) (actual time=136.880..24,331.580 rows=55,782 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.id, m.case_id
  • Buffers: shared hit=112863 read=45064
  • I/O Timings: read=22937.544
21. 10,361.926 10,361.926 ↓ 1.1 26,369 1

CTE Scan on x (cost=0.00..467.30 rows=23,365 width=16) (actual time=120.444..10,361.926 rows=26,369 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date
  • Buffers: shared hit=6 read=22725
  • I/O Timings: read=9462.371
22. 13,896.463 13,896.463 ↑ 450.5 2 26,369

Index Scan using md_srv_rendered_case_id_idx on public.md_srv_rendered m (cost=0.57..79.45 rows=901 width=8) (actual time=0.480..0.527 rows=2 loops=26,369)

  • Output: m.id, m.is_urgent, m.is_use_cryogenic, m.is_use_endoscopic, m.is_use_laser, m.anesthesia_type_id, m.step_id, m.complication_type_id, m.diagnosis_id, m.case_id, m.referral_id, m.result_category_id, m.patient_prescription_id, m.health_group_id, m.vmp_type_id, m.vmp_method_id, m.anatomic_zone_id, m.aud_who, m.aud_when, m.aud_source, m.aud_who_create, m.aud_when_create, m.aud_source_create, m.prescription_id, m.by_standard, m.complicated_value, m.vmp_profile_id
  • Index Cond: (m.case_id = x.id)
  • Buffers: shared hit=112857 read=22339
  • I/O Timings: read=13475.173
23. 176.469 349.404 ↑ 1.0 434,588 1

Hash (cost=17,403.88..17,403.88 rows=434,588 width=130) (actual time=349.404..349.404 rows=434,588 loops=1)

  • Output: v.code, v.name, v.type_id, v.id
  • Buckets: 524288 Batches: 1 Memory Usage: 74703kB
  • Buffers: shared hit=13058
24. 172.935 172.935 ↑ 1.0 434,588 1

Seq Scan on public.sr_service v (cost=0.00..17,403.88 rows=434,588 width=130) (actual time=0.012..172.935 rows=434,588 loops=1)

  • Output: v.code, v.name, v.type_id, v.id
  • Buffers: shared hit=13058
25. 2,410.203 90,600.178 ↑ 1.3 23,319,629 1

Materialize (cost=5,095,191.37..5,244,795.85 rows=29,920,896 width=12) (actual time=77,839.000..90,600.178 rows=23,319,629 loops=1)

  • Output: ms.res_group_id, ms.id, ms.case_id
  • Buffers: shared hit=81029 read=488232 dirtied=8, temp read=64133 written=80411
  • I/O Timings: read=14763.970
26. 62,830.890 88,189.975 ↑ 1.3 23,267,646 1

Sort (cost=5,095,191.37..5,169,993.61 rows=29,920,896 width=12) (actual time=77,838.994..88,189.975 rows=23,267,646 loops=1)

  • Output: ms.res_group_id, ms.id, ms.case_id
  • Sort Key: ms.case_id
  • Sort Method: external merge Disk: 643272kB
  • Buffers: shared hit=81029 read=488232 dirtied=8, temp read=64133 written=80411
  • I/O Timings: read=14763.970
27. 25,359.085 25,359.085 ↓ 1.0 29,920,960 1

Seq Scan on public.mc_step ms (cost=0.00..868,469.96 rows=29,920,896 width=12) (actual time=0.073..25,359.085 rows=29,920,960 loops=1)

  • Output: ms.res_group_id, ms.id, ms.case_id
  • Buffers: shared hit=81029 read=488232 dirtied=8
  • I/O Timings: read=14763.970
28.          

CTE z

29. 1.034 281,195.241 ↓ 81.0 81 1

Nested Loop Anti Join (cost=1.31..92.36 rows=1 width=152) (actual time=263,612.585..281,195.241 rows=81 loops=1)

  • Output: y.id, s_1.id, y.patient_id, s_1.main_diagnosis_id, s_1.res_group_id, y.close_date, (('B01.001.003'::text = ANY ((array_remove(y.service_code_arr, NULL::character varying))::text[])) OR ('1'::text = ANY (y.service_name_arr))), y.s_res_group_id, y.sid_arr
  • Buffers: shared hit=346039 read=1859975 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=116629.024
30. 1.274 280,224.229 ↓ 82.0 82 1

Nested Loop Semi Join (cost=0.88..87.61 rows=1 width=152) (actual time=263,594.131..280,224.229 rows=82 loops=1)

  • Output: y.id, y.patient_id, y.close_date, y.service_code_arr, y.service_name_arr, y.s_res_group_id, y.sid_arr, s_1.id, s_1.main_diagnosis_id, s_1.res_group_id
  • Join Filter: (t_1.id = ANY (y.type_id_arr))
  • Rows Removed by Join Filter: 1518
  • Buffers: shared hit=345878 read=1859807 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=115661.951
31. 59.098 280,221.451 ↓ 94.0 94 1

Nested Loop Anti Join (cost=0.88..85.83 rows=1 width=184) (actual time=263,594.083..280,221.451 rows=94 loops=1)

  • Output: y.id, y.patient_id, y.close_date, y.service_code_arr, y.service_name_arr, y.s_res_group_id, y.sid_arr, y.type_id_arr, s_1.id, s_1.main_diagnosis_id, s_1.res_group_id
  • Buffers: shared hit=345784 read=1859807 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=115661.951
32. 32.132 264,591.137 ↓ 20,066.0 20,066 1

Nested Loop (cost=0.44..72.74 rows=1 width=216) (actual time=262,366.651..264,591.137 rows=20,066 loops=1)

  • Output: y.id, y.patient_id, y.close_date, y.service_code_arr, y.service_name_arr, y.s_res_group_id, y.sid_arr, y.srv_rendered_id_arr, y.type_id_arr, s_1.id, s_1.main_diagnosis_id, s_1.res_group_id
  • Buffers: shared hit=272592 read=1852681 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=100278.734
33. 263,650.770 263,650.770 ↓ 20,183.0 20,183 1

CTE Scan on y (cost=0.00..64.27 rows=1 width=208) (actual time=262,332.307..263,650.770 rows=20,183 loops=1)

  • Output: y.id, y.closing_step_id, y.patient_id, y.close_date, y.srv_rendered_id_arr, y.service_code_arr, y.service_name_arr, y.type_id_arr, y.s_res_group_id, y.sid_arr
  • Filter: ((NOT ('{B01.070.06,B01.070.04,B01.070.03,B01.069.008,B01.031.006,B01.008.01.02,B01.029.01.01,B01.015.03,B01.058.03,B01.023.03,B01.057.03,B01.053.03,B01.001.06,B01.028.03,B01.031.009,B01.031.011,B01.069.002,B01.031.010}'::character varying[] && y.service_code_arr)) AND (array_to_string(y.service_code_arr, ','::text) !~~ '%A%'::text))
  • Rows Removed by Filter: 6185
  • Buffers: shared hit=207254 read=1837179 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=99612.334
34. 908.235 908.235 ↑ 1.0 1 20,183

Index Scan using mc_step_pk on public.mc_step s_1 (cost=0.44..8.46 rows=1 width=16) (actual time=0.044..0.045 rows=1 loops=20,183)

  • Output: s_1.id, s_1.admission_date, s_1.admission_time, s_1.death_date, s_1.death_time, s_1.outcome_date, s_1.outcome_time, s_1.death_employee_id, s_1.main_diagnosis_id, s_1.case_id, s_1.outcome_id, s_1.outcome_clinic_id, s_1.outcome_regimen_id, s_1.regimen_id, s_1.res_group_id, s_1.result_id, s_1.reason_id, s_1.profile_id, s_1.mes_id, s_1.mes_quality_criterion_id, s_1.is_continue, s_1.is_continue_editable, s_1.standard_id, s_1.csg_id, s_1.vmp_type_id, s_1.vmp_method_id, s_1.deviation_reason_id, s_1.aud_who, s_1.aud_when, s_1.aud_source, s_1.aud_who_create, s_1.aud_when_create, s_1.aud_source_create, s_1._clinic_id, s_1._patient_id, s_1.planned_date_closing_case, s_1.gestational_age, s_1.onko_stage, s_1.hsp_bed_profile_id, s_1.is_first_step
  • Index Cond: (s_1.id = y.closing_step_id)
  • Filter: ((y.close_date = s_1.admission_date) AND (s_1.profile_id <> ALL ('{34,35,36,37,38,39}'::integer[])))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=65338 read=15502
  • I/O Timings: read=666.400
35. 15,571.216 15,571.216 ↑ 8.0 1 20,066

Index Scan using fin_bill_spec_item_service_id_idx on public.fin_bill_spec_item i (cost=0.44..56.34 rows=8 width=4) (actual time=0.776..0.776 rows=1 loops=20,066)

  • Output: i.id, i.price, i.tariff, i.bill_id, i.service_id, i.number, i.is_deleted, i.comment, i.doc_id, i.status_id, i.correctional_bill_id
  • Index Cond: (i.service_id = ANY (y.srv_rendered_id_arr))
  • Filter: (i.status_id = ANY ('{1,2,3}'::integer[]))
  • Buffers: shared hit=73192 read=7126
  • I/O Timings: read=15383.217
36. 1.504 1.504 ↑ 1.4 17 94

Seq Scan on public.sr_srv_type t_1 (cost=0.00..1.30 rows=23 width=4) (actual time=0.011..0.016 rows=17 loops=94)

  • Output: t_1.id, t_1.code, t_1.name, t_1.priority, 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.from_dt, t_1.to_dt, t_1.is_physical, t_1.is_surgery, t_1.app_code, t_1.is_procedure, t_1.is_require_evaluation, t_1.billing_code
  • Filter: ((COALESCE(t_1.code, ''::character varying))::text <> 'APPOINTMENT'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=94
37. 969.978 969.978 ↓ 0.0 0 82

Index Scan using plc_visit_pk on public.plc_visit p (cost=0.44..2.58 rows=1 width=4) (actual time=11.829..11.829 rows=0 loops=82)

  • Output: p.id, p.duration, p.goal_id, p.initiator_id, p.place_id, p.type_id, p.is_viewed, p.is_needed, p.is_sanitized, p.aud_who, p.aud_when, p.aud_source, p.aud_who_create, p.aud_when_create, p.aud_source_create, p.dental_formula_id, p.appointment_id, p.planned_date
  • Index Cond: (p.id = s_1.id)
  • Filter: (((p.goal_id = 1) AND (p.place_id = 4)) OR ((p.goal_id = 5) AND (p.place_id = 2)) OR ((p.goal_id = 5) AND (p.place_id = 4)))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=161 read=168
  • I/O Timings: read=967.073
38.          

CTE t

39. 0.292 284,564.295 ↓ 46.0 46 1

Nested Loop (cost=234.66..242.80 rows=1 width=158) (actual time=263,699.464..284,564.295 rows=46 loops=1)

  • Output: z.id, z.sid, z.patient_id, z.main_diagnosis_id, z.res_group_id, z.close_date, z.exeption_flag, z.s_res_group_id, z.sid_arr, (array_agg(DISTINCT btrim((ps.code)::text))), d.code, (array_agg(DISTINCT pv.goal_id ORDER BY pv.goal_id))
  • Buffers: shared hit=348354 read=1860406 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=119972.698
40. 0.544 282,884.468 ↓ 81.0 81 1

Nested Loop (cost=49.56..57.66 rows=1 width=126) (actual time=263,638.677..282,884.468 rows=81 loops=1)

  • Output: z.id, z.sid, z.patient_id, z.main_diagnosis_id, z.res_group_id, z.close_date, z.exeption_flag, z.s_res_group_id, z.sid_arr, d.code, (array_agg(DISTINCT pv.goal_id ORDER BY pv.goal_id))
  • Buffers: shared hit=346904 read=1860166 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=118307.159
41. 0.695 282,857.437 ↓ 81.0 81 1

Nested Loop (cost=0.85..8.93 rows=1 width=94) (actual time=263,638.629..282,857.437 rows=81 loops=1)

  • Output: z.id, z.sid, z.patient_id, z.main_diagnosis_id, z.res_group_id, z.close_date, z.exeption_flag, z.s_res_group_id, z.sid_arr, d.code
  • Buffers: shared hit=346499 read=1860163 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=118284.184
42. 0.549 282,800.771 ↓ 81.0 81 1

Nested Loop (cost=0.56..8.61 rows=1 width=93) (actual time=263,638.567..282,800.771 rows=81 loops=1)

  • Output: z.id, z.sid, z.patient_id, z.main_diagnosis_id, z.res_group_id, z.close_date, z.exeption_flag, z.s_res_group_id, z.sid_arr, md.diagnos_id
  • Buffers: shared hit=346265 read=1860154 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=118230.199
43. 281,195.693 281,195.693 ↓ 81.0 81 1

CTE Scan on z (cost=0.00..0.02 rows=1 width=89) (actual time=263,612.591..281,195.693 rows=81 loops=1)

  • Output: z.id, z.sid, z.patient_id, z.main_diagnosis_id, z.res_group_id, z.close_date, z.exeption_flag, z.s_res_group_id, z.sid_arr
  • Buffers: shared hit=346039 read=1859975 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=116629.024
44. 1,604.529 1,604.529 ↑ 1.0 1 81

Index Scan using mc_diagnosis_pk on public.mc_diagnosis md (cost=0.56..8.58 rows=1 width=8) (actual time=19.807..19.809 rows=1 loops=81)

  • Output: md.id, md.diagnos_id
  • Index Cond: (md.id = z.main_diagnosis_id)
  • Buffers: shared hit=226 read=179
  • I/O Timings: read=1601.175
45. 55.971 55.971 ↑ 1.0 1 81

Index Scan using md_diagnosis_pk on public.md_diagnosis d (cost=0.29..0.30 rows=1 width=9) (actual time=0.690..0.691 rows=1 loops=81)

  • Output: d.id, d.code, d.name, d.parent_id, d.is_injury, d.level, d.is_leaf, d.e_code, d.oms, d.stomat, d.aud_who, d.aud_when, d.aud_source, d.aud_who_create, d.aud_when_create, d.aud_source_create, d.from_dt, d.to_dt
  • Index Cond: (d.id = md.diagnos_id)
  • Buffers: shared hit=234 read=9
  • I/O Timings: read=53.985
46. 2.349 26.487 ↑ 1.0 1 81

Aggregate (cost=48.71..48.72 rows=1 width=4) (actual time=0.327..0.327 rows=1 loops=81)

  • Output: array_agg(DISTINCT pv.goal_id ORDER BY pv.goal_id)
  • Buffers: shared hit=405 read=3
  • I/O Timings: read=22.975
47. 24.138 24.138 ↑ 10.0 1 81

Index Scan using plc_visit_pk on public.plc_visit pv (cost=0.44..48.68 rows=10 width=4) (actual time=0.295..0.298 rows=1 loops=81)

  • Output: pv.id, pv.duration, pv.goal_id, pv.initiator_id, pv.place_id, pv.type_id, pv.is_viewed, pv.is_needed, pv.is_sanitized, pv.aud_who, pv.aud_when, pv.aud_source, pv.aud_who_create, pv.aud_when_create, pv.aud_source_create, pv.dental_formula_id, pv.appointment_id, pv.planned_date
  • Index Cond: (pv.id = ANY (z.sid_arr))
  • Buffers: shared hit=405 read=3
  • I/O Timings: read=22.975
48. 3.078 1,679.535 ↑ 1.0 1 81

Aggregate (cost=185.11..185.12 rows=1 width=7) (actual time=20.735..20.735 rows=1 loops=81)

  • Output: array_agg(DISTINCT btrim((ps.code)::text))
  • Filter: ('{040122,040110,040200}'::text[] && array_agg(DISTINCT btrim((ps.code)::text)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1450 read=240
  • I/O Timings: read=1665.539
49. 0.755 1,676.457 ↑ 3.0 2 81

Hash Join (cost=16.33..185.04 rows=6 width=7) (actual time=19.210..20.697 rows=2 loops=81)

  • Output: ps.code
  • Hash Cond: (pp.speciality_id = ps.id)
  • Buffers: shared hit=1450 read=240
  • I/O Timings: read=1665.539
50. 0.521 1,675.485 ↑ 5.0 2 81

Nested Loop (cost=1.15..169.76 rows=10 width=4) (actual time=19.200..20.685 rows=2 loops=81)

  • Output: pp.speciality_id
  • Buffers: shared hit=1439 read=240
  • I/O Timings: read=1665.539
51. 1.661 1,648.431 ↑ 5.0 2 81

Nested Loop (cost=0.86..134.40 rows=10 width=4) (actual time=18.874..20.351 rows=2 loops=81)

  • Output: pep.position_id
  • Buffers: shared hit=974 read=234
  • I/O Timings: read=1640.526
52. 1,546.290 1,546.290 ↑ 5.0 2 81

Index Scan using sr_res_group_pk on public.sr_res_group srg (cost=0.57..51.22 rows=10 width=4) (actual time=17.694..19.090 rows=2 loops=81)

  • 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 = ANY (z.s_res_group_id))
  • Buffers: shared hit=521 read=216
  • I/O Timings: read=1542.255
53. 100.480 100.480 ↑ 1.0 1 157

Index Scan using pim_employee_position_pk on public.pim_employee_position pep (cost=0.29..8.31 rows=1 width=8) (actual time=0.639..0.640 rows=1 loops=157)

  • 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)
  • Buffers: shared hit=453 read=18
  • I/O Timings: read=98.271
54. 26.533 26.533 ↑ 1.0 1 157

Index Scan using pim_position_pk on public.pim_position pp (cost=0.29..3.53 rows=1 width=8) (actual time=0.168..0.169 rows=1 loops=157)

  • 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)
  • Buffers: shared hit=465 read=6
  • I/O Timings: read=25.013
55. 0.064 0.217 ↑ 1.0 186 1

Hash (cost=12.86..12.86 rows=186 width=11) (actual time=0.217..0.217 rows=186 loops=1)

  • Output: ps.code, ps.id
  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
  • Buffers: shared hit=11
56. 0.153 0.153 ↑ 1.0 186 1

Seq Scan on public.pim_speciality ps (cost=0.00..12.86 rows=186 width=11) (actual time=0.015..0.153 rows=186 loops=1)

  • Output: ps.code, ps.id
  • Buffers: shared hit=11
57. 5.936 284,570.676 ↓ 46.0 46 1

Sort (cost=0.03..0.04 rows=1 width=597) (actual time=284,570.663..284,570.676 rows=46 loops=1)

  • Output: t.spec, ("substring"((t.code)::text, 1, 3)), t.patient_id, t.exeption_flag, t.goal_id_arr, t.id, t.close_date, t.sid
  • Sort Key: t.spec, ("substring"((t.code)::text, 1, 3)), t.patient_id, t.exeption_flag, t.goal_id_arr
  • Sort Method: quicksort Memory: 31kB
  • Buffers: shared hit=348354 read=1860406 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=119972.698
58. 284,564.740 284,564.740 ↓ 46.0 46 1

CTE Scan on t (cost=0.00..0.02 rows=1 width=597) (actual time=263,699.496..284,564.740 rows=46 loops=1)

  • Output: t.spec, "substring"((t.code)::text, 1, 3), t.patient_id, t.exeption_flag, t.goal_id_arr, t.id, t.close_date, t.sid
  • Buffers: shared hit=348354 read=1860406 dirtied=8, temp read=312419 written=328667
  • I/O Timings: read=119972.698
Planning time : 8.415 ms
Execution time : 284,836.344 ms