explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ITLz

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1,854,988.104 ↑ 1.0 6 1

Limit (cost=6,471,266,314.35..6,471,266,314.37 rows=6 width=1,236) (actual time=1,854,988.102..1,854,988.104 rows=6 loops=1)

  • Output: mr.id, mrrt.name, (referral_type_verbose(mr.id)), ms.case_id, ms.id, mr.order_number, (to_char(mr.referral_date, 'DD.MM.YYYY'::text)), mr.referral_date, (CASE WHEN (mr.status_id = 4) THEN true ELSE false END), mr.ref_organization_id, (COALESCE(ref_org.short_name, ref_org.full_name)), (COALESCE(ref_org.short_name, ref_org.full_name)), mr.ref_department_id, ref_dep.name, mr.ref_doctor_id, po.id, po.full_name, (COALESCE(po.short_name, po.full_name)), pd.id, pd.name, mr.recv_doctor_id, ((SubPlan 1)), md.id, ((((md.code)::text || ' '::text) || (md.name)::text)), (COALESCE(mr.is_urgent, false)), mr.is_operation_required, ((SubPlan 8)), mr.patient_id, mr.patient_id, mr.referral_type_id, (CASE WHEN (SubPlan 9) THEN true ELSE false END), (CASE WHEN (SubPlan 10) THEN true ELSE false END), ($16), ffst.id, ((SubPlan 13)), ((SubPlan 16)), ((SubPlan 18)), ((SubPlan 19)), mr.comment, ((SubPlan 21)), ((SubPlan 22)), ((SubPlan 23)), ((SubPlan 24)), ('356'), (NULL::unknown), ((alternatives: SubPlan 25 or hashed SubPlan 26)), (CASE WHEN (356 <> po.id) THEN CASE WHEN (((SubPlan 27) <> 1) OR ((SubPlan 28) <> 1)) THEN NULL::integer ELSE CASE WHEN ((SubPlan 30) = 1) THEN (SubPlan 32) ELSE NULL::integer END END ELSE CASE WHEN ((SubPlan 33) IS NOT NULL) THEN CASE WHEN ((SubPlan 34) IS NOT NULL) THEN NULL::integer ELSE (SubPlan 35) END ELSE CASE WHEN ((SubPlan 36) <> 1) THEN NULL::integer ELSE (SubPlan 37) END END END), (NULL::unknown), (NULL::unknown), (NULL::unknown), mr.receiving_speciality_id, (NULL::unknown), ((mr.ref_status_id IS NULL)), mr.goal_id
  • Buffers: shared hit=419,718,829 read=1,644,580 dirtied=30,441, temp read=118,953 written=118,947
  • I/O Timings: read=295,047.423
2.          

Initplan (for Limit)

3. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on public.pim_party_role ppr (cost=0.00..1.30 rows=1 width=4) (actual time=0.013..0.017 rows=1 loops=1)

  • Output: ppr.id
  • Filter: ((ppr.code)::text = 'CLINIC_ORGANIZATION'::text)
  • Rows Removed by Filter: 23
  • Buffers: shared hit=1
4. 15,728.481 1,854,988.103 ↑ 1,096,626.0 6 1

Sort (cost=6,471,266,313.05..6,471,282,762.44 rows=6,579,756 width=1,236) (actual time=1,854,988.101..1,854,988.103 rows=6 loops=1)

  • Output: mr.id, mrrt.name, (referral_type_verbose(mr.id)), ms.case_id, ms.id, mr.order_number, (to_char(mr.referral_date, 'DD.MM.YYYY'::text)), mr.referral_date, (CASE WHEN (mr.status_id = 4) THEN true ELSE false END), mr.ref_organization_id, (COALESCE(ref_org.short_name, ref_org.full_name)), (COALESCE(ref_org.short_name, ref_org.full_name)), mr.ref_department_id, ref_dep.name, mr.ref_doctor_id, po.id, po.full_name, (COALESCE(po.short_name, po.full_name)), pd.id, pd.name, mr.recv_doctor_id, ((SubPlan 1)), md.id, ((((md.code)::text || ' '::text) || (md.name)::text)), (COALESCE(mr.is_urgent, false)), mr.is_operation_required, ((SubPlan 8)), mr.patient_id, mr.patient_id, mr.referral_type_id, (CASE WHEN (SubPlan 9) THEN true ELSE false END), (CASE WHEN (SubPlan 10) THEN true ELSE false END), ($16), ffst.id, ((SubPlan 13)), ((SubPlan 16)), ((SubPlan 18)), ((SubPlan 19)), mr.comment, ((SubPlan 21)), ((SubPlan 22)), ((SubPlan 23)), ((SubPlan 24)), ('356'), (NULL::unknown), ((alternatives: SubPlan 25 or hashed SubPlan 26)), (CASE WHEN (356 <> po.id) THEN CASE WHEN (((SubPlan 27) <> 1) OR ((SubPlan 28) <> 1)) THEN NULL::integer ELSE CASE WHEN ((SubPlan 30) = 1) THEN (SubPlan 32) ELSE NULL::integer END END ELSE CASE WHEN ((SubPlan 33) IS NOT NULL) THEN CASE WHEN ((SubPlan 34) IS NOT NULL) THEN NULL::integer ELSE (SubPlan 35) END ELSE CASE WHEN ((SubPlan 36) <> 1) THEN NULL::integer ELSE (SubPlan 37) END END END), (NULL::unknown), (NULL::unknown), (NULL::unknown), mr.receiving_speciality_id, (NULL::unknown), ((mr.ref_status_id IS NULL)), mr.goal_id
  • Sort Key: mr.order_number DESC
  • Sort Method: top-N heapsort Memory: 29kB
  • Buffers: shared hit=419,718,829 read=1,644,580 dirtied=30,441, temp read=118,953 written=118,947
  • I/O Timings: read=295,047.423
5. 508,229.496 1,839,259.622 ↓ 1.0 6,596,602 1

Hash Left Join (cost=1,999,725.69..6,471,148,372.16 rows=6,579,756 width=1,236) (actual time=52,081.881..1,839,259.622 rows=6,596,602 loops=1)

  • Output: mr.id, mrrt.name, referral_type_verbose(mr.id), ms.case_id, ms.id, mr.order_number, to_char(mr.referral_date, 'DD.MM.YYYY'::text), mr.referral_date, CASE WHEN (mr.status_id = 4) THEN true ELSE false END, mr.ref_organization_id, COALESCE(ref_org.short_name, ref_org.full_name), COALESCE(ref_org.short_name, ref_org.full_name), mr.ref_department_id, ref_dep.name, mr.ref_doctor_id, po.id, po.full_name, COALESCE(po.short_name, po.full_name), pd.id, pd.name, mr.recv_doctor_id, (SubPlan 1), md.id, (((md.code)::text || ' '::text) || (md.name)::text), COALESCE(mr.is_urgent, false), mr.is_operation_required, (SubPlan 8), mr.patient_id, mr.patient_id, mr.referral_type_id, CASE WHEN (SubPlan 9) THEN true ELSE false END, CASE WHEN (SubPlan 10) THEN true ELSE false END, $16, ffst.id, (SubPlan 13), (SubPlan 16), (SubPlan 18), (SubPlan 19), mr.comment, (SubPlan 21), (SubPlan 22), (SubPlan 23), (SubPlan 24), '356', NULL::unknown, (alternatives: SubPlan 25 or hashed SubPlan 26), CASE WHEN (356 <> po.id) THEN CASE WHEN (((SubPlan 27) <> 1) OR ((SubPlan 28) <> 1)) THEN NULL::integer ELSE CASE WHEN ((SubPlan 30) = 1) THEN (SubPlan 32) ELSE NULL::integer END END ELSE CASE WHEN ((SubPlan 33) IS NOT NULL) THEN CASE WHEN ((SubPlan 34) IS NOT NULL) THEN NULL::integer ELSE (SubPlan 35) END ELSE CASE WHEN ((SubPlan 36) <> 1) THEN NULL::integer ELSE (SubPlan 37) END END END, NULL::unknown, NULL::unknown, NULL::unknown, mr.receiving_speciality_id, NULL::unknown, (mr.ref_status_id IS NULL), mr.goal_id
  • Hash Cond: (mr.funding_id = ffst.id)
  • Buffers: shared hit=419,718,826 read=1,644,580 dirtied=30,441, temp read=118,953 written=118,947
  • I/O Timings: read=295,047.423
6. 5,207.232 109,527.824 ↓ 1.0 6,596,602 1

Hash Left Join (cost=1,999,724.51..3,537,098.77 rows=6,579,756 width=1,236) (actual time=48,739.656..109,527.824 rows=6,596,602 loops=1)

  • Output: mr.id, mr.order_number, mr.referral_date, mr.status_id, mr.ref_organization_id, mr.ref_department_id, mr.ref_doctor_id, mr.recv_doctor_id, mr.is_urgent, mr.is_operation_required, mr.patient_id, mr.referral_type_id, mr.comment, mr.receiving_speciality_id, mr.ref_status_id, mr.goal_id, mr.funding_id, ms.case_id, ms.id, ref_org.short_name, ref_org.full_name, ref_dep.name, mrrt.name, po.id, po.full_name, po.short_name, pd.id, pd.name, md.id, md.code, md.name
  • Hash Cond: (mr.diagnosis_id = md.id)
  • Buffers: shared hit=124,823 read=872,769 dirtied=1, temp read=118,953 written=118,947
  • I/O Timings: read=28,218.717
7. 5,942.117 104,310.251 ↓ 1.0 6,596,602 1

Hash Left Join (cost=1,999,003.28..3,493,817.48 rows=6,579,756 width=1,137) (actual time=48,729.162..104,310.251 rows=6,596,602 loops=1)

  • Output: mr.id, mr.order_number, mr.referral_date, mr.status_id, mr.ref_organization_id, mr.ref_department_id, mr.ref_doctor_id, mr.recv_doctor_id, mr.is_urgent, mr.is_operation_required, mr.patient_id, mr.referral_type_id, mr.comment, mr.receiving_speciality_id, mr.ref_status_id, mr.goal_id, mr.diagnosis_id, mr.funding_id, ms.case_id, ms.id, ref_org.short_name, ref_org.full_name, ref_dep.name, mrrt.name, po.id, po.full_name, po.short_name, pd.id, pd.name
  • Hash Cond: (mr.department_id = pd.id)
  • Buffers: shared hit=124,448 read=872,769 dirtied=1, temp read=118,953 written=118,947
  • I/O Timings: read=28,218.717
8. 6,000.615 98,362.041 ↓ 1.0 6,596,602 1

