explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jrz

Settings
# exclusive inclusive rows x rows loops node
1. 2.774 678,117.547 ↓ 2.0 1,574 1

Sort (cost=51,121,572.81..51,121,574.80 rows=796 width=559) (actual time=678,117.419..678,117.547 rows=1,574 loops=1)

  • Sort Key: (date(b.open_date))
  • Sort Method: quicksort Memory: 813kB
2. 7.035 678,114.773 ↓ 2.0 1,574 1

Nested Loop Left Join (cost=49,480,234.61..51,121,534.46 rows=796 width=559) (actual time=677,669.775..678,114.773 rows=1,574 loops=1)

3. 79.136 677,950.338 ↓ 2.0 1,574 1

Merge Right Join (cost=49,480,234.19..51,121,124.04 rows=796 width=479) (actual time=677,666.880..677,950.338 rows=1,574 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (b.bill_no)::text)
4. 213.937 240,982.820 ↑ 20.2 372,912 1

Group (cost=6,897,282.27..7,556,239.17 rows=7,530,936 width=1,683) (actual time=240,415.075..240,982.820 rows=372,912 loops=1)

  • Group Key: bcl.bill_no, bcl_1.claim_id, sic.submission_batch_id, sic.main_visit_id, sic.status, sic.resubmission_count, sic.closure_type, sic.action_remarks, sic.attachment, sic.attachment_content_type, sic.resubmiss
5. 10,714.493 240,768.883 ↑ 20.2 373,172 1

Sort (cost=6,897,282.27..6,916,109.61 rows=7,530,936 width=1,683) (actual time=240,415.069..240,768.883 rows=373,172 loops=1)

  • Sort Key: bcl.bill_no, bcl_1.claim_id, sic.submission_batch_id, sic.main_visit_id, sic.status, sic.resubmission_count, sic.closure_type, sic.action_remarks, sic.attachment, sic.attachment_content_type, sic.resu
  • Sort Method: external merge Disk: 1386912kB
6. 7,469.790 230,054.390 ↑ 1.0 7,526,603 1

Merge Left Join (cost=1,711,969.79..2,102,463.78 rows=7,530,936 width=1,683) (actual time=204,415.346..230,054.390 rows=7,526,603 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_1.bill_no)::text)
7. 2,648.304 27,469.281 ↑ 1.0 7,526,601 1

Merge Left Join (cost=113,851.78..480,240.34 rows=7,530,936 width=841) (actual time=18,135.757..27,469.281 rows=7,526,601 loops=1)

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

Index Only Scan using bill_claim_bill_no_priority_key on bill_claim bcl (cost=0.56..347,533.36 rows=7,530,936 width=15) (actual time=0.042..6,666.164 rows=7,526,601 loops=1)

  • Heap Fetches: 2808216
9. 250.230 18,154.813 ↓ 132.0 97,047 1

Sort (cost=113,851.22..113,853.06 rows=735 width=841) (actual time=18,135.706..18,154.813 rows=97,047 loops=1)

  • Sort Key: bcl_2.bill_no
  • Sort Method: quicksort Memory: 10957kB
10. 30.726 17,904.583 ↓ 66.0 48,525 1

