explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1r7S

Settings
# exclusive inclusive rows x rows loops node
1. 17.174 67,210.699 ↓ 1.1 20,081 1

Sort (cost=33,285,349.11..33,285,395.49 rows=18,552 width=252) (actual time=67,209.047..67,210.699 rows=20,081 loops=1)

  • Sort Key: rpt_patient_package_view.pres_date
  • Sort Method: external merge Disk: 272kB
2. 3.002 67,193.525 ↓ 1.1 20,081 1

Subquery Scan on rpt_patient_package_view (cost=152,626.47..33,281,811.34 rows=18,552 width=252) (actual time=1,634.068..67,193.525 rows=20,081 loops=1)

3. 1,156.647 67,190.523 ↓ 1.1 20,081 1

Hash Left Join (cost=152,626.47..33,281,625.82 rows=18,552 width=1,354) (actual time=1,634.066..67,190.523 rows=20,081 loops=1)

  • Hash Cond: ((bc.bill_no)::text = (b.bill_no)::text)
4. 6.121 1,372.470 ↓ 1.1 20,081 1

Nested Loop Left Join (cost=81,220.92..231,801.93 rows=18,552 width=1,352) (actual time=1,135.654..1,372.470 rows=20,081 loops=1)

5. 6.320 1,205.701 ↓ 1.1 20,081 1

Hash Left Join (cost=81,220.49..82,381.12 rows=18,552 width=1,348) (actual time=1,135.630..1,205.701 rows=20,081 loops=1)

  • Hash Cond: ((pps.doctor_id)::text = (pd.doctor_id)::text)
6. 5.798 1,199.337 ↓ 1.1 20,081 1

Hash Left Join (cost=81,210.85..82,116.98 rows=18,552 width=1,329) (actual time=1,135.581..1,199.337 rows=20,081 loops=1)

  • Hash Cond: (COALESCE(pr.center_id, isr.center_id) = hcm.center_id)
7. 6.669 1,193.535 ↓ 1.1 20,081 1

Hash Left Join (cost=81,209.78..82,043.56 rows=18,552 width=1,119) (actual time=1,135.573..1,193.535 rows=20,081 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (tm.tpa_id)::text)
8. 4.130 1,186.763 ↓ 1.1 20,081 1

Hash Left Join (cost=81,193.62..81,777.49 rows=18,552 width=1,098) (actual time=1,135.466..1,186.763 rows=20,081 loops=1)

  • Hash Cond: ((pps.patient_id)::text = (isr.incoming_visit_id)::text)
9. 6.088 1,182.625 ↓ 1.1 20,081 1

Hash Join (cost=81,192.23..81,706.35 rows=18,552 width=578) (actual time=1,135.450..1,182.625 rows=20,081 loops=1)

  • Hash Cond: (pps.package_id = pm.package_id)
10. 25.398 1,175.551 ↓ 1.1 20,081 1

Merge Left Join (cost=81,160.35..81,419.38 rows=18,552 width=331) (actual time=1,134.452..1,175.551 rows=20,081 loops=1)

  • Merge Cond: (((pps.prescription_id)::text) = (bac.activity_id)::text)
11. 69.722 1,073.268 ↓ 1.1 19,767 1

Sort (cost=64,932.68..64,979.06 rows=18,552 width=322) (actual time=1,059.844..1,073.268 rows=19,767 loops=1)

  • Sort Key: ((pps.prescription_id)::text)
  • Sort Method: external merge Disk: 1,896kB
12. 5.660 1,003.546 ↓ 1.1 19,767 1

Hash Join (cost=54,412.77..60,824.41 rows=18,552 width=322) (actual time=845.824..1,003.546 rows=19,767 loops=1)

  • Hash Cond: (pps_1.package_id = pm_1.package_id)
13. 13.198 997.543 ↓ 1.1 19,767 1