Hash Left Join (cost=1,998,481.60..3,435,058.37 rows=6,579,756 width=1,092) (actual time=48,722.903..98,362.041 rows=6,596,602 loops=1)

  • Output: mr.id, mr.order_number, mr.referral_date, mr.status_id, mr.ref_organization_id, mr.ref_department_id, mr.ref_doctor_id, mr.recv_doctor_id, mr.is_urgent, mr.is_operation_required, mr.patient_id, mr.referral_type_id, mr.comment, mr.receiving_speciality_id, mr.ref_status_id, mr.goal_id, mr.department_id, mr.diagnosis_id, mr.funding_id, ms.case_id, ms.id, ref_org.short_name, ref_org.full_name, ref_dep.name, mrrt.name, po.id, po.full_name, po.short_name
  • Hash Cond: (mr.recv_organization_id = po.id)
  • Buffers: shared hit=124,216 read=872,769 dirtied=1, temp read=118,953 written=118,947
  • I/O Timings: read=28,218.717
9. 3,256.392 92,282.983 ↓ 1.0 6,596,602 1

Hash Left Join (cost=1,992,605.48..3,339,651.52 rows=6,579,756 width=915) (actual time=48,643.825..92,282.983 rows=6,596,602 loops=1)

  • Output: mr.id, mr.order_number, mr.referral_date, mr.status_id, mr.ref_organization_id, mr.ref_department_id, mr.ref_doctor_id, mr.recv_doctor_id, mr.is_urgent, mr.is_operation_required, mr.patient_id, mr.referral_type_id, mr.comment, mr.receiving_speciality_id, mr.ref_status_id, mr.goal_id, mr.recv_organization_id, mr.department_id, mr.diagnosis_id, mr.funding_id, ms.case_id, ms.id, ref_org.short_name, ref_org.full_name, ref_dep.name, mrrt.name
  • Hash Cond: (mr.referral_type_id = mrrt.referral_type_id)
  • Buffers: shared hit=120,693 read=872,769 dirtied=1, temp read=118,953 written=118,947
  • I/O Timings: read=28,218.717
10. 5,726.495 89,026.590 ↓ 1.0 6,596,602 1

Hash Left Join (cost=1,992,593.00..3,249,404.27 rows=6,579,756 width=399) (actual time=48,643.808..89,026.590 rows=6,596,602 loops=1)

  • Output: mr.id, mr.order_number, mr.referral_date, mr.status_id, mr.ref_organization_id, mr.ref_department_id, mr.ref_doctor_id, mr.recv_doctor_id, mr.is_urgent, mr.is_operation_required, mr.patient_id, mr.referral_type_id, mr.comment, mr.receiving_speciality_id, mr.ref_status_id, mr.goal_id, mr.recv_organization_id, mr.department_id, mr.diagnosis_id, mr.funding_id, ms.case_id, ms.id, ref_org.short_name, ref_org.full_name, ref_dep.name
  • Hash Cond: (mr.ref_department_id = ref_dep.id)
  • Buffers: shared hit=120,693 read=872,769 dirtied=1, temp read=118,953 written=118,947
  • I/O Timings: read=28,218.717
11. 6,477.826 83,253.521 ↓ 1.0 6,596,602 1

Hash Left Join (cost=1,992,071.32..3,177,810.25 rows=6,579,756 width=354) (actual time=48,597.067..83,253.521 rows=6,596,602 loops=1)

  • Output: mr.id, mr.order_number, mr.referral_date, mr.status_id, mr.ref_organization_id, mr.ref_department_id, mr.ref_doctor_id, mr.recv_doctor_id, mr.is_urgent, mr.is_operation_required, mr.patient_id, mr.referral_type_id, mr.comment, mr.receiving_speciality_id, mr.ref_status_id, mr.goal_id, mr.recv_organization_id, mr.department_id, mr.diagnosis_id, mr.funding_id, ms.case_id, ms.id, ref_org.short_name, ref_org.full_name
  • Hash Cond: (mr.ref_organization_id = ref_org.id)
  • Buffers: shared hit=120,570 read=872,660 dirtied=1, temp read=118,953 written=118,947
  • I/O Timings: read=28,178.788
12. 19,588.871 76,687.361 ↓ 1.0 6,596,602 1

Hash Left Join (cost=1,986,195.20..3,081,495.38 rows=6,579,756 width=177) (actual time=48,508.144..76,687.361 rows=6,596,602 loops=1)

  • Output: mr.id, mr.order_number, mr.referral_date, mr.status_id, mr.ref_organization_id, mr.ref_department_id, mr.ref_doctor_id, mr.recv_doctor_id, mr.is_urgent, mr.is_operation_required, mr.patient_id, mr.referral_type_id, mr.comment, mr.receiving_speciality_id, mr.ref_status_id, mr.goal_id, mr.recv_organization_id, mr.department_id, mr.diagnosis_id, mr.funding_id, ms.case_id, ms.id
  • Hash Cond: (mr.step_id = ms.id)
  • Buffers: shared hit=117,047 read=872,660 dirtied=1, temp read=118,953 written=118,947
  • I/O Timings: read=28,178.788
13. 8,763.492 8,763.492 ↓ 1.0 6,596,602 1

Seq Scan on public.md_referral mr (cost=0.00..213,805.56 rows=6,579,756 width=177) (actual time=0.020..8,763.492 rows=6,596,602 loops=1)

  • Output: mr.id, mr.order_number, mr.referral_date, mr.status_id, mr.ref_organization_id, mr.ref_department_id, mr.ref_doctor_id, mr.recv_doctor_id, mr.is_urgent, mr.is_operation_required, mr.patient_id, mr.referral_type_id, mr.comment, mr.receiving_speciality_id, mr.ref_status_id, mr.goal_id, mr.step_id, mr.recv_organization_id, mr.department_id, mr.diagnosis_id, mr.service_id, mr.funding_id
  • Buffers: shared hit=97,471 read=50,537
  • I/O Timings: read=1,863.252
14. 12,977.294 48,334.998 ↓ 1.0 43,486,711 1

Hash (cost=1,275,117.31..1,275,117.31 rows=43,341,831 width=8) (actual time=48,334.998..48,334.998 rows=43,486,711 loops=1)

  • Output: ms.case_id, ms.id
  • Buckets: 16,777,216 Batches: 4 Memory Usage: 555,573kB
  • Buffers: shared hit=19,576 read=822,123 dirtied=1, temp written=111,491
  • I/O Timings: read=26,315.536
15. 35,357.704 35,357.704 ↓ 1.0 43,486,711 1

Seq Scan on public.mc_step ms (cost=0.00..1,275,117.31 rows=43,341,831 width=8) (actual time=5.706..35,357.704 rows=43,486,711 loops=1)

  • Output: ms.case_id, ms.id
  • Buffers: shared hit=19,576 read=822,123 dirtied=1
  • I/O Timings: read=26,315.536
16. 52.323 88.334 ↓ 1.0 104,608 1

Hash (cost=4,568.83..4,568.83 rows=104,583 width=181) (actual time=88.334..88.334 rows=104,608 loops=1)

  • Output: ref_org.short_name, ref_org.full_name, ref_org.id
  • Buckets: 131,072 Batches: 1 Memory Usage: 23,023kB
  • Buffers: shared hit=3,523
17. 36.011 36.011 ↓ 1.0 104,608 1

Seq Scan on public.pim_organization ref_org (cost=0.00..4,568.83 rows=104,583 width=181) (actual time=0.010..36.011 rows=104,608 loops=1)

  • Output: ref_org.short_name, ref_org.full_name, ref_org.id
  • Buffers: shared hit=3,523
18. 3.431 46.574 ↑ 1.0 12,875 1

Hash (cost=360.75..360.75 rows=12,875 width=49) (actual time=46.574..46.574 rows=12,875 loops=1)

  • Output: ref_dep.name, ref_dep.id
  • Buckets: 16,384 Batches: 1 Memory Usage: 1,170kB
  • Buffers: shared hit=123 read=109
  • I/O Timings: read=39.929
19. 43.143 43.143 ↑ 1.0 12,875 1

Seq Scan on public.pim_department ref_dep (cost=0.00..360.75 rows=12,875 width=49) (actual time=0.013..43.143 rows=12,875 loops=1)

  • Output: ref_dep.name, ref_dep.id
  • Buffers: shared hit=123 read=109
  • I/O Timings: read=39.929
20. 0.001 0.001 ↓ 0.0 0 1

Hash (cost=11.10..11.10 rows=110 width=520) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: mrrt.name, mrrt.referral_type_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
21. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on public.md_report_referral_type mrrt (cost=0.00..11.10 rows=110 width=520) (actual time=0.000..0.000 rows=0 loops=1)

  • Output: mrrt.name, mrrt.referral_type_id
22. 52.781 78.443 ↓ 1.0 104,608 1

Hash (cost=4,568.83..4,568.83 rows=104,583 width=181) (actual time=78.443..78.443 rows=104,608 loops=1)

  • Output: po.id, po.full_name, po.short_name
  • Buckets: 131,072 Batches: 1 Memory Usage: 22,893kB
  • Buffers: shared hit=3,523
23. 25.662 25.662 ↓ 1.0 104,608 1

Seq Scan on public.pim_organization po (cost=0.00..4,568.83 rows=104,583 width=181) (actual time=0.006..25.662 rows=104,608 loops=1)

  • Output: po.id, po.full_name, po.short_name
  • Buffers: shared hit=3,523
24. 3.438 6.093 ↑ 1.0 12,875 1

Hash (cost=360.75..360.75 rows=12,875 width=49) (actual time=6.093..6.093 rows=12,875 loops=1)

  • Output: pd.id, pd.name
  • Buckets: 16,384 Batches: 1 Memory Usage: 1,154kB
  • Buffers: shared hit=232
25. 2.655 2.655 ↑ 1.0 12,875 1

Seq Scan on public.pim_department pd (cost=0.00..360.75 rows=12,875 width=49) (actual time=0.006..2.655 rows=12,875 loops=1)

  • Output: pd.id, pd.name
  • Buffers: shared hit=232
26. 6.165 10.341 ↑ 1.0 15,388 1

Hash (cost=528.88..528.88 rows=15,388 width=103) (actual time=10.341..10.341 rows=15,388 loops=1)

  • Output: md.id, md.code, md.name
  • Buckets: 16,384 Batches: 1 Memory Usage: 2,175kB
  • Buffers: shared hit=375
27. 4.176 4.176 ↑ 1.0 15,388 1

Seq Scan on public.md_diagnosis md (cost=0.00..528.88 rows=15,388 width=103) (actual time=0.014..4.176 rows=15,388 loops=1)

  • Output: md.id, md.code, md.name
  • Buffers: shared hit=375
