explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VgN7

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 474,136.592 ↑ 1.0 1 1

Aggregate (cost=22,687,958.32..22,687,958.33 rows=1 width=0) (actual time=474,136.592..474,136.592 rows=1 loops=1)

2. 202.117 474,131.125 ↓ 8.6 59,083 1

Nested Loop Left Join (cost=21,953,438.35..22,687,872.72 rows=6,848 width=468) (actual time=472,919.087..474,131.125 rows=59,083 loops=1)

3. 222.663 473,929.008 ↓ 8.6 59,083 1

Nested Loop Left Join (cost=21,953,437.94..22,684,134.09 rows=6,848 width=492) (actual time=472,919.033..473,929.008 rows=59,083 loops=1)

4. 442,617.748 473,706.345 ↓ 8.6 59,083 1

Merge Left Join (cost=21,953,437.52..22,681,044.34 rows=6,848 width=493) (actual time=472,918.998..473,706.345 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=17887983.87..18176205.80 rows=6848 width=465) (actual time=348952.693..349214.903 rows=59083 loop
  • Merge Cond: ((b.bill_no)::text = (all_insurance_remittance_details_view.bill_no)::text)
5. 3,263.982 31,088.597 ↓ 6.8 46,647 1

Sort (cost=835,520.23..835,537.35 rows=6,848 width=343) (actual time=31,078.329..31,088.597 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=29930.857..30886.589 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=29930.829..30872.878 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=1164.027..1396.771 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=28766.749..28766.749 rows=466
  • Buckets: 1024 Batches: 1 Memory Usage: 12927kB
  • -> Nested Loop Left Join (cost=400408.70..761605.73 rows=6848 width=329) (actual time=3949.2
6. 941.253 27,824.615 ↓ 6.8 46,598 1

Nested Loop Left Join (cost=400,407.84..738,842.53 rows=6,848 width=306) (actual time=3,949.173..27,824.615 rows=46,598 loops=1)

7. 14.172 26,883.362 ↓ 6.8 46,598 1

Hash Left Join (cost=400,407.41..735,325.84 rows=6,848 width=298) (actual time=3,949.156..26,883.362 rows=46,598 loops=1)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stpa.tpa_id)::text)
8. 24.739 26,869.190 ↓ 6.8 46,598 1

Hash Left Join (cost=400,374.17..735,266.29 rows=6,848 width=275) (actual time=3,948.999..26,869.190 rows=46,598 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
9. 25.967 26,844.451 ↓ 6.8 46,598 1

Hash Left Join (cost=400,340.93..735,144.16 rows=6,848 width=262) (actual time=3,947.989..26,844.451 rows=46,598 loops=1)

  • Hash Cond: ((pr.org_id)::text = (od.org_id)::text)
10. 21.995 26,818.484 ↓ 6.8 46,598 1

Hash Left Join (cost=400,281.86..734,990.93 rows=6,848 width=249) (actual time=3,947.509..26,818.484 rows=46,598 loops=1)

  • Hash Cond: (pr.visit_type = vn.visit_type)
11. 1,018.822 26,796.489 ↓ 6.8 46,598 1

Nested Loop Left Join (cost=400,280.77..734,895.68 rows=6,848 width=247) (actual time=3,947.491..26,796.489 rows=46,598 loops=1)

12. 38.027 25,777.667 ↓ 6.8 46,598 1

Hash Join (cost=400,279.63..725,271.86 rows=6,848 width=259) (actual time=3,947.451..25,777.667 rows=46,598 loops=1)

  • Hash Cond: (pr.center_id = hcm.center_id)
13. 8,031.283 25,739.640 ↑ 1.1 149,353 1

Nested Loop Left Join (cost=400,275.33..724,608.40 rows=157,515 width=251) (actual time=3,938.620..25,739.640 rows=149,353 loops=1)

14. 351.502 17,708.357 ↑ 1.1 149,353 1

Hash Left Join (cost=400,274.90..639,667.49 rows=157,515 width=218) (actual time=3,938.581..17,708.357 rows=149,353 loops=1)

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

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

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
16. 858.466 17,310.117 ↑ 1.1 149,353 1

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

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
17. 12,519.760 12,519.760 ↑ 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=5.024..12,519.760 rows=149,353 loops=1)

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

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

  • Buckets: 65536 Batches: 16 Memory Usage: 26587kB
19. 2,456.030 2,456.030 ↓ 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.015..2,456.030 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. 42.917 119.696 ↑ 1.0 225,307 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 9022kB
23. 76.779 76.779 ↑ 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..76.779 rows=225,307 loops=1)

24. 0.000 7,915.709 ↑ 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.053..0.053 rows=1 loops=149,353)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
25. 0.002 0.059 ↑ 1.0 1 1

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

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

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

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 22
27. 46.605 978.558 ↑ 1.0 1 46,598

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

