explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jbKc

Settings
# exclusive inclusive rows x rows loops node
1. 5.191 1,334.813 ↓ 15.0 15 1

Result (cost=32.84..47,962.75 rows=1 width=3,922) (actual time=139.709..1,334.813 rows=15 loops=1)

  • Buffers: shared hit=1,219,683
2. 0.071 0.412 ↓ 15.0 15 1

Sort (cost=32.84..32.85 rows=1 width=2,588) (actual time=0.400..0.412 rows=15 loops=1)

  • Sort Key: v.start_time, v.end_time, v.cln_visit_id
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=115
3. 0.026 0.341 ↓ 15.0 15 1

Nested Loop Left Join (cost=9.17..32.83 rows=1 width=2,588) (actual time=0.138..0.341 rows=15 loops=1)

  • Buffers: shared hit=109
4. 0.012 0.285 ↓ 15.0 15 1

Nested Loop (cost=4.87..20.79 rows=1 width=2,584) (actual time=0.114..0.285 rows=15 loops=1)

  • Buffers: shared hit=79
5. 0.098 0.138 ↓ 15.0 15 1

Bitmap Heap Scan on cln_visit v (cost=4.45..12.35 rows=1 width=1,822) (actual time=0.073..0.138 rows=15 loops=1)

  • Recheck Cond: ((ptf_clinic_id = 1) AND (visit_datetime < (CURRENT_DATE + 1)) AND (visit_datetime >= CURRENT_DATE))
  • Filter: (((visit_type)::text <> ALL ('{VTY4,VTY6,VTY7,VTY3,VTY8}'::text[])) AND ((visit_status)::text = ANY ('{VST3,VST4,VST5,VST6,VST7,VST8,VST9,VST10}'::text[])))
  • Rows Removed by Filter: 5
  • Heap Blocks: exact=10
  • Buffers: shared hit=19
6. 0.040 0.040 ↓ 10.5 21 1

Bitmap Index Scan on ndx_vst_clinicid_vstdt (cost=0.00..4.45 rows=2 width=0) (actual time=0.040..0.040 rows=21 loops=1)

  • Index Cond: ((ptf_clinic_id = 1) AND (visit_datetime < (CURRENT_DATE + 1)) AND (visit_datetime >= CURRENT_DATE))
  • Buffers: shared hit=9
7. 0.135 0.135 ↑ 1.0 1 15

Index Scan using pk_cln_patient on cln_patient p (cost=0.42..8.44 rows=1 width=770) (actual time=0.009..0.009 rows=1 loops=15)

  • Index Cond: (cln_patient_id = v.cln_patient_id)
  • Buffers: shared hit=60
8. 0.015 0.030 ↓ 0.0 0 15

Bitmap Heap Scan on cln_visitops cvt (cost=4.30..12.02 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=15)

  • Recheck Cond: (v.cln_visit_id = cln_visit_id)
  • Filter: (defunct_ind = 'N'::bpchar)
  • Buffers: shared hit=30
9. 0.015 0.015 ↓ 0.0 0 15

Bitmap Index Scan on ndx_vstops_vst_id (cost=0.00..4.30 rows=3 width=0) (actual time=0.001..0.001 rows=0 loops=15)

  • Index Cond: (v.cln_visit_id = cln_visit_id)
  • Buffers: shared hit=30
10.          

SubPlan (for Nested Loop Left Join)

11. 0.000 0.000 ↑ 1.0 1 15

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=15)

12.          

SubPlan (for Result)

13. 0.105 0.105 ↑ 1.0 1 15

Index Scan using pk_cln_specialty on cln_specialty sp (cost=0.28..8.30 rows=1 width=418) (actual time=0.006..0.007 rows=1 loops=15)

  • Index Cond: (cln_specialty_id = v.visit_specialty_id)
  • Filter: (defunct_ind = 'N'::bpchar)
  • Buffers: shared hit=45
14. 0.135 0.135 ↑ 1.0 1 15

Index Scan using pk_cln_doctor on cln_doctor d (cost=0.29..8.30 rows=1 width=418) (actual time=0.008..0.009 rows=1 loops=15)

  • Index Cond: (cln_doctor_id = v.see_doctor_id)
  • Buffers: shared hit=45
15. 0.030 0.030 ↑ 1.0 1 15

Index Scan using pk_cln_doctor on cln_doctor d_1 (cost=0.29..8.30 rows=1 width=418) (actual time=0.001..0.002 rows=1 loops=15)

  • Index Cond: (cln_doctor_id = v.booking_doctor_id)
  • Buffers: shared hit=45
