explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Aisr

Settings
# exclusive inclusive rows x rows loops node
1. 353.900 304,347.686 ↓ 24,201.0 48,402 1

Sort (cost=3,756,587.47..3,756,587.47 rows=2 width=1,226) (actual time=304,330.164..304,347.686 rows=48,402 loops=1)

  • Sort Key: ((isr.date)::date)
  • Sort Method: quicksort Memory: 50689kB
2. 21.758 303,993.786 ↓ 24,201.0 48,402 1

Append (cost=48,153.70..3,756,587.46 rows=2 width=1,226) (actual time=15,923.116..303,993.786 rows=48,402 loops=1)

3. 2,220.968 156,458.192 ↓ 48,397.0 48,397 1

Nested Loop Left Join (cost=48,153.70..1,878,293.70 rows=1 width=1,226) (actual time=15,923.114..156,458.192 rows=48,397 loops=1)

4. 183.228 154,043.636 ↓ 48,397.0 48,397 1

Nested Loop (cost=48,153.56..1,878,293.24 rows=1 width=646) (actual time=15,922.078..154,043.636 rows=48,397 loops=1)

5. 114.001 153,182.850 ↓ 48,397.0 48,397 1

Nested Loop Left Join (cost=48,153.56..1,878,290.26 rows=1 width=636) (actual time=15,922.051..153,182.850 rows=48,397 loops=1)

6. 135.142 152,730.070 ↓ 48,397.0 48,397 1

Nested Loop Left Join (cost=48,153.28..1,878,289.94 rows=1 width=621) (actual time=15,922.029..152,730.070 rows=48,397 loops=1)

7. 148.249 152,207.752 ↓ 48,397.0 48,397 1

Nested Loop (cost=48,153.00..1,878,289.62 rows=1 width=607) (actual time=15,921.996..152,207.752 rows=48,397 loops=1)

8. 190.323 151,773.263 ↓ 23,853.3 71,560 1

Nested Loop Left Join (cost=48,152.86..1,878,289.02 rows=3 width=593) (actual time=15,921.971..151,773.263 rows=71,560 loops=1)

  • Join Filter: ((bccl1.claim_id)::text = (bcc1.claim_id)::text)
9. 153.776 130,687.420 ↓ 23,853.3 71,560 1

Nested Loop Left Join (cost=48,152.30..1,878,286.56 rows=3 width=613) (actual time=15,921.106..130,687.420 rows=71,560 loops=1)

10. 189.060 125,953.804 ↓ 23,853.3 71,560 1

Nested Loop Left Join (cost=48,151.74..1,878,284.65 rows=3 width=599) (actual time=15,920.793..125,953.804 rows=71,560 loops=1)

11. 176.845 123,117.024 ↓ 23,853.3 71,560 1

Nested Loop Left Join (cost=48,151.18..1,878,282.61 rows=3 width=588) (actual time=15,920.497..123,117.024 rows=71,560 loops=1)

12. 159.665 122,725.499 ↓ 23,853.3 71,560 1

Nested Loop Left Join (cost=48,151.05..1,878,282.12 rows=3 width=572) (actual time=15,920.481..122,725.499 rows=71,560 loops=1)

13. 179.961 121,993.354 ↓ 23,853.3 71,560 1

Nested Loop Left Join (cost=48,150.77..1,878,281.18 rows=3 width=558) (actual time=15,920.452..121,993.354 rows=71,560 loops=1)

14. 155.549 121,384.033 ↓ 23,853.3 71,560 1

Nested Loop (cost=48,150.48..1,878,280.20 rows=3 width=518) (actual time=15,920.425..121,384.033 rows=71,560 loops=1)

15. 166.248 94,256.876 ↓ 1,668.2 71,733 1

Nested Loop Left Join (cost=48,149.92..1,878,166.69 rows=43 width=425) (actual time=15,919.493..94,256.876 rows=71,733 loops=1)

16. 194.083 92,512.502 ↓ 1,668.2 71,733 1

Nested Loop Left Join (cost=48,149.36..1,878,137.98 rows=43 width=427) (actual time=15,919.155..92,512.502 rows=71,733 loops=1)

17. 115.130 90,740.293 ↓ 1,668.2 71,733 1

