explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 32qD

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 27,375.045 ↑ 103.5 2 1

Sort (cost=3,569,918.61..3,569,919.12 rows=207 width=581) (actual time=27,375.044..27,375.045 rows=2 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 25kB
2. 0.043 27,375.034 ↑ 103.5 2 1

Hash Left Join (cost=3,502,732.06..3,569,910.64 rows=207 width=581) (actual time=27,375.011..27,375.034 rows=2 loops=1)

  • Hash Cond: ((max((insurance_claim_resubmission.resubmission_batch_id)::text)) = (resub.submission_batch_id)::text)
3. 51.984 27,371.592 ↑ 103.5 2 1

Merge Left Join (cost=3,502,238.72..3,569,405.38 rows=207 width=633) (actual time=27,371.572..27,371.592 rows=2 loops=1)

  • Merge Cond: ((b.bill_no)::text = (all_insurance_remittance_details_view.bill_no)::text)
4. 62.755 9,439.751 ↑ 103.5 2 1

Merge Left Join (cost=807,711.93..857,357.49 rows=207 width=511) (actual time=9,439.734..9,439.751 rows=2 loops=1)

  • Merge Cond: ((b.bill_no)::text = (bcl.bill_no)::text)
5. 0.010 2,563.631 ↑ 103.5 2 1

Sort (cost=188,245.75..188,246.27 rows=207 width=483) (actual time=2,563.630..2,563.631 rows=2 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 25kB
6. 0.076 2,563.621 ↑ 103.5 2 1

Hash Left Join (cost=172,123.58..188,237.79 rows=207 width=483) (actual time=2,343.824..2,563.621 rows=2 loops=1)

  • Hash Cond: ((icl.claim_id)::text = (insurance_claim_resubmission.claim_id)::text)
7. 0.015 2,523.924 ↑ 103.5 2 1

Hash Left Join (cost=170,011.48..186,125.14 rows=207 width=465) (actual time=2,304.128..2,523.924 rows=2 loops=1)

  • Hash Cond: ((icl.submission_batch_id)::text = (firstsub.submission_batch_id)::text)
8. 0.012 2,518.948 ↑ 103.5 2 1

Hash Left Join (cost=169,539.94..185,653.06 rows=207 width=466) (actual time=2,299.154..2,518.948 rows=2 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
9. 36.513 2,518.216 ↑ 103.5 2 1

Hash Right Join (cost=169,538.53..185,650.97 rows=207 width=357) (actual time=2,298.424..2,518.216 rows=2 loops=1)

  • Hash Cond: ((icl.claim_id)::text = (bclm.claim_id)::text)
10. 236.600 236.600 ↑ 1.0 496,027 1

Seq Scan on insurance_claim icl (cost=0.00..14,250.27 rows=496,027 width=23) (actual time=0.455..236.600 rows=496,027 loops=1)

11. 0.012 2,245.103 ↑ 103.5 2 1

Hash (cost=169,535.95..169,535.95 rows=207 width=348) (actual time=2,245.103..2,245.103 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 49.537 2,245.091 ↑ 103.5 2 1

Hash Right Join (cost=148,660.83..169,535.95 rows=207 width=348) (actual time=2,241.800..2,245.091 rows=2 loops=1)

  • Hash Cond: ((bclm.bill_no)::text = (b.bill_no)::text)
13. 273.621 273.621 ↓ 1.0 484,321 1

Seq Scan on bill_claim bclm (cost=0.00..19,057.20 rows=484,320 width=28) (actual time=0.010..273.621 rows=484,321 loops=1)

14. 0.003 1,921.933 ↑ 103.5 2 1

Hash (cost=148,658.24..148,658.24 rows=207 width=334) (actual time=1,921.933..1,921.933 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.004 1,921.930 ↑ 103.5 2 1

Hash Left Join (cost=142,663.24..148,658.24 rows=207 width=334) (actual time=1,897.333..1,921.930 rows=2 loops=1)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stpa.tpa_id)::text)
16. 0.007 1,921.884 ↑ 103.5 2 1

Hash Left Join (cost=142,653.38..148,647.84 rows=207 width=312) (actual time=1,897.288..1,921.884 rows=2 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
17. 0.022 1,921.827 ↑ 103.5 2 1

Hash Left Join (cost=142,643.52..148,637.42 rows=207 width=300) (actual time=1,897.232..1,921.827 rows=2 loops=1)

  • Hash Cond: ((pr.org_id)::text = (od.org_id)::text)
18. 0.024 1,920.960 ↑ 103.5 2 1

Hash Left Join (cost=142,618.52..148,611.87 rows=207 width=287) (actual time=1,896.365..1,920.960 rows=2 loops=1)

  • Hash Cond: (pr.visit_type = vn.visit_type)
19. 21.082 1,920.360 ↑ 103.5 2 1

Hash Right Join (cost=142,617.43..148,609.66 rows=207 width=257) (actual time=1,895.767..1,920.360 rows=2 loops=1)

  • Hash Cond: (pppd.patient_policy_id = pri_plan.patient_policy_id)
20. 31.894 31.894 ↓ 1.0 185,023 1

Seq Scan on patient_policy_details pppd (cost=0.00..4,371.22 rows=185,022 width=17) (actual time=0.487..31.894 rows=185,023 loops=1)

21. 0.008 1,867.384 ↑ 103.5 2 1

Hash (cost=142,614.84..142,614.84 rows=207 width=248) (actual time=1,867.384..1,867.384 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 24.221 1,867.376 ↑ 103.5 2 1

Hash Right Join (cost=125,674.67..142,614.84 rows=207 width=248) (actual time=1,797.956..1,867.376 rows=2 loops=1)

  • Hash Cond: ((pri_plan.patient_id)::text = (pr.patient_id)::text)
23. 56.458 273.985 ↑ 1.0 452,899 1

Hash Right Join (cost=23,657.07..38,896.00 rows=453,179 width=20) (actual time=193.291..273.985 rows=452,899 loops=1)

  • Hash Cond: ((sec_plan.patient_id)::text = (pri_plan.patient_id)::text)
24. 3.828 25.599 ↓ 1.0 17,465 1

Gather (cost=1,000.00..12,402.21 rows=17,184 width=38) (actual time=1.232..25.599 rows=17,465 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
25. 21.771 21.771 ↑ 1.2 5,822 3 / 3

Parallel Seq Scan on patient_insurance_plans sec_plan (cost=0.00..9,683.81 rows=7,160 width=38) (actual time=0.020..21.771 rows=5,822 loops=3)

  • Filter: (priority = 2)
  • Rows Removed by Filter: 150966
26. 57.020 191.928 ↑ 1.0 452,899 1

Hash (cost=14,336.33..14,336.33 rows=453,179 width=20) (actual time=191.928..191.928 rows=452,899 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3388kB
27. 60.571 134.908 ↑ 1.0 452,899 1

Hash Join (cost=9.86..14,336.33 rows=453,179 width=20) (actual time=0.393..134.908 rows=452,899 loops=1)

  • Hash Cond: ((pri_plan.sponsor_id)::text = (pri_tm.tpa_id)::text)
28. 73.956 73.956 ↑ 1.0 452,899 1

Seq Scan on patient_insurance_plans pri_plan (cost=0.00..13,113.54 rows=453,179 width=42) (actual time=0.008..73.956 rows=452,899 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 17465
29. 0.025 0.381 ↑ 1.0 216 1

Hash (cost=7.16..7.16 rows=216 width=10) (actual time=0.381..0.381 rows=216 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
30. 0.356 0.356 ↑ 1.0 216 1

Seq Scan on tpa_master pri_tm (cost=0.00..7.16 rows=216 width=10) (actual time=0.004..0.356 rows=216 loops=1)

31. 0.006 1,569.170 ↑ 103.5 2 1

Hash (cost=102,015.01..102,015.01 rows=207 width=260) (actual time=1,569.170..1,569.170 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.006 1,569.164 ↑ 103.5 2 1

Hash Join (cost=81,639.35..102,015.01 rows=207 width=260) (actual time=1,568.996..1,569.164 rows=2 loops=1)

  • Hash Cond: (pr.center_id = hcm.center_id)
33. 0.015 1,568.435 ↑ 71.2 32 1

Hash Left Join (cost=81,634.20..102,001.58 rows=2,277 width=250) (actual time=1,337.052..1,568.435 rows=32 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
34. 0.016 1,566.964 ↑ 71.2 32 1

Hash Left Join (cost=81,575.70..101,937.10 rows=2,277 width=241) (actual time=1,335.593..1,566.964 rows=32 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
35. 35.141 1,566.946 ↑ 71.2 32 1

Hash Right Join (cost=81,559.40..101,914.83 rows=2,277 width=193) (actual time=1,335.584..1,566.946 rows=32 loops=1)

  • Hash Cond: ((pd.mr_no)::text = (pr.mr_no)::text)
36. 198.384 198.384 ↑ 1.0 494,849 1

Seq Scan on patient_details pd (cost=0.00..18,476.02 rows=495,102 width=47) (actual time=0.298..198.384 rows=494,849 loops=1)

37. 0.000 1,333.421 ↑ 71.2 32 1

Hash (cost=81,530.94..81,530.94 rows=2,277 width=161) (actual time=1,333.421..1,333.421 rows=32 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 38kB
38. 21.474 1,333.438 ↑ 71.2 32 1

Gather (cost=42,383.38..81,530.94 rows=2,277 width=161) (actual time=948.695..1,333.438 rows=32 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
39. 19.008 1,311.964 ↑ 86.3 11 3 / 3

Hash Join (cost=41,383.38..80,303.24 rows=949 width=161) (actual time=936.466..1,311.964 rows=11 loops=3)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
40. 771.206 771.206 ↑ 86.3 11 3 / 3

Parallel Seq Scan on bill b (cost=0.00..32,846.37 rows=949 width=92) (actual time=411.997..771.206 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: 193732
41. 128.883 521.750 ↑ 1.0 515,425 3 / 3

Hash (cost=28,895.17..28,895.17 rows=515,617 width=69) (actual time=521.750..521.750 rows=515,425 loops=3)

  • Buckets: 65536 Batches: 16 Memory Usage: 3271kB
42. 392.867 392.867 ↑ 1.0 515,425 3 / 3

Seq Scan on patient_registration pr (cost=0.00..28,895.17 rows=515,617 width=69) (actual time=0.193..392.867 rows=515,425 loops=3)

43. 0.000 0.002 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
44. 0.002 0.002 ↓ 0.0 0 1

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

45. 0.134 1.456 ↑ 1.0 1,400 1

Hash (cost=41.00..41.00 rows=1,400 width=9) (actual time=1.456..1.456 rows=1,400 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 73kB
46. 1.322 1.322 ↑ 1.0 1,400 1

Seq Scan on incoming_sample_registration isr (cost=0.00..41.00 rows=1,400 width=9) (actual time=0.004..1.322 rows=1,400 loops=1)

47. 0.004 0.723 ↑ 1.0 1 1

Hash (cost=5.14..5.14 rows=1 width=18) (actual time=0.723..0.723 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 0.719 0.719 ↑ 1.0 1 1

Seq Scan on hospital_center_master hcm (cost=0.00..5.14 rows=1 width=18) (actual time=0.717..0.719 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 10
49. 0.017 0.576 ↑ 1.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.559 0.559 ↑ 1.0 4 1

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

51. 0.087 0.845 ↑ 1.0 667 1

Hash (cost=16.67..16.67 rows=667 width=29) (actual time=0.845..0.845 rows=667 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 48kB
52. 0.758 0.758 ↑ 1.0 667 1

Seq Scan on organization_details od (cost=0.00..16.67 rows=667 width=29) (actual time=0.020..0.758 rows=667 loops=1)

53. 0.025 0.050 ↑ 1.0 216 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
54. 0.025 0.025 ↑ 1.0 216 1

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

55. 0.025 0.042 ↑ 1.0 216 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
56. 0.017 0.017 ↑ 1.0 216 1

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

57. 0.004 0.720 ↑ 1.0 18 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
58. 0.716 0.716 ↑ 1.0 18 1

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

59. 0.756 4.961 ↑ 1.0 6,339 1

Hash (cost=392.30..392.30 rows=6,339 width=17) (actual time=4.961..4.961 rows=6,339 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 367kB
60. 4.205 4.205 ↑ 1.0 6,339 1

Seq Scan on insurance_submission_batch firstsub (cost=0.00..392.30 rows=6,339 width=17) (actual time=0.005..4.205 rows=6,339 loops=1)

  • Filter: (is_resubmission = 'N'::bpchar)
  • Rows Removed by Filter: 3765
61. 4.020 39.621 ↓ 1.0 25,353 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1636kB
62. 17.427 35.601 ↓ 1.0 25,353 1

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

  • Group Key: insurance_claim_resubmission.claim_id
63. 18.174 18.174 ↑ 1.0 37,494 1

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

64. 114.843 6,813.365 ↑ 1.0 464,386 1

Materialize (cost=619,466.18..667,898.18 rows=484,320 width=42) (actual time=6,463.074..6,813.365 rows=464,386 loops=1)

65. 190.585 6,698.522 ↑ 1.0 464,386 1

Group (cost=619,466.18..661,844.18 rows=484,320 width=886) (actual time=6,463.070..6,698.522 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
66. 2,419.661 6,507.937 ↑ 1.0 482,195 1

Sort (cost=619,466.18..620,676.98 rows=484,320 width=886) (actual time=6,463.067..6,507.937 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: 69296kB
67. 380.345 4,088.276 ↓ 1.0 484,371 1

Merge Left Join (cost=164,196.97..196,299.81 rows=484,320 width=886) (actual time=3,277.264..4,088.276 rows=484,371 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_1.bill_no)::text)
68. 149.664 662.041 ↓ 1.0 484,321 1

Merge Left Join (cost=63,692.20..94,472.06 rows=484,320 width=442) (actual time=333.379..662.041 rows=484,321 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_2.bill_no)::text)
69. 177.023 177.023 ↓ 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.010..177.023 rows=484,321 loops=1)

  • Heap Fetches: 2862
70. 55.139 335.354 ↓ 152.9 35,619 1

Sort (cost=63,691.78..63,692.36 rows=233 width=442) (actual time=333.365..335.354 rows=35,619 loops=1)

  • Sort Key: bcl_2.bill_no
  • Sort Method: quicksort Memory: 3310kB
71. 84.559 280.215 ↓ 76.4 17,811 1

Hash Join (cost=42,806.89..63,682.62 rows=233 width=442) (actual time=142.636..280.215 rows=17,811 loops=1)

  • Hash Cond: ((bcl_2.claim_id)::text = (sic_1.claim_id)::text)
72. 53.039 53.039 ↓ 1.0 484,321 1

Seq Scan on bill_claim bcl_2 (cost=0.00..19,057.20 rows=484,320 width=28) (actual time=0.005..53.039 rows=484,321 loops=1)

73. 4.661 142.617 ↓ 74.8 17,888 1

Hash (cost=42,803.90..42,803.90 rows=239 width=428) (actual time=142.617..142.617 rows=17,888 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2006kB
74. 6.469 137.956 ↓ 74.8 17,888 1

Gather (cost=14,371.30..42,803.90 rows=239 width=428) (actual time=76.642..137.956 rows=17,888 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
75. 36.824 131.487 ↓ 59.6 5,963 3 / 3

Hash Join (cost=13,371.30..41,780.00 rows=100 width=428) (actual time=76.436..131.487 rows=5,963 loops=3)

  • Hash Cond: (((sic_1.patient_id)::text = (sip_1.patient_id)::text) AND (sic_1.plan_id = sip_1.plan_id))
76. 18.374 18.374 ↑ 1.3 165,342 3 / 3

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

77. 4.169 76.289 ↓ 1.0 17,449 3 / 3

Hash (cost=13,113.54..13,113.54 rows=17,184 width=20) (actual time=76.289..76.289 rows=17,449 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1143kB
78. 72.120 72.120 ↓ 1.0 17,465 3 / 3

Seq Scan on patient_insurance_plans sip_1 (cost=0.00..13,113.54 rows=17,184 width=20) (actual time=0.019..72.120 rows=17,465 loops=3)

  • Filter: (priority = 2)
  • Rows Removed by Filter: 452899
79. 2,134.795 3,045.890 ↓ 78.8 484,190 1

Sort (cost=100,504.76..100,520.12 rows=6,145 width=458) (actual time=2,943.879..3,045.890 rows=484,190 loops=1)

  • Sort Key: bcl_1.bill_no
  • Sort Method: external sort Disk: 66304kB
80. 325.618 911.095 ↓ 75.9 466,317 1

Hash Join (cost=79,183.23..100,118.08 rows=6,145 width=458) (actual time=540.136..911.095 rows=466,317 loops=1)

  • Hash Cond: ((bcl_1.claim_id)::text = (sic.claim_id)::text)
81. 45.507 45.507 ↓ 1.0 484,321 1

Seq Scan on bill_claim bcl_1 (cost=0.00..19,057.20 rows=484,320 width=28) (actual time=0.010..45.507 rows=484,321 loops=1)

82. 142.275 539.970 ↓ 72.8 457,924 1

Hash (cost=79,104.56..79,104.56 rows=6,294 width=444) (actual time=539.970..539.970 rows=457,924 loops=1)

  • Buckets: 32768 (originally 8192) Batches: 32 (originally 1) Memory Usage: 3841kB
83. 59.596 397.695 ↓ 72.8 457,924 1

Gather (cost=23,567.22..79,104.56 rows=6,294 width=444) (actual time=161.600..397.695 rows=457,924 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
84. 139.164 338.099 ↓ 58.2 152,641 3 / 3

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

  • Hash Cond: (((sic.patient_id)::text = (sip.patient_id)::text) AND (sic.plan_id = sip.plan_id))
85. 38.726 38.726 ↑ 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.012..38.726 rows=165,342 loops=3)

86. 70.591 160.209 ↑ 1.0 452,733 3 / 3

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

  • Buckets: 65536 Batches: 8 Memory Usage: 3372kB
87. 89.618 89.618 ↑ 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.009..89.618 rows=452,899 loops=3)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 17465
88. 51.897 17,879.857 ↓ 2.4 275,211 1

Materialize (cost=2,694,526.79..2,711,754.95 rows=116,801 width=170) (actual time=17,640.575..17,879.857 rows=275,211 loops=1)

89. 87.476 17,827.960 ↓ 2.4 275,211 1

GroupAggregate (cost=2,694,526.79..2,710,294.94 rows=116,801 width=388) (actual time=17,640.572..17,827.960 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
90. 1,401.509 17,740.484 ↑ 4.2 277,244 1

Sort (cost=2,694,526.79..2,697,446.82 rows=1,168,011 width=388) (actual time=17,640.563..17,740.484 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: 13544kB
91. 31.622 16,338.975 ↑ 4.2 277,835 1

Subquery Scan on all_insurance_remittance_details_view (cost=2,147,018.71..2,161,618.85 rows=1,168,011 width=388) (actual time=16,215.561..16,338.975 rows=277,835 loops=1)

92. 1,288.363 16,307.353 ↑ 4.2 277,835 1

Sort (cost=2,147,018.71..2,149,938.74 rows=1,168,011 width=400) (actual time=16,215.559..16,307.353 rows=277,835 loops=1)

  • Sort Key: bill_charge.bill_no DESC
  • Sort Method: external merge Disk: 14728kB
93. 244.171 15,018.990 ↑ 4.2 277,835 1

Unique (cost=1,585,690.57..1,606,130.77 rows=1,168,011 width=400) (actual time=13,657.954..15,018.990 rows=277,835 loops=1)

94. 4,497.446 14,774.819 ↑ 1.0 1,122,775 1

Sort (cost=1,585,690.57..1,588,610.60 rows=1,168,011 width=400) (actual time=13,657.953..14,774.819 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: 59128kB
95. 43.679 10,277.373 ↑ 1.0 1,122,775 1

Append (cost=191,355.38..1,044,802.63 rows=1,168,011 width=400) (actual time=3,044.096..10,277.373 rows=1,122,775 loops=1)

96. 157.531 4,006.345 ↑ 1.0 1,122,342 1

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

  • Hash Cond: (insurance_payment_allocation.remittance_id = ir.remittance_id)
97. 601.615 3,847.030 ↑ 1.0 1,123,175 1

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

  • Hash Cond: ((insurance_payment_allocation.charge_id)::text = (bill_charge.charge_id)::text)
98. 203.282 203.282 ↑ 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=0.008..203.282 rows=1,129,465 loops=1)

99. 577.605 3,042.133 ↑ 1.0 2,415,957 1

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

  • Buckets: 65536 Batches: 64 Memory Usage: 2602kB
100. 2,464.528 2,464.528 ↑ 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=0.006..2,464.528 rows=2,415,957 loops=1)

101. 0.789 1.784 ↑ 1.0 7,894 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 457kB
102. 0.995 0.995 ↑ 1.0 7,894 1

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

103. 3.601 4,823.576 ↓ 0.0 0 1

Hash Join (cost=505,210.64..524,727.36 rows=12,849 width=40) (actual time=4,823.576..4,823.576 rows=0 loops=1)

  • Hash Cond: ((ipa.charge_id)::text = (bc.charge_id)::text)
104. 116.067 2,433.096 ↓ 0.0 0 1

Hash Join (cost=314,186.87..318,991.86 rows=12,849 width=35) (actual time=2,433.096..2,433.096 rows=0 loops=1)

  • Hash Cond: (ipu.remittance_id = ipa.remittance_id)
105. 0.046 4.228 ↑ 3.3 34 1

Hash Join (cost=331.62..335.04 rows=113 width=22) (actual time=4.203..4.228 rows=34 loops=1)

  • Hash Cond: (ipu.remittance_id = ir_1.remittance_id)
106. 0.020 0.020 ↑ 1.0 113 1

Seq Scan on insurance_payment_unalloc_amount ipu (cost=0.00..3.13 rows=113 width=10) (actual time=0.011..0.020 rows=113 loops=1)

107. 1.909 4.162 ↑ 1.0 7,894 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 423kB
108. 2.253 2.253 ↑ 1.0 7,894 1

Seq Scan on insurance_remittance ir_1 (cost=0.00..232.94 rows=7,894 width=12) (actual time=0.006..2.253 rows=7,894 loops=1)

109. 200.684 2,312.801 ↓ 2.0 1,128,611 1

Hash (cost=303,487.11..303,487.11 rows=564,732 width=21) (actual time=2,312.801..2,312.801 rows=1,128,611 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 32 (originally 16) Memory Usage: 3585kB
110. 247.462 2,112.117 ↓ 2.0 1,128,611 1

Seq Scan on insurance_payment_allocation ipa (cost=273,139.80..303,487.11 rows=564,732 width=21) (actual time=1,873.303..2,112.117 rows=1,128,611 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 854
111.          

SubPlan (for Seq Scan)

112. 51.964 1,864.655 ↓ 2.5 63,712 1

Gather (cost=189,616.31..273,075.55 rows=25,697 width=4) (actual time=1,053.318..1,864.655 rows=63,712 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
113. 236.081 1,812.691 ↓ 2.0 21,237 3 / 3

Hash Join (cost=188,616.31..269,505.85 rows=10,707 width=4) (actual time=1,046.747..1,812.691 rows=21,237 loops=3)

  • Hash Cond: ((insurance_payment_allocation_1.charge_id)::text = (bc_2.charge_id)::text)
114. 69.119 600.300 ↓ 2.0 21,237 3 / 3

Hash Join (cost=4.54..68,695.98 rows=10,707 width=13) (actual time=2.250..600.300 rows=21,237 loops=3)

  • Hash Cond: (insurance_payment_allocation_1.remittance_id = ipu_2.remittance_id)
115. 531.122 531.122 ↑ 1.2 376,488 3 / 3

Parallel Seq Scan on insurance_payment_allocation insurance_payment_allocation_1 (cost=0.00..20,935.10 rows=470,610 width=13) (actual time=0.017..531.122 rows=376,488 loops=3)

116. 0.024 0.059 ↑ 1.0 113 3 / 3

Hash (cost=3.13..3.13 rows=113 width=4) (actual time=0.059..0.059 rows=113 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
117. 0.035 0.035 ↑ 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.011..0.035 rows=113 loops=3)

118. 369.609 976.310 ↑ 1.0 2,415,957 3 / 3

Hash (cost=145,666.23..145,666.23 rows=2,470,523 width=9) (actual time=976.310..976.310 rows=2,415,957 loops=3)

  • Buckets: 131072 Batches: 64 Memory Usage: 2562kB
119. 606.701 606.701 ↑ 1.0 2,415,957 3 / 3

Seq Scan on bill_charge bc_2 (cost=0.00..145,666.23 rows=2,470,523 width=9) (actual time=0.012..606.701 rows=2,415,957 loops=3)

120. 476.164 2,386.879 ↑ 1.0 2,415,957 1

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

  • Buckets: 65536 Batches: 64 Memory Usage: 2602kB
121. 1,910.715 1,910.715 ↑ 1.0 2,415,957 1

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

122. 82.264 1,403.773 ↑ 59.3 433 1

Hash Join (cost=236,100.35..255,872.85 rows=25,697 width=40) (actual time=1,309.780..1,403.773 rows=433 loops=1)

  • Hash Cond: ((ipa_1.charge_id)::text = (bc_1.charge_id)::text)
123. 12.222 334.725 ↑ 59.3 433 1

Hash Join (cost=47,488.58..54,627.62 rows=25,697 width=49) (actual time=322.567..334.725 rows=433 loops=1)

  • Hash Cond: (ipu_1.remittance_id = ipa_1.remittance_id)
124. 0.039 2.325 ↑ 3.3 34 1

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

  • Hash Cond: (ipu_1.remittance_id = ir_2.remittance_id)
125. 0.022 0.022 ↑ 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.013..0.022 rows=113 loops=1)

126. 0.985 2.264 ↑ 1.0 7,894 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 423kB
127. 1.279 1.279 ↑ 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.279 rows=7,894 loops=1)

128. 172.608 320.178 ↑ 1.0 1,129,465 1

Hash (cost=27,523.65..27,523.65 rows=1,129,465 width=13) (actual time=320.178..320.178 rows=1,129,465 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 2439kB
129. 147.570 147.570 ↑ 1.0 1,129,465 1

Seq Scan on insurance_payment_allocation ipa_1 (cost=0.00..27,523.65 rows=1,129,465 width=13) (actual time=0.012..147.570 rows=1,129,465 loops=1)

130. 437.890 986.784 ↑ 1.0 2,415,957 1

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

  • Buckets: 131072 Batches: 64 Memory Usage: 2562kB
131. 548.894 548.894 ↑ 1.0 2,415,957 1

Seq Scan on bill_charge bc_1 (cost=0.00..145,666.23 rows=2,470,523 width=9) (actual time=0.007..548.894 rows=2,415,957 loops=1)

132. 1.706 3.399 ↑ 1.0 10,104 1

Hash (cost=367.04..367.04 rows=10,104 width=17) (actual time=3.399..3.399 rows=10,104 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 611kB
133. 1.693 1.693 ↑ 1.0 10,104 1

Seq Scan on insurance_submission_batch resub (cost=0.00..367.04 rows=10,104 width=17) (actual time=0.014..1.693 rows=10,104 loops=1)

Planning time : 49.273 ms
Execution time : 27,399.440 ms