explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3oUi

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 680,490.376 ↑ 1.0 1 1

Aggregate (cost=17,117,803.91..17,117,803.92 rows=1 width=0) (actual time=680,490.376..680,490.376 rows=1 loops=1)

  • Index Cond: ((charge_id)::text = (ipa.charge_id)::text)
2. 366.418 680,483.471 ↓ 8.6 59,083 1

Nested Loop Left Join (cost=16,381,242.19..17,117,718.31 rows=6,848 width=360) (actual time=677,762.485..680,483.471 rows=59,083 loops=1)

3. 432.566 680,117.053 ↓ 8.6 59,083 1

Nested Loop Left Join (cost=16,381,241.77..17,113,979.68 rows=6,848 width=384) (actual time=677,761.659..680,117.053 rows=59,083 loops=1)

4. 607,462.863 679,684.487 ↓ 8.6 59,083 1

Merge Left Join (cost=16,381,241.35..17,110,889.93 rows=6,848 width=385) (actual time=677,752.658..679,684.487 rows=59,083 loops=1)

  • Merge Cond: ((b.bill_no)::text = (all_insurance_remittance_details_view.bill_no)::text)
  • -> Merge Left Join (cost=4900973.88..5340362.60 rows=6848 width=371) (actual time=271622.253..272648.278 rows=46647 loops=
  • Merge Cond: ((b.bill_no)::text = (bcl.bill_no)::text)
5. 14,458.984 72,221.624 ↓ 6.8 46,647 1

Sort (cost=835,520.23..835,537.35 rows=6,848 width=343) (actual time=72,209.854..72,221.624 rows=46,647 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 18263kB
  • -> Hash Left Join (cost=825855.37..835083.96 rows=6848 width=343) (actual time=71022.420..72016.929 rows=46647
  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
  • -> Hash Right Join (cost=825853.90..834988.33 rows=6848 width=347) (actual time=71022.397..72003.479 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=1558.884..1810.930 ro
  • -> Seq Scan on insurance_claim_resubmission (cost=0.00..58724.38 rows=1087638 width=23) (act
  • -> Hash (cost=761605.73..761605.73 rows=6848 width=329) (actual time=69463.462..69463.462 rows=466
  • Buckets: 1024 Batches: 1 Memory Usage: 12927kB
  • -> Nested Loop Left Join (cost=400408.70..761605.73 rows=6848 width=329) (actual time=6287.5
  • -> Nested Loop Left Join (cost=400408.27..757573.44 rows=6848 width=320) (actual time=
6. 29.869 57,762.640 ↓ 6.8 46,598 1

Hash Left Join (cost=400,407.41..735,325.84 rows=6,848 width=298) (actual time=6,281.010..57,762.640 rows=46,598 loops=1)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stpa.tpa_id)::text)
7. 50.539 57,732.327 ↓ 6.8 46,598 1

Hash Left Join (cost=400,374.17..735,266.29 rows=6,848 width=275) (actual time=6,280.559..57,732.327 rows=46,598 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
8. 53.179 57,681.314 ↓ 6.8 46,598 1

Hash Left Join (cost=400,340.93..735,144.16 rows=6,848 width=262) (actual time=6,280.074..57,681.314 rows=46,598 loops=1)

  • Hash Cond: ((pr.org_id)::text = (od.org_id)::text)
9. 50.115 57,626.908 ↓ 6.8 46,598 1

Hash Left Join (cost=400,281.86..734,990.93 rows=6,848 width=249) (actual time=6,278.823..57,626.908 rows=46,598 loops=1)

  • Hash Cond: (pr.visit_type = vn.visit_type)
10. 66.761 57,576.780 ↓ 6.8 46,598 1

Nested Loop Left Join (cost=400,280.77..734,895.68 rows=6,848 width=247) (actual time=6,278.787..57,576.780 rows=46,598 loops=1)

11. 89.567 54,667.541 ↓ 6.8 46,598 1

Hash Join (cost=400,279.63..725,271.86 rows=6,848 width=259) (actual time=6,277.803..54,667.541 rows=46,598 loops=1)

  • Hash Cond: (pr.center_id = hcm.center_id)
12. 191.876 54,577.955 ↑ 1.1 149,353 1

Nested Loop Left Join (cost=400,275.33..724,608.40 rows=157,515 width=251) (actual time=5,552.007..54,577.955 rows=149,353 loops=1)

13. 356.219 8,385.355 ↑ 1.1 149,353 1

Hash Left Join (cost=400,274.90..639,667.49 rows=157,515 width=218) (actual time=5,549.330..8,385.355 rows=149,353 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
14. 94.939 7,950.461 ↑ 1.1 149,353 1

Hash Left Join (cost=390,888.49..628,431.32 rows=157,515 width=209) (actual time=5,470.611..7,950.461 rows=149,353 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
15. 1,261.906 7,855.522 ↑ 1.1 149,353 1

Hash Join (cost=390,872.19..627,824.24 rows=157,515 width=161) (actual time=5,470.599..7,855.522 rows=149,353 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
16. 1,125.633 1,125.633 ↑ 1.1 149,353 1

Index Scan using idx_bill_open_date on bill b (cost=0.44..171,530.08 rows=157,515 width=92) (actual time=2.375..1,125.633 rows=149,353 loops=1)

  • Index Cond: ((date(open_date) >= '2018-05-01'::date) AND (date(open_date) <= '2018-05-31'::date))
  • Filter: (is_tpa AND (status <> 'X'::bpchar))
17. 1,483.471 5,467.983 ↓ 1.0 4,937,318 1

Hash (cost=271,308.89..271,308.89 rows=4,936,789 width=69) (actual time=5,467.983..5,467.983 rows=4,937,318 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 26587kB
18. 3,984.512 3,984.512 ↓ 1.0 4,937,318 1

Seq Scan on patient_registration pr (cost=0.00..271,308.89 rows=4,936,789 width=69) (actual time=0.011..3,984.512 rows=4,937,318 loops=1)

19. 0.000 0.000 ↓ 0.0 0 1

Hash (cost=12.80..12.80 rows=280 width=48) (actual time=0.000..0.000 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. 36.867 78.675 ↑ 1.0 225,307 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 9022kB
22. 41.808 41.808 ↑ 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..41.808 rows=225,307 loops=1)

23. 46,000.724 46,000.724 ↑ 1.0 1 149,353

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.53 rows=1 width=48) (actual time=0.308..0.308 rows=1 loops=149,353)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
24. 0.002 0.019 ↑ 1.0 1 1

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

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

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

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 22
26. 46.613 2,842.478 ↑ 1.0 1 46,598

Nested Loop Left Join (cost=1.14..1.40 rows=1 width=20) (actual time=0.060..0.061 rows=1 loops=46,598)

27. 93.202 2,096.910 ↑ 1.0 1 46,598

Nested Loop (cost=0.71..0.84 rows=1 width=20) (actual time=0.044..0.045 rows=1 loops=46,598)

28. 1,724.126 1,724.126 ↑ 1.0 1 46,598

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.037..0.037 rows=1 loops=46,598)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 0
29. 279.582 279.582 ↑ 1.0 1 46,597

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.28..0.30 rows=1 width=10) (actual time=0.006..0.006 rows=1 loops=46,597)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 0
30. 698.955 698.955 ↓ 0.0 0 46,597

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.015..0.015 rows=0 loops=46,597)

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

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

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

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

33. 0.686 1.227 ↑ 1.0 1,381 1

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

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

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

35. 0.255 0.474 ↑ 1.0 544 1

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

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

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

37. 0.270 0.444 ↑ 1.0 544 1

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

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

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

39. 0.000 3,914.232 ↑ 1.0 1 46,598

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.083..0.084 rows=1 loops=46,598)

  • Index Cond: (patient_policy_id = pri_plan.patient_policy_id)
40. 3,215.262 3,215.262 ↑ 1.0 1 46,598

Index Scan using bill_claim_bill_no_idx on bill_claim bclm (cost=0.43..2.73 rows=1 width=28) (actual time=0.069..0.069 rows=1 loops=46,598)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
41. 4,244.877 4,244.877 ↑ 1.0 1 46,647

Index Scan using insurance_claim_id_index on insurance_claim icl (cost=0.43..0.58 rows=1 width=23) (actual time=0.091..0.091 rows=1 loops=46,647)

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

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

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

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

44. 1,466.846 199,546.279 ↑ 1.0 4,240,031 1

Materialize (cost=4,065,453.65..4,494,040.25 rows=4,285,866 width=42) (actual time=192,982.729..199,546.279 rows=4,240,031 loops=1)

45. 4,403.223 198,079.433 ↑ 1.0 4,239,982 1

Group (cost=4,065,453.65..4,440,466.93 rows=4,285,866 width=968) (actual time=192,982.723..198,079.433 rows=4,239,982 loops=1)

46. 46,686.117 193,676.210 ↑ 1.0 4,285,107 1

Sort (cost=4,065,453.65..4,076,168.32 rows=4,285,866 width=968) (actual time=192,982.719..193,676.210 rows=4,285,107 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. 119,863.074 146,990.093 ↓ 1.0 4,285,909 1

Merge Left Join (cost=1,550,145.62..1,776,868.75 rows=4,285,866 width=968) (actual time=132,931.373..146,990.093 rows=4,285,909 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_1.bill_no)::text)
48. 21,009.382 27,127.019 ↑ 1.0 4,285,864 1

Merge Left Join (cost=286,493.85..501,300.33 rows=4,285,866 width=483) (actual time=19,438.039..27,127.019 rows=4,285,864 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_2.bill_no)::text)
49. 6,117.637 6,117.637 ↑ 1.0 4,285,864 1

Index Only Scan using bill_claim_bill_no_idx on bill_claim bcl (cost=0.43..204,080.42 rows=4,285,866 width=14) (actual time=0.533..6,117.637 rows=4,285,864 loops=1)

  • Heap Fetches: 710
50. 0.000 19,449.150 ↓ 130.5 90,076 1

Sort (cost=286,493.42..286,495.15 rows=690 width=483) (actual time=19,437.499..19,449.150 rows=90,076 loops=1)

  • Sort Key: bcl_2.bill_no
  • Sort Method: quicksort Memory: 10239kB
51. 88.950 19,176.522 ↓ 65.3 45,039 1

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

52. 75.303 12,775.160 ↓ 59.8 46,076 1

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

53. 3,441.599 3,441.599 ↓ 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=18.029..3,441.599 rows=44,943 loops=1)

  • Index Cond: (priority = 2)
54. 9,258.258 9,258.258 ↑ 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.151..0.206 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
55. 6,312.412 6,312.412 ↑ 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.136..0.137 rows=1 loops=46,076)

  • Index Cond: ((claim_id)::text = (sic_1.claim_id)::text)
56. 36,407.789 114,589.902 ↓ 61.1 4,285,235 1

Sort (cost=1,263,651.77..1,263,827.19 rows=70,169 width=499) (actual time=113,493.326..114,589.902 rows=4,285,235 loops=1)

  • Sort Key: bcl_1.bill_no
  • Sort Method: external sort Disk: 741088kB
57. 38,863.009 78,182.113 ↓ 60.4 4,240,128 1

Nested Loop (cost=1.30..1,258,003.67 rows=70,169 width=499) (actual time=0.398..78,182.113 rows=4,240,128 loops=1)

58. 6,696.489 39,319.104 ↓ 59.0 4,625,626 1

Merge Join (cost=0.86..1,215,825.70 rows=78,358 width=485) (actual time=0.046..39,319.104 rows=4,625,626 loops=1)

  • Merge Cond: ((sip.patient_id)::text = (sic.patient_id)::text)
  • Join Filter: (sic.plan_id = sip.plan_id)
  • Rows Removed by Join Filter: 157712
59. 15,551.580 15,551.580 ↑ 1.0 4,552,593 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.023..15,551.580 rows=4,552,593 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 44943
60. 17,071.035 17,071.035 ↓ 1.0 4,786,800 1

Materialize (cost=0.43..639,958.59 rows=4,786,054 width=489) (actual time=0.016..17,071.035 rows=4,786,800 loops=1)

61. 0.000 15,581.605 ↓ 1.0 4,786,800 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.012..15,581.605 rows=4,786,800 loops=1)

62. 37,005.008 37,005.008 ↑ 1.0 1 4,625,626

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

  • Index Cond: ((claim_id)::text = (sic.claim_id)::text)
63. 657.376 406,301.638 ↓ 2.1 4,014,856 1

Materialize (cost=11,480,267.47..11,765,649.21 rows=1,934,792 width=28) (actual time=377,970.292..406,301.638 rows=4,014,856 loops=1)

64. 3,866.505 405,644.262 ↓ 2.1 4,014,781 1

GroupAggregate (cost=11,480,267.47..11,741,464.31 rows=1,934,792 width=38) (actual time=377,970.287..405,644.262 rows=4,014,781 loops=1)

65. 148,830.237 401,777.757 ↑ 1.0 18,897,279 1

Sort (cost=11,480,267.47..11,528,637.25 rows=19,347,914 width=38) (actual time=377,970.271..401,777.757 rows=18,897,279 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
66. 2,343.534 252,947.520 ↑ 1.0 18,897,946 1

Subquery Scan on all_insurance_remittance_details_view (cost=8,367,725.98..8,609,574.91 rows=19,347,914 width=38) (actual time=210,341.378..252,947.520 rows=18,897,946 loops=1)

67. 149,979.388 250,603.986 ↑ 1.0 18,897,946 1

Sort (cost=8,367,725.98..8,416,095.77 rows=19,347,914 width=44) (actual time=210,341.374..250,603.986 rows=18,897,946 loops=1)

  • Sort Key: bill_charge.bill_no
  • Sort Method: external merge Disk: 1019736kB
68. 18,493.916 100,624.598 ↑ 1.0 18,897,946 1

Append (cost=2,109,656.55..5,430,900.92 rows=19,347,914 width=44) (actual time=32,601.330..100,624.598 rows=18,897,946 loops=1)

69. 7,969.134 82,130.682 ↓ 1.0 18,862,754 1

Hash Join (cost=2,109,656.55..4,398,356.90 rows=18,843,832 width=44) (actual time=32,601.328..82,130.682 rows=18,862,754 loops=1)

  • Hash Cond: (insurance_payment_allocation.remittance_id = ir.remittance_id)
70. 33,451.205 74,161.548 ↓ 1.0 18,873,225 1

Hash Join (cost=2,102,999.21..3,920,603.76 rows=18,843,832 width=28) (actual time=32,515.469..74,161.548 rows=18,873,225 loops=1)

  • Hash Cond: ((insurance_payment_allocation.charge_id)::text = (bill_charge.charge_id)::text)
71. 8,195.763 8,195.763 ↓ 1.0 18,873,225 1

Seq Scan on insurance_payment_allocation (cost=0.00..569,915.32 rows=18,843,832 width=24) (actual time=0.273..8,195.763 rows=18,873,225 loops=1)

72. 6,445.649 32,514.580 ↑ 1.0 26,507,871 1

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

  • Buckets: 131072 Batches: 32 Memory Usage: 46663kB
73. 26,068.931 26,068.931 ↑ 1.0 26,507,871 1

Seq Scan on bill_charge (cost=0.00..1,616,286.76 rows=26,510,276 width=24) (actual time=0.013..26,068.931 rows=26,507,871 loops=1)

74. 0.000 85.830 ↑ 1.0 155,793 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 8010kB
75. 58.013 58.013 ↑ 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.028..58.013 rows=155,793 loops=1)

76. 0.002 16,976.591 ↓ 0.0 0 1

Nested Loop (cost=256,502.91..616,283.53 rows=168,027 width=44) (actual time=16,976.591..16,976.591 rows=0 loops=1)

77. 246.298 16,976.589 ↓ 0.0 0 1

Merge Join (cost=256,502.35..412,727.49 rows=168,027 width=40) (actual time=16,976.589..16,976.589 rows=0 loops=1)

  • Merge Cond: (ipu.remittance_id = ipa.remittance_id)
78. 2.770 46.396 ↑ 1.4 222 1

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

  • Merge Cond: (ipu.remittance_id = ir_1.remittance_id)
79. 1.108 1.108 ↑ 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.756..1.108 rows=301 loops=1)

80. 42.518 42.518 ↑ 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=12.018..42.518 rows=14,429 loops=1)