Hash Left Join (cost=48,148.80..1,877,942.03 rows=43 width=389) (actual time=15,919.114..90,740.293 rows=71,733 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
18. 130.408 90,625.150 ↓ 1,668.2 71,733 1

Hash Left Join (cost=48,146.49..1,877,939.54 rows=43 width=393) (actual time=15,919.080..90,625.150 rows=71,733 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
19. 124.298 90,494.729 ↓ 1,668.2 71,733 1

Nested Loop Left Join (cost=48,145.18..1,877,937.84 rows=43 width=377) (actual time=15,918.922..90,494.729 rows=71,733 loops=1)

20. 135.457 89,796.567 ↓ 1,668.2 71,733 1

Nested Loop Left Join (cost=48,144.90..1,877,924.40 rows=43 width=363) (actual time=15,918.894..89,796.567 rows=71,733 loops=1)

21. 174.743 89,230.712 ↓ 1,668.2 71,733 1

Nested Loop Left Join (cost=48,144.61..1,877,910.30 rows=43 width=323) (actual time=15,918.857..89,230.712 rows=71,733 loops=1)

22. 195.470 87,478.921 ↓ 1,667.1 71,684 1

Nested Loop (cost=48,144.05..1,877,881.59 rows=43 width=325) (actual time=15,916.726..87,478.921 rows=71,684 loops=1)

23. 186.682 86,638.295 ↓ 1,667.1 71,684 1

Nested Loop Left Join (cost=48,143.77..1,877,867.94 rows=43 width=311) (actual time=15,916.674..86,638.295 rows=71,684 loops=1)

  • Join Filter: ((bccl.claim_id)::text = (bcc.claim_id)::text)
  • Rows Removed by Join Filter: 39
24. 135.674 67,885.457 ↓ 1,667.1 71,684 1

Nested Loop Left Join (cost=48,143.21..1,877,832.67 rows=43 width=331) (actual time=15,915.474..67,885.457 rows=71,684 loops=1)

25. 150.551 63,305.375 ↓ 1,667.1 71,684 1

Nested Loop (cost=48,142.65..1,877,805.35 rows=43 width=317) (actual time=15,914.594..63,305.375 rows=71,684 loops=1)

26. 133.660 60,144.096 ↓ 1,667.1 71,684 1

Hash Join (cost=48,142.09..1,877,776.15 rows=43 width=306) (actual time=15,913.956..60,144.096 rows=71,684 loops=1)

  • Hash Cond: (tp.outsource_dest_id = dom.outsource_dest_id)
27. 153.700 60,010.257 ↓ 246.3 71,684 1

Nested Loop (cost=48,120.85..1,877,752.53 rows=291 width=274) (actual time=15,913.751..60,010.257 rows=71,684 loops=1)

28. 245.526 58,279.509 ↓ 246.3 71,684 1

Nested Loop (cost=48,120.42..1,877,608.85 rows=291 width=255) (actual time=15,913.698..58,279.509 rows=71,684 loops=1)

29. 184.196 56,456.935 ↓ 246.3 71,684 1

Nested Loop (cost=48,119.86..1,877,414.55 rows=291 width=202) (actual time=15,913.650..56,456.935 rows=71,684 loops=1)

30. 197.100 28,844.757 ↓ 70.7 71,801 1

Nested Loop (cost=48,119.30..1,876,371.84 rows=1,015 width=113) (actual time=15,912.485..28,844.757 rows=71,801 loops=1)

  • Join Filter: (isrd.prescribed_id = tp1.prescribed_id)
31. 5,429.206 24,626.801 ↓ 8.8 71,801 1

Hash Join (cost=48,118.86..1,871,652.61 rows=8,168 width=113) (actual time=15,911.804..24,626.801 rows=71,801 loops=1)

  • Hash Cond: ((bac.activity_id)::integer = tp.prescribed_id)
32. 14,987.015 14,987.015 ↓ 1.0 13,323,201 1

Seq Scan on bill_activity_charge bac (cost=0.00..1,740,032.44 rows=13,321,002 width=18) (actual time=0.428..14,987.015 rows=13,323,201 loops=1)

  • Filter: ((activity_code)::text = 'DIA'::text)
  • Rows Removed by Filter: 26600757
33. 91.090 4,210.580 ↓ 8.8 71,811 1

Hash (cost=47,852.17..47,852.17 rows=8,206 width=107) (actual time=4,210.580..4,210.580 rows=71,811 loops=1)

  • Buckets: 131072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 11400kB
34. 107.201 4,119.490 ↓ 8.8 71,811 1

Nested Loop (cost=0.99..47,852.17 rows=8,206 width=107) (actual time=24.403..4,119.490 rows=71,811 loops=1)

35. 50.325 840.989 ↓ 8.7 72,075 1

Nested Loop (cost=0.43..37,494.21 rows=8,314 width=50) (actual time=23.796..840.989 rows=72,075 loops=1)

36. 170.266 170.266 ↓ 8.2 18,247 1

Seq Scan on incoming_sample_registration isr (cost=0.00..27,291.00 rows=2,228 width=44) (actual time=22.820..170.266 rows=18,247 loops=1)

  • Filter: (((date)::date >= '2020-01-01'::date) AND ((date)::date <= '2020-01-31'::date))
  • Rows Removed by Filter: 427728
37. 620.398 620.398 ↑ 1.8 4 18,247

Index Scan using idx_incoming_sample_registration_details_visit_id on incoming_sample_registration_details isrd (cost=0.43..4.37 rows=7 width=24) (actual time=0.027..0.034 rows=4 loops=18,247)

  • Index Cond: ((incoming_visit_id)::text = (isr.incoming_visit_id)::text)
38. 3,171.300 3,171.300 ↑ 1.0 1 72,075

Index Scan using tp_out_des_idx on tests_prescribed tp (cost=0.56..1.22 rows=1 width=57) (actual time=0.043..0.044 rows=1 loops=72,075)

  • Index Cond: (outsource_dest_prescribed_id = isrd.prescribed_id)
39. 4,020.856 4,020.856 ↑ 1.0 1 71,801

Index Scan using tests_prescribed_pkey on tests_prescribed tp1 (cost=0.43..0.55 rows=1 width=12) (actual time=0.056..0.056 rows=1 loops=71,801)

  • Index Cond: (prescribed_id = tp.outsource_dest_prescribed_id)
40. 27,427.982 27,427.982 ↑ 1.0 1 71,801

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..1.03 rows=1 width=99) (actual time=0.382..0.382 rows=1 loops=71,801)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
  • Filter: ((charge_head)::text = 'LTDIA'::text)
  • Rows Removed by Filter: 0
41. 1,577.048 1,577.048 ↑ 1.0 1 71,684

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..0.67 rows=1 width=53) (actual time=0.022..0.022 rows=1 loops=71,684)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
42. 1,577.048 1,577.048 ↑ 1.0 1 71,684

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.49 rows=1 width=49) (actual time=0.022..0.022 rows=1 loops=71,684)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
43. 0.018 0.179 ↑ 1.0 86 1