28. 0.011 0.017 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=4) (actual time=0.017..0.017 rows=8 loops=1)

  • Output: ffst.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
29. 0.006 0.006 ↑ 1.0 8 1

Seq Scan on public.fin_funding_source_type ffst (cost=0.00..1.08 rows=8 width=4) (actual time=0.004..0.006 rows=8 loops=1)

  • Output: ffst.id
  • Buffers: shared hit=1
30.          

SubPlan (for Hash Left Join)

31. 9,680.776 39,579.612 ↓ 0.0 0 6,596,602

Nested Loop Left Join (cost=1.01..21.19 rows=1 width=50) (actual time=0.006..0.006 rows=0 loops=6,596,602)

  • Output: (((((pi1.surname)::text || ' '::text) || "left"(initcap((pi1.name)::text), 1)) || '. '::text) || "left"(initcap((pi1.patr_name)::text), 1))
  • Buffers: shared hit=5,215,716 read=6,405 dirtied=9
  • I/O Timings: read=14,983.748
32. 3,464.296 13,193.204 ↓ 0.0 0 6,596,602

Nested Loop Left Join (cost=0.58..16.62 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=6,596,602)

  • Output: pe1.individual_id
  • Buffers: shared hit=3,132,653 read=791 dirtied=9
  • I/O Timings: read=1,449.154
33. 6,596.602 6,596.602 ↓ 0.0 0 6,596,602

Index Scan using pim_employee_position_pk on public.pim_employee_position pep1 (cost=0.29..8.31 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=6,596,602)

  • Output: pep1.id, pep1.dismissal_order_code, pep1.start_date, pep1.hiring_order_code, pep1.rate, pep1.end_date, pep1.dismissal_reason_id, pep1.employee_id, pep1.employment_type_id, pep1.hiring_type_id, pep1.position_id, pep1.position_type_id, pep1.unit_id, pep1.code, pep1.aud_who, pep1.aud_when, pep1.aud_source, pep1.aud_who_create, pep1.aud_when_create, pep1.aud_source_create, pep1.pref_prescription, pep1.extra_payment, pep1.target_training, pep1.leaving_reason_id
  • Index Cond: (pep1.id = mr.recv_doctor_id)
  • Buffers: shared hit=1,566,021 read=446 dirtied=6
  • I/O Timings: read=666.517
34. 3,132.306 3,132.306 ↑ 1.0 1 522,051

Index Scan using pim_employee_pk on public.pim_employee pe1 (cost=0.29..8.31 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=522,051)

  • Output: pe1.id, pe1.note, pe1.number, pe1.photo, pe1.callup_subject_id, pe1.individual_id, pe1.organization_id, pe1.is_dismissed, pe1.employment_dt, pe1.dismissal_dt, pe1.aud_who, pe1.aud_when, pe1.aud_source, pe1.aud_who_create, pe1.aud_when_create, pe1.aud_source_create, pe1.accreditation_id
  • Index Cond: (pep1.employee_id = pe1.id)
  • Buffers: shared hit=1,566,632 read=345 dirtied=3
  • I/O Timings: read=782.637
35. 16,705.632 16,705.632 ↑ 1.0 1 522,051

Index Scan using pim_individual_pk on public.pim_individual pi1 (cost=0.43..4.53 rows=1 width=54) (actual time=0.031..0.032 rows=1 loops=522,051)

  • Output: pi1.id, pi1.birth_dt, pi1.death_dt, pi1.has_citizenship, pi1.name, pi1.patr_name, pi1.surname, pi1.gender_id, pi1.nationality_id, pi1.list_identity_doc, pi1.list_oms_doc, pi1.list_job_org, pi1.list_reg_name, pi1.list_snils, pi1.list_uid, pi1.aud_who, pi1.aud_when, pi1.aud_source, pi1.aud_who_create, pi1.aud_when_create, pi1.aud_source_create, pi1.birth_place, pi1.age_year, pi1.age_month, pi1.age_day, pi1.list_main_contact
  • Index Cond: (pe1.individual_id = pi1.id)
  • Buffers: shared hit=2,083,063 read=5,614
  • I/O Timings: read=13,534.594
36. 6,400.924 164,915.050 ↑ 1.0 1 6,596,602

Result (cost=246.61..246.63 rows=1 width=0) (actual time=0.025..0.025 rows=1 loops=6,596,602)

  • Output: CASE WHEN ($4 IS TRUE) THEN (($6 || '; '::text) || $9) ELSE $13 END
  • Buffers: shared hit=72,988,553 read=143,489 dirtied=3,077
  • I/O Timings: read=32,613.917
37.          

Initplan (for Result)

38. 65,966.020 65,966.020 ↓ 0.0 0 6,596,602

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrsx (cost=0.43..9.09 rows=1 width=0) (actual time=0.010..0.010 rows=0 loops=6,596,602)

  • Index Cond: (mrsx.referral_id = mr.id)
  • Filter: (mrsx.main_complex IS TRUE)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=23,636,268 read=140,638 dirtied=3,076
  • I/O Timings: read=29,888.056
39. 21.742 282.646 ↑ 1.0 1 21,742

Limit (cost=0.86..17.55 rows=1 width=173) (actual time=0.013..0.013 rows=1 loops=21,742)

  • Output: (concat('[+] ', ss.name))
  • Buffers: shared hit=178,864 read=17
  • I/O Timings: read=0.230
40. 108.710 260.904 ↑ 1.0 1 21,742

Nested Loop (cost=0.86..17.55 rows=1 width=173) (actual time=0.012..0.012 rows=1 loops=21,742)

  • Output: concat('[+] ', ss.name)
  • Buffers: shared hit=178,864 read=17
  • I/O Timings: read=0.230
41. 65.226 65.226 ↑ 1.0 1 21,742

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs1 (cost=0.43..9.09 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=21,742)

  • Output: mrs1.id, mrs1.comment, mrs1.referral_id, mrs1.service_id, mrs1.service_prototype_id, mrs1.aud_who, mrs1.aud_when, mrs1.aud_source, mrs1.aud_who_create, mrs1.aud_when_create, mrs1.aud_source_create, mrs1.biomaterial_id, mrs1.main_complex, mrs1.biomaterial_dt, mrs1.complex_id, mrs1.building_id, mrs1.room_id
  • Index Cond: (mrs1.referral_id = mr.id)
  • Filter: (mrs1.main_complex IS TRUE)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=91,913
42. 86.968 86.968 ↑ 1.0 1 21,742

Index Scan using sr_service_pk on public.sr_service ss (cost=0.42..8.44 rows=1 width=177) (actual time=0.004..0.004 rows=1 loops=21,742)

  • Output: ss.id, ss.code, ss.cul, ss.is_death, ss.duration, ss.is_fictitious, ss.is_independent, ss.is_multuplicity, ss.name, ss.terms, ss.org_id, ss.category_id, ss.duration_unit_id, ss.prototype_id, ss.type_id, ss.from_dt, ss.to_dt, ss.is_repeated, ss.is_inherit_protocol, ss.is_expendable_materials, ss.is_actual_cul, ss.is_paraclinical, ss.is_complex, ss.accounting_id, ss.is_autocopy_diagnoses, ss.is_stomat, ss.srg_dif_type_id, ss.srg_opr_kind_id, ss.is_need_anatomic_zone, ss.aud_who, ss.aud_when, ss.aud_source, ss.aud_who_create, ss.aud_when_create, ss.aud_source_create, ss.is_filling, ss.is_extraction, ss.is_need_close_date, ss.is_vmp, ss.short_name
  • Index Cond: (ss.id = mrs1.service_id)
  • Buffers: shared hit=86,951 read=17
  • I/O Timings: read=0.230
43. 86.968 217.420 ↑ 1.0 1 21,742

Result (cost=76.71..76.73 rows=1 width=0) (actual time=0.009..0.010 rows=1 loops=21,742)

  • Output: array_to_string($8, '; '::text)
  • Buffers: shared hit=99,027 read=74
  • I/O Timings: read=15.790
44.          

Initplan (for Result)

45. 2.110 130.452 ↓ 0.0 0 21,742

Nested Loop (cost=0.86..76.71 rows=8 width=173) (actual time=0.006..0.006 rows=0 loops=21,742)

  • Output: ss_1.name
  • Buffers: shared hit=99,027 read=74
  • I/O Timings: read=15.790
46. 108.710 108.710 ↓ 0.0 0 21,742

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs1_1 (cost=0.43..9.09 rows=8 width=4) (actual time=0.005..0.005 rows=0 loops=21,742)

  • Output: mrs1_1.id, mrs1_1.comment, mrs1_1.referral_id, mrs1_1.service_id, mrs1_1.service_prototype_id, mrs1_1.aud_who, mrs1_1.aud_when, mrs1_1.aud_source, mrs1_1.aud_who_create, mrs1_1.aud_when_create, mrs1_1.aud_source_create, mrs1_1.biomaterial_id, mrs1_1.main_complex, mrs1_1.biomaterial_dt, mrs1_1.complex_id, mrs1_1.building_id, mrs1_1.room_id
  • Index Cond: (mrs1_1.referral_id = mr.id)
  • Filter: ((mrs1_1.complex_id IS NULL) AND (mrs1_1.main_complex IS NOT TRUE))
  • Rows Removed by Filter: 5
  • Buffers: shared hit=92,476 read=64
  • I/O Timings: read=2.707
47. 19.632 19.632 ↑ 1.0 1 1,636

Index Scan using sr_service_pk on public.sr_service ss_1 (cost=0.42..8.44 rows=1 width=177) (actual time=0.012..0.012 rows=1 loops=1,636)

  • Output: ss_1.id, ss_1.code, ss_1.cul, ss_1.is_death, ss_1.duration, ss_1.is_fictitious, ss_1.is_independent, ss_1.is_multuplicity, ss_1.name, ss_1.terms, ss_1.org_id, ss_1.category_id, ss_1.duration_unit_id, ss_1.prototype_id, ss_1.type_id, ss_1.from_dt, ss_1.to_dt, ss_1.is_repeated, ss_1.is_inherit_protocol, ss_1.is_expendable_materials, ss_1.is_actual_cul, ss_1.is_paraclinical, ss_1.is_complex, ss_1.accounting_id, ss_1.is_autocopy_diagnoses, ss_1.is_stomat, ss_1.srg_dif_type_id, ss_1.srg_opr_kind_id, ss_1.is_need_anatomic_zone, ss_1.aud_who, ss_1.aud_when, ss_1.aud_source, ss_1.aud_who_create, ss_1.aud_when_create, ss_1.aud_source_create, ss_1.is_filling, ss_1.is_extraction, ss_1.is_need_close_date, ss_1.is_vmp, ss_1.short_name
  • Index Cond: (ss_1.id = mrs1_1.service_id)
  • Buffers: shared hit=6,551 read=10
  • I/O Timings: read=13.083
