explain.depesz.com

PostgreSQL's explain analyze made readable

Result: beiL

Settings
# exclusive inclusive rows x rows loops node
1. 0.047 10,931.353 ↓ 2.0 2 1

Sort (cost=848,037.76..848,037.77 rows=1 width=1,862) (actual time=10,931.352..10,931.353 rows=2 loops=1)

  • Sort Key: ((isr.date)::date)
  • Sort Method: quicksort Memory: 27kB
2. 1.129 10,931.306 ↓ 2.0 2 1

Nested Loop Left Join (cost=72,111.16..848,037.74 rows=1 width=1,862) (actual time=1,014.293..10,931.306 rows=2 loops=1)

  • Join Filter: ((isr.orig_lab_name)::text = (ih.hospital_id)::text)
  • Rows Removed by Join Filter: 14
3. 0.006 10,929.535 ↓ 2.0 2 1

Nested Loop (cost=72,111.16..848,036.03 rows=1 width=1,414) (actual time=1,012.547..10,929.535 rows=2 loops=1)

4. 0.012 10,929.503 ↓ 2.0 2 1

Nested Loop Left Join (cost=72,111.16..848,034.27 rows=1 width=1,404) (actual time=1,012.533..10,929.503 rows=2 loops=1)

5. 0.010 10,929.465 ↓ 2.0 2 1

Nested Loop Left Join (cost=72,110.88..848,027.89 rows=1 width=1,390) (actual time=1,012.503..10,929.465 rows=2 loops=1)

6. 0.025 10,929.149 ↓ 2.0 2 1

Hash Join (cost=72,110.61..848,021.52 rows=1 width=1,375) (actual time=1,012.198..10,929.149 rows=2 loops=1)

  • Hash Cond: (pr.center_id = hcm.center_id)
7. 0.025 10,929.084 ↓ 4.0 28 1

Nested Loop Left Join (cost=72,108.26..848,019.15 rows=7 width=1,161) (actual time=1,003.751..10,929.084 rows=28 loops=1)

  • Join Filter: ((bccl1.claim_id)::text = (bcc1.claim_id)::text)
  • Rows Removed by Join Filter: 24
8. 0.037 10,925.867 ↓ 4.0 28 1

Nested Loop Left Join (cost=72,107.69..847,969.51 rows=7 width=1,181) (actual time=1,003.035..10,925.867 rows=28 loops=1)

9. 0.036 10,925.130 ↓ 4.0 28 1

Nested Loop Left Join (cost=72,107.26..847,920.96 rows=7 width=1,167) (actual time=1,002.994..10,925.130 rows=28 loops=1)

10. 0.028 10,923.974 ↓ 4.0 28 1

Hash Left Join (cost=72,106.83..847,872.48 rows=7 width=1,156) (actual time=1,002.543..10,923.974 rows=28 loops=1)

  • Hash Cond: (pr1.op_type = otn1.op_type)
11. 0.030 10,923.940 ↓ 4.0 28 1

Hash Left Join (cost=72,105.72..847,871.34 rows=7 width=1,040) (actual time=1,002.526..10,923.940 rows=28 loops=1)

  • Hash Cond: ((pip1.sponsor_id)::text = (tpm1.tpa_id)::text)
12. 0.037 10,923.730 ↓ 4.0 28 1

Nested Loop Left Join (cost=72,079.40..847,845.00 rows=7 width=1,027) (actual time=1,002.333..10,923.730 rows=28 loops=1)

13. 0.031 10,923.413 ↓ 4.0 28 1

Nested Loop (cost=72,079.11..847,797.74 rows=7 width=988) (actual time=1,002.189..10,923.413 rows=28 loops=1)

14. 0.014 10,922.598 ↓ 1.1 28 1

Nested Loop Left Join (cost=72,078.54..847,617.62 rows=25 width=890) (actual time=1,001.906..10,922.598 rows=28 loops=1)

15. 0.027 10,921.624 ↑ 1.7 15 1

Nested Loop Left Join (cost=72,078.11..847,444.28 rows=25 width=892) (actual time=1,001.608..10,921.624 rows=15 loops=1)

