explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K4Ni

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 81,935.527 ↓ 0.0 0 1

Hash Left Join (cost=3,567,243.95..3,884,797.20 rows=160,595 width=454) (actual time=81,935.527..81,935.527 rows=0 loops=1)

  • Hash Cond: (b.account_group = agm.account_group_id)
  • (actual time=0.006..0.146 rows=700 loops=1)
  • Total runtime: 81942.056 ms
2. 0.001 81,935.526 ↓ 0.0 0 1

Nested Loop Left Join (cost=3,567,242.82..3,875,361.11 rows=160,595 width=437) (actual time=81,935.526..81,935.526 rows=0 loops=1)

3. 130.420 81,935.525 ↓ 0.0 0 1

Hash Join (cost=3,567,242.39..3,794,952.24 rows=160,595 width=427) (actual time=81,935.525..81,935.525 rows=0 loops=1)

  • Hash Cond: ((max(insurance_remittance_details.remittance_id)) = ir.remittance_id)
4. 341.786 81,805.105 ↓ 2.3 1,096,106 1

Hash Left Join (cost=3,563,221.32..3,783,302.91 rows=481,785 width=415) (actual time=75,674.758..81,805.105 rows=1,096,106 loops=1)

  • Hash Cond: ((pr.org_id)::text = (org.org_id)::text)
5. 495.453 81,463.319 ↓ 2.3 1,096,106 1

Hash Left Join (cost=3,563,197.64..3,776,654.68 rows=481,785 width=407) (actual time=75,674.559..81,463.319 rows=1,096,106 loops=1)

  • Hash Cond: ((max((claim_submissions.submission_batch_id)::text)) = (priresub.submission_batch_id)::text)
6. 241.896 80,967.866 ↓ 2.3 1,096,106 1

Hash Left Join (cost=3,560,854.08..3,767,172.19 rows=481,785 width=431) (actual time=75,668.095..80,967.866 rows=1,096,106 loops=1)

  • Hash Cond: ((pbcc.denial_code)::text = (pidc.denial_code)::text)
7. 3,276.137 80,725.970 ↓ 2.3 1,096,106 1

Hash Right Join (cost=3,560,848.71..3,764,729.47 rows=481,785 width=380) (actual time=75,668.024..80,725.970 rows=1,096,106 loops=1)

  • Hash Cond: ((claim_submissions.claim_id)::text = (picl.claim_id)::text)
8. 584.086 1,850.229 ↓ 1.3 1,416,010 1

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

9. 1,266.143 1,266.143 ↓ 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.046..1,266.143 rows=1,615,650 loops=1)

10. 1,042.037 75,599.604 ↓ 2.3 1,096,106 1

Hash (cost=3,532,241.97..3,532,241.97 rows=481,785 width=358) (actual time=75,599.604..75,599.604 rows=1,096,106 loops=1)

  • Buckets: 16384 Batches: 16 (originally 8) Memory Usage: 35390kB
11. 294.885 74,557.567 ↓ 2.3 1,096,106 1

Hash Left Join (cost=3,223,982.19..3,532,241.97 rows=481,785 width=358) (actual time=70,695.377..74,557.567 rows=1,096,106 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
12. 3,378.087 74,262.682 ↓ 2.3 1,096,106 1

Hash Join (cost=3,223,980.77..3,525,616.00 rows=481,785 width=363) (actual time=70,695.317..74,262.682 rows=1,096,106 loops=1)

  • Hash Cond: ((insurance_remittance_details.claim_id)::text = (pbcl.claim_id)::text)
13. 497.857 538.317 ↓ 1.6 418,441 1

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

14. 40.460 40.460 ↑ 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.004..40.460 rows=556,164 loops=1)

15. 4,454.020 70,346.278 ↓ 1.1 3,581,996 1

Hash (cost=3,003,704.85..3,003,704.85 rows=3,334,748 width=369) (actual time=70,346.278..70,346.278 rows=3,581,996 loops=1)

  • Buckets: 16384 Batches: 64 Memory Usage: 18080kB
16. 1,072.353 65,892.258 ↓ 1.1 3,581,996 1

Hash Left Join (cost=1,459,132.13..3,003,704.85 rows=3,334,748 width=369) (actual time=22,103.985..65,892.258 rows=3,581,996 loops=1)

  • Hash Cond: (pr.center_id = hcm.center_id)
17. 1,137.296 64,819.905 ↓ 1.1 3,581,996 1

