explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0Ce

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 119,652.299 ↓ 1.1 3,667,206 1

Hash Left Join (cost=3,677,273.64..4,855,595.55 rows=3,307,840 width=454) (actual time=95,504.777..119,652.299 rows=3,667,206 loops=1)

  • Hash Cond: (b.account_group = agm.account_group_id)
  • Total runtime: 119798.667 ms
2. 1,593.679 114,222.183 ↓ 1.1 3,667,206 1

Hash Left Join (cost=3,677,272.50..4,661,258.81 rows=3,307,840 width=437) (actual time=95,504.691..114,222.183 rows=3,667,206 loops=1)

  • Hash Cond: ((max(insurance_remittance_details.remittance_id)) = ir.remittance_id)
3. 8,006.331 112,628.504 ↓ 1.1 3,667,206 1

Hash Left Join (cost=3,673,407.09..4,591,236.60 rows=3,307,840 width=425) (actual time=95,257.154..112,628.504 rows=3,667,206 loops=1)

  • Hash Cond: (ppip.patient_policy_id = pppd.patient_policy_id)
4. 1,190.823 104,622.173 ↓ 1.1 3,667,193 1

Hash Left Join (cost=3,622,869.29..4,099,519.60 rows=3,307,840 width=415) (actual time=93,843.697..104,622.173 rows=3,667,193 loops=1)

  • Hash Cond: ((pr.org_id)::text = (org.org_id)::text)
5. 739.458 103,431.350 ↓ 1.1 3,667,193 1

Hash Left Join (cost=3,622,845.61..4,054,013.12 rows=3,307,840 width=407) (actual time=93,843.415..103,431.350 rows=3,667,193 loops=1)

  • Hash Cond: ((pbcc.denial_code)::text = (pidc.denial_code)::text)
6. 1,091.715 102,691.892 ↓ 1.1 3,667,193 1

Hash Left Join (cost=3,622,840.25..4,037,273.40 rows=3,307,840 width=356) (actual time=93,843.342..102,691.892 rows=3,667,193 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
7. 5,493.604 101,600.177 ↓ 1.1 3,667,193 1

Hash Right Join (cost=3,622,838.82..3,991,789.17 rows=3,307,840 width=361) (actual time=93,843.307..101,600.177 rows=3,667,193 loops=1)

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

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

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

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

10. 1,340.521 1,340.521 ↓ 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.048..1,340.521 rows=1,615,650 loops=1)

11. 1.416 14.579 ↓ 1.0 9,250 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 443kB
12. 11.938 13.163 ↓ 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=1.422..13.163 rows=9,250 loops=1)

  • Recheck Cond: (is_resubmission = 'Y'::bpchar)
13. 1.225 1.225 ↓ 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=1.225..1.225 rows=9,289 loops=1)

  • Index Cond: (is_resubmission = 'Y'::bpchar)
14. 4,784.699 93,644.594 ↓ 1.1 3,667,193 1

Hash (cost=3,420,860.83..3,420,860.83 rows=3,307,840 width=363) (actual time=93,644.594..93,644.594 rows=3,667,193 loops=1)

  • Buckets: 16384 Batches: 128 (originally 64) Memory Usage: 35390kB
15. 6,123.335 88,859.895 ↓ 1.1 3,667,193 1

Hash Right Join (cost=3,251,928.74..3,420,860.83 rows=3,307,840 width=363) (actual time=82,541.523..88,859.895 rows=3,667,193 loops=1)

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

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

17. 51.850 51.850 ↑ 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.012..51.850 rows=556,164 loops=1)

18. 4,963.392 80,759.293 ↓ 1.1 3,667,193 1

Hash (cost=3,039,764.18..3,039,764.18 rows=3,307,840 width=359) (actual time=80,759.293..80,759.293 rows=3,667,193 loops=1)

  • Buckets: 16384 Batches: 128 (originally 64) Memory Usage: 35390kB
19. 1,587.206 75,795.901 ↓ 1.1 3,667,193 1

Hash Left Join (cost=1,387,684.96..3,039,764.18 rows=3,307,840 width=359) (actual time=22,744.235..75,795.901 rows=3,667,193 loops=1)

  • Hash Cond: ((ppip.insurance_co)::text = (picm.insurance_co_id)::text)
20. 1,070.740 74,208.695 ↓ 1.1 3,667,193 1

Hash Left Join (cost=1,387,629.98..2,994,226.40 rows=3,307,840 width=337) (actual time=22,743.714..74,208.695 rows=3,667,193 loops=1)

  • Hash Cond: (pr.center_id = hcm.center_id)
21. 1,246.135 73,137.955 ↓ 1.1 3,667,193 1

