explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mZqs

Settings
# exclusive inclusive rows x rows loops node
1. 127.269 1,143,314.020 ↓ 65.3 57,345 1

Hash Left Join (cost=91,466,925.09..91,509,426.43 rows=878 width=561) (actual time=1,142,922.842..1,143,314.020 rows=57,345 loops=1)

  • Hash Cond: ((max((insurance_claim_resubmission.resubmission_batch_id)::text)) = (resub.submission_batch_id)::text)
2. 26.475 1,143,060.265 ↓ 65.3 57,345 1

Hash Left Join (cost=91,452,011.27..91,494,462.01 rows=878 width=505) (actual time=1,142,795.968..1,143,060.265 rows=57,345 loops=1)

  • Hash Cond: ((icl.submission_batch_id)::text = (firstsub.submission_batch_id)::text)
3. 433.279 1,142,725.384 ↓ 65.3 57,345 1

Merge Left Join (cost=91,437,579.47..91,480,027.90 rows=878 width=506) (actual time=1,142,487.147..1,142,725.384 rows=57,345 loops=1)

  • Merge Cond: ((icl.claim_id)::text = (insurance_claim_resubmission.claim_id)::text)
4. 732.163 1,118,395.440 ↓ 65.3 57,345 1

Sort (cost=90,939,222.29..90,939,224.49 rows=878 width=488) (actual time=1,118,356.483..1,118,395.440 rows=57,345 loops=1)

  • Sort Key: icl.claim_id
  • Sort Method: external merge Disk: 15840kB
5. 2,203.481 1,117,663.277 ↓ 65.3 57,345 1

Merge Right Join (cost=90,283,227.55..90,939,179.37 rows=878 width=488) (actual time=1,117,257.409..1,117,663.277 rows=57,345 loops=1)

  • Merge Cond: ((all_insurance_remittance_details_view.bill_no)::text = (b.bill_no)::text)
6. 3,871.832 1,027,044.124 ↓ 2.1 9,439,998 1

GroupAggregate (cost=86,738,971.40..87,339,326.75 rows=4,447,077 width=388) (actual time=1,014,915.485..1,027,044.124 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
7. 64,125.179 1,023,172.292 ↑ 4.7 9,525,298 1

Sort (cost=86,738,971.40..86,850,148.32 rows=44,470,766 width=388) (actual time=1,014,915.468..1,023,172.292 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
8. 1,687.279 959,047.113 ↑ 4.7 9,525,414 1

Subquery Scan on all_insurance_remittance_details_view (cost=64,725,912.73..65,281,797.30 rows=44,470,766 width=388) (actual time=951,560.482..959,047.113 rows=9,525,414 loops=1)

9. 57,790.829 957,359.834 ↑ 4.7 9,525,414 1

Sort (cost=64,725,912.73..64,837,089.64 rows=44,470,766 width=400) (actual time=951,560.476..957,359.834 rows=9,525,414 loops=1)

  • Sort Key: bill_charge.bill_no DESC
  • Sort Method: external merge Disk: 518336kB
10. 15,197.909 899,569.005 ↑ 4.7 9,525,414 1

Unique (cost=42,186,504.22..42,964,742.63 rows=44,470,766 width=400) (actual time=800,371.919..899,569.005 rows=9,525,414 loops=1)

11. 470,823.850 884,371.096 ↑ 1.0 43,721,796 1

Sort (cost=42,186,504.22..42,297,681.14 rows=44,470,766 width=400) (actual time=800,371.905..884,371.096 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
12. 4,531.025 413,547.246 ↑ 1.0 43,721,796 1

Append (cost=4,019,707.48..20,425,334.12 rows=44,470,766 width=400) (actual time=80,502.382..413,547.246 rows=43,721,796 loops=1)

13. 29,411.453 201,066.282 ↑ 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=80,502.381..201,066.282 rows=43,686,604 loops=1)

  • Hash Cond: (insurance_payment_allocation.remittance_id = ir.remittance_id)
14. 69,937.821 170,662.879 ↑ 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=79,494.911..170,662.879 rows=43,697,103 loops=1)

  • Hash Cond: ((insurance_payment_allocation.charge_id)::text = (bill_charge.charge_id)::text)
15. 21,240.134 21,240.134 ↑ 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=1.081..21,240.134 rows=43,697,103 loops=1)