28. 0.003 605.774 ↑ 1.0 1 46,598

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

29. 465.980 465.980 ↑ 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.009..0.010 rows=1 loops=46,598)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
  • Rows Removed by Filter: 0
30. 139.791 139.791 ↑ 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.003 rows=1 loops=46,597)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 0
31. 326.179 326.179 ↓ 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.007..0.007 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.002 0.006 ↑ 1.0 4 1

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

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.003..0.004 rows=4 loops=1)

34. 0.206 0.473 ↑ 1.0 1,381 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 83kB
35. 0.267 0.267 ↑ 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.267 rows=1,381 loops=1)

36. 0.080 1.002 ↑ 1.0 544 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
37. 0.922 0.922 ↑ 1.0 544 1

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

38. 0.085 0.148 ↑ 1.0 544 1

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

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

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

40. 931.960 931.960 ↑ 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.020..0.020 rows=1 loops=46,598)

  • Index Cond: (patient_policy_id = pri_plan.patient_policy_id)
41. 419.382 419.382 ↑ 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.009..0.009 rows=1 loops=46,598)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
42. 419.823 419.823 ↑ 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.009..0.009 rows=1 loops=46,647)

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

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

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

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

45. 643.109 317,403.272 ↓ 2.1 4,014,856 1

Materialize (cost=17,052,463.64..17,335,824.74 rows=1,921,093 width=170) (actual time=313,270.599..317,403.272 rows=4,014,856 loops=1)

46. 1,000.969 316,760.163 ↓ 2.1 4,014,781 1

GroupAggregate (cost=17,052,463.64..17,311,811.08 rows=1,921,093 width=388) (actual time=313,270.593..316,760.163 rows=4,014,781 loops=1)

47. 24,254.928 315,759.194 ↑ 4.8 4,025,988 1

Sort (cost=17,052,463.64..17,100,490.94 rows=19,210,921 width=388) (actual time=313,270.577..315,759.194 rows=4,025,988 loops=1)

  • Sort Method: external merge Disk: 201648kB
48. 522.967 291,504.266 ↑ 4.8 4,026,486 1

Subquery Scan on all_insurance_remittance_details_view (cost=11,073,802.79..11,313,939.30 rows=19,210,921 width=388) (actual time=287,858.158..291,504.266 rows=4,026,486 loops=1)

49. 23,793.521 290,981.299 ↑ 4.8 4,026,486 1

Sort (cost=11,073,802.79..11,121,830.09 rows=19,210,921 width=44) (actual time=287,858.155..290,981.299 rows=4,026,486 loops=1)

  • Sort Key: bill_charge.bill_no
  • Sort Method: external merge Disk: 217840kB
50. 3,948.056 267,187.778 ↑ 4.8 4,026,486 1

Unique (cost=7,822,568.84..8,158,759.96 rows=19,210,921 width=44) (actual time=200,939.438..267,187.778 rows=4,026,486 loops=1)

51. 181,987.759 263,239.722 ↑ 1.0 18,897,946 1

Sort (cost=7,822,568.84..7,870,596.14 rows=19,210,921 width=44) (actual time=200,939.437..263,239.722 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
52. 1,307.071 81,251.963 ↑ 1.0 18,897,946 1

Append (cost=2,109,656.55..4,907,526.01 rows=19,210,921 width=44) (actual time=27,018.218..81,251.963 rows=18,897,946 loops=1)

53. 7,953.497 79,731.876 ↑ 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=27,018.217..79,731.876 rows=18,862,754 loops=1)

  • Hash Cond: (insurance_payment_allocation.remittance_id = ir.remittance_id)
54. 33,115.988 71,728.692 ↓ 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=26,968.503..71,728.692 rows=18,873,225 loops=1)

  • Hash Cond: ((insurance_payment_allocation.charge_id)::text = (bill_charge.charge_id)::text)
55. 11,644.648 11,644.648 ↓ 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.028..11,644.648 rows=18,873,225 loops=1)

56. 5,981.717 26,968.056 ↑ 1.0 26,507,931 1

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

  • Buckets: 131072 Batches: 32 Memory Usage: 46663kB
57. 20,986.339 20,986.339 ↑ 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.010..20,986.339 rows=26,507,931 loops=1)

58. 22.054 49.687 ↑ 1.0 155,793 1

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

59. 27.633 27.633 ↑ 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.008..27.633 rows=155,793 loops=1)

60. 0.001 4.350 ↓ 0.0 0 1

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

61. 0.001 4.349 ↓ 0.0 0 1

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

  • Join Filter: (ipu.remittance_id = ipa.remittance_id)
62. 0.001 4.348 ↓ 0.0 0 1

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

63. 0.098 4.347 ↓ 0.0 0 1

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

64. 0.035 0.035 ↑ 1.0 301 1

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

65. 0.000 4.214 ↑ 48,083.0 1 301

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

