explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N4LFM

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 17,100.398 ↓ 2.0 2 1

Sort (cost=913,812.83..913,812.84 rows=1 width=1,862) (actual time=17,100.398..17,100.398 rows=2 loops=1)

  • Sort Key: ((isr.date)::date)
  • Sort Method: quicksort Memory: 27kB
2. 0.113 17,100.378 ↓ 2.0 2 1

Nested Loop Left Join (cost=109,635.47..913,812.81 rows=1 width=1,862) (actual time=694.714..17,100.378 rows=2 loops=1)

  • Join Filter: ((isr.orig_lab_name)::text = (ih.hospital_id)::text)
  • Rows Removed by Join Filter: 14
3. 0.007 17,100.259 ↓ 2.0 2 1

Nested Loop (cost=109,635.47..913,811.10 rows=1 width=1,414) (actual time=694.626..17,100.259 rows=2 loops=1)

4. 0.008 17,100.226 ↓ 2.0 2 1

Nested Loop Left Join (cost=109,635.47..913,809.34 rows=1 width=1,404) (actual time=694.611..17,100.226 rows=2 loops=1)

5. 0.010 17,100.200 ↓ 2.0 2 1

Nested Loop Left Join (cost=109,635.19..913,802.96 rows=1 width=1,390) (actual time=694.593..17,100.200 rows=2 loops=1)

6. 0.016 17,100.170 ↓ 2.0 2 1

Hash Join (cost=109,634.91..913,796.59 rows=1 width=1,375) (actual time=694.573..17,100.170 rows=2 loops=1)

  • Hash Cond: (pr.center_id = hcm.center_id)
7. 0.021 17,100.138 ↓ 1.3 28 1

Nested Loop Left Join (cost=109,632.56..913,794.17 rows=21 width=1,161) (actual time=692.742..17,100.138 rows=28 loops=1)

  • Join Filter: ((bccl1.claim_id)::text = (bcc1.claim_id)::text)
  • Rows Removed by Join Filter: 24
8. 0.038 17,099.809 ↓ 1.3 28 1

Nested Loop Left Join (cost=109,632.00..913,645.27 rows=21 width=1,181) (actual time=692.712..17,099.809 rows=28 loops=1)

9. 0.039 17,099.491 ↓ 1.3 28 1

Nested Loop Left Join (cost=109,631.57..913,499.62 rows=21 width=1,167) (actual time=692.686..17,099.491 rows=28 loops=1)

10. 0.027 17,099.172 ↓ 1.3 28 1

Hash Left Join (cost=109,631.14..913,354.16 rows=21 width=1,156) (actual time=692.661..17,099.172 rows=28 loops=1)

  • Hash Cond: (pr1.op_type = otn1.op_type)
11. 0.027 17,099.138 ↓ 1.3 28 1

Hash Left Join (cost=109,630.02..913,352.95 rows=21 width=1,040) (actual time=692.643..17,099.138 rows=28 loops=1)

  • Hash Cond: ((pip1.sponsor_id)::text = (tpm1.tpa_id)::text)
12. 0.017 17,098.857 ↓ 1.3 28 1

Nested Loop Left Join (cost=109,603.70..913,326.58 rows=21 width=1,027) (actual time=692.378..17,098.857 rows=28 loops=1)

13. 0.050 17,098.756 ↓ 1.3 28 1

Nested Loop (cost=109,603.41..913,184.79 rows=21 width=988) (actual time=692.365..17,098.756 rows=28 loops=1)

14. 0.016 17,098.426 ↑ 2.7 28 1

Nested Loop Left Join (cost=109,602.85..912,637.24 rows=76 width=890) (actual time=692.339..17,098.426 rows=28 loops=1)

15. 0.031 17,098.275 ↑ 5.1 15 1

Nested Loop Left Join (cost=109,602.42..912,110.28 rows=76 width=892) (actual time=692.318..17,098.275 rows=15 loops=1)

16. 0.019 17,098.109 ↑ 5.1 15 1