Hash Left Join (cost=1,459,130.81..2,957,850.75 rows=3,334,748 width=351) (actual time=22,103.966..64,819.905 rows=3,581,996 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
18. 1,121.219 63,682.609 ↓ 1.1 3,581,996 1

Hash Left Join (cost=1,459,072.15..2,918,648.82 rows=3,334,748 width=338) (actual time=22,103.702..63,682.609 rows=3,581,996 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
19. 14,263.112 62,561.390 ↓ 1.1 3,581,996 1

Hash Left Join (cost=1,459,037.39..2,878,110.21 rows=3,334,748 width=326) (actual time=22,103.534..62,561.390 rows=3,581,996 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (pr_1.patient_id)::text)
20. 8,703.952 48,298.278 ↓ 1.1 3,581,996 1

Hash Join (cost=1,091,049.94..2,159,508.93 rows=3,334,748 width=284) (actual time=15,391.558..48,298.278 rows=3,581,996 loops=1)

  • Hash Cond: ((pbcl.claim_id)::text = (picl.claim_id)::text)
21. 13,701.324 39,594.326 ↓ 1.1 3,667,130 1

Hash Left Join (cost=979,320.49..1,795,174.39 rows=3,334,748 width=162) (actual time=14,482.273..39,594.326 rows=3,667,130 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
22. 9,461.424 25,893.002 ↓ 1.1 3,667,130 1

Hash Left Join (cost=587,309.90..1,219,659.71 rows=3,334,748 width=90) (actual time=8,468.500..25,893.002 rows=3,667,130 loops=1)

  • Hash Cond: (((pbcl.claim_id)::text = (pbcc.claim_id)::text) AND ((bcc.charge_id)::text = (pbcc.charge_id)::text))
23. 16,431.578 16,431.578 ↓ 1.1 3,667,089 1

Hash Join (cost=368,933.92..840,087.49 rows=3,334,748 width=68) (actual time=6,091.573..16,431.578 rows=3,667,089 loops=1)

  • Hash Cond: ((bcc.bill_no)::text = (b.bill_no)::text)
24. 0.000 3,361.178 ↑ 1.0 4,380,178 1

Seq Scan on bill_charge bcc (cost=0.00..331,377.43 rows=4,415,980 width=36) (actual time=0.029..3,361.178 rows=4,380,178 loops=1)

  • Filter: ((status <> 'X'::bpchar) AND ((charge_head)::text <> ALL ('{PHMED,PHCMED,PHRET,PHCRET}'::text[])))
  • Rows Removed by Filter: 686300
25. 686.372 6,085.318 ↓ 1.3 2,087,680 1

Hash (cost=331,222.38..331,222.38 rows=1,622,524 width=58) (actual time=6,085.318..6,085.318 rows=2,087,680 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 23669kB
26. 2,863.891 5,398.946 ↓ 1.3 2,087,680 1

Hash Join (cost=206,237.96..331,222.38 rows=1,622,524 width=58) (actual time=2,133.731..5,398.946 rows=2,087,680 loops=1)

  • Hash Cond: ((pbcl.bill_no)::text = (b.bill_no)::text)
27. 454.898 454.898 ↑ 1.0 2,111,688 1

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

  • Filter: (priority = 1)
  • Rows Removed by Filter: 2023
28. 616.058 2,080.157 ↓ 1.0 2,087,875 1

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

  • Buckets: 65536 Batches: 8 Memory Usage: 17613kB
29. 1,464.099 1,464.099 ↓ 1.0 2,087,875 1

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

  • Filter: (is_tpa AND (status <> 'X'::bpchar))
  • Rows Removed by Filter: 695547
30. 1,088.817 2,266.530 ↑ 1.0 4,113,869 1

Hash (cost=121,972.79..121,972.79 rows=4,225,879 width=40) (actual time=2,266.530..2,266.530 rows=4,113,869 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 16350kB
31. 1,177.713 1,177.713 ↑ 1.0 4,113,869 1

Seq Scan on bill_charge_claim pbcc (cost=0.00..121,972.79 rows=4,225,879 width=40) (actual time=0.011..1,177.713 rows=4,113,869 loops=1)

32. 884.219 5,917.654 ↓ 1.0 2,034,743 1

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

  • Buckets: 32768 Batches: 8 Memory Usage: 28470kB
33. 2,746.265 5,033.435 ↓ 1.0 2,034,743 1

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

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
34. 1,048.885 1,048.885 ↓ 1.0 2,034,743 1

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

35. 529.214 1,238.285 ↑ 1.0 2,068,679 1

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

  • Buckets: 65536 Batches: 8 Memory Usage: 17316kB
36. 709.071 709.071 ↑ 1.0 2,068,679 1

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

37. 476.976 894.234 ↑ 1.0 1,930,038 1

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

  • Buckets: 32768 Batches: 16 Memory Usage: 6751kB
38. 417.258 417.258 ↑ 1.0 1,930,038 1

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

39. 594.246 6,564.155 ↓ 1.0 2,034,747 1

Hash (cost=321,326.75..321,326.75 rows=2,007,576 width=60) (actual time=6,564.155..6,564.155 rows=2,034,747 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 20888kB
40. 527.363 5,969.909 ↓ 1.0 2,034,747 1

Hash Left Join (cost=142,801.54..321,326.75 rows=2,007,576 width=60) (actual time=1,454.419..5,969.909 rows=2,034,747 loops=1)

  • Hash Cond: ((ppip.insurance_co)::text = (picm.insurance_co_id)::text)
41. 5,442.546 5,442.546 ↓ 1.0 2,034,747 1

Hash Left Join (cost=142,746.56..293,667.61 rows=2,007,576 width=38) (actual time=1,453.890..5,442.546 rows=2,034,747 loops=1)

  • Hash Cond: (ppip.plan_id = pipm.plan_id)
42. 2,299.351 4,352.439 ↓ 1.0 2,034,747 1

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

  • Hash Cond: ((ppip.patient_id)::text = (pr_1.patient_id)::text)
43. 571.367 571.367 ↑ 1.0 1,632,472 1

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

  • Filter: (priority = 1)
  • Rows Removed by Filter: 2322
44. 386.665 1,432.207 ↓ 1.0 2,034,743 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 20648kB
45. 712.224 1,045.542 ↓ 1.0 2,034,743 1

Merge Left Join (cost=270.45..104,801.48 rows=2,007,576 width=9) (actual time=4.984..1,045.542 rows=2,034,743 loops=1)

  • Merge Cond: ((pr_1.patient_id)::text = (spip.patient_id)::text)
46. 328.044 328.044 ↓ 1.0 2,034,743 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.022..328.044 rows=2,034,743 loops=1)

  • Heap Fetches: 191267
47. 3.653 5.274 ↑ 1.3 1,484 1

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

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

  • Index Cond: (priority = 2)
49. 4.610 21.502 ↓ 1.0 30,314 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 1485kB
50. 11.576 16.892 ↓ 1.0 30,314 1

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

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

52. 1.397 2.730 ↓ 1.0 9,929 1

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

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

54. 0.267 0.496 ↑ 1.0 1,510 1

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

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

56. 0.041 0.120 ↑ 2.0 328 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
57. 0.079 0.079 ↑ 2.0 328 1

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

58. 0.256 0.256 ↓ 1.0 700 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
59. 0.000 0.000 ↓ 0.0

Seq Scan on tpa_master ptpa (cost=0.00..49.96 rows=696 width=33) (actual rows= loops=)

60. 0.002 0.009 ↑ 1.0 14 1

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

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

62. 0.010 0.029 ↓ 1.1 20 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
63. 0.019 0.019 ↓ 1.1 20 1

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

64. 0.017 0.049 ↑ 1.0 105 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
65. 0.032 0.032 ↑ 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.032 rows=105 loops=1)

66. 1.261 6.451 ↓ 1.0 9,250 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 443kB
67. 4.460 5.190 ↓ 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.907..5.190 rows=9,250 loops=1)

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

  • Index Cond: (is_resubmission = 'Y'::bpchar)
69. 0.073 0.181 ↑ 1.0 608 1

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

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

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

71. 0.007 20.421 ↑ 31,132.0 1 1

Hash (cost=3,631.92..3,631.92 rows=31,132 width=20) (actual time=20.421..20.421 rows=1 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1kB
72. 20.414 20.414 ↑ 31,132.0 1 1

Seq Scan on insurance_remittance ir (cost=0.00..3,631.92 rows=31,132 width=20) (actual time=18.203..20.414 rows=1 loops=1)

  • Filter: (((mod_time)::date >= '2019-05-01'::date) AND ((mod_time)::date >= '2019-05-31'::date))
  • Rows Removed by Filter: 76055
73. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_ppd_patient_policy_id on patient_policy_details pppd (cost=0.43..0.49 rows=1 width=18) (never executed)

  • Index Cond: (patient_policy_id = ppip.patient_policy_id)
74. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.06..1.06 rows=6 width=25) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Seq Scan on account_group_master agm (cost=0.00..1.06 rows=6 width=25) (never executed)