16. 22,117.033 79,484.924 ↑ 1.0 50,386,914 1

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

  • Buckets: 524288 Batches: 256 Memory Usage: 15174kB
17. 57,367.891 57,367.891 ↑ 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.022..57,367.891 rows=50,386,914 loops=1)

18. 340.983 991.950 ↑ 1.0 567,587 1

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

  • Buckets: 524288 Batches: 4 Memory Usage: 11310kB
19. 650.967 650.967 ↑ 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.028..650.967 rows=567,587 loops=1)

20. 41.691 145,163.571 ↓ 0.0 0 1

Hash Join (cost=8,243,584.17..8,715,775.50 rows=227,858 width=43) (actual time=145,163.571..145,163.571 rows=0 loops=1)

  • Hash Cond: ((ipa.charge_id)::text = (bc.charge_id)::text)
21. 170.562 72,897.469 ↓ 0.0 0 1

Merge Join (cost=4,253,402.22..4,424,235.42 rows=227,858 width=39) (actual time=72,897.469..72,897.469 rows=0 loops=1)

  • Merge Cond: (ipu.remittance_id = ipa.remittance_id)
22. 1.262 45.463 ↑ 1.4 222 1

Merge Join (cost=21.28..4,679.42 rows=302 width=23) (actual time=1.921..45.463 rows=222 loops=1)

  • Merge Cond: (ir_1.remittance_id = ipu.remittance_id)
23. 43.224 43.224 ↑ 41.7 14,213 1

Index Scan using insurance_remittance_pkey on insurance_remittance ir_1 (cost=0.42..180,133.23 rows=592,646 width=13) (actual time=0.023..43.224 rows=14,213 loops=1)

24. 0.111 0.977 ↑ 1.0 302 1

Sort (cost=20.46..21.21 rows=302 width=10) (actual time=0.952..0.977 rows=302 loops=1)

  • Sort Key: ipu.remittance_id
  • Sort Method: quicksort Memory: 39kB
25. 0.866 0.866 ↑ 1.0 302 1

Seq Scan on insurance_payment_unalloc_amount ipu (cost=0.00..8.02 rows=302 width=10) (actual time=0.435..0.866 rows=302 loops=1)

