explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GM3t

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 20,719.177 ↑ 103.5 2 1

Nested Loop Left Join (cost=2,678,991.74..2,746,242.62 rows=207 width=581) (actual time=20,719.159..20,719.177 rows=2 loops=1)

2. 45.083 20,719.153 ↑ 103.5 2 1

Merge Left Join (cost=2,678,991.46..2,746,158.11 rows=207 width=633) (actual time=20,719.139..20,719.153 rows=2 loops=1)

  • Merge Cond: ((b.bill_no)::text = (all_insurance_remittance_details_view.bill_no)::text)
3. 63.521 9,113.111 ↑ 103.5 2 1

Merge Left Join (cost=612,655.34..662,300.89 rows=207 width=511) (actual time=9,113.098..9,113.111 rows=2 loops=1)

  • Merge Cond: ((b.bill_no)::text = (bcl.bill_no)::text)
4. 0.000 737.423 ↑ 103.5 2 1

Sort (cost=44,225.23..44,225.75 rows=207 width=483) (actual time=737.422..737.423 rows=2 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 25kB
5. 50.884 737.537 ↑ 103.5 2 1

Gather (cost=42,511.27..44,217.27 rows=207 width=483) (actual time=737.291..737.537 rows=2 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 0.005 686.653 ↑ 86.0 1 3 / 3

Hash Left Join (cost=41,511.27..43,196.57 rows=86 width=483) (actual time=686.614..686.653 rows=1 loops=3)

  • Hash Cond: ((icl.claim_id)::text = (insurance_claim_resubmission.claim_id)::text)
7. 0.002 678.628 ↑ 86.0 1 3 / 3

Nested Loop Left Join (cost=39,399.16..41,084.24 rows=86 width=465) (actual time=678.589..678.628 rows=1 loops=3)

8. 0.002 678.567 ↑ 86.0 1 3 / 3

Nested Loop Left Join (cost=39,398.88..41,057.80 rows=86 width=466) (actual time=678.531..678.567 rows=1 loops=3)

9. 0.003 678.502 ↑ 86.0 1 3 / 3

Nested Loop Left Join (cost=39,398.45..41,012.00 rows=86 width=457) (actual time=678.469..678.502 rows=1 loops=3)

10. 0.003 678.490 ↑ 86.0 1 3 / 3

Hash Left Join (cost=39,398.03..40,368.99 rows=86 width=443) (actual time=678.461..678.490 rows=1 loops=3)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
11. 0.002 678.425 ↑ 86.0 1 3 / 3

Nested Loop Left Join (cost=39,396.63..40,367.31 rows=86 width=334) (actual time=678.397..678.425 rows=1 loops=3)

12. 0.003 678.314 ↑ 86.0 1 3 / 3

Hash Left Join (cost=39,396.21..40,327.38 rows=86 width=325) (actual time=678.289..678.314 rows=1 loops=3)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stpa.tpa_id)::text)
13. 0.004 678.297 ↑ 86.0 1 3 / 3

Hash Left Join (cost=39,386.35..40,317.30 rows=86 width=303) (actual time=678.273..678.297 rows=1 loops=3)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
14. 0.003 678.222 ↑ 86.0 1 3 / 3

Nested Loop Left Join (cost=39,376.49..40,307.21 rows=86 width=291) (actual time=678.199..678.222 rows=1 loops=3)

15. 0.005 678.164 ↑ 86.0 1 3 / 3

Hash Left Join (cost=39,376.21..40,282.05 rows=86 width=278) (actual time=678.144..678.164 rows=1 loops=3)

  • Hash Cond: (pr.visit_type = vn.visit_type)
16. 0.002 678.099 ↑ 86.0 1 3 / 3

Nested Loop Left Join (cost=39,375.12..40,280.49 rows=86 width=248) (actual time=678.079..678.099 rows=1 loops=3)

17. 0.002 677.904 ↑ 86.0 1 3 / 3