Hash Left Join (cost=109,601.99..911,477.69 rows=76 width=863) (actual time=692.292..17,098.109 rows=15 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
17. 0.026 17,098.074 ↑ 5.1 15 1

Hash Left Join (cost=109,600.51..911,475.97 rows=76 width=754) (actual time=692.265..17,098.074 rows=15 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
18. 0.026 17,098.039 ↑ 5.1 15 1

Hash Left Join (cost=109,599.40..911,474.49 rows=76 width=638) (actual time=692.241..17,098.039 rows=15 loops=1)

  • Hash Cond: ((pip.sponsor_id)::text = (tpm.tpa_id)::text)
19. 0.012 17,097.698 ↑ 5.1 15 1

Nested Loop Left Join (cost=109,573.08..911,447.97 rows=76 width=625) (actual time=691.907..17,097.698 rows=15 loops=1)

20. 0.010 17,097.641 ↑ 5.1 15 1

Nested Loop Left Join (cost=109,572.79..910,934.84 rows=76 width=586) (actual time=691.894..17,097.641 rows=15 loops=1)

21. 0.027 17,097.481 ↑ 5.1 15 1

Hash Join (cost=109,572.36..910,407.88 rows=76 width=588) (actual time=691.867..17,097.481 rows=15 loops=1)

  • Hash Cond: ((pr.doctor)::text = (cdoc.doctor_id)::text)
22. 0.025 17,096.747 ↑ 5.1 15 1

Nested Loop Left Join (cost=109,486.47..910,321.79 rows=76 width=573) (actual time=691.144..17,096.747 rows=15 loops=1)

  • Join Filter: ((bccl.claim_id)::text = (bcc.claim_id)::text)
23. 0.015 17,096.557 ↑ 5.1 15 1

Nested Loop Left Join (cost=109,485.90..909,782.89 rows=76 width=593) (actual time=691.110..17,096.557 rows=15 loops=1)

24. 0.019 17,096.392 ↑ 5.1 15 1

Nested Loop (cost=109,485.47..909,255.78 rows=76 width=579) (actual time=691.084..17,096.392 rows=15 loops=1)

25. 0.028 17,096.223 ↑ 5.1 15 1

Hash Join (cost=109,485.04..908,729.38 rows=76 width=568) (actual time=691.060..17,096.223 rows=15 loops=1)

  • Hash Cond: (tp.outsource_dest_id = dom.outsource_dest_id)
26. 0.033 17,096.092 ↑ 35.2 15 1

Nested Loop (cost=109,476.51..908,718.11 rows=528 width=339) (actual time=690.940..17,096.092 rows=15 loops=1)

27. 0.029 17,095.909 ↑ 35.2 15 1

Nested Loop (cost=109,476.08..905,065.30 rows=528 width=321) (actual time=690.913..17,095.909 rows=15 loops=1)

28. 0.040 17,095.745 ↑ 35.2 15 1

Nested Loop (cost=109,475.64..901,408.80 rows=528 width=273) (actual time=690.884..17,095.745 rows=15 loops=1)

29. 5,592.010 17,095.510 ↑ 127.2 15 1

Hash Join (cost=109,475.08..887,894.97 rows=1,908 width=179) (actual time=690.833..17,095.510 rows=15 loops=1)

  • Hash Cond: ((bac.activity_id)::integer = tp.prescribed_id)
30. 11,275.142 11,275.142 ↓ 1.0 28,659,540 1

Seq Scan on bill_activity_charge bac (cost=0.00..599,278.96 rows=28,659,496 width=17) (actual time=0.023..11,275.142 rows=28,659,540 loops=1)

31. 0.014 228.358 ↑ 42.0 15 1

Hash (cost=109,467.21..109,467.21 rows=630 width=173) (actual time=228.358..228.358 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
32. 0.007 228.344 ↑ 42.0 15 1

Nested Loop (cost=11,401.48..109,467.21 rows=630 width=173) (actual time=228.282..228.344 rows=15 loops=1)

33. 0.006 228.292 ↑ 361.7 15 1

Nested Loop (cost=11,401.04..71,828.11 rows=5,425 width=127) (actual time=228.259..228.292 rows=15 loops=1)

34. 80.423 228.241 ↑ 361.7 15 1

Hash Join (cost=11,400.61..34,143.78 rows=5,425 width=115) (actual time=228.234..228.241 rows=15 loops=1)

  • Hash Cond: ((isrd.incoming_visit_id)::text = (isr.incoming_visit_id)::text)
35. 81.437 81.437 ↑ 1.0 1,085,054 1

Seq Scan on incoming_sample_registration_details isrd (cost=0.00..19,894.59 rows=1,085,159 width=61) (actual time=0.006..81.437 rows=1,085,054 loops=1)

36. 0.023 66.381 ↑ 103.9 14 1

Hash (cost=11,382.42..11,382.42 rows=1,455 width=72) (actual time=66.381..66.381 rows=14 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
37. 66.358 66.358 ↑ 103.9 14 1

Seq Scan on incoming_sample_registration isr (cost=0.00..11,382.42 rows=1,455 width=72) (actual time=66.348..66.358 rows=14 loops=1)

  • Filter: (((date)::date >= '2019-07-01'::date) AND ((date)::date <= '2019-07-05'::date))
  • Rows Removed by Filter: 291010
38. 0.045 0.045 ↑ 1.0 1 15

Index Scan using tests_prescribed_pkey on tests_prescribed tp1 (cost=0.43..6.95 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=15)

  • Index Cond: (prescribed_id = isrd.prescribed_id)
39. 0.045 0.045 ↑ 1.0 1 15

Index Scan using tp_out_des_idx on tests_prescribed tp (cost=0.43..6.93 rows=1 width=58) (actual time=0.003..0.003 rows=1 loops=15)

  • Index Cond: (outsource_dest_prescribed_id = tp1.prescribed_id)
40. 0.195 0.195 ↑ 1.0 1 15

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..7.08 rows=1 width=104) (actual time=0.013..0.013 rows=1 loops=15)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
  • Filter: ((charge_head)::text = 'LTDIA'::text)
41. 0.135 0.135 ↑ 1.0 1 15

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..6.93 rows=1 width=48) (actual time=0.009..0.009 rows=1 loops=15)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
42. 0.150 0.150 ↑ 1.0 1 15

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..6.92 rows=1 width=48) (actual time=0.010..0.010 rows=1 loops=15)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
43. 0.013 0.103 ↑ 1.0 71 1

Hash (cost=7.65..7.65 rows=71 width=237) (actual time=0.103..0.103 rows=71 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
44. 0.020 0.090 ↑ 1.0 71 1

Hash Left Join (cost=5.03..7.65 rows=71 width=237) (actual time=0.060..0.090 rows=71 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm_1.center_id)::text)
45. 0.023 0.053 ↑ 1.0 71 1

Hash Left Join (cost=2.42..4.33 rows=71 width=29) (actual time=0.035..0.053 rows=71 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
46. 0.008 0.008 ↑ 1.0 71 1

Seq Scan on diag_outsource_master dom (cost=0.00..1.71 rows=71 width=16) (actual time=0.005..0.008 rows=71 loops=1)

47. 0.013 0.022 ↑ 1.0 63 1

Hash (cost=1.63..1.63 rows=63 width=24) (actual time=0.022..0.022 rows=63 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
48. 0.009 0.009 ↑ 1.0 63 1

Seq Scan on outhouse_master om (cost=0.00..1.63 rows=63 width=24) (actual time=0.004..0.009 rows=63 loops=1)

49. 0.011 0.017 ↑ 1.0 27 1

Hash (cost=2.27..2.27 rows=27 width=222) (actual time=0.017..0.017 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
50. 0.006 0.006 ↑ 1.0 27 1

Seq Scan on hospital_center_master hcm_1 (cost=0.00..2.27 rows=27 width=222) (actual time=0.002..0.006 rows=27 loops=1)

51. 0.150 0.150 ↑ 1.0 1 15

Index Scan using bill_pkey on bill b (cost=0.43..6.93 rows=1 width=25) (actual time=0.010..0.010 rows=1 loops=15)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
52. 0.150 0.150 ↑ 1.0 1 15

Index Scan using bill_claim_bill_no_idx on bill_claim bccl (cost=0.43..6.93 rows=1 width=29) (actual time=0.010..0.010 rows=1 loops=15)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (priority = 1)
53. 0.165 0.165 ↑ 1.0 1 15

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc (cost=0.56..7.08 rows=1 width=28) (actual time=0.010..0.011 rows=1 loops=15)

  • Index Cond: ((charge_id)::text = (bc.charge_id)::text)
54. 0.308 0.707 ↑ 1.0 1,773 1

Hash (cost=63.73..63.73 rows=1,773 width=30) (actual time=0.707..0.707 rows=1,773 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 125kB
55. 0.399 0.399 ↑ 1.0 1,773 1

Seq Scan on doctors cdoc (cost=0.00..63.73 rows=1,773 width=30) (actual time=0.005..0.399 rows=1,773 loops=1)

56. 0.150 0.150 ↑ 1.0 1 15

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip (cost=0.43..6.92 rows=1 width=30) (actual time=0.009..0.010 rows=1 loops=15)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
57. 0.045 0.045 ↑ 1.0 1 15

Index Scan using insurance_plan_main_pkey on insurance_plan_main ipm (cost=0.29..6.75 rows=1 width=47) (actual time=0.003..0.003 rows=1 loops=15)

  • Index Cond: (plan_id = pip.plan_id)
58. 0.168 0.315 ↑ 1.0 592 1

Hash (cost=18.92..18.92 rows=592 width=33) (actual time=0.315..0.315 rows=592 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
59. 0.147 0.147 ↑ 1.0 592 1

Seq Scan on tpa_master tpm (cost=0.00..18.92 rows=592 width=33) (actual time=0.009..0.147 rows=592 loops=1)

60. 0.004 0.009 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=126) (actual time=0.009..0.009 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
61. 0.005 0.005 ↑ 1.0 5 1

Seq Scan on op_type_names otn (cost=0.00..1.05 rows=5 width=126) (actual time=0.004..0.005 rows=5 loops=1)

62. 0.007 0.016 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=156) (actual time=0.016..0.016 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
63. 0.009 0.009 ↑ 1.0 21 1

Seq Scan on salutation_master sm (cost=0.00..1.21 rows=21 width=156) (actual time=0.006..0.009 rows=21 loops=1)

64. 0.135 0.135 ↑ 1.0 1 15

Index Scan using patient_registration_pkey on patient_registration pr1 (cost=0.43..8.32 rows=1 width=29) (actual time=0.009..0.009 rows=1 loops=15)

  • Index Cond: ((patient_id)::text = (isr.visit_id)::text)
65. 0.135 0.135 ↓ 2.0 2 15

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip1 (cost=0.43..6.92 rows=1 width=30) (actual time=0.008..0.009 rows=2 loops=15)

  • Index Cond: ((patient_id)::text = (pr1.patient_id)::text)
66. 0.280 0.280 ↑ 1.0 1 28

Index Scan using bill_charge_pkey on bill_charge bc1 (cost=0.56..7.20 rows=1 width=98) (actual time=0.010..0.010 rows=1 loops=28)

  • Index Cond: ((charge_id)::text = (isrd.billed_charge_id)::text)
  • Filter: ((charge_head)::text = 'LTDIA'::text)
67. 0.084 0.084 ↑ 1.0 1 28

Index Scan using insurance_plan_main_pkey on insurance_plan_main ipm1 (cost=0.29..6.75 rows=1 width=47) (actual time=0.003..0.003 rows=1 loops=28)

  • Index Cond: (plan_id = pip1.plan_id)
68. 0.150 0.254 ↑ 1.0 592 1

Hash (cost=18.92..18.92 rows=592 width=33) (actual time=0.254..0.254 rows=592 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
69. 0.104 0.104 ↑ 1.0 592 1

Seq Scan on tpa_master tpm1 (cost=0.00..18.92 rows=592 width=33) (actual time=0.003..0.104 rows=592 loops=1)

70. 0.004 0.007 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=126) (actual time=0.007..0.007 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
71. 0.003 0.003 ↑ 1.0 5 1

Seq Scan on op_type_names otn1 (cost=0.00..1.05 rows=5 width=126) (actual time=0.002..0.003 rows=5 loops=1)

72. 0.280 0.280 ↑ 1.0 1 28

Index Scan using bill_pkey on bill b1 (cost=0.43..6.93 rows=1 width=25) (actual time=0.010..0.010 rows=1 loops=28)

  • Index Cond: ((bc1.bill_no)::text = (bill_no)::text)
73. 0.280 0.280 ↑ 1.0 1 28

Index Scan using bill_claim_bill_no_idx on bill_claim bccl1 (cost=0.43..6.93 rows=1 width=29) (actual time=0.010..0.010 rows=1 loops=28)

  • Index Cond: ((bill_no)::text = (b1.bill_no)::text)
  • Filter: (priority = 1)
  • Rows Removed by Filter: 1
74. 0.308 0.308 ↓ 2.0 2 28

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc1 (cost=0.56..7.08 rows=1 width=28) (actual time=0.010..0.011 rows=2 loops=28)

  • Index Cond: ((charge_id)::text = (bc1.charge_id)::text)
75. 0.004 0.016 ↑ 1.0 1 1

Hash (cost=2.34..2.34 rows=1 width=222) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
76. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on hospital_center_master hcm (cost=0.00..2.34 rows=1 width=222) (actual time=0.009..0.012 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMC Deira'::text)
  • Rows Removed by Filter: 26
77. 0.020 0.020 ↑ 1.0 1 2

Index Scan using doctors_pkey on doctors cdoc1 (cost=0.28..6.37 rows=1 width=30) (actual time=0.010..0.010 rows=1 loops=2)

  • Index Cond: ((doctor_id)::text = (pr1.doctor)::text)
78. 0.018 0.018 ↑ 1.0 1 2

Index Scan using doctors_pkey on doctors d (cost=0.28..6.37 rows=1 width=30) (actual time=0.009..0.009 rows=1 loops=2)

  • Index Cond: ((doctor_id)::text = (tp.pres_doctor)::text)
79. 0.026 0.026 ↑ 1.0 1 2

Seq Scan on chargehead_constants cc (cost=0.00..1.75 rows=1 width=22) (actual time=0.010..0.013 rows=1 loops=2)

  • Filter: ((chargehead_id)::text = 'LTDIA'::text)
  • Rows Removed by Filter: 59
80. 0.006 0.006 ↑ 2.4 8 2

Seq Scan on incoming_hospitals ih (cost=0.00..1.19 rows=19 width=266) (actual time=0.003..0.003 rows=8 loops=2)