explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SVlK

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 567,446.068 ↑ 1.0 1 1

Aggregate (cost=17,116,763.11..17,116,763.12 rows=1 width=0) (actual time=567,446.068..567,446.068 rows=1 loops=1)

  • -> Index Only Scan using bill_claim_bill_no_idx on bill_claim bcl (cost=0.43..204080.4
2. 1,556.541 567,437.167 ↓ 11.3 72,990 1

Nested Loop Left Join (cost=16,380,175.47..17,116,682.69 rows=6,433 width=360) (actual time=560,572.075..567,437.167 rows=72,990 loops=1)

3. 1,697.536 565,880.626 ↓ 11.3 72,990 1

Nested Loop Left Join (cost=16,380,175.05..17,113,170.63 rows=6,433 width=384) (actual time=560,559.067..565,880.626 rows=72,990 loops=1)

4. 526,343.123 564,183.090 ↓ 11.3 72,990 1

Merge Left Join (cost=16,380,174.64..17,110,268.13 rows=6,433 width=385) (actual time=560,537.326..564,183.090 rows=72,990 loops=1)

  • Merge Cond: ((b.bill_no)::text = (all_insurance_remittance_details_view.bill_no)::text)
  • -> Merge Left Join (cost=4886105.74..5325489.15 rows=6433 width=371) (actual time=171758.008..173105.594 rows=44360 loops=
  • Merge Cond: ((b.bill_no)::text = (bcl.bill_no)::text)
5. 14,223.793 37,839.967 ↓ 6.9 44,360 1

Sort (cost=820,652.09..820,668.17 rows=6,433 width=343) (actual time=37,827.022..37,839.967 rows=44,360 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 17485kB
  • -> Hash Left Join (cost=811022.60..820245.16 rows=6433 width=343) (actual time=36696.066..37640.517 rows=44360
  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
  • -> Hash Right Join (cost=811021.13..820155.23 rows=6433 width=347) (actual time=36696.041..37628.120 row
  • Hash Cond: ((insurance_claim_resubmission.claim_id)::text = (icl.claim_id)::text)
  • -> HashAggregate (cost=64162.57..68006.33 rows=384376 width=23) (actual time=4750.624..4988.755 ro
  • -> Seq Scan on insurance_claim_resubmission (cost=0.00..58724.38 rows=1087638 width=23) (act
  • -> Hash (cost=746778.15..746778.15 rows=6433 width=329) (actual time=31945.208..31945.208 rows=443
  • Buckets: 1024 Batches: 1 Memory Usage: 12245kB
  • -> Nested Loop Left Join (cost=400408.70..746778.15 rows=6433 width=329) (actual time=3768.9
  • -> Nested Loop Left Join (cost=400408.27..742990.22 rows=6433 width=320) (actual time=
6. 15.687 23,616.174 ↓ 6.9 44,299 1

Hash Left Join (cost=400,407.41..721,144.92 rows=6,433 width=298) (actual time=3,768.887..23,616.174 rows=44,299 loops=1)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stpa.tpa_id)::text)
7. 26.403 23,600.487 ↓ 6.9 44,299 1

Hash Left Join (cost=400,374.17..721,086.97 rows=6,433 width=275) (actual time=3,768.742..23,600.487 rows=44,299 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
8. 28.230 23,574.084 ↓ 6.9 44,299 1

Hash Left Join (cost=400,340.93..720,970.22 rows=6,433 width=262) (actual time=3,768.553..23,574.084 rows=44,299 loops=1)

  • Hash Cond: ((pr.org_id)::text = (od.org_id)::text)
9. 23.879 23,545.854 ↓ 6.9 44,299 1

Hash Left Join (cost=400,281.86..720,822.70 rows=6,433 width=249) (actual time=3,768.071..23,545.854 rows=44,299 loops=1)

  • Hash Cond: (pr.visit_type = vn.visit_type)
10. 1,100.777 23,521.975 ↓ 6.9 44,299 1

Nested Loop Left Join (cost=400,280.77..720,733.15 rows=6,433 width=247) (actual time=3,768.053..23,521.975 rows=44,299 loops=1)

11. 40.834 22,421.198 ↓ 6.9 44,299 1

Hash Join (cost=400,279.63..711,692.55 rows=6,433 width=259) (actual time=3,768.008..22,421.198 rows=44,299 loops=1)

  • Hash Cond: (pr.center_id = hcm.center_id)
12. 7,027.095 22,380.364 ↑ 1.0 143,430 1

Nested Loop Left Join (cost=400,275.33..711,069.10 rows=147,953 width=251) (actual time=3,767.698..22,380.364 rows=143,430 loops=1)

13. 304.589 15,353.269 ↑ 1.0 143,430 1

Hash Left Join (cost=400,274.90..631,284.56 rows=147,953 width=218) (actual time=3,767.662..15,353.269 rows=143,430 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
14. 51.533 15,048.680 ↑ 1.0 143,430 1

Hash Left Join (cost=390,888.49..620,160.68 rows=147,953 width=209) (actual time=3,704.683..15,048.680 rows=143,430 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
15. 14,997.147 14,997.147 ↑ 1.0 143,430 1

Hash Join (cost=390,872.19..619,589.47 rows=147,953 width=161) (actual time=3,704.669..14,997.147 rows=143,430 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
16. 0.000 10,288.864 ↑ 1.0 143,430 1

Index Scan using idx_bill_open_date on bill b (cost=0.44..163,754.60 rows=147,953 width=92) (actual time=0.043..10,288.864 rows=143,430 loops=1)

  • Index Cond: ((date(open_date) >= '2018-01-01'::date) AND (date(open_date) <= '2018-01-31'::date))
  • Filter: (is_tpa AND (status <> 'X'::bpchar))
  • Rows Removed by Filter: 61890
17. 1,334.965 3,704.347 ↓ 1.0 4,937,321 1

Hash (cost=271,308.89..271,308.89 rows=4,936,789 width=69) (actual time=3,704.347..3,704.347 rows=4,937,321 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 26587kB
18. 2,369.382 2,369.382 ↓ 1.0 4,937,321 1

Seq Scan on patient_registration pr (cost=0.00..271,308.89 rows=4,936,789 width=69) (actual time=0.017..2,369.382 rows=4,937,321 loops=1)

19. 0.001 0.001 ↓ 0.0 0 1

Hash (cost=12.80..12.80 rows=280 width=48) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
20. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on store_retail_customers prc (cost=0.00..12.80 rows=280 width=48) (actual time=0.000..0.000 rows=0 loops=1)

21. 30.937 62.943 ↑ 1.0 225,307 1

Hash (cost=6,570.07..6,570.07 rows=225,307 width=9) (actual time=62.943..62.943 rows=225,307 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 9022kB
22. 32.006 32.006 ↑ 1.0 225,307 1

Seq Scan on incoming_sample_registration isr (cost=0.00..6,570.07 rows=225,307 width=9) (actual time=0.003..32.006 rows=225,307 loops=1)

23. 6,884.640 6,884.640 ↑ 1.0 1 143,430

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.53 rows=1 width=48) (actual time=0.048..0.048 rows=1 loops=143,430)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
24. 0.001 0.015 ↑ 1.0 1 1

Hash (cost=4.29..4.29 rows=1 width=16) (actual time=0.015..0.015 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
25. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on hospital_center_master hcm (cost=0.00..4.29 rows=1 width=16) (actual time=0.007..0.014 rows=1 loops=1)

  • Rows Removed by Filter: 22
26. 44.299 1,063.176 ↑ 1.0 1 44,299

Nested Loop Left Join (cost=1.14..1.40 rows=1 width=20) (actual time=0.024..0.024 rows=1 loops=44,299)

27. 0.000 664.485 ↑ 1.0 1 44,299

Nested Loop (cost=0.71..0.84 rows=1 width=20) (actual time=0.015..0.015 rows=1 loops=44,299)

28. 487.289 487.289 ↑ 1.0 1 44,299

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pri_plan (cost=0.43..0.54 rows=1 width=42) (actual time=0.011..0.011 rows=1 loops=44,299)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
  • Rows Removed by Filter: 0
29. 177.196 177.196 ↑ 1.0 1 44,299

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.28..0.30 rows=1 width=10) (actual time=0.003..0.004 rows=1 loops=44,299)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 0
30. 354.392 354.392 ↓ 0.0 0 44,299

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sec_plan (cost=0.43..0.54 rows=1 width=38) (actual time=0.008..0.008 rows=0 loops=44,299)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
31. 0.001 0.005 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=6) (actual time=0.005..0.005 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
32. 0.004 0.004 ↑ 1.0 4 1

Seq Scan on visit_type_names vn (cost=0.00..1.04 rows=4 width=6) (actual time=0.002..0.004 rows=4 loops=1)

33. 0.231 0.472 ↑ 1.0 1,381 1

Hash (cost=41.81..41.81 rows=1,381 width=29) (actual time=0.472..0.472 rows=1,381 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 83kB
34. 0.241 0.241 ↑ 1.0 1,381 1

Seq Scan on organization_details od (cost=0.00..41.81 rows=1,381 width=29) (actual time=0.002..0.241 rows=1,381 loops=1)

35. 0.085 0.183 ↑ 1.0 544 1

Hash (cost=26.44..26.44 rows=544 width=33) (actual time=0.183..0.183 rows=544 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
36. 0.098 0.098 ↑ 1.0 544 1

Seq Scan on tpa_master ptpa (cost=0.00..26.44 rows=544 width=33) (actual time=0.002..0.098 rows=544 loops=1)

37. 0.000 0.141 ↑ 1.0 544 1

Hash (cost=26.44..26.44 rows=544 width=33) (actual time=0.141..0.141 rows=544 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
38. 0.065 0.065 ↑ 1.0 544 1

Seq Scan on tpa_master stpa (cost=0.00..26.44 rows=544 width=33) (actual time=0.001..0.065 rows=544 loops=1)

39. 7,176.438 7,176.438 ↑ 1.0 1 44,299

Index Scan using patient_policy_details_patient_policy_id_key on patient_policy_details pppd (cost=0.43..0.50 rows=1 width=16) (actual time=0.162..0.162 rows=1 loops=44,299)

  • Index Cond: (patient_policy_id = pri_plan.patient_policy_id)
40. 442.990 442.990 ↑ 1.0 1 44,299

Index Scan using bill_claim_bill_no_idx on bill_claim bclm (cost=0.43..2.87 rows=1 width=28) (actual time=0.010..0.010 rows=1 loops=44,299)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
41. 532.320 532.320 ↑ 1.0 1 44,360

Index Scan using insurance_claim_id_index on insurance_claim icl (cost=0.43..0.58 rows=1 width=23) (actual time=0.012..0.012 rows=1 loops=44,360)

  • Index Cond: ((claim_id)::text = (bclm.claim_id)::text)
42. 0.005 0.011 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=14) (actual time=0.011..0.011 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
43. 0.006 0.006 ↑ 1.0 21 1

Seq Scan on salutation_master sm (cost=0.00..1.21 rows=21 width=14) (actual time=0.004..0.006 rows=21 loops=1)

44. 1,356.097 134,437.176 ↑ 1.0 4,239,428 1

Materialize (cost=4,065,453.65..4,494,040.25 rows=4,285,866 width=42) (actual time=129,847.186..134,437.176 rows=4,239,428 loops=1)

45. 2,494.331 133,081.079 ↑ 1.0 4,239,367 1

Group (cost=4,065,453.65..4,440,466.93 rows=4,285,866 width=968) (actual time=129,847.176..133,081.079 rows=4,239,367 loops=1)

46. 29,896.409 130,586.748 ↑ 1.0 4,284,492 1

Sort (cost=4,065,453.65..4,076,168.32 rows=4,285,866 width=968) (actual time=129,847.170..130,586.748 rows=4,284,492 loops=1)

  • Sort Key: bcl.bill_no, bcl_1.claim_id, sic.submission_batch_id, sic.main_visit_id, sic.status, sic.resubmission_count, sic.closure_type, sic.action_remarks, sic.attachment, sic.attachment_content_type, sic.resubmission_type, sic.comments, sic.account_group, sip.patient_id, sic.denial_remarks, sic.submission_id_with_correction, sic.payers_reference_no, bcl_2.claim_id, sic_1.submission_batch_id, sic_1.main_visit_id, sic_1.status, sic_1.resubmission_count, sic_1.closure_type, sic_1.action_remarks, sic_1.attachment, sic_1.attachment_content_type, sic_1.resubmission_type, sic_1.comments, sic_1.account_group, sic_1.denial_remarks, sic_1.submission_id_with_correction, sic_1.payers_reference_no, sic.op_type, sic_1.op_type
  • Sort Method: external sort Disk: 746280kB
47. 4,860.341 100,690.339 ↓ 1.0 4,285,912 1

Merge Left Join (cost=1,550,145.62..1,776,868.75 rows=4,285,866 width=968) (actual time=92,825.676..100,690.339 rows=4,285,912 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_1.bill_no)::text)
48. 2,140.156 3,640.707 ↓ 1.0 4,285,867 1

Merge Left Join (cost=286,493.85..501,300.33 rows=4,285,866 width=483) (actual time=1,650.896..3,640.707 rows=4,285,867 loops=1)

  • Heap Fetches: 1259
  • -> Sort (cost=286493.42..286495.15 rows=690 width=483) (actual time=1650.870..1661.356
  • Sort Key: bcl_2.bill_no
  • Sort Method: quicksort Memory: 10239kB
49. 1.469 1,500.551 ↓ 65.3 45,039 1

Nested Loop (cost=1.30..286,460.89 rows=690 width=483) (actual time=36.372..1,500.551 rows=45,039 loops=1)

50. 1.970 1,130.474 ↓ 59.8 46,076 1

Nested Loop (cost=0.86..286,046.42 rows=770 width=469) (actual time=36.339..1,130.474 rows=46,076 loops=1)

51. 679.074 679.074 ↓ 1.0 44,943 1

Index Scan using patient_insurance_plans_priority on patient_insurance_plans sip_1 (cost=0.43..12,489.67 rows=44,750 width=20) (actual time=36.276..679.074 rows=44,943 loops=1)

  • Index Cond: (priority = 2)
52. 449.430 449.430 ↑ 1.0 1 44,943

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic_1 (cost=0.43..6.10 rows=1 width=489) (actual time=0.009..0.010 rows=1 loops=44,943)

  • Index Cond: ((patient_id)::text = (sip_1.patient_id)::text)
  • Filter: (sip_1.plan_id = plan_id)
  • Rows Removed by Filter: 1
53. 368.608 368.608 ↑ 1.0 1 46,076

Index Scan using bill_claim_claim_idx on bill_claim bcl_2 (cost=0.43..0.53 rows=1 width=28) (actual time=0.007..0.008 rows=1 loops=46,076)

  • Index Cond: ((claim_id)::text = (sic_1.claim_id)::text)
54. 35,609.014 92,189.291 ↓ 61.1 4,285,238 1

Sort (cost=1,263,651.77..1,263,827.19 rows=70,169 width=499) (actual time=91,174.773..92,189.291 rows=4,285,238 loops=1)

  • Sort Key: bcl_1.bill_no
  • Sort Method: external sort Disk: 741088kB
55. 2,295.654 56,580.277 ↓ 60.4 4,240,131 1

Nested Loop (cost=1.30..1,258,003.67 rows=70,169 width=499) (actual time=0.061..56,580.277 rows=4,240,131 loops=1)

56. 6,469.758 26,530.849 ↓ 59.0 4,625,629 1

Merge Join (cost=0.86..1,215,825.70 rows=78,358 width=485) (actual time=0.039..26,530.849 rows=4,625,629 loops=1)

  • Join Filter: (sic.plan_id = sip.plan_id)
  • Rows Removed by Join Filter: 157712
57. 11,662.910 11,662.910 ↑ 1.0 4,552,596 1

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sip (cost=0.43..494,683.60 rows=4,552,816 width=20) (actual time=0.020..11,662.910 rows=4,552,596 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 44943
58. 1,460.080 8,398.181 ↓ 1.0 4,786,803 1

Materialize (cost=0.43..639,958.59 rows=4,786,054 width=489) (actual time=0.013..8,398.181 rows=4,786,803 loops=1)

59. 6,938.101 6,938.101 ↓ 1.0 4,786,803 1

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic (cost=0.43..627,993.45 rows=4,786,054 width=489) (actual time=0.009..6,938.101 rows=4,786,803 loops=1)

60. 27,753.774 27,753.774 ↑ 1.0 1 4,625,629

Index Scan using bill_claim_claim_idx on bill_claim bcl_1 (cost=0.43..0.53 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=4,625,629)

  • Index Cond: ((claim_id)::text = (sic.claim_id)::text)
61. 654.565 390,354.832 ↓ 2.1 4,014,179 1

Materialize (cost=11,494,068.90..11,779,895.77 rows=1,937,810 width=28) (actual time=361,887.761..390,354.832 rows=4,014,179 loops=1)

62. 3,820.894 389,700.267 ↓ 2.1 4,014,052 1

GroupAggregate (cost=11,494,068.90..11,755,673.15 rows=1,937,810 width=38) (actual time=361,887.758..389,700.267 rows=4,014,052 loops=1)

63. 149,019.073 385,879.373 ↑ 1.0 18,896,262 1

Sort (cost=11,494,068.90..11,542,514.13 rows=19,378,092 width=38) (actual time=361,887.743..385,879.373 rows=18,896,262 loops=1)

  • Sort Key: all_insurance_remittance_details_view.bill_no, all_insurance_remittance_details_view.payment_reference, all_insurance_remittance_details_view.reference_no
  • Sort Method: external merge Disk: 944840kB
64. 2,353.238 236,860.300 ↑ 1.0 18,897,946 1

Subquery Scan on all_insurance_remittance_details_view (cost=8,376,453.93..8,618,680.08 rows=19,378,092 width=38) (actual time=194,248.458..236,860.300 rows=18,897,946 loops=1)

65. 149,471.100 234,507.062 ↑ 1.0 18,897,946 1

Sort (cost=8,376,453.93..8,424,899.16 rows=19,378,092 width=44) (actual time=194,248.455..234,507.062 rows=18,897,946 loops=1)

  • Sort Key: bill_charge.bill_no
  • Sort Method: external merge Disk: 1019736kB
66. 1,464.180 85,035.962 ↑ 1.0 18,897,946 1

Append (cost=2,109,656.55..5,434,831.12 rows=19,378,092 width=44) (actual time=26,526.208..85,035.962 rows=18,897,946 loops=1)

67. 7,927.515 77,851.578 ↑ 1.0 18,862,754 1

Hash Join (cost=2,109,656.55..4,401,089.00 rows=18,873,224 width=44) (actual time=26,526.207..77,851.578 rows=18,862,754 loops=1)

68. 32,429.010 69,873.777 ↓ 1.0 18,873,225 1

Hash Join (cost=2,102,999.21..3,922,601.06 rows=18,873,224 width=28) (actual time=26,475.891..69,873.777 rows=18,873,225 loops=1)

  • Hash Cond: ((insurance_payment_allocation.charge_id)::text = (bill_charge.charge_id)::text)
69. 10,970.102 10,970.102 ↓ 1.0 18,873,225 1

Seq Scan on insurance_payment_allocation (cost=0.00..570,209.24 rows=18,873,224 width=24) (actual time=0.549..10,970.102 rows=18,873,225 loops=1)

70. 6,019.404 26,474.665 ↑ 1.0 26,507,931 1

Hash (cost=1,616,286.76..1,616,286.76 rows=26,510,276 width=24) (actual time=26,474.665..26,474.665 rows=26,507,931 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 46663kB
71. 20,455.261 20,455.261 ↑ 1.0 26,507,931 1

Seq Scan on bill_charge (cost=0.00..1,616,286.76 rows=26,510,276 width=24) (actual time=0.012..20,455.261 rows=26,507,931 loops=1)

72. 23.914 50.286 ↑ 1.0 155,793 1

Hash (cost=4,709.93..4,709.93 rows=155,793 width=20) (actual time=50.286..50.286 rows=155,793 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 8010kB
73. 26.372 26.372 ↑ 1.0 155,793 1

Seq Scan on insurance_remittance ir (cost=0.00..4,709.93 rows=155,793 width=20) (actual time=0.014..26.372 rows=155,793 loops=1)

74. 0.000 5,720.204 ↓ 0.0 0 1

Nested Loop (cost=256,835.68..616,849.75 rows=168,289 width=44) (actual time=5,720.204..5,720.204 rows=0 loops=1)

75. 5,686.826 5,720.204 ↓ 0.0 0 1

Merge Join (cost=256,835.12..413,138.45 rows=168,289 width=40) (actual time=5,720.204..5,720.204 rows=0 loops=1)

  • Merge Cond: (ipu.remittance_id = ipa.remittance_id)
76. 2.597 33.378 ↑ 1.4 222 1

Merge Join (cost=7.13..738.35 rows=301 width=24) (actual time=17.434..33.378 rows=222 loops=1)

  • Merge Cond: (ipu.remittance_id = ir_1.remittance_id)
77. 0.961 0.961 ↑ 1.0 301 1

Index Scan using idx_insurance_payment_unalloc_amount_remittance_id on insurance_payment_unalloc_amount ipu (cost=0.15..24.38 rows=301 width=10) (actual time=0.022..0.961 rows=301 loops=1)

78. 29.820 29.820 ↑ 10.8 14,429 1

Index Scan using insurance_remittance_pkey on insurance_remittance ir_1 (cost=0.42..7,282.06 rows=155,793 width=14) (actual time=0.009..29.820 rows=14,429 loops=1)

79. 636.685 5,499.017 ↑ 3.6 2,604,881 1

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa (cost=256,827.99..1,355,641.58 rows=9,436,612 width=24) (actual time=1,618.124..5,499.017 rows=2,604,881 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 1798
80. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..1.20 rows=1 width=24) (never executed)

  • Index Cond: ((charge_id)::text = (ipa.charge_id)::text)
81. 10.103 206.576 ↑ 9.6 35,192 1

Nested Loop (cost=8.13..223,111.45 rows=336,579 width=42) (actual time=0.203..206.576 rows=35,192 loops=1)

82. 16.087 20.513 ↑ 9.6 35,192 1

Nested Loop (cost=7.56..16,074.34 rows=336,579 width=52) (actual time=0.177..20.513 rows=35,192 loops=1)

  • Join Filter: (ipu_1.remittance_id = ipa_1.remittance_id)
  • -> Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa_1 (cost=0.44..36.24 rows=1177 width=14) (actual time=0.004..0.048 rows=159 loops=222)Index Cond: (remittance_id = ir_2.remittance_id)
83. 1.040 4.426 ↑ 1.4 222 1

Merge Join (cost=7.13..738.35 rows=301 width=46) (actual time=0.160..4.426 rows=222 loops=1)

  • Merge Cond: (ipu_1.remittance_id = ir_2.remittance_id)
84. 0.114 0.114 ↑ 1.0 301 1

Index Scan using idx_insurance_payment_unalloc_amount_remittance_id on insurance_payment_unalloc_amount ipu_1 (cost=0.15..24.38 rows=301 width=32) (actual time=0.006..0.114 rows=301 loops=1)

85. 3.272 3.272 ↑ 10.8 14,429 1

Index Scan using insurance_remittance_pkey on insurance_remittance ir_2 (cost=0.42..7,282.06 rows=155,793 width=14) (actual time=0.008..3.272 rows=14,429 loops=1)

86. 175.960 175.960 ↑ 1.0 1 35,192

Index Only Scan using bill_charge_pkey on bill_charge bc_1 (cost=0.56..0.61 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=35,192)

  • Index Cond: (charge_id = (ipa_1.charge_id)::text)
  • Heap Fetches: 0
87. 1,678.770 1,678.770 ↑ 1.0 1 72,990

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch firstsub (cost=0.42..0.44 rows=1 width=17) (actual time=0.023..0.023 rows=1 loops=72,990)

  • Index Cond: ((submission_batch_id)::text = (icl.submission_batch_id)::text)
  • Filter: (is_resubmission = 'N'::bpchar)
88. 1,386.810 1,386.810 ↑ 1.0 1 72,990

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch resub (cost=0.42..0.48 rows=1 width=17) (actual time=0.018..0.019 rows=1 loops=72,990)

  • Index Cond: ((submission_batch_id)::text = (max((insurance_claim_resubmission.resubmission_batch_id)::text)))
89.          

SubPlan (forIndex Scan)

90. 9.101 1,590.176 ↑ 2.1 159,459 1

Nested Loop (cost=1.00..255,986.11 rows=336,579 width=4) (actual time=1.041..1,590.176 rows=159,459 loops=1)

91. 16.435 145.944 ↑ 2.1 159,459 1

Nested Loop (cost=0.44..48,949.00 rows=336,579 width=14) (actual time=0.031..145.944 rows=159,459 loops=1)

92. 0.079 0.079 ↑ 1.0 301 1

Seq Scan on insurance_payment_unalloc_amount ipu_2 (cost=0.00..8.01 rows=301 width=4) (actual time=0.005..0.079 rows=301 loops=1)

93. 129.430 129.430 ↑ 2.2 530 301

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation insurance_payment_allocation_1 (cost=0.44..150.82 rows=1,177 width=14) (actual time=0.105..0.430 rows=530 loops=301)

  • Index Cond: (remittance_id = ipu_2.remittance_id)
94. 1,435.131 1,435.131 ↑ 1.0 1 159,459

Index Only Scan using bill_charge_pkey on bill_charge bc_2 (cost=0.56..0.61 rows=1 width=10) (actual time=0.009..0.009 rows=1 loops=159,459)

  • Index Cond: (charge_id = (insurance_payment_allocation_1.charge_id)::text)
  • Heap Fetches: 237