Hash (cost=18.44..18.44 rows=86 width=40) (actual time=0.179..0.179 rows=86 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
44. 0.023 0.161 ↑ 1.0 86 1

Hash Left Join (cost=12.69..18.44 rows=86 width=40) (actual time=0.126..0.161 rows=86 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm_1.center_id)::text)
45. 0.033 0.081 ↑ 1.0 86 1

Hash Left Join (cost=5.81..9.65 rows=86 width=32) (actual time=0.060..0.081 rows=86 loops=1)

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

Seq Scan on diag_outsource_master dom (cost=0.00..3.58 rows=86 width=16) (actual time=0.015..0.019 rows=86 loops=1)

47. 0.018 0.029 ↑ 1.0 77 1

Hash (cost=3.31..3.31 rows=77 width=27) (actual time=0.029..0.029 rows=77 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
48. 0.011 0.011 ↑ 1.0 77 1

Seq Scan on outhouse_master om (cost=0.00..3.31 rows=77 width=27) (actual time=0.005..0.011 rows=77 loops=1)

49. 0.018 0.057 ↑ 1.0 46 1

Hash (cost=5.38..5.38 rows=46 width=22) (actual time=0.057..0.057 rows=46 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
50. 0.039 0.039 ↑ 1.0 46 1

Seq Scan on hospital_center_master hcm_1 (cost=0.00..5.38 rows=46 width=22) (actual time=0.004..0.039 rows=46 loops=1)

51. 3,010.728 3,010.728 ↑ 1.0 1 71,684

Index Scan using bill_pkey on bill b (cost=0.56..0.68 rows=1 width=25) (actual time=0.042..0.042 rows=1 loops=71,684)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
52. 4,444.408 4,444.408 ↑ 1.0 1 71,684

Index Scan using bill_claim_bill_no_priority_key on bill_claim bccl (cost=0.56..0.64 rows=1 width=29) (actual time=0.062..0.062 rows=1 loops=71,684)

  • Index Cond: (((bill_no)::text = (b.bill_no)::text) AND (priority = 1))
53. 18,566.156 18,566.156 ↑ 1.0 1 71,684

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc (cost=0.56..0.79 rows=1 width=28) (actual time=0.252..0.259 rows=1 loops=71,684)

  • Index Cond: ((charge_id)::text = (bc.charge_id)::text)
54. 645.156 645.156 ↑ 1.0 1 71,684

Index Scan using doctors_pkey on doctors cdoc (cost=0.28..0.32 rows=1 width=30) (actual time=0.009..0.009 rows=1 loops=71,684)

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
55. 1,577.048 1,577.048 ↑ 1.0 1 71,684

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip (cost=0.56..0.64 rows=1 width=30) (actual time=0.022..0.022 rows=1 loops=71,684)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
56. 430.398 430.398 ↑ 1.0 1 71,733

Index Scan using insurance_plan_main_pkey on insurance_plan_main ipm (cost=0.29..0.33 rows=1 width=48) (actual time=0.006..0.006 rows=1 loops=71,733)

  • Index Cond: (plan_id = pip.plan_id)
57. 573.864 573.864 ↑ 1.0 1 71,733

Index Scan using tpa_master_pkey on tpa_master tpm (cost=0.28..0.31 rows=1 width=34) (actual time=0.008..0.008 rows=1 loops=71,733)

  • Index Cond: ((tpa_id)::text = (pip.sponsor_id)::text)
58. 0.006 0.013 ↑ 1.0 5 1

Hash (cost=1.15..1.15 rows=5 width=20) (actual time=0.013..0.013 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
59. 0.007 0.007 ↑ 1.0 5 1

Seq Scan on op_type_names otn (cost=0.00..1.15 rows=5 width=20) (actual time=0.006..0.007 rows=5 loops=1)

60. 0.006 0.013 ↑ 1.0 21 1

Hash (cost=1.63..1.63 rows=21 width=14) (actual time=0.012..0.013 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
61. 0.007 0.007 ↑ 1.0 21 1

Seq Scan on salutation_master sm (cost=0.00..1.63 rows=21 width=14) (actual time=0.005..0.007 rows=21 loops=1)

62. 1,578.126 1,578.126 ↑ 1.0 1 71,733

Index Scan using patient_registration_pkey on patient_registration pr1 (cost=0.56..4.56 rows=1 width=38) (actual time=0.022..0.022 rows=1 loops=71,733)

  • Index Cond: ((patient_id)::text = (isr.visit_id)::text)
63. 1,578.126 1,578.126 ↑ 1.0 1 71,733

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip1 (cost=0.56..0.64 rows=1 width=30) (actual time=0.022..0.022 rows=1 loops=71,733)

  • Index Cond: ((patient_id)::text = (pr1.patient_id)::text)
64. 26,971.608 26,971.608 ↑ 1.0 1 71,733

Index Scan using bill_charge_pkey on bill_charge bc1 (cost=0.56..2.64 rows=1 width=93) (actual time=0.376..0.376 rows=1 loops=71,733)

  • Index Cond: ((charge_id)::text = (isrd.billed_charge_id)::text)
  • Filter: ((charge_head)::text = 'LTDIA'::text)
65. 429.360 429.360 ↑ 1.0 1 71,560

Index Scan using insurance_plan_main_pkey on insurance_plan_main ipm1 (cost=0.29..0.33 rows=1 width=48) (actual time=0.006..0.006 rows=1 loops=71,560)

  • Index Cond: (plan_id = pip1.plan_id)
66. 572.480 572.480 ↑ 1.0 1 71,560

Index Scan using tpa_master_pkey on tpa_master tpm1 (cost=0.28..0.31 rows=1 width=34) (actual time=0.008..0.008 rows=1 loops=71,560)

  • Index Cond: ((tpa_id)::text = (pip1.sponsor_id)::text)
67. 214.680 214.680 ↑ 1.0 1 71,560

Index Scan using op_type_names_pkey on op_type_names otn1 (cost=0.13..0.17 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=71,560)

  • Index Cond: (op_type = pr1.op_type)
68. 2,647.720 2,647.720 ↑ 1.0 1 71,560

Index Scan using bill_pkey on bill b1 (cost=0.56..0.68 rows=1 width=25) (actual time=0.037..0.037 rows=1 loops=71,560)

  • Index Cond: ((bc1.bill_no)::text = (bill_no)::text)
69. 4,579.840 4,579.840 ↑ 1.0 1 71,560

Index Scan using bill_claim_bill_no_priority_key on bill_claim bccl1 (cost=0.56..0.64 rows=1 width=29) (actual time=0.064..0.064 rows=1 loops=71,560)

  • Index Cond: (((bill_no)::text = (b1.bill_no)::text) AND (priority = 1))
70. 20,895.520 20,895.520 ↑ 1.0 1 71,560

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc1 (cost=0.56..0.79 rows=1 width=28) (actual time=0.290..0.292 rows=1 loops=71,560)

  • Index Cond: ((charge_id)::text = (bc1.charge_id)::text)
71. 286.240 286.240 ↑ 1.0 1 71,560

Index Scan using center_pkey on hospital_center_master hcm (cost=0.14..0.19 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=71,560)

  • Index Cond: (center_id = isr.center_id)
  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 0
72. 387.176 387.176 ↑ 1.0 1 48,397

Index Scan using doctors_pkey on doctors cdoc1 (cost=0.28..0.32 rows=1 width=30) (actual time=0.008..0.008 rows=1 loops=48,397)

  • Index Cond: ((doctor_id)::text = (pr1.doctor)::text)
73. 338.779 338.779 ↑ 1.0 1 48,397

Index Scan using doctors_pkey on doctors d (cost=0.28..0.32 rows=1 width=30) (actual time=0.007..0.007 rows=1 loops=48,397)

  • Index Cond: ((doctor_id)::text = (tp.pres_doctor)::text)
74. 677.558 677.558 ↑ 1.0 1 48,397

Seq Scan on chargehead_constants cc (cost=0.00..2.95 rows=1 width=22) (actual time=0.009..0.014 rows=1 loops=48,397)

  • Filter: ((chargehead_id)::text = 'LTDIA'::text)
  • Rows Removed by Filter: 59
75. 193.588 193.588 ↑ 1.0 1 48,397

Index Scan using incoming_hospitals_pkey on incoming_hospitals ih (cost=0.14..0.18 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=48,397)

  • Index Cond: ((isr.orig_lab_name)::text = (hospital_id)::text)
76. 1.035 147,513.836 ↓ 5.0 5 1

Nested Loop Left Join (cost=48,153.70..1,878,293.70 rows=1 width=1,226) (actual time=81,044.332..147,513.836 rows=5 loops=1)

77. 0.102 147,512.746 ↓ 5.0 5 1

Nested Loop (cost=48,153.56..1,878,293.24 rows=1 width=646) (actual time=81,044.120..147,512.746 rows=5 loops=1)

78. 0.139 147,512.519 ↓ 5.0 5 1

Nested Loop Left Join (cost=48,153.56..1,878,290.26 rows=1 width=636) (actual time=81,044.066..147,512.519 rows=5 loops=1)

79. 0.128 147,512.330 ↓ 5.0 5 1

Nested Loop Left Join (cost=48,153.28..1,878,289.94 rows=1 width=621) (actual time=81,044.028..147,512.330 rows=5 loops=1)

80. 65.033 147,512.137 ↓ 5.0 5 1

Nested Loop (cost=48,153.00..1,878,289.62 rows=1 width=607) (actual time=81,043.980..147,512.137 rows=5 loops=1)

81. 200.312 147,089.304 ↓ 23,853.3 71,560 1

Nested Loop Left Join (cost=48,152.86..1,878,289.02 rows=3 width=593) (actual time=24,464.189..147,089.304 rows=71,560 loops=1)

  • Join Filter: ((bccl1_1.claim_id)::text = (bcc1_1.claim_id)::text)
82. 157.116 126,923.752 ↓ 23,853.3 71,560 1

Nested Loop Left Join (cost=48,152.30..1,878,286.56 rows=3 width=613) (actual time=24,462.598..126,923.752 rows=71,560 loops=1)

83. 161.947 123,045.516 ↓ 23,853.3 71,560 1

Nested Loop Left Join (cost=48,151.74..1,878,284.65 rows=3 width=599) (actual time=24,462.558..123,045.516 rows=71,560 loops=1)

84. 184.237 120,736.769 ↓ 23,853.3 71,560 1

Nested Loop Left Join (cost=48,151.18..1,878,282.61 rows=3 width=588) (actual time=24,462.517..120,736.769 rows=71,560 loops=1)

85. 177.772 120,337.852 ↓ 23,853.3 71,560 1

Nested Loop Left Join (cost=48,151.05..1,878,282.12 rows=3 width=572) (actual time=24,462.486..120,337.852 rows=71,560 loops=1)

86. 159.284 119,587.600 ↓ 23,853.3 71,560 1

Nested Loop Left Join (cost=48,150.77..1,878,281.18 rows=3 width=558) (actual time=24,462.448..119,587.600 rows=71,560 loops=1)

87. 196.107 118,998.956 ↓ 23,853.3 71,560 1

Nested Loop (cost=48,150.48..1,878,280.20 rows=3 width=518) (actual time=24,462.420..118,998.956 rows=71,560 loops=1)

88. 118.776 97,785.080 ↓ 1,668.2 71,733 1

Nested Loop Left Join (cost=48,149.92..1,878,166.69 rows=43 width=425) (actual time=24,458.176..97,785.080 rows=71,733 loops=1)

89. 215.779 96,016.445 ↓ 1,668.2 71,733 1

Nested Loop Left Join (cost=48,149.36..1,878,137.98 rows=43 width=427) (actual time=24,458.143..96,016.445 rows=71,733 loops=1)

90. 109.396 94,007.341 ↓ 1,668.2 71,733 1

Hash Left Join (cost=48,148.80..1,877,942.03 rows=43 width=389) (actual time=24,458.105..94,007.341 rows=71,733 loops=1)

  • Hash Cond: ((pd_1.salutation)::text = (sm_1.salutation_id)::text)
91. 121.689 93,897.924 ↓ 1,668.2 71,733 1

Hash Left Join (cost=48,146.49..1,877,939.54 rows=43 width=393) (actual time=24,458.060..93,897.924 rows=71,733 loops=1)

  • Hash Cond: (pr_1.op_type = otn_1.op_type)
92. 153.591 93,776.210 ↓ 1,668.2 71,733 1

Nested Loop Left Join (cost=48,145.18..1,877,937.84 rows=43 width=377) (actual time=24,457.987..93,776.210 rows=71,733 loops=1)

93. 148.914 93,048.755 ↓ 1,668.2 71,733 1

Nested Loop Left Join (cost=48,144.90..1,877,924.40 rows=43 width=363) (actual time=24,457.957..93,048.755 rows=71,733 loops=1)

94. 141.305 92,469.443 ↓ 1,668.2 71,733 1

Nested Loop Left Join (cost=48,144.61..1,877,910.30 rows=43 width=323) (actual time=24,457.925..92,469.443 rows=71,733 loops=1)

95. 161.055 90,679.406 ↓ 1,667.1 71,684 1

Nested Loop (cost=48,144.05..1,877,881.59 rows=43 width=325) (actual time=24,457.159..90,679.406 rows=71,684 loops=1)

96. 139.916 89,801.511 ↓ 1,667.1 71,684 1

Nested Loop Left Join (cost=48,143.77..1,877,867.94 rows=43 width=311) (actual time=24,457.117..89,801.511 rows=71,684 loops=1)

  • Join Filter: ((bccl_1.claim_id)::text = (bcc_1.claim_id)::text)
  • Rows Removed by Join Filter: 39
97. 147.436 72,385.751 ↓ 1,667.1 71,684 1

Nested Loop Left Join (cost=48,143.21..1,877,832.67 rows=43 width=331) (actual time=24,455.580..72,385.751 rows=71,684 loops=1)

98. 163.501 68,582.431 ↓ 1,667.1 71,684 1

Nested Loop (cost=48,142.65..1,877,805.35 rows=43 width=317) (actual time=24,454.515..68,582.431 rows=71,684 loops=1)

99. 126.593 65,838.306 ↓ 1,667.1 71,684 1

Hash Join (cost=48,142.09..1,877,776.15 rows=43 width=306) (actual time=24,453.833..65,838.306 rows=71,684 loops=1)

  • Hash Cond: (tp_1.outsource_dest_id = dom_1.outsource_dest_id)
100. 173.381 65,711.477 ↓ 246.3 71,684 1

Nested Loop (cost=48,120.85..1,877,752.53 rows=291 width=274) (actual time=24,453.546..65,711.477 rows=71,684 loops=1)

101. 215.936 63,889.364 ↓ 246.3 71,684 1

Nested Loop (cost=48,120.42..1,877,608.85 rows=291 width=255) (actual time=24,453.502..63,889.364 rows=71,684 loops=1)

102. 147.281 61,666.276 ↓ 246.3 71,684 1

Nested Loop (cost=48,119.86..1,877,414.55 rows=291 width=202) (actual time=24,453.454..61,666.276 rows=71,684 loops=1)

103. 209.108 41,199.312 ↓ 70.7 71,801 1

Nested Loop (cost=48,119.30..1,876,371.84 rows=1,015 width=113) (actual time=24,451.898..41,199.312 rows=71,801 loops=1)

  • Join Filter: (isrd_1.prescribed_id = tp1_1.prescribed_id)
104. 6,481.051 34,743.517 ↓ 8.8 71,801 1

Hash Join (cost=48,118.86..1,871,652.61 rows=8,168 width=113) (actual time=24,451.438..34,743.517 rows=71,801 loops=1)

  • Hash Cond: ((bac_1.activity_id)::integer = tp_1.prescribed_id)
105. 19,779.627 19,779.627 ↓ 1.0 13,323,201 1

Seq Scan on bill_activity_charge bac_1 (cost=0.00..1,740,032.44 rows=13,321,002 width=18) (actual time=4.369..19,779.627 rows=13,323,201 loops=1)

  • Filter: ((activity_code)::text = 'DIA'::text)
  • Rows Removed by Filter: 26600757
106. 89.444 8,482.839 ↓ 8.8 71,811 1

Hash (cost=47,852.17..47,852.17 rows=8,206 width=107) (actual time=8,482.839..8,482.839 rows=71,811 loops=1)

  • Buckets: 131072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 11400kB
107. 102.805 8,393.395 ↓ 8.8 71,811 1

Nested Loop (cost=0.99..47,852.17 rows=8,206 width=107) (actual time=18.183..8,393.395 rows=71,811 loops=1)

108. 55.938 722.715 ↓ 8.7 72,075 1

Nested Loop (cost=0.43..37,494.21 rows=8,314 width=50) (actual time=18.153..722.715 rows=72,075 loops=1)

109. 137.614 137.614 ↓ 8.2 18,247 1

Seq Scan on incoming_sample_registration isr_1 (cost=0.00..27,291.00 rows=2,228 width=44) (actual time=17.461..137.614 rows=18,247 loops=1)

  • Filter: (((date)::date >= '2020-01-01'::date) AND ((date)::date <= '2020-01-31'::date))
  • Rows Removed by Filter: 427728
110. 529.163 529.163 ↑ 1.8 4 18,247

Index Scan using idx_incoming_sample_registration_details_visit_id on incoming_sample_registration_details isrd_1 (cost=0.43..4.37 rows=7 width=24) (actual time=0.023..0.029 rows=4 loops=18,247)

  • Index Cond: ((incoming_visit_id)::text = (isr_1.incoming_visit_id)::text)
111. 7,567.875 7,567.875 ↑ 1.0 1 72,075

Index Scan using tp_out_des_idx on tests_prescribed tp_1 (cost=0.56..1.22 rows=1 width=57) (actual time=0.104..0.105 rows=1 loops=72,075)

  • Index Cond: (outsource_dest_prescribed_id = isrd_1.prescribed_id)
112. 6,246.687 6,246.687 ↑ 1.0 1 71,801

Index Scan using tests_prescribed_pkey on tests_prescribed tp1_1 (cost=0.43..0.55 rows=1 width=12) (actual time=0.087..0.087 rows=1 loops=71,801)

  • Index Cond: (prescribed_id = tp_1.outsource_dest_prescribed_id)
113. 20,319.683 20,319.683 ↑ 1.0 1 71,801

Index Scan using bill_charge_pkey on bill_charge bc_1 (cost=0.56..1.03 rows=1 width=99) (actual time=0.283..0.283 rows=1 loops=71,801)

  • Index Cond: ((charge_id)::text = (bac_1.charge_id)::text)
  • Filter: ((charge_head)::text = 'LTDIA'::text)
  • Rows Removed by Filter: 0
114. 2,007.152 2,007.152 ↑ 1.0 1 71,684

Index Scan using patient_registration_pkey on patient_registration pr_1 (cost=0.56..0.67 rows=1 width=53) (actual time=0.028..0.028 rows=1 loops=71,684)

  • Index Cond: ((patient_id)::text = (tp_1.pat_id)::text)
115. 1,648.732 1,648.732 ↑ 1.0 1 71,684

Index Scan using patient_details_pkey on patient_details pd_1 (cost=0.43..0.49 rows=1 width=49) (actual time=0.023..0.023 rows=1 loops=71,684)

  • Index Cond: ((mr_no)::text = (pr_1.mr_no)::text)
116. 0.036 0.236 ↑ 1.0 86 1

Hash (cost=18.44..18.44 rows=86 width=40) (actual time=0.236..0.236 rows=86 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
117. 0.050 0.200 ↑ 1.0 86 1

Hash Left Join (cost=12.69..18.44 rows=86 width=40) (actual time=0.148..0.200 rows=86 loops=1)

  • Hash Cond: ((dom_1.outsource_dest)::text = (hcm_3.center_id)::text)
118. 0.054 0.109 ↑ 1.0 86 1

Hash Left Join (cost=5.81..9.65 rows=86 width=32) (actual time=0.079..0.109 rows=86 loops=1)

  • Hash Cond: ((dom_1.outsource_dest)::text = (om_1.oh_id)::text)
119. 0.016 0.016 ↑ 1.0 86 1

Seq Scan on diag_outsource_master dom_1 (cost=0.00..3.58 rows=86 width=16) (actual time=0.010..0.016 rows=86 loops=1)

120. 0.022 0.039 ↑ 1.0 77 1

Hash (cost=3.31..3.31 rows=77 width=27) (actual time=0.039..0.039 rows=77 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
121. 0.017 0.017 ↑ 1.0 77 1

Seq Scan on outhouse_master om_1 (cost=0.00..3.31 rows=77 width=27) (actual time=0.007..0.017 rows=77 loops=1)

122. 0.021 0.041 ↑ 1.0 46 1

Hash (cost=5.38..5.38 rows=46 width=22) (actual time=0.041..0.041 rows=46 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
123. 0.020 0.020 ↑ 1.0 46 1

Seq Scan on hospital_center_master hcm_3 (cost=0.00..5.38 rows=46 width=22) (actual time=0.005..0.020 rows=46 loops=1)

124. 2,580.624 2,580.624 ↑ 1.0 1 71,684

Index Scan using bill_pkey on bill b_1 (cost=0.56..0.68 rows=1 width=25) (actual time=0.036..0.036 rows=1 loops=71,684)

  • Index Cond: ((bill_no)::text = (bc_1.bill_no)::text)
125. 3,655.884 3,655.884 ↑ 1.0 1 71,684

Index Scan using bill_claim_bill_no_priority_key on bill_claim bccl_1 (cost=0.56..0.64 rows=1 width=29) (actual time=0.051..0.051 rows=1 loops=71,684)

  • Index Cond: (((bill_no)::text = (b_1.bill_no)::text) AND (priority = 1))
126. 17,275.844 17,275.844 ↑ 1.0 1 71,684

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc_1 (cost=0.56..0.79 rows=1 width=28) (actual time=0.237..0.241 rows=1 loops=71,684)

  • Index Cond: ((charge_id)::text = (bc_1.charge_id)::text)
127. 716.840 716.840 ↑ 1.0 1 71,684

Index Scan using doctors_pkey on doctors cdoc_1 (cost=0.28..0.32 rows=1 width=30) (actual time=0.010..0.010 rows=1 loops=71,684)

  • Index Cond: ((doctor_id)::text = (pr_1.doctor)::text)
128. 1,648.732 1,648.732 ↑ 1.0 1 71,684

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip_1 (cost=0.56..0.64 rows=1 width=30) (actual time=0.022..0.023 rows=1 loops=71,684)

  • Index Cond: ((patient_id)::text = (pr_1.patient_id)::text)
129. 430.398 430.398 ↑ 1.0 1 71,733

Index Scan using insurance_plan_main_pkey on insurance_plan_main ipm_1 (cost=0.29..0.33 rows=1 width=48) (actual time=0.006..0.006 rows=1 loops=71,733)

  • Index Cond: (plan_id = pip_1.plan_id)
130. 573.864 573.864 ↑ 1.0 1 71,733

Index Scan using tpa_master_pkey on tpa_master tpm_1 (cost=0.28..0.31 rows=1 width=34) (actual time=0.008..0.008 rows=1 loops=71,733)

  • Index Cond: ((tpa_id)::text = (pip_1.sponsor_id)::text)
131. 0.006 0.025 ↑ 1.0 5 1

Hash (cost=1.15..1.15 rows=5 width=20) (actual time=0.025..0.025 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
132. 0.019 0.019 ↑ 1.0 5 1

Seq Scan on op_type_names otn_1 (cost=0.00..1.15 rows=5 width=20) (actual time=0.018..0.019 rows=5 loops=1)

133. 0.011 0.021 ↑ 1.0 21 1

Hash (cost=1.63..1.63 rows=21 width=14) (actual time=0.021..0.021 rows=21 loops=1)

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

Seq Scan on salutation_master sm_1 (cost=0.00..1.63 rows=21 width=14) (actual time=0.007..0.010 rows=21 loops=1)

135. 1,793.325 1,793.325 ↑ 1.0 1 71,733

Index Scan using patient_registration_pkey on patient_registration pr1_1 (cost=0.56..4.56 rows=1 width=38) (actual time=0.025..0.025 rows=1 loops=71,733)

  • Index Cond: ((patient_id)::text = (isr_1.visit_id)::text)
136. 1,649.859 1,649.859 ↑ 1.0 1 71,733

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip1_1 (cost=0.56..0.64 rows=1 width=30) (actual time=0.022..0.023 rows=1 loops=71,733)

  • Index Cond: ((patient_id)::text = (pr1_1.patient_id)::text)
137. 21,017.769 21,017.769 ↑ 1.0 1 71,733

Index Scan using bill_charge_pkey on bill_charge bc1_1 (cost=0.56..2.64 rows=1 width=93) (actual time=0.293..0.293 rows=1 loops=71,733)

  • Index Cond: ((charge_id)::text = (isrd_1.billed_charge_id)::text)
  • Filter: ((charge_head)::text = 'LTDIA'::text)
138. 429.360 429.360 ↑ 1.0 1 71,560

Index Scan using insurance_plan_main_pkey on insurance_plan_main ipm1_1 (cost=0.29..0.33 rows=1 width=48) (actual time=0.006..0.006 rows=1 loops=71,560)

  • Index Cond: (plan_id = pip1_1.plan_id)
139. 572.480 572.480 ↑ 1.0 1 71,560

Index Scan using tpa_master_pkey on tpa_master tpm1_1 (cost=0.28..0.31 rows=1 width=34) (actual time=0.008..0.008 rows=1 loops=71,560)

  • Index Cond: ((tpa_id)::text = (pip1_1.sponsor_id)::text)
140. 214.680 214.680 ↑ 1.0 1 71,560

Index Scan using op_type_names_pkey on op_type_names otn1_1 (cost=0.13..0.17 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=71,560)

  • Index Cond: (op_type = pr1_1.op_type)
141. 2,146.800 2,146.800 ↑ 1.0 1 71,560

Index Scan using bill_pkey on bill b1_1 (cost=0.56..0.68 rows=1 width=25) (actual time=0.030..0.030 rows=1 loops=71,560)

  • Index Cond: ((bc1_1.bill_no)::text = (bill_no)::text)
142. 3,721.120 3,721.120 ↑ 1.0 1 71,560

Index Scan using bill_claim_bill_no_priority_key on bill_claim bccl1_1 (cost=0.56..0.64 rows=1 width=29) (actual time=0.052..0.052 rows=1 loops=71,560)

  • Index Cond: (((bill_no)::text = (b1_1.bill_no)::text) AND (priority = 1))
143. 19,965.240 19,965.240 ↑ 1.0 1 71,560

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc1_1 (cost=0.56..0.79 rows=1 width=28) (actual time=0.278..0.279 rows=1 loops=71,560)

  • Index Cond: ((charge_id)::text = (bc1_1.charge_id)::text)
144. 357.800 357.800 ↓ 0.0 0 71,560

Index Scan using center_pkey on hospital_center_master hcm_2 (cost=0.14..0.19 rows=1 width=22) (actual time=0.005..0.005 rows=0 loops=71,560)

  • Index Cond: (center_id = isr_1.source_center_id)
  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 1
145. 0.065 0.065 ↑ 1.0 1 5

Index Scan using doctors_pkey on doctors cdoc1_1 (cost=0.28..0.32 rows=1 width=30) (actual time=0.013..0.013 rows=1 loops=5)

  • Index Cond: ((doctor_id)::text = (pr1_1.doctor)::text)
146. 0.050 0.050 ↑ 1.0 1 5

Index Scan using doctors_pkey on doctors d_1 (cost=0.28..0.32 rows=1 width=30) (actual time=0.010..0.010 rows=1 loops=5)

  • Index Cond: ((doctor_id)::text = (tp_1.pres_doctor)::text)
147. 0.125 0.125 ↑ 1.0 1 5

Seq Scan on chargehead_constants cc_1 (cost=0.00..2.95 rows=1 width=22) (actual time=0.019..0.025 rows=1 loops=5)

  • Filter: ((chargehead_id)::text = 'LTDIA'::text)
  • Rows Removed by Filter: 59
148. 0.055 0.055 ↑ 1.0 1 5

Index Scan using incoming_hospitals_pkey on incoming_hospitals ih_1 (cost=0.14..0.18 rows=1 width=17) (actual time=0.011..0.011 rows=1 loops=5)

  • Index Cond: ((isr_1.orig_lab_name)::text = (hospital_id)::text)
Planning time : 130.874 ms
Execution time : 304,357.430 ms