81. 15,178.813 16,683.895 ↑ 3.6 2,604,881 1

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa (cost=256,495.22..1,354,794.45 rows=9,421,916 width=24) (actual time=1,556.103..16,683.895 rows=2,604,881 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 1798
82.          

SubPlan (forIndex Scan)

83. 63.718 1,505.082 ↑ 2.1 159,459 1

Nested Loop (cost=1.00..255,654.65 rows=336,055 width=4) (actual time=1.157..1,505.082 rows=159,459 loops=1)

84. 16.601 165.692 ↑ 2.1 159,459 1

Nested Loop (cost=0.44..48,928.07 rows=336,055 width=14) (actual time=0.037..165.692 rows=159,459 loops=1)

85. 0.096 0.096 ↑ 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.096 rows=301 loops=1)

86. 148.995 148.995 ↑ 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.78 rows=1,175 width=14) (actual time=0.082..0.495 rows=530 loops=301)

  • Index Cond: (remittance_id = ipu_2.remittance_id)
87. 1,275.672 1,275.672 ↑ 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.008..0.008 rows=1 loops=159,459)

  • Index Cond: (charge_id = (insurance_payment_allocation_1.charge_id)::text)
  • Heap Fetches: 237
88. 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)

89. 9.882 206.376 ↑ 9.5 35,192 1

