explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rBd8

Settings
# exclusive inclusive rows x rows loops node
1. 5,358.306 118,819.671 ↓ 1.1 3,667,246 1

Hash Left Join (cost=3,677,334.05..4,855,679.66 rows=3,307,923 width=454) (actual time=95,740.334..118,819.671 rows=3,667,246 loops=1)

  • Hash Cond: (b.account_group = agm.account_group_id)
  • Total runtime: 118960.016 ms
2. 1,328.209 113,458.285 ↓ 1.1 3,667,246 1

Hash Left Join (cost=3,677,332.92..4,661,338.04 rows=3,307,923 width=437) (actual time=95,740.265..113,458.285 rows=3,667,246 loops=1)

  • Hash Cond: ((max(insurance_remittance_details.remittance_id)) = ir.remittance_id)
3. 6,981.850 112,130.076 ↓ 1.1 3,667,246 1

Hash Left Join (cost=3,673,467.51..4,591,314.17 rows=3,307,923 width=425) (actual time=95,696.486..112,130.076 rows=3,667,246 loops=1)

  • Hash Cond: (ppip.patient_policy_id = pppd.patient_policy_id)
4. 1,162.085 105,148.226 ↓ 1.1 3,667,233 1

Hash Left Join (cost=3,622,929.70..4,099,587.20 rows=3,307,923 width=415) (actual time=94,801.549..105,148.226 rows=3,667,233 loops=1)

  • Hash Cond: ((pr.org_id)::text = (org.org_id)::text)
5. 707.342 103,986.141 ↓ 1.1 3,667,233 1

Hash Left Join (cost=3,622,906.02..4,054,079.58 rows=3,307,923 width=407) (actual time=94,801.258..103,986.141 rows=3,667,233 loops=1)

  • Hash Cond: ((pbcc.denial_code)::text = (pidc.denial_code)::text)
6. 1,042.904 103,278.799 ↓ 1.1 3,667,233 1