Hash Join (cost=54,387.89..60,544.44 rows=18,552 width=326) (actual time=845.473..997.543 rows=19,767 loops=1)

  • Hash Cond: (pps.prescription_id = pps_1.prescription_id)
14. 371.928 744.896 ↓ 1.1 19,745 1

Hash Left Join (cost=35,597.50..41,313.44 rows=18,552 width=318) (actual time=606.014..744.896 rows=19,745 loops=1)

  • Hash Cond: ((pps.patient_id)::text = (pr.patient_id)::text)
15. 4.903 4.903 ↓ 1.1 19,745 1

Seq Scan on package_prescribed pps (cost=0.00..756.52 rows=18,552 width=303) (actual time=0.005..4.903 rows=19,745 loops=1)

16. 157.167 368.065 ↑ 1.0 521,138 1

Hash (cost=26,024.00..26,024.00 rows=521,400 width=24) (actual time=368.065..368.065 rows=521,138 loops=1)

  • Buckets: 2,048 Batches: 32 Memory Usage: 961kB
17. 210.898 210.898 ↑ 1.0 521,138 1

Seq Scan on patient_registration pr (cost=0.00..26,024.00 rows=521,400 width=24) (actual time=0.003..210.898 rows=521,138 loops=1)

18. 2.047 239.449 ↓ 1.1 19,767 1

