explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nYrU

Settings
# exclusive inclusive rows x rows loops node
1. 194.915 1,208,410.810 ↓ 65.2 57,211 1

Nested Loop Left Join (cost=88,386,893.25..89,875,807.92 rows=878 width=561) (actual time=1,206,577.757..1,208,410.810 rows=57,211 loops=1)

2. 2,678.768 1,208,158.684 ↓ 65.2 57,211 1

Merge Right Join (cost=88,386,892.83..89,874,203.71 rows=878 width=505) (actual time=1,206,577.715..1,208,158.684 rows=57,211 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (b.bill_no)::text)
3. 11,605.926 361,454.739 ↑ 1.0 8,149,188 1

Group (cost=10,461,306.71..11,179,008.22 rows=8,202,303 width=918) (actual time=345,982.690..361,454.739 rows=8,149,188 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
4. 120,097.391 349,848.813 ↑ 1.0 8,198,705 1

Sort (cost=10,461,306.71..10,481,812.46 rows=8,202,303 width=918) (actual time=345,982.684..349,848.813 rows=8,198,705 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: 1546056kB
5. 16,383.335 229,751.422 ↑ 1.0 8,199,028 1

Merge Left Join (cost=2,381,568.20..2,903,054.73 rows=8,202,303 width=918) (actual time=184,964.424..229,751.422 rows=8,199,028 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_1.bill_no)::text)
6. 6,053.716 30,391.572 ↑ 1.0 8,199,026 1

Merge Left Join (cost=162,668.11..661,134.82 rows=8,202,303 width=458) (actual time=12,265.722..30,391.572 rows=8,199,026 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_2.bill_no)::text)
7. 12,048.890 12,048.890 ↑ 1.0 8,199,026 1

Index Only Scan using bill_claim_bill_no_idx on bill_claim bcl (cost=0.43..477,948.02 rows=8,202,303 width=14) (actual time=0.766..12,048.890 rows=8,199,026 loops=1)

  • Heap Fetches: 3385978
8. 391.373 12,288.966 ↓ 115.2 98,991 1

Sort (cost=162,667.67..162,669.82 rows=859 width=458) (actual time=12,264.945..12,288.966 rows=98,991 loops=1)

  • Sort Key: bcl_2.bill_no
  • Sort Method: quicksort Memory: 11134kB
9. 7.906 11,897.593 ↓ 57.6 49,497 1

