explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WjIV

Settings
# exclusive inclusive rows x rows loops node
1. 2.375 612,341.908 ↓ 2.0 1,574 1

Sort (cost=51,491,332.33..51,491,334.32 rows=796 width=559) (actual time=612,341.761..612,341.908 rows=1,574 loops=1)

  • Sort Key: (date(b.open_date))
  • Sort Method: quicksort Memory: 813kB
2. 5.406 612,339.533 ↓ 2.0 1,574 1

Nested Loop Left Join (cost=49,849,975.32..51,491,293.98 rows=796 width=559) (actual time=611,931.679..612,339.533 rows=1,574 loops=1)

3. 85.139 612,234.965 ↓ 2.0 1,574 1

Merge Right Join (cost=49,849,974.90..51,490,864.75 rows=796 width=502) (actual time=611,931.089..612,234.965 rows=1,574 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (b.bill_no)::text)
4. 244.762 176,563.817 ↑ 20.2 372,912 1

Group (cost=6,897,222.20..7,556,179.10 rows=7,530,936 width=1,683) (actual time=175,918.510..176,563.817 rows=372,912 loops=1)

  • Group 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
5. 10,412.509 176,319.055 ↑ 20.2 373,172 1

Sort (cost=6,897,222.20..6,916,049.54 rows=7,530,936 width=1,683) (actual time=175,918.489..176,319.055 rows=373,172 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 merge Disk: 1386888kB
6. 7,497.495 165,906.546 ↑ 1.0 7,526,327 1

Merge Left Join (cost=1,711,915.72..2,102,403.71 rows=7,530,936 width=1,683) (actual time=140,154.486..165,906.546 rows=7,526,327 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_1.bill_no)::text)
7. 2,684.291 28,173.756 ↑ 1.0 7,526,325 1

Merge Left Join (cost=113,851.78..480,234.34 rows=7,530,936 width=841) (actual time=19,108.698..28,173.756 rows=7,526,325 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_2.bill_no)::text)
8. 6,351.846 6,351.846 ↑ 1.0 7,526,325 1

Index Only Scan using bill_claim_bill_no_priority_key on bill_claim bcl (cost=0.56..347,527.36 rows=7,530,936 width=15) (actual time=0.060..6,351.846 rows=7,526,325 loops=1)

  • Heap Fetches: 2801766
9. 284.588 19,137.619 ↓ 132.0 97,047 1

Sort (cost=113,851.22..113,853.06 rows=735 width=841) (actual time=19,108.627..19,137.619 rows=97,047 loops=1)

  • Sort Key: bcl_2.bill_no
  • Sort Method: quicksort Memory: 10957kB
10. 25.226 18,853.031 ↓ 66.0 48,525 1

Gather (cost=1,001.55..113,816.23 rows=735 width=841) (actual time=2.949..18,853.031 rows=48,525 loops=1)

  • Workers Planned: 1
  • Workers Launched: 0
11. 55.517 18,827.805 ↓ 112.3 48,525 1

Nested Loop (cost=1.55..112,742.73 rows=432 width=841) (actual time=0.653..18,827.805 rows=48,525 loops=1)

12. 65.711 5,682.068 ↓ 104.0 50,347 1

Nested Loop (cost=0.99..112,412.46 rows=484 width=826) (actual time=0.601..5,682.068 rows=50,347 loops=1)

13. 177.801 177.801 ↓ 1.8 48,996 1

Parallel Index Scan using patient_insurance_plans_priority on patient_insurance_plans sip_1 (cost=0.43..6,144.39 rows=27,216 width=20) (actual time=0.054..177.801 rows=48,996 loops=1)

  • Index Cond: (priority = 2)
14. 5,438.556 5,438.556 ↑ 1.0 1 48,996

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic_1 (cost=0.56..3.87 rows=1 width=846) (actual time=0.108..0.111 rows=1 loops=48,996)

  • Index Cond: ((patient_id)::text = (sip_1.patient_id)::text)
  • Filter: (sip_1.plan_id = plan_id)
  • Rows Removed by Filter: 1
15. 13,090.220 13,090.220 ↑ 1.0 1 50,347

Index Scan using bill_claim_claim_idx on bill_claim bcl_2 (cost=0.56..0.65 rows=1 width=29) (actual time=0.258..0.260 rows=1 loops=50,347)

  • Index Cond: ((claim_id)::text = (sic_1.claim_id)::text)