Hash (cost=18,558.49..18,558.49 rows=18,552 width=8) (actual time=239.449..239.449 rows=19,767 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 773kB
19. 18.854 237.402 ↓ 1.1 19,767 1

Merge Left Join (cost=18,299.46..18,558.49 rows=18,552 width=8) (actual time=211.631..237.402 rows=19,767 loops=1)

  • Merge Cond: (((pps_1.prescription_id)::text) = (bac_1.activity_id)::text)
20. 113.049 124.432 ↓ 1.1 19,745 1

Sort (cost=2,071.79..2,118.17 rows=18,552 width=15) (actual time=119.119..124.432 rows=19,745 loops=1)

  • Sort Key: ((pps_1.prescription_id)::text)
  • Sort Method: external merge Disk: 608kB
21. 11.383 11.383 ↓ 1.1 19,745 1

Seq Scan on package_prescribed pps_1 (cost=0.00..756.52 rows=18,552 width=15) (actual time=0.008..11.383 rows=19,745 loops=1)

22. 60.512 94.116 ↓ 2.6 18,707 1

Sort (cost=16,227.67..16,245.64 rows=7,190 width=16) (actual time=92.506..94.116 rows=18,707 loops=1)

  • Sort Key: bac_1.activity_id
  • Sort Method: external sort Disk: 536kB
23. 30.035 33.604 ↓ 2.6 18,707 1

Bitmap Heap Scan on bill_activity_charge bac_1 (cost=136.15..15,767.08 rows=7,190 width=16) (actual time=14.294..33.604 rows=18,707 loops=1)

  • Recheck Cond: ((activity_code)::text = 'PKG'::text)
24. 3.569 3.569 ↓ 5.1 36,467 1

Bitmap Index Scan on bill_activity_charge_activity_code_idx (cost=0.00..134.36 rows=7,190 width=0) (actual time=3.569..3.569 rows=36,467 loops=1)

  • Index Cond: ((activity_code)::text = 'PKG'::text)
25. 0.168 0.343 ↓ 1.0 538 1

Hash (cost=18.28..18.28 rows=528 width=4) (actual time=0.343..0.343 rows=538 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
26. 0.175 0.175 ↓ 1.0 538 1

Seq Scan on packages pm_1 (cost=0.00..18.28 rows=528 width=4) (actual time=0.004..0.175 rows=538 loops=1)

27. 61.119 76.885 ↓ 2.6 19,021 1

Sort (cost=16,227.67..16,245.64 rows=7,190 width=16) (actual time=74.602..76.885 rows=19,021 loops=1)

  • Sort Key: bac.activity_id
  • Sort Method: external sort Disk: 536kB
28. 12.586 15.766 ↓ 2.6 18,707 1

Bitmap Heap Scan on bill_activity_charge bac (cost=136.15..15,767.08 rows=7,190 width=16) (actual time=10.649..15.766 rows=18,707 loops=1)

  • Recheck Cond: ((activity_code)::text = 'PKG'::text)
29. 3.180 3.180 ↓ 5.1 36,467 1

Bitmap Index Scan on bill_activity_charge_activity_code_idx (cost=0.00..134.36 rows=7,190 width=0) (actual time=3.180..3.180 rows=36,467 loops=1)

  • Index Cond: ((activity_code)::text = 'PKG'::text)
30. 0.263 0.986 ↓ 1.0 538 1

Hash (cost=25.29..25.29 rows=528 width=251) (actual time=0.986..0.986 rows=538 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 40kB
31. 0.569 0.723 ↓ 1.0 538 1

Hash Left Join (cost=1.07..25.29 rows=528 width=251) (actual time=0.039..0.723 rows=538 loops=1)

  • Hash Cond: (pm.package_category_id = pcm.package_category_id)
32. 0.133 0.133 ↓ 1.0 538 1

Seq Scan on packages pm (cost=0.00..18.28 rows=528 width=37) (actual time=0.005..0.133 rows=538 loops=1)

33. 0.004 0.021 ↓ 1.3 4 1

Hash (cost=1.03..1.03 rows=3 width=222) (actual time=0.021..0.021 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
34. 0.017 0.017 ↓ 1.3 4 1

Seq Scan on package_category_master pcm (cost=0.00..1.03 rows=3 width=222) (actual time=0.014..0.017 rows=4 loops=1)

35. 0.004 0.008 ↓ 1.1 18 1

Hash (cost=1.17..1.17 rows=17 width=568) (actual time=0.008..0.008 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 2kB
36. 0.004 0.004 ↓ 1.1 18 1

Seq Scan on incoming_sample_registration isr (cost=0.00..1.17 rows=17 width=568) (actual time=0.002..0.004 rows=18 loops=1)

37. 0.047 0.103 ↓ 1.0 364 1

Hash (cost=11.63..11.63 rows=363 width=31) (actual time=0.103..0.103 rows=364 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
38. 0.056 0.056 ↓ 1.0 364 1

Seq Scan on tpa_master tm (cost=0.00..11.63 rows=363 width=31) (actual time=0.003..0.056 rows=364 loops=1)

39. 0.001 0.004 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=222) (actual time=0.004..0.004 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
40. 0.003 0.003 ↑ 1.0 3 1

Seq Scan on hospital_center_master hcm (cost=0.00..1.03 rows=3 width=222) (actual time=0.002..0.003 rows=3 loops=1)

41. 0.019 0.044 ↑ 1.2 167 1

Hash (cost=7.06..7.06 rows=206 width=26) (actual time=0.044..0.044 rows=167 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
42. 0.025 0.025 ↑ 1.2 167 1

Seq Scan on doctors pd (cost=0.00..7.06 rows=206 width=26) (actual time=0.002..0.025 rows=167 loops=1)

43. 160.648 160.648 ↑ 1.0 1 20,081

Index Scan using bill_charge_charge_id_idx on bill_charge bc (cost=0.43..8.04 rows=1 width=22) (actual time=0.008..0.008 rows=1 loops=20,081)

  • Index Cond: ((bac.charge_id)::text = (charge_id)::text)
44. 261.569 462.449 ↑ 1.0 1,081,230 1

Hash (cost=52,553.69..52,553.69 rows=1,084,469 width=10) (actual time=462.449..462.449 rows=1,081,230 loops=1)

  • Buckets: 4,096 Batches: 64 Memory Usage: 719kB
45. 200.880 200.880 ↑ 1.0 1,081,230 1

Seq Scan on bill b (cost=0.00..52,553.69 rows=1,084,469 width=10) (actual time=0.008..200.880 rows=1,081,230 loops=1)

46.          

SubPlan (for Hash Left Join)

47. 40.162 64,198.957 ↑ 1.0 1 20,081

Subquery Scan on foo (cost=1.14..1,776.71 rows=1 width=32) (actual time=3.189..3.197 rows=1 loops=20,081)

48. 200.810 64,158.795 ↑ 1.0 1 20,081

Group (cost=1.14..1,776.69 rows=1 width=4) (actual time=3.187..3.195 rows=1 loops=20,081)

49. 65.446 6,184.948 ↑ 17.9 24 20,081

Nested Loop Left Join (cost=1.14..202.62 rows=429 width=4) (actual time=0.301..0.308 rows=24 loops=20,081)

  • Join Filter: (tp_4.package_ref = pp.prescription_id)
50. 19.985 5,964.057 ↑ 4.3 3 20,081

Nested Loop Left Join (cost=0.71..175.40 rows=13 width=4) (actual time=0.296..0.297 rows=3 loops=20,081)

  • Join Filter: (sp_4.package_ref = pp.prescription_id)
51. 20.081 5,863.652 ↑ 1.0 1 20,081

Nested Loop Left Join (cost=0.29..161.88 rows=1 width=4) (actual time=0.291..0.292 rows=1 loops=20,081)

  • Join Filter: (op_4.package_ref = pp.prescription_id)
52. 40.162 40.162 ↑ 1.0 1 20,081

Index Only Scan using package_prescribed_pkey on package_prescribed pp (cost=0.29..4.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=20,081)

  • Index Cond: (prescription_id = pps_1.prescription_id)
  • Heap Fetches: 1,529
53. 5,803.409 5,803.409 ↓ 0.0 0 20,081

Seq Scan on bed_operation_schedule op_4 (cost=0.00..157.56 rows=1 width=4) (actual time=0.289..0.289 rows=0 loops=20,081)

  • Filter: (package_ref = pps_1.prescription_id)
  • Rows Removed by Filter: 4,686
54. 80.420 80.420 ↑ 6.5 2 20,105

Index Only Scan using services_prescribed_pkg_ref_idx on services_prescribed sp_4 (cost=0.42..13.35 rows=13 width=4) (actual time=0.004..0.004 rows=2 loops=20,105)

  • Index Cond: (package_ref = pps_1.prescription_id)
  • Heap Fetches: 48,553
55. 55.040 155.445 ↑ 3.7 9 51,815

Materialize (cost=0.42..20.88 rows=33 width=4) (actual time=0.002..0.003 rows=9 loops=51,815)

56. 100.405 100.405 ↑ 4.1 8 20,081

Index Only Scan using tests_prescribed_pkg_ref_idx on tests_prescribed tp_4 (cost=0.42..20.71 rows=33 width=4) (actual time=0.004..0.005 rows=8 loops=20,081)

  • Index Cond: (package_ref = pps_1.prescription_id)
  • Heap Fetches: 160,491
57.          

SubPlan (for Group)

58. 20.081 180.729 ↑ 1.0 1 20,081

Aggregate (cost=20.87..20.88 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=20,081)

59. 160.648 160.648 ↑ 4.0 8 20,081

Index Scan using tests_prescribed_pkg_ref_idx on tests_prescribed tp (cost=0.42..20.79 rows=32 width=0) (actual time=0.002..0.008 rows=8 loops=20,081)

  • Index Cond: (package_ref = pp.prescription_id)
  • Filter: ((conducted)::text <> ALL ('{U,X}'::text[]))
  • Rows Removed by Filter: 0
60. 0.000 40.162 ↑ 1.0 1 20,081

Aggregate (cost=13.40..13.41 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=20,081)

61. 40.162 40.162 ↑ 6.0 1 20,081

Index Scan using services_prescribed_pkg_ref_idx on services_prescribed sp (cost=0.42..13.39 rows=6 width=0) (actual time=0.002..0.002 rows=1 loops=20,081)

  • Index Cond: (package_ref = pp.prescription_id)
  • Filter: ((conducted)::text <> ALL ('{U,X}'::text[]))
  • Rows Removed by Filter: 2
62. 20.081 15,703.342 ↑ 1.0 1 20,081

Aggregate (cost=169.28..169.29 rows=1 width=0) (actual time=0.782..0.782 rows=1 loops=20,081)

63. 15,683.261 15,683.261 ↓ 0.0 0 20,081

Seq Scan on bed_operation_schedule op (cost=0.00..169.27 rows=1 width=0) (actual time=0.780..0.781 rows=0 loops=20,081)

  • Filter: ((status <> ALL ('{U,X}'::bpchar[])) AND (package_ref = pp.prescription_id))
  • Rows Removed by Filter: 4,686
64. 0.000 5,783.328 ↑ 1.0 1 20,081

Aggregate (cost=166.02..166.03 rows=1 width=0) (actual time=0.288..0.288 rows=1 loops=20,081)

65. 19.495 5,783.328 ↓ 0.0 0 20,081

Nested Loop (cost=0.42..166.02 rows=1 width=0) (actual time=0.288..0.288 rows=0 loops=20,081)

66. 5,763.247 5,763.247 ↓ 0.0 0 20,081

Seq Scan on bed_operation_schedule opp (cost=0.00..157.56 rows=1 width=4) (actual time=0.287..0.287 rows=0 loops=20,081)

  • Filter: (package_ref = pp.prescription_id)
  • Rows Removed by Filter: 4,686
67. 0.586 0.586 ↓ 0.0 0 293

Index Scan using services_prescribed_operation_ref_idx on services_prescribed spp (cost=0.42..8.45 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=293)

  • Index Cond: (operation_ref = opp.prescribed_id)
  • Filter: ((conducted)::text <> ALL ('{U,X}'::text[]))
68. 0.000 80.324 ↑ 1.0 1 20,081

Aggregate (cost=20.80..20.81 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=20,081)

69. 80.324 80.324 ↑ 3.0 1 20,081

Index Scan using tests_prescribed_pkg_ref_idx on tests_prescribed tp_1 (cost=0.42..20.79 rows=3 width=0) (actual time=0.003..0.004 rows=1 loops=20,081)

  • Index Cond: (package_ref = pp.prescription_id)
  • Filter: ((conducted)::text = ANY ('{N,NRN}'::text[]))
  • Rows Removed by Filter: 7
70. 0.000 40.162 ↑ 1.0 1 20,081

Aggregate (cost=13.39..13.40 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=20,081)

71. 40.162 40.162 ↑ 2.0 1 20,081

Index Scan using services_prescribed_pkg_ref_idx on services_prescribed sp_1 (cost=0.42..13.39 rows=2 width=0) (actual time=0.002..0.002 rows=1 loops=20,081)

  • Index Cond: (package_ref = pp.prescription_id)
  • Filter: ((conducted)::text = 'N'::text)
  • Rows Removed by Filter: 2
72. 20.081 5,903.814 ↑ 1.0 1 20,081

Aggregate (cost=169.28..169.29 rows=1 width=0) (actual time=0.294..0.294 rows=1 loops=20,081)

73. 5,883.733 5,883.733 ↓ 0.0 0 20,081

Seq Scan on bed_operation_schedule op_1 (cost=0.00..169.27 rows=1 width=0) (actual time=0.293..0.293 rows=0 loops=20,081)

  • Filter: ((package_ref = pp.prescription_id) AND (status = 'N'::bpchar))
  • Rows Removed by Filter: 4,686
74. 0.000 5,863.652 ↑ 1.0 1 20,081

Aggregate (cost=166.02..166.03 rows=1 width=0) (actual time=0.292..0.292 rows=1 loops=20,081)

75. 19.788 5,863.652 ↓ 0.0 0 20,081

Nested Loop (cost=0.42..166.02 rows=1 width=0) (actual time=0.292..0.292 rows=0 loops=20,081)

76. 5,843.571 5,843.571 ↓ 0.0 0 20,081

Seq Scan on bed_operation_schedule opp_1 (cost=0.00..157.56 rows=1 width=4) (actual time=0.291..0.291 rows=0 loops=20,081)

  • Filter: (package_ref = pp.prescription_id)
  • Rows Removed by Filter: 4,686
77. 0.293 0.293 ↓ 0.0 0 293

Index Scan using services_prescribed_operation_ref_idx on services_prescribed spp_1 (cost=0.42..8.45 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=293)

  • Index Cond: (operation_ref = opp_1.prescribed_id)
  • Filter: ((conducted)::text = 'N'::text)
78. 0.000 80.324 ↑ 1.0 1 20,081

Aggregate (cost=20.80..20.81 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=20,081)

79. 80.324 80.324 ↓ 0.0 0 20,081

Index Scan using tests_prescribed_pkg_ref_idx on tests_prescribed tp_2 (cost=0.42..20.79 rows=1 width=0) (actual time=0.003..0.004 rows=0 loops=20,081)

  • Index Cond: (package_ref = pp.prescription_id)
  • Filter: ((conducted)::text = ANY ('{P,RP}'::text[]))
  • Rows Removed by Filter: 8
80. 0.000 40.162 ↑ 1.0 1 20,081

Aggregate (cost=13.39..13.40 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=20,081)

81. 40.162 40.162 ↓ 0.0 0 20,081

Index Scan using services_prescribed_pkg_ref_idx on services_prescribed sp_2 (cost=0.42..13.39 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=20,081)

  • Index Cond: (package_ref = pp.prescription_id)
  • Filter: ((conducted)::text = 'P'::text)
  • Rows Removed by Filter: 2
82. 20.081 6,084.543 ↑ 1.0 1 20,081

Aggregate (cost=169.28..169.29 rows=1 width=0) (actual time=0.303..0.303 rows=1 loops=20,081)

83. 6,064.462 6,064.462 ↓ 0.0 0 20,081

Seq Scan on bed_operation_schedule op_2 (cost=0.00..169.27 rows=1 width=0) (actual time=0.302..0.302 rows=0 loops=20,081)

  • Filter: ((package_ref = pp.prescription_id) AND (status = 'P'::bpchar))
  • Rows Removed by Filter: 4,686
84. 0.000 6,064.462 ↑ 1.0 1 20,081

Aggregate (cost=166.02..166.03 rows=1 width=0) (actual time=0.302..0.302 rows=1 loops=20,081)

85. 0.000 6,064.462 ↓ 0.0 0 20,081

Nested Loop (cost=0.42..166.02 rows=1 width=0) (actual time=0.302..0.302 rows=0 loops=20,081)

86. 6,064.462 6,064.462 ↓ 0.0 0 20,081

Seq Scan on bed_operation_schedule opp_2 (cost=0.00..157.56 rows=1 width=4) (actual time=0.301..0.302 rows=0 loops=20,081)

  • Filter: (package_ref = pp.prescription_id)
  • Rows Removed by Filter: 4,686
87. 0.293 0.293 ↓ 0.0 0 293

Index Scan using services_prescribed_operation_ref_idx on services_prescribed spp_2 (cost=0.42..8.45 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=293)

  • Index Cond: (operation_ref = opp_2.prescribed_id)
  • Filter: ((conducted)::text = 'P'::text)
88. 20.081 140.567 ↑ 1.0 1 20,081

Aggregate (cost=107.24..107.25 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=20,081)

89. 59.527 120.486 ↑ 2.7 6 20,081

Hash Join (cost=86.25..107.20 rows=16 width=0) (actual time=0.003..0.006 rows=6 loops=20,081)

  • Hash Cond: ((tp_3.test_id)::text = (diagnostics.test_id)::text)
  • Join Filter: CASE WHEN diagnostics.results_entry_applicable THEN ((tp_3.conducted)::text = 'S'::text) ELSE ((tp_3.conducted)::text = 'CRN'::text) END
  • Rows Removed by Join Filter: 2
90. 60.243 60.243 ↑ 4.1 8 20,081

Index Scan using tests_prescribed_pkg_ref_idx on tests_prescribed tp_3 (cost=0.42..20.71 rows=33 width=10) (actual time=0.001..0.003 rows=8 loops=20,081)

  • Index Cond: (package_ref = pp.prescription_id)
91. 0.228 0.716 ↓ 1.0 1,958 1

Hash (cost=61.48..61.48 rows=1,948 width=9) (actual time=0.716..0.716 rows=1,958 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 79kB
92. 0.488 0.488 ↓ 1.0 1,958 1

Seq Scan on diagnostics (cost=0.00..61.48 rows=1,948 width=9) (actual time=0.002..0.488 rows=1,958 loops=1)

93. 0.000 40.162 ↑ 1.0 1 20,081

Aggregate (cost=13.40..13.41 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=20,081)

94. 40.162 40.162 ↓ 0.0 0 20,081

Index Scan using services_prescribed_pkg_ref_idx on services_prescribed sp_3 (cost=0.42..13.39 rows=5 width=0) (actual time=0.002..0.002 rows=0 loops=20,081)

  • Index Cond: (package_ref = pp.prescription_id)
  • Filter: ((conducted)::text = 'C'::text)
  • Rows Removed by Filter: 2
95. 20.081 5,783.328 ↑ 1.0 1 20,081

Aggregate (cost=169.28..169.29 rows=1 width=0) (actual time=0.288..0.288 rows=1 loops=20,081)

96. 5,763.247 5,763.247 ↓ 0.0 0 20,081

Seq Scan on bed_operation_schedule op_3 (cost=0.00..169.27 rows=1 width=0) (actual time=0.287..0.287 rows=0 loops=20,081)

  • Filter: ((package_ref = pp.prescription_id) AND (status = 'C'::bpchar))
  • Rows Removed by Filter: 4,686
97. 0.000 5,903.814 ↑ 1.0 1 20,081

Aggregate (cost=166.02..166.03 rows=1 width=0) (actual time=0.294..0.294 rows=1 loops=20,081)

98. 19.788 5,903.814 ↓ 0.0 0 20,081

Nested Loop (cost=0.42..166.02 rows=1 width=0) (actual time=0.294..0.294 rows=0 loops=20,081)

99. 5,883.733 5,883.733 ↓ 0.0 0 20,081

Seq Scan on bed_operation_schedule opp_3 (cost=0.00..157.56 rows=1 width=4) (actual time=0.293..0.293 rows=0 loops=20,081)

  • Filter: (package_ref = pp.prescription_id)
  • Rows Removed by Filter: 4,686
100. 0.293 0.293 ↓ 0.0 0 293

Index Scan using services_prescribed_operation_ref_idx on services_prescribed spp_3 (cost=0.42..8.45 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=293)

  • Index Cond: (operation_ref = opp_3.prescribed_id)
  • Filter: ((conducted)::text = 'C'::text)
101. 0.000 40.162 ↑ 1.0 1 20,081

Aggregate (cost=8.31..8.32 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=20,081)

102. 40.162 40.162 ↓ 0.0 0 20,081

Index Scan using package_prescribed_pkey on package_prescribed ppp (cost=0.29..8.31 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=20,081)

  • Index Cond: (prescription_id = pp.prescription_id)
  • Filter: (status = 'X'::bpchar)
  • Rows Removed by Filter: 1
Total runtime : 67,212.855 ms