16. 0.030 0.105 ↓ 0.0 0 15

Nested Loop Left Join (cost=5.46..242.36 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=15)

  • Buffers: shared hit=30
17. 0.015 0.075 ↓ 0.0 0 15

Bitmap Heap Scan on cln_visitdiagnosis vd (cost=5.03..233.91 rows=1 width=426) (actual time=0.005..0.005 rows=0 loops=15)

  • Recheck Cond: (cln_visit_id = v.cln_visit_id)
  • Filter: (defunct_ind = 'N'::bpchar)
  • Buffers: shared hit=30
18. 0.060 0.060 ↓ 0.0 0 15

Bitmap Index Scan on ndx_vstdiag_vstid (cost=0.00..5.03 rows=99 width=0) (actual time=0.004..0.004 rows=0 loops=15)

  • Index Cond: (cln_visit_id = v.cln_visit_id)
  • Buffers: shared hit=30
19. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_diagnosis on cln_diagnosis diag (cost=0.43..8.45 rows=1 width=426) (never executed)

  • Index Cond: (vd.cln_diagnosis_id = cln_diagnosis_id)
20. 0.045 20.745 ↑ 1.0 1 15

Aggregate (cost=7,190.15..7,190.16 rows=1 width=8) (actual time=1.383..1.383 rows=1 loops=15)

  • Buffers: shared hit=10,026
21. 1.847 20.700 ↓ 3.0 3 15

Nested Loop (cost=40.71..7,190.15 rows=1 width=0) (actual time=1.312..1.380 rows=3 loops=15)

  • Buffers: shared hit=10,026
22. 2.820 8.010 ↓ 51.5 103 15

Bitmap Heap Scan on cln_visit v2 (cost=14.10..21.96 rows=2 width=8) (actual time=0.360..0.534 rows=103 loops=15)

  • Recheck Cond: ((cln_patient_id = v.cln_patient_id) AND (ptf_clinic_id = 1))
  • Filter: ((visit_type)::text <> ALL ('{VTY4,VTY6,VTY7,VTY3,VTY8}'::text[]))
  • Rows Removed by Filter: 17
  • Heap Blocks: exact=1,190
  • Buffers: shared hit=1,492
23. 0.075 5.190 ↓ 0.0 0 15

BitmapAnd (cost=14.10..14.10 rows=2 width=0) (actual time=0.346..0.346 rows=0 loops=15)

  • Buffers: shared hit=302
24. 0.225 0.225 ↑ 2.8 125 15

Bitmap Index Scan on ndx_vst_ptid (cost=0.00..6.93 rows=351 width=0) (actual time=0.015..0.015 rows=125 loops=15)

  • Index Cond: (cln_patient_id = v.cln_patient_id)
  • Buffers: shared hit=32
25. 4.890 4.890 ↓ 16.8 5,891 15

Bitmap Index Scan on ndx_vst_clinicid (cost=0.00..6.93 rows=351 width=0) (actual time=0.326..0.326 rows=5,891 loops=15)

  • Index Cond: (ptf_clinic_id = 1)
  • Buffers: shared hit=270
26. 6.196 10.843 ↓ 0.0 0 1,549

Bitmap Heap Scan on cln_visitcharge vc (cost=26.61..3,584.08 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1,549)

  • Recheck Cond: (cln_visit_id = v2.cln_visit_id)
  • Filter: ((payment_ind = 'N'::bpchar) AND ((charge_status)::text = 'CST1'::text))
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=3,873
  • Buffers: shared hit=8,534
27. 4.647 4.647 ↑ 339.5 4 1,549

Bitmap Index Scan on ndx_vstcharge_vstid (cost=0.00..26.61 rows=1,358 width=0) (actual time=0.003..0.003 rows=4 loops=1,549)

  • Index Cond: (cln_visit_id = v2.cln_visit_id)
  • Buffers: shared hit=4,661
28. 0.060 34.560 ↑ 1.0 1 15

Aggregate (cost=9,917.69..9,917.70 rows=1 width=8) (actual time=2.304..2.304 rows=1 loops=15)

  • Buffers: shared hit=28,343
29. 0.048 34.500 ↓ 7.0 7 15

Nested Loop (cost=9,879.66..9,917.69 rows=1 width=0) (actual time=1.897..2.300 rows=7 loops=15)

  • Buffers: shared hit=28,343
30. 0.675 34.020 ↓ 7.0 7 15