16. 813.466 130,235.295 ↓ 58.7 7,525,478 1

Materialize (cost=1,598,063.94..1,598,705.33 rows=128,277 width=857) (actual time=121,045.752..130,235.295 rows=7,525,478 loops=1)

17. 45,063.841 129,421.829 ↓ 58.3 7,476,933 1

Sort (cost=1,598,063.94..1,598,384.64 rows=128,277 width=857) (actual time=121,045.735..129,421.829 rows=7,476,933 loops=1)

  • Sort Key: bcl_1.bill_no
  • Sort Method: external merge Disk: 1358552kB
18. 0.000 84,357.988 ↓ 58.3 7,476,933 1

Gather (cost=1,001.67..1,552,415.34 rows=128,277 width=857) (actual time=2.752..84,357.988 rows=7,476,933 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 1,535.808 85,187.146 ↓ 46.6 2,492,311 3 / 3

Nested Loop (cost=1.68..1,538,587.64 rows=53,449 width=857) (actual time=4.025..85,187.146 rows=2,492,311 loops=3)

20. 6,167.588 39,786.122 ↓ 45.8 2,741,576 3 / 3

Merge Join (cost=1.12..1,497,717.98 rows=59,893 width=842) (actual time=3.458..39,786.122 rows=2,741,576 loops=3)

  • Merge Cond: ((sip.patient_id)::text = (sic.patient_id)::text)
  • Join Filter: (sic.plan_id = sip.plan_id)
  • Rows Removed by Join Filter: 75776
21. 13,127.813 13,127.813 ↑ 1.3 2,691,519 3 / 3

Parallel Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sip (cost=0.56..522,093.62 rows=3,366,184 width=20) (actual time=0.278..13,127.813 rows=2,691,519 loops=3)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 16332
22. 20,490.721 20,490.721 ↓ 1.0 8,458,524 3 / 3

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic (cost=0.56..832,620.64 rows=8,438,985 width=846) (actual time=0.058..20,490.721 rows=8,458,524 loops=3)

23. 43,865.216 43,865.216 ↑ 1.0 1 8,224,728 / 3

Index Scan using bill_claim_claim_idx on bill_claim bcl_1 (cost=0.56..0.65 rows=1 width=29) (actual time=0.015..0.016 rows=1 loops=8,224,728)

  • Index Cond: ((claim_id)::text = (sic.claim_id)::text)
24. 1.961 435,586.009 ↓ 2.0 1,574 1

Materialize (cost=42,952,752.71..43,689,909.73 rows=796 width=474) (actual time=435,468.989..435,586.009 rows=1,574 loops=1)

25. 93.743 435,584.048 ↓ 2.0 1,574 1

Merge Left Join (cost=42,952,752.71..43,689,907.74 rows=796 width=474) (actual time=435,468.965..435,584.048 rows=1,574 loops=1)

  • Merge Cond: ((b.bill_no)::text = (all_insurance_remittance_details_view.bill_no)::text)
26. 10.710 12,166.810 ↓ 1.5 1,176 1

Sort (cost=1,086,044.29..1,086,046.28 rows=796 width=352) (actual time=12,166.411..12,166.810 rows=1,176 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 588kB
27. 664.013 12,156.100 ↓ 1.5 1,176 1

Gather (cost=715,926.39..1,086,005.94 rows=796 width=352) (actual time=11,076.867..12,156.100 rows=1,176 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
28. 51.798 11,492.087 ↓ 1.2 392 3 / 3

Merge Left Join (cost=714,926.39..1,084,926.34 rows=332 width=352) (actual time=11,058.692..11,492.087 rows=392 loops=3)

  • Merge Cond: ((icl.claim_id)::text = (insurance_claim_resubmission.claim_id)::text)
29. 2.033 4,454.380 ↓ 1.2 392 3 / 3

Sort (cost=714,925.96..714,926.79 rows=332 width=334) (actual time=4,454.237..4,454.380 rows=392 loops=3)

  • Sort Key: icl.claim_id
  • Sort Method: quicksort Memory: 239kB
30. 0.169 4,452.347 ↓ 1.2 392 3 / 3

Nested Loop Left Join (cost=693,529.63..714,912.06 rows=332 width=334) (actual time=4,352.691..4,452.347 rows=392 loops=3)

31. 0.041 4,445.906 ↓ 1.2 392 3 / 3

Nested Loop Left Join (cost=693,529.21..714,758.44 rows=332 width=335) (actual time=4,351.883..4,445.906 rows=392 loops=3)

32. 0.265 4,439.201 ↓ 1.2 392 3 / 3

Nested Loop Left Join (cost=693,528.65..714,536.71 rows=332 width=326) (actual time=4,351.502..4,439.201 rows=392 loops=3)

33. 0.148 4,435.408 ↓ 1.2 392 3 / 3

Hash Left Join (cost=693,528.09..713,279.78 rows=332 width=312) (actual time=4,351.124..4,435.408 rows=392 loops=3)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
34. 0.205 4,435.241 ↓ 1.2 392 3 / 3

Nested Loop Left Join (cost=693,525.78..713,276.10 rows=332 width=316) (actual time=4,351.088..4,435.241 rows=392 loops=3)

35. 0.110 4,431.116 ↓ 1.2 392 3 / 3

Hash Left Join (cost=693,525.35..713,106.38 rows=332 width=307) (actual time=4,350.546..4,431.116 rows=392 loops=3)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stpa.tpa_id)::text)
36. 0.178 4,430.817 ↓ 1.2 392 3 / 3

Hash Left Join (cost=693,450.22..713,030.38 rows=332 width=283) (actual time=4,350.337..4,430.817 rows=392 loops=3)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
37. 0.173 4,430.331 ↓ 1.2 392 3 / 3

Nested Loop Left Join (cost=693,375.10..712,954.38 rows=332 width=269) (actual time=4,349.991..4,430.331 rows=392 loops=3)

38. 0.259 4,428.590 ↓ 1.2 392 3 / 3

Hash Left Join (cost=693,374.82..712,849.80 rows=332 width=256) (actual time=4,349.681..4,428.590 rows=392 loops=3)

  • Hash Cond: (pr.visit_type = vn.visit_type)
39. 0.131 4,428.315 ↓ 1.2 392 3 / 3

Nested Loop Left Join (cost=693,373.57..712,845.10 rows=332 width=254) (actual time=4,349.545..4,428.315 rows=392 loops=3)

40. 0.769 4,419.560 ↓ 1.2 392 3 / 3

Nested Loop (cost=693,372.18..712,295.26 rows=332 width=266) (actual time=4,348.917..4,419.560 rows=392 loops=3)

41. 0.788 4,417.537 ↑ 12.2 1,254 3 / 3

Nested Loop Left Join (cost=693,372.04..709,540.85 rows=15,247 width=252) (actual time=4,348.882..4,417.537 rows=1,254 loops=3)

42. 0.583 4,354.066 ↑ 12.2 1,254 3 / 3

Nested Loop Left Join (cost=693,371.61..702,012.46 rows=15,247 width=218) (actual time=4,348.825..4,354.066 rows=1,254 loops=3)

43. 0.684 4,349.722 ↑ 12.2 1,254 3 / 3

Merge Left Join (cost=693,371.19..693,448.85 rows=15,247 width=209) (actual time=4,348.789..4,349.722 rows=1,254 loops=3)

  • Merge Cond: ((b.visit_id)::text = (prc.customer_id)::text)
44. 4.079 4,348.994 ↑ 12.2 1,254 3 / 3

Sort (cost=693,341.40..693,379.52 rows=15,247 width=161) (actual time=4,348.735..4,348.994 rows=1,254 loops=3)

  • Sort Key: b.visit_id
  • Sort Method: quicksort Memory: 414kB
45. 2.707 4,344.915 ↑ 12.2 1,254 3 / 3

Nested Loop (cost=0.56..692,282.03 rows=15,247 width=161) (actual time=361.971..4,344.915 rows=1,254 loops=3)

46. 4,320.896 4,320.896 ↑ 12.2 1,254 3 / 3

Parallel Seq Scan on bill b (cost=0.00..630,391.40 rows=15,247 width=92) (actual time=361.640..4,320.896 rows=1,254 loops=3)

  • Filter: (is_tpa AND (status <> 'X'::bpchar) AND (date(open_date) >= '2019-01-01'::date) AND (date(open_date) <= '2019-01-01'::date))
  • Rows Removed by Filter: 3238873
47. 21.312 21.312 ↑ 1.0 1 3,761 / 3

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..4.06 rows=1 width=69) (actual time=0.017..0.017 rows=1 loops=3,761)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
48. 0.029 0.044 ↓ 0.0 0 3 / 3