16. 0.020 10,921.477 ↑ 1.7 15 1

Hash Left Join (cost=72,077.68..847,236.19 rows=25 width=863) (actual time=1,001.569..10,921.477 rows=15 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
17. 0.043 10,921.440 ↑ 1.7 15 1

Hash Left Join (cost=72,076.21..847,234.64 rows=25 width=754) (actual time=1,001.541..10,921.440 rows=15 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
18. 0.027 10,921.381 ↑ 1.7 15 1

Hash Left Join (cost=72,075.09..847,233.41 rows=25 width=638) (actual time=1,001.491..10,921.381 rows=15 loops=1)

  • Hash Cond: ((pip.sponsor_id)::text = (tpm.tpa_id)::text)
19. 0.024 10,921.120 ↑ 1.7 15 1

Nested Loop Left Join (cost=72,048.77..847,207.02 rows=25 width=625) (actual time=1,001.240..10,921.120 rows=15 loops=1)

20. 0.012 10,920.961 ↑ 1.7 15 1

Nested Loop Left Join (cost=72,048.48..847,038.23 rows=25 width=586) (actual time=1,001.117..10,920.961 rows=15 loops=1)

21. 0.036 10,920.064 ↑ 1.7 15 1

Hash Join (cost=72,048.05..846,864.89 rows=25 width=588) (actual time=1,000.574..10,920.064 rows=15 loops=1)

  • Hash Cond: ((pr.doctor)::text = (cdoc.doctor_id)::text)
22. 0.018 10,918.914 ↑ 1.7 15 1

Nested Loop Left Join (cost=71,962.16..846,778.93 rows=25 width=573) (actual time=999.435..10,918.914 rows=15 loops=1)

  • Join Filter: ((bccl.claim_id)::text = (bcc.claim_id)::text)
23. 0.024 10,918.116 ↑ 1.7 15 1

Nested Loop Left Join (cost=71,961.60..846,601.66 rows=25 width=593) (actual time=998.995..10,918.116 rows=15 loops=1)

24. 0.020 10,916.832 ↑ 1.7 15 1

Nested Loop (cost=71,961.16..846,428.27 rows=25 width=579) (actual time=998.312..10,916.832 rows=15 loops=1)

25. 0.026 10,915.612 ↑ 1.7 15 1

Hash Join (cost=71,960.73..846,255.11 rows=25 width=568) (actual time=997.715..10,915.612 rows=15 loops=1)

  • Hash Cond: (tp.outsource_dest_id = dom.outsource_dest_id)
26. 0.039 10,915.114 ↑ 11.6 15 1

Nested Loop (cost=71,952.20..846,245.67 rows=174 width=339) (actual time=997.229..10,915.114 rows=15 loops=1)

27. 0.036 10,914.400 ↑ 11.6 15 1

Nested Loop (cost=71,951.77..845,041.91 rows=174 width=321) (actual time=997.192..10,914.400 rows=15 loops=1)

28. 0.021 10,914.244 ↑ 11.6 15 1

Nested Loop (cost=71,951.33..843,836.92 rows=174 width=273) (actual time=997.154..10,914.244 rows=15 loops=1)

29. 0.027 10,912.423 ↑ 41.9 15 1

Nested Loop (cost=71,950.77..839,382.83 rows=628 width=179) (actual time=996.214..10,912.423 rows=15 loops=1)

  • Join Filter: (isrd.prescribed_id = tp1.prescribed_id)
30. 1,593.580 10,912.336 ↑ 360.3 15 1

Hash Join (cost=71,950.34..801,869.15 rows=5,405 width=179) (actual time=996.189..10,912.336 rows=15 loops=1)

  • Hash Cond: ((bac.activity_id)::integer = tp.prescribed_id)
31. 8,741.628 8,741.628 ↑ 1.0 9,381,472 1

Seq Scan on bill_activity_charge bac (cost=0.00..670,927.70 rows=9,429,930 width=17) (actual time=0.565..8,741.628 rows=9,381,472 loops=1)

  • Filter: ((activity_code)::text = 'DIA'::text)
  • Rows Removed by Filter: 19278068
32. 0.015 577.128 ↑ 361.7 15 1

Hash (cost=71,882.52..71,882.52 rows=5,425 width=173) (actual time=577.128..577.128 rows=15 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 67kB
33. 0.020 577.113 ↑ 361.7 15 1

Nested Loop (cost=11,401.04..71,882.52 rows=5,425 width=173) (actual time=576.780..577.113 rows=15 loops=1)

34. 99.245 576.448 ↑ 361.7 15 1

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

  • Hash Cond: ((isrd.incoming_visit_id)::text = (isr.incoming_visit_id)::text)
35. 395.806 395.806 ↑ 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.008..395.806 rows=1,085,054 loops=1)

36. 0.014 81.397 ↑ 103.9 14 1

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

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

Seq Scan on incoming_sample_registration isr (cost=0.00..11,382.42 rows=1,455 width=72) (actual time=81.377..81.383 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.645 0.645 ↑ 1.0 1 15

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

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

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

  • Index Cond: (prescribed_id = tp.outsource_dest_prescribed_id)
40. 1.800 1.800 ↑ 1.0 1 15

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

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
  • Filter: ((charge_head)::text = 'LTDIA'::text)
41. 0.120 0.120 ↑ 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.008..0.008 rows=1 loops=15)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
42. 0.675 0.675 ↑ 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.045..0.045 rows=1 loops=15)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
44. 0.033 0.450 ↑ 1.0 71 1

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

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

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

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

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

47. 0.020 0.037 ↑ 1.0 63 1

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

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

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

49. 0.020 0.032 ↑ 1.0 27 1

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

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

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

51. 1.200 1.200 ↑ 1.0 1 15

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

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
52. 1.260 1.260 ↑ 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.084..0.084 rows=1 loops=15)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (priority = 1)
53. 0.780 0.780 ↑ 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.052..0.052 rows=1 loops=15)

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

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

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

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