Gather (cost=1,001.55..113,816.23 rows=735 width=841) (actual time=1.996..17,904.583 rows=48,525 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
11. 46.769 17,873.857 ↓ 56.2 24,262 2 / 2

Nested Loop (cost=1.55..112,742.73 rows=432 width=841) (actual time=1.868..17,873.857 rows=24,262 loops=2)

12. 124.895 12,716.867 ↓ 52.0 25,174 2 / 2

Nested Loop (cost=0.99..112,412.46 rows=484 width=826) (actual time=1.199..12,716.867 rows=25,174 loops=2)

  • -> Parallel Index Scan using patient_insurance_plans_priority on patient_insurance_plans sip_1 (cost=0.43..6144.39 rows=27216 width=20) (actual time=0.034..78.929 rows=2449
  • Index Cond: (priority = 2)
13. 12,591.972 12,591.972 ↑ 1.0 1 48,996 / 2

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

  • Index Cond: ((patient_id)::text = (sip_1.patient_id)::text)
  • Filter: (sip_1.plan_id = plan_id)
  • Rows Removed by Filter: 1
14. 5,110.221 5,110.221 ↑ 1.0 1 50,347 / 2

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

  • Index Cond: ((claim_id)::text = (sic_1.claim_id)::text)
15. 788.562 195,115.319 ↓ 58.7 7,525,754 1

Materialize (cost=1,598,118.01..1,598,759.40 rows=128,277 width=857) (actual time=186,279.569..195,115.319 rows=7,525,754 loops=1)

16. 42,222.176 194,326.757 ↓ 58.3 7,477,209 1

Sort (cost=1,598,118.01..1,598,438.70 rows=128,277 width=857) (actual time=186,279.547..194,326.757 rows=7,477,209 loops=1)

  • Sort Key: bcl_1.bill_no
  • Sort Method: external merge Disk: 1358576kB
17. 357.495 152,104.581 ↓ 58.3 7,477,209 1

Gather (cost=1,001.67..1,552,469.41 rows=128,277 width=857) (actual time=2.801..152,104.581 rows=7,477,209 loops=1)

  • Workers Planned: 2
  • Workers Launched: 1
18. 3,925.840 151,747.086 ↓ 69.9 3,738,604 2 / 2

Nested Loop (cost=1.68..1,538,641.71 rows=53,449 width=857) (actual time=54.041..151,747.086 rows=3,738,604 loops=2)

19. 21,466.421 90,246.106 ↓ 68.7 4,112,510 2 / 2

Merge Join (cost=1.12..1,497,769.47 rows=59,897 width=842) (actual time=53.913..90,246.106 rows=4,112,510 loops=2)

  • Merge Cond: ((sip.patient_id)::text = (sic.patient_id)::text)
  • Join Filter: (sic.plan_id = sip.plan_id)
  • Rows Removed by Join Filter: 113665
  • -> Parallel Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sip (cost=0.56..522097.62 rows=3366184 width=20) (actual time=0.262..13459.405 ro
  • Filter: (priority = 1)
  • Rows Removed by Filter: 24498
20. 68,779.685 68,779.685 ↓ 1.0 8,458,951 2 / 2

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic (cost=0.56..832,660.62 rows=8,439,442 width=846) (actual time=0.061..68,779.685 rows=8,458,951 loops=2)

21. 57,575.140 57,575.140 ↑ 1.0 1 8,225,020 / 2

Index Scan using bill_claim_claim_idx on bill_claim bcl_1 (cost=0.56..0.65 rows=1 width=29) (actual time=0.014..0.014 rows=1 loops=8,225,020)

  • Index Cond: ((claim_id)::text = (sic.claim_id)::text)
22. 2.261 436,888.382 ↓ 2.0 1,574 1

Materialize (cost=42,582,951.93..43,320,108.95 rows=796 width=451) (actual time=436,771.659..436,888.382 rows=1,574 loops=1)

23. 90.317 436,886.121 ↓ 2.0 1,574 1

Merge Left Join (cost=42,582,951.93..43,320,106.96 rows=796 width=451) (actual time=436,771.649..436,886.121 rows=1,574 loops=1)

  • Merge Cond: ((b.bill_no)::text = (all_insurance_remittance_details_view.bill_no)::text)
24. 5.458 14,631.456 ↓ 1.5 1,176 1

Sort (cost=716,243.11..716,245.10 rows=796 width=329) (actual time=14,630.968..14,631.456 rows=1,176 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 621kB
25. 28.296 14,625.998 ↓ 1.5 1,176 1

Gather (cost=694,531.02..716,204.75 rows=796 width=329) (actual time=14,280.882..14,625.998 rows=1,176 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
26. 0.862 14,597.702 ↓ 1.2 392 3 / 3

Nested Loop Left Join (cost=693,531.02..715,125.15 rows=332 width=329) (actual time=14,258.687..14,597.702 rows=392 loops=3)

27. 0.629 14,439.256 ↓ 1.2 392 3 / 3

Nested Loop Left Join (cost=693,530.46..714,912.91 rows=332 width=334) (actual time=14,257.116..14,439.256 rows=392 loops=3)

28. 0.614 14,417.067 ↓ 1.2 392 3 / 3

Nested Loop Left Join (cost=693,530.04..714,759.29 rows=332 width=335) (actual time=14,254.027..14,417.067 rows=392 loops=3)

29. 0.650 14,403.517 ↓ 1.2 392 3 / 3

Nested Loop Left Join (cost=693,529.48..714,537.56 rows=332 width=326) (actual time=14,253.177..14,403.517 rows=392 loops=3)

30. 0.498 14,391.107 ↓ 1.2 392 3 / 3

Hash Left Join (cost=693,528.92..713,280.62 rows=332 width=312) (actual time=14,251.947..14,391.107 rows=392 loops=3)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
31. 0.494 14,390.564 ↓ 1.2 392 3 / 3

Nested Loop Left Join (cost=693,526.61..713,276.93 rows=332 width=316) (actual time=14,251.861..14,390.564 rows=392 loops=3)

32. 0.312 14,355.574 ↓ 1.2 392 3 / 3

Hash Left Join (cost=693,526.18..713,107.21 rows=332 width=307) (actual time=14,250.268..14,355.574 rows=392 loops=3)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stpa.tpa_id)::text)
33. 0.508 14,354.814 ↓ 1.2 392 3 / 3

Hash Left Join (cost=693,451.05..713,031.21 rows=332 width=283) (actual time=14,249.787..14,354.814 rows=392 loops=3)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
34. 0.681 14,353.700 ↓ 1.2 392 3 / 3

Nested Loop Left Join (cost=693,375.93..712,955.22 rows=332 width=269) (actual time=14,249.112..14,353.700 rows=392 loops=3)

35. 0.768 14,349.491 ↓ 1.2 392 3 / 3

Hash Left Join (cost=693,375.65..712,850.63 rows=332 width=256) (actual time=14,248.882..14,349.491 rows=392 loops=3)

  • Hash Cond: (pr.visit_type = vn.visit_type)
36. 0.724 14,348.691 ↓ 1.2 392 3 / 3

Nested Loop Left Join (cost=693,374.40..712,845.93 rows=332 width=254) (actual time=14,248.577..14,348.691 rows=392 loops=3)

37. 0.741 14,327.191 ↓ 1.2 392 3 / 3

Nested Loop (cost=693,373.01..712,296.09 rows=332 width=266) (actual time=14,248.273..14,327.191 rows=392 loops=3)

38. 1.114 14,323.943 ↑ 12.2 1,254 3 / 3

Nested Loop Left Join (cost=693,372.87..709,541.68 rows=15,247 width=252) (actual time=14,248.221..14,323.943 rows=1,254 loops=3)

39. 0.710 14,256.385 ↑ 12.2 1,254 3 / 3

Nested Loop Left Join (cost=693,372.44..702,013.30 rows=15,247 width=218) (actual time=14,248.144..14,256.385 rows=1,254 loops=3)

40. 1.002 14,249.407 ↑ 12.2 1,254 3 / 3

Merge Left Join (cost=693,372.02..693,449.68 rows=15,247 width=209) (actual time=14,248.081..14,249.407 rows=1,254 loops=3)

  • Merge Cond: ((b.visit_id)::text = (prc.customer_id)::text)
41. 11.224 14,248.326 ↑ 12.2 1,254 3 / 3

Sort (cost=693,342.24..693,380.36 rows=15,247 width=161) (actual time=14,247.989..14,248.326 rows=1,254 loops=3)

  • Sort Key: b.visit_id
  • Sort Method: quicksort Memory: 397kB
42. 6.610 14,237.102 ↑ 12.2 1,254 3 / 3

Nested Loop (cost=0.56..692,282.86 rows=15,247 width=161) (actual time=253.216..14,237.102 rows=1,254 loops=3)

43. 13,855.646 13,855.646 ↑ 12.2 1,254 3 / 3

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

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

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

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
45. 0.051 0.079 ↓ 0.0 0 3 / 3

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

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

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

47. 6.268 6.268 ↓ 0.0 0 3,761 / 3

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

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

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

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

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

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

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

51. 0.784 12.936 ↑ 1.0 1 1,176 / 3

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

52. 9.016 9.016 ↑ 1.0 1 1,176 / 3

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

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

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

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

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

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
55. 0.012 0.032 ↑ 1.0 4 3 / 3

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

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

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

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

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

  • Index Cond: ((pr.org_id)::text = (org_id)::text)
58. 0.275 0.606 ↓ 1.0 675 3 / 3

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

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

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

60. 0.265 0.448 ↓ 1.0 675 3 / 3

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

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

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

62. 34.496 34.496 ↑ 1.0 1 1,176 / 3

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

  • Index Cond: (patient_policy_id = pri_plan.patient_policy_id)
63. 0.015 0.045 ↑ 1.0 21 3 / 3

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

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

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

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

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

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

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

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

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

  • Index Cond: ((submission_batch_id)::text = (icl.submission_batch_id)::text)
  • Filter: (is_resubmission = 'N'::bpchar)
68. 157.584 157.584 ↑ 1.0 1 1,176 / 3

Index Scan using claim_reconciliation_pkey on claim_reconciliation icr (cost=0.56..0.64 rows=1 width=23) (actual time=0.402..0.402 rows=1 loops=1,176)

  • Index Cond: ((claim_id)::text = (icl.claim_id)::text)
69. 72.047 422,164.348 ↑ 8.0 483,111 1

Materialize (cost=41,866,708.82..42,594,151.11 rows=3,879,692 width=170) (actual time=421,803.750..422,164.348 rows=483,111 loops=1)

70. 120.852 422,092.301 ↑ 8.0 483,111 1

GroupAggregate (cost=41,866,708.82..42,468,061.12 rows=3,879,692 width=388) (actual time=421,803.744..422,092.301 rows=483,111 loops=1)

  • Group Key: all_insurance_remittance_details_view.bill_no, all_insurance_remittance_details_view.payment_reference, all_insurance_remittance_details_view.reference_no
71. 30,974.747 421,971.449 ↑ 79.4 488,557 1

Sort (cost=41,866,708.82..41,963,701.13 rows=38,796,923 width=388) (actual time=421,803.723..421,971.449 rows=488,557 loops=1)

  • Sort Key: all_insurance_remittance_details_view.bill_no, all_insurance_remittance_details_view.payment_reference, all_insurance_remittance_details_view.reference_no
  • Sort Method: external merge Disk: 415744kB
72. 838.041 390,996.702 ↑ 4.7 8,240,693 1

Subquery Scan on all_insurance_remittance_details_view (cost=30,790,173.02..32,051,073.02 rows=38,796,923 width=388) (actual time=387,426.095..390,996.702 rows=8,240,693 loops=1)

73. 30,961.492 390,158.661 ↑ 4.7 8,240,693 1

Sort (cost=30,790,173.02..30,887,165.33 rows=38,796,923 width=400) (actual time=387,426.090..390,158.661 rows=8,240,693 loops=1)

  • Sort Key: bill_charge.bill_no DESC
  • Sort Method: external merge Disk: 448680kB
74. 8,016.299 359,197.169 ↑ 4.7 8,240,693 1

Unique (cost=20,200,873.57..20,879,819.72 rows=38,796,923 width=400) (actual time=287,689.586..359,197.169 rows=8,240,693 loops=1)

75. 209,935.301 351,180.870 ↑ 1.0 38,200,977 1

Sort (cost=20,200,873.57..20,297,865.88 rows=38,796,923 width=400) (actual time=287,689.583..351,180.870 rows=38,200,977 loops=1)

  • Sort Key: bill_charge.bill_no, insurance_payment_allocation.payment_reference, ir.remittance_id, ir.received_date, ir.is_recovery, ir.reference_no
  • Sort Method: external merge Disk: 2075320kB
76. 2,911.168 141,245.569 ↑ 1.0 38,200,977 1

Append (cost=5,548,398.44..10,290,520.27 rows=38,796,923 width=400) (actual time=63,939.036..141,245.569 rows=38,200,977 loops=1)

77. 8,704.731 130,101.460 ↓ 1.0 38,165,785 1

Hash Join (cost=5,548,398.44..8,399,145.90 rows=38,160,955 width=44) (actual time=63,939.032..130,101.460 rows=38,165,785 loops=1)

  • Hash Cond: (insurance_payment_allocation.remittance_id = ir.remittance_id)
78. 37,446.742 121,085.876 ↓ 1.0 38,176,283 1

Hash Join (cost=5,512,424.25..8,262,996.44 rows=38,160,955 width=28) (actual time=63,626.511..121,085.876 rows=38,176,283 loops=1)

  • Hash Cond: ((insurance_payment_allocation.charge_id)::text = (bill_charge.charge_id)::text)
79. 20,030.141 20,030.141 ↓ 1.0 38,176,283 1

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

80. 13,401.657 63,608.993 ↓ 1.0 46,292,456 1

Hash (cost=3,737,387.80..3,737,387.80 rows=46,273,860 width=24) (actual time=63,608.993..63,608.993 rows=46,292,456 loops=1)

  • Buckets: 2097152 Batches: 64 Memory Usage: 57112kB
81. 50,207.336 50,207.336 ↓ 1.0 46,292,456 1

Seq Scan on bill_charge (cost=0.00..3,737,387.80 rows=46,273,860 width=24) (actual time=0.435..50,207.336 rows=46,292,456 loops=1)

82. 103.725 310.853 ↓ 1.0 431,759 1

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

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

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

84. 19.544 7,972.644 ↓ 0.0 0 1

Gather (cost=322,261.88..559,091.62 rows=211,989 width=44) (actual time=7,972.574..7,972.644 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
85. 0.002 7,953.100 ↓ 0.0 0 3 / 3

Nested Loop (cost=321,261.88..536,892.72 rows=88,329 width=44) (actual time=7,953.100..7,953.100 rows=0 loops=3)

86. 116.826 7,953.098 ↓ 0.0 0 3 / 3

Merge Join (cost=321,261.31..457,628.87 rows=88,329 width=40) (actual time=7,953.098..7,953.098 rows=0 loops=3)

  • Merge Cond: (ipa.remittance_id = ipu.remittance_id)
87. 5,701.614 7,799.513 ↑ 9.2 868,294 3 / 3

Parallel Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa (cost=320,913.04..2,237,536.32 rows=7,950,199 width=24) (actual time=2,131.663..7,799.513 rows=868,294 loops=3)

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

SubPlan (for Parallel Index Scan)

89. 44.896 2,097.899 ↑ 2.6 161,592 3 / 3

Nested Loop (cost=1.13..319,852.53 rows=423,979 width=4) (actual time=2.975..2,097.899 rows=161,592 loops=3)

90. 16.386 437.083 ↑ 2.6 161,592 3 / 3

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

91. 0.501 0.501 ↑ 1.0 301 3 / 3

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

92. 420.196 420.196 ↑ 2.7 537 903 / 3

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

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

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

  • Index Cond: (charge_id = (insurance_payment_allocation_1.charge_id)::text)
  • Heap Fetches: 3081
94. 0.093 36.759 ↑ 1.4 222 3 / 3

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

95. 2.271 36.666 ↑ 1.4 222 3 / 3

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

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

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

97. 0.150 0.210 ↑ 1.0 301 3 / 3

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

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

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

99. 0.000 0.000 ↓ 0.0 0 / 3

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

  • Index Cond: ((charge_id)::text = (ipa.charge_id)::text)
100. 176.466 260.297 ↑ 12.0 35,192 1

Gather (cost=1,028.37..168,375.05 rows=423,979 width=42) (actual time=6.501..260.297 rows=35,192 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
101. 4.202 83.831 ↑ 15.1 11,731 3 / 3

Nested Loop (cost=28.37..124,977.15 rows=176,658 width=42) (actual time=0.567..83.831 rows=11,731 loops=3)

102. 1.953 9.245 ↑ 15.1 11,731 3 / 3

Nested Loop (cost=27.80..14,229.75 rows=176,658 width=52) (actual time=0.554..9.245 rows=11,731 loops=3)

  • Join Filter: (ipu_1.remittance_id = ipa_1.remittance_id)
103. 0.457 2.704 ↑ 1.7 74 3 / 3

Merge Join (cost=27.24..761.24 rows=125 width=46) (actual time=0.545..2.704 rows=74 loops=3)

  • Merge Cond: (ir_2.remittance_id = ipu_1.remittance_id)
104. 1.903 1.903 ↑ 37.9 4,738 3 / 3

Parallel Index Scan using insurance_remittance_pkey on insurance_remittance ir_2 (cost=0.42..22,197.36 rows=179,515 width=14) (actual time=0.083..1.903 rows=4,738 loops=3)

105. 0.176 0.344 ↑ 1.0 301 3 / 3

Sort (cost=26.42..27.17 rows=301 width=32) (actual time=0.314..0.344 rows=301 loops=3)

  • Sort Key: ipu_1.remittance_id
  • Sort Method: quicksort Memory: 48kB
106. 0.168 0.168 ↑ 1.0 301 3 / 3

Seq Scan on insurance_payment_unalloc_amount ipu_1 (cost=0.00..14.03 rows=301 width=32) (actual time=0.021..0.168 rows=301 loops=3)

107. 4.588 4.588 ↑ 9.2 159 222 / 3

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

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

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

  • Index Cond: (charge_id = (ipa_1.charge_id)::text)
  • Heap Fetches: 0
109. 157.400 157.400 ↑ 1.0 1 1,574

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

  • Index Cond: ((submission_batch_id)::text = (icr.latest_resubmission_id)::text)
Planning time : 27.646 ms
Execution time : 678,560.200 ms