48. 32,874.300 92,048.040 ↑ 1.0 1 6,574,860

Result (cost=143.23..143.25 rows=1 width=0) (actual time=0.013..0.014 rows=1 loops=6,574,860)

  • Output: array_to_string($12, '; '::text)
  • Buffers: shared hit=49,074,394 read=2,760 dirtied=1
  • I/O Timings: read=2,709.841
49.          

Initplan (for Result)

50. 6,574.860 59,173.740 ↑ 8.0 1 6,574,860

Nested Loop Left Join (cost=1.15..143.23 rows=8 width=334) (actual time=0.007..0.009 rows=1 loops=6,574,860)

  • Output: COALESCE(ss_2.name, ssp.name)
  • Buffers: shared hit=49,074,394 read=2,760 dirtied=1
  • I/O Timings: read=2,709.841
51. 7,714.090 52,598.880 ↑ 8.0 1 6,574,860

Nested Loop Left Join (cost=0.86..76.71 rows=8 width=177) (actual time=0.006..0.008 rows=1 loops=6,574,860)

  • Output: mrs1_2.service_prototype_id, ss_2.name
  • Buffers: shared hit=48,886,864 read=2,716 dirtied=1
  • I/O Timings: read=2,611.807
52. 13,149.720 13,149.720 ↑ 8.0 1 6,574,860

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs1_2 (cost=0.43..9.09 rows=8 width=8) (actual time=0.002..0.002 rows=1 loops=6,574,860)

  • Output: mrs1_2.id, mrs1_2.comment, mrs1_2.referral_id, mrs1_2.service_id, mrs1_2.service_prototype_id, mrs1_2.aud_who, mrs1_2.aud_when, mrs1_2.aud_source, mrs1_2.aud_who_create, mrs1_2.aud_when_create, mrs1_2.aud_source_create, mrs1_2.biomaterial_id, mrs1_2.main_complex, mrs1_2.biomaterial_dt, mrs1_2.complex_id, mrs1_2.building_id, mrs1_2.room_id
  • Index Cond: (mrs1_2.referral_id = mr.id)
  • Filter: ((mrs1_2.complex_id IS NULL) AND (mrs1_2.main_complex IS NOT TRUE))
  • Buffers: shared hit=23,684,689
53. 31,735.070 31,735.070 ↑ 1.0 1 6,347,014

Index Scan using sr_service_pk on public.sr_service ss_2 (cost=0.42..8.44 rows=1 width=177) (actual time=0.004..0.005 rows=1 loops=6,347,014)

  • Output: ss_2.id, ss_2.code, ss_2.cul, ss_2.is_death, ss_2.duration, ss_2.is_fictitious, ss_2.is_independent, ss_2.is_multuplicity, ss_2.name, ss_2.terms, ss_2.org_id, ss_2.category_id, ss_2.duration_unit_id, ss_2.prototype_id, ss_2.type_id, ss_2.from_dt, ss_2.to_dt, ss_2.is_repeated, ss_2.is_inherit_protocol, ss_2.is_expendable_materials, ss_2.is_actual_cul, ss_2.is_paraclinical, ss_2.is_complex, ss_2.accounting_id, ss_2.is_autocopy_diagnoses, ss_2.is_stomat, ss_2.srg_dif_type_id, ss_2.srg_opr_kind_id, ss_2.is_need_anatomic_zone, ss_2.aud_who, ss_2.aud_when, ss_2.aud_source, ss_2.aud_who_create, ss_2.aud_when_create, ss_2.aud_source_create, ss_2.is_filling, ss_2.is_extraction, ss_2.is_need_close_date, ss_2.is_vmp, ss_2.short_name
  • Index Cond: (mrs1_2.service_id = ss_2.id)
  • Buffers: shared hit=25,202,175 read=2,716 dirtied=1
  • I/O Timings: read=2,611.807
54. 0.000 0.000 ↓ 0.0 0 6,347,014

Index Scan using sr_srv_prototype_pk on public.sr_srv_prototype ssp (cost=0.29..8.30 rows=1 width=165) (actual time=0.000..0.000 rows=0 loops=6,347,014)

  • Output: ssp.id, ssp.code, ssp.is_independent, ssp.name, ssp.is_remote, ssp.terms, ssp.type_id, ssp.anesthesia_type, ssp.is_repeated, ssp.is_expendable_materials, ssp.protocol_path, ssp.report_path, ssp.is_complete_protocol, ssp.is_complex, ssp.accounting_id, ssp.from_dt, ssp.to_dt, ssp.e_code, ssp.aud_who, ssp.aud_when, ssp.aud_source, ssp.aud_who_create, ssp.aud_when_create, ssp.aud_source_create
  • Index Cond: (ssp.id = mrs1_2.service_prototype_id)
  • Buffers: shared hit=187,530 read=44
  • I/O Timings: read=98.034
55. 32,983.010 32,983.010 ↑ 1.0 1 6,596,602

Seq Scan on public.md_referral_type (cost=0.00..2.39 rows=1 width=1) (actual time=0.004..0.005 rows=1 loops=6,596,602)

  • Output: md_referral_type.is_laboratory
  • Filter: (md_referral_type.id = mr.referral_type_id)
  • Rows Removed by Filter: 30
  • Buffers: shared hit=13,193,204
56. 26,386.408 26,386.408 ↑ 1.0 1 6,596,602

Seq Scan on public.md_referral_type md_referral_type_1 (cost=0.00..2.39 rows=1 width=1) (actual time=0.003..0.004 rows=1 loops=6,596,602)

  • Output: md_referral_type_1.hsp_rsv_mark
  • Filter: (md_referral_type_1.id = mr.referral_type_id)
  • Rows Removed by Filter: 30
  • Buffers: shared hit=13,193,204
57. 26,386.408 125,335.438 ↑ 1.0 1 6,596,602

Aggregate (cost=86.11..86.12 rows=1 width=28) (actual time=0.019..0.019 rows=1 loops=6,596,602)

  • Output: array_to_json(array_agg(r.*))
  • Buffers: shared hit=64,280,148
58. 19,789.806 98,949.030 ↑ 8.0 1 6,596,602

Subquery Scan on r (cost=85.89..86.09 rows=8 width=28) (actual time=0.015..0.015 rows=1 loops=6,596,602)

  • Output: r.*
  • Buffers: shared hit=64,280,148
59. 0.000 79,159.224 ↑ 8.0 1 6,596,602

HashAggregate (cost=85.89..86.01 rows=8 width=186) (actual time=0.012..0.012 rows=1 loops=6,596,602)

  • Output: ss1.id, (CASE WHEN ($18 IS TRUE) THEN concat(ss1.code, ' ', ss1.name) ELSE concat(ss1.code, ' ', ss1.name) END)
  • Group Key: ss1.id, CASE WHEN ($18 IS TRUE) THEN concat(ss1.code, ' ', ss1.name) ELSE concat(ss1.code, ' ', ss1.name) END
  • Buffers: shared hit=64,280,148
60.          

Initplan (for HashAggregate)

61. 7,575.240 7,575.240 ↓ 0.0 0 3,787,620

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service xmrs (cost=0.43..9.09 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=3,787,620)

  • Index Cond: (xmrs.referral_id = mr.id)
  • Filter: xmrs.main_complex
  • Rows Removed by Filter: 2
  • Buffers: shared hit=15,291,469
62. 40,203.188 72,562.622 ↑ 8.0 1 6,596,602

Nested Loop Left Join (cost=0.86..76.75 rows=8 width=186) (actual time=0.008..0.011 rows=1 loops=6,596,602)

  • Output: ss1.id, CASE WHEN ($18 IS TRUE) THEN concat(ss1.code, ' ', ss1.name) ELSE concat(ss1.code, ' ', ss1.name) END
  • Buffers: shared hit=64,280,148
63. 19,789.806 19,789.806 ↑ 8.0 1 6,596,602

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs (cost=0.43..9.09 rows=8 width=4) (actual time=0.003..0.003 rows=1 loops=6,596,602)

  • Output: mrs.id, mrs.comment, mrs.referral_id, mrs.service_id, mrs.service_prototype_id, mrs.aud_who, mrs.aud_when, mrs.aud_source, mrs.aud_who_create, mrs.aud_when_create, mrs.aud_source_create, mrs.biomaterial_id, mrs.main_complex, mrs.biomaterial_dt, mrs.complex_id, mrs.building_id, mrs.room_id
  • Index Cond: (mrs.referral_id = mr.id)
  • Filter: ((mrs.main_complex IS NOT TRUE) AND (mrs.service_id IS NOT NULL) AND (mrs.complex_id IS NULL))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=23,777,229
64. 12,569.628 12,569.628 ↑ 1.0 1 6,284,814

Index Scan using sr_service_pk on public.sr_service ss1 (cost=0.42..8.44 rows=1 width=186) (actual time=0.002..0.002 rows=1 loops=6,284,814)

  • Output: ss1.id, ss1.code, ss1.cul, ss1.is_death, ss1.duration, ss1.is_fictitious, ss1.is_independent, ss1.is_multuplicity, ss1.name, ss1.terms, ss1.org_id, ss1.category_id, ss1.duration_unit_id, ss1.prototype_id, ss1.type_id, ss1.from_dt, ss1.to_dt, ss1.is_repeated, ss1.is_inherit_protocol, ss1.is_expendable_materials, ss1.is_actual_cul, ss1.is_paraclinical, ss1.is_complex, ss1.accounting_id, ss1.is_autocopy_diagnoses, ss1.is_stomat, ss1.srg_dif_type_id, ss1.srg_opr_kind_id, ss1.is_need_anatomic_zone, ss1.aud_who, ss1.aud_when, ss1.aud_source, ss1.aud_who_create, ss1.aud_when_create, ss1.aud_source_create, ss1.is_filling, ss1.is_extraction, ss1.is_need_close_date, ss1.is_vmp, ss1.short_name
  • Index Cond: (mrs.service_id = ss1.id)
  • Buffers: shared hit=25,211,450
65. 6,596.602 46,176.214 ↑ 1.0 1 6,596,602

Aggregate (cost=48.82..48.84 rows=1 width=28) (actual time=0.007..0.007 rows=1 loops=6,596,602)

  • Output: array_to_json(array_agg(r_1.*))
  • Buffers: shared hit=24,742,514 read=310
  • I/O Timings: read=369.618
66. 6,596.602 39,579.612 ↓ 0.0 0 6,596,602

Subquery Scan on r_1 (cost=48.79..48.82 rows=1 width=28) (actual time=0.006..0.006 rows=0 loops=6,596,602)

  • Output: r_1.*
  • Buffers: shared hit=24,742,514 read=310
  • I/O Timings: read=369.618