26. 1,020.757 72,681.444 ↑ 8.4 2,604,881 1

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa (cost=4,253,380.94..6,914,113.93 rows=21,893,596 width=24) (actual time=71,710.967..72,681.444 rows=2,604,881 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 1826
27.          

SubPlan (for Index Scan)

28. 489.950 71,660.687 ↑ 2.8 163,272 1

Gather (cost=176,368.59..4,252,241.08 rows=455,716 width=4) (actual time=27,709.590..71,660.687 rows=163,272 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
29. 9,224.402 71,170.737 ↑ 3.5 54,424 3 / 3

Hash Join (cost=175,368.59..4,205,669.48 rows=189,882 width=4) (actual time=26,364.266..71,170.737 rows=54,424 loops=3)

  • Hash Cond: ((bc_2.charge_id)::text = (insurance_payment_allocation_1.charge_id)::text)
30. 46,863.456 46,863.456 ↑ 1.3 16,795,638 3 / 3

Parallel Seq Scan on bill_charge bc_2 (cost=0.00..2,763,087.17 rows=21,134,018 width=10) (actual time=0.011..46,863.456 rows=16,795,638 loops=3)

31. 73.585 15,082.879 ↑ 2.8 163,272 3 / 3

Hash (cost=167,446.14..167,446.14 rows=455,716 width=14) (actual time=15,082.879..15,082.879 rows=163,272 loops=3)

  • Buckets: 524288 Batches: 2 Memory Usage: 7769kB
32. 506.704 15,009.294 ↑ 2.8 163,272 3 / 3

Merge Join (cost=21.02..167,446.14 rows=455,716 width=14) (actual time=0.193..15,009.294 rows=163,272 loops=3)

  • Merge Cond: (insurance_payment_allocation_1.remittance_id = ipu_2.remittance_id)
33. 14,479.654 14,479.654 ↑ 16.8 2,606,707 3 / 3

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation insurance_payment_allocation_1 (cost=0.56..2,551,219.92 rows=43,787,192 width=14) (actual time=0.041..14,479.654 rows=2,606,707 loops=3)

34. 22.861 22.936 ↓ 540.0 163,090 3 / 3

Sort (cost=20.46..21.21 rows=302 width=4) (actual time=0.148..22.936 rows=163,090 loops=3)

  • Sort Key: ipu_2.remittance_id
  • Sort Method: quicksort Memory: 39kB
35. 0.075 0.075 ↑ 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.018..0.075 rows=302 loops=3)

36. 20,897.448 72,224.411 ↑ 1.0 50,386,914 1

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

  • Buckets: 524288 Batches: 256 Memory Usage: 15174kB
37. 51,326.963 51,326.963 ↑ 1.0 50,386,914 1

Seq Scan on bill_charge bc (cost=0.00..3,058,963.42 rows=50,721,642 width=24) (actual time=0.111..51,326.963 rows=50,386,914 loops=1)

38. 664.456 62,786.368 ↑ 12.9 35,192 1

Gather (cost=180,977.48..4,259,074.97 rows=455,716 width=41) (actual time=15,673.345..62,786.368 rows=35,192 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
39. 10,889.119 62,121.912 ↑ 16.2 11,731 3 / 3

Hash Join (cost=179,977.48..4,212,503.37 rows=189,882 width=41) (actual time=15,431.455..62,121.912 rows=11,731 loops=3)

  • Hash Cond: ((bc_1.charge_id)::text = (ipa_1.charge_id)::text)
40. 50,242.973 50,242.973 ↑ 1.3 16,795,638 3 / 3

Parallel Seq Scan on bill_charge bc_1 (cost=0.00..2,763,087.17 rows=21,134,018 width=10) (actual time=0.013..50,242.973 rows=16,795,638 loops=3)

41. 13.025 989.820 ↑ 12.9 35,192 3 / 3

Hash (cost=169,830.03..169,830.03 rows=455,716 width=51) (actual time=989.820..989.820 rows=35,192 loops=3)

  • Buckets: 262144 Batches: 4 Memory Usage: 2922kB
42. 236.833 976.795 ↑ 12.9 35,192 3 / 3

Merge Join (cost=21.85..169,830.03 rows=455,716 width=51) (actual time=25.213..976.795 rows=35,192 loops=3)

  • Merge Cond: (ipu_1.remittance_id = ipa_1.remittance_id)
43. 1.249 11.969 ↑ 1.4 222 3 / 3

Merge Join (cost=21.28..4,679.42 rows=302 width=45) (actual time=0.496..11.969 rows=222 loops=3)

  • Merge Cond: (ir_2.remittance_id = ipu_1.remittance_id)
44. 10.449 10.449 ↑ 41.7 14,213 3 / 3

Index Scan using insurance_remittance_pkey on insurance_remittance ir_2 (cost=0.42..180,133.23 rows=592,646 width=13) (actual time=0.043..10.449 rows=14,213 loops=3)

45. 0.167 0.271 ↑ 1.0 302 3 / 3

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

  • Sort Key: ipu_1.remittance_id
  • Sort Method: quicksort Memory: 48kB
46. 0.104 0.104 ↑ 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.021..0.104 rows=302 loops=3)

47. 727.993 727.993 ↑ 16.6 2,641,674 3 / 3

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa_1 (cost=0.56..2,551,265.58 rows=43,787,192 width=14) (actual time=0.036..727.993 rows=2,641,674 loops=3)

48. 664.904 88,415.672 ↓ 65.3 57,345 1

Sort (cost=3,544,256.15..3,544,258.34 rows=878 width=366) (actual time=88,392.201..88,415.672 rows=57,345 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: external sort Disk: 15352kB
49. 31.709 87,750.768 ↓ 64.9 56,942 1

Hash Left Join (cost=3,229,998.80..3,544,213.22 rows=878 width=366) (actual time=82,690.009..87,750.768 rows=56,942 loops=1)

  • Hash Cond: ((b.bill_no)::text = (bclm2.bill_no)::text)
50. 1,377.734 87,122.921 ↓ 64.9 56,942 1

Hash Left Join (cost=3,039,236.44..3,353,448.55 rows=878 width=352) (actual time=82,093.516..87,122.921 rows=56,942 loops=1)

  • Hash Cond: ((bclm.claim_id)::text = (icl.claim_id)::text)
51. 28.143 78,454.151 ↓ 64.9 56,942 1

Hash Left Join (cost=2,543,653.53..2,803,466.34 rows=878 width=343) (actual time=73,557.629..78,454.151 rows=56,942 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
52. 15.753 78,425.989 ↓ 64.9 56,942 1

Hash Left Join (cost=2,543,652.05..2,803,462.09 rows=878 width=347) (actual time=73,557.592..78,425.989 rows=56,942 loops=1)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stpa.tpa_id)::text)
53. 2,439.465 78,409.894 ↓ 64.9 56,942 1

Hash Right Join (cost=2,543,603.03..2,803,410.77 rows=878 width=322) (actual time=73,557.225..78,409.894 rows=56,942 loops=1)

  • Hash Cond: (pppd.patient_policy_id = pip.patient_policy_id)
  • Join Filter: (pip.priority = 1)
  • Rows Removed by Join Filter: 104
54. 2,568.900 2,568.900 ↓ 1.0 8,324,135 1

Seq Scan on patient_policy_details pppd (cost=0.00..228,631.46 rows=8,310,746 width=17) (actual time=34.824..2,568.900 rows=8,324,135 loops=1)

55. 63.115 73,401.529 ↓ 64.9 56,942 1

Hash (cost=2,543,592.06..2,543,592.06 rows=878 width=317) (actual time=73,401.529..73,401.529 rows=56,942 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 16618kB
56. 2,022.637 73,338.414 ↓ 64.9 56,942 1

Hash Right Join (cost=2,288,426.71..2,543,592.06 rows=878 width=317) (actual time=69,984.864..73,338.414 rows=56,942 loops=1)

  • Hash Cond: ((bclm.bill_no)::text = (b.bill_no)::text)
57. 1,331.052 1,331.052 ↑ 1.0 8,199,026 1

Seq Scan on bill_claim bclm (cost=0.00..224,400.03 rows=8,202,303 width=28) (actual time=0.063..1,331.052 rows=8,199,026 loops=1)

58. 59.088 69,984.725 ↓ 64.7 56,838 1

Hash (cost=2,288,415.74..2,288,415.74 rows=878 width=303) (actual time=69,984.724..69,984.725 rows=56,838 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 15754kB
59. 2,483.049 69,925.637 ↓ 64.7 56,838 1

Hash Right Join (cost=2,027,950.89..2,288,415.74 rows=878 width=303) (actual time=67,127.922..69,925.637 rows=56,838 loops=1)

  • Hash Cond: ((pip.patient_id)::text = (pr.patient_id)::text)
60. 2,217.288 2,217.288 ↑ 1.0 8,856,284 1

Seq Scan on patient_insurance_plans pip (cost=0.00..227,244.93 rows=8,856,493 width=24) (actual time=0.883..2,217.288 rows=8,856,284 loops=1)

61. 63.296 65,225.300 ↓ 64.7 56,786 1

Hash (cost=2,027,939.91..2,027,939.91 rows=878 width=311) (actual time=65,225.300..65,225.300 rows=56,786 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 16099kB
62. 45.483 65,162.004 ↓ 64.7 56,786 1

Hash Join (cost=1,752,009.04..2,027,939.91 rows=878 width=311) (actual time=58,278.355..65,162.004 rows=56,786 loops=1)

  • Hash Cond: (pr.center_id = hcm.center_id)
63. 3,918.683 65,116.457 ↓ 6.1 248,414 1

Hash Right Join (cost=1,751,998.45..2,027,813.48 rows=40,410 width=297) (actual time=58,276.883..65,116.457 rows=248,414 loops=1)

  • Hash Cond: ((bclm1.bill_no)::text = (b.bill_no)::text)
64. 2,923.173 2,923.173 ↑ 1.0 8,149,499 1

Seq Scan on bill_claim bclm1 (cost=0.00..244,905.79 rows=8,160,836 width=28) (actual time=1.886..2,923.173 rows=8,149,499 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 49527
65. 269.036 58,274.601 ↓ 6.1 248,414 1

Hash (cost=1,751,493.33..1,751,493.33 rows=40,410 width=283) (actual time=58,274.601..58,274.601 rows=248,414 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 4 (originally 1) Memory Usage: 19457kB
66. 669.047 58,005.565 ↓ 6.1 248,414 1

Gather Merge (cost=1,729,360.14..1,751,493.33 rows=40,410 width=283) (actual time=57,009.459..58,005.565 rows=248,414 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
67. 110.521 57,336.518 ↓ 4.9 82,805 3 / 3

Merge Left Join (cost=1,728,360.12..1,745,828.99 rows=16,838 width=283) (actual time=56,828.668..57,336.518 rows=82,805 loops=3)

  • Merge Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
68. 46.577 56,944.027 ↓ 4.9 82,805 3 / 3

Merge Left Join (cost=1,728,359.70..1,728,445.30 rows=16,838 width=274) (actual time=56,828.407..56,944.027 rows=82,805 loops=3)

  • Merge Cond: ((b.visit_id)::text = (prc.customer_id)::text)
69. 829.826 56,897.398 ↓ 4.9 82,805 3 / 3

Sort (cost=1,728,335.52..1,728,377.61 rows=16,838 width=226) (actual time=56,828.347..56,897.398 rows=82,805 loops=3)

  • Sort Key: b.visit_id
  • Sort Method: external merge Disk: 19904kB
70. 35.213 56,067.572 ↓ 4.9 82,805 3 / 3

Hash Left Join (cost=974,944.68..1,727,153.54 rows=16,838 width=226) (actual time=41,490.520..56,067.572 rows=82,805 loops=3)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
71. 39.158 55,958.829 ↓ 4.9 82,805 3 / 3

Hash Left Join (cost=974,895.66..1,727,060.07 rows=16,838 width=211) (actual time=41,416.964..55,958.829 rows=82,805 loops=3)

  • Hash Cond: ((pr.org_id)::text = (od.org_id)::text)
72. 35.287 55,917.988 ↓ 4.9 82,805 3 / 3

Hash Left Join (cost=974,830.67..1,726,950.77 rows=16,838 width=197) (actual time=41,415.243..55,917.988 rows=82,805 loops=3)

  • Hash Cond: (pr.visit_type = vn.visit_type)
73. 690.958 55,882.661 ↓ 4.9 82,805 3 / 3

Hash Left Join (cost=974,829.58..1,726,858.64 rows=16,838 width=195) (actual time=41,415.040..55,882.661 rows=82,805 loops=3)

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
74. 5,727.182 46,533.739 ↓ 4.9 82,805 3 / 3

Hash Join (cost=778,478.97..1,498,963.83 rows=16,838 width=161) (actual time=28,124.215..46,533.739 rows=82,805 loops=3)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
75. 13,152.336 13,152.336 ↓ 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=164.669..13,152.336 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
76. 5,058.544 27,654.221 ↑ 1.0 9,550,751 3 / 3

Hash (cost=545,086.43..545,086.43 rows=9,636,843 width=69) (actual time=27,654.221..27,654.221 rows=9,550,751 loops=3)

  • Buckets: 262144 Batches: 64 Memory Usage: 14874kB
77. 22,595.677 22,595.677 ↑ 1.0 9,550,751 3 / 3

Seq Scan on patient_registration pr (cost=0.00..545,086.43 rows=9,636,843 width=69) (actual time=0.195..22,595.677 rows=9,550,751 loops=3)

78. 1,351.353 8,657.964 ↑ 1.0 3,118,543 3 / 3

Hash (cost=126,331.94..126,331.94 rows=3,144,694 width=49) (actual time=8,657.964..8,657.964 rows=3,118,543 loops=3)

  • Buckets: 262144 Batches: 16 Memory Usage: 17309kB
79. 7,306.611 7,306.611 ↑ 1.0 3,118,543 3 / 3

Seq Scan on patient_details pd (cost=0.00..126,331.94 rows=3,144,694 width=49) (actual time=0.199..7,306.611 rows=3,118,543 loops=3)

80. 0.010 0.040 ↑ 1.0 4 3 / 3

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

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

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

82. 0.492 1.683 ↑ 1.0 1,644 3 / 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 116kB
83. 1.191 1.191 ↑ 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.020..1.191 rows=1,644 loops=3)

84. 0.226 73.530 ↑ 1.0 712 3 / 3

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

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

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

86. 0.024 0.052 ↓ 0.0 0 3 / 3

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

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

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

88. 281.970 281.970 ↓ 1.0 497,052 3 / 3

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

  • Heap Fetches: 6787
89. 0.004 0.064 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
90. 0.060 0.060 ↑ 1.0 1 1

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

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 45
91. 0.167 0.342 ↑ 1.0 712 1

Hash (cost=40.12..40.12 rows=712 width=35) (actual time=0.342..0.342 rows=712 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
92. 0.175 0.175 ↑ 1.0 712 1

Seq Scan on tpa_master stpa (cost=0.00..40.12 rows=712 width=35) (actual time=0.009..0.175 rows=712 loops=1)

93. 0.010 0.019 ↑ 1.0 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
94. 0.009 0.009 ↑ 1.0 21 1

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

95. 2,714.214 7,291.036 ↑ 1.0 9,226,628 1

Hash (cost=325,390.96..325,390.96 rows=9,269,996 width=23) (actual time=7,291.036..7,291.036 rows=9,226,628 loops=1)

  • Buckets: 524288 Batches: 32 Memory Usage: 19359kB
96. 4,576.822 4,576.822 ↑ 1.0 9,226,628 1

Seq Scan on insurance_claim icl (cost=0.00..325,390.96 rows=9,269,996 width=23) (actual time=0.738..4,576.822 rows=9,226,628 loops=1)

97. 19.226 596.138 ↓ 1.2 49,527 1

Hash (cost=190,244.03..190,244.03 rows=41,467 width=28) (actual time=596.138..596.138 rows=49,527 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3507kB
98. 8.301 576.912 ↓ 1.2 49,527 1

Gather (cost=1,000.00..190,244.03 rows=41,467 width=28) (actual time=2.360..576.912 rows=49,527 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
99. 568.611 568.611 ↑ 1.0 16,509 3 / 3

Parallel Seq Scan on bill_claim bclm2 (cost=0.00..185,097.33 rows=17,278 width=28) (actual time=0.072..568.611 rows=16,509 loops=3)

  • Filter: (priority = 2)
  • Rows Removed by Filter: 2716500
100. 1,157.574 23,896.665 ↓ 1.4 1,373,759 1

GroupAggregate (cost=498,357.17..528,785.25 rows=961,205 width=46) (actual time=19,033.141..23,896.665 rows=1,373,759 loops=1)

  • Group Key: insurance_claim_resubmission.claim_id
101. 20,100.128 22,739.091 ↑ 1.0 2,740,689 1

Sort (cost=498,357.17..505,295.85 rows=2,775,470 width=23) (actual time=19,033.112..22,739.091 rows=2,740,689 loops=1)

  • Sort Key: insurance_claim_resubmission.claim_id
  • Sort Method: external merge Disk: 91968kB
102. 2,638.963 2,638.963 ↑ 1.0 2,775,287 1

Seq Scan on insurance_claim_resubmission (cost=0.00..144,401.70 rows=2,775,470 width=23) (actual time=1.487..2,638.963 rows=2,775,287 loops=1)

103. 50.164 308.406 ↓ 1.0 160,352 1

Hash (cost=12,440.12..12,440.12 rows=159,334 width=17) (actual time=308.406..308.406 rows=160,352 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9721kB
104. 258.242 258.242 ↓ 1.0 160,352 1

Seq Scan on insurance_submission_batch firstsub (cost=0.00..12,440.12 rows=159,334 width=17) (actual time=8.301..258.242 rows=160,352 loops=1)

  • Filter: (is_resubmission = 'N'::bpchar)
  • Rows Removed by Filter: 87373
105. 63.468 126.486 ↓ 1.0 247,725 1

Hash (cost=11,821.70..11,821.70 rows=247,370 width=17) (actual time=126.486..126.486 rows=247,725 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 13902kB
106. 63.018 63.018 ↓ 1.0 247,725 1

Seq Scan on insurance_submission_batch resub (cost=0.00..11,821.70 rows=247,370 width=17) (actual time=0.010..63.018 rows=247,725 loops=1)

Planning time : 452.404 ms
Execution time : 1,143,772.628 ms