Nested Loop (cost=8.13..222,781.35 rows=336,055 width=42) (actual time=0.214..206.376 rows=35,192 loops=1)

90. 5.391 20.534 ↑ 9.5 35,192 1

Nested Loop (cost=7.56..16,054.78 rows=336,055 width=52) (actual time=0.186..20.534 rows=35,192 loops=1)

  • Join Filter: (ipu_1.remittance_id = ipa_1.remittance_id)
91. 1.067 4.487 ↑ 1.4 222 1

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

  • Merge Cond: (ipu_1.remittance_id = ir_2.remittance_id)
92. 0.111 0.111 ↑ 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.007..0.111 rows=301 loops=1)

93. 3.309 3.309 ↑ 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.011..3.309 rows=14,429 loops=1)

94. 10.656 10.656 ↑ 7.4 159 222

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa_1 (cost=0.44..36.20 rows=1,175 width=14) (actual time=0.004..0.048 rows=159 loops=222)

  • Index Cond: (remittance_id = ir_2.remittance_id)
95. 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
96. 413.581 413.581 ↑ 1.0 1 59,083

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch firstsub (cost=0.42..0.44 rows=1 width=17) (actual time=0.007..0.007 rows=1 loops=59,083)

  • Index Cond: ((submission_batch_id)::text = (icl.submission_batch_id)::text)
  • Filter: (is_resubmission = 'N'::bpchar)
97. 236.332 236.332 ↓ 0.0 0 59,083

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch resub (cost=0.42..0.48 rows=1 width=17) (actual time=0.004..0.004 rows=0 loops=59,083)

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