Gather (cost=1,001.30..162,625.81 rows=859 width=458) (actual time=19.808..11,897.593 rows=49,497 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 68.303 11,889.687 ↓ 46.1 16,499 3 / 3

Nested Loop (cost=1.30..161,539.91 rows=358 width=458) (actual time=15.762..11,889.687 rows=16,499 loops=3)

11. 77.775 8,437.318 ↓ 42.4 17,178 3 / 3

Nested Loop (cost=0.87..161,321.41 rows=405 width=444) (actual time=15.045..8,437.318 rows=17,178 loops=3)

12. 1,389.110 1,389.110 ↑ 1.4 16,716 3 / 3

Parallel Index Scan using patient_insurance_plans_priority on patient_insurance_plans sip_1 (cost=0.43..11,995.77 rows=22,654 width=20) (actual time=14.028..1,389.110 rows=16,716 loops=3)

  • Index Cond: (priority = 2)
13. 6,970.433 6,970.433 ↑ 1.0 1 50,147 / 3

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic_1 (cost=0.43..6.58 rows=1 width=464) (actual time=0.300..0.417 rows=1 loops=50,147)

  • Index Cond: ((patient_id)::text = (sip_1.patient_id)::text)
  • Filter: (sip_1.plan_id = plan_id)
  • Rows Removed by Filter: 1
14. 3,384.066 3,384.066 ↑ 1.0 1 51,534 / 3

Index Scan using bill_claim_claim_idx on bill_claim bcl_2 (cost=0.43..0.53 rows=1 width=28) (actual time=0.195..0.197 rows=1 loops=51,534)

  • Index Cond: ((claim_id)::text = (sic_1.claim_id)::text)
15. 1,582.777 182,976.515 ↓ 58.9 8,198,144 1

Materialize (cost=2,218,900.09..2,219,595.83 rows=139,148 width=474) (actual time=172,698.690..182,976.515 rows=8,198,144 loops=1)

16. 99,825.605 181,393.738 ↓ 58.6 8,148,627 1

Sort (cost=2,218,900.09..2,219,247.96 rows=139,148 width=474) (actual time=172,698.667..181,393.738 rows=8,148,627 loops=1)

  • Sort Key: bcl_1.bill_no
  • Sort Method: external merge Disk: 1516496kB
17. 0.000 81,568.133 ↓ 58.6 8,148,627 1

Gather (cost=1,001.30..2,177,049.00 rows=139,148 width=474) (actual time=3.543..81,568.133 rows=8,148,627 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 1,850.423 116,087.044 ↓ 46.8 2,716,209 3 / 3

Nested Loop (cost=1.30..2,162,134.20 rows=57,978 width=474) (actual time=103.266..116,087.044 rows=2,716,209 loops=3)

19. 10,725.746 66,364.306 ↓ 45.7 2,992,020 3 / 3

Merge Join (cost=0.87..2,126,783.29 rows=65,525 width=460) (actual time=102.756..66,364.306 rows=2,992,020 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: 81091
20. 14,691.838 14,691.838 ↑ 1.2 2,935,379 3 / 3

Parallel Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sip (cost=0.43..791,050.25 rows=3,667,552 width=20) (actual time=0.666..14,691.838 rows=2,935,379 loops=3)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 16716
21. 40,946.722 40,946.722 ↑ 1.0 9,226,538 3 / 3

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic (cost=0.43..1,259,613.10 rows=9,269,996 width=464) (actual time=0.051..40,946.722 rows=9,226,538 loops=3)

22. 47,872.315 47,872.315 ↑ 1.0 1 8,976,059 / 3

Index Scan using bill_claim_claim_idx on bill_claim bcl_1 (cost=0.43..0.53 rows=1 width=28) (actual time=0.015..0.016 rows=1 loops=8,976,059)

  • Index Cond: ((claim_id)::text = (sic.claim_id)::text)
23. 46.288 844,025.177 ↓ 65.2 57,211 1

Materialize (cost=77,925,586.12..78,592,657.82 rows=878 width=477) (actual time=843,450.685..844,025.177 rows=57,211 loops=1)

24. 3,009.730 843,978.889 ↓ 65.2 57,211 1

Merge Left Join (cost=77,925,586.12..78,592,655.63 rows=878 width=477) (actual time=843,450.679..843,978.889 rows=57,211 loops=1)

  • Merge Cond: ((b.bill_no)::text = (all_insurance_remittance_details_view.bill_no)::text)
25. 718.547 51,596.790 ↓ 64.7 56,838 1

Sort (cost=1,324,673.95..1,324,676.14 rows=878 width=355) (actual time=51,515.239..51,596.790 rows=56,838 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: external merge Disk: 13568kB
26. 425.646 50,878.243 ↓ 64.7 56,838 1

Merge Right Join (cost=1,053,207.35..1,324,631.02 rows=878 width=355) (actual time=50,612.175..50,878.243 rows=56,838 loops=1)

  • Merge Cond: ((insurance_claim_resubmission.claim_id)::text = (icl.claim_id)::text)
27. 922.070 12,324.749 ↓ 1.4 1,373,759 1

Finalize GroupAggregate (cost=269,392.42..528,795.73 rows=961,205 width=46) (actual time=7,463.687..12,324.749 rows=1,373,759 loops=1)

  • Group Key: insurance_claim_resubmission.claim_id
28. 2,631.794 11,402.679 ↓ 1.1 2,130,781 1

Gather Merge (cost=269,392.42..509,571.63 rows=1,922,410 width=46) (actual time=7,463.654..11,402.679 rows=2,130,781 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
29. 519.709 8,770.885 ↑ 1.4 711,044 3 / 3

Partial GroupAggregate (cost=268,392.39..286,677.79 rows=961,205 width=46) (actual time=7,271.735..8,770.885 rows=711,044 loops=3)

  • Group Key: insurance_claim_resubmission.claim_id
30. 6,661.847 8,251.176 ↑ 1.3 914,535 3 / 3

Sort (cost=268,392.39..271,283.51 rows=1,156,446 width=23) (actual time=7,271.717..8,251.176 rows=914,535 loops=3)

  • Sort Key: insurance_claim_resubmission.claim_id
  • Sort Method: external merge Disk: 32360kB
31. 1,589.329 1,589.329 ↑ 1.3 925,096 3 / 3

Parallel Seq Scan on insurance_claim_resubmission (cost=0.00..128,211.46 rows=1,156,446 width=23) (actual time=6.800..1,589.329 rows=925,096 loops=3)

32. 901.005 38,127.848 ↓ 64.7 56,838 1

Sort (cost=783,814.93..783,817.13 rows=878 width=337) (actual time=38,095.897..38,127.848 rows=56,838 loops=1)

  • Sort Key: icl.claim_id
  • Sort Method: external sort Disk: 14608kB
33. 0.000 37,226.843 ↓ 64.7 56,838 1

Gather (cost=729,448.25..783,772.01 rows=878 width=337) (actual time=24,970.352..37,226.843 rows=56,838 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
34. 71.210 37,352.163 ↓ 51.8 18,946 3 / 3

Nested Loop Left Join (cost=728,448.25..782,684.21 rows=366 width=337) (actual time=24,917.872..37,352.163 rows=18,946 loops=3)

35. 49.561 36,883.087 ↓ 51.8 18,946 3 / 3

Nested Loop Left Join (cost=728,447.83..782,044.45 rows=366 width=338) (actual time=24,909.643..36,883.087 rows=18,946 loops=3)

36. 50.423 34,370.546 ↓ 51.8 18,946 3 / 3

Nested Loop Left Join (cost=728,447.40..781,316.63 rows=366 width=329) (actual time=24,874.209..34,370.546 rows=18,946 loops=3)

37. 41.002 33,562.976 ↓ 51.7 18,929 3 / 3

Hash Left Join (cost=728,446.97..778,886.26 rows=366 width=315) (actual time=24,873.371..33,562.976 rows=18,929 loops=3)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
38. 60.304 33,521.561 ↓ 51.7 18,929 3 / 3

Nested Loop Left Join (cost=728,445.49..778,883.64 rows=366 width=319) (actual time=24,872.919..33,521.561 rows=18,929 loops=3)

39. 22.970 31,511.604 ↓ 51.7 18,929 3 / 3

Hash Left Join (cost=728,445.06..778,180.15 rows=366 width=310) (actual time=24,871.252..31,511.604 rows=18,929 loops=3)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stpa.tpa_id)::text)
40. 30.814 31,488.008 ↓ 51.7 18,929 3 / 3

Hash Left Join (cost=728,396.04..778,130.17 rows=366 width=285) (actual time=24,870.576..31,488.008 rows=18,929 loops=3)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
41. 32.487 31,453.849 ↓ 51.7 18,929 3 / 3

Hash Left Join (cost=728,347.02..778,080.19 rows=366 width=270) (actual time=24,867.185..31,453.849 rows=18,929 loops=3)

  • Hash Cond: ((pr.org_id)::text = (od.org_id)::text)
42. 37.571 31,417.737 ↓ 51.7 18,929 3 / 3

Hash Left Join (cost=728,282.03..778,014.24 rows=366 width=256) (actual time=24,863.396..31,417.737 rows=18,929 loops=3)

  • Hash Cond: (pr.visit_type = vn.visit_type)
43. 57.429 31,379.956 ↓ 51.7 18,929 3 / 3

Nested Loop Left Join (cost=728,280.94..778,011.17 rows=366 width=254) (actual time=24,862.524..31,379.956 rows=18,929 loops=3)

44. 86.660 29,959.663 ↓ 51.7 18,929 3 / 3

Hash Join (cost=728,279.80..776,355.45 rows=366 width=266) (actual time=24,860.612..29,959.663 rows=18,929 loops=3)

  • Hash Cond: (pr.center_id = hcm.center_id)
45. 197.208 29,872.334 ↓ 4.9 82,805 3 / 3

Nested Loop Left Join (cost=728,269.21..776,296.59 rows=16,838 width=252) (actual time=20,003.669..29,872.334 rows=82,805 loops=3)

46. 194.925 20,732.222 ↓ 4.9 82,805 3 / 3

Merge Left Join (cost=728,268.78..745,724.98 rows=16,838 width=218) (actual time=20,002.053..20,732.222 rows=82,805 loops=3)

  • Merge Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
47. 90.308 20,239.836 ↓ 4.9 82,805 3 / 3

Merge Left Join (cost=728,268.36..728,353.96 rows=16,838 width=209) (actual time=19,997.854..20,239.836 rows=82,805 loops=3)

  • Merge Cond: ((b.visit_id)::text = (prc.customer_id)::text)
48. 1,207.468 20,149.495 ↓ 4.9 82,805 3 / 3

Sort (cost=728,244.18..728,286.27 rows=16,838 width=161) (actual time=19,997.814..20,149.495 rows=82,805 loops=3)

  • Sort Key: b.visit_id
  • Sort Method: external merge Disk: 14720kB
49. 270.272 18,942.027 ↓ 4.9 82,805 3 / 3

Nested Loop (cost=0.43..727,062.20 rows=16,838 width=161) (actual time=165.335..18,942.027 rows=82,805 loops=3)

50. 11,053.726 11,053.726 ↓ 4.9 82,805 3 / 3

Parallel Seq Scan on bill b (cost=0.00..607,014.66 rows=16,838 width=92) (actual time=150.225..11,053.726 rows=82,805 loops=3)

  • Filter: (is_tpa AND (status <> 'X'::bpchar) AND (date(open_date) >= '2020-03-01'::date) AND (date(open_date) <= '2020-03-31'::date))
  • Rows Removed by Filter: 3457172
51. 7,618.029 7,618.029 ↑ 1.0 1 248,414 / 3

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..7.13 rows=1 width=69) (actual time=0.092..0.092 rows=1 loops=248,414)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
52. 0.014 0.033 ↓ 0.0 0 3 / 3

Sort (cost=24.18..24.88 rows=280 width=48) (actual time=0.033..0.033 rows=0 loops=3)

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

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

54. 297.461 297.461 ↓ 1.0 497,052 3 / 3

Index Only Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.42..16,078.16 rows=496,696 width=9) (actual time=4.193..297.461 rows=497,052 loops=3)

  • Heap Fetches: 6787
55. 8,942.904 8,942.904 ↑ 1.0 1 248,414 / 3

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..1.82 rows=1 width=49) (actual time=0.108..0.108 rows=1 loops=248,414)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
56. 0.019 0.669 ↑ 1.0 1 3 / 3

Hash (cost=10.57..10.57 rows=1 width=22) (actual time=0.669..0.669 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.650 0.650 ↑ 1.0 1 3 / 3

Seq Scan on hospital_center_master hcm (cost=0.00..10.57 rows=1 width=22) (actual time=0.649..0.650 rows=1 loops=3)

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 45
58. 37.857 1,362.864 ↑ 1.0 1 56,786 / 3

Nested Loop Left Join (cost=1.15..4.51 rows=1 width=20) (actual time=0.071..0.072 rows=1 loops=56,786)

59. 56.786 908.576 ↑ 1.0 1 56,786 / 3

Nested Loop (cost=0.71..2.58 rows=1 width=20) (actual time=0.047..0.048 rows=1 loops=56,786)

60. 662.503 662.503 ↑ 1.0 1 56,786 / 3

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pri_plan (cost=0.43..1.92 rows=1 width=42) (actual time=0.035..0.035 rows=1 loops=56,786)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
  • Rows Removed by Filter: 0
61. 189.287 189.287 ↑ 1.0 1 56,786 / 3

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.28..0.66 rows=1 width=10) (actual time=0.010..0.010 rows=1 loops=56,786)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 0
62. 416.431 416.431 ↓ 0.0 0 56,786 / 3

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sec_plan (cost=0.43..1.93 rows=1 width=38) (actual time=0.022..0.022 rows=0 loops=56,786)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
63. 0.014 0.210 ↑ 1.0 4 3 / 3

Hash (cost=1.04..1.04 rows=4 width=6) (actual time=0.210..0.210 rows=4 loops=3)

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

Seq Scan on visit_type_names vn (cost=0.00..1.04 rows=4 width=6) (actual time=0.193..0.196 rows=4 loops=3)

65. 1.162 3.625 ↑ 1.0 1,644 3 / 3

Hash (cost=44.44..44.44 rows=1,644 width=30) (actual time=3.625..3.625 rows=1,644 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 116kB
66. 2.463 2.463 ↑ 1.0 1,644 3 / 3

Seq Scan on organization_details od (cost=0.00..44.44 rows=1,644 width=30) (actual time=0.199..2.463 rows=1,644 loops=3)

67. 0.399 3.345 ↑ 1.0 712 3 / 3

Hash (cost=40.12..40.12 rows=712 width=35) (actual time=3.345..3.345 rows=712 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
68. 2.946 2.946 ↑ 1.0 712 3 / 3

Seq Scan on tpa_master ptpa (cost=0.00..40.12 rows=712 width=35) (actual time=0.402..2.946 rows=712 loops=3)

69. 0.366 0.626 ↑ 1.0 712 3 / 3

Hash (cost=40.12..40.12 rows=712 width=35) (actual time=0.626..0.626 rows=712 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
70. 0.260 0.260 ↑ 1.0 712 3 / 3

Seq Scan on tpa_master stpa (cost=0.00..40.12 rows=712 width=35) (actual time=0.014..0.260 rows=712 loops=3)

71. 1,949.653 1,949.653 ↑ 1.0 1 56,786 / 3

Index Scan using patient_policy_details_patient_policy_id_key on patient_policy_details pppd (cost=0.43..1.92 rows=1 width=17) (actual time=0.103..0.103 rows=1 loops=56,786)

  • Index Cond: (patient_policy_id = pri_plan.patient_policy_id)
72. 0.020 0.413 ↑ 1.0 21 3 / 3

Hash (cost=1.21..1.21 rows=21 width=14) (actual time=0.413..0.413 rows=21 loops=3)

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

Seq Scan on salutation_master sm (cost=0.00..1.21 rows=21 width=14) (actual time=0.386..0.393 rows=21 loops=3)

74. 757.147 757.147 ↑ 1.0 1 56,786 / 3

Index Scan using bill_claim_bill_no_idx on bill_claim bclm (cost=0.43..6.63 rows=1 width=28) (actual time=0.038..0.040 rows=1 loops=56,786)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
75. 2,462.980 2,462.980 ↑ 1.0 1 56,838 / 3

Index Scan using insurance_claim_id_index on insurance_claim icl (cost=0.43..1.99 rows=1 width=23) (actual time=0.130..0.130 rows=1 loops=56,838)

  • Index Cond: ((claim_id)::text = (bclm.claim_id)::text)
76. 397.866 397.866 ↑ 1.0 1 56,838 / 3

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch firstsub (cost=0.42..1.75 rows=1 width=17) (actual time=0.021..0.021 rows=1 loops=56,838)

  • Index Cond: ((submission_batch_id)::text = (icl.submission_batch_id)::text)
  • Filter: (is_resubmission = 'N'::bpchar)
77. 2,444.000 789,372.369 ↓ 2.1 9,440,064 1

Materialize (cost=76,600,912.17..77,256,855.98 rows=4,447,077 width=170) (actual time=776,223.549..789,372.369 rows=9,440,064 loops=1)

78. 4,305.338 786,928.369 ↓ 2.1 9,439,998 1

GroupAggregate (cost=76,600,912.17..77,201,267.52 rows=4,447,077 width=388) (actual time=776,223.542..786,928.369 rows=9,439,998 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
79. 63,083.606 782,623.031 ↑ 4.7 9,525,298 1

Sort (cost=76,600,912.17..76,712,089.09 rows=44,470,766 width=388) (actual time=776,223.526..782,623.031 rows=9,525,298 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: 480336kB
80. 2,007.455 719,539.425 ↑ 4.7 9,525,414 1

Subquery Scan on all_insurance_remittance_details_view (cost=54,587,853.50..55,143,738.07 rows=44,470,766 width=388) (actual time=710,639.970..719,539.425 rows=9,525,414 loops=1)

81. 60,691.666 717,531.970 ↑ 4.7 9,525,414 1

Sort (cost=54,587,853.50..54,699,030.41 rows=44,470,766 width=400) (actual time=710,639.962..717,531.970 rows=9,525,414 loops=1)

  • Sort Key: bill_charge.bill_no DESC
  • Sort Method: external merge Disk: 518336kB
82. 15,734.555 656,840.304 ↑ 4.7 9,525,414 1

Unique (cost=32,048,444.99..32,826,683.39 rows=44,470,766 width=400) (actual time=565,681.800..656,840.304 rows=9,525,414 loops=1)

83. 422,546.388 641,105.749 ↑ 1.0 43,721,796 1

Sort (cost=32,048,444.99..32,159,621.90 rows=44,470,766 width=400) (actual time=565,681.797..641,105.749 rows=43,721,796 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: 2372152kB
84. 5,234.570 218,559.361 ↑ 1.0 43,721,796 1

Append (cost=4,019,707.48..10,287,274.89 rows=44,470,766 width=400) (actual time=82,254.648..218,559.361 rows=43,721,796 loops=1)

85. 33,299.516 207,617.356 ↑ 1.0 43,686,604 1

Hash Join (cost=4,019,707.48..7,005,775.99 rows=43,787,192 width=43) (actual time=82,254.641..207,617.356 rows=43,686,604 loops=1)

  • Hash Cond: (insurance_payment_allocation.remittance_id = ir.remittance_id)
86. 70,819.010 173,867.760 ↑ 1.0 43,697,103 1

Hash Join (cost=3,990,181.94..6,259,181.26 rows=43,787,192 width=28) (actual time=81,802.300..173,867.760 rows=43,697,103 loops=1)

  • Hash Cond: ((insurance_payment_allocation.charge_id)::text = (bill_charge.charge_id)::text)
87. 21,255.007 21,255.007 ↑ 1.0 43,697,103 1

Seq Scan on insurance_payment_allocation (cost=0.00..1,343,727.92 rows=43,787,192 width=24) (actual time=0.403..21,255.007 rows=43,697,103 loops=1)

88. 25,094.541 81,793.743 ↑ 1.0 50,386,914 1

Hash (cost=3,058,963.42..3,058,963.42 rows=50,721,642 width=24) (actual time=81,793.743..81,793.743 rows=50,386,914 loops=1)

  • Buckets: 524288 Batches: 256 Memory Usage: 15174kB
89. 56,699.202 56,699.202 ↑ 1.0 50,386,914 1

Seq Scan on bill_charge (cost=0.00..3,058,963.42 rows=50,721,642 width=24) (actual time=0.398..56,699.202 rows=50,386,914 loops=1)

90. 157.794 450.080 ↑ 1.0 567,587 1

Hash (cost=18,644.46..18,644.46 rows=592,646 width=19) (actual time=450.080..450.080 rows=567,587 loops=1)

  • Buckets: 524288 Batches: 4 Memory Usage: 11310kB
91. 292.286 292.286 ↑ 1.0 567,587 1

Seq Scan on insurance_remittance ir (cost=0.00..18,644.46 rows=592,646 width=19) (actual time=0.708..292.286 rows=567,587 loops=1)

92. 22.265 5,186.394 ↓ 0.0 0 1

Gather (cost=1,503,532.52..2,168,117.83 rows=227,858 width=43) (actual time=5,186.214..5,186.394 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
93. 0.001 5,164.129 ↓ 0.0 0 3 / 3

Nested Loop (cost=1,502,532.52..2,144,332.03 rows=94,941 width=43) (actual time=5,164.129..5,164.129 rows=0 loops=3)

94. 133.592 5,164.128 ↓ 0.0 0 3 / 3

Merge Join (cost=1,502,531.96..1,646,654.10 rows=94,941 width=39) (actual time=5,164.128..5,164.128 rows=0 loops=3)

  • Merge Cond: (ipa.remittance_id = ipu.remittance_id)
95. 2,907.554 5,029.059 ↑ 10.5 868,294 3 / 3

Parallel Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa (cost=1,500,045.86..3,841,497.25 rows=9,122,332 width=24) (actual time=2,188.489..5,029.059 rows=868,294 loops=3)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 609
96.          

SubPlan (for Parallel Index Scan)

97. 19.899 2,121.505 ↑ 2.8 163,272 3 / 3

Nested Loop (cost=1.13..1,498,906.01 rows=455,716 width=4) (actual time=1.677..2,121.505 rows=163,272 loops=3)

98. 24.963 142.342 ↑ 2.8 163,272 3 / 3

Nested Loop (cost=0.56..63,917.52 rows=455,716 width=14) (actual time=0.574..142.342 rows=163,272 loops=3)

99. 0.505 0.505 ↑ 1.0 302 3 / 3

Seq Scan on insurance_payment_unalloc_amount ipu_2 (cost=0.00..8.02 rows=302 width=4) (actual time=0.276..0.505 rows=302 loops=3)

100. 116.874 116.874 ↑ 2.9 541 906 / 3

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation insurance_payment_allocation_1 (cost=0.56..195.95 rows=1,567 width=14) (actual time=0.011..0.387 rows=541 loops=906)

  • Index Cond: (remittance_id = ipu_2.remittance_id)
101. 1,959.264 1,959.264 ↑ 1.0 1 489,816 / 3

Index Only Scan using bill_charge_pkey on bill_charge bc_2 (cost=0.56..3.15 rows=1 width=10) (actual time=0.012..0.012 rows=1 loops=489,816)

  • Index Cond: (charge_id = (insurance_payment_allocation_1.charge_id)::text)
  • Heap Fetches: 1800
102. 0.149 1.477 ↑ 1.4 222 3 / 3

Sort (cost=2,486.10..2,486.85 rows=302 width=23) (actual time=1.448..1.477 rows=222 loops=3)

  • Sort Key: ipu.remittance_id
  • Sort Method: quicksort Memory: 42kB
103. 0.079 1.328 ↑ 1.4 222 3 / 3

Nested Loop (cost=0.42..2,473.66 rows=302 width=23) (actual time=0.528..1.328 rows=222 loops=3)

104. 0.041 0.041 ↑ 1.0 302 3 / 3

Seq Scan on insurance_payment_unalloc_amount ipu (cost=0.00..8.02 rows=302 width=10) (actual time=0.006..0.041 rows=302 loops=3)

105. 1.208 1.208 ↑ 1.0 1 906 / 3

Index Scan using insurance_remittance_pkey on insurance_remittance ir_1 (cost=0.42..8.16 rows=1 width=13) (actual time=0.004..0.004 rows=1 loops=906)

  • Index Cond: (remittance_id = ipu.remittance_id)
106. 0.000 0.000 ↓ 0.0 0 / 3

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

  • Index Cond: ((charge_id)::text = (ipa.charge_id)::text)
107. 378.652 521.041 ↑ 12.9 35,192 1

Gather (cost=1,022.41..668,673.41 rows=455,716 width=41) (actual time=6.362..521.041 rows=35,192 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
108. 4.546 142.389 ↑ 16.2 11,731 3 / 3

Nested Loop (cost=22.41..622,101.81 rows=189,882 width=41) (actual time=2.775..142.389 rows=11,731 loops=3)

109. 4.403 20.536 ↑ 16.2 11,731 3 / 3

Nested Loop (cost=21.85..22,954.22 rows=189,882 width=51) (actual time=2.717..20.536 rows=11,731 loops=3)

  • Join Filter: (ipu_1.remittance_id = ipa_1.remittance_id)
110. 1.019 6.439 ↑ 1.7 74 3 / 3

Merge Join (cost=21.28..4,566.98 rows=126 width=45) (actual time=2.671..6.439 rows=74 loops=3)

  • Merge Cond: (ir_2.remittance_id = ipu_1.remittance_id)
111. 4.925 4.925 ↑ 52.1 4,738 3 / 3

Parallel Index Scan using insurance_remittance_pkey on insurance_remittance ir_2 (cost=0.42..176,676.13 rows=246,936 width=13) (actual time=0.103..4.925 rows=4,738 loops=3)

112. 0.282 0.495 ↑ 1.0 302 3 / 3

Sort (cost=20.46..21.21 rows=302 width=32) (actual time=0.428..0.495 rows=302 loops=3)

  • Sort Key: ipu_1.remittance_id
  • Sort Method: quicksort Memory: 48kB
113. 0.213 0.213 ↑ 1.0 302 3 / 3

Seq Scan on insurance_payment_unalloc_amount ipu_1 (cost=0.00..8.02 rows=302 width=32) (actual time=0.037..0.213 rows=302 loops=3)

114. 9.694 9.694 ↑ 9.9 159 222 / 3

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa_1 (cost=0.56..126.34 rows=1,567 width=14) (actual time=0.009..0.131 rows=159 loops=222)

  • Index Cond: (remittance_id = ir_2.remittance_id)
115. 117.307 117.307 ↑ 1.0 1 35,192 / 3

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

  • Index Cond: (charge_id = (ipa_1.charge_id)::text)
  • Heap Fetches: 0
116. 57.211 57.211 ↓ 0.0 0 57,211

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch resub (cost=0.42..1.77 rows=1 width=17) (actual time=0.001..0.001 rows=0 loops=57,211)

  • Index Cond: ((submission_batch_id)::text = (max((insurance_claim_resubmission.resubmission_batch_id)::text)))
Planning time : 68.134 ms
Execution time : 1,209,530.551 ms