Sort (cost=29.78..30.48 rows=280 width=48) (actual time=0.044..0.044 rows=0 loops=3)

  • Sort Key: prc.customer_id
  • Sort Method: quicksort Memory: 25kB
49. 0.015 0.015 ↓ 0.0 0 3 / 3

Seq Scan on store_retail_customers prc (cost=0.00..18.40 rows=280 width=48) (actual time=0.015..0.015 rows=0 loops=3)

50. 3.761 3.761 ↓ 0.0 0 3,761 / 3

Index Only Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.42..0.56 rows=1 width=9) (actual time=0.003..0.003 rows=0 loops=3,761)

  • Index Cond: (incoming_visit_id = (b.visit_id)::text)
  • Heap Fetches: 0
51. 62.683 62.683 ↑ 1.0 1 3,761 / 3

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.49 rows=1 width=49) (actual time=0.050..0.050 rows=1 loops=3,761)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
52. 1.254 1.254 ↓ 0.0 0 3,761 / 3

Index Scan using center_pkey on hospital_center_master hcm (cost=0.14..0.18 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=3,761)

  • Index Cond: (center_id = pr.center_id)
  • Filter: ((center_name)::text = 'NMCAbuDhabi'::text)
  • Rows Removed by Filter: 1
53. 0.000 8.624 ↑ 1.0 1 1,176 / 3