67. 6,454.785 32,983.010 ↓ 0.0 0 6,596,602

HashAggregate (cost=48.79..48.81 rows=1 width=187) (actual time=0.005..0.005 rows=0 loops=6,596,602)

  • Output: ss1_1.id, (CASE WHEN ($21 IS TRUE) THEN CASE WHEN (sc.is_required IS TRUE) THEN concat('[!] ', ss1_1.code, ' ', ss1_1.name) ELSE concat(ss1_1.code, ' ', ss1_1.name) END ELSE concat(ss1_1.code, ' ', ss1_1.name) END)
  • Group Key: ss1_1.id, CASE WHEN ($21 IS TRUE) THEN CASE WHEN (sc.is_required IS TRUE) THEN concat('[!] ', ss1_1.code, ' ', ss1_1.name) ELSE concat(ss1_1.code, ' ', ss1_1.name) END ELSE concat(ss1_1.code, ' ', ss1_1.name) END
  • Buffers: shared hit=24,742,514 read=310
  • I/O Timings: read=369.618
68.          

Initplan (for HashAggregate)

69. 60.933 60.933 ↑ 1.0 1 20,311

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service xmrs_1 (cost=0.43..9.09 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=20,311)

  • Index Cond: (xmrs_1.referral_id = mr.id)
  • Filter: xmrs_1.main_complex
  • Rows Removed by Filter: 4
  • Buffers: shared hit=86,171
70. 20.221 80.884 ↑ 1.0 1 20,221

Limit (cost=0.43..9.09 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=20,221)

  • Output: xmrs_2.service_id
  • Buffers: shared hit=85,809
71. 60.663 60.663 ↑ 1.0 1 20,221

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service xmrs_2 (cost=0.43..9.09 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=20,221)

  • Output: xmrs_2.service_id
  • Index Cond: (xmrs_2.referral_id = mr.id)
  • Filter: xmrs_2.main_complex
  • Rows Removed by Filter: 4
  • Buffers: shared hit=85,809
72. 0.000 26,386.408 ↓ 0.0 0 6,596,602

Nested Loop Left Join (cost=9.88..30.60 rows=1 width=187) (actual time=0.004..0.004 rows=0 loops=6,596,602)

  • Output: ss1_1.id, CASE WHEN ($21 IS TRUE) THEN CASE WHEN (sc.is_required IS TRUE) THEN concat('[!] ', ss1_1.code, ' ', ss1_1.name) ELSE concat(ss1_1.code, ' ', ss1_1.name) END ELSE concat(ss1_1.code, ' ', ss1_1.name) END
  • Buffers: shared hit=24,742,514 read=310
  • I/O Timings: read=369.618
73. 6,243.974 26,386.408 ↓ 0.0 0 6,596,602

Nested Loop Left Join (cost=0.86..17.55 rows=1 width=190) (actual time=0.003..0.004 rows=0 loops=6,596,602)

  • Output: mrs_1.service_id, ss1_1.id, ss1_1.code, ss1_1.name
  • Buffers: shared hit=24,129,704 read=195
  • I/O Timings: read=55.455
74. 19,789.806 19,789.806 ↓ 0.0 0 6,596,602

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs_1 (cost=0.43..9.09 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=6,596,602)

  • Output: mrs_1.id, mrs_1.comment, mrs_1.referral_id, mrs_1.service_id, mrs_1.service_prototype_id, mrs_1.aud_who, mrs_1.aud_when, mrs_1.aud_source, mrs_1.aud_who_create, mrs_1.aud_when_create, mrs_1.aud_source_create, mrs_1.biomaterial_id, mrs_1.main_complex, mrs_1.biomaterial_dt, mrs_1.complex_id, mrs_1.building_id, mrs_1.room_id
  • Index Cond: (mrs_1.referral_id = mr.id)
  • Filter: ((mrs_1.main_complex IS NOT TRUE) AND (mrs_1.service_id IS NOT NULL) AND ((mrs_1.complex_id IS NOT NULL) OR (mrs_1.main_complex IS TRUE)))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=23,777,229
75. 352.628 352.628 ↑ 1.0 1 88,157

Index Scan using sr_service_pk on public.sr_service ss1_1 (cost=0.42..8.44 rows=1 width=186) (actual time=0.004..0.004 rows=1 loops=88,157)

  • Output: ss1_1.id, ss1_1.code, ss1_1.cul, ss1_1.is_death, ss1_1.duration, ss1_1.is_fictitious, ss1_1.is_independent, ss1_1.is_multuplicity, ss1_1.name, ss1_1.terms, ss1_1.org_id, ss1_1.category_id, ss1_1.duration_unit_id, ss1_1.prototype_id, ss1_1.type_id, ss1_1.from_dt, ss1_1.to_dt, ss1_1.is_repeated, ss1_1.is_inherit_protocol, ss1_1.is_expendable_materials, ss1_1.is_actual_cul, ss1_1.is_paraclinical, ss1_1.is_complex, ss1_1.accounting_id, ss1_1.is_autocopy_diagnoses, ss1_1.is_stomat, ss1_1.srg_dif_type_id, ss1_1.srg_opr_kind_id, ss1_1.is_need_anatomic_zone, ss1_1.aud_who, ss1_1.aud_when, ss1_1.aud_source, ss1_1.aud_who_create, ss1_1.aud_when_create, ss1_1.aud_source_create, ss1_1.is_filling, ss1_1.is_extraction, ss1_1.is_need_close_date, ss1_1.is_vmp, ss1_1.short_name
  • Index Cond: (mrs_1.service_id = ss1_1.id)
  • Buffers: shared hit=352,475 read=195
  • I/O Timings: read=55.455
76. 352.628 1,146.041 ↑ 1.0 1 88,157

Bitmap Heap Scan on public.sr_srv_composition sc (cost=9.02..13.04 rows=1 width=5) (actual time=0.013..0.013 rows=1 loops=88,157)

  • Output: sc.id, sc.service_id, sc.complex_id, sc.is_required, sc.is_default, sc.aud_who, sc.aud_when, sc.aud_source, sc.aud_who_create, sc.aud_when_create, sc.aud_source_create
  • Recheck Cond: ((mrs_1.service_id = sc.service_id) AND (sc.complex_id = $22))
  • Heap Blocks: exact=87,985
  • Buffers: shared hit=526,639 read=115
  • I/O Timings: read=314.163
77. 176.902 793.413 ↓ 0.0 0 88,157

BitmapAnd (cost=9.02..9.02 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=88,157)

  • Buffers: shared hit=438,726 read=43
  • I/O Timings: read=107.876
78. 264.471 264.471 ↑ 1.7 3 88,157

Bitmap Index Scan on sr_srv_composition_service_id_idx (cost=0.00..4.33 rows=5 width=0) (actual time=0.003..0.003 rows=3 loops=88,157)

  • Index Cond: (mrs_1.service_id = sc.service_id)
  • Buffers: shared hit=176,560 read=25
  • I/O Timings: read=14.882
79. 352.040 352.040 ↑ 1.1 18 88,010

Bitmap Index Scan on sr_srv_composition_complex_id_idx (cost=0.00..4.44 rows=20 width=0) (actual time=0.004..0.004 rows=18 loops=88,010)

  • Index Cond: (sc.complex_id = $22)
  • Buffers: shared hit=176,357 read=18
  • I/O Timings: read=92.994
80. 0.000 46,176.214 ↓ 0.0 0 6,596,602

Subquery Scan on r_2 (cost=83.37..83.39 rows=1 width=28) (actual time=0.007..0.007 rows=0 loops=6,596,602)

  • Output: row_to_json(r_2.*)
  • Buffers: shared hit=24,030,663 read=86
  • I/O Timings: read=262.578
81. 6,596.602 46,176.214 ↓ 0.0 0 6,596,602

Limit (cost=83.37..83.38 rows=1 width=190) (actual time=0.007..0.007 rows=0 loops=6,596,602)

  • Output: ss1_2.id, (concat('[+] ', ss1_2.code, ' ', ss1_2.name)), ((SubPlan 17)), mrs_2.id
  • Buffers: shared hit=24,030,663 read=86
  • I/O Timings: read=262.578
82. 19,789.806 39,579.612 ↓ 0.0 0 6,596,602

Sort (cost=83.37..83.38 rows=1 width=190) (actual time=0.006..0.006 rows=0 loops=6,596,602)

  • Output: ss1_2.id, (concat('[+] ', ss1_2.code, ' ', ss1_2.name)), ((SubPlan 17)), mrs_2.id
  • Sort Key: mrs_2.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=24,030,663 read=86
  • I/O Timings: read=262.578
83. 6,031.310 19,789.806 ↓ 0.0 0 6,596,602

Nested Loop Left Join (cost=0.86..83.36 rows=1 width=190) (actual time=0.003..0.003 rows=0 loops=6,596,602)

  • Output: ss1_2.id, concat('[+] ', ss1_2.code, ' ', ss1_2.name), (SubPlan 17), mrs_2.id
  • Buffers: shared hit=24,030,660 read=86
  • I/O Timings: read=262.578
84. 13,193.204 13,193.204 ↓ 0.0 0 6,596,602

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs_2 (cost=0.43..9.09 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=6,596,602)

  • Output: mrs_2.id, mrs_2.comment, mrs_2.referral_id, mrs_2.service_id, mrs_2.service_prototype_id, mrs_2.aud_who, mrs_2.aud_when, mrs_2.aud_source, mrs_2.aud_who_create, mrs_2.aud_when_create, mrs_2.aud_source_create, mrs_2.biomaterial_id, mrs_2.main_complex, mrs_2.biomaterial_dt, mrs_2.complex_id, mrs_2.building_id, mrs_2.room_id
  • Index Cond: (mrs_2.referral_id = mr.id)
  • Filter: (mrs_2.main_complex IS TRUE)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=23,777,229
85. 65.226 65.226 ↑ 1.0 1 21,742