Nested Loop (cost=9,879.24..9,907.24 rows=1 width=8) (actual time=1.890..2.268 rows=7 loops=15)

  • Buffers: shared hit=27,907
31. 1.201 23.445 ↓ 330.0 330 15

Hash Join (cost=9,878.95..9,879.50 rows=1 width=16) (actual time=1.478..1.563 rows=330 loops=15)

  • Hash Cond: (csitem.cln_item_id = i.cln_item_id)
  • Buffers: shared hit=12,580
32. 0.158 6.825 ↓ 2.2 51 15

HashAggregate (cost=9,812.61..9,812.84 rows=23 width=8) (actual time=0.447..0.455 rows=51 loops=15)

  • Group Key: csitem.cln_item_id
  • Buffers: shared hit=978
33. 0.008 6.667 ↓ 2.2 51 1

Append (cost=48.36..9,812.55 rows=23 width=8) (actual time=5.595..6.667 rows=51 loops=1)

  • Buffers: shared hit=978
34. 0.004 0.984 ↓ 0.0 0 1

Bitmap Heap Scan on cln_storeitem csitem (cost=48.36..75.30 rows=1 width=8) (actual time=0.984..0.984 rows=0 loops=1)

  • Recheck Cond: ((ptf_clinic_id = 1) AND (cln_store_id = ANY ('{639173185180001,640069063820001,636698337260001}'::bigint[])))
  • Filter: (defunct_ind = 'N'::bpchar)
  • Buffers: shared hit=61
35. 0.031 0.980 ↓ 0.0 0 1

BitmapAnd (cost=48.36..48.36 rows=7 width=0) (actual time=0.980..0.980 rows=0 loops=1)

  • Buffers: shared hit=61
36. 0.927 0.927 ↓ 39.4 19,603 1

Bitmap Index Scan on ndx_storeitem_clinicid (cost=0.00..12.03 rows=498 width=0) (actual time=0.927..0.927 rows=19,603 loops=1)

  • Index Cond: (ptf_clinic_id = 1)
  • Buffers: shared hit=55
37. 0.022 0.022 ↑ 114.9 13 1

Bitmap Index Scan on ndx_storeitem_storeid (cost=0.00..36.08 rows=1,494 width=0) (actual time=0.022..0.022 rows=13 loops=1)

  • Index Cond: (cln_store_id = ANY ('{639173185180001,640069063820001,636698337260001}'::bigint[]))
  • Buffers: shared hit=6
38. 5.175 5.675 ↓ 2.3 51 1

Bitmap Heap Scan on cln_itemclinic thisic (cost=81.09..9,736.91 rows=22 width=8) (actual time=4.610..5.675 rows=51 loops=1)

  • Recheck Cond: (ptf_clinic_id = 1)
  • Filter: (entered_ind = 'Y'::bpchar)
  • Rows Removed by Filter: 10,900
  • Heap Blocks: exact=885
  • Buffers: shared hit=917
39. 0.500 0.500 ↓ 2.5 10,951 1

Bitmap Index Scan on ndx_clncitm_clncid (cost=0.00..81.08 rows=4,354 width=0) (actual time=0.500..0.500 rows=10,951 loops=1)

  • Index Cond: (ptf_clinic_id = 1)
  • Buffers: shared hit=32
40. 0.493 15.419 ↓ 2,578.0 2,578 1