Nested Loop Left Join (cost=1.39..1.63 rows=1 width=20) (actual time=0.022..0.022 rows=1 loops=1,176)

54. 0.392 5.880 ↑ 1.0 1 1,176 / 3

Nested Loop (cost=0.83..0.95 rows=1 width=20) (actual time=0.014..0.015 rows=1 loops=1,176)

55. 4.312 4.312 ↑ 1.0 1 1,176 / 3

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pri_plan (cost=0.56..0.64 rows=1 width=42) (actual time=0.010..0.011 rows=1 loops=1,176)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
56. 1.176 1.176 ↑ 1.0 1 1,176 / 3

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.28..0.31 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=1,176)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 0
57. 2.744 2.744 ↓ 0.0 0 1,176 / 3

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sec_plan (cost=0.56..0.64 rows=1 width=38) (actual time=0.007..0.007 rows=0 loops=1,176)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
58. 0.005 0.016 ↑ 1.0 4 3 / 3

Hash (cost=1.12..1.12 rows=4 width=6) (actual time=0.016..0.016 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
59. 0.011 0.011 ↑ 1.0 4 3 / 3

Seq Scan on visit_type_names vn (cost=0.00..1.12 rows=4 width=6) (actual time=0.011..0.011 rows=4 loops=3)

60. 1.568 1.568 ↑ 1.0 1 1,176 / 3

Index Scan using organization_details_pkey on organization_details od (cost=0.28..0.32 rows=1 width=29) (actual time=0.004..0.004 rows=1 loops=1,176)

  • Index Cond: ((pr.org_id)::text = (org_id)::text)
61. 0.119 0.308 ↓ 1.0 675 3 / 3

Hash (cost=53.22..53.22 rows=674 width=34) (actual time=0.308..0.308 rows=675 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
62. 0.189 0.189 ↓ 1.0 675 3 / 3

Seq Scan on tpa_master ptpa (cost=0.00..53.22 rows=674 width=34) (actual time=0.010..0.189 rows=675 loops=3)

63. 0.110 0.189 ↓ 1.0 675 3 / 3

Hash (cost=53.22..53.22 rows=674 width=34) (actual time=0.189..0.189 rows=675 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
64. 0.079 0.079 ↓ 1.0 675 3 / 3

Seq Scan on tpa_master stpa (cost=0.00..53.22 rows=674 width=34) (actual time=0.003..0.079 rows=675 loops=3)

65. 3.920 3.920 ↑ 1.0 1 1,176 / 3

Index Scan using patient_policy_details_patient_policy_id_key on patient_policy_details pppd (cost=0.43..0.51 rows=1 width=17) (actual time=0.010..0.010 rows=1 loops=1,176)

  • Index Cond: (patient_policy_id = pri_plan.patient_policy_id)
66. 0.008 0.019 ↑ 1.0 21 3 / 3

Hash (cost=1.63..1.63 rows=21 width=14) (actual time=0.019..0.019 rows=21 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
67. 0.011 0.011 ↑ 1.0 21 3 / 3

Seq Scan on salutation_master sm (cost=0.00..1.63 rows=21 width=14) (actual time=0.009..0.011 rows=21 loops=3)

68. 3.528 3.528 ↑ 1.0 1 1,176 / 3

Index Scan using bill_claim_bill_no_priority_key on bill_claim bclm (cost=0.56..3.76 rows=1 width=29) (actual time=0.009..0.009 rows=1 loops=1,176)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
69. 6.664 6.664 ↑ 1.0 1 1,176 / 3

Index Scan using insurance_claim_id_index on insurance_claim icl (cost=0.56..0.67 rows=1 width=23) (actual time=0.017..0.017 rows=1 loops=1,176)

  • Index Cond: ((claim_id)::text = (bclm.claim_id)::text)
70. 6.272 6.272 ↑ 1.0 1 1,176 / 3

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch firstsub (cost=0.42..0.46 rows=1 width=17) (actual time=0.016..0.016 rows=1 loops=1,176)

  • Index Cond: ((submission_batch_id)::text = (icl.submission_batch_id)::text)
  • Filter: (is_resubmission = 'N'::bpchar)
71. 140.629 6,985.909 ↑ 11.5 74,948 3 / 3

GroupAggregate (cost=0.43..342,053.84 rows=859,811 width=46) (actual time=1.473..6,985.909 rows=74,948 loops=3)

  • Group Key: insurance_claim_resubmission.claim_id
72. 6,845.280 6,845.280 ↑ 18.2 130,035 3 / 3

Index Scan using insurance_claim_resubmission_claim_id_index on insurance_claim_resubmission (cost=0.43..304,436.44 rows=2,364,615 width=23) (actual time=0.063..6,845.280 rows=130,035 loops=3)

73. 74.866 423,323.495 ↑ 8.0 483,111 1

Materialize (cost=41,866,708.41..42,594,150.70 rows=3,879,692 width=170) (actual time=422,945.502..423,323.495 rows=483,111 loops=1)

74. 125.628 423,248.629 ↑ 8.0 483,111 1

GroupAggregate (cost=41,866,708.41..42,468,060.71 rows=3,879,692 width=388) (actual time=422,945.478..423,248.629 rows=483,111 loops=1)

  • Group Key: all_insurance_remittance_details_view.bill_no, all_insurance_remittance_details_view.payment_reference, all_insurance_remittance_details_view.reference_no
75. 31,257.431 423,123.001 ↑ 79.4 488,557 1

Sort (cost=41,866,708.41..41,963,700.72 rows=38,796,923 width=388) (actual time=422,945.442..423,123.001 rows=488,557 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: 415744kB
76. 843.629 391,865.570 ↑ 4.7 8,240,693 1

Subquery Scan on all_insurance_remittance_details_view (cost=30,790,172.61..32,051,072.61 rows=38,796,923 width=388) (actual time=388,129.626..391,865.570 rows=8,240,693 loops=1)

77. 31,997.465 391,021.941 ↑ 4.7 8,240,693 1

Sort (cost=30,790,172.61..30,887,164.92 rows=38,796,923 width=400) (actual time=388,129.617..391,021.941 rows=8,240,693 loops=1)

  • Sort Key: bill_charge.bill_no DESC
  • Sort Method: external merge Disk: 448680kB
78. 8,499.548 359,024.476 ↑ 4.7 8,240,693 1

Unique (cost=20,200,873.16..20,879,819.31 rows=38,796,923 width=400) (actual time=283,532.979..359,024.476 rows=8,240,693 loops=1)

79. 212,685.384 350,524.928 ↑ 1.0 38,200,977 1

Sort (cost=20,200,873.16..20,297,865.47 rows=38,796,923 width=400) (actual time=283,532.969..350,524.928 rows=38,200,977 loops=1)

  • Sort Key: bill_charge.bill_no, insurance_payment_allocation.payment_reference, ir.remittance_id, ir.received_date, ir.is_recovery, ir.reference_no
  • Sort Method: external merge Disk: 2075320kB
80. 2,829.090 137,839.544 ↑ 1.0 38,200,977 1

Append (cost=5,548,398.44..10,290,519.86 rows=38,796,923 width=400) (actual time=60,266.321..137,839.544 rows=38,200,977 loops=1)

81. 8,804.637 126,292.874 ↓ 1.0 38,165,785 1

Hash Join (cost=5,548,398.44..8,399,145.90 rows=38,160,955 width=44) (actual time=60,266.314..126,292.874 rows=38,165,785 loops=1)

  • Hash Cond: (insurance_payment_allocation.remittance_id = ir.remittance_id)
82. 38,695.760 117,171.971 ↓ 1.0 38,176,283 1

Hash Join (cost=5,512,424.25..8,262,996.44 rows=38,160,955 width=28) (actual time=59,947.611..117,171.971 rows=38,176,283 loops=1)

  • Hash Cond: ((insurance_payment_allocation.charge_id)::text = (bill_charge.charge_id)::text)
83. 18,559.868 18,559.868 ↓ 1.0 38,176,283 1

Seq Scan on insurance_payment_allocation (cost=0.00..1,932,063.65 rows=38,160,955 width=24) (actual time=0.678..18,559.868 rows=38,176,283 loops=1)

84. 13,753.715 59,916.343 ↓ 1.0 46,291,544 1

Hash (cost=3,737,387.80..3,737,387.80 rows=46,273,860 width=24) (actual time=59,916.343..59,916.343 rows=46,291,544 loops=1)

  • Buckets: 2097152 Batches: 64 Memory Usage: 57112kB
85. 46,162.628 46,162.628 ↓ 1.0 46,291,544 1

Seq Scan on bill_charge (cost=0.00..3,737,387.80 rows=46,273,860 width=24) (actual time=0.024..46,162.628 rows=46,291,544 loops=1)

86. 109.427 316.266 ↓ 1.0 431,759 1

Hash (cost=21,972.05..21,972.05 rows=430,835 width=20) (actual time=316.266..316.266 rows=431,759 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 26164kB
87. 206.839 206.839 ↓ 1.0 431,759 1

Seq Scan on insurance_remittance ir (cost=0.00..21,972.05 rows=430,835 width=20) (actual time=0.037..206.839 rows=431,759 loops=1)

88. 18.654 8,498.162 ↓ 0.0 0 1

Gather (cost=322,261.66..559,091.31 rows=211,989 width=44) (actual time=8,498.091..8,498.162 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
89. 0.002 8,479.508 ↓ 0.0 0 3 / 3

Nested Loop (cost=321,261.66..536,892.41 rows=88,329 width=44) (actual time=8,479.508..8,479.508 rows=0 loops=3)

90. 118.743 8,479.506 ↓ 0.0 0 3 / 3

Merge Join (cost=321,261.09..457,628.64 rows=88,329 width=40) (actual time=8,479.506..8,479.506 rows=0 loops=3)

  • Merge Cond: (ipa.remittance_id = ipu.remittance_id)
91. 5,992.583 8,335.927 ↑ 9.2 868,294 3 / 3

Parallel Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa (cost=320,912.82..2,237,536.10 rows=7,950,199 width=24) (actual time=2,378.265..8,335.927 rows=868,294 loops=3)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 608
92.          

SubPlan (for Parallel Index Scan)

93. 0.000 2,343.344 ↑ 2.6 161,592 3 / 3

Nested Loop (cost=1.13..319,852.31 rows=423,979 width=4) (actual time=1.833..2,343.344 rows=161,592 loops=3)

94. 16.755 579.347 ↑ 2.6 161,592 3 / 3

Nested Loop (cost=0.56..54,058.89 rows=423,979 width=14) (actual time=1.308..579.347 rows=161,592 loops=3)

95. 1.227 1.227 ↑ 1.0 301 3 / 3

Seq Scan on insurance_payment_unalloc_amount ipu_2 (cost=0.00..14.03 rows=301 width=4) (actual time=1.030..1.227 rows=301 loops=3)

96. 561.365 561.365 ↑ 2.7 537 903 / 3

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation insurance_payment_allocation_1 (cost=0.56..135.60 rows=1,465 width=14) (actual time=0.020..1.865 rows=537 loops=903)

  • Index Cond: (remittance_id = ipu_2.remittance_id)
97. 1,777.512 1,777.512 ↑ 1.0 1 484,776 / 3

Index Only Scan using bill_charge_pkey on bill_charge bc_2 (cost=0.56..0.63 rows=1 width=10) (actual time=0.011..0.011 rows=1 loops=484,776)

  • Index Cond: (charge_id = (insurance_payment_allocation_1.charge_id)::text)
  • Heap Fetches: 3081
98. 0.109 24.836 ↑ 1.4 222 3 / 3

Materialize (cost=27.24..1,030.34 rows=301 width=24) (actual time=1.032..24.836 rows=222 loops=3)

99. 1.983 24.727 ↑ 1.4 222 3 / 3

Merge Join (cost=27.24..1,029.59 rows=301 width=24) (actual time=1.015..24.727 rows=222 loops=3)

  • Merge Cond: (ir_1.remittance_id = ipu.remittance_id)
100. 22.494 22.494 ↑ 30.3 14,213 3 / 3

Index Scan using insurance_remittance_pkey on insurance_remittance ir_1 (cost=0.42..29,736.97 rows=430,835 width=14) (actual time=0.219..22.494 rows=14,213 loops=3)

101. 0.189 0.250 ↑ 1.0 301 3 / 3

Sort (cost=26.42..27.17 rows=301 width=10) (actual time=0.182..0.250 rows=301 loops=3)

  • Sort Key: ipu.remittance_id
  • Sort Method: quicksort Memory: 39kB
102. 0.061 0.061 ↑ 1.0 301 3 / 3

Seq Scan on insurance_payment_unalloc_amount ipu (cost=0.00..14.03 rows=301 width=10) (actual time=0.009..0.061 rows=301 loops=3)

103. 0.000 0.000 ↓ 0.0 0 / 3

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

  • Index Cond: ((charge_id)::text = (ipa.charge_id)::text)
104. 9.472 219.418 ↑ 12.0 35,192 1

Gather (cost=1,028.37..168,374.96 rows=423,979 width=42) (actual time=7.393..219.418 rows=35,192 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
105. 9.426 209.946 ↑ 5.0 35,192 1

Nested Loop (cost=28.37..124,977.06 rows=176,658 width=42) (actual time=0.987..209.946 rows=35,192 loops=1)

106. 5.229 24.560 ↑ 5.0 35,192 1

Nested Loop (cost=27.80..14,229.75 rows=176,658 width=52) (actual time=0.922..24.560 rows=35,192 loops=1)

  • Join Filter: (ipu_1.remittance_id = ipa_1.remittance_id)
107. 1.494 8.231 ↓ 1.8 222 1

Merge Join (cost=27.24..761.24 rows=125 width=46) (actual time=0.881..8.231 rows=222 loops=1)

  • Merge Cond: (ir_2.remittance_id = ipu_1.remittance_id)
108. 6.330 6.330 ↑ 12.6 14,213 1

Parallel Index Scan using insurance_remittance_pkey on insurance_remittance ir_2 (cost=0.42..22,197.36 rows=179,515 width=14) (actual time=0.029..6.330 rows=14,213 loops=1)

109. 0.252 0.407 ↑ 1.0 301 1

Sort (cost=26.42..27.17 rows=301 width=32) (actual time=0.375..0.407 rows=301 loops=1)

  • Sort Key: ipu_1.remittance_id
  • Sort Method: quicksort Memory: 48kB
110. 0.155 0.155 ↑ 1.0 301 1

Seq Scan on insurance_payment_unalloc_amount ipu_1 (cost=0.00..14.03 rows=301 width=32) (actual time=0.027..0.155 rows=301 loops=1)

111. 11.100 11.100 ↑ 9.2 159 222

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa_1 (cost=0.56..60.14 rows=1,465 width=14) (actual time=0.005..0.050 rows=159 loops=222)

  • Index Cond: (remittance_id = ir_2.remittance_id)
112. 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.63 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
113. 99.162 99.162 ↓ 0.0 0 1,574

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

  • Index Cond: ((submission_batch_id)::text = (max((insurance_claim_resubmission.resubmission_batch_id)::text)))
Planning time : 110.916 ms
Execution time : 612,787.510 ms