Nested Loop (cost=39,374.13..40,175.76 rows=86 width=260) (actual time=677.893..677.904 rows=1 loops=3)

18. 0.015 677.763 ↑ 86.3 11 3 / 3

Nested Loop Left Join (cost=39,374.00..40,027.75 rows=949 width=250) (actual time=677.634..677.763 rows=11 loops=3)

19. 0.006 677.311 ↑ 86.3 11 3 / 3

Merge Left Join (cost=39,373.57..39,496.56 rows=949 width=218) (actual time=677.300..677.311 rows=11 loops=3)

  • Merge Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
20. 0.008 676.992 ↑ 86.3 11 3 / 3

Merge Left Join (cost=39,373.30..39,379.45 rows=949 width=209) (actual time=676.984..676.992 rows=11 loops=3)

  • Merge Cond: ((b.visit_id)::text = (prc.customer_id)::text)
21. 0.055 676.958 ↑ 86.3 11 3 / 3

Sort (cost=39,349.12..39,351.49 rows=949 width=161) (actual time=676.955..676.958 rows=11 loops=3)

  • Sort Key: b.visit_id
  • Sort Method: quicksort Memory: 27kB
22. 0.048 676.903 ↑ 86.3 11 3 / 3

Nested Loop (cost=0.42..39,302.19 rows=949 width=161) (actual time=357.634..676.903 rows=11 loops=3)

23. 675.639 675.639 ↑ 86.3 11 3 / 3

Parallel Seq Scan on bill b (cost=0.00..32,846.37 rows=949 width=92) (actual time=356.794..675.639 rows=11 loops=3)

  • Filter: (is_tpa AND (status <> 'X'::bpchar) AND (date(open_date) >= '2018-02-10'::date) AND (date(open_date) <= '2018-02-28'::date))
  • Rows Removed by Filter: 193,732
24. 1.216 1.216 ↑ 1.0 1 32 / 3

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..6.80 rows=1 width=69) (actual time=0.114..0.114 rows=1 loops=32)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
25. 0.015 0.026 ↓ 0.0 0 3 / 3

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

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

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

27. 0.313 0.313 ↑ 1,400.0 1 3 / 3

Index Only Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.28..111.20 rows=1,400 width=9) (actual time=0.313..0.313 rows=1 loops=3)

  • Heap Fetches: 1
28. 0.437 0.437 ↑ 1.0 1 32 / 3

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..0.56 rows=1 width=47) (actual time=0.041..0.041 rows=1 loops=32)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
29. 0.139 0.139 ↓ 0.0 0 32 / 3

Index Scan using center_pkey on hospital_center_master hcm (cost=0.14..0.16 rows=1 width=18) (actual time=0.013..0.013 rows=0 loops=32)

  • Index Cond: (center_id = pr.center_id)
  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 1
30. 0.001 0.193 ↑ 1.0 1 2 / 3

Nested Loop Left Join (cost=0.99..1.21 rows=1 width=20) (actual time=0.288..0.290 rows=1 loops=2)

31. 0.002 0.188 ↑ 1.0 1 2 / 3

Nested Loop (cost=0.57..0.68 rows=1 width=20) (actual time=0.280..0.282 rows=1 loops=2)

32. 0.129 0.129 ↑ 1.0 1 2 / 3

Index Scan using policy_patient_idx on patient_insurance_plans pri_plan (cost=0.42..0.51 rows=1 width=42) (actual time=0.193..0.194 rows=1 loops=2)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
33. 0.057 0.057 ↑ 1.0 1 2 / 3

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.14..0.16 rows=1 width=10) (actual time=0.085..0.085 rows=1 loops=2)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 2
34. 0.004 0.004 ↓ 0.0 0 2 / 3

Index Scan using policy_patient_idx on patient_insurance_plans sec_plan (cost=0.42..0.52 rows=1 width=38) (actual time=0.006..0.006 rows=0 loops=2)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
35. 0.001 0.060 ↑ 1.0 4 1 / 3