Index Scan using sr_service_pk on public.sr_service ss1_2 (cost=0.42..8.44 rows=1 width=186) (actual time=0.003..0.003 rows=1 loops=21,742)

  • Output: ss1_2.id, ss1_2.code, ss1_2.cul, ss1_2.is_death, ss1_2.duration, ss1_2.is_fictitious, ss1_2.is_independent, ss1_2.is_multuplicity, ss1_2.name, ss1_2.terms, ss1_2.org_id, ss1_2.category_id, ss1_2.duration_unit_id, ss1_2.prototype_id, ss1_2.type_id, ss1_2.from_dt, ss1_2.to_dt, ss1_2.is_repeated, ss1_2.is_inherit_protocol, ss1_2.is_expendable_materials, ss1_2.is_actual_cul, ss1_2.is_paraclinical, ss1_2.is_complex, ss1_2.accounting_id, ss1_2.is_autocopy_diagnoses, ss1_2.is_stomat, ss1_2.srg_dif_type_id, ss1_2.srg_opr_kind_id, ss1_2.is_need_anatomic_zone, ss1_2.aud_who, ss1_2.aud_when, ss1_2.aud_source, ss1_2.aud_who_create, ss1_2.aud_when_create, ss1_2.aud_source_create, ss1_2.is_filling, ss1_2.is_extraction, ss1_2.is_need_close_date, ss1_2.is_vmp, ss1_2.short_name
  • Index Cond: (mrs_2.service_id = ss1_2.id)
  • Buffers: shared hit=87,020
86.          

SubPlan (for Nested Loop Left Join)

87. 21.742 500.066 ↑ 1.0 1 21,742

Aggregate (cost=65.81..65.82 rows=1 width=4) (actual time=0.023..0.023 rows=1 loops=21,742)

  • Output: array_agg(s1.id)
  • Buffers: shared hit=166,411 read=86
  • I/O Timings: read=262.578
88. 31.568 478.324 ↑ 1.0 1 21,742

Nested Loop (cost=0.71..65.80 rows=1 width=4) (actual time=0.014..0.022 rows=1 loops=21,742)

  • Output: s1.id
  • Buffers: shared hit=166,411 read=86
  • I/O Timings: read=262.578
89. 413.098 413.098 ↑ 1.0 1 21,742

Index Scan using sr_srv_composition_complex_id_idx on public.sr_srv_composition sc1 (cost=0.29..61.35 rows=1 width=4) (actual time=0.014..0.019 rows=1 loops=21,742)

  • Output: sc1.id, sc1.service_id, sc1.complex_id, sc1.is_required, sc1.is_default, sc1.aud_who, sc1.aud_when, sc1.aud_source, sc1.aud_who_create, sc1.aud_when_create, sc1.aud_source_create
  • Index Cond: (sc1.complex_id = ss1_2.id)
  • Filter: sc1.is_required
  • Rows Removed by Filter: 15
  • Buffers: shared hit=113,667 read=85
  • I/O Timings: read=262.085
90. 33.658 33.658 ↑ 1.0 1 16,829

Index Only Scan using sr_service_pk on public.sr_service s1 (cost=0.42..4.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=16,829)

  • Output: s1.id
  • Index Cond: (s1.id = sc1.service_id)
  • Heap Fetches: 2,257
  • Buffers: shared hit=52,744 read=1
  • I/O Timings: read=0.493
91. 0.000 32,983.010 ↑ 1.0 1 6,596,602

Aggregate (cost=17.44..17.45 rows=1 width=197) (actual time=0.005..0.005 rows=1 loops=6,596,602)

  • Output: array_to_json(array_agg(r_3.*))
  • Buffers: shared hit=23,964,803
92. 6,596.602 32,983.010 ↓ 0.0 0 6,596,602

Subquery Scan on r_3 (cost=17.42..17.43 rows=1 width=197) (actual time=0.005..0.005 rows=0 loops=6,596,602)

  • Output: r_3.*
  • Buffers: shared hit=23,964,803
93. 6,596.602 26,386.408 ↓ 0.0 0 6,596,602

Sort (cost=17.42..17.42 rows=1 width=169) (actual time=0.004..0.004 rows=0 loops=6,596,602)

  • Output: mrs_3.id, mrs_3.id, mrs_3.service_prototype_id, ssp_1.name
  • Sort Key: mrs_3.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=23,964,803
94. 6,471.562 19,789.806 ↓ 0.0 0 6,596,602

Nested Loop Left Join (cost=0.72..17.41 rows=1 width=169) (actual time=0.003..0.003 rows=0 loops=6,596,602)

  • Output: mrs_3.id, mrs_3.id, mrs_3.service_prototype_id, ssp_1.name
  • Buffers: shared hit=23,964,803
