explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l6IY

Settings
# exclusive inclusive rows x rows loops node
1. 3.146 827,445.799 ↑ 4,911.5 4,999 1

Subquery Scan on final (cost=17,412,078.65..34,901,036.14 rows=24,552,555 width=268) (actual time=414,769.774..827,445.799 rows=4,999 loops=1)

2. 411,351.142 827,442.653 ↑ 4,911.5 4,999 1

Append (cost=17,412,078.65..34,164,459.49 rows=24,552,555 width=260) (actual time=414,769.767..827,442.653 rows=4,999 loops=1)

3. 2.613 414,773.228 ↑ 4,184.8 4,171 1

Unique (cost=17,412,078.65..17,848,450.50 rows=17,454,874 width=260) (actual time=414,769.766..414,773.228 rows=4,171 loops=1)

4. 18.677 414,770.615 ↑ 4,158.9 4,197 1

Sort (cost=17,412,078.65..17,455,715.84 rows=17,454,874 width=260) (actual time=414,769.763..414,770.615 rows=4,197 loops=1)

  • Sort Key: (1), ('Autopay failed'::text), ((loan_autopay_entity.loan_id)::text), ((loan_autopay_entity.id)::text), ((loan_autopay_entity.apply_date)::text), loan_autopay_entity.name, (''::text), (''::text), ('Site 1'::text)
  • Sort Method: quicksort Memory: 1299kB
5. 1.866 414,751.938 ↑ 4,158.9 4,197 1

Append (cost=0.43..8,869,204.08 rows=17,454,874 width=260) (actual time=8,163.157..414,751.938 rows=4,197 loops=1)

6. 7,730.291 7,730.291 ↓ 0.0 0 1

Index Scan using loan_autopay_entity_apply_date on loan_autopay_entity (cost=0.43..20,825.37 rows=1 width=237) (actual time=7,730.291..7,730.291 rows=0 loops=1)

  • Index Cond: (apply_date >= (CURRENT_DATE - 5))
  • Filter: ((status = 'autopay.status.failed'::bpchar) AND (deleted = 0))
  • Rows Removed by Filter: 72294
7. 206.802 206.802 ↓ 0.0 0 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity loan_autopay_entity_1 (cost=0.41..23,215.41 rows=1 width=237) (actual time=206.802..206.802 rows=0 loops=1)

  • Filter: ((charge_off_recovery = 1) AND (deleted = 0))
  • Rows Removed by Filter: 47727
8. 59.981 59.981 ↓ 0.0 0 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity loan_autopay_entity_2 (cost=0.41..23,432.54 rows=155 width=237) (actual time=59.981..59.981 rows=0 loops=1)

  • Filter: ((name <> 'Autopay - Final Payment'::text) AND (recurring_frequency = 'autopay.recurringFrequency.semiMonthly'::bpchar) AND (type = 'autopay.type.recurring'::bpchar) AND (deleted = 0))
  • Rows Removed by Filter: 47727
9. 0.002 96.876 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=23,522.66..24,625.40 rows=36,758 width=260) (actual time=96.876..96.876 rows=0 loops=1)

10. 27.966 96.874 ↓ 0.0 0 1

HashAggregate (cost=23,522.66..24,257.82 rows=36,758 width=268) (actual time=96.874..96.874 rows=0 loops=1)

  • Group Key: loan_autopay_entity_3.loan_id, loan_autopay_entity_3.apply_date
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 45347
11. 68.908 68.908 ↓ 1.1 45,347 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity loan_autopay_entity_3 (cost=0.41..23,215.39 rows=40,969 width=8) (actual time=0.500..68.908 rows=45,347 loops=1)

  • Filter: ((name = ANY ('{Autopay,""Autopay - Final Payment""}'::text[])) AND (deleted = 0))
  • Rows Removed by Filter: 2380
12. 62.783 62.783 ↓ 0.0 0 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity loan_autopay_entity_4 (cost=0.41..24,328.42 rows=42,307 width=237) (actual time=62.783..62.783 rows=0 loops=1)

  • Filter: ((deleted = 0) AND (date_part('hour'::text, process_datetime) <> ALL ('{21,22,23}'::double precision[])))
  • Rows Removed by Filter: 47727
13. 57.998 57.998 ↑ 945.5 2 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity loan_autopay_entity_5 (cost=0.41..23,351.17 rows=1,891 width=237) (actual time=6.414..57.998 rows=2 loops=1)

  • Filter: ((name <> ALL ('{Autopay,""Autopay - Final Payment""}'::text[])) AND (deleted = 0) AND (type = 'autopay.type.recurring'::bpchar))
  • Rows Removed by Filter: 47725
14. 27.324 364.848 ↓ 0.0 0 1

Hash Join (cost=14,898.85..39,757.54 rows=15,535 width=237) (actual time=364.848..364.848 rows=0 loops=1)

  • Hash Cond: (lae.primary_payment_method_id = pae.id)
15. 69.922 69.922 ↓ 1.1 46,976 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity lae (cost=0.41..23,107.98 rows=42,951 width=25) (actual time=0.018..69.922 rows=46,976 loops=1)

  • Filter: (deleted = 0)
  • Rows Removed by Filter: 751
16. 49.372 267.602 ↓ 1.0 137,244 1