Hash (cost=1.04..1.04 rows=4 width=40) (actual time=0.181..0.181 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
36. 0.060 0.060 ↑ 1.0 4 1 / 3

Seq Scan on visit_type_names vn (cost=0.00..1.04 rows=4 width=40) (actual time=0.178..0.179 rows=4 loops=1)

37. 0.055 0.055 ↑ 1.0 1 2 / 3

Index Scan using organization_details_pkey on organization_details od (cost=0.28..0.29 rows=1 width=29) (actual time=0.083..0.083 rows=1 loops=2)

  • Index Cond: ((pr.org_id)::text = (org_id)::text)
38. 0.008 0.071 ↑ 1.0 216 1 / 3

Hash (cost=7.16..7.16 rows=216 width=32) (actual time=0.214..0.214 rows=216 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
39. 0.063 0.063 ↑ 1.0 216 1 / 3

Seq Scan on tpa_master ptpa (cost=0.00..7.16 rows=216 width=32) (actual time=0.004..0.189 rows=216 loops=1)

40. 0.008 0.014 ↑ 1.0 216 1 / 3

Hash (cost=7.16..7.16 rows=216 width=32) (actual time=0.043..0.043 rows=216 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
41. 0.006 0.006 ↑ 1.0 216 1 / 3

Seq Scan on tpa_master stpa (cost=0.00..7.16 rows=216 width=32) (actual time=0.002..0.019 rows=216 loops=1)

42. 0.109 0.109 ↑ 1.0 1 2 / 3

Index Scan using patient_policy_details_patient_policy_id_key on patient_policy_details pppd (cost=0.42..0.46 rows=1 width=17) (actual time=0.163..0.163 rows=1 loops=2)

  • Index Cond: (patient_policy_id = pri_plan.patient_policy_id)
43. 0.001 0.062 ↑ 1.0 18 1 / 3

Hash (cost=1.18..1.18 rows=18 width=156) (actual time=0.186..0.186 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
44. 0.061 0.061 ↑ 1.0 18 1 / 3

Seq Scan on salutation_master sm (cost=0.00..1.18 rows=18 width=156) (actual time=0.181..0.182 rows=18 loops=1)

45. 0.009 0.009 ↑ 1.0 1 2 / 3

Index Scan using bill_claim_bill_no_idx on bill_claim bclm (cost=0.42..7.47 rows=1 width=28) (actual time=0.012..0.014 rows=1 loops=2)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
46. 0.063 0.063 ↑ 1.0 1 2 / 3

Index Scan using insurance_claim_id_index on insurance_claim icl (cost=0.42..0.53 rows=1 width=23) (actual time=0.094..0.094 rows=1 loops=2)

  • Index Cond: ((claim_id)::text = (bclm.claim_id)::text)
47. 0.059 0.059 ↑ 1.0 1 2 / 3

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch firstsub (cost=0.29..0.31 rows=1 width=17) (actual time=0.088..0.088 rows=1 loops=2)

  • Index Cond: ((submission_batch_id)::text = (icl.submission_batch_id)::text)
  • Filter: (is_resubmission = 'N'::bpchar)
48. 1.066 8.020 ↓ 1.0 25,353 1 / 3

Hash (cost=1,809.53..1,809.53 rows=24,206 width=46) (actual time=24.059..24.059 rows=25,353 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,636kB
49. 4.237 6.953 ↓ 1.0 25,353 1 / 3

HashAggregate (cost=1,325.41..1,567.47 rows=24,206 width=46) (actual time=17.221..20.860 rows=25,353 loops=1)

  • Group Key: insurance_claim_resubmission.claim_id
50. 2.716 2.716 ↑ 1.0 37,494 1 / 3

Seq Scan on insurance_claim_resubmission (cost=0.00..1,137.94 rows=37,494 width=23) (actual time=0.234..8.148 rows=37,494 loops=1)

51. 117.326 8,312.167 ↑ 1.0 464,386 1

Materialize (cost=568,430.11..616,862.11 rows=484,320 width=42) (actual time=7,922.419..8,312.167 rows=464,386 loops=1)

52. 226.603 8,194.841 ↑ 1.0 464,386 1

Group (cost=568,430.11..610,808.11 rows=484,320 width=886) (actual time=7,922.415..8,194.841 rows=464,386 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
53. 2,488.351 7,968.238 ↑ 1.0 482,195 1

Sort (cost=568,430.11..569,640.91 rows=484,320 width=886) (actual time=7,922.413..7,968.238 rows=482,195 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: 69,296kB
54. 372.131 5,479.887 ↓ 1.0 484,371 1

Merge Left Join (cost=113,160.89..145,263.73 rows=484,320 width=886) (actual time=4,762.884..5,479.887 rows=484,371 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_1.bill_no)::text)
55. 148.681 893.730 ↓ 1.0 484,321 1

Merge Left Join (cost=32,085.90..62,865.76 rows=484,320 width=442) (actual time=646.397..893.730 rows=484,321 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_2.bill_no)::text)
56. 95.965 95.965 ↓ 1.0 484,321 1

Index Only Scan using bill_claim_bill_no_idx on bill_claim bcl (cost=0.42..29,565.22 rows=484,320 width=14) (actual time=0.007..95.965 rows=484,321 loops=1)

  • Heap Fetches: 2,862
57. 52.320 649.084 ↓ 152.9 35,619 1

Sort (cost=32,085.47..32,086.06 rows=233 width=442) (actual time=646.386..649.084 rows=35,619 loops=1)

  • Sort Key: bcl_2.bill_no
  • Sort Method: quicksort Memory: 3,307kB
58. 3.705 596.764 ↓ 76.4 17,811 1

Gather (cost=1,000.85..32,076.31 rows=233 width=442) (actual time=2.018..596.764 rows=17,811 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
59. 2.370 593.059 ↓ 61.2 5,937 3 / 3

Nested Loop (cost=0.84..31,053.01 rows=97 width=442) (actual time=0.416..593.059 rows=5,937 loops=3)

60. 5.786 358.145 ↓ 59.6 5,963 3 / 3

Nested Loop (cost=0.42..30,992.04 rows=100 width=428) (actual time=0.392..358.145 rows=5,963 loops=3)

61. 26.346 26.346 ↑ 1.2 5,822 3 / 3

Parallel Seq Scan on patient_insurance_plans sip_1 (cost=0.00..9,683.81 rows=7,160 width=20) (actual time=0.013..26.346 rows=5,822 loops=3)

  • Filter: (priority = 2)
  • Rows Removed by Filter: 150,966
62. 326.013 326.013 ↑ 1.0 1 17,465 / 3

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic_1 (cost=0.42..2.97 rows=1 width=448) (actual time=0.042..0.056 rows=1 loops=17,465)

  • Index Cond: ((patient_id)::text = (sip_1.patient_id)::text)
  • Filter: (sip_1.plan_id = plan_id)
  • Rows Removed by Filter: 1
63. 232.544 232.544 ↑ 1.0 1 17,888 / 3

Index Scan using bill_claim_claim_idx on bill_claim bcl_2 (cost=0.42..0.60 rows=1 width=28) (actual time=0.038..0.039 rows=1 loops=17,888)

  • Index Cond: ((claim_id)::text = (sic_1.claim_id)::text)
64. 2,183.757 4,214.026 ↓ 78.8 484,190 1

Sort (cost=81,074.99..81,090.36 rows=6,145 width=458) (actual time=4,116.479..4,214.026 rows=484,190 loops=1)

  • Sort Key: bcl_1.bill_no
  • Sort Method: external sort Disk: 66,304kB
65. 0.000 2,030.269 ↓ 75.9 466,317 1

Gather (cost=23,567.65..80,688.31 rows=6,145 width=458) (actual time=153.799..2,030.269 rows=466,317 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
66. 90.977 2,034.815 ↓ 60.7 155,439 3 / 3

Nested Loop (cost=22,567.65..79,073.81 rows=2,560 width=458) (actual time=151.955..2,034.815 rows=155,439 loops=3)

67. 177.377 417.425 ↓ 58.2 152,641 3 / 3

Hash Join (cost=22,567.22..77,475.16 rows=2,622 width=444) (actual time=151.739..417.425 rows=152,641 loops=3)

  • Hash Cond: (((sic.patient_id)::text = (sip.patient_id)::text) AND (sic.plan_id = sip.plan_id))
68. 88.600 88.600 ↑ 1.3 165,342 3 / 3

Parallel Seq Scan on insurance_claim sic (cost=0.00..11,356.78 rows=206,678 width=448) (actual time=0.009..88.600 rows=165,342 loops=3)

69. 65.895 151.448 ↑ 1.0 452,733 3 / 3

Hash (cost=13,113.54..13,113.54 rows=453,179 width=20) (actual time=151.448..151.448 rows=452,733 loops=3)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,372kB
70. 85.553 85.553 ↑ 1.0 452,899 3 / 3

Seq Scan on patient_insurance_plans sip (cost=0.00..13,113.54 rows=453,179 width=20) (actual time=0.014..85.553 rows=452,899 loops=3)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 17,465
71. 1,526.413 1,526.413 ↑ 1.0 1 457,924 / 3

Index Scan using bill_claim_claim_idx on bill_claim bcl_1 (cost=0.42..0.60 rows=1 width=28) (actual time=0.010..0.010 rows=1 loops=457,924)

  • Index Cond: ((claim_id)::text = (sic.claim_id)::text)
72. 44.692 11,560.959 ↓ 2.4 275,211 1

Materialize (cost=2,066,336.12..2,083,564.28 rows=116,801 width=170) (actual time=11,352.458..11,560.959 rows=275,211 loops=1)

73. 76.026 11,516.267 ↓ 2.4 275,211 1

GroupAggregate (cost=2,066,336.12..2,082,104.27 rows=116,801 width=388) (actual time=11,352.454..11,516.267 rows=275,211 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
74. 1,381.873 11,440.241 ↑ 4.2 277,244 1

Sort (cost=2,066,336.12..2,069,256.15 rows=1,168,011 width=388) (actual time=11,352.446..11,440.241 rows=277,244 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: 13,544kB
75. 31.238 10,058.368 ↑ 4.2 277,835 1

Subquery Scan on all_insurance_remittance_details_view (cost=1,518,828.04..1,533,428.18 rows=1,168,011 width=388) (actual time=9,935.749..10,058.368 rows=277,835 loops=1)

76. 1,287.793 10,027.130 ↑ 4.2 277,835 1

Sort (cost=1,518,828.04..1,521,748.07 rows=1,168,011 width=400) (actual time=9,935.746..10,027.130 rows=277,835 loops=1)

  • Sort Key: bill_charge.bill_no DESC
  • Sort Method: external merge Disk: 14,728kB
77. 242.873 8,739.337 ↑ 4.2 277,835 1

Unique (cost=957,499.90..977,940.10 rows=1,168,011 width=400) (actual time=7,448.043..8,739.337 rows=277,835 loops=1)

78. 4,217.142 8,496.464 ↑ 1.0 1,122,775 1

Sort (cost=957,499.90..960,419.93 rows=1,168,011 width=400) (actual time=7,448.041..8,496.464 rows=1,122,775 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: 59,128kB
79. 44.926 4,279.322 ↑ 1.0 1,122,775 1

Append (cost=191,355.38..416,611.96 rows=1,168,011 width=400) (actual time=2,919.149..4,279.322 rows=1,122,775 loops=1)

80. 166.549 3,849.154 ↑ 1.0 1,122,342 1

Hash Join (cost=191,355.38..252,522.31 rows=1,129,465 width=40) (actual time=2,919.148..3,849.154 rows=1,122,342 loops=1)

  • Hash Cond: (insurance_payment_allocation.remittance_id = ir.remittance_id)
81. 633.490 3,678.957 ↑ 1.0 1,123,175 1

Hash Join (cost=191,023.77..249,224.26 rows=1,129,465 width=26) (actual time=2,915.492..3,678.957 rows=1,123,175 loops=1)

  • Hash Cond: ((insurance_payment_allocation.charge_id)::text = (bill_charge.charge_id)::text)
82. 131.467 131.467 ↑ 1.0 1,129,465 1

Seq Scan on insurance_payment_allocation (cost=0.00..27,523.65 rows=1,129,465 width=21) (actual time=1.246..131.467 rows=1,129,465 loops=1)

83. 640.806 2,914.000 ↑ 1.0 2,415,957 1

Hash (cost=145,666.23..145,666.23 rows=2,470,523 width=23) (actual time=2,914.000..2,914.000 rows=2,415,957 loops=1)

  • Buckets: 65,536 Batches: 64 Memory Usage: 2,602kB
84. 2,273.194 2,273.194 ↑ 1.0 2,415,957 1

Seq Scan on bill_charge (cost=0.00..145,666.23 rows=2,470,523 width=23) (actual time=1.760..2,273.194 rows=2,415,957 loops=1)

85. 0.877 3.648 ↑ 1.0 7,894 1

Hash (cost=232.94..232.94 rows=7,894 width=18) (actual time=3.648..3.648 rows=7,894 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 457kB
86. 2.771 2.771 ↑ 1.0 7,894 1

Seq Scan on insurance_remittance ir (cost=0.00..232.94 rows=7,894 width=18) (actual time=0.448..2.771 rows=7,894 loops=1)

87. 23.836 381.266 ↓ 0.0 0 1

Gather (cost=71,907.83..126,535.84 rows=12,849 width=40) (actual time=381.203..381.266 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
88. 0.002 357.430 ↓ 0.0 0 3 / 3

Nested Loop (cost=70,907.83..124,250.94 rows=5,354 width=40) (actual time=357.430..357.430 rows=0 loops=3)

89. 18.883 357.428 ↓ 0.0 0 3 / 3

Hash Join (cost=70,907.40..116,897.20 rows=5,354 width=35) (actual time=357.428..357.428 rows=0 loops=3)

  • Hash Cond: (ipa.remittance_id = ipu.remittance_id)
90. 64.317 336.283 ↓ 1.6 376,204 3 / 3

Parallel Seq Scan on insurance_payment_allocation ipa (cost=70,570.95..92,682.58 rows=235,305 width=21) (actual time=279.309..336.283 rows=376,204 loops=3)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 285
91.          

SubPlan (for Parallel Seq Scan)

92. 0.000 271.966 ↓ 2.5 63,712 3 / 3

Nested Loop (cost=0.86..70,506.70 rows=25,697 width=4) (actual time=0.976..271.966 rows=63,712 loops=3)

93. 5.003 17.344 ↓ 2.5 63,712 3 / 3

Nested Loop (cost=0.43..47,109.55 rows=25,697 width=13) (actual time=0.444..17.344 rows=63,712 loops=3)

94. 0.024 0.024 ↑ 1.0 113 3 / 3

Seq Scan on insurance_payment_unalloc_amount ipu_2 (cost=0.00..3.13 rows=113 width=4) (actual time=0.003..0.024 rows=113 loops=3)

95. 12.317 12.317 ↓ 1.9 564 339 / 3

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation insurance_payment_allocation_1 (cost=0.43..413.96 rows=291 width=13) (actual time=0.012..0.109 rows=564 loops=339)

  • Index Cond: (remittance_id = ipu_2.remittance_id)
96. 254.848 254.848 ↑ 1.0 1 191,136 / 3

Index Only Scan using bill_charge_pkey on bill_charge bc_2 (cost=0.43..0.91 rows=1 width=9) (actual time=0.004..0.004 rows=1 loops=191,136)

  • Index Cond: (charge_id = (insurance_payment_allocation_1.charge_id)::text)
  • Heap Fetches: 63,712
97. 0.008 2.262 ↑ 3.3 34 3 / 3

Hash (cost=335.04..335.04 rows=113 width=22) (actual time=2.262..2.262 rows=34 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
98. 0.031 2.254 ↑ 3.3 34 3 / 3

Hash Join (cost=331.62..335.04 rows=113 width=22) (actual time=2.236..2.254 rows=34 loops=3)

  • Hash Cond: (ipu.remittance_id = ir_1.remittance_id)
99. 0.150 0.150 ↑ 1.0 113 3 / 3

Seq Scan on insurance_payment_unalloc_amount ipu (cost=0.00..3.13 rows=113 width=10) (actual time=0.142..0.150 rows=113 loops=3)

100. 0.859 2.073 ↑ 1.0 7,894 3 / 3

Hash (cost=232.94..232.94 rows=7,894 width=12) (actual time=2.073..2.073 rows=7,894 loops=3)

  • Buckets: 8,192 Batches: 1 Memory Usage: 423kB
101. 1.214 1.214 ↑ 1.0 7,894 3 / 3

Seq Scan on insurance_remittance ir_1 (cost=0.00..232.94 rows=7,894 width=12) (actual time=0.010..1.214 rows=7,894 loops=3)

102. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.43..1.37 rows=1 width=23) (never executed)

  • Index Cond: ((charge_id)::text = (ipa.charge_id)::text)
103. 0.000 3.976 ↑ 59.3 433 1

Nested Loop (cost=332.47..25,873.69 rows=25,697 width=40) (actual time=2.075..3.976 rows=433 loops=1)

104. 0.050 2.269 ↑ 59.3 433 1

Nested Loop (cost=332.04..2,476.53 rows=25,697 width=49) (actual time=2.058..2.269 rows=433 loops=1)

  • Join Filter: (ipu_1.remittance_id = ipa_1.remittance_id)
105. 0.038 2.049 ↑ 3.3 34 1

Hash Join (cost=331.62..335.04 rows=113 width=44) (actual time=2.029..2.049 rows=34 loops=1)

  • Hash Cond: (ipu_1.remittance_id = ir_2.remittance_id)
106. 0.014 0.014 ↑ 1.0 113 1

Seq Scan on insurance_payment_unalloc_amount ipu_1 (cost=0.00..3.13 rows=113 width=32) (actual time=0.007..0.014 rows=113 loops=1)

107. 0.831 1.997 ↑ 1.0 7,894 1

Hash (cost=232.94..232.94 rows=7,894 width=12) (actual time=1.997..1.997 rows=7,894 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 423kB
108. 1.166 1.166 ↑ 1.0 7,894 1

Seq Scan on insurance_remittance ir_2 (cost=0.00..232.94 rows=7,894 width=12) (actual time=0.005..1.166 rows=7,894 loops=1)

109. 0.170 0.170 ↑ 22.4 13 34

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa_1 (cost=0.43..15.31 rows=291 width=13) (actual time=0.002..0.005 rows=13 loops=34)

  • Index Cond: (remittance_id = ir_2.remittance_id)
110. 1.732 1.732 ↑ 1.0 1 433

Index Only Scan using bill_charge_pkey on bill_charge bc_1 (cost=0.43..0.91 rows=1 width=9) (actual time=0.004..0.004 rows=1 loops=433)

  • Index Cond: (charge_id = (ipa_1.charge_id)::text)
  • Heap Fetches: 433
111. 0.006 0.006 ↓ 0.0 0 2

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch resub (cost=0.29..0.35 rows=1 width=17) (actual time=0.003..0.003 rows=0 loops=2)

  • Index Cond: ((submission_batch_id)::text = (max((insurance_claim_resubmission.resubmission_batch_id)::text)))
Planning time : 41.558 ms
Execution time : 20,742.690 ms