explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gDKE

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 491,221.622 ↑ 1.0 1 1

Aggregate (cost=19,724,888.19..19,724,888.20 rows=1 width=0) (actual time=491,221.622..491,221.622 rows=1 loops=1)

  • Index Cond: ((charge_id)::text = (ipa.charge_id)::text)
2. 344.101 491,215.952 ↓ 8.6 59,083 1

Nested Loop Left Join (cost=18,990,368.22..19,724,802.59 rows=6,848 width=468) (actual time=489,689.227..491,215.952 rows=59,083 loops=1)

3. 331.281 490,871.851 ↓ 8.6 59,083 1

Nested Loop Left Join (cost=18,990,367.80..19,721,063.96 rows=6,848 width=492) (actual time=489,688.629..490,871.851 rows=59,083 loops=1)

4. 177,293.402 490,540.570 ↓ 8.6 59,083 1

Merge Left Join (cost=18,990,367.38..19,717,974.21 rows=6,848 width=493) (actual time=489,687.481..490,540.570 rows=59,083 loops=1)

  • Merge Cond: ((b.bill_no)::text = (bcl.bill_no)::text)
  • Join Filter: ((sip.patient_id)::text = (b.visit_id)::text)
  • Rows Removed by Join Filter: 1
  • -> Merge Left Join (cost=14924913.73..15213135.66 rows=6848 width=465) (actual time=313742.182..313993.778 rows=59083 loop
  • Merge Cond: ((b.bill_no)::text = (all_insurance_remittance_details_view.bill_no)::text)
5. 2,421.187 28,006.160 ↓ 6.8 46,647 1

Sort (cost=835,520.23..835,537.35 rows=6,848 width=343) (actual time=27,992.708..28,006.160 rows=46,647 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 18263kB
  • -> Hash Left Join (cost=825855.37..835083.96 rows=6848 width=343) (actual time=26858.789..27805.405 rows=46647
  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
  • -> Hash Right Join (cost=825853.90..834988.33 rows=6848 width=347) (actual time=26858.762..27792.210 row
  • Hash Cond: ((insurance_claim_resubmission.claim_id)::text = (icl.claim_id)::text)
  • -> HashAggregate (cost=64162.57..68006.33 rows=384376 width=23) (actual time=1150.411..1383.494 ro
  • -> Seq Scan on insurance_claim_resubmission (cost=0.00..58724.38 rows=1087638 width=23) (act
  • -> Hash (cost=761605.73..761605.73 rows=6848 width=329) (actual time=25708.301..25708.301 rows=466
  • Buckets: 1024 Batches: 1 Memory Usage: 12927kB
  • -> Nested Loop Left Join (cost=400408.70..761605.73 rows=6848 width=329) (actual time=4107.0
6. 15.354 19,290.557 ↓ 6.8 46,598 1

Nested Loop Left Join (cost=400,407.84..738,842.53 rows=6,848 width=306) (actual time=4,106.997..19,290.557 rows=46,598 loops=1)

7. 16.863 18,063.655 ↓ 6.8 46,598 1

Hash Left Join (cost=400,407.41..735,325.84 rows=6,848 width=298) (actual time=4,106.980..18,063.655 rows=46,598 loops=1)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stpa.tpa_id)::text)
8. 27.027 18,046.623 ↓ 6.8 46,598 1

Hash Left Join (cost=400,374.17..735,266.29 rows=6,848 width=275) (actual time=4,106.807..18,046.623 rows=46,598 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
9. 28.132 18,019.393 ↓ 6.8 46,598 1

Hash Left Join (cost=400,340.93..735,144.16 rows=6,848 width=262) (actual time=4,106.597..18,019.393 rows=46,598 loops=1)

  • Hash Cond: ((pr.org_id)::text = (od.org_id)::text)
10. 24.613 17,990.737 ↓ 6.8 46,598 1

Hash Left Join (cost=400,281.86..734,990.93 rows=6,848 width=249) (actual time=4,106.064..17,990.737 rows=46,598 loops=1)

  • Hash Cond: (pr.visit_type = vn.visit_type)
11. 11.482 17,966.119 ↓ 6.8 46,598 1

Nested Loop Left Join (cost=400,280.77..734,895.68 rows=6,848 width=247) (actual time=4,106.044..17,966.119 rows=46,598 loops=1)

12. 38.914 16,463.501 ↓ 6.8 46,598 1

Hash Join (cost=400,279.63..725,271.86 rows=6,848 width=259) (actual time=4,106.001..16,463.501 rows=46,598 loops=1)

  • Hash Cond: (pr.center_id = hcm.center_id)
13. 120.581 16,424.574 ↑ 1.1 149,353 1

Nested Loop Left Join (cost=400,275.33..724,608.40 rows=157,515 width=251) (actual time=4,099.611..16,424.574 rows=149,353 loops=1)

14. 247.035 10,628.579 ↑ 1.1 149,353 1

Hash Left Join (cost=400,274.90..639,667.49 rows=157,515 width=218) (actual time=4,099.579..10,628.579 rows=149,353 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
15. 51.225 10,249.634 ↑ 1.1 149,353 1

Hash Left Join (cost=390,888.49..628,431.32 rows=157,515 width=209) (actual time=3,967.637..10,249.634 rows=149,353 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
16. 1,511.922 10,198.408 ↑ 1.1 149,353 1

Hash Join (cost=390,872.19..627,824.24 rows=157,515 width=161) (actual time=3,967.625..10,198.408 rows=149,353 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
17. 4,720.768 4,720.768 ↑ 1.1 149,353 1

Index Scan using idx_bill_open_date on bill b (cost=0.44..171,530.08 rows=157,515 width=92) (actual time=0.019..4,720.768 rows=149,353 loops=1)

  • Index Cond: ((date(open_date) >= '2018-05-01'::date) AND (date(open_date) <= '2018-05-31'::date))
  • Rows Removed by Filter: 61662
18. 1,337.022 3,965.718 ↓ 1.0 4,937,321 1

Hash (cost=271,308.89..271,308.89 rows=4,936,789 width=69) (actual time=3,965.718..3,965.718 rows=4,937,321 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 26587kB
19. 2,628.696 2,628.696 ↓ 1.0 4,937,321 1

Seq Scan on patient_registration pr (cost=0.00..271,308.89 rows=4,936,789 width=69) (actual time=0.003..2,628.696 rows=4,937,321 loops=1)

20. 0.000 0.001 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
21. 0.001 0.001 ↓ 0.0 0 1

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

22. 50.938 131.910 ↑ 1.0 225,307 1

Hash (cost=6,570.07..6,570.07 rows=225,307 width=9) (actual time=131.910..131.910 rows=225,307 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 9022kB
23. 80.972 80.972 ↑ 1.0 225,307 1

Seq Scan on incoming_sample_registration isr (cost=0.00..6,570.07 rows=225,307 width=9) (actual time=0.002..80.972 rows=225,307 loops=1)

24. 5,675.414 5,675.414 ↑ 1.0 1 149,353

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.53 rows=1 width=48) (actual time=0.038..0.038 rows=1 loops=149,353)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
25. 0.001 0.013 ↑ 1.0 1 1

Hash (cost=4.29..4.29 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
26. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on hospital_center_master hcm (cost=0.00..4.29 rows=1 width=16) (actual time=0.006..0.012 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMC Royal'::text)
27. 46.607 1,491.136 ↑ 1.0 1 46,598

Nested Loop Left Join (cost=1.14..1.40 rows=1 width=20) (actual time=0.031..0.032 rows=1 loops=46,598)

28. 0.004 1,025.156 ↑ 1.0 1 46,598

Nested Loop (cost=0.71..0.84 rows=1 width=20) (actual time=0.022..0.022 rows=1 loops=46,598)

29. 838.764 838.764 ↑ 1.0 1 46,598

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pri_plan (cost=0.43..0.54 rows=1 width=42) (actual time=0.018..0.018 rows=1 loops=46,598)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
  • Rows Removed by Filter: 0
30. 186.388 186.388 ↑ 1.0 1 46,597

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.28..0.30 rows=1 width=10) (actual time=0.003..0.004 rows=1 loops=46,597)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 0
31. 419.373 419.373 ↓ 0.0 0 46,597

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sec_plan (cost=0.43..0.54 rows=1 width=38) (actual time=0.009..0.009 rows=0 loops=46,597)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
32. 0.001 0.005 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=6) (actual time=0.005..0.005 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
33. 0.004 0.004 ↑ 1.0 4 1

Seq Scan on visit_type_names vn (cost=0.00..1.04 rows=4 width=6) (actual time=0.002..0.004 rows=4 loops=1)

34. 0.254 0.524 ↑ 1.0 1,381 1

Hash (cost=41.81..41.81 rows=1,381 width=29) (actual time=0.524..0.524 rows=1,381 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 83kB
35. 0.270 0.270 ↑ 1.0 1,381 1

Seq Scan on organization_details od (cost=0.00..41.81 rows=1,381 width=29) (actual time=0.003..0.270 rows=1,381 loops=1)

36. 0.104 0.203 ↑ 1.0 544 1

Hash (cost=26.44..26.44 rows=544 width=33) (actual time=0.203..0.203 rows=544 loops=1)

37. 0.099 0.099 ↑ 1.0 544 1

Seq Scan on tpa_master ptpa (cost=0.00..26.44 rows=544 width=33) (actual time=0.002..0.099 rows=544 loops=1)

38. 0.090 0.169 ↑ 1.0 544 1

Hash (cost=26.44..26.44 rows=544 width=33) (actual time=0.169..0.169 rows=544 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
39. 0.079 0.079 ↑ 1.0 544 1

Seq Scan on tpa_master stpa (cost=0.00..26.44 rows=544 width=33) (actual time=0.001..0.079 rows=544 loops=1)

40. 1,211.548 1,211.548 ↑ 1.0 1 46,598

Index Scan using patient_policy_details_patient_policy_id_key on patient_policy_details pppd (cost=0.43..0.50 rows=1 width=16) (actual time=0.026..0.026 rows=1 loops=46,598)

  • Index Cond: (patient_policy_id = pri_plan.patient_policy_id)
41. 2,795.880 2,795.880 ↑ 1.0 1 46,598

Index Scan using bill_claim_bill_no_idx on bill_claim bclm (cost=0.43..2.73 rows=1 width=28) (actual time=0.059..0.060 rows=1 loops=46,598)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
42. 3,498.525 3,498.525 ↑ 1.0 1 46,647

Index Scan using insurance_claim_id_index on insurance_claim icl (cost=0.43..0.58 rows=1 width=23) (actual time=0.075..0.075 rows=1 loops=46,647)

  • Index Cond: ((claim_id)::text = (bclm.claim_id)::text)
43. 0.005 0.011 ↑ 1.0 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
44. 0.006 0.006 ↑ 1.0 21 1

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

45. 639.524 285,241.008 ↓ 2.1 4,014,856 1

Materialize (cost=14,089,393.50..14,372,754.61 rows=1,921,093 width=170) (actual time=283,278.097..285,241.008 rows=4,014,856 loops=1)

46. 951.793 284,601.484 ↓ 2.1 4,014,781 1

GroupAggregate (cost=14,089,393.50..14,348,740.94 rows=1,921,093 width=388) (actual time=283,278.091..284,601.484 rows=4,014,781 loops=1)

47. 283,649.691 283,649.691 ↑ 4.8 4,025,988 1

Sort (cost=14,089,393.50..14,137,420.80 rows=19,210,921 width=388) (actual time=283,278.082..283,649.691 rows=4,025,988 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 sort Disk: 201728kB
48. 0.000 258,935.597 ↑ 4.8 4,026,486 1

Subquery Scan on all_insurance_remittance_details_view (cost=7,822,568.84..8,350,869.17 rows=19,210,921 width=388) (actual time=192,688.787..258,935.597 rows=4,026,486 loops=1)

49. 3,676.043 258,457.501 ↑ 4.8 4,026,486 1

Unique (cost=7,822,568.84..8,158,759.96 rows=19,210,921 width=44) (actual time=192,688.785..258,457.501 rows=4,026,486 loops=1)

50. 180,552.245 254,781.458 ↑ 1.0 18,897,946 1

Sort (cost=7,822,568.84..7,870,596.14 rows=19,210,921 width=44) (actual time=192,688.783..254,781.458 rows=18,897,946 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: 1019728kB
51. 1,252.183 74,229.213 ↑ 1.0 18,897,946 1

Append (cost=2,109,656.55..4,907,526.01 rows=19,210,921 width=44) (actual time=21,446.675..74,229.213 rows=18,897,946 loops=1)

52. 7,938.520 72,752.542 ↑ 1.0 18,862,754 1

Hash Join (cost=2,109,656.55..4,401,089.00 rows=18,873,224 width=44) (actual time=21,446.673..72,752.542 rows=18,862,754 loops=1)

  • Hash Cond: (insurance_payment_allocation.remittance_id = ir.remittance_id)
53. 32,219.593 64,709.832 ↓ 1.0 18,873,225 1

Hash Join (cost=2,102,999.21..3,922,601.06 rows=18,873,224 width=28) (actual time=21,342.457..64,709.832 rows=18,873,225 loops=1)

  • Hash Cond: ((insurance_payment_allocation.charge_id)::text = (bill_charge.charge_id)::text)
54. 11,148.177 11,148.177 ↓ 1.0 18,873,225 1

Seq Scan on insurance_payment_allocation (cost=0.00..570,209.24 rows=18,873,224 width=24) (actual time=0.008..11,148.177 rows=18,873,225 loops=1)

55. 5,731.668 21,342.062 ↑ 1.0 26,507,931 1

Hash (cost=1,616,286.76..1,616,286.76 rows=26,510,276 width=24) (actual time=21,342.062..21,342.062 rows=26,507,931 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 46663kB
56. 15,610.394 15,610.394 ↑ 1.0 26,507,931 1

Seq Scan on bill_charge (cost=0.00..1,616,286.76 rows=26,510,276 width=24) (actual time=0.008..15,610.394 rows=26,507,931 loops=1)

57. 25.816 104.190 ↑ 1.0 155,793 1

Hash (cost=4,709.93..4,709.93 rows=155,793 width=20) (actual time=104.190..104.190 rows=155,793 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 8010kB
58. 78.374 78.374 ↑ 1.0 155,793 1

Seq Scan on insurance_remittance ir (cost=0.00..4,709.93 rows=155,793 width=20) (actual time=0.007..78.374 rows=155,793 loops=1)

59. 0.001 4.744 ↓ 0.0 0 1

Nested Loop (cost=2.57..91,216.34 rows=1,118 width=44) (actual time=4.744..4.744 rows=0 loops=1)

60. 0.001 4.743 ↓ 0.0 0 1

Nested Loop (cost=2.00..88,021.31 rows=1,118 width=40) (actual time=4.743..4.743 rows=0 loops=1)

61. 0.000 4.742 ↓ 0.0 0 1

Nested Loop (cost=1.57..87,942.52 rows=1 width=24) (actual time=4.742..4.742 rows=0 loops=1)

62. 0.199 4.742 ↓ 0.0 0 1

Nested Loop Anti Join (cost=1.15..87,935.28 rows=1 width=10) (actual time=4.742..4.742 rows=0 loops=1)

63. 0.028 0.028 ↑ 1.0 301 1

Seq Scan on insurance_payment_unalloc_amount ipu (cost=0.00..8.01 rows=301 width=10) (actual time=0.007..0.028 rows=301 loops=1)

64. 0.000 4.515 ↑ 48,083.0 1 301

Nested Loop (cost=1.15..87,251.31 rows=48,083 width=4) (actual time=0.015..0.015 rows=1 loops=301)

65. 0.000 3.010 ↑ 48,083.0 1 301

Nested Loop (cost=0.58..6,992.50 rows=48,083 width=14) (actual time=0.010..0.010 rows=1 loops=301)

66. 1.806 1.806 ↑ 43.0 1 301

Index Only Scan using idx_insurance_payment_unalloc_amount_remittance_id on insurance_payment_unalloc_amount ipu1 (cost=0.15..0.93 rows=43 width=4) (actual time=0.006..0.006 rows=1 loops=301)

  • Index Cond: (remittance_id = ipu.remittance_id)
  • Heap Fetches: 0
67. 1.204 1.204 ↑ 1,177.0 1 301

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation insurance_payment_allocation_1 (cost=0.44..150.82 rows=1,177 width=14) (actual time=0.004..0.004 rows=1 loops=301)

  • Index Cond: (remittance_id = ipu1.remittance_id)
68. 1.505 1.505 ↑ 1.0 1 301

Index Only Scan using bill_charge_pkey on bill_charge bc_1 (cost=0.56..1.66 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=301)

  • Index Cond: (charge_id = (insurance_payment_allocation_1.charge_id)::text)
  • Heap Fetches: 0
69. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_remittance_pkey on insurance_remittance ir_1 (cost=0.42..7.23 rows=1 width=14) (never executed)

  • Index Cond: (remittance_id = ipu.remittance_id)
70. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa (cost=0.44..64.08 rows=1,177 width=24) (never executed)

  • Index Cond: (remittance_id = ir_1.remittance_id)
71. 0.000 0.000 ↓ 0.0 0

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

72. 12.568 219.744 ↑ 9.6 35,192 1

Nested Loop (cost=8.13..223,111.45 rows=336,579 width=42) (actual time=0.639..219.744 rows=35,192 loops=1)

73. 5.545 31.216 ↑ 9.6 35,192 1

Nested Loop (cost=7.56..16,074.34 rows=336,579 width=52) (actual time=0.629..31.216 rows=35,192 loops=1)

  • Join Filter: (ipu_1.remittance_id = ipa_1.remittance_id)
74. 1.056 15.459 ↑ 1.4 222 1

Merge Join (cost=7.13..738.35 rows=301 width=46) (actual time=0.619..15.459 rows=222 loops=1)

  • Merge Cond: (ipu_1.remittance_id = ir_2.remittance_id)
75. 0.101 0.101 ↑ 1.0 301 1

Index Scan using idx_insurance_payment_unalloc_amount_remittance_id on insurance_payment_unalloc_amount ipu_1 (cost=0.15..24.38 rows=301 width=32) (actual time=0.003..0.101 rows=301 loops=1)

76. 14.302 14.302 ↑ 10.8 14,429 1

Index Scan using insurance_remittance_pkey on insurance_remittance ir_2 (cost=0.42..7,282.06 rows=155,793 width=14) (actual time=0.009..14.302 rows=14,429 loops=1)

77. 10.212 10.212 ↑ 7.4 159 222

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa_1 (cost=0.44..36.24 rows=1,177 width=14) (actual time=0.004..0.046 rows=159 loops=222)

  • Index Cond: (remittance_id = ir_2.remittance_id)
78. 175.960 175.960 ↑ 1.0 1 35,192

Index Only Scan using bill_charge_pkey on bill_charge bc_2 (cost=0.56..0.61 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=35,192)

  • Index Cond: (charge_id = (ipa_1.charge_id)::text)
  • Heap Fetches: 0
79. 1,343.249 175,724.991 ↑ 1.0 4,252,470 1

Materialize (cost=4,065,453.65..4,494,040.25 rows=4,285,866 width=58) (actual time=170,550.645..175,724.991 rows=4,252,470 loops=1)

80. 3,198.264 174,381.742 ↑ 1.0 4,239,985 1

Group (cost=4,065,453.65..4,440,466.93 rows=4,285,866 width=968) (actual time=170,550.639..174,381.742 rows=4,239,985 loops=1)

81. 39,099.040 171,183.478 ↑ 1.0 4,285,110 1

Sort (cost=4,065,453.65..4,076,168.32 rows=4,285,866 width=968) (actual time=170,550.634..171,183.478 rows=4,285,110 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
82. 5,140.255 132,084.438 ↓ 1.0 4,285,912 1

Merge Left Join (cost=1,550,145.62..1,776,868.75 rows=4,285,866 width=968) (actual time=117,846.063..132,084.438 rows=4,285,912 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_1.bill_no)::text)
83. 1,526.313 9,907.431 ↓ 1.0 4,285,867 1

Merge Left Join (cost=286,493.85..501,300.33 rows=4,285,866 width=483) (actual time=1,880.237..9,907.431 rows=4,285,867 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_2.bill_no)::text)
84. 6,490.199 6,490.199 ↓ 1.0 4,285,867 1

Index Only Scan using bill_claim_bill_no_idx on bill_claim bcl (cost=0.43..204,080.42 rows=4,285,866 width=14) (actual time=0.544..6,490.199 rows=4,285,867 loops=1)

  • Heap Fetches: 1259
85. 160.648 1,890.919 ↓ 130.5 90,076 1

Sort (cost=286,493.42..286,495.15 rows=690 width=483) (actual time=1,879.687..1,890.919 rows=90,076 loops=1)

  • Sort Key: bcl_2.bill_no
  • Sort Method: quicksort Memory: 10239kB
86. 5.307 1,730.271 ↓ 65.3 45,039 1

Nested Loop (cost=1.30..286,460.89 rows=690 width=483) (actual time=13.604..1,730.271 rows=45,039 loops=1)

87. 21.443 757.368 ↓ 59.8 46,076 1

Nested Loop (cost=0.86..286,046.42 rows=770 width=469) (actual time=13.568..757.368 rows=46,076 loops=1)

88. 241.552 241.552 ↓ 1.0 44,943 1

Index Scan using patient_insurance_plans_priority on patient_insurance_plans sip_1 (cost=0.43..12,489.67 rows=44,750 width=20) (actual time=13.531..241.552 rows=44,943 loops=1)

  • Index Cond: (priority = 2)
89. 494.373 494.373 ↑ 1.0 1 44,943

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic_1 (cost=0.43..6.10 rows=1 width=489) (actual time=0.010..0.011 rows=1 loops=44,943)

  • Index Cond: ((patient_id)::text = (sip_1.patient_id)::text)
  • Filter: (sip_1.plan_id = plan_id)
  • Rows Removed by Filter: 1
90. 967.596 967.596 ↑ 1.0 1 46,076

Index Scan using bill_claim_claim_idx on bill_claim bcl_2 (cost=0.43..0.53 rows=1 width=28) (actual time=0.020..0.021 rows=1 loops=46,076)

  • Index Cond: ((claim_id)::text = (sic_1.claim_id)::text)
91. 35,986.649 117,036.752 ↓ 61.1 4,285,238 1

Sort (cost=1,263,651.77..1,263,827.19 rows=70,169 width=499) (actual time=115,965.818..117,036.752 rows=4,285,238 loops=1)

  • Sort Key: bcl_1.bill_no
  • Sort Method: external sort Disk: 741088kB
92. 81,050.103 81,050.103 ↓ 60.4 4,240,131 1

Nested Loop (cost=1.30..1,258,003.67 rows=70,169 width=499) (actual time=0.069..81,050.103 rows=4,240,131 loops=1)

93. 0.000 40,526.665 ↓ 59.0 4,625,629 1

Merge Join (cost=0.86..1,215,825.70 rows=78,358 width=485) (actual time=0.040..40,526.665 rows=4,625,629 loops=1)

  • Merge Cond: ((sip.patient_id)::text = (sic.patient_id)::text)
  • Join Filter: (sic.plan_id = sip.plan_id)
  • Rows Removed by Join Filter: 157712
94. 13,340.031 13,340.031 ↑ 1.0 4,552,596 1

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sip (cost=0.43..494,683.60 rows=4,552,816 width=20) (actual time=0.022..13,340.031 rows=4,552,596 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 44943
95. 1,506.140 20,512.249 ↓ 1.0 4,786,803 1

Materialize (cost=0.43..639,958.59 rows=4,786,054 width=489) (actual time=0.013..20,512.249 rows=4,786,803 loops=1)

96. 19,006.109 19,006.109 ↓ 1.0 4,786,803 1

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic (cost=0.43..627,993.45 rows=4,786,054 width=489) (actual time=0.010..19,006.109 rows=4,786,803 loops=1)

97. 37,005.032 37,005.032 ↑ 1.0 1 4,625,629

Index Scan using bill_claim_claim_idx on bill_claim bcl_1 (cost=0.43..0.53 rows=1 width=28) (actual time=0.008..0.008 rows=1 loops=4,625,629)

  • Index Cond: ((claim_id)::text = (sic.claim_id)::text)
98. 295.415 295.415 ↑ 1.0 1 59,083

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch firstsub (cost=0.42..0.44 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=59,083)

  • Index Cond: ((submission_batch_id)::text = (icl.submission_batch_id)::text)
  • Filter: (is_resubmission = 'N'::bpchar)
99. 236.332 236.332 ↓ 0.0 0 59,083

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch resub (cost=0.42..0.48 rows=1 width=17) (actual time=0.004..0.004 rows=0 loops=59,083)

  • Index Cond: ((submission_batch_id)::text = (max((insurance_claim_resubmission.resubmission_batch_id)::text)))