Hash (cost=12,649.05..12,649.05 rows=137,071 width=4) (actual time=267.602..267.602 rows=137,244 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3452kB
17. 218.230 218.230 ↓ 1.0 137,244 1

Seq Scan on payment_account_entity pae (cost=0.00..12,649.05 rows=137,071 width=4) (actual time=0.011..218.230 rows=137,244 loops=1)

  • Filter: (active = 0)
  • Rows Removed by Filter: 241720
18. 52.794 3,578.546 ↓ 0.0 0 1

Hash Join (cost=65,663.57..91,001.72 rows=11,130 width=237) (actual time=3,578.546..3,578.546 rows=0 loops=1)

  • Hash Cond: (lae_1.loan_id = lse.loan_id)
  • Join Filter: (lae_1.process_datetime > lse.orig_final_payment_date)
  • Rows Removed by Join Filter: 339
19. 72.491 72.491 ↓ 1.1 45,346 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity lae_1 (cost=0.41..23,215.39 rows=40,981 width=29) (actual time=0.505..72.491 rows=45,346 loops=1)

  • Filter: ((type = 'autopay.type.recurring'::bpchar) AND (deleted = 0))
  • Rows Removed by Filter: 2381
20. 133.998 3,453.261 ↑ 1.0 313,790 1

Hash (cost=60,495.15..60,495.15 rows=314,960 width=8) (actual time=3,453.261..3,453.261 rows=313,790 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 2574kB
21. 3,319.263 3,319.263 ↑ 1.0 313,790 1

Seq Scan on loan_setup_entity lse (cost=0.00..60,495.15 rows=314,960 width=8) (actual time=3.360..3,319.263 rows=313,790 loops=1)

  • Filter: (orig_final_payment_date <= (CURRENT_DATE + 7))
  • Rows Removed by Filter: 74732
22. 34.157 120,845.014 ↑ 15,961,261.0 1 1

Hash Join (cost=665,276.21..952,957.52 rows=15,961,261 width=260) (actual time=120,691.155..120,845.014 rows=1 loops=1)

  • Hash Cond: (loan_autopay_entity_7.loan_id = tx.loan_id)
  • Join Filter: ((min(loan_autopay_entity_7.apply_date)) <> tx.date)
  • Rows Removed by Join Filter: 10193
23. 5.405 121.284 ↑ 2.9 10,195 1

Merge Anti Join (cost=0.83..47,527.13 rows=29,371 width=8) (actual time=0.960..121.284 rows=10,195 loops=1)

  • Merge Cond: (loan_autopay_entity_7.loan_id = loan_autopay_entity_6.loan_id)
  • Join Filter: (loan_autopay_entity_6.apply_date <= (min(loan_autopay_entity_7.apply_date)))
  • Rows Removed by Join Filter: 79
24. 23.235 88.350 ↑ 3.4 10,432 1

GroupAggregate (cost=0.41..23,968.43 rows=35,245 width=8) (actual time=0.650..88.350 rows=10,432 loops=1)

  • Group Key: loan_autopay_entity_7.loan_id
  • Filter: (min(loan_autopay_entity_7.apply_date) <= (CURRENT_DATE + 7))
  • Rows Removed by Filter: 13554
25. 65.115 65.115 ↓ 1.2 45,344 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity loan_autopay_entity_7 (cost=0.41..23,322.81 rows=39,090 width=8) (actual time=0.522..65.115 rows=45,344 loops=1)

  • Filter: ((name = ANY ('{Autopay,""Autopay - Final Payment""}'::text[])) AND (type = 'autopay.type.recurring'::bpchar) AND (deleted = 0))
  • Rows Removed by Filter: 2383
26. 27.529 27.529 ↑ 1.2 1,642 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity loan_autopay_entity_6 (cost=0.41..23,107.98 rows=1,982 width=8) (actual time=0.012..27.529 rows=1,642 loops=1)

  • Filter: (name <> ALL ('{Autopay,""Autopay - Final Payment""}'::text[]))
  • Rows Removed by Filter: 46085
27. 103.095 120,689.573 ↓ 2.6 285,885 1

Hash (cost=663,482.96..663,482.96 rows=109,234 width=8) (actual time=120,689.573..120,689.573 rows=285,885 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 4 (originally 2) Memory Usage: 3815kB
28. 115.668 120,586.478 ↓ 2.6 285,885 1

Subquery Scan on tx (cost=648,137.03..663,482.96 rows=109,234 width=8) (actual time=119,722.432..120,586.478 rows=285,885 loops=1)

29. 394.155 120,470.810 ↓ 2.6 285,885 1

GroupAggregate (cost=648,137.03..662,390.62 rows=109,234 width=8) (actual time=119,722.430..120,470.810 rows=285,885 loops=1)

  • Group Key: loan_tx.entity_id
30. 1,137.850 120,076.655 ↑ 1.5 1,127,185 1

Sort (cost=648,137.03..652,433.09 rows=1,718,421 width=12) (actual time=119,722.414..120,076.655 rows=1,127,185 loops=1)

  • Sort Key: loan_tx.entity_id
  • Sort Method: external merge Disk: 28744kB
31. 118,938.805 118,938.805 ↑ 1.5 1,127,185 1

Index Scan using loan_tx_date1 on loan_tx (cost=0.44..411,434.75 rows=1,718,421 width=12) (actual time=20.950..118,938.805 rows=1,127,185 loops=1)

  • Index Cond: (date > CURRENT_DATE)
  • Filter: (type = 'scheduledPayment'::bpchar)
  • Rows Removed by Filter: 1127177
32. 0.932 61.344 ↓ 0.0 0 1

Hash Left Join (cost=2.25..23,281.52 rows=1,970 width=237) (actual time=61.344..61.344 rows=0 loops=1)

  • Hash Cond: (lae_2.apply_date = bh.holiday_date)
  • Filter: ((date_part('dow'::text, (lae_2.apply_date)::timestamp without time zone) = ANY ('{0,6}'::double precision[])) OR (bh.holiday_date IS NOT NULL))
  • Rows Removed by Filter: 1630
33. 60.381 60.381 ↑ 1.2 1,630 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity lae_2 (cost=0.41..23,215.39 rows=1,970 width=21) (actual time=0.020..60.381 rows=1,630 loops=1)

  • Filter: ((type <> 'autopay.type.recurring'::bpchar) AND (deleted = 0))
  • Rows Removed by Filter: 46097
34. 0.014 0.031 ↑ 1.0 37 1

Hash (cost=1.37..1.37 rows=37 width=4) (actual time=0.031..0.031 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
35. 0.017 0.017 ↑ 1.0 37 1

Seq Scan on bank_holidays bh (cost=0.00..1.37 rows=37 width=4) (actual time=0.008..0.017 rows=37 loops=1)

36. 0.209 91.897 ↓ 0.0 0 1

Hash Left Join (cost=2.26..667.13 rows=490 width=246) (actual time=91.897..91.897 rows=0 loops=1)

  • Hash Cond: (pe.apply_date = bh_1.holiday_date)
  • Filter: ((date_part('dow'::text, (pe.apply_date)::timestamp without time zone) = ANY ('{0,6}'::double precision[])) OR (bh_1.holiday_date IS NOT NULL))
  • Rows Removed by Filter: 114
37. 91.656 91.656 ↑ 4.3 114 1

Index Scan using payment_entity_apply_date on payment_entity pe (cost=0.43..649.31 rows=490 width=30) (actual time=8.504..91.656 rows=114 loops=1)

  • Index Cond: (apply_date >= CURRENT_DATE)
  • Filter: (((status)::text <> 'payment.status.voided'::text) AND ((echeck_auth_type)::text <> 'payment.echeckauth.PPD'::text) AND (deleted = 0) AND (payment_method_id = 4))
  • Rows Removed by Filter: 333
38. 0.014 0.032 ↑ 1.0 37 1

Hash (cost=1.37..1.37 rows=37 width=4) (actual time=0.032..0.032 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
39. 0.018 0.018 ↑ 1.0 37 1

Seq Scan on bank_holidays bh_1 (cost=0.00..1.37 rows=37 width=4) (actual time=0.008..0.018 rows=37 loops=1)

40. 0.098 0.098 ↓ 0.0 0 1

Index Scan using payment_entity_apply_date on payment_entity (cost=0.43..637.18 rows=167 width=246) (actual time=0.098..0.098 rows=0 loops=1)

  • Index Cond: (apply_date > CURRENT_DATE)
  • Filter: (((status)::text <> 'payment.status.voided'::text) AND (payment_method_id <> 4) AND (deleted = 0))
  • Rows Removed by Filter: 64
41. 258.298 258.298 ↓ 0.0 0 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity lae_3 (cost=0.41..45,835.09 rows=41,632 width=237) (actual time=258.298..258.298 rows=0 loops=1)

  • Filter: ((deleted = 0) AND (apply_date <= (CURRENT_DATE + 7)) AND (dw_reporting_meta.prev_business_date(apply_date) <> (process_datetime)::date))
  • Rows Removed by Filter: 47727
42. 0.001 5,541.833 ↓ 0.0 0 1

Subquery Scan on a (cost=63,357.01..206,615.94 rows=28,403 width=260) (actual time=5,541.833..5,541.833 rows=0 loops=1)

43. 0.003 5,541.832 ↓ 0.0 0 1

WindowAgg (cost=63,357.01..205,763.85 rows=28,403 width=16) (actual time=5,541.831..5,541.832 rows=0 loops=1)

44. 0.002 5,541.829 ↓ 0.0 0 1

Nested Loop Anti Join (cost=63,357.01..205,408.81 rows=28,403 width=8) (actual time=5,541.829..5,541.829 rows=0 loops=1)

45. 760.589 5,541.827 ↓ 0.0 0 1

Hash Left Join (cost=63,356.59..190,220.85 rows=31,798 width=12) (actual time=5,541.827..5,541.827 rows=0 loops=1)

  • Hash Cond: (pe_1.id = pcf.payment_id)
  • Filter: ((pcf.endpoint = ''::text) OR (pcf.endpoint IS NULL))
  • Rows Removed by Filter: 1060655
46. 3,584.313 3,584.313 ↓ 1.2 1,060,655 1

Index Scan using payment_entity_apply_date on payment_entity pe_1 (cost=0.43..106,042.25 rows=921,687 width=12) (actual time=2.678..3,584.313 rows=1,060,655 loops=1)

  • Index Cond: ((apply_date >= '2018-01-01'::date) AND (apply_date <= CURRENT_DATE))
  • Filter: ((child IS NULL) AND (((reverse_reason)::text ~~ '%nachaErrorCode'::text) OR (reverse_reason IS NULL)) AND (deleted = 0) AND (payment_method_id = 4))
  • Rows Removed by Filter: 52537
47. 598.306 1,196.925 ↑ 1.0 1,590,985 1

Hash (cost=33,900.07..33,900.07 rows=1,604,407 width=18) (actual time=1,196.924..1,196.925 rows=1,590,985 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2955kB
48. 598.619 598.619 ↑ 1.0 1,590,985 1

Seq Scan on payment_custom_fields pcf (cost=0.00..33,900.07 rows=1,604,407 width=18) (actual time=0.007..598.619 rows=1,590,985 loops=1)

49. 0.000 0.000 ↓ 0.0 0

Index Scan using achfilerecord_payment_id on achfilerecord (cost=0.43..0.87 rows=10 width=4) (never executed)

  • Index Cond: (payment_id = pe_1.id)
  • Filter: (product = 'BALANCE_CREDIT'::text)
50. 0.001 6,604.375 ↓ 0.0 0 1

Subquery Scan on a_1 (cost=71,509.89..156,704.09 rows=1 width=260) (actual time=6,604.375..6,604.375 rows=0 loops=1)

51. 0.003 6,604.374 ↓ 0.0 0 1

WindowAgg (cost=71,509.89..156,704.06 rows=1 width=22) (actual time=6,604.374..6,604.374 rows=0 loops=1)

52. 592.663 6,604.371 ↓ 0.0 0 1

Hash Anti Join (cost=71,509.89..156,704.04 rows=1 width=14) (actual time=6,604.370..6,604.371 rows=0 loops=1)

  • Hash Cond: (ach.payment_id = pe_2.id)
53. 5,039.708 5,039.708 ↓ 1.1 829,540 1

Index Scan using achfilerecord_effectivedate on achfilerecord ach (cost=0.43..66,171.49 rows=780,418 width=18) (actual time=3.319..5,039.708 rows=829,540 loops=1)

  • Index Cond: (effectivedate > '2018-06-01'::date)
  • Filter: ((product = 'BALANCE_CREDIT'::text) AND (isorigination = 0))
  • Rows Removed by Filter: 122576
54. 514.261 972.000 ↓ 1.0 1,538,255 1

Hash (cost=46,417.02..46,417.02 rows=1,529,396 width=4) (actual time=972.000..972.000 rows=1,538,255 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 2719kB
55. 457.739 457.739 ↓ 1.0 1,538,255 1

Index Only Scan using payment_entity_id_conditional on payment_entity pe_2 (cost=0.43..46,417.02 rows=1,529,396 width=4) (actual time=1.322..457.739 rows=1,538,255 loops=1)

  • Heap Fetches: 82254
56. 0.024 3,487.269 ↓ 0.0 0 1

Nested Loop (cost=17,296.19..183,749.66 rows=320 width=260) (actual time=3,487.269..3,487.269 rows=0 loops=1)

57. 48.795 3,485.037 ↑ 14.0 23 1

Merge Join (cost=17,295.77..183,143.09 rows=322 width=44) (actual time=3,484.219..3,485.037 rows=23 loops=1)

  • Merge Cond: (payment_entity_1.entity_id = lset.loan_id)
58. 675.245 3,104.993 ↓ 1.3 207,217 1

GroupAggregate (cost=0.43..163,893.75 rows=155,743 width=36) (actual time=5.007..3,104.993 rows=207,217 loops=1)

  • Group Key: payment_entity_1.entity_id
59. 2,429.748 2,429.748 ↓ 1.2 1,367,515 1

Index Scan using payment_entity_entity_id on payment_entity payment_entity_1 (cost=0.43..156,123.06 rows=1,164,780 width=10) (actual time=0.033..2,429.748 rows=1,367,515 loops=1)

  • Filter: ((reverse_reason IS NULL) AND ((status)::text <> 'payment.status.voided'::text) AND (deleted = 0))
  • Rows Removed by Filter: 233503
60. 0.051 331.249 ↑ 30.8 26 1

Sort (cost=17,295.34..17,297.34 rows=800 width=8) (actual time=331.240..331.249 rows=26 loops=1)

  • Sort Key: lset.loan_id
  • Sort Method: quicksort Memory: 26kB
61. 331.198 331.198 ↑ 30.8 26 1

Seq Scan on loan_settings_entity lset (cost=0.00..17,256.76 rows=800 width=8) (actual time=248.580..331.198 rows=26 loops=1)

  • Filter: (loan_sub_status_id = 40)
  • Rows Removed by Filter: 388496
62. 2.208 2.208 ↓ 0.0 0 23

Index Scan using loan_setup_entity_loan_id on loan_setup_entity lse_1 (cost=0.42..1.86 rows=1 width=9) (actual time=0.096..0.096 rows=0 loops=23)

  • Index Cond: (loan_id = payment_entity_1.entity_id)
  • Filter: ((sum(payment_entity_1.amount)) <> loan_amount)
  • Rows Removed by Filter: 1
63. 0.065 75.676 ↓ 0.0 0 1

Nested Loop (cost=0.85..31,944.44 rows=314 width=239) (actual time=75.676..75.676 rows=0 loops=1)

  • Join Filter: (((pe_3.created - '06:00:00'::interval))::date > (lscf.notice_of_cancel_date)::date)
  • Rows Removed by Join Filter: 3
64. 0.055 75.455 ↑ 30.8 26 1

Nested Loop (cost=0.42..23,336.76 rows=800 width=19) (actual time=40.074..75.455 rows=26 loops=1)

65. 52.312 52.312 ↑ 30.8 26 1

Seq Scan on loan_settings_entity lset_1 (cost=0.00..17,256.76 rows=800 width=16) (actual time=40.035..52.312 rows=26 loops=1)

  • Filter: (loan_sub_status_id = 40)
  • Rows Removed by Filter: 388496
66. 23.088 23.088 ↑ 1.0 1 26

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf (cost=0.42..7.60 rows=1 width=15) (actual time=0.888..0.888 rows=1 loops=26)

  • Index Cond: (loan_settings_id = lset_1.id)
67. 0.156 0.156 ↓ 0.0 0 26

Index Scan using payment_entity_entity_id on payment_entity pe_3 (cost=0.43..10.69 rows=3 width=16) (actual time=0.006..0.006 rows=0 loops=26)

  • Index Cond: (entity_id = lset_1.loan_id)
  • Filter: (((status)::text <> 'payment.status.voided'::text) AND (((created - '06:00:00'::interval))::date >= '2019-10-08'::date))
  • Rows Removed by Filter: 1
68. 0.037 5,069.968 ↑ 14,313.0 3 1

Hash Join (cost=24,678.80..106,246.93 rows=42,939 width=209) (actual time=4,982.967..5,069.968 rows=3 loops=1)

  • Hash Cond: (lp.portfolio_id = pe_4.id)
69. 72.464 5,068.164 ↑ 12,063.7 15 1

Nested Loop (cost=24,676.89..104,278.27 rows=180,956 width=27) (actual time=4,981.172..5,068.164 rows=15 loops=1)

  • Join Filter: ((lp.portfolio_id = 23) OR (lse_2.autopay_enabled = 0) OR (lscf_1.payment_type = 'DirectPay'::text))
  • Rows Removed by Join Filter: 169023
70. 26.661 3,642.292 ↑ 1.6 24,168 1

Nested Loop (cost=24,676.46..67,675.45 rows=38,177 width=27) (actual time=138.386..3,642.292 rows=24,168 loops=1)

71. 135.325 401.287 ↑ 1.6 24,168 1

Hash Join (cost=24,676.03..42,944.12 rows=38,177 width=30) (actual time=137.585..401.287 rows=24,168 loops=1)

  • Hash Cond: (lse_2.loan_id = lae_4.loan_id)
72. 155.616 155.616 ↓ 1.0 388,383 1

Seq Scan on loan_settings_entity lse_2 (cost=0.00..17,256.76 rows=385,261 width=18) (actual time=0.005..155.616 rows=388,383 loops=1)

  • Filter: (loan_sub_status_id <> ALL ('{39,40}'::bigint[]))
  • Rows Removed by Filter: 139
73. 8.853 110.346 ↑ 1.6 24,175 1

Hash (cost=24,196.83..24,196.83 rows=38,336 width=12) (actual time=110.346..110.346 rows=24,175 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1551kB
74. 10.211 101.493 ↑ 1.6 24,175 1

Subquery Scan on lae_4 (cost=0.41..24,196.83 rows=38,336 width=12) (actual time=0.026..101.493 rows=24,175 loops=1)

75. 24.998 91.282 ↑ 1.6 24,175 1

GroupAggregate (cost=0.41..23,813.47 rows=38,336 width=12) (actual time=0.024..91.282 rows=24,175 loops=1)

  • Group Key: loan_autopay_entity_8.loan_id
76. 66.284 66.284 ↓ 1.1 46,976 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity loan_autopay_entity_8 (cost=0.41..23,107.98 rows=42,951 width=12) (actual time=0.016..66.284 rows=46,976 loops=1)

  • Filter: (deleted = 0)
  • Rows Removed by Filter: 751
77. 3,214.344 3,214.344 ↑ 1.0 1 24,168

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_1 (cost=0.42..0.65 rows=1 width=9) (actual time=0.133..0.133 rows=1 loops=24,168)

  • Index Cond: (loan_settings_id = lse_2.id)
78. 1,353.408 1,353.408 ↑ 1.0 7 24,168

Index Scan using loan__portfolio_loan_id on loan__portfolio lp (cost=0.43..0.84 rows=7 width=8) (actual time=0.052..0.056 rows=7 loops=24,168)

  • Index Cond: (loan_id = lse_2.loan_id)
79. 0.010 1.767 ↑ 1.0 14 1

Hash (cost=1.74..1.74 rows=14 width=12) (actual time=1.766..1.767 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
80. 1.757 1.757 ↑ 1.0 14 1

Seq Scan on portfolio_entity pe_4 (cost=0.00..1.74 rows=14 width=12) (actual time=1.750..1.757 rows=14 loops=1)

  • Filter: (category_id = 4)
  • Rows Removed by Filter: 45
81. 0.085 2,552.451 ↑ 56.0 1 1

Nested Loop (cost=1.54..376,363.21 rows=56 width=203) (actual time=2,210.979..2,552.451 rows=1 loops=1)

82. 0.086 2,550.947 ↑ 52.0 1 1

Nested Loop Left Join (cost=0.86..375,930.41 rows=52 width=42) (actual time=2,209.477..2,550.947 rows=1 loops=1)

  • Filter: (CASE WHEN ((pe_5.payment_method_id = 4) AND (f9.payment_method = 'ACH/RCC'::text)) THEN 1 WHEN ((pe_5.payment_method_id = 3) AND (f9.payment_method = 'Debit Card'::text)) THEN 1 WHEN ((lae_5.amount IS NOT NULL) AND (f9.payment_method = 'ACH/RCC'::text)) THEN 1 ELSE 0 END = 0)
  • Rows Removed by Filter: 66
83. 0.163 2,183.871 ↑ 161.3 65 1

Nested Loop Left Join (cost=0.43..239,527.19 rows=10,485 width=53) (actual time=1,876.090..2,183.871 rows=65 loops=1)

84. 2,182.684 2,182.684 ↑ 163.8 64 1

Seq Scan on worksheet_details f9 (cost=0.00..157,095.02 rows=10,485 width=41) (actual time=1,876.036..2,182.684 rows=64 loops=1)

  • Filter: ((campaign !~~ 'Chorus%'::text) AND (("timestamp")::date >= '2018-05-01'::date) AND ((payment_transactions ~~ '%Single Payment%'::text) OR (payment_transactions ~~ '%Payoff%'::text) OR (payment_transactions ~~ '%Settlement%'::text)) AND (("timestamp")::date >= '2019-10-08'::date))
  • Rows Removed by Filter: 2594421
85. 1.024 1.024 ↑ 1.0 1 64

Index Scan using payment_entity_entity_id on payment_entity pe_5 (cost=0.43..7.85 rows=1 width=24) (actual time=0.009..0.016 rows=1 loops=64)

  • Index Cond: (entity_id = (f9.loan_id)::integer)
  • Filter: (((echeck_auth_type)::text ~~ '%TEL'::text) AND (payment_method_id = ANY ('{3,4}'::integer[])) AND ((status)::text <> 'payment.status.voided'::text) AND (info !~~ 'AutoPay%'::text) AND (deleted = 0) AND (((created - '07:00:00'::interval))::date = (f9.""timestamp"")::date))
  • Rows Removed by Filter: 6
86. 366.990 366.990 ↓ 0.0 0 65

Index Scan using loan_autopay_entity_loan_id on loan_autopay_entity lae_5 (cost=0.43..12.98 rows=1 width=26) (actual time=3.928..5.646 rows=0 loops=65)

  • Index Cond: (loan_id = (f9.loan_id)::integer)
  • Filter: ((status = ANY ('{autopay.status.pending,autopay.status.completed}'::bpchar[])) AND (payment_method_auth_type = 'payment.echeckauth.TEL'::bpchar) AND (type = 'autopay.type.single'::bpchar) AND (deleted = 0) AND (((created - '07:00:00'::interval))::date = (f9.""timestamp"")::date))
  • Rows Removed by Filter: 7
87. 1.419 1.419 ↑ 1.0 1 1

Index Scan using call_log_extended_call_id on call_log_extended cl (cost=0.68..8.28 rows=1 width=7) (actual time=1.419..1.419 rows=1 loops=1)

  • Index Cond: (call_id = (dw_reporting_meta.text_to_integer(f9.call_id))::numeric)
  • Filter: (disposition !~~ '%Promise%'::text)
88. 0.135 1,958.897 ↑ 1,878.3 6 1

Nested Loop (cost=1.54..463,365.91 rows=11,270 width=203) (actual time=1,871.258..1,958.897 rows=6 loops=1)

89. 0.125 1,955.234 ↑ 1,747.5 6 1

Nested Loop Left Join (cost=0.86..375,930.41 rows=10,485 width=44) (actual time=1,869.620..1,955.234 rows=6 loops=1)

  • Filter: (((pe_6.amount)::text <> f9_1.payment_setup_amount) OR ((lae_6.amount)::text <> f9_1.payment_setup_amount))
  • Rows Removed by Filter: 61
90. 0.131 1,953.939 ↑ 161.3 65 1

Nested Loop Left Join (cost=0.43..239,527.19 rows=10,485 width=55) (actual time=1,869.502..1,953.939 rows=65 loops=1)

91. 1,952.976 1,952.976 ↑ 163.8 64 1

Seq Scan on worksheet_details f9_1 (cost=0.00..157,095.02 rows=10,485 width=41) (actual time=1,869.451..1,952.976 rows=64 loops=1)

  • Filter: ((campaign !~~ 'Chorus%'::text) AND (("timestamp")::date >= '2018-05-01'::date) AND ((payment_transactions ~~ '%Single Payment%'::text) OR (payment_transactions ~~ '%Payoff%'::text) OR (payment_transactions ~~ '%Settlement%'::text)) AND (("timestamp")::date >= '2019-10-08'::date))
  • Rows Removed by Filter: 2594421
92. 0.832 0.832 ↑ 1.0 1 64

Index Scan using payment_entity_entity_id on payment_entity pe_6 (cost=0.43..7.85 rows=1 width=26) (actual time=0.007..0.013 rows=1 loops=64)

  • Index Cond: (entity_id = (f9_1.loan_id)::integer)
  • Filter: (((echeck_auth_type)::text ~~ '%TEL'::text) AND (payment_method_id = ANY ('{3,4}'::integer[])) AND ((status)::text <> 'payment.status.voided'::text) AND (info !~~ 'AutoPay%'::text) AND (deleted = 0) AND (((created - '07:00:00'::interval))::date = (f9_1.""timestamp"")::date))
  • Rows Removed by Filter: 6
93. 1.170 1.170 ↓ 0.0 0 65

Index Scan using loan_autopay_entity_loan_id on loan_autopay_entity lae_6 (cost=0.43..12.98 rows=1 width=26) (actual time=0.014..0.018 rows=0 loops=65)

  • Index Cond: (loan_id = (f9_1.loan_id)::integer)
  • Filter: ((status = ANY ('{autopay.status.pending,autopay.status.completed}'::bpchar[])) AND (payment_method_auth_type = 'payment.echeckauth.TEL'::bpchar) AND (type = 'autopay.type.single'::bpchar) AND (deleted = 0) AND (((created - '07:00:00'::interval))::date = (f9_1.""timestamp"")::date))
  • Rows Removed by Filter: 7
94. 3.528 3.528 ↑ 1.0 1 6

Index Scan using call_log_extended_call_id on call_log_extended cl_1 (cost=0.68..8.28 rows=1 width=7) (actual time=0.587..0.588 rows=1 loops=6)

  • Index Cond: (call_id = (dw_reporting_meta.text_to_integer(f9_1.call_id))::numeric)
  • Filter: (disposition !~~ '%Promise%'::text)
95. 0.191 749.574 ↓ 6.0 6 1

Nested Loop Left Join (cost=0.43..198,512.84 rows=1 width=246) (actual time=700.231..749.574 rows=6 loops=1)

  • Filter: (wd.loan_id IS NULL)
  • Rows Removed by Filter: 116
96. 738.691 738.691 ↑ 22.7 99 1

Seq Scan on payment_entity pe_7 (cost=0.00..135,560.51 rows=2,248 width=38) (actual time=694.286..738.691 rows=99 loops=1)

  • Filter: (((echeck_auth_type)::text ~~ '%TEL'::text) AND ((status)::text <> 'payment.status.voided'::text) AND (info !~~ 'AutoPay%'::text) AND (payment_method_id = ANY ('{3,4}'::integer[])) AND (payment_type_id <> 2) AND (deleted = 0) AND ((created)::date >= '2019-10-08'::date))
  • Rows Removed by Filter: 1600919
97. 10.692 10.692 ↑ 1.0 1 99

Index Scan using worksheet_details_loan_id on worksheet_details wd (cost=0.43..27.99 rows=1 width=31) (actual time=0.096..0.108 rows=1 loops=99)

  • Index Cond: (pe_7.entity_id = dw_reporting_meta.text_to_integer(loan_id))
  • Filter: ((campaign !~~ 'Chorus%'::text) AND (((pe_7.created - '07:00:00'::interval))::date = ("timestamp")::date))
  • Rows Removed by Filter: 8
98. 0.271 67.882 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.84..24,802.79 rows=1 width=237) (actual time=31.574..67.882 rows=1 loops=1)

  • Filter: (wd_1.loan_id IS NULL)
  • Rows Removed by Filter: 176
99. 61.502 61.502 ↓ 5.1 149 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity lae_7 (cost=0.41..23,645.04 rows=29 width=29) (actual time=1.274..61.502 rows=149 loops=1)

  • Filter: ((payment_type_id <> 2) AND (payment_method_auth_type = 'payment.echeckauth.TEL'::bpchar) AND (deleted = 0) AND (type = 'autopay.type.single'::bpchar) AND ((created)::date >= '2019-10-08'::date))
  • Rows Removed by Filter: 47578
100. 6.109 6.109 ↑ 1.0 1 149

Index Scan using worksheet_details_loan_id on worksheet_details wd_1 (cost=0.43..39.91 rows=1 width=31) (actual time=0.028..0.041 rows=1 loops=149)

  • Index Cond: (lae_7.loan_id = dw_reporting_meta.text_to_integer(loan_id))
  • Filter: ((campaign !~~ 'Chorus%'::text) AND (((lae_7.created - '07:00:00'::interval))::date = ("timestamp")::date))
  • Rows Removed by Filter: 7
101. 0.045 8,535.272 ↓ 0.0 0 1

Nested Loop (cost=0.85..158,959.98 rows=94 width=260) (actual time=8,535.272..8,535.272 rows=0 loops=1)

102. 8,530.055 8,530.055 ↑ 7.0 12 1

Index Scan using payment_entity_entity_id_cond_reverse_reason on payment_entity pe_8 (cost=0.42..156,965.50 rows=84 width=50) (actual time=5,372.063..8,530.055 rows=12 loops=1)

  • Filter: (((reverse_reason)::text !~~ '%nachaErrorCode'::text) AND (payment_method_id = 4) AND (reverse_date <= dw_reporting_meta.add_business_days(apply_date, 5)) AND (reverse_date >= dw_reporting_meta.prev_business_date((CURRENT_DATE - 1))))
  • Rows Removed by Filter: 224296
103. 5.172 5.172 ↓ 0.0 0 12

Index Scan using achfilerecord_payment_id on achfilerecord ach_1 (cost=0.43..23.62 rows=10 width=8) (actual time=0.431..0.431 rows=0 loops=12)

  • Index Cond: (payment_id = pe_8.id)
  • Filter: (product = 'BALANCE_CREDIT'::text)
104. 58.815 58.815 ↓ 0.0 0 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity loan_autopay_entity_9 (cost=0.41..23,215.41 rows=1 width=237) (actual time=58.815..58.815 rows=0 loops=1)

  • Filter: ((type = 'autopay.type.recurringMatch'::bpchar) AND (deleted = 0))
  • Rows Removed by Filter: 47727
105. 0.002 56.160 ↓ 0.0 0 1

Nested Loop (cost=0.41..23,418.10 rows=704 width=215) (actual time=56.160..56.160 rows=0 loops=1)

  • Join Filter: (lae_8.payment_type_id = cpte.id)
106. 56.158 56.158 ↓ 0.0 0 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity lae_8 (cost=0.41..23,269.10 rows=704 width=25) (actual time=56.158..56.158 rows=0 loops=1)

  • Filter: ((deleted = 0) AND (payment_type_id <> ALL ('{2,13,14}'::integer[])))
  • Rows Removed by Filter: 47727
107. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.19 rows=13 width=14) (never executed)

108. 0.000 0.000 ↓ 0.0 0

Seq Scan on custom_payment_type_entity cpte (cost=0.00..1.13 rows=13 width=14) (never executed)

109. 0.013 263.874 ↑ 420.0 1 1

Nested Loop (cost=0.43..44,397.59 rows=420 width=224) (actual time=57.661..263.874 rows=1 loops=1)

  • Join Filter: (pe_9.payment_type_id = cpte_1.id)
  • Rows Removed by Join Filter: 12
110. 263.829 263.829 ↑ 420.0 1 1

Index Scan using payment_entity_apply_date on payment_entity pe_9 (cost=0.43..44,308.23 rows=420 width=34) (actual time=57.632..263.829 rows=1 loops=1)

  • Index Cond: (apply_date > '2018-12-31'::date)
  • Filter: (((status)::text !~~ '%voided'::text) AND (deleted = 0) AND (payment_type_id <> ALL ('{2,13,14}'::integer[])))
  • Rows Removed by Filter: 476396
111. 0.013 0.032 ↑ 1.0 13 1

Materialize (cost=0.00..1.19 rows=13 width=14) (actual time=0.021..0.032 rows=13 loops=1)

112. 0.019 0.019 ↑ 1.0 13 1

Seq Scan on custom_payment_type_entity cpte_1 (cost=0.00..1.13 rows=13 width=14) (actual time=0.014..0.019 rows=13 loops=1)

113. 0.003 237.687 ↓ 0.0 0 1

Hash Join (cost=36.53..45,842.67 rows=12,552 width=224) (actual time=237.687..237.687 rows=0 loops=1)

  • Hash Cond: (pe_10.payment_method_id = cpme.id)
114. 237.684 237.684 ↓ 0.0 0 1

Index Scan using payment_entity_apply_date on payment_entity pe_10 (cost=0.43..45,445.70 rows=12,552 width=34) (actual time=237.684..237.684 rows=0 loops=1)

  • Index Cond: (apply_date >= '2018-12-31'::date)
  • Filter: (((status)::text <> 'payment.status.voided'::text) AND (deleted = 0) AND (payment_method_id <> ALL ('{2,3,4,13,14}'::integer[])))
  • Rows Removed by Filter: 478905
115. 0.000 0.000 ↓ 0.0 0

Hash (cost=21.60..21.60 rows=1,160 width=14) (never executed)

116. 0.000 0.000 ↓ 0.0 0

Seq Scan on custom_payment_method_entity cpme (cost=0.00..21.60 rows=1,160 width=14) (never executed)

117. 60.312 60.312 ↓ 0.0 0 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity lae_9 (cost=0.41..23,219.38 rows=228 width=237) (actual time=60.311..60.312 rows=0 loops=1)

  • Filter: ((payment_extra_towards <> ALL ('{payment.extra.tx.principal,payment.extra.periods.principalonly}'::bpchar[])) AND (deleted = 0))
  • Rows Removed by Filter: 47727
118. 0.781 0.781 ↑ 25.0 1 1

Index Scan using payment_entity_apply_date on payment_entity pe_11 (cost=0.43..723.15 rows=25 width=246) (actual time=0.040..0.781 rows=1 loops=1)

  • Index Cond: (apply_date >= '2019-10-08'::date)
  • Filter: (((status)::text !~~ '%voided'::text) AND (payment_type_id = ANY ('{13,14}'::integer[])) AND (deleted = 0) AND (1 = CASE WHEN ((payment_type_id = 14) AND ((extra)::text = ANY ('{payment.extra.tx.principal,payment.extra.periods.principalonly}'::text[]))) THEN 0 WHEN ((payment_type_id = 13) AND ((extra)::text = ANY ('{payment.extra.tx.principalonly,payment.extra.periods.principalonly}'::text[]))) THEN 0 ELSE 1 END))
  • Rows Removed by Filter: 879
119. 0.002 929.167 ↓ 0.0 0 1

Nested Loop (cost=0.42..25,595.03 rows=1 width=260) (actual time=929.167..929.167 rows=0 loops=1)

120. 929.165 929.165 ↓ 0.0 0 1

Seq Scan on customer_entity ce (cost=0.00..25,585.34 rows=1 width=4) (actual time=929.165..929.165 rows=0 loops=1)

  • Filter: ((email IS NULL) OR (email = ''::text))
  • Rows Removed by Filter: 273707
121. 0.000 0.000 ↓ 0.0 0

Index Scan using loan__customer_customer_id on loan__customer lc (cost=0.42..9.67 rows=2 width=8) (never executed)

  • Index Cond: (customer_id = ce.id)
122. 0.126 6,703.787 ↓ 6.0 6 1

Nested Loop (cost=4.26..62,737.49 rows=1 width=240) (actual time=6,662.541..6,703.787 rows=6 loops=1)

  • Join Filter: (lse_3.loan_sub_status_id = lsse.id)
  • Rows Removed by Join Filter: 342
123. 0.013 6,703.571 ↓ 6.0 6 1

Nested Loop (cost=4.26..62,735.17 rows=1 width=26) (actual time=6,662.493..6,703.571 rows=6 loops=1)

124. 0.014 6,702.112 ↓ 6.0 6 1

Nested Loop (cost=3.84..62,734.58 rows=1 width=18) (actual time=6,661.849..6,702.112 rows=6 loops=1)

125. 202.848 6,700.232 ↓ 6.0 6 1

Merge Right Join (cost=3.42..62,733.89 rows=1 width=8) (actual time=6,661.216..6,700.232 rows=6 loops=1)

  • Merge Cond: (pae_1.entity_id = lc_1.customer_id)
  • Filter: (pae_1.id IS NULL)
  • Rows Removed by Filter: 388718
126. 1,986.295 1,986.295 ↑ 1.0 273,543 1

Index Scan using payment_account_entity_cond on payment_account_entity pae_1 (cost=0.42..25,437.57 rows=274,117 width=8) (actual time=0.689..1,986.295 rows=273,543 loops=1)

  • Filter: ((is_primary = 1) AND (deleted = 0))
  • Rows Removed by Filter: 105421
127. 186.376 4,511.089 ↓ 1.0 388,724 1

Materialize (cost=0.42..29,545.64 rows=387,308 width=8) (actual time=0.018..4,511.089 rows=388,724 loops=1)

128. 4,324.713 4,324.713 ↓ 1.0 388,528 1

Index Scan using loan__customer_customer_id on loan__customer lc_1 (cost=0.42..28,577.37 rows=387,308 width=8) (actual time=0.015..4,324.713 rows=388,528 loops=1)

129. 1.866 1.866 ↑ 1.0 1 6

Index Scan using customer_entity_id on customer_entity ce_1 (cost=0.42..0.68 rows=1 width=18) (actual time=0.311..0.311 rows=1 loops=6)

  • Index Cond: (id = lc_1.customer_id)
130. 1.446 1.446 ↑ 1.0 1 6

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_3 (cost=0.42..0.58 rows=1 width=16) (actual time=0.241..0.241 rows=1 loops=6)

  • Index Cond: (loan_id = lc_1.loan_id)
131. 0.090 0.090 ↑ 1.0 58 6

Seq Scan on loan_sub_status_entity lsse (cost=0.00..1.58 rows=58 width=16) (actual time=0.003..0.015 rows=58 loops=6)

132. 0.055 612.574 ↑ 16,583.0 4 1

Hash Join (cost=77,558.67..103,487.09 rows=66,332 width=240) (actual time=557.372..612.574 rows=4 loops=1)

  • Hash Cond: (lse_4.loan_sub_status_id = lsse_1.id)
133. 129.681 612.474 ↑ 16,583.0 4 1

Hash Join (cost=77,556.36..101,577.74 rows=66,332 width=26) (actual time=557.288..612.474 rows=4 loops=1)

  • Hash Cond: (lse_4.loan_id = lc_2.loan_id)
134. 142.262 142.262 ↓ 1.0 388,522 1

Seq Scan on loan_settings_entity lse_4 (cost=0.00..16,289.61 rows=386,861 width=16) (actual time=0.004..142.262 rows=388,522 loops=1)

135. 0.664 340.531 ↑ 16,588.2 4 1

Hash (cost=76,337.95..76,337.95 rows=66,353 width=18) (actual time=340.531..340.531 rows=4 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 513kB
136. 0.010 339.867 ↑ 16,588.2 4 1

Nested Loop (cost=43,920.72..76,337.95 rows=66,353 width=18) (actual time=339.780..339.867 rows=4 loops=1)

137. 6.805 339.826 ↑ 24,243.0 1 1

Hash Join (cost=43,920.30..61,009.97 rows=24,243 width=26) (actual time=339.749..339.826 rows=1 loops=1)

  • Hash Cond: (pae2.entity_id = ce_2.id)
138. 77.681 77.681 ↑ 9.9 6,749 1

Seq Scan on payment_account_entity pae2 (cost=0.00..15,491.28 rows=66,924 width=4) (actual time=0.023..77.681 rows=6,749 loops=1)

  • Filter: (((entity_type)::text = 'Entity.Customer'::text) AND (active = 1) AND (is_primary = 0) AND (deleted = 0))
  • Rows Removed by Filter: 372215
139. 17.460 255.340 ↑ 2.6 38,596 1

Hash (cost=42,099.95..42,099.95 rows=99,148 width=22) (actual time=255.339..255.340 rows=38,596 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 1574kB
140. 93.317 237.880 ↑ 2.6 38,596 1

Merge Join (cost=1.84..42,099.95 rows=99,148 width=22) (actual time=0.125..237.880 rows=38,596 loops=1)

  • Merge Cond: (ce_2.id = pae_2.entity_id)
141. 117.085 117.085 ↑ 1.0 271,073 1

Index Scan using customer_entity_id on customer_entity ce_2 (cost=0.42..29,285.03 rows=273,707 width=18) (actual time=0.012..117.085 rows=271,073 loops=1)

142. 27.478 27.478 ↑ 2.6 38,596 1

Index Only Scan using payment_account_entity_entity_id_type_flags on payment_account_entity pae_2 (cost=0.29..10,892.43 rows=99,148 width=4) (actual time=0.078..27.478 rows=38,596 loops=1)

  • Heap Fetches: 38596
143. 0.031 0.031 ↓ 2.0 4 1

Index Scan using loan__customer_customer_id on loan__customer lc_2 (cost=0.42..0.61 rows=2 width=8) (actual time=0.024..0.031 rows=4 loops=1)

  • Index Cond: (customer_id = ce_2.id)
144. 0.025 0.045 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.044..0.045 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
145. 0.020 0.020 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_1 (cost=0.00..1.58 rows=58 width=16) (actual time=0.005..0.020 rows=58 loops=1)

146. 20.584 636.803 ↓ 0.0 0 1

Hash Join (cost=38,113.16..54,587.13 rows=36,102 width=260) (actual time=636.803..636.803 rows=0 loops=1)

  • Hash Cond: (lc_3.customer_id = cpme_1.customer_id)
147. 146.987 298.620 ↓ 1.0 52,303 1

Hash Join (cost=5,824.27..21,040.80 rows=51,404 width=8) (actual time=56.997..298.620 rows=52,303 loops=1)

  • Hash Cond: (lc_3.loan_id = lse_5.loan_id)
148. 94.863 94.863 ↓ 1.0 388,528 1

Seq Scan on loan__customer lc_3 (cost=0.00..13,250.08 rows=387,308 width=8) (actual time=0.009..94.863 rows=388,528 loops=1)

149. 16.475 56.770 ↓ 1.0 52,303 1

Hash (cost=5,182.25..5,182.25 rows=51,362 width=8) (actual time=56.770..56.770 rows=52,303 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2556kB
150. 40.295 40.295 ↓ 1.0 52,303 1

Index Scan using loan_settings_entity_id_cond on loan_settings_entity lse_5 (cost=0.29..5,182.25 rows=51,362 width=8) (actual time=0.031..40.295 rows=52,303 loops=1)

151. 0.691 317.599 ↑ 12,556.8 11 1

Hash (cost=30,022.33..30,022.33 rows=138,125 width=4) (actual time=317.599..317.599 rows=11 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1025kB
152. 0.010 316.908 ↑ 12,556.8 11 1

Subquery Scan on cpme_1 (cost=0.42..30,022.33 rows=138,125 width=4) (actual time=7.451..316.908 rows=11 loops=1)

153. 92.797 316.898 ↑ 12,556.8 11 1

GroupAggregate (cost=0.42..28,641.08 rows=138,125 width=12) (actual time=7.450..316.898 rows=11 loops=1)

  • Group Key: payment_account_entity.entity_id
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 234925
154. 224.101 224.101 ↓ 1.3 234,947 1

Index Scan using payment_account_entity_cond on payment_account_entity (cost=0.42..26,384.98 rows=174,969 width=4) (actual time=0.018..224.101 rows=234,947 loops=1)

  • Filter: ((is_primary = 1) AND (active = 1) AND (deleted = 0))
  • Rows Removed by Filter: 144017
155. 14.081 7,312.542 ↓ 0.0 0 1

Nested Loop (cost=21,685.34..40,493.44 rows=1 width=209) (actual time=7,312.542..7,312.542 rows=0 loops=1)

  • Join Filter: ((pe_12.title <> 'DirectPay'::text) OR (lscf_2.payment_type <> 'DirectPay'::text))
  • Rows Removed by Join Filter: 8754
156. 197.537 5,827.789 ↓ 8,754.0 8,754 1

Nested Loop (cost=21,684.91..40,491.42 rows=1 width=20) (actual time=513.435..5,827.789 rows=8,754 loops=1)

  • Join Filter: (pe_12.id = lp_1.portfolio_id)
  • Rows Removed by Join Filter: 847570
157. 37.482 3,611.774 ↓ 61,166.0 61,166 1

Nested Loop (cost=21,683.78..40,488.34 rows=1 width=16) (actual time=513.227..3,611.774 rows=61,166 loops=1)

158. 84.299 519.146 ↓ 8,754.0 8,754 1

Hash Right Join (cost=21,683.35..40,487.43 rows=1 width=20) (actual time=511.780..519.146 rows=8,754 loops=1)

  • Hash Cond: (pae_3.entity_id = lc_4.customer_id)
  • Filter: (pae_3.id IS NULL)
  • Rows Removed by Filter: 49249
159. 134.523 134.523 ↑ 1.0 241,696 1

Seq Scan on payment_account_entity pae_3 (cost=0.00..14,543.87 rows=241,893 width=8) (actual time=0.011..134.523 rows=241,696 loops=1)

  • Filter: (((entity_type)::text = 'Entity.Customer'::text) AND (active = 1) AND (deleted = 0))
  • Rows Removed by Filter: 137268
160. 18.992 300.324 ↓ 1.0 52,303 1

Hash (cost=21,040.80..21,040.80 rows=51,404 width=24) (actual time=300.324..300.324 rows=52,303 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3373kB
161. 137.135 281.332 ↓ 1.0 52,303 1

Hash Join (cost=5,824.27..21,040.80 rows=51,404 width=24) (actual time=52.900..281.332 rows=52,303 loops=1)

  • Hash Cond: (lc_4.loan_id = lse_6.loan_id)
162. 91.577 91.577 ↓ 1.0 388,528 1

Seq Scan on loan__customer lc_4 (cost=0.00..13,250.08 rows=387,308 width=8) (actual time=0.007..91.577 rows=388,528 loops=1)

163. 16.920 52.620 ↓ 1.0 52,303 1

Hash (cost=5,182.25..5,182.25 rows=51,362 width=16) (actual time=52.620..52.620 rows=52,303 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2964kB
164. 35.700 35.700 ↓ 1.0 52,303 1

Index Scan using loan_settings_entity_id_cond on loan_settings_entity lse_6 (cost=0.29..5,182.25 rows=51,362 width=16) (actual time=0.018..35.700 rows=52,303 loops=1)

165. 3,055.146 3,055.146 ↑ 1.0 7 8,754

Index Scan using loan__portfolio_loan_id on loan__portfolio lp_1 (cost=0.43..0.83 rows=7 width=8) (actual time=0.335..0.349 rows=7 loops=8,754)

  • Index Cond: (loan_id = lc_4.loan_id)
166. 1,223.301 2,018.478 ↓ 2.3 14 61,166

Hash Join (cost=1.14..3.01 rows=6 width=12) (actual time=0.006..0.033 rows=14 loops=61,166)

  • Hash Cond: (pe_12.category_id = pce.id)
167. 795.158 795.158 ↑ 1.0 59 61,166

Seq Scan on portfolio_entity pe_12 (cost=0.00..1.59 rows=59 width=16) (actual time=0.001..0.013 rows=59 loops=61,166)

168. 0.005 0.019 ↑ 1.0 1 1

Hash (cost=1.12..1.12 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
169. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on portfolio_category_entity pce (cost=0.00..1.12 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1)

  • Filter: (title = 'PaymentType'::text)
  • Rows Removed by Filter: 9
170. 1,470.672 1,470.672 ↑ 1.0 1 8,754

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_2 (cost=0.42..2.00 rows=1 width=9) (actual time=0.167..0.168 rows=1 loops=8,754)

  • Index Cond: (loan_settings_id = lse_6.id)
171. 0.002 35.499 ↓ 0.0 0 1

Hash Join (cost=2.60..6,913.77 rows=46,748 width=240) (actual time=35.499..35.499 rows=0 loops=1)

  • Hash Cond: (lse_7.loan_sub_status_id = lsse_2.id)
172. 35.497 35.497 ↓ 0.0 0 1

Index Scan using loan_settings_entity_id_cond on loan_settings_entity lse_7 (cost=0.29..5,567.46 rows=46,748 width=28) (actual time=35.497..35.497 rows=0 loops=1)

  • Filter: ((date_part('year'::text, (closed_date)::timestamp without time zone) > '0'::double precision) OR (closed_date IS NOT NULL))
  • Rows Removed by Filter: 52303
173. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.58..1.58 rows=58 width=16) (never executed)

174. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_sub_status_entity lsse_2 (cost=0.00..1.58 rows=58 width=16) (never executed)

175. 0.037 127.504 ↑ 141,648.0 1 1

Hash Join (cost=2.31..23,524.67 rows=141,648 width=240) (actual time=124.754..127.504 rows=1 loops=1)

  • Hash Cond: (lse_8.loan_sub_status_id = lsse_3.id)
176. 127.402 127.402 ↑ 141,648.0 1 1

Seq Scan on loan_settings_entity lse_8 (cost=0.00..20,158.22 rows=141,648 width=24) (actual time=124.654..127.402 rows=1 loops=1)

  • Filter: ((loan_status_id = ANY ('{2,5}'::bigint[])) AND ((closed_date IS NULL) OR (date_part('year'::text, (closed_date)::timestamp without time zone) < '1'::double precision)))
  • Rows Removed by Filter: 388521
177. 0.040 0.065 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.064..0.065 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
178. 0.025 0.025 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_3 (cost=0.00..1.58 rows=58 width=16) (actual time=0.010..0.025 rows=58 loops=1)

179. 0.003 347.487 ↓ 0.0 0 1

Nested Loop (cost=0.84..26,084.67 rows=339 width=260) (actual time=347.486..347.487 rows=0 loops=1)

180. 0.006 347.446 ↑ 340.0 1 1

Nested Loop (cost=0.42..25,899.22 rows=340 width=8) (actual time=3.898..347.446 rows=1 loops=1)

181. 346.772 346.772 ↑ 342.0 1 1

Seq Scan on loan_settings_custom_fields lscf_3 (cost=0.00..23,177.33 rows=342 width=4) (actual time=3.226..346.772 rows=1 loops=1)

  • Filter: ((notice_of_cancel_date IS NULL) OR (notice_of_cancel_date = ''::text))
  • Rows Removed by Filter: 388521
182. 0.668 0.668 ↑ 1.0 1 1

Index Scan using loan_settings_entity_id on loan_settings_entity lse_9 (cost=0.42..7.95 rows=1 width=16) (actual time=0.667..0.668 rows=1 loops=1)

  • Index Cond: (id = lscf_3.loan_settings_id)
183. 0.038 0.038 ↓ 0.0 0 1

Index Scan using loan_entity_loan_id on loan_entity le (cost=0.42..0.53 rows=1 width=4) (actual time=0.037..0.038 rows=0 loops=1)

  • Index Cond: (id = lse_9.loan_id)
  • Filter: (deleted = 0)
  • Rows Removed by Filter: 1
184. 6,390.701 6,390.701 ↑ 263.2 10 1

Index Scan using loan_status_archive_date_loan_id on loan_status_archive (cost=0.57..321,854.25 rows=2,632 width=260) (actual time=570.563..6,390.701 rows=10 loops=1)

  • Index Cond: (date = CURRENT_DATE)
  • Filter: ((loan_sub_status_id <> ALL ('{39,40}'::integer[])) AND (loan_status_id = 4) AND ((amount_due < '0'::numeric) OR (principal_balance < '0'::numeric) OR (payoff < '30'::numeric)) AND (dw_reporting_meta.add_business_days(last_payment_date, 5) <= CURRENT_DATE))
  • Rows Removed by Filter: 385232
185. 321.953 321.953 ↑ 1.6 9 1

Index Scan using loan_status_archive_date_loan_id on loan_status_archive loan_status_archive_1 (cost=0.57..281,745.78 rows=14 width=260) (actual time=102.463..321.953 rows=9 loops=1)

  • Index Cond: (date = CURRENT_DATE)
  • Filter: ((loan_sub_status_id <> ALL ('{39,40}'::integer[])) AND (payoff >= 0.07) AND (payoff <= '15'::numeric) AND (loan_status_id = 4))
  • Rows Removed by Filter: 385233
186. 256.270 256.270 ↓ 0.0 0 1

Index Scan using loan_status_archive_active_sub_status_id_39 on loan_status_archive loan_status_archive_2 (cost=0.42..8,740.61 rows=83 width=260) (actual time=256.270..256.270 rows=0 loops=1)

  • Index Cond: (date = CURRENT_DATE)
  • Filter: ((dw_reporting_meta.add_business_days(last_payment_date, 5) <= CURRENT_DATE) OR ((last_payment_date)::text = ''::text))
  • Rows Removed by Filter: 113
187. 314.866 314.866 ↑ 20.8 4 1

Index Scan using loan_status_archive_date_loan_id on loan_status_archive loan_status_archive_3 (cost=0.57..321,361.42 rows=83 width=260) (actual time=271.301..314.866 rows=4 loops=1)

  • Index Cond: (date = CURRENT_DATE)
  • Filter: ((loan_sub_status_id = 40) AND ((dw_reporting_meta.add_business_days(last_payment_date, 5) <= CURRENT_DATE) OR ((last_payment_date)::text = ''::text)))
  • Rows Removed by Filter: 385238
188. 291.720 291.720 ↑ 8,181.2 6 1

Index Scan using loan_status_archive_date_loan_id on loan_status_archive lsa (cost=0.57..284,097.43 rows=49,087 width=260) (actual time=195.652..291.720 rows=6 loops=1)

  • Index Cond: (date = CURRENT_DATE)
  • Filter: ((loan_sub_status_id <> 35) AND (loan_status_id = 5) AND ((amount_due <> '0'::numeric) OR (principal_balance <> '0'::numeric) OR (payoff <> '0'::numeric)))
  • Rows Removed by Filter: 385236
189. 62.825 681.319 ↓ 0.0 0 1

Hash Join (cost=47,164.43..53,268.93 rows=1,922 width=211) (actual time=681.318..681.319 rows=0 loops=1)

  • Hash Cond: ((lot.loan_id = lse_10.loan_id) AND (lot.debt_provider = lscf_4.owner))
190. 68.041 68.041 ↓ 1.0 141,196 1

Seq Scan on dim_loan_ownership_transfers lot (cost=0.00..3,281.24 rows=140,057 width=9) (actual time=1.341..68.041 rows=141,196 loops=1)

  • Filter: ((debt_provider = ANY ('{SPV2,SPV3}'::text[])) AND (lms = 'LP'::text))
  • Rows Removed by Filter: 3420
191. 36.359 550.453 ↓ 1.0 65,128 1

Hash (cost=45,826.12..45,826.12 rows=64,154 width=23) (actual time=550.453..550.453 rows=65,128 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3585kB
192. 172.225 514.094 ↓ 1.0 65,128 1

Hash Join (cost=24,363.99..45,826.12 rows=64,154 width=23) (actual time=217.098..514.094 rows=65,128 loops=1)

  • Hash Cond: (lse_10.id = lscf_4.loan_settings_id)
193. 129.756 129.756 ↓ 1.0 388,522 1

Seq Scan on loan_settings_entity lse_10 (cost=0.00..16,289.61 rows=386,861 width=16) (actual time=0.005..129.756 rows=388,522 loops=1)

194. 27.653 212.113 ↓ 1.0 65,128 1

Hash (cost=23,177.33..23,177.33 rows=64,613 width=19) (actual time=212.113..212.113 rows=65,128 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 2985kB
195. 184.460 184.460 ↓ 1.0 65,128 1

Seq Scan on loan_settings_custom_fields lscf_4 (cost=0.00..23,177.33 rows=64,613 width=19) (actual time=0.007..184.460 rows=65,128 loops=1)

  • Filter: (unpledge_date <> ''::text)
  • Rows Removed by Filter: 323394
196. 140.272 850.267 ↓ 0.0 0 1

Hash Join (cost=32,382.29..62,599.40 rows=76,462 width=214) (actual time=850.267..850.267 rows=0 loops=1)

  • Hash Cond: (lscf_5.loan_settings_id = lse_11.id)
  • Join Filter: (lot_1.debt_provider <> lscf_5.owner)
  • Rows Removed by Join Filter: 35693
197. 251.988 251.988 ↑ 1.0 323,394 1

Seq Scan on loan_settings_custom_fields lscf_5 (cost=0.00..23,177.33 rows=325,013 width=17) (actual time=0.011..251.988 rows=323,394 loops=1)

  • Filter: (unpledge_date = ''::text)
  • Rows Removed by Filter: 65128
198. 36.707 458.007 ↓ 1.0 100,821 1

Hash (cost=30,546.33..30,546.33 rows=99,997 width=21) (actual time=458.007..458.007 rows=100,821 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3124kB
199. 110.920 421.300 ↓ 1.0 100,821 1

Hash Join (cost=23,014.37..30,546.33 rows=99,997 width=21) (actual time=266.722..421.300 rows=100,821 loops=1)

  • Hash Cond: (lot_1.loan_id = lse_11.loan_id)
200. 47.532 47.532 ↓ 1.0 100,821 1

Seq Scan on dim_loan_ownership_transfers lot_1 (cost=0.00..3,281.24 rows=100,732 width=9) (actual time=0.005..47.532 rows=100,821 loops=1)

  • Filter: ((lms = 'LP'::text) AND (debt_provider = 'SPV2'::text))
  • Rows Removed by Filter: 43795
201. 135.881 262.848 ↓ 1.0 388,522 1

Hash (cost=16,289.61..16,289.61 rows=386,861 width=16) (actual time=262.848..262.848 rows=388,522 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3303kB
202. 126.967 126.967 ↓ 1.0 388,522 1

Seq Scan on loan_settings_entity lse_11 (cost=0.00..16,289.61 rows=386,861 width=16) (actual time=0.006..126.967 rows=388,522 loops=1)

203. 0.002 26.736 ↓ 0.0 0 1

Nested Loop (cost=1.13..11,238.22 rows=799 width=260) (actual time=26.736..26.736 rows=0 loops=1)

  • Join Filter: (lset_2.loan_id = le_1.id)
204. 0.007 26.734 ↓ 0.0 0 1

Nested Loop (cost=0.71..10,796.33 rows=799 width=16) (actual time=26.734..26.734 rows=0 loops=1)

205. 26.698 26.698 ↑ 800.0 1 1

Index Scan using loan_settings_entity_loan_id_veritec_cond on loan_settings_entity lset_2 (cost=0.29..4,764.33 rows=800 width=8) (actual time=26.684..26.698 rows=1 loops=1)

  • Filter: (loan_sub_status_id = 56)
  • Rows Removed by Filter: 32507
206. 0.029 0.029 ↓ 0.0 0 1

Index Scan using loan_setup_entity_loan_id on loan_setup_entity lse_12 (cost=0.42..7.54 rows=1 width=8) (actual time=0.029..0.029 rows=0 loops=1)

  • Index Cond: (loan_id = lset_2.loan_id)
  • Filter: (contract_date <= CURRENT_DATE)
  • Rows Removed by Filter: 1
207. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_entity_loan_id on loan_entity le_1 (cost=0.42..0.53 rows=1 width=4) (never executed)

  • Index Cond: (id = lse_12.loan_id)
  • Filter: (deleted = 0)
208. 0.001 21.108 ↓ 0.0 0 1

Nested Loop (cost=1.13..10,639.32 rows=799 width=260) (actual time=21.108..21.108 rows=0 loops=1)

  • Join Filter: (lset_3.loan_id = le_2.id)
209. 0.006 21.107 ↓ 0.0 0 1

Nested Loop (cost=0.71..10,197.43 rows=799 width=16) (actual time=21.107..21.107 rows=0 loops=1)

210. 20.869 20.869 ↑ 13.8 58 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lset_3 (cost=0.29..4,165.43 rows=800 width=8) (actual time=20.650..20.869 rows=58 loops=1)

  • Filter: (loan_sub_status_id = 14)
  • Rows Removed by Filter: 32449
211. 0.232 0.232 ↓ 0.0 0 58

Index Scan using loan_setup_entity_loan_id on loan_setup_entity lse_13 (cost=0.42..7.54 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=58)

  • Index Cond: (loan_id = lset_3.loan_id)
  • Filter: (contract_date <= CURRENT_DATE)
  • Rows Removed by Filter: 1
212. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_entity_loan_id on loan_entity le_2 (cost=0.42..0.53 rows=1 width=4) (never executed)

  • Index Cond: (id = lse_13.loan_id)
  • Filter: (deleted = 0)
213. 0.001 52.924 ↓ 0.0 0 1

Nested Loop (cost=0.84..23,734.65 rows=799 width=260) (actual time=52.924..52.924 rows=0 loops=1)

  • Join Filter: (lset_4.loan_id = le_3.id)
214. 0.002 52.923 ↓ 0.0 0 1

Nested Loop (cost=0.42..23,288.76 rows=799 width=16) (actual time=52.923..52.923 rows=0 loops=1)

215. 52.921 52.921 ↓ 0.0 0 1

Seq Scan on loan_settings_entity lset_4 (cost=0.00..17,256.76 rows=800 width=8) (actual time=52.921..52.921 rows=0 loops=1)

  • Filter: (loan_sub_status_id = 59)
  • Rows Removed by Filter: 388522
216. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_setup_entity_loan_id on loan_setup_entity lse_14 (cost=0.42..7.54 rows=1 width=8) (never executed)

  • Index Cond: (loan_id = lset_4.loan_id)
  • Filter: (contract_date <= CURRENT_DATE)
217. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_entity_loan_id on loan_entity le_3 (cost=0.42..0.53 rows=1 width=8) (never executed)

  • Index Cond: (id = lse_14.loan_id)
  • Filter: (deleted = 0)
218. 0.020 230.930 ↓ 0.0 0 1

Hash Join (cost=42,063.31..56,486.00 rows=18,538 width=238) (actual time=230.929..230.930 rows=0 loops=1)

  • Hash Cond: (lset_5.loan_status_id = lse_15.id)
219. 0.137 230.880 ↓ 0.0 0 1

Hash Join (cost=42,021.81..55,957.88 rows=18,538 width=18) (actual time=230.880..230.880 rows=0 loops=1)

  • Hash Cond: (le_4.id = lset_5.loan_id)
220. 0.009 0.009 ↑ 388,294.0 1 1

Seq Scan on loan_entity le_4 (cost=0.00..12,294.59 rows=388,294 width=4) (actual time=0.009..0.009 rows=1 loops=1)

  • Filter: (deleted = 0)
  • Rows Removed by Filter: 1
221. 0.001 230.734 ↓ 0.0 0 1

Hash (cost=41,789.95..41,789.95 rows=18,549 width=18) (actual time=230.734..230.734 rows=0 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 256kB
222. 42.343 230.733 ↓ 0.0 0 1

Hash Join (cost=17,902.50..41,789.95 rows=18,549 width=18) (actual time=230.733..230.733 rows=0 loops=1)

  • Hash Cond: (cf.loan_settings_id = lset_5.id)
223. 96.087 96.087 ↑ 1.0 139,719 1

Seq Scan on loan_settings_custom_fields cf (cost=0.00..23,177.33 rows=139,902 width=6) (actual time=0.006..96.087 rows=139,719 loops=1)

  • Filter: (esigned <> '1'::text)
  • Rows Removed by Filter: 248803
224. 18.412 92.303 ↓ 1.0 52,362 1

Hash (cost=17,256.76..17,256.76 rows=51,659 width=24) (actual time=92.303..92.303 rows=52,362 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3376kB
225. 73.891 73.891 ↓ 1.0 52,362 1

Seq Scan on loan_settings_entity lset_5 (cost=0.00..17,256.76 rows=51,659 width=24) (actual time=0.019..73.891 rows=52,362 loops=1)

  • Filter: (loan_status_id = ANY ('{3,4}'::bigint[]))
  • Rows Removed by Filter: 336160
226. 0.015 0.030 ↑ 155.6 9 1

Hash (cost=24.00..24.00 rows=1,400 width=14) (actual time=0.030..0.030 rows=9 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
227. 0.015 0.015 ↑ 155.6 9 1

Seq Scan on loan_status_entity lse_15 (cost=0.00..24.00 rows=1,400 width=14) (actual time=0.012..0.015 rows=9 loops=1)

228. 66.829 80,948.232 ↑ 1.6 9 1

Nested Loop (cost=2.24..310,186.95 rows=14 width=260) (actual time=53,430.009..80,948.232 rows=9 loops=1)

  • Join Filter: (((cfe.lastupdated >= '2019-10-08'::date) OR (pae_4.created >= '2019-10-08'::date)) AND ((cfe.lastupdated <= (pae_4.created - '00:30:00'::interval)) OR (cfe.lastupdated >= (pae_4.created + '00:30:00'::interval))))
  • Rows Removed by Join Filter: 52081
229. 82.132 80,621.048 ↓ 5.2 52,071 1

Nested Loop (cost=1.82..304,294.81 rows=10,072 width=20) (actual time=2.906..80,621.048 rows=52,071 loops=1)

230. 79.278 80,226.490 ↓ 5.2 52,071 1

Nested Loop (cost=1.40..291,358.51 rows=10,064 width=16) (actual time=2.888..80,226.490 rows=52,071 loops=1)

  • Join Filter: (lse_16.id = cfe.entity_id)
231. 55.590 686.866 ↓ 3.0 52,071 1

Nested Loop (cost=0.71..120,810.43 rows=17,121 width=20) (actual time=0.244..686.866 rows=52,071 loops=1)

232. 55.943 55.943 ↓ 1.0 52,303 1

Index Scan using loan_settings_entity_id_cond on loan_settings_entity lse_16 (cost=0.29..5,182.25 rows=51,362 width=16) (actual time=0.026..55.943 rows=52,303 loops=1)

233. 575.333 575.333 ↑ 1.0 1 52,303

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cf_1 (cost=0.42..2.25 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=52,303)

  • Index Cond: (loan_settings_id = lse_16.id)
  • Filter: (dw_reporting_meta.text_to_date(origination_date) < '2019-10-08'::date)
  • Rows Removed by Filter: 0
234. 79,460.346 79,460.346 ↑ 2.0 1 52,071

Index Scan using custom_field__entity_entity_id_entity_type_custom_field_id_v2 on custom_field__entity cfe (cost=0.69..9.94 rows=2 width=12) (actual time=1.469..1.526 rows=1 loops=52,071)

  • Index Cond: ((entity_id = cf_1.loan_settings_id) AND (entity_type = 'Entity.LoanSettings'::bpchar) AND (custom_field_id = 86))
235. 312.426 312.426 ↑ 1.0 1 52,071

Index Scan using loan__customer_loan_id on loan__customer lc_5 (cost=0.42..1.28 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=52,071)

  • Index Cond: (loan_id = lse_16.loan_id)
236. 260.355 260.355 ↑ 1.0 1 52,071

Index Scan using payment_account_entity_cond on payment_account_entity pae_4 (cost=0.42..0.56 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=52,071)

  • Index Cond: (entity_id = lc_5.customer_id)
  • Filter: ((is_primary = 1) AND (deleted = 0))
  • Rows Removed by Filter: 1
237. 44.396 37,355.213 ↑ 2.0 7 1

Nested Loop (cost=2.24..310,067.25 rows=14 width=260) (actual time=37,352.834..37,355.213 rows=7 loops=1)

  • Join Filter: (((cfe_1.lastupdated >= '2019-10-08'::date) OR (pae_5.created >= '2019-10-08'::date)) AND ((cfe_1.lastupdated <= (pae_5.created - '00:30:00'::interval)) OR (cfe_1.lastupdated >= (pae_5.created + '00:30:00'::interval))))
  • Rows Removed by Join Filter: 52083
238. 99.880 37,050.462 ↓ 5.2 52,071 1

Nested Loop (cost=1.82..304,212.54 rows=10,008 width=20) (actual time=1.263..37,050.462 rows=52,071 loops=1)

239. 65.111 36,794.369 ↓ 5.2 52,071 1

Nested Loop (cost=1.40..291,358.51 rows=10,000 width=16) (actual time=1.251..36,794.369 rows=52,071 loops=1)

  • Join Filter: (lse_17.id = cfe_1.entity_id)
240. 69.439 591.984 ↓ 3.0 52,071 1

Nested Loop (cost=0.71..120,810.43 rows=17,121 width=20) (actual time=0.039..591.984 rows=52,071 loops=1)

241. 51.818 51.818 ↓ 1.0 52,303 1

Index Scan using loan_settings_entity_id_cond on loan_settings_entity lse_17 (cost=0.29..5,182.25 rows=51,362 width=16) (actual time=0.015..51.818 rows=52,303 loops=1)

242. 470.727 470.727 ↑ 1.0 1 52,303

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cf_2 (cost=0.42..2.25 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=52,303)

  • Index Cond: (loan_settings_id = lse_17.id)
  • Filter: (dw_reporting_meta.text_to_date(origination_date) < '2019-10-08'::date)
  • Rows Removed by Filter: 0
243. 36,137.274 36,137.274 ↑ 2.0 1 52,071

Index Scan using custom_field__entity_entity_id_entity_type_custom_field_id_v2 on custom_field__entity cfe_1 (cost=0.69..9.94 rows=2 width=12) (actual time=0.645..0.694 rows=1 loops=52,071)

  • Index Cond: ((entity_id = cf_2.loan_settings_id) AND (entity_type = 'Entity.LoanSettings'::bpchar) AND (custom_field_id = 36))
244. 156.213 156.213 ↑ 1.0 1 52,071

Index Scan using loan__customer_loan_id on loan__customer lc_6 (cost=0.42..1.28 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=52,071)

  • Index Cond: (loan_id = lse_17.loan_id)
245. 260.355 260.355 ↑ 1.0 1 52,071

Index Scan using payment_account_entity_cond on payment_account_entity pae_5 (cost=0.42..0.56 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=52,071)

  • Index Cond: (entity_id = lc_6.customer_id)
  • Filter: ((is_primary = 1) AND (deleted = 0))
  • Rows Removed by Filter: 1
246. 0.653 1,001.548 ↓ 0.0 0 1

Nested Loop (cost=1.42..16,881.53 rows=1 width=212) (actual time=1,001.547..1,001.548 rows=0 loops=1)

  • Join Filter: ((cfe_2.custom_field_value = ''::text) OR (cfe2.custom_field_value = ''::text))
  • Rows Removed by Join Filter: 670
247. 1.460 84.935 ↓ 9.2 680 1

Nested Loop (cost=0.73..16,084.86 rows=74 width=36) (actual time=0.870..84.935 rows=680 loops=1)

248. 81.165 81.165 ↓ 2.1 1,155 1

Index Scan using custom_field__entity_last_updated on custom_field__entity cfe_2 (cost=0.44..12,696.33 rows=556 width=20) (actual time=0.860..81.165 rows=1,155 loops=1)

  • Index Cond: (lastupdated > (now() - '4 days'::interval))
  • Filter: ((entity_type = 'Entity.LoanSettings'::bpchar) AND (custom_field_id = 36))
  • Rows Removed by Filter: 52850
249. 2.310 2.310 ↑ 1.0 1 1,155

Index Scan using loan_settings_entity_id_cond on loan_settings_entity lse_18 (cost=0.29..6.08 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,155)

  • Index Cond: (id = cfe_2.entity_id)
250. 915.960 915.960 ↑ 1.0 1 680

Index Scan using custom_field__entity_entity_id_entity_type_custom_field_id_v2 on custom_field__entity cfe2 (cost=0.69..10.75 rows=1 width=20) (actual time=1.312..1.347 rows=1 loops=680)

  • Index Cond: ((entity_id = lse_18.id) AND (entity_type = 'Entity.LoanSettings'::bpchar) AND (custom_field_id = 86))
  • Filter: (lastupdated > (now() - '4 days'::interval))
  • Rows Removed by Filter: 0
251. 40.668 38,228.052 ↑ 9,231.0 2 1

Merge Join (cost=212,367.20..309,115.62 rows=18,462 width=240) (actual time=10,688.705..38,228.052 rows=2 loops=1)

  • Merge Cond: (app.loan_num = lse_19.loan_id)
252. 278.084 38,051.178 ↑ 1,021.0 29 1

Nested Loop (cost=212,365.06..390,190.58 rows=29,608 width=68) (actual time=10,550.375..38,051.178 rows=29 loops=1)

  • Join Filter: (lower(ce_3.email) <> lower(ua.username))
  • Rows Removed by Join Filter: 128956
253. 255.938 8,751.469 ↓ 5.0 128,985 1

Nested Loop (cost=212,364.63..252,934.44 rows=25,798 width=46) (actual time=1,653.929..8,751.469 rows=128,985 loops=1)

254. 201.533 1,917.296 ↓ 5.0 128,985 1

Merge Join (cost=212,364.21..235,219.47 rows=25,798 width=20) (actual time=1,644.802..1,917.296 rows=128,985 loops=1)

  • Merge Cond: (lc_7.loan_id = app.loan_num)
255. 183.989 183.989 ↓ 1.0 388,528 1

Index Scan using loan__customer_loan_id on loan__customer lc_7 (cost=0.42..21,502.26 rows=387,308 width=8) (actual time=0.021..183.989 rows=388,528 loops=1)

256. 183.240 1,531.774 ↓ 2.0 128,986 1

Sort (cost=212,362.39..212,524.60 rows=64,885 width=12) (actual time=1,482.749..1,531.774 rows=128,986 loops=1)

  • Sort Key: app.loan_num
  • Sort Method: external sort Disk: 3792kB
257. 1,348.534 1,348.534 ↓ 2.0 128,987 1

Seq Scan on fact_application app (cost=0.00..207,176.27 rows=64,885 width=12) (actual time=0.030..1,348.534 rows=128,987 loops=1)

  • Filter: ((applicant_transaction_datetime >= '2018-10-01 00:00:00'::timestamp without time zone) AND (lms = 'LP'::text) AND (brand = 'balance'::text))
  • Rows Removed by Filter: 933295
258. 6,578.235 6,578.235 ↑ 1.0 1 128,985

Index Scan using customer_entity_id on customer_entity ce_3 (cost=0.42..0.68 rows=1 width=34) (actual time=0.051..0.051 rows=1 loops=128,985)

  • Index Cond: (id = lc_7.customer_id)
259. 29,021.625 29,021.625 ↑ 2.0 1 128,985

Index Scan using user_account_user_account_id on user_account ua (cost=0.43..5.29 rows=2 width=38) (actual time=0.071..0.225 rows=1 loops=128,985)

  • Index Cond: (user_account_id = app.user_account_id)
  • Filter: ((brand = 'balance'::text) AND (now() <@ (asserted)::tstzrange) AND (now() <@ (effective)::tstzrange))
  • Rows Removed by Filter: 6
260. 136.206 136.206 ↑ 1.3 184,032 1

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_19 (cost=0.42..25,267.30 rows=241,234 width=8) (actual time=0.020..136.206 rows=184,032 loops=1)

  • Filter: (loan_status_id = ANY ('{4,5}'::bigint[]))
  • Rows Removed by Filter: 116275
261. 0.003 63.754 ↓ 0.0 0 1

Hash Join (cost=2.31..18,804.82 rows=4,001 width=240) (actual time=63.754..63.754 rows=0 loops=1)

  • Hash Cond: (lse_20.loan_sub_status_id = lsse_4.id)
262. 63.751 63.751 ↓ 0.0 0 1

Seq Scan on loan_settings_entity lse_20 (cost=0.00..18,707.49 rows=4,001 width=24) (actual time=63.751..63.751 rows=0 loops=1)

  • Filter: (loan_sub_status_id = ANY ('{2,4,29,31,42}'::bigint[]))
  • Rows Removed by Filter: 388522
263. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.58..1.58 rows=58 width=16) (never executed)

264. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_sub_status_entity lsse_4 (cost=0.00..1.58 rows=58 width=16) (never executed)

265. 0.028 7,185.188 ↑ 1,138.0 2 1

Hash Join (cost=60,723.64..114,993.17 rows=2,276 width=189) (actual time=7,136.169..7,185.188 rows=2 loops=1)

  • Hash Cond: (lp_2.portfolio_id = pe_13.id)
266. 0.012 7,185.107 ↑ 1,138.0 2 1

Hash Join (cost=60,721.31..114,948.17 rows=2,276 width=29) (actual time=7,136.093..7,185.107 rows=2 loops=1)

  • Hash Cond: (lse_21.loan_sub_status_id = lsse_5.id)
267. 279.547 7,185.055 ↑ 1,138.0 2 1

Hash Join (cost=60,719.01..114,914.57 rows=2,276 width=25) (actual time=7,136.045..7,185.055 rows=2 loops=1)

  • Hash Cond: (lse_21.id = lscf_6.loan_settings_id)
  • Join Filter: (CASE WHEN ((lscf_6.payment_type = 'DirectPay'::text) AND (lp_2.portfolio_id = 23)) THEN 1 WHEN ((lscf_6.payment_type <> 'DirectPay'::text) AND (lp_2.portfolio_id <> 23)) THEN 1 ELSE 0 END = 0)
  • Rows Removed by Join Filter: 388489
268. 358.261 6,563.993 ↑ 1.2 388,491 1

Hash Join (cost=31,742.42..76,613.83 rows=455,297 width=28) (actual time=492.136..6,563.993 rows=388,491 loops=1)

  • Hash Cond: (lp_2.loan_id = lse_21.loan_id)
269. 5,718.060 5,908.958 ↑ 1.2 388,494 1

Bitmap Heap Scan on loan__portfolio lp_2 (cost=8,350.05..41,029.52 rows=464,526 width=8) (actual time=193.973..5,908.958 rows=388,494 loops=1)

  • Recheck Cond: (portfolio_id = ANY ('{10,11,12,17,23,38,40,41,42,44,45,47,48,49}'::integer[]))
  • Heap Blocks: exact=19905
270. 190.898 190.898 ↑ 1.2 388,494 1

Bitmap Index Scan on loan__portfolio_portfolio_id (cost=0.00..8,233.92 rows=464,526 width=0) (actual time=190.898..190.898 rows=388,494 loops=1)

  • Index Cond: (portfolio_id = ANY ('{10,11,12,17,23,38,40,41,42,44,45,47,48,49}'::integer[]))
271. 152.331 296.774 ↓ 1.0 388,522 1

Hash (cost=16,289.61..16,289.61 rows=386,861 width=24) (actual time=296.774..296.774 rows=388,522 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3175kB
272. 144.443 144.443 ↓ 1.0 388,522 1

Seq Scan on loan_settings_entity lse_21 (cost=0.00..16,289.61 rows=386,861 width=24) (actual time=0.008..144.443 rows=388,522 loops=1)

273. 145.134 341.515 ↑ 1.0 388,522 1

Hash (cost=22,203.26..22,203.26 rows=389,626 width=9) (actual time=341.515..341.515 rows=388,522 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3042kB
274. 196.381 196.381 ↑ 1.0 388,522 1

Seq Scan on loan_settings_custom_fields lscf_6 (cost=0.00..22,203.26 rows=389,626 width=9) (actual time=0.004..196.381 rows=388,522 loops=1)

275. 0.021 0.040 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.039..0.040 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
276. 0.019 0.019 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_5 (cost=0.00..1.58 rows=58 width=16) (actual time=0.004..0.019 rows=58 loops=1)

277. 0.024 0.053 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=12) (actual time=0.053..0.053 rows=59 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
278. 0.029 0.029 ↑ 1.0 59 1

Seq Scan on portfolio_entity pe_13 (cost=0.00..1.59 rows=59 width=12) (actual time=0.013..0.029 rows=59 loops=1)

279. 0.036 2,780.002 ↑ 1.0 6 1

Nested Loop Anti Join (cost=2.15..18,217.10 rows=6 width=231) (actual time=2,466.885..2,780.002 rows=6 loops=1)

280. 0.029 2,571.916 ↑ 1.0 6 1

Nested Loop (cost=1.71..15,641.22 rows=6 width=39) (actual time=2,427.157..2,571.916 rows=6 loops=1)

281. 0.021 2,571.839 ↑ 1.0 6 1

Nested Loop (cost=1.29..15,592.56 rows=6 width=44) (actual time=2,427.139..2,571.839 rows=6 loops=1)

282. 14.211 2,544.074 ↑ 7.8 6 1

Merge Join (cost=0.87..15,210.30 rows=47 width=36) (actual time=2,411.748..2,544.074 rows=6 loops=1)

  • Merge Cond: (lsa1.loan_id = lsa2.loan_id)
  • Join Filter: (((lsa1.last_payment_date < lsa2.date) OR (lsa1.last_payment_date IS NULL)) AND (lsa1.payoff <> lsa2.payoff))
  • Rows Removed by Join Filter: 10911
283. 2,483.441 2,483.441 ↓ 2.6 10,918 1

Index Scan using loan_status_archive_date_loan_id_called_off on loan_status_archive lsa1 (cost=0.43..7,594.12 rows=4,184 width=18) (actual time=3.315..2,483.441 rows=10,918 loops=1)

  • Index Cond: (date = '2019-10-08'::date)
284. 46.422 46.422 ↓ 2.6 10,922 1

Index Scan using loan_status_archive_date_loan_id_called_off on loan_status_archive lsa2 (cost=0.43..7,594.12 rows=4,184 width=22) (actual time=1.340..46.422 rows=10,922 loops=1)

  • Index Cond: (date = CURRENT_DATE)
285. 27.744 27.744 ↑ 1.0 1 6

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_22 (cost=0.42..8.12 rows=1 width=16) (actual time=4.623..4.624 rows=1 loops=6)

  • Index Cond: (loan_id = lsa1.loan_id)
  • Filter: (loan_status_id = 4)
286. 0.048 0.048 ↑ 1.0 1 6

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_7 (cost=0.42..8.11 rows=1 width=7) (actual time=0.008..0.008 rows=1 loops=6)

  • Index Cond: (loan_settings_id = lse_22.id)
287. 208.050 208.050 ↓ 0.0 0 6

Index Scan using loan_tx_entity_id1 on loan_tx tx_1 (cost=0.43..368.02 rows=1 width=4) (actual time=34.675..34.675 rows=0 loops=6)

  • Index Cond: (entity_id = lsa1.loan_id)
  • Filter: ((type = ANY ('{credit,advancement}'::bpchar[])) AND (date >= '2019-10-08'::date))
  • Rows Removed by Filter: 16
288. 0.001 1,494.885 ↓ 0.0 0 1

Nested Loop (cost=18,354.34..35,391.45 rows=1 width=220) (actual time=1,494.885..1,494.885 rows=0 loops=1)

  • Join Filter: (lse2.loan_sub_status_id = lsse2.id)
289. 0.001 1,494.884 ↓ 0.0 0 1

Nested Loop (cost=18,354.34..35,389.13 rows=1 width=40) (actual time=1,494.884..1,494.884 rows=0 loops=1)

  • Join Filter: (lse1.loan_sub_status_id = lsse1.id)
290. 0.165 1,494.883 ↓ 0.0 0 1

Nested Loop (cost=18,354.34..35,386.83 rows=1 width=36) (actual time=1,494.883..1,494.883 rows=0 loops=1)

291. 0.265 1,414.110 ↑ 3.2 88 1

Nested Loop (cost=18,353.92..35,218.32 rows=285 width=24) (actual time=122.091..1,414.110 rows=88 loops=1)

  • Join Filter: (lse1.loan_id = le_5.id)
292. 2.132 1,273.925 ↑ 3.2 88 1

Nested Loop (cost=18,353.50..35,080.24 rows=285 width=24) (actual time=112.339..1,273.925 rows=88 loops=1)

293. 4.138 1,179.957 ↓ 1.3 2,551 1

Nested Loop (cost=18,353.07..34,092.22 rows=1,947 width=4) (actual time=53.662..1,179.957 rows=2,551 loops=1)

294. 3.021 55.930 ↓ 1.3 2,551 1

GroupAggregate (cost=18,352.65..18,386.86 rows=1,947 width=12) (actual time=51.714..55.930 rows=2,551 loops=1)

  • Group Key: application.user_account_id
295. 2.547 52.909 ↓ 1.4 2,687 1

Sort (cost=18,352.65..18,357.56 rows=1,966 width=12) (actual time=51.706..52.909 rows=2,687 loops=1)

  • Sort Key: application.user_account_id
  • Sort Method: quicksort Memory: 222kB
296. 50.362 50.362 ↓ 1.4 2,687 1

Index Scan using application_offer_refinance_ind on application (cost=0.29..18,245.10 rows=1,966 width=12) (actual time=2.102..50.362 rows=2,687 loops=1)

  • Filter: ((now() <@ (asserted)::tstzrange) AND (now() <@ (effective)::tstzrange))
  • Rows Removed by Filter: 15116
297. 1,119.889 1,119.889 ↑ 1.0 1 2,551

Index Scan using fact_application_bsf_application_id on fact_application fa (cost=0.43..8.05 rows=1 width=12) (actual time=0.436..0.439 rows=1 loops=2,551)

  • Index Cond: (bsf_application_id = (max(application.application_id)))
298. 91.836 91.836 ↓ 0.0 0 2,551

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse1 (cost=0.42..0.50 rows=1 width=20) (actual time=0.036..0.036 rows=0 loops=2,551)

  • Index Cond: (loan_id = fa.loan_num)
  • Filter: (loan_sub_status_id = ANY ('{9,10,11,38,41}'::bigint[]))
  • Rows Removed by Filter: 1
299. 139.920 139.920 ↑ 1.0 1 88

Index Scan using loan_entity_loan_id on loan_entity le_5 (cost=0.42..0.47 rows=1 width=8) (actual time=1.589..1.590 rows=1 loops=88)

  • Index Cond: (id = fa.loan_num)
300. 80.608 80.608 ↓ 0.0 0 88

Index Scan using loan_settings_entity_idx_cond4 on loan_settings_entity lse2 (cost=0.42..0.58 rows=1 width=16) (actual time=0.916..0.916 rows=0 loops=88)

  • Index Cond: (loan_id = le_5.linked_loan)
  • Filter: (loan_sub_status_id = ANY ('{57,58}'::bigint[]))
  • Rows Removed by Filter: 1
301. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_sub_status_entity lsse1 (cost=0.00..1.58 rows=58 width=16) (never executed)

302. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_sub_status_entity lsse2 (cost=0.00..1.58 rows=58 width=16) (never executed)

303. 0.001 718.956 ↓ 0.0 0 1

Nested Loop (cost=19,084.51..35,811.57 rows=1 width=217) (actual time=718.956..718.956 rows=0 loops=1)

304. 0.002 718.955 ↓ 0.0 0 1

Nested Loop (cost=19,084.51..35,787.42 rows=1 width=57) (actual time=718.955..718.955 rows=0 loops=1)

  • Join Filter: (lse2_1.loan_sub_status_id = lsse_6.id)
305. 0.001 718.953 ↓ 0.0 0 1

Nested Loop (cost=19,084.51..35,785.12 rows=1 width=53) (actual time=718.953..718.953 rows=0 loops=1)

306. 1.967 718.952 ↓ 0.0 0 1

Nested Loop (cost=19,084.08..35,784.53 rows=1 width=45) (actual time=718.952..718.952 rows=0 loops=1)

307. 2.582 707.221 ↓ 2,441.0 2,441 1

Hash Join (cost=19,083.66..35,784.02 rows=1 width=53) (actual time=45.839..707.221 rows=2,441 loops=1)

  • Hash Cond: (le_6.linked_loan = lce.entity_id)
308. 3.049 689.493 ↓ 3.3 2,531 1

Nested Loop (cost=18,353.50..35,030.65 rows=773 width=12) (actual time=30.664..689.493 rows=2,531 loops=1)

309. 2.732 46.143 ↓ 1.3 2,551 1

Nested Loop (cost=18,353.07..34,092.22 rows=1,947 width=4) (actual time=30.040..46.143 rows=2,551 loops=1)

310. 2.262 33.207 ↓ 1.3 2,551 1

GroupAggregate (cost=18,352.65..18,386.86 rows=1,947 width=12) (actual time=30.017..33.207 rows=2,551 loops=1)

  • Group Key: application_1.user_account_id
311. 2.872 30.945 ↓ 1.4 2,687 1

Sort (cost=18,352.65..18,357.56 rows=1,966 width=12) (actual time=30.009..30.945 rows=2,687 loops=1)

  • Sort Key: application_1.user_account_id
  • Sort Method: quicksort Memory: 222kB
312. 28.073 28.073 ↓ 1.4 2,687 1

Index Scan using application_offer_refinance_ind on application application_1 (cost=0.29..18,245.10 rows=1,966 width=12) (actual time=0.032..28.073 rows=2,687 loops=1)

  • Filter: ((now() <@ (asserted)::tstzrange) AND (now() <@ (effective)::tstzrange))
  • Rows Removed by Filter: 15116
313. 10.204 10.204 ↑ 1.0 1 2,551

Index Scan using fact_application_bsf_application_id on fact_application fa_1 (cost=0.43..8.05 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=2,551)

  • Index Cond: (bsf_application_id = (max(application_1.application_id)))
314. 640.301 640.301 ↑ 1.0 1 2,551

Index Scan using loan_entity_loan_id on loan_entity le_6 (cost=0.42..0.47 rows=1 width=8) (actual time=0.251..0.251 rows=1 loops=2,551)

  • Index Cond: (id = fa_1.loan_num)
315. 1.505 15.146 ↓ 1.1 2,444 1

Hash (cost=701.84..701.84 rows=2,266 width=41) (actual time=15.146..15.146 rows=2,444 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 223kB
316. 13.641 13.641 ↓ 1.1 2,444 1

Seq Scan on loan_credit_entity lce (cost=0.00..701.84 rows=2,266 width=41) (actual time=8.833..13.641 rows=2,444 loops=1)

  • Filter: (((entity_type)::text = 'Entity.Loan'::text) AND (category = 13) AND (deleted = 0))
  • Rows Removed by Filter: 18404
317. 9.764 9.764 ↓ 0.0 0 2,441

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse1_1 (cost=0.42..0.50 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=2,441)

  • Index Cond: (loan_id = fa_1.loan_num)
  • Filter: (loan_sub_status_id = ANY ('{9,10,11,38,41}'::bigint[]))
  • Rows Removed by Filter: 1
318. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse2_1 (cost=0.42..0.58 rows=1 width=16) (never executed)

  • Index Cond: (loan_id = le_6.linked_loan)
319. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_sub_status_entity lsse_6 (cost=0.00..1.58 rows=58 width=16) (never executed)

320. 0.000 0.000 ↓ 0.0 0

Seq Scan on credit_category_entity cce (cost=0.00..24.12 rows=1 width=20) (never executed)

  • Filter: (id = 13)
321. 0.001 1,008.438 ↓ 0.0 0 1

Nested Loop (cost=2.56..23,473.41 rows=1 width=163) (actual time=1,008.438..1,008.438 rows=0 loops=1)

  • Join Filter: (lp3.portfolio_id = pe3.id)
322. 0.000 1,008.437 ↓ 0.0 0 1

Nested Loop (cost=2.56..23,471.62 rows=1 width=67) (actual time=1,008.437..1,008.437 rows=0 loops=1)

  • Join Filter: (lp2.portfolio_id = pe2.id)
323. 0.002 1,008.437 ↓ 0.0 0 1

Nested Loop (cost=2.56..23,469.78 rows=1 width=63) (actual time=1,008.436..1,008.437 rows=0 loops=1)

  • Join Filter: ((cf_3.owner <> 'SunUp Financial'::text) OR (cf_3.servicer <> 'SunUp Financial Services'::text) OR (lp2.portfolio_id <> 1) OR (lp3.portfolio_id <> 18))
324. 0.001 1,008.435 ↓ 0.0 0 1

Nested Loop (cost=2.13..23,469.12 rows=1 width=36) (actual time=1,008.435..1,008.435 rows=0 loops=1)

  • Join Filter: (le_7.linked_loan = lp3.loan_id)
325. 0.001 1,008.434 ↓ 0.0 0 1

Nested Loop (cost=1.70..23,468.44 rows=1 width=40) (actual time=1,008.434..1,008.434 rows=0 loops=1)

326. 0.000 1,008.433 ↓ 0.0 0 1

Nested Loop (cost=1.27..23,467.53 rows=1 width=32) (actual time=1,008.433..1,008.433 rows=0 loops=1)

  • Join Filter: (lse2_2.loan_sub_status_id = lsse_7.id)
327. 0.001 1,008.433 ↓ 0.0 0 1

Nested Loop (cost=1.27..23,465.23 rows=1 width=28) (actual time=1,008.432..1,008.433 rows=0 loops=1)

  • Join Filter: (lse_23.loan_id = lp_3.loan_id)
328. 3.487 1,008.432 ↓ 0.0 0 1

Nested Loop (cost=0.84..23,460.91 rows=5 width=40) (actual time=1,008.431..1,008.432 rows=0 loops=1)

329. 4.667 1,004.945 ↓ 3.5 2,829 1

Nested Loop (cost=0.42..22,988.76 rows=800 width=16) (actual time=2.131..1,004.945 rows=2,829 loops=1)

330. 66.708 66.708 ↓ 3.5 2,829 1

Seq Scan on loan_settings_entity lse_23 (cost=0.00..17,256.76 rows=800 width=8) (actual time=0.069..66.708 rows=2,829 loops=1)

  • Filter: (loan_sub_status_id = 41)
  • Rows Removed by Filter: 385693
331. 933.570 933.570 ↑ 1.0 1 2,829

Index Scan using loan_entity_loan_id on loan_entity le_7 (cost=0.42..7.16 rows=1 width=8) (actual time=0.330..0.330 rows=1 loops=2,829)

  • Index Cond: (id = lse_23.loan_id)
332. 0.000 0.000 ↓ 0.0 0 2,829

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse2_2 (cost=0.42..0.58 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=2,829)

  • Index Cond: (loan_id = le_7.linked_loan)
333. 0.000 0.000 ↓ 0.0 0

Index Scan using loan__portfolio_loan_id on loan__portfolio lp_3 (cost=0.43..0.85 rows=1 width=4) (never executed)

  • Index Cond: (loan_id = le_7.id)
  • Filter: (portfolio_id = 55)
334. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_sub_status_entity lsse_7 (cost=0.00..1.58 rows=58 width=16) (never executed)

335. 0.000 0.000 ↓ 0.0 0

Index Scan using loan__portfolio_loan_id on loan__portfolio lp2 (cost=0.43..0.83 rows=7 width=8) (never executed)

  • Index Cond: (loan_id = le_7.linked_loan)
336. 0.000 0.000 ↓ 0.0 0

Index Scan using loan__portfolio_loan_id on loan__portfolio lp3 (cost=0.43..0.59 rows=7 width=8) (never executed)

  • Index Cond: (loan_id = lp2.loan_id)
337. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cf_3 (cost=0.42..0.65 rows=1 width=39) (never executed)

  • Index Cond: (loan_settings_id = lse2_2.id)
338. 0.000 0.000 ↓ 0.0 0

Seq Scan on portfolio_entity pe2 (cost=0.00..1.74 rows=8 width=12) (never executed)

  • Filter: (category_id = 1)
339. 0.000 0.000 ↓ 0.0 0

Seq Scan on portfolio_entity pe3 (cost=0.00..1.74 rows=4 width=12) (never executed)

  • Filter: (category_id = 7)
340. 181.032 1,261.371 ↑ 1.0 1 1

Nested Loop Anti Join (cost=18,355.19..43,217.50 rows=1 width=189) (actual time=255.342..1,261.371 rows=1 loops=1)

  • Join Filter: (cie.entity_id = lse2_3.loan_id)
  • Rows Removed by Join Filter: 856840
341. 0.191 511.316 ↓ 13.0 13 1

Nested Loop (cost=18,355.19..35,501.19 rows=1 width=43) (actual time=23.947..511.316 rows=13 loops=1)

  • Join Filter: ((lse2_3.autopay_enabled = 0) OR (lp2_1.portfolio_id = 23) OR (cf_4.payment_type = 'DirectPay'::text))
  • Rows Removed by Join Filter: 65
342. 1.369 510.579 ↓ 78.0 78 1

Nested Loop (cost=18,354.77..35,494.38 rows=1 width=50) (actual time=23.933..510.579 rows=78 loops=1)

  • Join Filter: (lp2_1.portfolio_id = pe_14.id)
  • Rows Removed by Join Filter: 4524
343. 1.356 508.040 ↓ 78.0 78 1

Nested Loop (cost=18,354.77..35,492.06 rows=1 width=42) (actual time=23.914..508.040 rows=78 loops=1)

  • Join Filter: (lse1_2.loan_sub_status_id = lsse_8.id)
  • Rows Removed by Join Filter: 4446
344. 0.252 505.514 ↓ 78.0 78 1

Nested Loop (cost=18,354.77..35,489.75 rows=1 width=38) (actual time=23.902..505.514 rows=78 loops=1)

345. 0.116 96.404 ↓ 41.5 83 1

Nested Loop (cost=18,354.35..35,466.31 rows=2 width=28) (actual time=20.503..96.404 rows=83 loops=1)

346. 0.210 45.072 ↑ 3.2 88 1

Nested Loop (cost=18,353.92..35,218.32 rows=285 width=20) (actual time=18.238..45.072 rows=88 loops=1)

  • Join Filter: (lse1_2.loan_id = le_8.id)
347. 0.673 44.422 ↑ 3.2 88 1

Nested Loop (cost=18,353.50..35,080.24 rows=285 width=20) (actual time=18.227..44.422 rows=88 loops=1)

348. 2.664 33.545 ↓ 1.3 2,551 1

Nested Loop (cost=18,353.07..34,092.22 rows=1,947 width=4) (actual time=17.807..33.545 rows=2,551 loops=1)

349. 1.998 20.677 ↓ 1.3 2,551 1

GroupAggregate (cost=18,352.65..18,386.86 rows=1,947 width=12) (actual time=17.791..20.677 rows=2,551 loops=1)

  • Group Key: application_2.user_account_id
350. 2.048 18.679 ↓ 1.4 2,687 1

Sort (cost=18,352.65..18,357.56 rows=1,966 width=12) (actual time=17.784..18.679 rows=2,687 loops=1)

  • Sort Key: application_2.user_account_id
  • Sort Method: quicksort Memory: 222kB
351. 16.631 16.631 ↓ 1.4 2,687 1

Index Scan using application_offer_refinance_ind on application application_2 (cost=0.29..18,245.10 rows=1,966 width=12) (actual time=0.024..16.631 rows=2,687 loops=1)

  • Filter: ((CURRENT_TIMESTAMP <@ (asserted)::tstzrange) AND (CURRENT_TIMESTAMP <@ (effective)::tstzrange))
  • Rows Removed by Filter: 15116
352. 10.204 10.204 ↑ 1.0 1 2,551

Index Scan using fact_application_bsf_application_id on fact_application fa_2 (cost=0.43..8.05 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=2,551)

  • Index Cond: (bsf_application_id = (max(application_2.application_id)))
353. 10.204 10.204 ↓ 0.0 0 2,551

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse1_2 (cost=0.42..0.50 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=2,551)

  • Index Cond: (loan_id = fa_2.loan_num)
  • Filter: (loan_sub_status_id = ANY ('{9,10,11,38,41}'::bigint[]))
  • Rows Removed by Filter: 1
354. 0.440 0.440 ↑ 1.0 1 88

Index Scan using loan_entity_loan_id on loan_entity le_8 (cost=0.42..0.47 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=88)

  • Index Cond: (id = fa_2.loan_num)
355. 51.216 51.216 ↑ 1.0 1 88

Index Scan using loan__portfolio_loan_id on loan__portfolio lp2_1 (cost=0.43..0.86 rows=1 width=8) (actual time=0.580..0.582 rows=1 loops=88)

  • Index Cond: (loan_id = le_8.linked_loan)
  • Filter: (portfolio_id = ANY ('{10,17,23}'::integer[]))
  • Rows Removed by Filter: 6
356. 3.652 408.858 ↑ 1.0 1 83

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse2_3 (cost=0.42..11.71 rows=1 width=18) (actual time=4.925..4.926 rows=1 loops=83)

  • Index Cond: (loan_id = le_8.linked_loan)
  • Filter: ((SubPlan 1) IS NULL)
  • Rows Removed by Filter: 0
357.          

SubPlan (forIndex Scan)

358. 405.206 405.206 ↓ 0.0 0 83

Index Scan using payment_entity_entity_id on payment_entity pe_25 (cost=0.43..11.13 rows=1 width=4) (actual time=4.804..4.882 rows=0 loops=83)

  • Index Cond: (entity_id = lse2_3.loan_id)
  • Filter: (((reverse_reason)::text = 'payment.reverse.nachaErrorCode'::text) AND (payment_method_id = 4) AND (""left""(nacha_return_code, 3) <> ALL ('{R01,R09}'::text[])))
  • Rows Removed by Filter: 14
359. 1.170 1.170 ↑ 1.0 58 78

Seq Scan on loan_sub_status_entity lsse_8 (cost=0.00..1.58 rows=58 width=16) (actual time=0.002..0.015 rows=58 loops=78)

360. 1.170 1.170 ↑ 1.0 59 78

Seq Scan on portfolio_entity pe_14 (cost=0.00..1.59 rows=59 width=12) (actual time=0.002..0.015 rows=59 loops=78)

361. 0.546 0.546 ↑ 1.0 1 78

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cf_4 (cost=0.42..6.79 rows=1 width=9) (actual time=0.007..0.007 rows=1 loops=78)

  • Index Cond: (loan_settings_id = lse2_3.id)
362. 569.023 569.023 ↑ 1.1 65,912 13

Seq Scan on checklist_item__entity cie (cost=0.00..6,845.83 rows=69,637 width=4) (actual time=0.166..43.771 rows=65,912 loops=13)

  • Filter: ((checklist_item_id = ANY ('{6,15}'::integer[])) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 91909
363. 0.173 682.716 ↓ 1.8 24 1

Nested Loop (cost=17,280.77..30,117.34 rows=13 width=240) (actual time=466.376..682.716 rows=24 loops=1)

  • Join Filter: (pe_15.reverse_date >= (oa.application_created_at)::date)
  • Rows Removed by Join Filter: 291
364. 2.177 658.876 ↓ 2.3 23 1

Nested Loop (cost=17,280.34..30,090.07 rows=10 width=70) (actual time=464.366..658.876 rows=23 loops=1)

  • Join Filter: (lse_24.loan_id = pe_15.entity_id)
365. 5.099 536.894 ↓ 349.3 2,445 1

Nested Loop (cost=17,279.92..30,080.55 rows=7 width=32) (actual time=241.166..536.894 rows=2,445 loops=1)

366. 1.600 287.295 ↓ 407.5 2,445 1

Hash Join (cost=17,279.49..30,065.46 rows=6 width=36) (actual time=237.884..287.295 rows=2,445 loops=1)

  • Hash Cond: (lse_24.loan_sub_status_id = lsse_9.id)
367. 2.644 285.651 ↓ 407.5 2,445 1

Nested Loop (cost=17,277.19..30,063.07 rows=6 width=32) (actual time=237.803..285.651 rows=2,445 loops=1)

368. 76.644 275.309 ↓ 256.6 2,566 1

Hash Join (cost=17,276.76..30,057.11 rows=10 width=24) (actual time=237.772..275.309 rows=2,566 loops=1)

  • Hash Cond: (le_9.linked_loan = lse_24.loan_id)
369. 139.063 139.063 ↑ 1.0 388,527 1

Seq Scan on loan_entity le_9 (cost=0.00..11,323.27 rows=388,527 width=8) (actual time=0.006..139.063 rows=388,527 loops=1)

370. 0.885 59.602 ↓ 1.5 2,445 1

Hash (cost=17,256.76..17,256.76 rows=1,600 width=16) (actual time=59.602..59.602 rows=2,445 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 147kB
371. 58.717 58.717 ↓ 1.5 2,445 1

Seq Scan on loan_settings_entity lse_24 (cost=0.00..17,256.76 rows=1,600 width=16) (actual time=17.571..58.717 rows=2,445 loops=1)

  • Filter: (loan_sub_status_id = ANY ('{57,58}'::bigint[]))
  • Rows Removed by Filter: 386077
372. 7.698 7.698 ↑ 1.0 1 2,566

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse2_4 (cost=0.42..0.59 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=2,566)

  • Index Cond: (loan_id = le_9.id)
  • Filter: (loan_sub_status_id <> ALL ('{9,10,11,38,41}'::bigint[]))
  • Rows Removed by Filter: 0
373. 0.025 0.044 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.044..0.044 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
374. 0.019 0.019 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_9 (cost=0.00..1.58 rows=58 width=16) (actual time=0.004..0.019 rows=58 loops=1)

375. 244.500 244.500 ↑ 1.0 1 2,445

Index Scan using fact_application_loan_num_key on fact_application fa_3 (cost=0.43..2.50 rows=1 width=12) (actual time=0.093..0.100 rows=1 loops=2,445)

  • Index Cond: (loan_num = le_9.id)
376. 119.805 119.805 ↓ 0.0 0 2,445

Index Scan using payment_entity_entity_id_cond_reverse_reason on payment_entity pe_15 (cost=0.42..1.35 rows=1 width=46) (actual time=0.048..0.049 rows=0 loops=2,445)

  • Index Cond: (entity_id = le_9.linked_loan)
377. 23.667 23.667 ↑ 2.1 14 23

Index Scan using application_application_id on application oa (cost=0.43..2.25 rows=30 width=16) (actual time=0.957..1.029 rows=14 loops=23)

  • Index Cond: (application_id = fa_3.bsf_application_id)
378. 0.010 5,591.565 ↓ 0.0 0 1

Nested Loop (cost=2.73..92,120.97 rows=3 width=240) (actual time=5,591.565..5,591.565 rows=0 loops=1)

  • Join Filter: (lsse_10.id = lse_25.loan_id)
379. 37.900 5,591.515 ↑ 22.5 2 1

Hash Join (cost=2.31..92,085.42 rows=45 width=20) (actual time=308.218..5,591.515 rows=2 loops=1)

  • Hash Cond: (lde.loan_id = lsse_10.id)
380. 5,553.533 5,553.533 ↑ 1.2 134,186 1

Seq Scan on loan_document_entity lde (cost=0.00..91,472.42 rows=162,731 width=4) (actual time=161.109..5,553.533 rows=134,186 loops=1)

  • Filter: ((filename ~~ 'marked-paid%'::text) AND (active = 1) AND (deleted = 0) AND (archived = 0))
  • Rows Removed by Filter: 1996765
381. 0.042 0.082 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.082..0.082 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
382. 0.040 0.040 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_10 (cost=0.00..1.58 rows=58 width=16) (actual time=0.012..0.040 rows=58 loops=1)

383. 0.040 0.040 ↓ 0.0 0 2

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_25 (cost=0.42..0.78 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=2)

  • Index Cond: (loan_id = lde.loan_id)
  • Filter: (loan_status_id = 4)
  • Rows Removed by Filter: 1
384. 18.717 10,641.226 ↑ 3,557.0 4 1

Nested Loop (cost=7,673.51..53,243.63 rows=14,228 width=202) (actual time=9,680.317..10,641.226 rows=4 loops=1)

  • Join Filter: ((lse_26.autopay_enabled <> 0) OR (lscf_8.payment_type <> 'DirectPay'::text) OR (lp_4.portfolio_id <> 23) OR (COALESCE(lscf_8.ach_revoke_date, ''::text) = ''::text))
  • Rows Removed by Join Filter: 12091
385. 18.031 8,445.229 ↑ 1.0 12,096 1

Nested Loop (cost=7,673.08..41,000.60 rows=12,178 width=20) (actual time=39.261..8,445.229 rows=12,096 loops=1)

386. 120.219 347.070 ↑ 1.0 12,096 1

Merge Join (cost=7,672.65..33,122.07 rows=12,178 width=22) (actual time=28.492..347.070 rows=12,096 loops=1)

  • Merge Cond: (lse_26.loan_id = cie_1.entity_id)
387. 193.818 193.818 ↑ 1.0 386,852 1

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_26 (cost=0.42..24,300.14 rows=386,861 width=18) (actual time=0.025..193.818 rows=386,852 loops=1)

388. 9.605 33.033 ↑ 1.0 12,096 1

Sort (cost=7,672.23..7,702.67 rows=12,178 width=4) (actual time=28.448..33.033 rows=12,096 loops=1)

  • Sort Key: cie_1.entity_id
  • Sort Method: quicksort Memory: 952kB
389. 23.428 23.428 ↑ 1.0 12,096 1

Seq Scan on checklist_item__entity cie_1 (cost=0.00..6,845.83 rows=12,178 width=4) (actual time=0.008..23.428 rows=12,096 loops=1)

  • Filter: ((checklist_item_id = 6) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 160237
390. 8,080.128 8,080.128 ↑ 1.0 1 12,096

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_8 (cost=0.42..0.65 rows=1 width=10) (actual time=0.668..0.668 rows=1 loops=12,096)

  • Index Cond: (loan_settings_id = lse_26.id)
391. 2,177.280 2,177.280 ↑ 1.0 1 12,096

Index Scan using loan__portfolio_loan_id on loan__portfolio lp_4 (cost=0.43..0.96 rows=1 width=8) (actual time=0.178..0.180 rows=1 loops=12,096)

  • Index Cond: (loan_id = lse_26.loan_id)
  • Filter: (portfolio_id = ANY ('{10,11,12,17,23,38,40,41,42,44,45,47,48,49}'::integer[]))
  • Rows Removed by Filter: 6
392. 7.577 229.383 ↓ 0.0 0 1

Nested Loop (cost=3.58..42,106.21 rows=6,539 width=182) (actual time=229.383..229.383 rows=0 loops=1)

  • Join Filter: ((lse_27.loan_sub_status_id <> 25) OR (lse_27.autopay_enabled <> 0) OR (lscf_9.payment_type <> 'DirectPay'::text) OR (lp_5.portfolio_id <> 23) OR (COALESCE(lscf_9.bankruptcynotifdate, ''::text) = ''::text))
  • Rows Removed by Join Filter: 5020
393. 11.635 106.346 ↑ 1.1 5,020 1

Nested Loop (cost=3.15..36,393.79 rows=5,670 width=40) (actual time=0.484..106.346 rows=5,020 loops=1)

394. 4.827 74.631 ↑ 1.1 5,020 1

Hash Join (cost=2.73..32,725.59 rows=5,670 width=42) (actual time=0.465..74.631 rows=5,020 loops=1)

  • Hash Cond: (lse_27.loan_sub_status_id = lsse_11.id)
395. 9.200 69.758 ↑ 1.1 5,020 1

Nested Loop (cost=0.42..32,645.33 rows=5,670 width=30) (actual time=0.404..69.758 rows=5,020 loops=1)

396. 35.458 35.458 ↑ 1.1 5,020 1

Seq Scan on checklist_item__entity cie_2 (cost=0.00..6,845.83 rows=5,670 width=4) (actual time=0.389..35.458 rows=5,020 loops=1)

  • Filter: ((checklist_item_id = 18) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 167313
397. 25.100 25.100 ↑ 1.0 1 5,020

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_27 (cost=0.42..4.54 rows=1 width=26) (actual time=0.004..0.005 rows=1 loops=5,020)

  • Index Cond: (loan_id = cie_2.entity_id)
398. 0.025 0.046 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.045..0.046 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
399. 0.021 0.021 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_11 (cost=0.00..1.58 rows=58 width=16) (actual time=0.005..0.021 rows=58 loops=1)

400. 20.080 20.080 ↑ 1.0 1 5,020

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_9 (cost=0.42..0.65 rows=1 width=10) (actual time=0.004..0.004 rows=1 loops=5,020)

  • Index Cond: (loan_settings_id = lse_27.id)
401. 115.460 115.460 ↑ 1.0 1 5,020

Index Scan using loan__portfolio_loan_id on loan__portfolio lp_5 (cost=0.43..0.96 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=5,020)

  • Index Cond: (loan_id = lse_27.loan_id)
  • Filter: (portfolio_id = ANY ('{10,11,12,17,23,38,40,41,42,44,45,47,48,49}'::integer[]))
  • Rows Removed by Filter: 6
402. 2.533 8,024.457 ↑ 6,668.0 1 1

Hash Left Join (cost=151,005.68..201,981.74 rows=6,668 width=260) (actual time=8,024.004..8,024.457 rows=1 loops=1)

  • Hash Cond: (lse_28.loan_id = ckl.entity_id)
  • Filter: ((ckl.checklist_item_id IS NULL) OR ((cpe.phone IS NOT NULL) AND (cpe.dnd_enabled <> 1)) OR ((cpe2.phone IS NOT NULL) AND (cpe2.dnd_enabled <> 1)) OR ((cpe3.phone IS NOT NULL) AND (cpe3.dnd_enabled <> 1)) OR (ccf.emailoptinoper <> '2'::text) OR (ccf.emailmarketingoptin <> '2'::text))
  • Rows Removed by Filter: 4231
403. 35.199 7,998.576 ↑ 1.6 4,232 1

Hash Left Join (cost=144,088.98..194,737.77 rows=6,668 width=86) (actual time=6,279.279..7,998.576 rows=4,232 loops=1)

  • Hash Cond: (cpe.entity_id = cpe2.entity_id)
  • Join Filter: (cpe2.phone <> cpe.phone)
  • Rows Removed by Join Filter: 4305
404. 4.051 7,526.006 ↑ 1.3 4,231 1

Nested Loop (cost=103,920.90..151,656.89 rows=5,497 width=65) (actual time=5,809.350..7,526.006 rows=4,231 loops=1)

405. 4.274 7,483.876 ↑ 1.1 4,231 1

Nested Loop (cost=103,920.47..147,137.27 rows=4,671 width=73) (actual time=5,809.323..7,483.876 rows=4,231 loops=1)

406. 3.188 6,201.840 ↑ 1.1 4,231 1

Nested Loop (cost=103,920.05..144,870.25 rows=4,671 width=74) (actual time=5,806.731..6,201.840 rows=4,231 loops=1)

407. 7.522 6,181.728 ↑ 1.1 4,231 1

Nested Loop (cost=103,919.63..142,394.74 rows=4,671 width=82) (actual time=5,806.026..6,181.728 rows=4,231 loops=1)

408. 82.393 6,157.282 ↑ 1.1 4,231 1

Hash Right Join (cost=103,919.21..139,635.94 rows=4,671 width=66) (actual time=5,805.998..6,157.282 rows=4,231 loops=1)

  • Hash Cond: (cpe3.entity_id = lc_8.customer_id)
409. 269.018 269.018 ↓ 1.0 256,407 1

Seq Scan on customer_phone_entity cpe3 (cost=0.00..34,730.17 rows=251,165 width=29) (actual time=0.012..269.018 rows=256,407 loops=1)

  • Filter: ((deleted = 0) AND (type = 'customer.phoneType.home'::bpchar))
  • Rows Removed by Filter: 298204
410. 1.735 5,805.871 ↑ 1.1 4,215 1

Hash (cost=103,860.82..103,860.82 rows=4,671 width=41) (actual time=5,805.871..5,805.871 rows=4,215 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 377kB
411. 65.874 5,804.136 ↑ 1.1 4,215 1

Merge Left Join (cost=95,406.32..103,860.82 rows=4,671 width=41) (actual time=5,217.722..5,804.136 rows=4,215 loops=1)

  • Merge Cond: (lc_8.customer_id = cpe.entity_id)
412. 2.968 1,210.814 ↑ 1.1 4,215 1

Sort (cost=28,496.00..28,507.68 rows=4,671 width=12) (actual time=1,209.618..1,210.814 rows=4,215 loops=1)

  • Sort Key: lc_8.customer_id
  • Sort Method: quicksort Memory: 390kB
413. 40.939 1,207.846 ↑ 1.1 4,215 1

Hash Join (cost=19,604.43..28,211.31 rows=4,671 width=12) (actual time=1,158.079..1,207.846 rows=4,215 loops=1)

  • Hash Cond: (cie_3.entity_id = lc_8.loan_id)
414. 21.189 21.189 ↑ 1.1 4,215 1

Seq Scan on checklist_item__entity cie_3 (cost=0.00..6,845.83 rows=4,667 width=4) (actual time=0.505..21.189 rows=4,215 loops=1)

  • Filter: ((checklist_item_id = 19) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 168118
415. 145.952 1,145.718 ↓ 1.0 388,528 1

Hash (cost=13,250.08..13,250.08 rows=387,308 width=8) (actual time=1,145.718..1,145.718 rows=388,528 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 2933kB
416. 999.766 999.766 ↓ 1.0 388,528 1

Seq Scan on loan__customer lc_8 (cost=0.00..13,250.08 rows=387,308 width=8) (actual time=0.717..999.766 rows=388,528 loops=1)

417. 122.357 4,527.448 ↓ 189.2 265,996 1

Materialize (cost=66,910.32..75,349.63 rows=1,406 width=29) (actual time=4,007.948..4,527.448 rows=265,996 loops=1)

418. 109.196 4,405.091 ↓ 189.2 265,996 1

Subquery Scan on cpe (cost=66,910.32..75,346.11 rows=1,406 width=29) (actual time=4,007.943..4,405.091 rows=265,996 loops=1)

  • Filter: (cpe.row_num = 1)
  • Rows Removed by Filter: 10942
419. 213.508 4,295.895 ↑ 1.0 276,938 1

WindowAgg (cost=66,910.32..71,831.20 rows=281,193 width=37) (actual time=4,007.940..4,295.895 rows=276,938 loops=1)

420. 330.531 4,082.387 ↑ 1.0 276,938 1

Sort (cost=66,910.32..67,613.30 rows=281,193 width=29) (actual time=4,007.931..4,082.387 rows=276,938 loops=1)

  • Sort Key: customer_phone_entity.entity_id
  • Sort Method: external merge Disk: 11672kB
421. 3,751.856 3,751.856 ↓ 1.0 283,770 1

Seq Scan on customer_phone_entity (cost=0.00..34,730.17 rows=281,193 width=29) (actual time=0.818..3,751.856 rows=283,770 loops=1)

  • Filter: ((deleted = 0) AND (type = 'customer.phoneType.cell'::bpchar))
  • Rows Removed by Filter: 270841
422. 16.924 16.924 ↑ 1.0 1 4,231

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_28 (cost=0.42..0.58 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=4,231)

  • Index Cond: (loan_id = lc_8.loan_id)
423. 16.924 16.924 ↑ 1.0 1 4,231

Index Only Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_10 (cost=0.42..0.53 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=4,231)

  • Index Cond: (loan_settings_id = lse_28.id)
  • Heap Fetches: 1685
424. 1,277.762 1,277.762 ↑ 1.0 1 4,231

Index Scan using customer_custom_fields_customer_id on customer_custom_fields ccf (cost=0.42..0.49 rows=1 width=7) (actual time=0.302..0.302 rows=1 loops=4,231)

  • Index Cond: (customer_id = lc_8.customer_id)
425. 38.079 38.079 ↑ 1.0 1 4,231

Index Scan using loan__portfolio_loan_id on loan__portfolio lp_6 (cost=0.43..0.96 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=4,231)

  • Index Cond: (loan_id = lse_28.loan_id)
  • Filter: (portfolio_id = ANY ('{10,11,12,17,23,38,40,41,42,44,45,47,48,49}'::integer[]))
  • Rows Removed by Filter: 6
426. 130.485 437.371 ↓ 1.0 283,770 1

Hash (cost=34,730.17..34,730.17 rows=281,193 width=29) (actual time=437.370..437.371 rows=283,770 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2735kB
427. 306.886 306.886 ↓ 1.0 283,770 1

Seq Scan on customer_phone_entity cpe2 (cost=0.00..34,730.17 rows=281,193 width=29) (actual time=0.008..306.886 rows=283,770 loops=1)

  • Filter: ((deleted = 0) AND (type = 'customer.phoneType.cell'::bpchar))
  • Rows Removed by Filter: 270841
428. 1.544 23.348 ↑ 1.1 5,020 1

Hash (cost=6,845.83..6,845.83 rows=5,670 width=8) (actual time=23.348..23.348 rows=5,020 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 261kB
429. 21.804 21.804 ↑ 1.1 5,020 1

Seq Scan on checklist_item__entity ckl (cost=0.00..6,845.83 rows=5,670 width=8) (actual time=0.422..21.804 rows=5,020 loops=1)

  • Filter: ((checklist_item_id = 18) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 167313
430. 5.294 591.197 ↓ 0.0 0 1

Nested Loop (cost=3.58..40,136.70 rows=6,190 width=182) (actual time=591.196..591.197 rows=0 loops=1)

  • Join Filter: ((lse_29.loan_sub_status_id <> ALL ('{28,30}'::bigint[])) OR (lse_29.autopay_enabled <> 0) OR (lscf_11.payment_type <> 'DirectPay'::text) OR (lp_7.portfolio_id <> 23) OR (COALESCE(lscf_11.debtmgmtdate, ''::text) = ''::text))
  • Rows Removed by Join Filter: 4414
431. 9.558 484.381 ↑ 1.2 4,414 1

Nested Loop (cost=3.15..34,834.40 rows=5,260 width=40) (actual time=0.121..484.381 rows=4,414 loops=1)

432. 4.316 68.735 ↑ 1.2 4,414 1

Hash Join (cost=2.73..31,431.46 rows=5,260 width=42) (actual time=0.104..68.735 rows=4,414 loops=1)

  • Hash Cond: (lse_29.loan_sub_status_id = lsse_12.id)
433. 8.031 64.377 ↑ 1.2 4,414 1

Nested Loop (cost=0.42..31,356.83 rows=5,260 width=30) (actual time=0.047..64.377 rows=4,414 loops=1)

434. 34.276 34.276 ↑ 1.2 4,414 1

Seq Scan on checklist_item__entity cie_4 (cost=0.00..6,845.83 rows=5,260 width=4) (actual time=0.028..34.276 rows=4,414 loops=1)

  • Filter: ((checklist_item_id = 12) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 167919
435. 22.070 22.070 ↑ 1.0 1 4,414

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_29 (cost=0.42..4.65 rows=1 width=26) (actual time=0.004..0.005 rows=1 loops=4,414)

  • Index Cond: (loan_id = cie_4.entity_id)
436. 0.022 0.042 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.042..0.042 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
437. 0.020 0.020 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_12 (cost=0.00..1.58 rows=58 width=16) (actual time=0.005..0.020 rows=58 loops=1)

438. 406.088 406.088 ↑ 1.0 1 4,414

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_11 (cost=0.42..0.65 rows=1 width=10) (actual time=0.092..0.092 rows=1 loops=4,414)

  • Index Cond: (loan_settings_id = lse_29.id)
439. 101.522 101.522 ↑ 1.0 1 4,414

Index Scan using loan__portfolio_loan_id on loan__portfolio lp_7 (cost=0.43..0.96 rows=1 width=8) (actual time=0.021..0.023 rows=1 loops=4,414)

  • Index Cond: (loan_id = lse_29.loan_id)
  • Filter: (portfolio_id = ANY ('{10,11,12,17,23,38,40,41,42,44,45,47,48,49}'::integer[]))
  • Rows Removed by Filter: 6
440. 3.182 2,729.722 ↑ 7,362.0 1 1

Hash Left Join (cost=151,194.24..200,757.08 rows=7,362 width=229) (actual time=2,710.342..2,729.722 rows=1 loops=1)

  • Hash Cond: (lse_30.loan_id = ckl_1.entity_id)
  • Filter: ((ckl_1.checklist_item_id IS NULL) OR (COALESCE(lscf_12.debtmgmtcompany, ''::text) = ''::text) OR ((cpe_1.phone IS NOT NULL) AND (cpe_1.dnd_enabled <> 1)) OR ((cpe2_1.phone IS NOT NULL) AND (cpe2_1.dnd_enabled <> 1)) OR ((cpe3_1.phone IS NOT NULL) AND (cpe3_1.dnd_enabled <> 1)) OR (ccf_1.emailoptinoper <> '2'::text) OR (ccf_1.emailmarketingoptin <> '2'::text))
  • Rows Removed by Filter: 5318
441. 34.209 2,703.676 ↑ 1.4 5,319 1

Hash Left Join (cost=144,282.66..193,484.11 rows=7,362 width=87) (actual time=1,996.211..2,703.676 rows=5,319 loops=1)

  • Hash Cond: (cpe_1.entity_id = cpe2_1.entity_id)
  • Join Filter: (cpe2_1.phone <> cpe_1.phone)
  • Rows Removed by Join Filter: 5448
442. 9.538 2,091.934 ↑ 1.1 5,318 1

Nested Loop (cost=104,114.58..150,299.76 rows=6,069 width=66) (actual time=1,410.386..2,091.934 rows=5,318 loops=1)

443. 9.214 1,811.178 ↑ 1.1 5,318 1

Nested Loop (cost=104,114.16..147,354.24 rows=6,069 width=67) (actual time=1,409.798..1,811.178 rows=5,318 loops=1)

444. 5.742 1,786.010 ↑ 1.1 5,318 1

Nested Loop (cost=104,113.74..143,427.91 rows=6,069 width=74) (actual time=1,409.769..1,786.010 rows=5,318 loops=1)

445. 80.889 1,758.996 ↑ 1.1 5,318 1

Hash Right Join (cost=104,113.31..139,843.43 rows=6,069 width=66) (actual time=1,409.747..1,758.996 rows=5,318 loops=1)

  • Hash Cond: (cpe3_1.entity_id = lc_9.customer_id)
446. 268.485 268.485 ↓ 1.0 256,407 1

Seq Scan on customer_phone_entity cpe3_1 (cost=0.00..34,730.17 rows=251,165 width=29) (actual time=0.013..268.485 rows=256,407 loops=1)

  • Filter: ((deleted = 0) AND (type = 'customer.phoneType.home'::bpchar))
  • Rows Removed by Filter: 298204
447. 2.164 1,409.622 ↑ 1.1 5,300 1

Hash (cost=104,037.45..104,037.45 rows=6,069 width=41) (actual time=1,409.621..1,409.622 rows=5,300 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 458kB
448. 68.045 1,407.458 ↑ 1.1 5,300 1

Merge Left Join (cost=95,575.86..104,037.45 rows=6,069 width=41) (actual time=813.829..1,407.458 rows=5,300 loops=1)

  • Merge Cond: (lc_9.customer_id = cpe_1.entity_id)
449. 3.820 326.260 ↑ 1.1 5,300 1

Sort (cost=28,665.54..28,680.71 rows=6,069 width=12) (actual time=324.687..326.260 rows=5,300 loops=1)

  • Sort Key: lc_9.customer_id
  • Sort Method: quicksort Memory: 441kB
450. 36.130 322.440 ↑ 1.1 5,300 1

Hash Join (cost=19,604.43..28,284.19 rows=6,069 width=12) (actual time=261.581..322.440 rows=5,300 loops=1)

  • Hash Cond: (cie_5.entity_id = lc_9.loan_id)
451. 26.942 26.942 ↑ 1.1 5,300 1

Seq Scan on checklist_item__entity cie_5 (cost=0.00..6,845.83 rows=6,064 width=4) (actual time=0.023..26.942 rows=5,300 loops=1)

  • Filter: ((checklist_item_id = ANY ('{13,14}'::integer[])) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 167033
452. 134.521 259.368 ↓ 1.0 388,528 1

Hash (cost=13,250.08..13,250.08 rows=387,308 width=8) (actual time=259.368..259.368 rows=388,528 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 2933kB
453. 124.847 124.847 ↓ 1.0 388,528 1

Seq Scan on loan__customer lc_9 (cost=0.00..13,250.08 rows=387,308 width=8) (actual time=0.005..124.847 rows=388,528 loops=1)

454. 123.958 1,013.153 ↓ 191.7 269,473 1

Materialize (cost=66,910.32..75,349.63 rows=1,406 width=29) (actual time=488.983..1,013.153 rows=269,473 loops=1)

455. 108.954 889.195 ↓ 190.5 267,876 1

Subquery Scan on cpe_1 (cost=66,910.32..75,346.11 rows=1,406 width=29) (actual time=488.980..889.195 rows=267,876 loops=1)

  • Filter: (cpe_1.row_num = 1)
  • Rows Removed by Filter: 11280
456. 217.307 780.241 ↑ 1.0 279,156 1

WindowAgg (cost=66,910.32..71,831.20 rows=281,193 width=37) (actual time=488.978..780.241 rows=279,156 loops=1)

457. 290.982 562.934 ↑ 1.0 279,156 1

Sort (cost=66,910.32..67,613.30 rows=281,193 width=29) (actual time=488.971..562.934 rows=279,156 loops=1)

  • Sort Key: customer_phone_entity_1.entity_id
  • Sort Method: external merge Disk: 11672kB
458. 271.952 271.952 ↓ 1.0 283,770 1

Seq Scan on customer_phone_entity customer_phone_entity_1 (cost=0.00..34,730.17 rows=281,193 width=29) (actual time=0.012..271.952 rows=283,770 loops=1)

  • Filter: ((deleted = 0) AND (type = 'customer.phoneType.cell'::bpchar))
  • Rows Removed by Filter: 270841
459. 21.272 21.272 ↑ 1.0 1 5,318

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_30 (cost=0.42..0.58 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=5,318)

  • Index Cond: (loan_id = lc_9.loan_id)
460. 15.954 15.954 ↑ 1.0 1 5,318

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_12 (cost=0.42..0.65 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=5,318)

  • Index Cond: (loan_settings_id = lse_30.id)
461. 271.218 271.218 ↑ 1.0 1 5,318

Index Scan using customer_custom_fields_customer_id on customer_custom_fields ccf_1 (cost=0.42..0.49 rows=1 width=7) (actual time=0.051..0.051 rows=1 loops=5,318)

  • Index Cond: (customer_id = lc_9.customer_id)
462. 172.853 577.533 ↓ 1.0 283,770 1

Hash (cost=34,730.17..34,730.17 rows=281,193 width=29) (actual time=577.533..577.533 rows=283,770 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2735kB
463. 404.680 404.680 ↓ 1.0 283,770 1

Seq Scan on customer_phone_entity cpe2_1 (cost=0.00..34,730.17 rows=281,193 width=29) (actual time=0.005..404.680 rows=283,770 loops=1)

  • Filter: ((deleted = 0) AND (type = 'customer.phoneType.cell'::bpchar))
  • Rows Removed by Filter: 270841
464. 1.358 22.864 ↑ 1.2 4,414 1

Hash (cost=6,845.83..6,845.83 rows=5,260 width=8) (actual time=22.863..22.864 rows=4,414 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 237kB
465. 21.506 21.506 ↑ 1.2 4,414 1

Seq Scan on checklist_item__entity ckl_1 (cost=0.00..6,845.83 rows=5,260 width=8) (actual time=0.020..21.506 rows=4,414 loops=1)

  • Filter: ((checklist_item_id = 12) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 167919
466. 0.374 75.876 ↓ 0.0 0 1

Nested Loop (cost=3.58..10,428.79 rows=443 width=182) (actual time=75.876..75.876 rows=0 loops=1)

  • Join Filter: ((lse_31.loan_sub_status_id <> ALL ('{43,46}'::bigint[])) OR (lse_31.autopay_enabled <> 0) OR (lscf_13.payment_type <> 'DirectPay'::text) OR (lp_8.portfolio_id <> 23) OR (COALESCE(lscf_13.deceasednotifdate, ''::text) = ''::text))
  • Rows Removed by Join Filter: 197
467. 0.306 69.001 ↑ 1.9 197 1

Nested Loop (cost=3.15..10,049.75 rows=376 width=40) (actual time=2.133..69.001 rows=197 loops=1)

468. 0.195 28.507 ↑ 1.9 197 1

Hash Join (cost=2.73..9,806.50 rows=376 width=42) (actual time=2.121..28.507 rows=197 loops=1)

  • Hash Cond: (lse_31.loan_sub_status_id = lsse_13.id)
469. 0.395 28.269 ↑ 1.9 197 1

Nested Loop (cost=0.42..9,799.03 rows=376 width=30) (actual time=2.064..28.269 rows=197 loops=1)

470. 26.889 26.889 ↑ 1.9 197 1

Seq Scan on checklist_item__entity cie_6 (cost=0.00..6,845.83 rows=376 width=4) (actual time=2.047..26.889 rows=197 loops=1)

  • Filter: ((checklist_item_id = 20) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 172136
471. 0.985 0.985 ↑ 1.0 1 197

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_31 (cost=0.42..7.84 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=197)

  • Index Cond: (loan_id = cie_6.entity_id)
472. 0.023 0.043 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.043..0.043 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
473. 0.020 0.020 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_13 (cost=0.00..1.58 rows=58 width=16) (actual time=0.005..0.020 rows=58 loops=1)

474. 40.188 40.188 ↑ 1.0 1 197

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_13 (cost=0.42..0.65 rows=1 width=10) (actual time=0.204..0.204 rows=1 loops=197)

  • Index Cond: (loan_settings_id = lse_31.id)
475. 6.501 6.501 ↑ 1.0 1 197

Index Scan using loan__portfolio_loan_id on loan__portfolio lp_8 (cost=0.43..0.96 rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=197)

  • Index Cond: (loan_id = lse_31.loan_id)
  • Filter: (portfolio_id = ANY ('{10,11,12,17,23,38,40,41,42,44,45,47,48,49}'::integer[]))
  • Rows Removed by Filter: 6
476. 0.002 51.237 ↓ 0.0 0 1

Nested Loop (cost=6,854.11..17,232.58 rows=442 width=240) (actual time=51.236..51.237 rows=0 loops=1)

477. 0.002 51.235 ↓ 0.0 0 1

Nested Loop (cost=6,853.68..16,867.52 rows=375 width=28) (actual time=51.235..51.235 rows=0 loops=1)

478. 0.017 51.233 ↓ 0.0 0 1

Hash Join (cost=6,853.26..16,668.78 rows=375 width=36) (actual time=51.233..51.233 rows=0 loops=1)

  • Hash Cond: (lse_32.loan_sub_status_id = lsse_14.id)
479. 0.102 51.137 ↓ 0.0 0 1

Hash Left Join (cost=6,850.95..16,661.32 rows=375 width=32) (actual time=51.137..51.137 rows=0 loops=1)

  • Hash Cond: (lse_32.loan_id = ckl_2.entity_id)
  • Filter: ((lse_32.loan_sub_status_id <> 43) OR (ckl_2.checklist_item_id IS NULL))
  • Rows Removed by Filter: 196
480. 0.250 22.018 ↑ 1.9 196 1

Nested Loop (cost=0.42..9,799.03 rows=376 width=28) (actual time=3.437..22.018 rows=196 loops=1)

481. 20.984 20.984 ↑ 1.9 196 1

Seq Scan on checklist_item__entity cie_7 (cost=0.00..6,845.83 rows=376 width=4) (actual time=3.421..20.984 rows=196 loops=1)

  • Filter: ((checklist_item_id = 21) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 172137
482. 0.784 0.784 ↑ 1.0 1 196

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_32 (cost=0.42..7.84 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=196)

  • Index Cond: (loan_id = cie_7.entity_id)
483. 0.107 29.017 ↑ 1.9 197 1

Hash (cost=6,845.83..6,845.83 rows=376 width=8) (actual time=29.017..29.017 rows=197 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
484. 28.910 28.910 ↑ 1.9 197 1

Seq Scan on checklist_item__entity ckl_2 (cost=0.00..6,845.83 rows=376 width=8) (actual time=2.828..28.910 rows=197 loops=1)

  • Filter: ((checklist_item_id = 20) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 172136
485. 0.038 0.079 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.079..0.079 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
486. 0.041 0.041 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_14 (cost=0.00..1.58 rows=58 width=16) (actual time=0.014..0.041 rows=58 loops=1)

487. 0.000 0.000 ↓ 0.0 0

Index Only Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_14 (cost=0.42..0.53 rows=1 width=4) (never executed)

  • Index Cond: (loan_settings_id = lse_32.id)
  • Heap Fetches: 0
488. 0.000 0.000 ↓ 0.0 0

Index Scan using loan__portfolio_loan_id on loan__portfolio lp_9 (cost=0.43..0.96 rows=1 width=4) (never executed)

  • Index Cond: (loan_id = lse_32.loan_id)
  • Filter: (portfolio_id = ANY ('{10,11,12,17,23,38,40,41,42,44,45,47,48,49}'::integer[]))
489. 0.088 30.739 ↓ 0.0 0 1

Nested Loop (cost=3.58..10,428.58 rows=434 width=182) (actual time=30.739..30.739 rows=0 loops=1)

  • Join Filter: ((lse_33.loan_sub_status_id <> ALL ('{52,54}'::bigint[])) OR (lse_33.autopay_enabled <> 0) OR (lp_10.portfolio_id <> 23) OR (lscf_15.payment_type <> 'DirectPay'::text) OR (COALESCE(lscf_15.id_theft_notify_date, ''::text) = ''::text))
  • Rows Removed by Join Filter: 49
490. 0.078 27.515 ↑ 7.7 49 1

Nested Loop (cost=3.15..10,049.75 rows=376 width=40) (actual time=2.418..27.515 rows=49 loops=1)

491. 0.060 20.038 ↑ 7.7 49 1

Hash Join (cost=2.73..9,806.50 rows=376 width=42) (actual time=1.735..20.038 rows=49 loops=1)

  • Hash Cond: (lse_33.loan_sub_status_id = lsse_15.id)
492. 0.082 19.937 ↑ 7.7 49 1

Nested Loop (cost=0.42..9,799.03 rows=376 width=30) (actual time=1.676..19.937 rows=49 loops=1)

493. 19.610 19.610 ↑ 7.7 49 1

Seq Scan on checklist_item__entity cie_8 (cost=0.00..6,845.83 rows=376 width=4) (actual time=1.660..19.610 rows=49 loops=1)

  • Filter: ((checklist_item_id = 25) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 172284
494. 0.245 0.245 ↑ 1.0 1 49

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_33 (cost=0.42..7.84 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=49)

  • Index Cond: (loan_id = cie_8.entity_id)
495. 0.021 0.041 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.041..0.041 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
496. 0.020 0.020 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_15 (cost=0.00..1.58 rows=58 width=16) (actual time=0.005..0.020 rows=58 loops=1)

497. 7.399 7.399 ↑ 1.0 1 49

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_15 (cost=0.42..0.65 rows=1 width=10) (actual time=0.151..0.151 rows=1 loops=49)

  • Index Cond: (loan_settings_id = lse_33.id)
498. 3.136 3.136 ↑ 1.0 1 49

Index Scan using loan__portfolio_loan_id on loan__portfolio lp_10 (cost=0.43..0.96 rows=1 width=8) (actual time=0.063..0.064 rows=1 loops=49)

  • Index Cond: (loan_id = lse_33.loan_id)
  • Filter: (portfolio_id = ANY ('{10,11,12,17,23,38,40,41,42,44,45,47,48,49}'::integer[]))
  • Rows Removed by Filter: 6
499. 0.001 38.833 ↓ 0.0 0 1

Nested Loop (cost=6,854.11..17,232.53 rows=433 width=240) (actual time=38.833..38.833 rows=0 loops=1)

500. 0.001 38.832 ↓ 0.0 0 1

Nested Loop (cost=6,853.68..16,867.52 rows=375 width=28) (actual time=38.832..38.832 rows=0 loops=1)

501. 0.012 38.831 ↓ 0.0 0 1

Hash Join (cost=6,853.26..16,668.78 rows=375 width=36) (actual time=38.831..38.831 rows=0 loops=1)

  • Hash Cond: (lse_34.loan_sub_status_id = lsse_16.id)
502. 0.029 38.772 ↓ 0.0 0 1

Hash Left Join (cost=6,850.95..16,661.32 rows=375 width=32) (actual time=38.772..38.772 rows=0 loops=1)

  • Hash Cond: (lse_34.loan_id = ckl_3.entity_id)
  • Filter: ((lse_34.loan_sub_status_id <> 52) OR (ckl_3.checklist_item_id IS NULL))
  • Rows Removed by Filter: 38
503. 0.045 19.469 ↑ 9.9 38 1

Nested Loop (cost=0.42..9,799.03 rows=376 width=28) (actual time=1.756..19.469 rows=38 loops=1)

504. 19.234 19.234 ↑ 9.9 38 1

Seq Scan on checklist_item__entity cie_9 (cost=0.00..6,845.83 rows=376 width=4) (actual time=1.746..19.234 rows=38 loops=1)

  • Filter: ((checklist_item_id = 26) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 172295
505. 0.190 0.190 ↑ 1.0 1 38

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_34 (cost=0.42..7.84 rows=1 width=24) (actual time=0.004..0.005 rows=1 loops=38)

  • Index Cond: (loan_id = cie_9.entity_id)
506. 0.024 19.274 ↑ 7.7 49 1

Hash (cost=6,845.83..6,845.83 rows=376 width=8) (actual time=19.273..19.274 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
507. 19.250 19.250 ↑ 7.7 49 1

Seq Scan on checklist_item__entity ckl_3 (cost=0.00..6,845.83 rows=376 width=8) (actual time=1.216..19.250 rows=49 loops=1)

  • Filter: ((checklist_item_id = 25) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 172284
508. 0.022 0.047 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.047..0.047 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
509. 0.025 0.025 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_16 (cost=0.00..1.58 rows=58 width=16) (actual time=0.011..0.025 rows=58 loops=1)

510. 0.000 0.000 ↓ 0.0 0

Index Only Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_16 (cost=0.42..0.53 rows=1 width=4) (never executed)

  • Index Cond: (loan_settings_id = lse_34.id)
  • Heap Fetches: 0
511. 0.000 0.000 ↓ 0.0 0

Index Scan using loan__portfolio_loan_id on loan__portfolio lp_11 (cost=0.43..0.96 rows=1 width=4) (never executed)

  • Index Cond: (loan_id = lse_34.loan_id)
  • Filter: (portfolio_id = ANY ('{10,11,12,17,23,38,40,41,42,44,45,47,48,49}'::integer[]))
512. 0.027 625.134 ↑ 1,194.7 3 1

Hash Join (cost=3.15..28,272.35 rows=3,584 width=209) (actual time=602.773..625.134 rows=3 loops=1)

  • Hash Cond: (lse_35.loan_sub_status_id = lsse_17.id)
513. 1.476 625.063 ↑ 1,194.7 3 1

Nested Loop (cost=0.84..28,184.92 rows=3,584 width=19) (actual time=602.709..625.063 rows=3 loops=1)

514. 3.599 42.055 ↑ 1.1 3,381 1

Nested Loop (cost=0.42..25,798.23 rows=3,592 width=26) (actual time=0.023..42.055 rows=3,381 loops=1)

515. 24.932 24.932 ↑ 1.1 3,381 1

Seq Scan on checklist_item__entity cie_10 (cost=0.00..6,845.83 rows=3,592 width=4) (actual time=0.007..24.932 rows=3,381 loops=1)

  • Filter: ((checklist_item_id = 9) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 168952
516. 13.524 13.524 ↑ 1.0 1 3,381

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_35 (cost=0.42..5.27 rows=1 width=26) (actual time=0.003..0.004 rows=1 loops=3,381)

  • Index Cond: (loan_id = cie_10.entity_id)
517. 581.532 581.532 ↓ 0.0 0 3,381

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_17 (cost=0.42..0.65 rows=1 width=5) (actual time=0.172..0.172 rows=0 loops=3,381)

  • Index Cond: (loan_settings_id = lse_35.id)
  • Filter: ((lse_35.loan_sub_status_id <> ALL ('{36,40}'::bigint[])) OR (lse_35.autopay_enabled <> 0) OR (COALESCE(rescind_request_date, ''::text) = ''::text))
  • Rows Removed by Filter: 1
518. 0.022 0.044 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.044..0.044 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
519. 0.022 0.022 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_17 (cost=0.00..1.58 rows=58 width=16) (actual time=0.007..0.022 rows=58 loops=1)

520. 0.016 73.094 ↑ 3,286.0 1 1

Hash Join (cost=6,893.88..33,928.09 rows=3,286 width=209) (actual time=70.138..73.094 rows=1 loops=1)

  • Hash Cond: (lse_36.loan_sub_status_id = lsse_18.id)
521. 1.588 73.036 ↑ 3,286.0 1 1

Hash Left Join (cost=6,891.57..33,864.18 rows=3,286 width=21) (actual time=70.080..73.036 rows=1 loops=1)

  • Hash Cond: (lse_36.loan_id = ckl_4.entity_id)
  • Filter: ((lse_36.loan_sub_status_id <> 36) OR (COALESCE(lscf_18.rescind_confirm_date, ''::text) = ''::text) OR (ckl_4.checklist_item_id IS NULL))
  • Rows Removed by Filter: 3353
522. 3.334 49.306 ↓ 1.0 3,354 1

Nested Loop (cost=0.84..26,870.08 rows=3,293 width=17) (actual time=0.036..49.306 rows=3,354 loops=1)

523. 4.110 35.910 ↓ 1.0 3,354 1

Nested Loop (cost=0.42..24,739.68 rows=3,293 width=24) (actual time=0.027..35.910 rows=3,354 loops=1)

524. 21.738 21.738 ↓ 1.0 3,354 1

Seq Scan on checklist_item__entity cie_11 (cost=0.00..6,845.83 rows=3,293 width=4) (actual time=0.016..21.738 rows=3,354 loops=1)

  • Filter: ((checklist_item_id = 10) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 168979
525. 10.062 10.062 ↑ 1.0 1 3,354

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_36 (cost=0.42..5.42 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=3,354)

  • Index Cond: (loan_id = cie_11.entity_id)
526. 10.062 10.062 ↑ 1.0 1 3,354

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_18 (cost=0.42..0.65 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=3,354)

  • Index Cond: (loan_settings_id = lse_36.id)
527. 1.027 22.142 ↑ 1.1 3,381 1

Hash (cost=6,845.83..6,845.83 rows=3,592 width=8) (actual time=22.142..22.142 rows=3,381 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 165kB
528. 21.115 21.115 ↑ 1.1 3,381 1

Seq Scan on checklist_item__entity ckl_4 (cost=0.00..6,845.83 rows=3,592 width=8) (actual time=0.004..21.115 rows=3,381 loops=1)

  • Filter: ((checklist_item_id = 9) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 168952
529. 0.021 0.042 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.042..0.042 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
530. 0.021 0.021 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_18 (cost=0.00..1.58 rows=58 width=16) (actual time=0.006..0.021 rows=58 loops=1)

531. 0.002 189.508 ↓ 0.0 0 1

Hash Join (cost=2.73..21,158.44 rows=276 width=240) (actual time=189.508..189.508 rows=0 loops=1)

  • Hash Cond: (lse_37.loan_sub_status_id = lsse_19.id)
532. 1.420 189.506 ↓ 0.0 0 1

Nested Loop (cost=0.42..21,149.58 rows=276 width=20) (actual time=189.506..189.506 rows=0 loops=1)

533. 25.709 25.709 ↑ 1.1 2,287 1

Seq Scan on checklist_item__entity cie_12 (cost=0.00..6,845.83 rows=2,461 width=4) (actual time=0.211..25.709 rows=2,287 loops=1)

  • Filter: ((checklist_item_id = 17) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 170046
534. 162.377 162.377 ↓ 0.0 0 2,287

Index Scan using loan_settings_entity_idx_cond4 on loan_settings_entity lse_37 (cost=0.42..5.80 rows=1 width=20) (actual time=0.071..0.071 rows=0 loops=2,287)

  • Index Cond: (loan_id = cie_12.entity_id)
  • Filter: ((loan_sub_status_id = ANY ('{26,27}'::bigint[])) AND (loan_status_id = 5))
  • Rows Removed by Filter: 1
535. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.58..1.58 rows=58 width=16) (never executed)

536. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_sub_status_entity lsse_19 (cost=0.00..1.58 rows=58 width=16) (never executed)

537. 0.001 22.788 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.42..16,975.29 rows=17 width=248) (actual time=22.788..22.788 rows=0 loops=1)

  • Join Filter: (lse_38.loan_id = ckl_5.entity_id)
538. 0.105 22.787 ↓ 0.0 0 1

Nested Loop (cost=0.42..10,032.55 rows=17 width=28) (actual time=22.787..22.787 rows=0 loops=1)

539. 21.902 21.902 ↓ 1.6 195 1

Seq Scan on checklist_item__entity cie_13 (cost=0.00..8,999.99 rows=125 width=24) (actual time=2.087..21.902 rows=195 loops=1)

  • Filter: ((checklist_item_id = 20) AND (checklist_item_value = 1) AND (deleted = 0) AND ((lastupdated)::date < (CURRENT_DATE - 14)))
  • Rows Removed by Filter: 172138
540. 0.780 0.780 ↓ 0.0 0 195

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_38 (cost=0.42..8.25 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=195)

  • Index Cond: (loan_id = cie_13.entity_id)
  • Filter: (loan_status_id = 4)
  • Rows Removed by Filter: 1
541. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..6,847.71 rows=376 width=4) (never executed)

542. 0.000 0.000 ↓ 0.0 0

Seq Scan on checklist_item__entity ckl_5 (cost=0.00..6,845.83 rows=376 width=4) (never executed)

  • Filter: ((checklist_item_id = 21) AND (checklist_item_value = 1) AND (deleted = 0))
543. 0.001 19.885 ↓ 0.0 0 1

Nested Loop (cost=0.42..15,855.65 rows=1 width=248) (actual time=19.884..19.885 rows=0 loops=1)

544. 0.002 19.884 ↓ 0.0 0 1

Nested Loop (cost=0.00..15,847.38 rows=1 width=28) (actual time=19.884..19.884 rows=0 loops=1)

  • Join Filter: (cie_14.entity_id = ckl_6.entity_id)
545. 19.882 19.882 ↓ 0.0 0 1

Seq Scan on checklist_item__entity ckl_6 (cost=0.00..6,845.83 rows=1 width=4) (actual time=19.882..19.882 rows=0 loops=1)

  • Filter: ((checklist_item_id IS NULL) AND (checklist_item_value = 1) AND (deleted = 0) AND (checklist_item_id = 26))
  • Rows Removed by Filter: 172333
546. 0.000 0.000 ↓ 0.0 0

Seq Scan on checklist_item__entity cie_14 (cost=0.00..8,999.99 rows=125 width=24) (never executed)

  • Filter: ((checklist_item_id = 25) AND (checklist_item_value = 1) AND (deleted = 0) AND ((lastupdated)::date < (CURRENT_DATE - 14)))
547. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_39 (cost=0.42..8.25 rows=1 width=8) (never executed)

  • Index Cond: (loan_id = cie_14.entity_id)
  • Filter: (loan_status_id = 4)
548. 0.009 0.009 ↓ 0.0 0 1

Result (cost=0.57..280,581.66 rows=247 width=260) (actual time=0.008..0.009 rows=0 loops=1)

  • One-Time Filter: (date_part('day'::text, (CURRENT_DATE)::timestamp without time zone) = ANY ('{14,15,16}'::double precision[]))
549. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_status_archive_date_loan_id on loan_status_archive loan_status_archive_4 (cost=0.57..280,581.66 rows=247 width=260) (never executed)

  • Index Cond: (date = CURRENT_DATE)
  • Filter: (loan_sub_status_id = 44)
550. 5.109 1,339.131 ↑ 12,002.0 1 1

Hash Anti Join (cost=30,331.39..47,860.24 rows=12,002 width=229) (actual time=1,333.551..1,339.131 rows=1 loops=1)

  • Hash Cond: (lse_40.loan_id = cie_15.entity_id)
551. 125.410 1,293.105 ↑ 1.0 12,097 1

Hash Join (cost=23,333.34..40,638.47 rows=12,392 width=9) (actual time=1,074.130..1,293.105 rows=12,097 loops=1)

  • Hash Cond: (lse_40.id = cf_5.loan_settings_id)
552. 93.626 93.626 ↓ 1.0 388,522 1

Seq Scan on loan_settings_entity lse_40 (cost=0.00..16,289.61 rows=386,861 width=16) (actual time=0.010..93.626 rows=388,522 loops=1)

553. 6.369 1,074.069 ↑ 1.0 12,097 1

Hash (cost=23,177.33..23,177.33 rows=12,481 width=5) (actual time=1,074.069..1,074.069 rows=12,097 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 696kB
554. 1,067.700 1,067.700 ↑ 1.0 12,097 1

Seq Scan on loan_settings_custom_fields cf_5 (cost=0.00..23,177.33 rows=12,481 width=5) (actual time=0.016..1,067.700 rows=12,097 loops=1)

  • Filter: (ach_revoke_date <> ''::text)
  • Rows Removed by Filter: 376425
555. 5.355 40.917 ↑ 1.0 12,096 1

Hash (cost=6,845.83..6,845.83 rows=12,178 width=4) (actual time=40.917..40.917 rows=12,096 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 554kB
556. 35.562 35.562 ↑ 1.0 12,096 1

Seq Scan on checklist_item__entity cie_15 (cost=0.00..6,845.83 rows=12,178 width=4) (actual time=0.009..35.562 rows=12,096 loops=1)

  • Filter: ((checklist_item_id = 6) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 160237
557. 1.894 86.831 ↓ 0.0 0 1

Hash Anti Join (cost=6,975.05..24,343.21 rows=4,744 width=260) (actual time=86.831..86.831 rows=0 loops=1)

  • Hash Cond: (lse_41.loan_id = cie_16.entity_id)
558. 54.727 54.727 ↓ 1.0 5,020 1

Seq Scan on loan_settings_entity lse_41 (cost=0.00..17,256.76 rows=4,874 width=16) (actual time=0.018..54.727 rows=5,020 loops=1)

  • Filter: (loan_sub_status_id = 25)
  • Rows Removed by Filter: 383502
559. 2.932 30.210 ↑ 1.1 9,235 1

Hash (cost=6,845.83..6,845.83 rows=10,338 width=4) (actual time=30.210..30.210 rows=9,235 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 453kB
560. 27.278 27.278 ↑ 1.1 9,235 1

Seq Scan on checklist_item__entity cie_16 (cost=0.00..6,845.83 rows=10,338 width=4) (actual time=0.457..27.278 rows=9,235 loops=1)

  • Filter: ((checklist_item_id = ANY ('{18,19}'::integer[])) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 163098
561. 1.272 78.645 ↓ 0.0 0 1

Hash Anti Join (cost=6,911.58..24,186.53 rows=789 width=260) (actual time=78.645..78.645 rows=0 loops=1)

  • Hash Cond: (lse_42.loan_id = cie_17.entity_id)
562. 54.579 54.579 ↓ 4.4 3,544 1

Seq Scan on loan_settings_entity lse_42 (cost=0.00..17,256.76 rows=800 width=16) (actual time=0.048..54.579 rows=3,544 loops=1)

  • Filter: (loan_sub_status_id = 30)
  • Rows Removed by Filter: 384978
563. 1.392 22.794 ↑ 1.2 4,414 1

Hash (cost=6,845.83..6,845.83 rows=5,260 width=4) (actual time=22.794..22.794 rows=4,414 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 220kB
564. 21.402 21.402 ↑ 1.2 4,414 1

Seq Scan on checklist_item__entity cie_17 (cost=0.00..6,845.83 rows=5,260 width=4) (actual time=0.019..21.402 rows=4,414 loops=1)

  • Filter: ((checklist_item_id = 12) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 167919
565. 0.002 123.310 ↓ 0.0 0 1

Nested Loop (cost=13,701.06..32,077.15 rows=1 width=240) (actual time=123.310..123.310 rows=0 loops=1)

  • Join Filter: (lse_43.loan_sub_status_id = lsse_20.id)
566. 0.185 123.308 ↓ 0.0 0 1

Hash Left Join (cost=13,701.06..32,074.83 rows=1 width=32) (actual time=123.308..123.308 rows=0 loops=1)

  • Hash Cond: (lse_43.loan_id = ckl_7.entity_id)
  • Filter: (((lse_43.loan_sub_status_id = ANY ('{43,46}'::bigint[])) AND (cie_18.checklist_item_id IS NULL)) OR ((lse_43.loan_sub_status_id = 43) AND (ckl_7.checklist_item_id IS NULL)))
  • Rows Removed by Filter: 197
567. 0.237 96.348 ↑ 12.2 197 1

Hash Left Join (cost=6,850.53..25,149.37 rows=2,397 width=28) (actual time=20.466..96.348 rows=197 loops=1)

  • Hash Cond: (lse_43.loan_id = cie_18.entity_id)
568. 75.700 75.700 ↑ 12.2 197 1

Seq Scan on loan_settings_entity lse_43 (cost=0.00..18,223.92 rows=2,397 width=24) (actual time=0.035..75.700 rows=197 loops=1)

  • Filter: ((loan_sub_status_id = ANY ('{43,46}'::bigint[])) OR (loan_sub_status_id = 43))
  • Rows Removed by Filter: 388325
569. 0.097 20.411 ↑ 1.9 197 1

Hash (cost=6,845.83..6,845.83 rows=376 width=8) (actual time=20.411..20.411 rows=197 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
570. 20.314 20.314 ↑ 1.9 197 1

Seq Scan on checklist_item__entity cie_18 (cost=0.00..6,845.83 rows=376 width=8) (actual time=1.847..20.314 rows=197 loops=1)

  • Filter: ((checklist_item_id = 20) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 172136
571. 0.114 26.775 ↑ 1.9 196 1

Hash (cost=6,845.83..6,845.83 rows=376 width=8) (actual time=26.775..26.775 rows=196 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
572. 26.661 26.661 ↑ 1.9 196 1

Seq Scan on checklist_item__entity ckl_7 (cost=0.00..6,845.83 rows=376 width=8) (actual time=1.855..26.661 rows=196 loops=1)

  • Filter: ((checklist_item_id = 21) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 172137
573. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_sub_status_entity lsse_20 (cost=0.00..1.58 rows=58 width=16) (never executed)

574. 0.002 124.119 ↓ 0.0 0 1

Nested Loop (cost=13,701.06..32,077.15 rows=1 width=240) (actual time=124.119..124.119 rows=0 loops=1)

  • Join Filter: (lse_44.loan_sub_status_id = lsse_21.id)
575. 0.069 124.117 ↓ 0.0 0 1

Hash Left Join (cost=13,701.06..32,074.83 rows=1 width=32) (actual time=124.117..124.117 rows=0 loops=1)

  • Hash Cond: (lse_44.loan_id = ckl_8.entity_id)
  • Filter: (((lse_44.loan_sub_status_id = ANY ('{52,54}'::bigint[])) AND (cie_19.checklist_item_id IS NULL)) OR ((lse_44.loan_sub_status_id = 52) AND (ckl_8.checklist_item_id IS NULL)))
  • Rows Removed by Filter: 49
576. 0.100 101.484 ↑ 48.9 49 1

Hash Left Join (cost=6,850.53..25,149.37 rows=2,397 width=28) (actual time=29.368..101.484 rows=49 loops=1)

  • Hash Cond: (lse_44.loan_id = cie_19.entity_id)
577. 75.210 75.210 ↑ 48.9 49 1

Seq Scan on loan_settings_entity lse_44 (cost=0.00..18,223.92 rows=2,397 width=24) (actual time=3.167..75.210 rows=49 loops=1)

  • Filter: ((loan_sub_status_id = ANY ('{52,54}'::bigint[])) OR (loan_sub_status_id = 52))
  • Rows Removed by Filter: 388473
578. 0.042 26.174 ↑ 7.7 49 1

Hash (cost=6,845.83..6,845.83 rows=376 width=8) (actual time=26.174..26.174 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
579. 26.132 26.132 ↑ 7.7 49 1

Seq Scan on checklist_item__entity cie_19 (cost=0.00..6,845.83 rows=376 width=8) (actual time=2.214..26.132 rows=49 loops=1)

  • Filter: ((checklist_item_id = 25) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 172284
580. 0.029 22.564 ↑ 9.9 38 1

Hash (cost=6,845.83..6,845.83 rows=376 width=8) (actual time=22.564..22.564 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
581. 22.535 22.535 ↑ 9.9 38 1

Seq Scan on checklist_item__entity ckl_8 (cost=0.00..6,845.83 rows=376 width=8) (actual time=1.738..22.535 rows=38 loops=1)

  • Filter: ((checklist_item_id = 26) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 172295
582. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_sub_status_entity lsse_21 (cost=0.00..1.58 rows=58 width=16) (never executed)

583. 0.002 119.271 ↓ 0.0 0 1

Nested Loop (cost=13,777.72..32,154.28 rows=1 width=240) (actual time=119.270..119.271 rows=0 loops=1)

  • Join Filter: (lse_45.loan_sub_status_id = lsse_22.id)
584. 1.575 119.269 ↓ 0.0 0 1

Hash Left Join (cost=13,777.72..32,151.97 rows=1 width=32) (actual time=119.269..119.269 rows=0 loops=1)

  • Hash Cond: (lse_45.loan_id = ckl_9.entity_id)
  • Filter: (((lse_45.loan_sub_status_id = ANY ('{36,40}'::bigint[])) AND (cie_20.checklist_item_id IS NULL)) OR ((lse_45.loan_sub_status_id = 36) AND (ckl_9.checklist_item_id IS NULL)))
  • Rows Removed by Filter: 3380
585. 2.515 94.584 ↓ 1.4 3,380 1

Hash Left Join (cost=6,890.73..25,189.77 rows=2,397 width=28) (actual time=26.646..94.584 rows=3,380 loops=1)

  • Hash Cond: (lse_45.loan_id = cie_20.entity_id)
586. 65.583 65.583 ↓ 1.4 3,380 1

Seq Scan on loan_settings_entity lse_45 (cost=0.00..18,223.92 rows=2,397 width=24) (actual time=0.121..65.583 rows=3,380 loops=1)

  • Filter: ((loan_sub_status_id = ANY ('{36,40}'::bigint[])) OR (loan_sub_status_id = 36))
  • Rows Removed by Filter: 385142
587. 1.356 26.486 ↑ 1.1 3,381 1

Hash (cost=6,845.83..6,845.83 rows=3,592 width=8) (actual time=26.486..26.486 rows=3,381 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 165kB
588. 25.130 25.130 ↑ 1.1 3,381 1

Seq Scan on checklist_item__entity cie_20 (cost=0.00..6,845.83 rows=3,592 width=8) (actual time=0.007..25.130 rows=3,381 loops=1)

  • Filter: ((checklist_item_id = 9) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 168952
589. 1.098 23.110 ↓ 1.0 3,354 1

Hash (cost=6,845.83..6,845.83 rows=3,293 width=8) (actual time=23.110..23.110 rows=3,354 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 164kB
590. 22.012 22.012 ↓ 1.0 3,354 1

Seq Scan on checklist_item__entity ckl_9 (cost=0.00..6,845.83 rows=3,293 width=8) (actual time=0.012..22.012 rows=3,354 loops=1)

  • Filter: ((checklist_item_id = 10) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 168979
591. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_sub_status_entity lsse_22 (cost=0.00..1.58 rows=58 width=16) (never executed)

592. 0.003 90.734 ↓ 0.0 0 1

Nested Loop (cost=6,876.59..24,161.17 rows=795 width=240) (actual time=90.734..90.734 rows=0 loops=1)

593. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on loan_sub_status_entity lsse_23 (cost=0.00..1.73 rows=1 width=16) (actual time=0.009..0.014 rows=1 loops=1)

  • Filter: (id = 28)
  • Rows Removed by Filter: 57
594. 0.719 90.717 ↓ 0.0 0 1

Hash Anti Join (cost=6,876.59..24,143.55 rows=795 width=24) (actual time=90.716..90.717 rows=0 loops=1)

  • Hash Cond: (lse_46.loan_id = cie_21.entity_id)
595. 63.978 63.978 ↓ 1.8 1,462 1

Seq Scan on loan_settings_entity lse_46 (cost=0.00..17,256.76 rows=800 width=24) (actual time=0.024..63.978 rows=1,462 loops=1)

  • Filter: (loan_sub_status_id = 28)
  • Rows Removed by Filter: 387060
596. 0.941 26.020 ↑ 1.1 2,287 1

Hash (cost=6,845.83..6,845.83 rows=2,461 width=4) (actual time=26.020..26.020 rows=2,287 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 113kB
597. 25.079 25.079 ↑ 1.1 2,287 1

Seq Scan on checklist_item__entity cie_21 (cost=0.00..6,845.83 rows=2,461 width=4) (actual time=0.212..25.079 rows=2,287 loops=1)

  • Filter: ((checklist_item_id = 17) AND (checklist_item_value = 1) AND (deleted = 0))
  • Rows Removed by Filter: 170046
598. 0.029 41.214 ↑ 305.0 1 1

Hash Join (cost=6.19..21,034.60 rows=305 width=208) (actual time=33.009..41.214 rows=1 loops=1)

  • Hash Cond: (lsa2_1.loan_sub_status_id = lsse2_1.id)
599. 0.024 41.140 ↑ 305.0 1 1

Hash Join (cost=3.88..21,026.58 rows=305 width=44) (actual time=32.936..41.140 rows=1 loops=1)

  • Hash Cond: (lsa_1.loan_sub_status_id = lsse_24.id)
600. 0.005 41.069 ↑ 305.0 1 1

Nested Loop (cost=1.57..21,020.08 rows=305 width=36) (actual time=32.866..41.069 rows=1 loops=1)

  • Join Filter: (cie_22.entity_id = lse_47.loan_id)
601. 0.014 40.451 ↑ 305.0 1 1

Nested Loop (cost=1.15..19,493.23 rows=305 width=48) (actual time=32.250..40.451 rows=1 loops=1)

602. 0.037 39.121 ↑ 250.0 2 1

Nested Loop (cost=0.57..15,191.35 rows=500 width=36) (actual time=31.539..39.121 rows=2 loops=1)

603. 24.174 24.174 ↑ 117.1 7 1

Seq Scan on checklist_item__entity cie_22 (cost=0.00..8,138.32 rows=820 width=24) (actual time=24.124..24.174 rows=7 loops=1)

  • Filter: ((checklist_item_id = 17) AND (checklist_item_value = 1) AND (deleted = 0) AND ((lastupdated)::date >= '2019-10-08'::date))
  • Rows Removed by Filter: 172326
604. 14.910 14.910 ↓ 0.0 0 7

Index Scan using loan_status_archive_loan_id_date on loan_status_archive lsa_1 (cost=0.57..8.60 rows=1 width=16) (actual time=2.130..2.130 rows=0 loops=7)

  • Index Cond: ((loan_id = cie_22.entity_id) AND (date = (cie_22.lastupdated)::date))
  • Filter: (loan_sub_status_id <> ALL ('{30,32,33,44,55}'::integer[]))
  • Rows Removed by Filter: 1
605. 1.316 1.316 ↓ 0.0 0 2

Index Scan using loan_status_archive_loan_id_date on loan_status_archive lsa2_1 (cost=0.58..8.60 rows=1 width=16) (actual time=0.658..0.658 rows=0 loops=2)

  • Index Cond: ((loan_id = cie_22.entity_id) AND (date = ((cie_22.lastupdated)::date - 1)))
  • Filter: (loan_sub_status_id <> ALL ('{30,32,33,44,55}'::integer[]))
  • Rows Removed by Filter: 0
606. 0.613 0.613 ↑ 1.0 1 1

Index Only Scan using loan_settings_entity_loan_id on loan_settings_entity lse_47 (cost=0.42..4.99 rows=1 width=8) (actual time=0.612..0.613 rows=1 loops=1)

  • Index Cond: (loan_id = lsa_1.loan_id)
  • Heap Fetches: 1
607. 0.025 0.047 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.047..0.047 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
608. 0.022 0.022 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_24 (cost=0.00..1.58 rows=58 width=16) (actual time=0.007..0.022 rows=58 loops=1)

609. 0.024 0.045 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.044..0.045 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
610. 0.021 0.021 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse2_1 (cost=0.00..1.58 rows=58 width=16) (actual time=0.006..0.021 rows=58 loops=1)

611. 0.031 3,409.117 ↑ 8,646.0 1 1

Hash Join (cost=139,183.48..190,635.80 rows=8,646 width=219) (actual time=3,360.721..3,409.117 rows=1 loops=1)

  • Hash Cond: (lse_48.loan_sub_status_id = lsse_25.id)
612. 0.010 3,409.043 ↑ 8,646.0 1 1

Nested Loop Anti Join (cost=139,181.17..190,428.16 rows=8,646 width=31) (actual time=3,360.647..3,409.043 rows=1 loops=1)

  • Join Filter: (pe2_1.id > pe_16.id)
  • Rows Removed by Join Filter: 1
613. 157.683 3,408.331 ↑ 9,193.0 1 1

Hash Join (cost=139,180.75..163,327.74 rows=9,193 width=35) (actual time=3,359.937..3,408.331 rows=1 loops=1)

  • Hash Cond: (lscf_19.loan_settings_id = lse_48.id)
  • Join Filter: ((lscf_19.notice_of_cancel_date)::date >= pe_16.apply_date)
  • Rows Removed by Join Filter: 39240
614. 107.399 107.399 ↑ 1.0 388,522 1

Seq Scan on loan_settings_custom_fields lscf_19 (cost=0.00..22,203.26 rows=389,626 width=15) (actual time=0.003..107.399 rows=388,522 loops=1)

615. 18.059 3,143.249 ↓ 1.4 39,241 1

Hash (cost=138,836.01..138,836.01 rows=27,579 width=32) (actual time=3,143.248..3,143.249 rows=39,241 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2965kB
616. 75.223 3,125.190 ↓ 1.4 39,241 1

Hash Join (cost=21,062.38..138,836.01 rows=27,579 width=32) (actual time=259.652..3,125.190 rows=39,241 loops=1)

  • Hash Cond: (pe_16.entity_id = lse_48.loan_id)
617. 2,830.295 2,830.295 ↓ 1.1 74,339 1

Seq Scan on payment_entity pe_16 (cost=0.00..115,659.05 rows=69,012 width=12) (actual time=0.607..2,830.295 rows=74,339 loops=1)

  • Filter: (((status)::text <> 'payment.status.voided'::text) AND (payoff_flag = 1) AND (deleted = 0))
  • Rows Removed by Filter: 1526679
618. 64.790 219.672 ↑ 1.0 152,702 1

Hash (cost=18,223.92..18,223.92 rows=154,597 width=24) (actual time=219.672..219.672 rows=152,702 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2612kB
619. 154.882 154.882 ↑ 1.0 152,702 1

Seq Scan on loan_settings_entity lse_48 (cost=0.00..18,223.92 rows=154,597 width=24) (actual time=0.009..154.882 rows=152,702 loops=1)

  • Filter: ((loan_sub_status_id <> ALL ('{36,40}'::bigint[])) AND (closed_date >= '2018-08-09'::date))
  • Rows Removed by Filter: 235820
620. 0.702 0.702 ↑ 1.0 1 1

Index Scan using payment_entity_entity_id on payment_entity pe2_1 (cost=0.43..2.94 rows=1 width=8) (actual time=0.701..0.702 rows=1 loops=1)

  • Index Cond: (lse_48.loan_id = entity_id)
  • Filter: (((status)::text <> 'payment.status.voided'::text) AND (payoff_flag = 1) AND (deleted = 0))
621. 0.024 0.043 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.043..0.043 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
622. 0.019 0.019 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_25 (cost=0.00..1.58 rows=58 width=16) (actual time=0.005..0.019 rows=58 loops=1)

623. 0.069 2,572.337 ↑ 190.3 29 1

Hash Join (cost=144,955.34..184,191.58 rows=5,520 width=240) (actual time=2,276.055..2,572.337 rows=29 loops=1)

  • Hash Cond: (lse_49.loan_sub_status_id = lsse_26.id)
624. 36.117 2,572.222 ↑ 190.3 29 1

Hash Left Join (cost=144,953.04..184,016.77 rows=5,520 width=93) (actual time=2,275.985..2,572.222 rows=29 loops=1)

  • Hash Cond: (cpe_2.entity_id = cpe2_2.entity_id)
  • Join Filter: (cpe2_2.phone <> cpe_2.phone)
  • Rows Removed by Join Filter: 9688
  • Filter: (((cpe_2.phone IS NOT NULL) AND (ccf_2.dnccell = '1'::text) AND (cpe_2.dnd_enabled = 0)) OR ((cpe2_2.phone IS NOT NULL) AND (ccf_2.dnccell = '1'::text) AND (cpe2_2.dnd_enabled = 0)) OR ((cpe3_2.phone IS NOT NULL) AND (ccf_2.dnchome = '1'::text) AND (cpe3_2.dnd_enabled = 0)))
  • Rows Removed by Filter: 9401
625. 75.087 2,082.332 ↓ 1.3 9,423 1

Hash Right Join (cost=104,784.96..140,524.68 rows=7,073 width=72) (actual time=1,760.906..2,082.332 rows=9,423 loops=1)

  • Hash Cond: (cpe3_2.entity_id = lc_10.customer_id)
626. 246.452 246.452 ↓ 1.0 256,407 1

Seq Scan on customer_phone_entity cpe3_2 (cost=0.00..34,730.17 rows=251,165 width=29) (actual time=0.012..246.452 rows=256,407 loops=1)

  • Filter: ((deleted = 0) AND (type = 'customer.phoneType.home'::bpchar))
  • Rows Removed by Filter: 298204
627. 5.723 1,760.793 ↓ 1.3 9,347 1

Hash (cost=104,696.55..104,696.55 rows=7,073 width=51) (actual time=1,760.793..1,760.793 rows=9,347 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 930kB
628. 89.243 1,755.070 ↓ 1.3 9,347 1

Merge Left Join (cost=96,229.86..104,696.55 rows=7,073 width=51) (actual time=997.408..1,755.070 rows=9,347 loops=1)

  • Merge Cond: (lc_10.customer_id = cpe_2.entity_id)
629. 9.036 458.109 ↓ 1.3 9,347 1

Sort (cost=29,319.54..29,337.22 rows=7,073 width=22) (actual time=455.030..458.109 rows=9,347 loops=1)

  • Sort Key: lc_10.customer_id
  • Sort Method: quicksort Memory: 1115kB
630. 15.630 449.073 ↓ 1.3 9,347 1

Hash Join (cost=14,329.94..28,867.29 rows=7,073 width=22) (actual time=168.435..449.073 rows=9,347 loops=1)

  • Hash Cond: (lc_10.loan_id = lse_49.loan_id)
631. 149.921 349.698 ↑ 2.4 22,245 1

Hash Join (cost=8,505.67..22,772.45 rows=53,290 width=10) (actual time=84.332..349.698 rows=22,245 loops=1)

  • Hash Cond: (lc_10.customer_id = ccf_2.customer_id)
632. 115.845 115.845 ↓ 1.0 388,528 1

Seq Scan on loan__customer lc_10 (cost=0.00..13,250.08 rows=387,308 width=8) (actual time=0.007..115.845 rows=388,528 loops=1)

633. 5.689 83.932 ↑ 2.6 14,597 1

Hash (cost=8,037.25..8,037.25 rows=37,474 width=6) (actual time=83.932..83.932 rows=14,597 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1083kB
634. 78.243 78.243 ↑ 2.6 14,597 1

Seq Scan on customer_custom_fields ccf_2 (cost=0.00..8,037.25 rows=37,474 width=6) (actual time=0.028..78.243 rows=14,597 loops=1)

  • Filter: ((dnccell = '1'::text) OR (dnccell = '1'::text) OR (dnchome = '1'::text))
  • Rows Removed by Filter: 259092
635. 24.150 83.745 ↓ 1.0 52,303 1

Hash (cost=5,182.25..5,182.25 rows=51,362 width=16) (actual time=83.745..83.745 rows=52,303 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2964kB
636. 59.595 59.595 ↓ 1.0 52,303 1

Index Scan using loan_settings_entity_id_cond on loan_settings_entity lse_49 (cost=0.29..5,182.25 rows=51,362 width=16) (actual time=0.743..59.595 rows=52,303 loops=1)

637. 155.907 1,207.718 ↓ 191.3 269,035 1

Materialize (cost=66,910.32..75,349.63 rows=1,406 width=29) (actual time=542.219..1,207.718 rows=269,035 loops=1)

638. 136.732 1,051.811 ↓ 191.3 269,034 1

Subquery Scan on cpe_2 (cost=66,910.32..75,346.11 rows=1,406 width=29) (actual time=542.213..1,051.811 rows=269,034 loops=1)

  • Filter: (cpe_2.row_num = 1)
  • Rows Removed by Filter: 11490
639. 277.789 915.079 ↑ 1.0 280,524 1

WindowAgg (cost=66,910.32..71,831.20 rows=281,193 width=37) (actual time=542.212..915.079 rows=280,524 loops=1)

640. 337.929 637.290 ↑ 1.0 280,524 1

Sort (cost=66,910.32..67,613.30 rows=281,193 width=29) (actual time=542.204..637.290 rows=280,524 loops=1)

  • Sort Key: customer_phone_entity_2.entity_id
  • Sort Method: external merge Disk: 11672kB
641. 299.361 299.361 ↓ 1.0 283,770 1

Seq Scan on customer_phone_entity customer_phone_entity_2 (cost=0.00..34,730.17 rows=281,193 width=29) (actual time=0.009..299.361 rows=283,770 loops=1)

  • Filter: ((deleted = 0) AND (type = 'customer.phoneType.cell'::bpchar))
  • Rows Removed by Filter: 270841
642. 141.225 453.773 ↓ 1.0 283,770 1

Hash (cost=34,730.17..34,730.17 rows=281,193 width=29) (actual time=453.773..453.773 rows=283,770 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2735kB
643. 312.548 312.548 ↓ 1.0 283,770 1

Seq Scan on customer_phone_entity cpe2_2 (cost=0.00..34,730.17 rows=281,193 width=29) (actual time=0.004..312.548 rows=283,770 loops=1)

  • Filter: ((deleted = 0) AND (type = 'customer.phoneType.cell'::bpchar))
  • Rows Removed by Filter: 270841
644. 0.023 0.046 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.046..0.046 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
645. 0.023 0.023 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_26 (cost=0.00..1.58 rows=58 width=16) (actual time=0.008..0.023 rows=58 loops=1)

646. 562.847 1,090.206 ↓ 0.0 0 1

Hash Join (cost=28,595.88..39,602.03 rows=94,164 width=231) (actual time=1,090.205..1,090.206 rows=0 loops=1)

  • Hash Cond: (lse_50.id = cf_6.loan_settings_id)
  • Join Filter: (dw_reporting_meta.text_to_date(cf_6.debt_sale_date) <> lse_50.closed_date)
  • Rows Removed by Join Filter: 93397
647. 75.042 75.042 ↑ 1.0 93,397 1

Index Scan using loan_settings_entity_idx_debt_sale on loan_settings_entity lse_50 (cost=0.29..7,189.47 rows=94,226 width=20) (actual time=1.866..75.042 rows=93,397 loops=1)

648. 164.555 452.317 ↑ 1.0 388,522 1

Hash (cost=22,203.26..22,203.26 rows=389,626 width=7) (actual time=452.317..452.317 rows=388,522 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3014kB
649. 287.762 287.762 ↑ 1.0 388,522 1

Seq Scan on loan_settings_custom_fields cf_6 (cost=0.00..22,203.26 rows=389,626 width=7) (actual time=0.007..287.762 rows=388,522 loops=1)

650. 0.019 423.024 ↓ 0.0 0 1

Hash Join (cost=23,088.08..46,218.58 rows=69,950 width=211) (actual time=423.024..423.024 rows=0 loops=1)

  • Hash Cond: (lse_51.loan_sub_status_id = lsse_27.id)
651. 130.249 422.955 ↓ 0.0 0 1

Hash Join (cost=23,085.78..44,904.72 rows=69,950 width=19) (actual time=422.955..422.955 rows=0 loops=1)

  • Hash Cond: (lse_51.id = cf_7.loan_settings_id)
652. 136.093 136.093 ↓ 1.0 295,125 1

Seq Scan on loan_settings_entity lse_51 (cost=0.00..17,256.76 rows=292,635 width=24) (actual time=0.005..136.093 rows=295,125 loops=1)

  • Filter: (loan_sub_status_id <> 35)
  • Rows Removed by Filter: 93397
653. 34.198 156.613 ↓ 1.0 93,387 1

Hash (cost=21,557.60..21,557.60 rows=93,134 width=7) (actual time=156.613..156.613 rows=93,387 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3218kB
654. 80.328 122.415 ↓ 1.0 93,387 1

Bitmap Heap Scan on loan_settings_custom_fields cf_7 (cost=2,086.43..21,557.60 rows=93,134 width=7) (actual time=44.131..122.415 rows=93,387 loops=1)

  • Recheck Cond: (debt_sale_date <> ''::text)
  • Heap Blocks: exact=13594
655. 42.087 42.087 ↓ 1.0 93,399 1

Bitmap Index Scan on loan_settings_custom_fields_loan_settings_id_cond5 (cost=0.00..2,063.14 rows=93,134 width=0) (actual time=42.087..42.087 rows=93,399 loops=1)

656. 0.023 0.050 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.050..0.050 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
657. 0.027 0.027 ↑ 1.0 58 1

Seq Scan on loan_sub_status_entity lsse_27 (cost=0.00..1.58 rows=58 width=16) (actual time=0.011..0.027 rows=58 loops=1)

658. 180.752 2,278.776 ↓ 0.0 0 1

Nested Loop (cost=14,167.95..38,464.04 rows=11,006 width=241) (actual time=2,278.775..2,278.776 rows=0 loops=1)

  • Join Filter: ((pe_17.apply_date >= lse_52.closed_date) AND (lse_52.loan_id = pe_17.entity_id))
  • Rows Removed by Join Filter: 310744
659. 86.438 1,257.496 ↓ 11.8 93,392 1

Hash Join (cost=14,167.53..27,311.16 rows=7,943 width=33) (actual time=633.215..1,257.496 rows=93,392 loops=1)

  • Hash Cond: (lsp.sub_portfolio_id = spe.id)
660. 350.376 1,170.331 ↓ 5.6 200,353 1

Hash Join (cost=14,140.56..27,070.91 rows=35,697 width=24) (actual time=632.465..1,170.331 rows=200,353 loops=1)

  • Hash Cond: (lsp.loan_id = lse_52.loan_id)
661. 191.241 191.241 ↑ 1.0 595,809 1

Seq Scan on loan__sub_portfolio lsp (cost=0.00..10,339.09 rows=595,809 width=8) (actual time=1.476..191.241 rows=595,809 loops=1)

662. 47.937 628.714 ↓ 4.0 93,393 1

Hash (cost=13,850.84..13,850.84 rows=23,178 width=16) (actual time=628.714..628.714 rows=93,393 loops=1)

  • Buckets: 131072 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3213kB
663. 99.070 580.777 ↓ 4.0 93,393 1

Hash Join (cost=8,828.58..13,850.84 rows=23,178 width=16) (actual time=103.338..580.777 rows=93,393 loops=1)

  • Hash Cond: (lp_12.loan_id = lse_52.loan_id)
664. 380.596 380.596 ↑ 1.0 93,393 1

Index Only Scan using loan__portfolio_loan_id_idx_cond_debt_sale on loan__portfolio lp_12 (cost=0.29..3,205.67 rows=97,092 width=4) (actual time=1.763..380.596 rows=93,393 loops=1)

  • Heap Fetches: 93393
665. 35.440 101.111 ↑ 1.0 93,397 1

Hash (cost=7,189.47..7,189.47 rows=94,226 width=12) (actual time=101.111..101.111 rows=93,397 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3031kB
666. 65.671 65.671 ↑ 1.0 93,397 1

Index Scan using loan_settings_entity_idx_debt_sale on loan_settings_entity lse_52 (cost=0.29..7,189.47 rows=94,226 width=12) (actual time=0.018..65.671 rows=93,397 loops=1)

667. 0.012 0.727 ↑ 61.8 4 1

Hash (cost=23.88..23.88 rows=247 width=17) (actual time=0.727..0.727 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
668. 0.715 0.715 ↑ 61.8 4 1

Seq Scan on sub_portfolio_entity spe (cost=0.00..23.88 rows=247 width=17) (actual time=0.712..0.715 rows=4 loops=1)

  • Filter: (parent = 6)
  • Rows Removed by Filter: 14
669. 840.528 840.528 ↑ 3.0 3 93,392

Index Scan using payment_entity_entity_id on payment_entity pe_17 (cost=0.43..1.25 rows=9 width=8) (actual time=0.005..0.009 rows=3 loops=93,392)

  • Index Cond: (entity_id = lsp.loan_id)
  • Filter: ((reverse_reason IS NULL) AND (deleted = 0))
  • Rows Removed by Filter: 2
670. 2.832 902.646 ↑ 18.3 3,960 1

Hash Left Join (cost=37,993.74..47,989.35 rows=72,415 width=190) (actual time=755.751..902.646 rows=3,960 loops=1)

  • Hash Cond: (lse_53.loan_id = dsdl.loan_id)
671. 35.871 899.629 ↑ 18.3 3,960 1

Hash Right Join (cost=37,982.05..46,347.61 rows=72,415 width=30) (actual time=755.540..899.629 rows=3,960 loops=1)

  • Hash Cond: (lp_13.loan_id = lse_53.loan_id)
672. 71.930 502.834 ↓ 2.9 93,392 1

Hash Join (cost=16,360.70..23,735.14 rows=32,612 width=17) (actual time=382.782..502.834 rows=93,392 loops=1)

  • Hash Cond: (lp_13.loan_id = lsp_1.loan_id)
673. 49.029 49.029 ↑ 1.0 93,393 1

Index Only Scan using loan__portfolio_loan_id_idx_cond_debt_sale on loan__portfolio lp_13 (cost=0.29..3,205.67 rows=97,092 width=4) (actual time=0.021..49.029 rows=93,393 loops=1)

  • Heap Fetches: 93393
674. 42.105 381.875 ↑ 1.4 93,396 1

Hash (cost=13,926.15..13,926.15 rows=132,581 width=17) (actual time=381.875..381.875 rows=93,396 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2030kB
675. 188.215 339.770 ↑ 1.4 93,396 1

Hash Join (cost=26.96..13,926.15 rows=132,581 width=17) (actual time=0.034..339.770 rows=93,396 loops=1)

  • Hash Cond: (lsp_1.sub_portfolio_id = sub.id)
676. 151.544 151.544 ↑ 1.0 595,809 1

Seq Scan on loan__sub_portfolio lsp_1 (cost=0.00..10,339.09 rows=595,809 width=8) (actual time=0.007..151.544 rows=595,809 loops=1)

677. 0.004 0.011 ↑ 61.8 4 1

Hash (cost=23.88..23.88 rows=247 width=17) (actual time=0.011..0.011 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
678. 0.007 0.007 ↑ 61.8 4 1

Seq Scan on sub_portfolio_entity sub (cost=0.00..23.88 rows=247 width=17) (actual time=0.005..0.007 rows=4 loops=1)

  • Filter: (parent = 6)
  • Rows Removed by Filter: 14
679. 2.070 360.924 ↑ 18.3 3,960 1

Hash (cost=20,291.16..20,291.16 rows=72,415 width=17) (actual time=360.924..360.924 rows=3,960 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 595kB
680. 45.171 358.854 ↑ 18.3 3,960 1

Hash Anti Join (cost=10,609.64..20,291.16 rows=72,415 width=17) (actual time=259.012..358.854 rows=3,960 loops=1)

  • Hash Cond: (lse_53.loan_id = dsl.loanid)
681. 60.834 60.834 ↑ 1.0 93,397 1

Index Scan using loan_settings_entity_idx_debt_sale on loan_settings_entity lse_53 (cost=0.29..7,189.47 rows=94,226 width=12) (actual time=0.019..60.834 rows=93,397 loops=1)

682. 35.101 252.849 ↑ 1.0 89,526 1

Hash (cost=9,052.01..9,052.01 rows=89,547 width=9) (actual time=252.849..252.849 rows=89,526 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2851kB
683. 46.395 217.748 ↑ 1.0 89,526 1

Hash Anti Join (cost=12.65..9,052.01 rows=89,547 width=9) (actual time=3.707..217.748 rows=89,526 loops=1)

  • Hash Cond: ((dsl.loanid = dsd.loan_id) AND (dsl.saledate = dsd.sale_date))
  • Join Filter: (dsl.misc2 = 'LP'::text)
684. 169.915 169.915 ↑ 1.0 89,742 1

Seq Scan on debt_sale_list dsl (cost=0.00..7,443.60 rows=89,799 width=16) (actual time=2.245..169.915 rows=89,742 loops=1)

  • Filter: (misc2 = 'LP'::text)
  • Rows Removed by Filter: 866
685. 0.101 1.438 ↑ 1.3 297 1

Hash (cost=6.86..6.86 rows=386 width=8) (actual time=1.437..1.438 rows=297 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
686. 1.337 1.337 ↑ 1.2 311 1

Seq Scan on debt_sale_discarded_loans dsd (cost=0.00..6.86 rows=386 width=8) (actual time=1.241..1.337 rows=311 loops=1)

687. 0.093 0.185 ↑ 1.3 297 1

Hash (cost=6.86..6.86 rows=386 width=12) (actual time=0.185..0.185 rows=297 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
688. 0.092 0.092 ↑ 1.2 311 1

Seq Scan on debt_sale_discarded_loans dsdl (cost=0.00..6.86 rows=386 width=12) (actual time=0.009..0.092 rows=311 loops=1)

689. 0.155 594.584 ↑ 824.0 82 1

Hash Join (cost=9,371.04..36,688.40 rows=67,565 width=208) (actual time=179.033..594.584 rows=82 loops=1)

  • Hash Cond: (lse_54.loan_sub_status_id = lsse_28.id)
690. 0.239 594.380 ↑ 824.0 82 1

Hash Anti Join (cost=9,368.74..35,081.43 rows=67,565 width=52) (actual time=178.959..594.380 rows=82 loops=1)

  • Hash Cond: ((dsl_1.loanid = dsd_1.loan_id) AND (dsl_1.saledate = dsd_1.sale_date))
  • Join Filter: (dsl_1.misc2 = 'LP'::text)
691. 194.131 593.782 ↑ 251.9 269 1

Hash Join (cost=9,356.09..33,864.75 rows=67,755 width=59) (actual time=178.578..593.782 rows=269 loops=1)

  • Hash Cond: (lse_54.loan_id = dsl_1.loanid)
692. 225.186 225.186 ↓ 1.0 295,125 1

Seq Scan on loan_settings_entity lse_54 (cost=0.00..17,256.76 rows=292,635 width=16) (actual time=0.005..225.186 rows=295,125 loops=1)

  • Filter: (loan_sub_status_id <> 35)
  • Rows Removed by Filter: 93397
693. 61.144 174.465 ↑ 1.0 89,742 1

Hash (cost=7,443.60..7,443.60 rows=89,799 width=43) (actual time=174.465..174.465 rows=89,742 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2210kB
694. 113.321 113.321 ↑ 1.0 89,742 1

Seq Scan on debt_sale_list dsl_1 (cost=0.00..7,443.60 rows=89,799 width=43) (actual time=0.008..113.321 rows=89,742 loops=1)

  • Filter: (misc2 = 'LP'::text)
  • Rows Removed by Filter: 866
695. 0.175 0.359 ↑ 1.3 297 1

Hash (cost=6.86..6.86 rows=386 width=8) (actual time=0.359..0.359 rows=297 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
696. 0.184 0.184 ↑ 1.2 311 1

Seq Scan on debt_sale_discarded_loans dsd_1 (cost=0.00..6.86 rows=386 width=8) (actual time=0.012..0.184 rows=311 loops=1)

697. 0.021 0.049 ↑ 1.0 58 1

Hash (cost=1.58..1.58 rows=58 width=16) (actual time=0.049..0.049 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
698. 0.028 0.028 ↑ 1.0 58 1

Seq Scan