95. 13,193.204 13,193.204 ↓ 0.0 0 6,596,602

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs_3 (cost=0.43..9.09 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=6,596,602)

  • Output: mrs_3.id, mrs_3.comment, mrs_3.referral_id, mrs_3.service_id, mrs_3.service_prototype_id, mrs_3.aud_who, mrs_3.aud_when, mrs_3.aud_source, mrs_3.aud_who_create, mrs_3.aud_when_create, mrs_3.aud_source_create, mrs_3.biomaterial_id, mrs_3.main_complex, mrs_3.biomaterial_dt, mrs_3.complex_id, mrs_3.building_id, mrs_3.room_id
  • Index Cond: (mrs_3.referral_id = mr.id)
  • Filter: (mrs_3.service_prototype_id IS NOT NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=23,777,229
96. 125.040 125.040 ↑ 1.0 1 62,520

Index Scan using sr_srv_prototype_pk on public.sr_srv_prototype ssp_1 (cost=0.29..8.30 rows=1 width=165) (actual time=0.002..0.002 rows=1 loops=62,520)

  • Output: ssp_1.id, ssp_1.code, ssp_1.is_independent, ssp_1.name, ssp_1.is_remote, ssp_1.terms, ssp_1.type_id, ssp_1.anesthesia_type, ssp_1.is_repeated, ssp_1.is_expendable_materials, ssp_1.protocol_path, ssp_1.report_path, ssp_1.is_complete_protocol, ssp_1.is_complex, ssp_1.accounting_id, ssp_1.from_dt, ssp_1.to_dt, ssp_1.e_code, ssp_1.aud_who, ssp_1.aud_when, ssp_1.aud_source, ssp_1.aud_who_create, ssp_1.aud_when_create, ssp_1.aud_source_create
  • Index Cond: (mrs_3.service_prototype_id = ssp_1.id)
  • Buffers: shared hit=187,574
97. 26,386.408 514,534.956 ↑ 1.0 1 6,596,602

Aggregate (cost=36.56..36.57 rows=1 width=597) (actual time=0.078..0.078 rows=1 loops=6,596,602)

  • Output: array_to_json(array_agg(ar.*))
  • Buffers: shared hit=44,776,293 read=367,534 dirtied=19,249
  • I/O Timings: read=194,963.961
98. 6,596.602 488,148.548 ↑ 2.0 1 6,596,602

Subquery Scan on ar (cost=36.53..36.55 rows=2 width=597) (actual time=0.074..0.074 rows=1 loops=6,596,602)

  • Output: ar.*
  • Buffers: shared hit=44,776,293 read=367,534 dirtied=19,249
  • I/O Timings: read=194,963.961
99. 32,983.010 481,551.946 ↑ 2.0 1 6,596,602

Sort (cost=36.53..36.53 rows=2 width=603) (actual time=0.073..0.073 rows=1 loops=6,596,602)

  • Output: mra.id, mra.id, mrat.id, mrat.name, mra.appendix_name, mra.file_name, mra.comment, ((SubPlan 20))
  • Sort Key: mra.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=44,776,293 read=367,534 dirtied=19,249
  • I/O Timings: read=194,963.961
100. 33,907.692 448,568.936 ↑ 2.0 1 6,596,602

Hash Right Join (cost=8.53..36.52 rows=2 width=603) (actual time=0.048..0.068 rows=1 loops=6,596,602)

  • Output: mra.id, mra.id, mrat.id, mrat.name, mra.appendix_name, mra.file_name, mra.comment, (SubPlan 20)
  • Hash Cond: (mrat.id = mra.appendix_type_id)
  • Buffers: shared hit=44,776,293 read=367,534 dirtied=19,249
  • I/O Timings: read=194,963.961
101. 0.000 0.000 ↓ 0.0 0 2,685,437

Seq Scan on public.md_referral_appendix_type mrat (cost=0.00..10.60 rows=60 width=520) (actual time=0.000..0.000 rows=0 loops=2,685,437)

  • Output: mrat.id, mrat.code, mrat.name, mrat.aud_who, mrat.aud_when, mrat.aud_source, mrat.aud_who_create, mrat.aud_when_create, mrat.aud_source_create
102. 6,596.602 145,125.244 ↑ 2.0 1 6,596,602

Hash (cost=8.50..8.50 rows=2 width=87) (actual time=0.022..0.022 rows=1 loops=6,596,602)

  • Output: mra.id, mra.appendix_name, mra.file_name, mra.comment, mra.fs_url, mra.appendix_type_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=23,853,290 read=173,648 dirtied=11,369
  • I/O Timings: read=99,595.967
103. 138,528.642 138,528.642 ↑ 2.0 1 6,596,602

Index Scan using md_referral_appendix_referral_id_idx on public.md_referral_appendix mra (cost=0.43..8.50 rows=2 width=87) (actual time=0.019..0.021 rows=1 loops=6,596,602)

  • Output: mra.id, mra.appendix_name, mra.file_name, mra.comment, mra.fs_url, mra.appendix_type_id
  • Index Cond: (mra.referral_id = mr.id)
  • Buffers: shared hit=23,853,290 read=173,648 dirtied=11,369
  • I/O Timings: read=99,595.967
104.          

SubPlan (for Hash Right Join)

105. 269,536.000 269,536.000 ↑ 1.0 1 4,211,500

Index Scan using file_storage_url_pk on public.file_storage fs (cost=0.56..8.58 rows=1 width=107) (actual time=0.064..0.064 rows=1 loops=4,211,500)

  • Output: to_json(ROW(fs.url, fs.path, fs.original_file_name))
  • Index Cond: (fs.url = mra.fs_url)
  • Buffers: shared hit=20,923,003 read=193,886 dirtied=7,880
  • I/O Timings: read=95,367.994
106. 13,193.204 52,772.816 ↑ 1.0 1 6,596,602

Aggregate (cost=8.54..8.56 rows=1 width=28) (actual time=0.008..0.008 rows=1 loops=6,596,602)

  • Output: array_to_json(array_agg(ar_1.*))
  • Buffers: shared hit=24,007,125
107. 6,596.602 39,579.612 ↑ 2.0 1 6,596,602

Subquery Scan on ar_1 (cost=8.51..8.54 rows=2 width=28) (actual time=0.006..0.006 rows=1 loops=6,596,602)

  • Output: ar_1.*
  • Buffers: shared hit=24,007,125
108. 13,193.204 32,983.010 ↑ 2.0 1 6,596,602

Sort (cost=8.51..8.52 rows=2 width=4) (actual time=0.005..0.005 rows=1 loops=6,596,602)

  • Output: mra_1.id
  • Sort Key: mra_1.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=24,007,125
109. 19,789.806 19,789.806 ↑ 2.0 1 6,596,602

Index Scan using md_referral_appendix_referral_id_idx on public.md_referral_appendix mra_1 (cost=0.43..8.50 rows=2 width=4) (actual time=0.003..0.003 rows=1 loops=6,596,602)

  • Output: mra_1.id
  • Index Cond: (mra_1.referral_id = mr.id)
  • Buffers: shared hit=24,007,125
110. 6,596.602 39,579.612 ↑ 1.0 1 6,596,602

Aggregate (cost=9.12..9.14 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=6,596,602)

  • Output: array_to_json(array_agg(r_4.*))
  • Buffers: shared hit=23,777,229
111. 0.000 32,983.010 ↓ 0.0 0 6,596,602

Subquery Scan on r_4 (cost=9.10..9.12 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=6,596,602)

  • Output: r_4.*
  • Buffers: shared hit=23,777,229
112. 13,193.204 32,983.010 ↓ 0.0 0 6,596,602

Sort (cost=9.10..9.11 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=6,596,602)

  • Output: mrs_4.id
  • Sort Key: mrs_4.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=23,777,229
113. 19,789.806 19,789.806 ↓ 0.0 0 6,596,602

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs_4 (cost=0.43..9.09 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=6,596,602)

  • Output: mrs_4.id
  • Index Cond: (mrs_4.referral_id = mr.id)
  • Filter: ((mrs_4.main_complex IS TRUE) OR (mrs_4.complex_id IS NOT NULL))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=23,777,229
114. 19,789.806 52,772.816 ↑ 1.0 1 6,596,602

Aggregate (cost=9.34..9.35 rows=1 width=28) (actual time=0.008..0.008 rows=1 loops=6,596,602)

  • Output: array_to_json(array_agg(r_5.*))
  • Buffers: shared hit=23,777,229
115. 6,596.602 32,983.010 ↑ 8.0 1 6,596,602

Subquery Scan on r_5 (cost=9.21..9.31 rows=8 width=28) (actual time=0.005..0.005 rows=1 loops=6,596,602)

  • Output: r_5.*
  • Buffers: shared hit=23,777,229
116. 13,193.204 26,386.408 ↑ 8.0 1 6,596,602

Sort (cost=9.21..9.23 rows=8 width=4) (actual time=0.004..0.004 rows=1 loops=6,596,602)

  • Output: mrs_5.id
  • Sort Key: mrs_5.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=23,777,229
117. 13,193.204 13,193.204 ↑ 8.0 1 6,596,602

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs_5 (cost=0.43..9.09 rows=8 width=4) (actual time=0.002..0.002 rows=1 loops=6,596,602)

  • Output: mrs_5.id
  • Index Cond: (mrs_5.referral_id = mr.id)
  • Filter: ((mrs_5.main_complex IS NOT TRUE) AND (mrs_5.complex_id IS NULL))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=23,777,229
118. 0.000 0.000 ↓ 0.0 0

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service xmrs_3 (cost=0.43..9.09 rows=1 width=0) (never executed)

  • Index Cond: (xmrs_3.referral_id = mr.id)
  • Filter: xmrs_3.main_complex
119. 3,182.313 3,182.313 ↑ 1.2 21,742 1

Seq Scan on public.md_referral_service xmrs_4 (cost=0.00..162,897.57 rows=25,675 width=4) (actual time=7.349..3,182.313 rows=21,742 loops=1)

  • Output: xmrs_4.referral_id
  • Filter: xmrs_4.main_complex
  • Rows Removed by Filter: 6,436,807
  • Buffers: shared hit=52 read=98,658 dirtied=7,211
  • I/O Timings: read=2,296.889
120. 6,486.351 19,459.053 ↑ 1.0 1 6,486,351

Aggregate (cost=9.10..9.11 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=6,486,351)

  • Output: count(md_referral_service.service_prototype_id)
  • Buffers: shared hit=23,410,501
121. 12,972.702 12,972.702 ↓ 0.0 0 6,486,351

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service (cost=0.43..9.09 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=6,486,351)

  • Output: md_referral_service.id, md_referral_service.comment, md_referral_service.referral_id, md_referral_service.service_id, md_referral_service.service_prototype_id, md_referral_service.aud_who, md_referral_service.aud_when, md_referral_service.aud_source, md_referral_service.aud_who_create, md_referral_service.aud_when_create, md_referral_service.aud_source_create, md_referral_service.biomaterial_id, md_referral_service.main_complex, md_referral_service.biomaterial_dt, md_referral_service.complex_id, md_referral_service.building_id, md_referral_service.room_id
  • Index Cond: (md_referral_service.referral_id = mr.id)
  • Filter: (md_referral_service.service_prototype_id IS NOT NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=23,410,501
122. 168.492 463.353 ↑ 1.0 1 42,123

Aggregate (cost=44.73..44.74 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=42,123)

  • Output: count(DISTINCT ss1_3.id)
  • Buffers: shared hit=267,382
123. 49.200 294.861 ↑ 8.0 1 42,123

Nested Loop Left Join (cost=0.86..44.71 rows=8 width=4) (actual time=0.006..0.007 rows=1 loops=42,123)

  • Output: ss1_3.id
  • Buffers: shared hit=267,382
124. 126.369 126.369 ↑ 8.0 1 42,123

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs_6 (cost=0.43..9.09 rows=8 width=4) (actual time=0.002..0.003 rows=1 loops=42,123)

  • Output: mrs_6.id, mrs_6.comment, mrs_6.referral_id, mrs_6.service_id, mrs_6.service_prototype_id, mrs_6.aud_who, mrs_6.aud_when, mrs_6.aud_source, mrs_6.aud_who_create, mrs_6.aud_when_create, mrs_6.aud_source_create, mrs_6.biomaterial_id, mrs_6.main_complex, mrs_6.biomaterial_dt, mrs_6.complex_id, mrs_6.building_id, mrs_6.room_id
  • Index Cond: (mrs_6.referral_id = mr.id)
  • Filter: ((mrs_6.main_complex IS NOT TRUE) AND (mrs_6.service_id IS NOT NULL))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=173,287
125. 119.292 119.292 ↑ 1.0 1 29,823

Index Only Scan using sr_service_pk on public.sr_service ss1_3 (cost=0.42..4.44 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=29,823)

  • Output: ss1_3.id
  • Index Cond: (ss1_3.id = mrs_6.service_id)
  • Heap Fetches: 4,581
  • Buffers: shared hit=94,095
126. 23.447 20,961.618 ↑ 1.0 1 23,447

Aggregate (cost=93.19..93.20 rows=1 width=4) (actual time=0.894..0.894 rows=1 loops=23,447)

  • Output: count(sr_service.id)
  • Buffers: shared hit=786,619 read=778
  • I/O Timings: read=615.490
127.          

Initplan (for Aggregate)

128. 23.447 117.235 ↑ 1.0 1 23,447

Limit (cost=0.43..9.09 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=23,447)

  • Output: md_referral_service_1.service_prototype_id
  • Buffers: shared hit=94,758
129. 93.788 93.788 ↑ 1.0 1 23,447

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service md_referral_service_1 (cost=0.43..9.09 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=23,447)

  • Output: md_referral_service_1.service_prototype_id
  • Index Cond: (md_referral_service_1.referral_id = mr.id)
  • Filter: (md_referral_service_1.service_prototype_id IS NOT NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=94,758
130. 117.235 20,820.936 ↑ 1.0 1 23,447

Bitmap Heap Scan on public.sr_service (cost=80.08..84.09 rows=1 width=4) (actual time=0.887..0.888 rows=1 loops=23,447)

  • Output: sr_service.id, sr_service.code, sr_service.cul, sr_service.is_death, sr_service.duration, sr_service.is_fictitious, sr_service.is_independent, sr_service.is_multuplicity, sr_service.name, sr_service.terms, sr_service.org_id, sr_service.category_id, sr_service.duration_unit_id, sr_service.prototype_id, sr_service.type_id, sr_service.from_dt, sr_service.to_dt, sr_service.is_repeated, sr_service.is_inherit_protocol, sr_service.is_expendable_materials, sr_service.is_actual_cul, sr_service.is_paraclinical, sr_service.is_complex, sr_service.accounting_id, sr_service.is_autocopy_diagnoses, sr_service.is_stomat, sr_service.srg_dif_type_id, sr_service.srg_opr_kind_id, sr_service.is_need_anatomic_zone, sr_service.aud_who, sr_service.aud_when, sr_service.aud_source, sr_service.aud_who_create, sr_service.aud_when_create, sr_service.aud_source_create, sr_service.is_filling, sr_service.is_extraction, sr_service.is_need_close_date, sr_service.is_vmp, sr_service.short_name
  • Recheck Cond: ((sr_service.prototype_id = $41) AND (sr_service.org_id = po.id))
  • Heap Blocks: exact=22,120
  • Buffers: shared hit=786,619 read=778
  • I/O Timings: read=615.490
131. 748.498 20,703.701 ↓ 0.0 0 23,447

BitmapAnd (cost=80.08..80.08 rows=1 width=0) (actual time=0.883..0.883 rows=0 loops=23,447)

  • Buffers: shared hit=764,531 read=746
  • I/O Timings: read=612.639
132. 515.834 515.834 ↓ 1.2 125 23,447

Bitmap Index Scan on sr_service_prototype_id_idx (cost=0.00..5.21 rows=104 width=0) (actual time=0.022..0.022 rows=125 loops=23,447)

  • Index Cond: (sr_service.prototype_id = $41)
  • Buffers: shared hit=77,008 read=397
  • I/O Timings: read=147.515
133. 19,439.369 19,439.369 ↓ 2.3 9,258 19,897

Bitmap Index Scan on sr_service_org_id_id_idx (cost=0.00..74.62 rows=4,026 width=0) (actual time=0.977..0.977 rows=9,258 loops=19,897)

  • Index Cond: (sr_service.org_id = po.id)
  • Buffers: shared hit=592,765 read=349
  • I/O Timings: read=465.124
134. 0.000 1,591.172 ↑ 1.0 1 1,682

Limit (cost=89.17..93.18 rows=1 width=4) (actual time=0.946..0.946 rows=1 loops=1,682)

  • Output: sr_service_1.id
  • Buffers: shared hit=65,000
135.          

Initplan (for Limit)

136. 11.774 11.774 ↑ 1.0 1 1,682

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service md_referral_service_2 (cost=0.43..9.09 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1,682)

  • Output: md_referral_service_2.service_prototype_id
  • Index Cond: (md_referral_service_2.referral_id = mr.id)
  • Filter: (md_referral_service_2.service_prototype_id IS NOT NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=7,139
137. 10.092 1,582.762 ↑ 1.0 1 1,682

Bitmap Heap Scan on public.sr_service sr_service_1 (cost=80.08..84.09 rows=1 width=4) (actual time=0.941..0.941 rows=1 loops=1,682)

  • Output: sr_service_1.id
  • Recheck Cond: ((sr_service_1.prototype_id = $44) AND (sr_service_1.org_id = po.id))
  • Heap Blocks: exact=1,682
  • Buffers: shared hit=65,000
138. 63.916 1,572.670 ↓ 0.0 0 1,682

BitmapAnd (cost=80.08..80.08 rows=1 width=0) (actual time=0.935..0.935 rows=0 loops=1,682)

  • Buffers: shared hit=63,318
139. 37.004 37.004 ↓ 2.0 212 1,682

Bitmap Index Scan on sr_service_prototype_id_idx (cost=0.00..5.21 rows=104 width=0) (actual time=0.022..0.022 rows=212 loops=1,682)

  • Index Cond: (sr_service_1.prototype_id = $44)
  • Buffers: shared hit=6,013
140. 1,471.750 1,471.750 ↓ 2.2 8,802 1,682

Bitmap Index Scan on sr_service_org_id_id_idx (cost=0.00..74.62 rows=4,026 width=0) (actual time=0.875..0.875 rows=8,802 loops=1,682)

  • Index Cond: (sr_service_1.org_id = po.id)
  • Buffers: shared hit=50,166
141. 110.251 661.506 ↓ 0.0 0 110,251

Limit (cost=13.56..13.56 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=110,251)

  • Output: ss1_4.id, mrs_7.id
  • Buffers: shared hit=371,455
142. 220.502 551.255 ↓ 0.0 0 110,251

Sort (cost=13.56..13.56 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=110,251)

  • Output: ss1_4.id, mrs_7.id
  • Sort Key: mrs_7.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=371,455
143. 105.131 330.753 ↓ 0.0 0 110,251

Nested Loop Left Join (cost=0.86..13.55 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=110,251)

  • Output: ss1_4.id, mrs_7.id
  • Buffers: shared hit=371,455
144. 220.502 220.502 ↓ 0.0 0 110,251

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs_7 (cost=0.43..9.09 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=110,251)

  • Output: mrs_7.id, mrs_7.comment, mrs_7.referral_id, mrs_7.service_id, mrs_7.service_prototype_id, mrs_7.aud_who, mrs_7.aud_when, mrs_7.aud_source, mrs_7.aud_who_create, mrs_7.aud_when_create, mrs_7.aud_source_create, mrs_7.biomaterial_id, mrs_7.main_complex, mrs_7.biomaterial_dt, mrs_7.complex_id, mrs_7.building_id, mrs_7.room_id
  • Index Cond: (mrs_7.referral_id = mr.id)
  • Filter: (mrs_7.main_complex IS TRUE)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=366,728
145. 5.120 5.120 ↑ 1.0 1 1,280

Index Only Scan using sr_service_pk on public.sr_service ss1_4 (cost=0.42..4.44 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1,280)

  • Output: ss1_4.id
  • Index Cond: (ss1_4.id = mrs_7.service_id)
  • Heap Fetches: 886
  • Buffers: shared hit=4,727
146. 3.840 24.320 ↑ 1.0 1 1,280

Limit (cost=44.73..44.74 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=1,280)

  • Output: ss1_5.id
  • Buffers: shared hit=17,854
147. 2.560 20.480 ↑ 8.0 1 1,280

HashAggregate (cost=44.73..44.81 rows=8 width=4) (actual time=0.016..0.016 rows=1 loops=1,280)

  • Output: ss1_5.id
  • Group Key: ss1_5.id
  • Buffers: shared hit=17,854
148. 5.202 17.920 ↑ 2.7 3 1,280

Nested Loop Left Join (cost=0.86..44.71 rows=8 width=4) (actual time=0.007..0.014 rows=3 loops=1,280)

  • Output: ss1_5.id
  • Buffers: shared hit=17,854
149. 5.120 5.120 ↑ 2.7 3 1,280

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs_8 (cost=0.43..9.09 rows=8 width=4) (actual time=0.003..0.004 rows=3 loops=1,280)

  • Output: mrs_8.id, mrs_8.comment, mrs_8.referral_id, mrs_8.service_id, mrs_8.service_prototype_id, mrs_8.aud_who, mrs_8.aud_when, mrs_8.aud_source, mrs_8.aud_who_create, mrs_8.aud_when_create, mrs_8.aud_source_create, mrs_8.biomaterial_id, mrs_8.main_complex, mrs_8.biomaterial_dt, mrs_8.complex_id, mrs_8.building_id, mrs_8.room_id
  • Index Cond: (mrs_8.referral_id = mr.id)
  • Filter: ((mrs_8.main_complex IS NOT TRUE) AND (mrs_8.service_id IS NOT NULL))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=5,480
150. 7.598 7.598 ↑ 1.0 1 3,799

Index Only Scan using sr_service_pk on public.sr_service ss1_5 (cost=0.42..4.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=3,799)

  • Output: ss1_5.id
  • Index Cond: (ss1_5.id = mrs_8.service_id)
  • Heap Fetches: 943
  • Buffers: shared hit=12,374
151. 0.560 3.360 ↑ 1.0 1 280

Limit (cost=13.56..13.56 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=280)

  • Output: ss1_6.id, mrs_9.id
  • Buffers: shared hit=2,228
152. 0.560 2.800 ↑ 1.0 1 280

Sort (cost=13.56..13.56 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=280)

  • Output: ss1_6.id, mrs_9.id
  • Sort Key: mrs_9.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2,228
153. 0.840 2.240 ↑ 1.0 1 280

Nested Loop Left Join (cost=0.86..13.55 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=280)

  • Output: ss1_6.id, mrs_9.id
  • Buffers: shared hit=2,228
154. 0.840 0.840 ↑ 1.0 1 280

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs_9 (cost=0.43..9.09 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=280)

  • Output: mrs_9.id, mrs_9.comment, mrs_9.referral_id, mrs_9.service_id, mrs_9.service_prototype_id, mrs_9.aud_who, mrs_9.aud_when, mrs_9.aud_source, mrs_9.aud_who_create, mrs_9.aud_when_create, mrs_9.aud_source_create, mrs_9.biomaterial_id, mrs_9.main_complex, mrs_9.biomaterial_dt, mrs_9.complex_id, mrs_9.building_id, mrs_9.room_id
  • Index Cond: (mrs_9.referral_id = mr.id)
  • Filter: (mrs_9.main_complex IS TRUE)
  • Buffers: shared hit=1,122
155. 0.560 0.560 ↑ 1.0 1 280

Index Only Scan using sr_service_pk on public.sr_service ss1_6 (cost=0.42..4.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=280)

  • Output: ss1_6.id
  • Index Cond: (ss1_6.id = mrs_9.service_id)
  • Heap Fetches: 265
  • Buffers: shared hit=1,106
156. 326.913 762.797 ↑ 1.0 1 108,971

Aggregate (cost=44.73..44.74 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=108,971)

  • Output: count(DISTINCT ss1_7.id)
  • Buffers: shared hit=500,507
157. 96.202 435.884 ↓ 0.0 0 108,971

Nested Loop Left Join (cost=0.86..44.71 rows=8 width=4) (actual time=0.003..0.004 rows=0 loops=108,971)

  • Output: ss1_7.id
  • Buffers: shared hit=500,507
158. 217.942 217.942 ↓ 0.0 0 108,971

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs_10 (cost=0.43..9.09 rows=8 width=4) (actual time=0.002..0.002 rows=0 loops=108,971)

  • Output: mrs_10.id, mrs_10.comment, mrs_10.referral_id, mrs_10.service_id, mrs_10.service_prototype_id, mrs_10.aud_who, mrs_10.aud_when, mrs_10.aud_source, mrs_10.aud_who_create, mrs_10.aud_when_create, mrs_10.aud_source_create, mrs_10.biomaterial_id, mrs_10.main_complex, mrs_10.biomaterial_dt, mrs_10.complex_id, mrs_10.building_id, mrs_10.room_id
  • Index Cond: (mrs_10.referral_id = mr.id)
  • Filter: ((mrs_10.main_complex IS NOT TRUE) AND (mrs_10.service_id IS NOT NULL))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=361,248
159. 121.740 121.740 ↑ 1.0 1 40,580

Index Only Scan using sr_service_pk on public.sr_service ss1_7 (cost=0.42..4.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=40,580)

  • Output: ss1_7.id
  • Index Cond: (ss1_7.id = mrs_10.service_id)
  • Heap Fetches: 17,423
  • Buffers: shared hit=139,259
160. 35.934 197.637 ↑ 1.0 1 17,967

Limit (cost=44.73..44.74 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=17,967)

  • Output: ss1_8.id
  • Buffers: shared hit=135,607
161. 35.934 161.703 ↑ 8.0 1 17,967

HashAggregate (cost=44.73..44.81 rows=8 width=4) (actual time=0.009..0.009 rows=1 loops=17,967)

  • Output: ss1_8.id
  • Group Key: ss1_8.id
  • Buffers: shared hit=135,607
162. 35.876 125.769 ↑ 8.0 1 17,967

Nested Loop Left Join (cost=0.86..44.71 rows=8 width=4) (actual time=0.006..0.007 rows=1 loops=17,967)

  • Output: ss1_8.id
  • Buffers: shared hit=135,607
163. 53.901 53.901 ↑ 8.0 1 17,967

Index Scan using md_referral_service_referral_id_idx on public.md_referral_service mrs_11 (cost=0.43..9.09 rows=8 width=4) (actual time=0.003..0.003 rows=1 loops=17,967)

  • Output: mrs_11.id, mrs_11.comment, mrs_11.referral_id, mrs_11.service_id, mrs_11.service_prototype_id, mrs_11.aud_who, mrs_11.aud_when, mrs_11.aud_source, mrs_11.aud_who_create, mrs_11.aud_when_create, mrs_11.aud_source_create, mrs_11.biomaterial_id, mrs_11.main_complex, mrs_11.biomaterial_dt, mrs_11.complex_id, mrs_11.building_id, mrs_11.room_id
  • Index Cond: (mrs_11.referral_id = mr.id)
  • Filter: ((mrs_11.main_complex IS NOT TRUE) AND (mrs_11.service_id IS NOT NULL))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=72,068
164. 35.992 35.992 ↑ 1.0 1 17,996

Index Only Scan using sr_service_pk on public.sr_service ss1_8 (cost=0.42..4.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=17,996)

  • Output: ss1_8.id
  • Index Cond: (ss1_8.id = mrs_11.service_id)
  • Heap Fetches: 9,464
  • Buffers: shared hit=63,539
Planning time : 38.929 ms
Execution time : 1,854,995.368 ms