56. 0.885 0.885 ↑ 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.058..0.059 rows=1 loops=15)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
57. 0.135 0.135 ↑ 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.009..0.009 rows=1 loops=15)

  • Index Cond: (plan_id = pip.plan_id)
58. 0.111 0.234 ↑ 1.0 592 1

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

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

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

60. 0.003 0.016 ↑ 1.0 5 1

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

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

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

62. 0.007 0.017 ↑ 1.0 21 1

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

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

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

64. 0.120 0.120 ↑ 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.008..0.008 rows=1 loops=15)

  • Index Cond: ((patient_id)::text = (isr.visit_id)::text)
65. 0.960 0.960 ↓ 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.063..0.064 rows=2 loops=15)

  • Index Cond: ((patient_id)::text = (pr1.patient_id)::text)
66. 0.784 0.784 ↑ 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.028..0.028 rows=1 loops=28)

  • Index Cond: ((charge_id)::text = (isrd.billed_charge_id)::text)
  • Filter: ((charge_head)::text = 'LTDIA'::text)
67. 0.280 0.280 ↑ 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.010..0.010 rows=1 loops=28)

  • Index Cond: (plan_id = pip1.plan_id)
68. 0.105 0.180 ↑ 1.0 592 1

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

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

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

70. 0.003 0.006 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=126) (actual time=0.006..0.006 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. 1.120 1.120 ↑ 1.0 1 28

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

  • Index Cond: ((bc1.bill_no)::text = (bill_no)::text)
73. 0.700 0.700 ↑ 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.025..0.025 rows=1 loops=28)

  • Index Cond: ((bill_no)::text = (b1.bill_no)::text)
  • Filter: (priority = 1)
  • Rows Removed by Filter: 1
74. 3.192 3.192 ↓ 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.064..0.114 rows=2 loops=28)

  • Index Cond: ((charge_id)::text = (bc1.charge_id)::text)
75. 0.003 0.040 ↑ 1.0 1 1

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

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

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

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

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

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

Index Scan using doctors_pkey on doctors d (cost=0.28..6.37 rows=1 width=30) (actual time=0.013..0.013 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.009..0.013 rows=1 loops=2)

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

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

Planning time : 123.188 ms
Execution time : 10,934.865 ms