Hash Left Join (cost=3,622,900.66..4,037,339.43 rows=3,307,923 width=356) (actual time=94,801.161..103,278.799 rows=3,667,233 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
7. 6,097.512 102,235.895 ↓ 1.1 3,667,233 1

Hash Right Join (cost=3,622,899.23..3,991,854.06 rows=3,307,923 width=361) (actual time=94,801.113..102,235.895 rows=3,667,233 loops=1)

  • Hash Cond: ((claim_submissions.claim_id)::text = (picl.claim_id)::text)
8. 490.018 2,163.977 ↓ 1.3 1,416,010 1

Hash Left Join (cost=2,343.99..173,489.11 rows=1,128,994 width=18) (actual time=9.193..2,163.977 rows=1,416,010 loops=1)

  • Hash Cond: ((max((claim_submissions.submission_batch_id)::text)) = (priresub.submission_batch_id)::text)
9. 515.424 1,664.870 ↓ 1.3 1,416,010 1

GroupAggregate (cost=0.43..143,126.53 rows=1,128,994 width=19) (actual time=0.068..1,664.870 rows=1,416,010 loops=1)

10. 1,149.446 1,149.446 ↓ 1.0 1,615,650 1

Index Scan using claim_submissions_claim_id_idx on claim_submissions (cost=0.43..123,878.36 rows=1,591,646 width=19) (actual time=0.038..1,149.446 rows=1,615,650 loops=1)

11. 1.988 9.089 ↓ 1.0 9,250 1

Hash (cost=2,229.34..2,229.34 rows=9,138 width=17) (actual time=9.089..9.089 rows=9,250 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 443kB
12. 6.291 7.101 ↓ 1.0 9,250 1

Bitmap Heap Scan on insurance_submission_batch priresub (cost=171.11..2,229.34 rows=9,138 width=17) (actual time=0.990..7.101 rows=9,250 loops=1)

  • Recheck Cond: (is_resubmission = 'Y'::bpchar)
13. 0.810 0.810 ↓ 1.0 9,289 1

Bitmap Index Scan on idx_insurance_submission_batch_is_resubmission (cost=0.00..168.83 rows=9,138 width=0) (actual time=0.810..0.810 rows=9,289 loops=1)

  • Index Cond: (is_resubmission = 'Y'::bpchar)
14. 4,259.969 93,974.406 ↓ 1.1 3,667,233 1

Hash (cost=3,420,916.21..3,420,916.21 rows=3,307,923 width=363) (actual time=93,974.406..93,974.406 rows=3,667,233 loops=1)

  • Buckets: 16384 Batches: 128 (originally 64) Memory Usage: 35390kB
15. 6,330.931 89,714.437 ↓ 1.1 3,667,233 1

Hash Right Join (cost=3,251,980.01..3,420,916.21 rows=3,307,923 width=363) (actual time=83,202.709..89,714.437 rows=3,667,233 loops=1)

  • Hash Cond: ((insurance_remittance_details.claim_id)::text = (picl.claim_id)::text)
16. 761.842 809.007 ↓ 1.6 418,441 1

HashAggregate (cost=15,761.56..18,377.11 rows=261,555 width=14) (actual time=622.202..809.007 rows=418,441 loops=1)

17. 47.165 47.165 ↑ 1.0 556,164 1

Seq Scan on insurance_remittance_details (cost=0.00..12,979.71 rows=556,371 width=14) (actual time=0.014..47.165 rows=556,164 loops=1)

18. 6,638.727 82,574.499 ↓ 1.1 3,667,233 1

Hash (cost=3,039,810.41..3,039,810.41 rows=3,307,923 width=359) (actual time=82,574.499..82,574.499 rows=3,667,233 loops=1)

  • Buckets: 16384 Batches: 128 (originally 64) Memory Usage: 35390kB
19. 1,547.416 75,935.772 ↓ 1.1 3,667,233 1

Hash Left Join (cost=1,387,692.28..3,039,810.41 rows=3,307,923 width=359) (actual time=21,618.335..75,935.772 rows=3,667,233 loops=1)

  • Hash Cond: ((ppip.insurance_co)::text = (picm.insurance_co_id)::text)
20. 996.452 74,388.356 ↓ 1.1 3,667,233 1

Hash Left Join (cost=1,387,637.31..2,994,271.49 rows=3,307,923 width=337) (actual time=21,617.784..74,388.356 rows=3,667,233 loops=1)

  • Hash Cond: (pr.center_id = hcm.center_id)
21. 1,200.854 73,391.904 ↓ 1.1 3,667,233 1

Hash Left Join (cost=1,387,635.99..2,948,786.23 rows=3,307,923 width=319) (actual time=21,617.761..73,391.904 rows=3,667,233 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
22. 1,189.170 72,191.050 ↓ 1.1 3,667,233 1

Hash Left Join (cost=1,387,577.33..2,909,899.17 rows=3,307,923 width=306) (actual time=21,617.450..72,191.050 rows=3,667,233 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
23. 3,170.045 71,001.880 ↓ 1.1 3,667,233 1

Hash Left Join (cost=1,387,542.57..2,869,686.38 rows=3,307,923 width=294) (actual time=21,617.295..71,001.880 rows=3,667,233 loops=1)

  • Hash Cond: (ppip.plan_id = pipm.plan_id)
24. 10,713.179 67,805.974 ↓ 1.1 3,667,233 1

Hash Left Join (cost=1,384,495.19..2,796,345.64 rows=3,307,923 width=281) (actual time=21,591.349..67,805.974 rows=3,667,233 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (pr_1.patient_id)::text)
25. 10,150.320 52,110.137 ↓ 1.1 3,667,211 1

Hash Left Join (cost=1,097,717.25..2,175,905.43 rows=3,307,923 width=274) (actual time=16,608.587..52,110.137 rows=3,667,211 loops=1)

  • Hash Cond: ((pbcl.claim_id)::text = (picl.claim_id)::text)
26. 9,391.469 41,066.416 ↓ 1.1 3,667,211 1

Hash Left Join (cost=985,987.80..1,813,298.33 rows=3,307,923 width=162) (actual time=15,554.106..41,066.416 rows=3,667,211 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
27. 7,652.463 25,745.084 ↓ 1.1 3,667,211 1

Hash Left Join (cost=593,977.21..1,239,039.09 rows=3,307,923 width=90) (actual time=9,512.169..25,745.084 rows=3,667,211 loops=1)

  • Hash Cond: (((pbcl.claim_id)::text = (pbcc.claim_id)::text) AND ((bcc.charge_id)::text = (pbcc.charge_id)::text))
28. 5,075.979 15,471.777 ↓ 1.1 3,667,170 1

Hash Join (cost=375,603.56..860,501.57 rows=3,307,923 width=68) (actual time=6,812.185..15,471.777 rows=3,667,170 loops=1)

  • Hash Cond: ((bcc.bill_no)::text = (b.bill_no)::text)
29. 3,593.680 3,593.680 ↑ 1.0 4,380,166 1

Seq Scan on bill_charge bcc (cost=0.00..331,376.07 rows=4,415,962 width=36) (actual time=0.032..3,593.680 rows=4,380,166 loops=1)

  • Filter: ((status <> 'X'::bpchar) AND ((charge_head)::text <> ALL ('{PHMED,PHCMED,PHRET,PHCRET}'::text[])))
  • Rows Removed by Filter: 686295
30. 693.870 6,802.118 ↓ 1.0 2,087,885 1

Hash (cost=331,222.38..331,222.38 rows=2,084,655 width=45) (actual time=6,802.118..6,802.118 rows=2,087,885 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 20277kB
31. 3,458.046 6,108.248 ↓ 1.0 2,087,885 1

Hash Right Join (cost=206,237.96..331,222.38 rows=2,084,655 width=45) (actual time=2,135.474..6,108.248 rows=2,087,885 loops=1)

  • Hash Cond: ((pbcl.bill_no)::text = (b.bill_no)::text)
32. 553.983 553.983 ↑ 1.0 2,111,678 1

Seq Scan on bill_claim pbcl (cost=0.00..50,843.07 rows=2,166,013 width=23) (actual time=0.015..553.983 rows=2,111,678 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 2023
33. 535.268 2,096.219 ↓ 1.0 2,087,865 1

Hash (cost=163,892.77..163,892.77 rows=2,084,655 width=35) (actual time=2,096.219..2,096.219 rows=2,087,865 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 17613kB
34. 1,560.951 1,560.951 ↓ 1.0 2,087,865 1

Seq Scan on bill b (cost=0.00..163,892.77 rows=2,084,655 width=35) (actual time=0.027..1,560.951 rows=2,087,865 loops=1)

  • Filter: (is_tpa AND (status <> 'X'::bpchar))
  • Rows Removed by Filter: 695543
35. 1,200.304 2,620.844 ↑ 1.0 4,113,856 1

Hash (cost=121,971.26..121,971.26 rows=4,225,826 width=40) (actual time=2,620.844..2,620.844 rows=4,113,856 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 16350kB
36. 1,420.540 1,420.540 ↑ 1.0 4,113,856 1

Seq Scan on bill_charge_claim pbcc (cost=0.00..121,971.26 rows=4,225,826 width=40) (actual time=0.029..1,420.540 rows=4,113,856 loops=1)

37. 802.494 5,929.863 ↓ 1.0 2,034,735 1

Hash (cost=339,467.89..339,467.89 rows=2,007,576 width=81) (actual time=5,929.863..5,929.863 rows=2,034,735 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 28469kB
38. 2,696.288 5,127.369 ↓ 1.0 2,034,735 1

Hash Left Join (cost=152,549.55..339,467.89 rows=2,007,576 width=81) (actual time=1,411.587..5,127.369 rows=2,034,735 loops=1)

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
39. 1,019.973 1,019.973 ↓ 1.0 2,034,735 1

Seq Scan on patient_registration pr (cost=0.00..94,389.76 rows=2,007,576 width=50) (actual time=0.014..1,019.973 rows=2,034,735 loops=1)

40. 586.046 1,411.108 ↑ 1.0 2,068,678 1

Hash (cost=108,504.69..108,504.69 rows=2,068,869 width=42) (actual time=1,411.108..1,411.108 rows=2,068,678 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 17316kB
41. 825.062 825.062 ↑ 1.0 2,068,678 1

Seq Scan on patient_details pd (cost=0.00..108,504.69 rows=2,068,869 width=42) (actual time=0.009..825.062 rows=2,068,678 loops=1)

42. 464.247 893.401 ↑ 1.0 1,930,029 1

Hash (cost=49,526.31..49,526.31 rows=1,941,931 width=131) (actual time=893.401..893.401 rows=1,930,029 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 6751kB
43. 429.154 429.154 ↑ 1.0 1,930,029 1

Seq Scan on insurance_claim picl (cost=0.00..49,526.31 rows=1,941,931 width=131) (actual time=0.034..429.154 rows=1,930,029 loops=1)

44. 478.790 4,982.658 ↓ 1.0 2,034,739 1

Hash (cost=247,959.24..247,959.24 rows=2,007,576 width=25) (actual time=4,982.658..4,982.658 rows=2,034,739 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 29106kB
45. 2,473.531 4,503.868 ↓ 1.0 2,034,739 1

Hash Right Join (cost=139,699.18..247,959.24 rows=2,007,576 width=25) (actual time=1,520.250..4,503.868 rows=2,034,739 loops=1)

  • Hash Cond: ((ppip.patient_id)::text = (pr_1.patient_id)::text)
46. 510.311 510.311 ↑ 1.0 1,632,464 1

Seq Scan on patient_insurance_plans ppip (cost=0.00..44,130.03 rows=1,640,059 width=35) (actual time=0.014..510.311 rows=1,632,464 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 2322
47. 413.280 1,520.026 ↓ 1.0 2,034,735 1

Hash (cost=104,801.48..104,801.48 rows=2,007,576 width=9) (actual time=1,520.026..1,520.026 rows=2,034,735 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 20648kB
48. 730.409 1,106.746 ↓ 1.0 2,034,735 1

Merge Left Join (cost=270.45..104,801.48 rows=2,007,576 width=9) (actual time=5.214..1,106.746 rows=2,034,735 loops=1)

  • Merge Cond: ((pr_1.patient_id)::text = (spip.patient_id)::text)
49. 370.844 370.844 ↓ 1.0 2,034,735 1

Index Only Scan using patient_registration_pkey on patient_registration pr_1 (cost=0.43..99,484.67 rows=2,007,576 width=9) (actual time=0.051..370.844 rows=2,034,735 loops=1)

  • Heap Fetches: 191244
50. 3.571 5.493 ↑ 1.3 1,484 1

Sort (cost=269.94..274.60 rows=1,862 width=23) (actual time=5.155..5.493 rows=1,484 loops=1)

  • Sort Key: spip.patient_id
  • Sort Method: quicksort Memory: 164kB
51. 1.922 1.922 ↑ 1.3 1,484 1

Index Scan using patient_insurance_plans_priority_idx on patient_insurance_plans spip (cost=0.43..168.81 rows=1,862 width=23) (actual time=0.032..1.922 rows=1,484 loops=1)

  • Index Cond: (priority = 2)
52. 5.128 25.861 ↓ 1.0 30,314 1

Hash (cost=2,668.54..2,668.54 rows=30,307 width=21) (actual time=25.861..25.861 rows=30,314 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1485kB
53. 17.632 20.733 ↓ 1.0 30,314 1

Hash Left Join (cost=416.79..2,668.54 rows=30,307 width=21) (actual time=3.098..20.733 rows=30,314 loops=1)

  • Hash Cond: (pipm.category_id = picam.category_id)
54. 3.101 3.101 ↓ 1.0 30,314 1

Seq Scan on insurance_plan_main pipm (cost=0.00..1,494.07 rows=30,307 width=8) (actual time=0.006..3.101 rows=30,314 loops=1)

55. 0.000 3.080 ↓ 1.0 9,929 1

Hash (cost=293.02..293.02 rows=9,902 width=21) (actual time=3.080..3.080 rows=9,929 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 530kB
56. 1.617 1.617 ↓ 1.0 9,929 1

Seq Scan on insurance_category_master picam (cost=0.00..293.02 rows=9,902 width=21) (actual time=0.005..1.617 rows=9,929 loops=1)

57. 0.054 0.130 ↑ 2.0 328 1

Hash (cost=26.56..26.56 rows=656 width=28) (actual time=0.130..0.130 rows=328 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
58. 0.076 0.076 ↑ 2.0 328 1

Seq Scan on doctors dr (cost=0.00..26.56 rows=656 width=28) (actual time=0.005..0.076 rows=328 loops=1)

59. 0.132 0.298 ↓ 1.0 700 1

Hash (cost=49.96..49.96 rows=696 width=33) (actual time=0.298..0.298 rows=700 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
60. 0.166 0.166 ↓ 1.0 700 1

Seq Scan on tpa_master ptpa (cost=0.00..49.96 rows=696 width=33) (actual time=0.007..0.166 rows=700 loops=1)

61. 0.006 0.011 ↑ 1.0 14 1

Hash (cost=1.14..1.14 rows=14 width=22) (actual time=0.011..0.011 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
62. 0.005 0.005 ↑ 1.0 14 1

Seq Scan on hospital_center_master hcm (cost=0.00..1.14 rows=14 width=22) (actual time=0.004..0.005 rows=14 loops=1)

63. 0.297 0.539 ↑ 1.0 1,510 1

Hash (cost=36.10..36.10 rows=1,510 width=38) (actual time=0.539..0.539 rows=1,510 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 106kB
64. 0.242 0.242 ↑ 1.0 1,510 1

Seq Scan on insurance_company_master picm (cost=0.00..36.10 rows=1,510 width=38) (actual time=0.004..0.242 rows=1,510 loops=1)

65. 0.004 0.022 ↓ 1.1 20 1

Hash (cost=1.19..1.19 rows=19 width=13) (actual time=0.022..0.022 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
66. 0.018 0.018 ↓ 1.1 20 1

Seq Scan on salutation_master sm (cost=0.00..1.19 rows=19 width=13) (actual time=0.010..0.018 rows=20 loops=1)

67. 0.019 0.072 ↑ 1.0 105 1

Hash (cost=4.05..4.05 rows=105 width=60) (actual time=0.072..0.072 rows=105 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
68. 0.053 0.053 ↑ 1.0 105 1

Seq Scan on insurance_denial_codes pidc (cost=0.00..4.05 rows=105 width=60) (actual time=0.005..0.053 rows=105 loops=1)

69. 0.108 0.281 ↑ 1.0 608 1

Hash (cost=16.08..16.08 rows=608 width=24) (actual time=0.281..0.281 rows=608 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
70. 0.173 0.173 ↑ 1.0 608 1

Seq Scan on organization_details org (cost=0.00..16.08 rows=608 width=24) (actual time=0.007..0.173 rows=608 loops=1)

71. 358.554 601.692 ↓ 1.0 1,251,425 1

Hash (cost=27,722.58..27,722.58 rows=1,242,658 width=18) (actual time=601.692..601.692 rows=1,251,425 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 31934kB
72. 243.138 243.138 ↓ 1.0 1,251,425 1

Seq Scan on patient_policy_details pppd (cost=0.00..27,722.58 rows=1,242,658 width=18) (actual time=0.016..243.138 rows=1,251,425 loops=1)

73. 21.500 43.737 ↑ 1.2 76,056 1

Hash (cost=2,697.96..2,697.96 rows=93,396 width=20) (actual time=43.737..43.737 rows=76,056 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 3708kB
74. 22.237 22.237 ↑ 1.2 76,056 1

Seq Scan on insurance_remittance ir (cost=0.00..2,697.96 rows=93,396 width=20) (actual time=0.023..22.237 rows=76,056 loops=1)

75. 0.003 0.010 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=25) (actual time=0.010..0.010 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
76. 0.007 0.007 ↑ 1.0 6 1

Seq Scan on account_group_master agm (cost=0.00..1.06 rows=6 width=25) (actual time=0.006..0.007 rows=6 loops=1)