Hash (cost=66.33..66.33 rows=1 width=32) (actual time=15.419..15.419 rows=2,578 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 194kB
  • Buffers: shared hit=11,602
41. 0.123 14.926 ↓ 2,578.0 2,578 1

Nested Loop (cost=42.57..66.33 rows=1 width=32) (actual time=4.934..14.926 rows=2,578 loops=1)

  • Buffers: shared hit=11,602
42. 4.876 9.645 ↓ 2,579.0 2,579 1

Bitmap Heap Scan on cln_visitdrugdispense vdd (cost=42.14..57.88 rows=1 width=24) (actual time=4.911..9.645 rows=2,579 loops=1)

  • Recheck Cond: (((dispense_status)::text = 'DST0'::text) AND (ptf_clinic_id = 1))
  • Filter: ((material_type)::text = 'MTY1'::text)
  • Rows Removed by Filter: 102
  • Heap Blocks: exact=1,089
  • Buffers: shared hit=1,286
43. 0.226 4.769 ↓ 0.0 0 1

BitmapAnd (cost=42.14..42.14 rows=4 width=0) (actual time=4.769..4.769 rows=0 loops=1)

  • Buffers: shared hit=197
44. 3.704 3.704 ↓ 62.6 54,440 1

Bitmap Index Scan on ndx_dd_ddstatus (cost=0.00..18.95 rows=870 width=0) (actual time=3.704..3.704 rows=54,440 loops=1)

  • Index Cond: ((dispense_status)::text = 'DST0'::text)
  • Buffers: shared hit=151
45. 0.839 0.839 ↓ 13.1 11,362 1

Bitmap Index Scan on ndx_dd_clinicid_txndt (cost=0.00..22.95 rows=870 width=0) (actual time=0.839..0.839 rows=11,362 loops=1)

  • Index Cond: (ptf_clinic_id = 1)
  • Buffers: shared hit=46
46. 5.158 5.158 ↑ 1.0 1 2,579

Index Scan using pk_cln_item on cln_item i (cost=0.42..8.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,579)

  • Index Cond: (cln_item_id = vdd.cln_item_id)
  • Filter: (material_item_ind = 'Y'::bpchar)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=10,316
47. 9.684 9.900 ↓ 0.0 0 4,950

Index Scan using pk_cln_visit on cln_visit vt (cost=0.29..16.76 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=4,950)

  • Index Cond: (cln_visit_id = vdd.cln_visit_id)
  • Filter: ((cln_patient_id = v.cln_patient_id) AND ((visit_type)::text <> ALL ('{VTY4,VTY6,VTY7,VTY3,VTY8}'::text[])) AND ((SubPlan 7) = 'N'::bpchar))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=15,327
48.          

SubPlan (for Index Scan)

49. 0.216 0.216 ↑ 1.0 1 108

Index Scan using pk_cln_patient on cln_patient (cost=0.42..8.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=108)

  • Index Cond: (cln_patient_id = vt.cln_patient_id)
  • Buffers: shared hit=432
50. 0.432 0.432 ↑ 1.0 1 108

Index Scan using pk_cln_visitcharge on cln_visitcharge vc_1 (cost=0.42..8.45 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=108)

  • Index Cond: (cln_visitcharge_id = vdd.cln_visitcharge_id)
  • Filter: ((payment_ind = 'Y'::bpchar) AND ((charge_status)::text = 'CST1'::text))
  • Buffers: shared hit=436
51. 0.015 10.830 ↑ 1.0 1 15

Aggregate (cost=9,917.69..9,917.70 rows=1 width=8) (actual time=0.722..0.722 rows=1 loops=15)

  • Buffers: shared hit=3,077
52. 0.030 10.815 ↓ 0.0 0 15

Nested Loop (cost=9,879.66..9,917.69 rows=1 width=0) (actual time=0.721..0.721 rows=0 loops=15)

  • Buffers: shared hit=3,077
53. 0.045 10.785 ↓ 0.0 0 15

Nested Loop (cost=9,879.24..9,907.24 rows=1 width=8) (actual time=0.719..0.719 rows=0 loops=15)

  • Buffers: shared hit=3,077
54. 0.184 10.470 ↓ 9.0 9 15

Hash Join (cost=9,878.95..9,879.50 rows=1 width=16) (actual time=0.686..0.698 rows=9 loops=15)

  • Hash Cond: (csitem_1.cln_item_id = i_1.cln_item_id)
  • Buffers: shared hit=2,672
55. 0.114 4.080 ↓ 2.2 51 15

HashAggregate (cost=9,812.61..9,812.84 rows=23 width=8) (actual time=0.266..0.272 rows=51 loops=15)

  • Group Key: csitem_1.cln_item_id
  • Buffers: shared hit=978
56. 0.006 3.966 ↓ 2.2 51 1

Append (cost=48.36..9,812.55 rows=23 width=8) (actual time=3.614..3.966 rows=51 loops=1)

  • Buffers: shared hit=978
57. 0.003 0.760 ↓ 0.0 0 1

Bitmap Heap Scan on cln_storeitem csitem_1 (cost=48.36..75.30 rows=1 width=8) (actual time=0.760..0.760 rows=0 loops=1)

  • Recheck Cond: ((ptf_clinic_id = 1) AND (cln_store_id = ANY ('{639173185180001,640069063820001,636698337260001}'::bigint[])))
  • Filter: (defunct_ind = 'N'::bpchar)
  • Buffers: shared hit=61
58. 0.031 0.757 ↓ 0.0 0 1

BitmapAnd (cost=48.36..48.36 rows=7 width=0) (actual time=0.757..0.757 rows=0 loops=1)

  • Buffers: shared hit=61
59. 0.718 0.718 ↓ 39.4 19,603 1

Bitmap Index Scan on ndx_storeitem_clinicid (cost=0.00..12.03 rows=498 width=0) (actual time=0.718..0.718 rows=19,603 loops=1)

  • Index Cond: (ptf_clinic_id = 1)
  • Buffers: shared hit=55
60. 0.008 0.008 ↑ 114.9 13 1

Bitmap Index Scan on ndx_storeitem_storeid (cost=0.00..36.08 rows=1,494 width=0) (actual time=0.008..0.008 rows=13 loops=1)

  • Index Cond: (cln_store_id = ANY ('{639173185180001,640069063820001,636698337260001}'::bigint[]))
  • Buffers: shared hit=6
61. 2.793 3.200 ↓ 2.3 51 1

Bitmap Heap Scan on cln_itemclinic thisic_1 (cost=81.09..9,736.91 rows=22 width=8) (actual time=2.853..3.200 rows=51 loops=1)

  • Recheck Cond: (ptf_clinic_id = 1)
  • Filter: (entered_ind = 'Y'::bpchar)
  • Rows Removed by Filter: 10,900
  • Heap Blocks: exact=885
  • Buffers: shared hit=917
62. 0.407 0.407 ↓ 2.5 10,951 1

Bitmap Index Scan on ndx_clncitm_clncid (cost=0.00..81.08 rows=4,354 width=0) (actual time=0.407..0.407 rows=10,951 loops=1)

  • Index Cond: (ptf_clinic_id = 1)
  • Buffers: shared hit=32
63. 0.020 6.206 ↓ 94.0 94 1

Hash (cost=66.33..66.33 rows=1 width=32) (actual time=6.206..6.206 rows=94 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=1,694
64. 0.072 6.186 ↓ 94.0 94 1

Nested Loop (cost=42.57..66.33 rows=1 width=32) (actual time=4.297..6.186 rows=94 loops=1)

  • Buffers: shared hit=1,694
65. 1.722 5.808 ↓ 102.0 102 1

Bitmap Heap Scan on cln_visitdrugdispense vdd_1 (cost=42.14..57.88 rows=1 width=24) (actual time=4.276..5.808 rows=102 loops=1)

  • Recheck Cond: (((dispense_status)::text = 'DST0'::text) AND (ptf_clinic_id = 1))
  • Filter: ((material_type)::text = 'MTY2'::text)
  • Rows Removed by Filter: 2,579
  • Heap Blocks: exact=1,089
  • Buffers: shared hit=1,286
66. 0.216 4.086 ↓ 0.0 0 1

BitmapAnd (cost=42.14..42.14 rows=4 width=0) (actual time=4.086..4.086 rows=0 loops=1)

  • Buffers: shared hit=197
67. 3.220 3.220 ↓ 62.6 54,440 1

Bitmap Index Scan on ndx_dd_ddstatus (cost=0.00..18.95 rows=870 width=0) (actual time=3.220..3.220 rows=54,440 loops=1)

  • Index Cond: ((dispense_status)::text = 'DST0'::text)
  • Buffers: shared hit=151
68. 0.650 0.650 ↓ 13.1 11,362 1

Bitmap Index Scan on ndx_dd_clinicid_txndt (cost=0.00..22.95 rows=870 width=0) (actual time=0.650..0.650 rows=11,362 loops=1)

  • Index Cond: (ptf_clinic_id = 1)
  • Buffers: shared hit=46
69. 0.306 0.306 ↑ 1.0 1 102

Index Scan using pk_cln_item on cln_item i_1 (cost=0.42..8.45 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=102)

  • Index Cond: (cln_item_id = vdd_1.cln_item_id)
  • Filter: (material_item_ind = 'Y'::bpchar)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=408
70. 0.270 0.270 ↓ 0.0 0 135

Index Scan using pk_cln_visit on cln_visit vt_1 (cost=0.29..16.76 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=135)

  • Index Cond: (cln_visit_id = vdd_1.cln_visit_id)
  • Filter: ((cln_patient_id = v.cln_patient_id) AND ((visit_type)::text <> ALL ('{VTY4,VTY6,VTY7,VTY3,VTY8}'::text[])) AND ((SubPlan 9) = 'N'::bpchar))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=405
71.          

SubPlan (for Index Scan)

72. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_patient on cln_patient cln_patient_1 (cost=0.42..8.44 rows=1 width=8) (never executed)

  • Index Cond: (cln_patient_id = vt_1.cln_patient_id)
73. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_visitcharge on cln_visitcharge vc_2 (cost=0.42..8.45 rows=1 width=8) (never executed)

  • Index Cond: (cln_visitcharge_id = vdd_1.cln_visitcharge_id)
  • Filter: ((payment_ind = 'Y'::bpchar) AND ((charge_status)::text = 'CST1'::text))
74. 0.150 1,261.860 ↑ 1.0 1 15

Aggregate (cost=11,142.92..11,142.93 rows=1 width=8) (actual time=84.124..84.124 rows=1 loops=15)

  • Buffers: shared hit=1,177,109
75. 4.335 1,261.710 ↓ 13.0 13 15

Nested Loop (cost=39.76..11,142.91 rows=1 width=0) (actual time=43.222..84.114 rows=13 loops=15)

  • Buffers: shared hit=1,177,109
76. 29.280 1,237.755 ↓ 654.0 654 15

Nested Loop (cost=39.47..11,134.55 rows=1 width=8) (actual time=0.629..82.517 rows=654 loops=15)

  • Join Filter: (oe.cln_visitorderentry_id = pe.cln_visitorderentry_id)
  • Buffers: shared hit=1,147,515
77. 12.495 741.750 ↓ 4,445.0 4,445 15

Nested Loop Semi Join (cost=39.18..5,049.41 rows=1 width=24) (actual time=0.608..49.450 rows=4,445 loops=15)

  • Buffers: shared hit=953,340
78. 78.735 85.215 ↓ 7,156.0 7,156 15

Bitmap Heap Scan on cln_visitorderentry oe (cost=13.54..1,665.32 rows=1 width=16) (actual time=0.589..5.681 rows=7,156 loops=15)

  • Recheck Cond: (ptf_clinic_id = 1)
  • Filter: (((order_status)::text = 'OST1'::text) AND (lab_ind = 'N'::bpchar))
  • Rows Removed by Filter: 1,992
  • Heap Blocks: exact=23,850
  • Buffers: shared hit=24,255
79. 6.480 6.480 ↓ 13.4 9,148 15

Bitmap Index Scan on ndx_vstoe_clinicid (cost=0.00..13.54 rows=683 width=0) (actual time=0.432..0.432 rows=9,148 loops=15)

  • Index Cond: (ptf_clinic_id = 1)
  • Buffers: shared hit=405
80. 185.790 644.040 ↑ 1.0 1 107,340

Nested Loop (cost=25.63..3,384.09 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=107,340)

  • Buffers: shared hit=929,085
81. 107.340 214.680 ↑ 6.0 1 107,340

Bitmap Heap Scan on cln_visitorderentryitem oei (cost=25.21..2,980.36 rows=6 width=16) (actual time=0.002..0.002 rows=1 loops=107,340)

  • Recheck Cond: (cln_visitorderentry_id = oe.cln_visitorderentry_id)
  • Filter: ((order_status)::text = 'OST1'::text)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=110,190
  • Buffers: shared hit=432,915
82. 107.340 107.340 ↑ 1,172.0 1 107,340

Bitmap Index Scan on ndx_vstoei_vstoeid (cost=0.00..25.21 rows=1,172 width=0) (actual time=0.001..0.001 rows=1 loops=107,340)

  • Index Cond: (cln_visitorderentry_id = oe.cln_visitorderentry_id)
  • Buffers: shared hit=322,725
83. 243.570 243.570 ↑ 1.0 1 121,785

Index Scan using ndx_vstcharge_oeiid on cln_visitcharge vc_3 (cost=0.42..67.28 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=121,785)

  • Index Cond: (cln_visitorderentryitem_id = oei.cln_visitorderentryitem_id)
  • Filter: ((payment_ind = 'Y'::bpchar) AND ((charge_status)::text = 'CST1'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=496,170
84. 84.135 466.725 ↓ 0.0 0 66,675

Index Scan using ndx_pe_oeid on cln_visitplannedexecution pe (cost=0.29..6,085.12 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=66,675)

  • Index Cond: (cln_visitorderentry_id = oei.cln_visitorderentry_id)
  • Filter: (((end_date IS NULL) OR (end_date > '2020-10-14 23:59:59'::timestamp without time zone)) AND ((order_status)::text = 'OST1'::text) AND (completion_ind = 'N'::bpchar) AND (qty_per_day > ((SubPlan 11))::numeric))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=194,175
85.          

SubPlan (for Index Scan)

86. 9.810 382.590 ↑ 1.0 1 9,810

Aggregate (cost=17.05..17.06 rows=1 width=8) (actual time=0.039..0.039 rows=1 loops=9,810)

  • Buffers: shared hit=38,745
87. 0.000 372.780 ↓ 0.0 0 9,810

Bitmap Heap Scan on cln_visitorderstatusjournal osj (cost=13.03..17.05 rows=1 width=0) (actual time=0.038..0.038 rows=0 loops=9,810)

  • Recheck Cond: ((cln_visitplannedexecution_id = pe.cln_visitplannedexecution_id) AND (execed_datetime >= '2020-10-14 00:00:00'::timestamp without time zone) AND (execed_datetime <= '2020-10-14 23:59:59'::timestamp without time zone))
  • Filter: (defunct_ind = 'N'::bpchar)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=15
  • Buffers: shared hit=38,745
88. 6.810 372.780 ↓ 0.0 0 9,810

BitmapAnd (cost=13.03..13.03 rows=1 width=0) (actual time=0.038..0.038 rows=0 loops=9,810)

  • Buffers: shared hit=38,730
89. 9.810 9.810 ↑ 240.0 1 9,810

Bitmap Index Scan on ndx_osj_pe_id (cost=0.00..6.09 rows=240 width=0) (actual time=0.001..0.001 rows=1 loops=9,810)

  • Index Cond: (cln_visitplannedexecution_id = pe.cln_visitplannedexecution_id)
  • Buffers: shared hit=19,650
90. 356.160 356.160 ↓ 7.1 1,698 3,180

Bitmap Index Scan on ndx_osj_date (cost=0.00..6.69 rows=240 width=0) (actual time=0.112..0.112 rows=1,698 loops=3,180)

  • Index Cond: ((execed_datetime >= '2020-10-14 00:00:00'::timestamp without time zone) AND (execed_datetime <= '2020-10-14 23:59:59'::timestamp without time zone))
  • Buffers: shared hit=19,080
91. 19.620 19.620 ↓ 0.0 0 9,810

Index Scan using pk_cln_visit on cln_visit vt_2 (cost=0.29..8.32 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=9,810)

  • Index Cond: (cln_visit_id = oe.cln_visit_id)
  • Filter: ((cln_patient_id = v.cln_patient_id) AND ((visit_type)::text <> ALL ('{VTY4,VTY6,VTY7,VTY3,VTY8}'::text[])))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=29,594
92. 0.075 0.840 ↑ 1.0 1 15

Aggregate (cost=9,493.85..9,493.86 rows=1 width=8) (actual time=0.056..0.056 rows=1 loops=15)

  • Buffers: shared hit=450
93. 0.015 0.765 ↓ 0.0 0 15

Nested Loop (cost=30.68..9,493.84 rows=1 width=0) (actual time=0.050..0.051 rows=0 loops=15)

  • Buffers: shared hit=450
94. 0.045 0.705 ↑ 1.0 1 15

Nested Loop (cost=30.38..9,485.48 rows=1 width=8) (actual time=0.044..0.047 rows=1 loops=15)

  • Join Filter: (oe_1.cln_visitorderentry_id = pe_1.cln_visitorderentry_id)
  • Buffers: shared hit=405
95. 0.030 0.525 ↑ 1.0 1 15

Nested Loop Semi Join (cost=30.09..3,400.34 rows=1 width=24) (actual time=0.032..0.035 rows=1 loops=15)

  • Buffers: shared hit=330
96. 0.195 0.345 ↑ 1.0 1 15

Bitmap Heap Scan on cln_visitorderentry oe_1 (cost=4.46..16.25 rows=1 width=16) (actual time=0.020..0.023 rows=1 loops=15)

  • Recheck Cond: ((ptf_clinic_id = 1) AND (entered_datetime >= '2020-10-14 00:00:00'::timestamp without time zone) AND (entered_datetime <= '2020-10-14 23:59:59'::timestamp without time zone))
  • Filter: (((order_status)::text = 'OST1'::text) AND (lab_ind = 'Y'::bpchar))
  • Rows Removed by Filter: 18
  • Heap Blocks: exact=150
  • Buffers: shared hit=210
97. 0.150 0.150 ↓ 6.3 19 15

Bitmap Index Scan on ndx_vstoe_clinicid_enterdt (cost=0.00..4.46 rows=3 width=0) (actual time=0.010..0.010 rows=19 loops=15)

  • Index Cond: ((ptf_clinic_id = 1) AND (entered_datetime >= '2020-10-14 00:00:00'::timestamp without time zone) AND (entered_datetime <= '2020-10-14 23:59:59'::timestamp without time zone))
  • Buffers: shared hit=60
98. 0.045 0.150 ↑ 1.0 1 15

Nested Loop (cost=25.63..3,384.09 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=15)

  • Buffers: shared hit=120
99. 0.030 0.060 ↑ 6.0 1 15

Bitmap Heap Scan on cln_visitorderentryitem oei_1 (cost=25.21..2,980.36 rows=6 width=16) (actual time=0.004..0.004 rows=1 loops=15)

  • Recheck Cond: (cln_visitorderentry_id = oe_1.cln_visitorderentry_id)
  • Filter: ((order_status)::text = 'OST1'::text)
  • Heap Blocks: exact=15
  • Buffers: shared hit=60
100. 0.030 0.030 ↑ 1,172.0 1 15

Bitmap Index Scan on ndx_vstoei_vstoeid (cost=0.00..25.21 rows=1,172 width=0) (actual time=0.002..0.002 rows=1 loops=15)

  • Index Cond: (cln_visitorderentry_id = oe_1.cln_visitorderentry_id)
  • Buffers: shared hit=45
101. 0.045 0.045 ↑ 1.0 1 15

Index Scan using ndx_vstcharge_oeiid on cln_visitcharge vc_4 (cost=0.42..67.28 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=15)

  • Index Cond: (cln_visitorderentryitem_id = oei_1.cln_visitorderentryitem_id)
  • Filter: ((payment_ind = 'Y'::bpchar) AND ((charge_status)::text = 'CST1'::text))
  • Buffers: shared hit=60
102. 0.060 0.135 ↑ 1.0 1 15

Index Scan using ndx_pe_oeid on cln_visitplannedexecution pe_1 (cost=0.29..6,085.12 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=15)

  • Index Cond: (cln_visitorderentry_id = oei_1.cln_visitorderentry_id)
  • Filter: (((end_date IS NULL) OR (end_date > '2020-10-14 23:59:59'::timestamp without time zone)) AND ((order_status)::text = 'OST1'::text) AND (completion_ind = 'N'::bpchar) AND (qty_per_day > ((SubPlan 13))::numeric))
  • Buffers: shared hit=75
103.          

SubPlan (for Index Scan)

104. 0.030 0.075 ↑ 1.0 1 15

Aggregate (cost=17.05..17.06 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=15)

  • Buffers: shared hit=30
105. 0.015 0.045 ↓ 0.0 0 15

Bitmap Heap Scan on cln_visitorderstatusjournal osj_1 (cost=13.03..17.05 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=15)

  • Recheck Cond: ((cln_visitplannedexecution_id = pe_1.cln_visitplannedexecution_id) AND (execed_datetime >= '2020-10-14 00:00:00'::timestamp without time zone) AND (execed_datetime <= '2020-10-14 23:59:59'::timestamp without time zone))
  • Filter: (defunct_ind = 'N'::bpchar)
  • Buffers: shared hit=30
106. 0.000 0.030 ↓ 0.0 0 15

BitmapAnd (cost=13.03..13.03 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=15)

  • Buffers: shared hit=30
107. 0.030 0.030 ↓ 0.0 0 15

Bitmap Index Scan on ndx_osj_pe_id (cost=0.00..6.09 rows=240 width=0) (actual time=0.002..0.002 rows=0 loops=15)

  • Index Cond: (cln_visitplannedexecution_id = pe_1.cln_visitplannedexecution_id)
  • Buffers: shared hit=30
108. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ndx_osj_date (cost=0.00..6.69 rows=240 width=0) (never executed)

  • Index Cond: ((execed_datetime >= '2020-10-14 00:00:00'::timestamp without time zone) AND (execed_datetime <= '2020-10-14 23:59:59'::timestamp without time zone))
109. 0.045 0.045 ↓ 0.0 0 15

Index Scan using pk_cln_visit on cln_visit vt_3 (cost=0.29..8.32 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=15)

  • Index Cond: (cln_visit_id = oe_1.cln_visit_id)
  • Filter: ((cln_patient_id = v.cln_patient_id) AND ((visit_type)::text <> ALL ('{VTY4,VTY6,VTY7,VTY3,VTY8}'::text[])))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=45
Planning time : 9.381 ms
Execution time : 1,337.013 ms