66. 0.301 2.709 ↑ 48,083.0 1 301

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

67. 1.204 1.204 ↑ 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.004..0.004 rows=1 loops=301)

  • Index Cond: (remittance_id = ipu.remittance_id)
  • Heap Fetches: 0
68. 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)
69. 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)
70. 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)
71. 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)
72. 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)

  • Index Cond: ((charge_id)::text = (ipa.charge_id)::text)
73. 11.296 208.666 ↑ 9.6 35,192 1

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

74. 5.622 21.410 ↑ 9.6 35,192 1

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

  • Join Filter: (ipu_1.remittance_id = ipa_1.remittance_id)
75. 1.135 4.688 ↑ 1.4 222 1

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

  • Merge Cond: (ipu_1.remittance_id = ir_2.remittance_id)
76. 0.097 0.097 ↑ 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.097 rows=301 loops=1)

77. 3.456 3.456 ↑ 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..3.456 rows=14,429 loops=1)

78. 11.100 11.100 ↑ 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.050 rows=159 loops=222)

  • Index Cond: (remittance_id = ir_2.remittance_id)
79. 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
80. 1,338.346 123,708.637 ↑ 1.0 4,252,470 1

Materialize (cost=4,065,453.65..4,494,040.25 rows=4,285,866 width=58) (actual time=119,630.728..123,708.637 rows=4,252,470 loops=1)

81. 2,120.887 122,370.291 ↑ 1.0 4,239,985 1

Group (cost=4,065,453.65..4,440,466.93 rows=4,285,866 width=968) (actual time=119,630.723..122,370.291 rows=4,239,985 loops=1)

82. 30,695.759 120,249.404 ↑ 1.0 4,285,110 1

Sort (cost=4,065,453.65..4,076,168.32 rows=4,285,866 width=968) (actual time=119,630.719..120,249.404 rows=4,285,110 loops=1)

  • Sort Method: external sort Disk: 746280kB
83. 5,055.242 89,553.645 ↓ 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=81,431.405..89,553.645 rows=4,285,912 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_1.bill_no)::text)
84. 1,486.899 3,198.116 ↓ 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,173.005..3,198.116 rows=4,285,867 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_2.bill_no)::text)
85. 527.408 527.408 ↓ 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.025..527.408 rows=4,285,867 loops=1)

  • Heap Fetches: 1259
86. 157.405 1,183.809 ↓ 130.5 90,076 1

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

  • Sort Key: bcl_2.bill_no
  • Sort Method: quicksort Memory: 10239kB
87. 29.800 1,026.404 ↓ 65.3 45,039 1

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

88. 0.000 674.072 ↓ 59.8 46,076 1

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

89. 224.678 224.678 ↓ 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=14.524..224.678 rows=44,943 loops=1)

  • Index Cond: (priority = 2)
90. 449.430 449.430 ↑ 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.009..0.010 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
91. 322.532 322.532 ↑ 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.007..0.007 rows=1 loops=46,076)

  • Index Cond: ((claim_id)::text = (sic_1.claim_id)::text)
92. 81,300.287 81,300.287 ↓ 61.1 4,285,238 1

Sort (cost=1,263,651.77..1,263,827.19 rows=70,169 width=499) (actual time=80,258.390..81,300.287 rows=4,285,238 loops=1)

  • Sort Key: bcl_1.bill_no
  • Sort Method: external sort Disk: 741088kB
93. 994.490 46,328.924 ↓ 60.4 4,240,131 1

Nested Loop (cost=1.30..1,258,003.67 rows=70,169 width=499) (actual time=0.086..46,328.924 rows=4,240,131 loops=1)

94. 6,171.315 17,344.328 ↓ 59.0 4,625,629 1

Merge Join (cost=0.86..1,215,825.70 rows=78,358 width=485) (actual time=0.058..17,344.328 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
95. 3,931.934 3,931.934 ↑ 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.023..3,931.934 rows=4,552,596 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 44943
96. 1,402.092 7,241.079 ↓ 1.0 4,786,803 1

Materialize (cost=0.43..639,958.59 rows=4,786,054 width=489) (actual time=0.027..7,241.079 rows=4,786,803 loops=1)

97. 5,838.987 5,838.987 ↓ 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.023..5,838.987 rows=4,786,803 loops=1)

98. 27,753.774 27,753.774 ↑ 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.006..0.006 rows=1 loops=4,625,629)

  • Index Cond: ((claim_id)::text = (sic.claim_id)::text)
99. 177.249 177.249 ↑ 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.003..0.003 rows=1 loops=59,083)

  • Index Cond: ((submission_batch_id)::text = (icl.submission_batch_id)::text)
  • Filter: (is_resubmission = 'N'::bpchar)
100. 59.083 59.083 ↓ 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.001..0.001 rows=0 loops=59,083)

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