Hash Left Join (cost=1,387,628.67..2,948,742.29 rows=3,307,840 width=319) (actual time=22,743.684..73,137.955 rows=3,667,193 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
22. 1,229.166 71,891.820 ↓ 1.1 3,667,193 1

Hash Left Join (cost=1,387,570.01..2,909,856.20 rows=3,307,840 width=306) (actual time=22,743.399..71,891.820 rows=3,667,193 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
23. 70,662.654 70,662.654 ↓ 1.1 3,667,193 1

Hash Left Join (cost=1,387,535.25..2,869,644.42 rows=3,307,840 width=294) (actual time=22,743.247..70,662.654 rows=3,667,193 loops=1)

  • Hash Cond: (ppip.plan_id = pipm.plan_id)
24. 9,271.043 67,196.540 ↓ 1.1 3,667,193 1

Hash Left Join (cost=1,384,487.87..2,796,305.44 rows=3,307,840 width=281) (actual time=22,719.522..67,196.540 rows=3,667,193 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (pr_1.patient_id)::text)
25. 10,514.071 52,719.731 ↓ 1.1 3,667,171 1

Hash Left Join (cost=1,097,713.93..2,175,877.10 rows=3,307,840 width=274) (actual time=17,640.797..52,719.731 rows=3,667,171 loops=1)

  • Hash Cond: ((pbcl.claim_id)::text = (picl.claim_id)::text)
26. 10,265.761 42,205.660 ↓ 1.1 3,667,171 1

Hash Left Join (cost=985,984.48..1,813,275.45 rows=3,307,840 width=162) (actual time=16,567.945..42,205.660 rows=3,667,171 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
27. 7,165.298 25,923.579 ↓ 1.1 3,667,171 1

Hash Left Join (cost=593,973.89..1,239,019.66 rows=3,307,840 width=90) (actual time=10,358.951..25,923.579 rows=3,667,171 loops=1)

  • Hash Cond: (((pbcl.claim_id)::text = (pbcc.claim_id)::text) AND ((bcc.charge_id)::text = (pbcc.charge_id)::text))
28. 4,960.026 15,458.916 ↓ 1.1 3,667,130 1

Hash Join (cost=375,603.56..860,489.71 rows=3,307,840 width=68) (actual time=6,979.140..15,458.916 rows=3,667,130 loops=1)

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

Seq Scan on bill_charge bcc (cost=0.00..331,367.86 rows=4,415,852 width=36) (actual time=0.037..3,525.619 rows=4,380,125 loops=1)

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

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

  • Buckets: 65536 Batches: 8 Memory Usage: 20277kB
31. 3,237.846 6,376.574 ↓ 1.0 2,087,858 1

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

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

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

  • Filter: (priority = 1)
  • Rows Removed by Filter: 2023
33. 582.169 2,535.277 ↓ 1.0 2,087,838 1

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

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

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

  • Filter: (is_tpa AND (status <> 'X'::bpchar))
  • Rows Removed by Filter: 695539
35. 1,305.103 3,299.365 ↑ 1.0 4,113,811 1

Hash (cost=121,969.73..121,969.73 rows=4,225,773 width=40) (actual time=3,299.365..3,299.365 rows=4,113,811 loops=1)

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

Seq Scan on bill_charge_claim pbcc (cost=0.00..121,969.73 rows=4,225,773 width=40) (actual time=0.066..1,994.262 rows=4,113,811 loops=1)

37. 6,016.320 6,016.320 ↓ 1.0 2,034,713 1

Hash (cost=339,467.89..339,467.89 rows=2,007,576 width=81) (actual time=6,016.320..6,016.320 rows=2,034,713 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 28469kB
38. 0.000 5,202.890 ↓ 1.0 2,034,713 1

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

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

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

40. 603.543 1,416.635 ↑ 1.0 2,068,671 1

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

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

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

42. 514.807 1,019.484 ↑ 1.0 1,930,006 1

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

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

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

44. 519.954 5,078.625 ↓ 1.0 2,034,717 1

Hash (cost=247,955.24..247,955.24 rows=2,007,576 width=25) (actual time=5,078.625..5,078.625 rows=2,034,717 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 29106kB
45. 2,502.043 4,558.671 ↓ 1.0 2,034,717 1

Hash Right Join (cost=139,695.18..247,955.24 rows=2,007,576 width=25) (actual time=1,528.511..4,558.671 rows=2,034,717 loops=1)

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

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

  • Filter: (priority = 1)
  • Rows Removed by Filter: 2322
47. 444.496 1,528.329 ↓ 1.0 2,034,713 1

Hash (cost=104,797.48..104,797.48 rows=2,007,576 width=9) (actual time=1,528.329..1,528.329 rows=2,034,713 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 20648kB
48. 720.023 1,083.833 ↓ 1.0 2,034,713 1

Merge Left Join (cost=270.45..104,797.48 rows=2,007,576 width=9) (actual time=5.904..1,083.833 rows=2,034,713 loops=1)

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

Index Only Scan using patient_registration_pkey on patient_registration pr_1 (cost=0.43..99,480.67 rows=2,007,576 width=9) (actual time=0.044..357.631 rows=2,034,713 loops=1)

  • Heap Fetches: 191142
50. 4.177 6.179 ↑ 1.3 1,484 1

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

  • Sort Key: spip.patient_id
  • Sort Method: quicksort Memory: 164kB
51. 2.002 2.002 ↑ 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..2.002 rows=1,484 loops=1)

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

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

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

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

  • Hash Cond: (pipm.category_id = picam.category_id)
54. 3.132 3.132 ↓ 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.011..3.132 rows=30,314 loops=1)

55. 0.000 2.876 ↓ 1.0 9,929 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 530kB
56. 1.531 1.531 ↓ 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.014..1.531 rows=9,929 loops=1)

57. 0.056 0.131 ↑ 2.0 328 1

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

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

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

59. 0.122 0.275 ↓ 1.0 700 1

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

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

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

61. 0.002 0.010 ↑ 1.0 14 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
62. 0.008 0.008 ↑ 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.008 rows=14 loops=1)

63. 0.276 0.509 ↑ 1.0 1,510 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 106kB
64. 0.233 0.233 ↑ 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.005..0.233 rows=1,510 loops=1)

65. 0.004 0.015 ↓ 1.1 20 1

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

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

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

67. 0.020 0.054 ↑ 1.0 105 1

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

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

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

69. 0.161 0.274 ↑ 1.0 608 1

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

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

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

71. 920.587 1,151.856 ↓ 1.0 1,251,405 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 31933kB
72. 231.269 231.269 ↓ 1.0 1,251,405 1

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

73. 215.574 247.491 ↑ 1.2 76,056 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 3708kB
74. 31.917 31.917 ↑ 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.030..31.917 rows=76,056 loops=1)

75. 0.004 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.006 0.006 ↑ 1.0 6 1

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