explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eUXL : Optimization for: Optimization for: plan #bF8m; plan #nXu6

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.246 940,324.256 ↑ 134,249.3 154 1

Append (cost=19,936.87..7,151,585.92 rows=20,674,392 width=116) (actual time=7,577.702..940,324.256 rows=154 loops=1)

2. 0.608 0.642 ↓ 0.0 0 1

Hash Join (cost=19,936.87..37,301.36 rows=67,750 width=92) (actual time=0.642..0.642 rows=0 loops=1)

  • Hash Cond: (ls.id = cf.loan_settings_id)
3. 0.006 0.006 ↑ 384,563.0 1 1

Seq Scan on loan_settings_entity ls (cost=0.00..16,185.63 rows=384,563 width=24) (actual time=0.005..0.006 rows=1 loops=1)

4. 0.001 0.028 ↓ 0.0 0 1

Hash (cost=19,085.49..19,085.49 rows=68,110 width=12) (actual time=0.027..0.028 rows=0 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 1,024kB
5. 0.005 0.027 ↓ 0.0 0 1

Bitmap Heap Scan on loan_settings_custom_fields cf (cost=42.31..19,085.49 rows=68,110 width=12) (actual time=0.026..0.027 rows=0 loops=1)

  • Recheck Cond: (((esigned = '1'::text) AND ((esigned_date IS NULL) OR (esigned_date = ''::text))) OR ((esigned = '2'::text) AND (esigned_date IS NOT NULL) AND (esigned_date <> ''::text)))
6. 0.001 0.022 ↓ 0.0 0 1

BitmapOr (cost=42.31..42.31 rows=68,809 width=0) (actual time=0.022..0.022 rows=0 loops=1)

7. 0.014 0.014 ↓ 0.0 0 1

Bitmap Index Scan on loan_settings_custom_fields_loan_settings_id_cond (cost=0.00..4.13 rows=64,628 width=0) (actual time=0.014..0.014 rows=0 loops=1)

8. 0.007 0.007 ↓ 0.0 0 1

Bitmap Index Scan on loan_settings_custom_fields_loan_settings_id_cond1 (cost=0.00..4.13 rows=4,181 width=0) (actual time=0.007..0.007 rows=0 loops=1)

9. 77.207 6,601.114 ↓ 0.0 0 1

Hash Join (cost=7,905.71..28,055.00 rows=18,986 width=91) (actual time=6,601.113..6,601.114 rows=0 loops=1)

  • Hash Cond: (cf_1.loan_settings_id = lse.id)
10. 6,206.102 6,222.028 ↓ 1.0 142,261 1

Bitmap Heap Scan on loan_settings_custom_fields cf_1 (cost=2,301.05..21,731.47 rows=141,073 width=11) (actual time=19.465..6,222.028 rows=142,261 loops=1)

  • Recheck Cond: ((funded_date IS NULL) OR (funded_date = ''::text))
  • Heap Blocks: exact=16,111
11. 15.926 15.926 ↓ 1.0 142,426 1

Bitmap Index Scan on loan_settings_custom_fields_loan_settings_id_indx1 (cost=0.00..2,265.78 rows=141,073 width=0) (actual time=15.925..15.926 rows=142,426 loops=1)

12. 28.199 301.879 ↓ 1.0 53,496 1

Hash (cost=4,954.27..4,954.27 rows=52,031 width=24) (actual time=301.878..301.879 rows=53,496 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,438kB
13. 273.680 273.680 ↓ 1.0 53,496 1

Index Scan using loan_settings_entity_id_cond on loan_settings_entity lse (cost=0.29..4,954.27 rows=52,031 width=24) (actual time=0.073..273.680 rows=53,496 loops=1)

14. 144.137 976.651 ↑ 130.0 2 1

Nested Loop (cost=0.84..14,670.35 rows=260 width=91) (actual time=975.942..976.651 rows=2 loops=1)

15. 150.809 150.809 ↓ 70.5 136,341 1

Index Scan using loan_settings_custom_fields_loan_settings_id_cond3 on loan_settings_custom_fields cf_2 (cost=0.42..2,127.67 rows=1,933 width=11) (actual time=0.787..150.809 rows=136,341 loops=1)

16. 681.705 681.705 ↓ 0.0 0 136,341

Index Scan using loan_settings_entity_id on loan_settings_entity lse_1 (cost=0.42..6.48 rows=1 width=24) (actual time=0.005..0.005 rows=0 loops=136,341)

  • Index Cond: (id = cf_2.loan_settings_id)
  • Filter: (loan_status_id = ANY ('{3,4}'::bigint[]))
  • Rows Removed by Filter: 1
17. 0.003 4.875 ↓ 0.0 0 1

Nested Loop (cost=1.09..15,655.54 rows=1,922 width=95) (actual time=4.874..4.875 rows=0 loops=1)

18. 0.006 2.156 ↑ 1,923.0 1 1

Nested Loop (cost=0.67..14,616.52 rows=1,923 width=27) (actual time=0.094..2.156 rows=1 loops=1)

19. 2.133 2.133 ↑ 1,933.0 1 1

Index Scan using loan_settings_custom_fields_loan_settings_id_cond4 on loan_settings_custom_fields cf_3 (cost=0.25..2,073.84 rows=1,933 width=15) (actual time=0.073..2.133 rows=1 loops=1)

20. 0.017 0.017 ↑ 1.0 1 1

Index Scan using loan_settings_entity_id on loan_settings_entity lse_2 (cost=0.42..6.48 rows=1 width=24) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (id = cf_3.loan_settings_id)
  • Filter: (loan_id > 2)
21. 2.716 2.716 ↓ 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=2.715..2.716 rows=0 loops=1)

  • Index Cond: (id = lse_2.loan_id)
  • Filter: (deleted = 0)
  • Rows Removed by Filter: 1
22. 0.001 19,812.823 ↓ 0.0 0 1

Subquery Scan on *SELECT* 5 (cost=1,196.84..100,202.42 rows=548 width=92) (actual time=19,812.823..19,812.823 rows=0 loops=1)

23. 274.682 19,812.822 ↓ 0.0 0 1

Nested Loop (cost=1,196.84..100,194.20 rows=548 width=84) (actual time=19,812.821..19,812.822 rows=0 loops=1)

24. 125.577 2,442.672 ↓ 16.6 161,278 1

Nested Loop (cost=1,196.27..22,420.03 rows=9,732 width=21) (actual time=21.088..2,442.672 rows=161,278 loops=1)

25. 0.045 1.311 ↓ 4.0 4 1

Hash Join (cost=1.19..3.11 rows=1 width=21) (actual time=1.253..1.311 rows=4 loops=1)

  • Hash Cond: (pe.category_id = pce.id)
  • Join Filter: (((pce.title = 'Servicer'::text) AND (pe.title = ANY ('{Promovere,""Ohio Servicing""}'::text[]))) OR ((pce.title = 'Owner'::text) AND (pe.title = ANY ('{Bastion,NCP}'::text[]))))
26. 0.652 0.652 ↑ 1.0 4 1

Seq Scan on portfolio_entity pe (cost=0.00..1.89 rows=4 width=16) (actual time=0.616..0.652 rows=4 loops=1)

  • Filter: ((title = ANY ('{Promovere,""Ohio Servicing""}'::text[])) OR (title = ANY ('{Bastion,NCP}'::text[])))
  • Rows Removed by Filter: 55
27. 0.008 0.614 ↓ 2.0 2 1

Hash (cost=1.18..1.18 rows=1 width=13) (actual time=0.614..0.614 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 0.606 0.606 ↓ 2.0 2 1

Seq Scan on portfolio_category_entity pce (cost=0.00..1.18 rows=1 width=13) (actual time=0.601..0.606 rows=2 loops=1)

  • Filter: ((title = ANY ('{Servicer,Owner}'::text[])) AND ((title = 'Servicer'::text) OR (title = 'Owner'::text)))
  • Rows Removed by Filter: 8
29. 2,273.672 2,315.784 ↑ 1.6 40,320 4

Bitmap Heap Scan on loan__portfolio lp (cost=1,195.09..21,761.89 rows=65,504 width=8) (actual time=13.000..578.946 rows=40,320 loops=4)

  • Recheck Cond: (portfolio_id = pe.id)
  • Heap Blocks: exact=43,338
30. 42.112 42.112 ↑ 1.6 40,320 4

Bitmap Index Scan on loan__portfolio_portfolio_id (cost=0.00..1,178.71 rows=65,504 width=0) (actual time=10.528..10.528 rows=40,320 loops=4)

  • Index Cond: (portfolio_id = pe.id)
31. 17,095.468 17,095.468 ↓ 0.0 0 161,278

Index Scan using loan_status_archive_loan_id_cond1_rename on loan_status_archive lsa (cost=0.56..7.99 rows=1 width=12) (actual time=0.106..0.106 rows=0 loops=161,278)

  • Index Cond: ((loan_id = lp.loan_id) AND (date = CURRENT_DATE))
32. 0.002 2,942.438 ↓ 0.0 0 1

Subquery Scan on *SELECT* 6 (cost=415.75..13,538.67 rows=15 width=116) (actual time=2,942.438..2,942.438 rows=0 loops=1)

33. 0.001 2,942.436 ↓ 0.0 0 1

Nested Loop (cost=415.75..13,538.49 rows=15 width=112) (actual time=2,942.436..2,942.436 rows=0 loops=1)

  • Join Filter: (lae.loan_id = lse_3.loan_id)
34. 0.027 2,942.435 ↓ 0.0 0 1

Hash Join (cost=415.33..13,526.66 rows=15 width=29) (actual time=2,942.434..2,942.435 rows=0 loops=1)

  • Hash Cond: (lae.primary_payment_method_id = cp.id)
35. 5.746 2,932.040 ↑ 153.2 4 1

Nested Loop (cost=223.86..13,332.74 rows=613 width=33) (actual time=2,893.775..2,932.040 rows=4 loops=1)

36. 45.714 2,806.604 ↓ 5.3 11,969 1

Nested Loop (cost=223.44..5,514.12 rows=2,242 width=25) (actual time=2,486.125..2,806.604 rows=11,969 loops=1)

  • Join Filter: (cle.id = cie.checklist_item_id)
  • Rows Removed by Join Filter: 83,668
37. 0.033 2.002 ↑ 1.0 4 1

Hash Join (cost=1.59..2.96 rows=4 width=29) (actual time=1.972..2.002 rows=4 loops=1)

  • Hash Cond: (ce.id = cle.checklist_id)
38. 0.776 0.776 ↑ 1.0 19 1

Seq Scan on checklist_entity ce (cost=0.00..1.19 rows=19 width=4) (actual time=0.766..0.776 rows=19 loops=1)

39. 0.010 1.193 ↑ 1.0 4 1

Hash (cost=1.54..1.54 rows=4 width=25) (actual time=1.193..1.193 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
40. 1.183 1.183 ↑ 1.0 4 1

Seq Scan on checklist_item_entity cle (cost=0.00..1.54 rows=4 width=25) (actual time=1.176..1.183 rows=4 loops=1)

  • Filter: (title = 'ACH Revoke'::text)
  • Rows Removed by Filter: 39
41. 2,744.784 2,758.888 ↓ 1.5 23,909 4

Bitmap Heap Scan on checklist_item__entity cie (cost=221.86..1,172.08 rows=16,457 width=16) (actual time=4.468..689.722 rows=23,909 loops=4)

  • Recheck Cond: ((checklist_id = ce.id) AND (checklist_item_value = 1))
  • Filter: (entity_type = 'Entity.Loan'::bpchar)
  • Heap Blocks: exact=11,016
42. 14.104 14.104 ↓ 1.5 23,909 4

Bitmap Index Scan on checklist_item__entity_checklist_id_cond1 (cost=0.00..217.74 rows=16,457 width=0) (actual time=3.526..3.526 rows=23,909 loops=4)

  • Index Cond: (checklist_id = ce.id)
43. 119.690 119.690 ↓ 0.0 0 11,969

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity lae (cost=0.41..3.48 rows=1 width=8) (actual time=0.009..0.010 rows=0 loops=11,969)

  • Index Cond: (loan_id = cie.entity_id)
  • Filter: (deleted = 0)
  • Rows Removed by Filter: 0
44. 2.098 10.368 ↑ 1.0 4,021 1

Hash (cost=141.21..141.21 rows=4,021 width=4) (actual time=10.368..10.368 rows=4,021 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 174kB
45. 8.270 8.270 ↑ 1.0 4,021 1

Seq Scan on customer_payment_method_entity cp (cost=0.00..141.21 rows=4,021 width=4) (actual time=2.680..8.270 rows=4,021 loops=1)

46. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (loan_id = cie.entity_id)
47. 0.002 2,052.349 ↓ 0.0 0 1

Subquery Scan on x (cost=38,385.94..38,849.15 rows=8,422 width=116) (actual time=2,052.348..2,052.349 rows=0 loops=1)

48. 0.110 2,052.347 ↓ 0.0 0 1

HashAggregate (cost=38,385.94..38,617.54 rows=8,422 width=82) (actual time=2,052.347..2,052.347 rows=0 loops=1)

  • Group Key: 12, ls_1.loan_id, ls_1.loan_status_id, pe_1.title, spe.title, pce_1.title, CASE WHEN (pce_1.title = 'Owner'::text) THEN lscf.owner WHEN (pce_1.title = 'Marketing'::text) THEN lscf.mktg_channel1 WHEN (pce_1.title = 'Pricing'::text) THEN lscf.pricing WHEN (pce_1.title = 'PaymentType'::text) THEN lscf.payment_type WHEN (pce_1.title = 'Servicer'::text) THEN lscf.servicer WHEN (pce_1.title = 'Customer Type'::text) THEN lscf.customertype WHEN (pce_1.title = 'EV Type'::text) THEN lscf.ev_type ELSE NULL::text END
49. 40.446 2,052.237 ↓ 0.0 0 1

Hash Left Join (cost=21,021.85..38,238.55 rows=8,422 width=82) (actual time=2,052.237..2,052.237 rows=0 loops=1)

  • Hash Cond: (lsp.sub_portfolio_id = spe.id)
  • Filter: CASE WHEN (pe_1.title <> 'Debt Sale'::text) THEN (lower(replace(CASE WHEN (pce_1.title = 'Owner'::text) THEN lscf.owner WHEN (pce_1.title = 'Marketing'::text) THEN lscf.mktg_channel1 WHEN (pce_1.title = 'Pricing'::text) THEN lscf.pricing WHEN (pce_1.title = 'PaymentType'::text) THEN lscf.payment_type WHEN (pce_1.title = 'Servicer'::text) THEN lscf.servicer WHEN (pce_1.title = 'Customer Type'::text) THEN lscf.customertype WHEN (pce_1.title = 'EV Type'::text) THEN lscf.ev_type ELSE NULL::text END, ' '::text, ''::text)) <> lower(replace(pe_1.title, ' '::text, ''::text))) WHEN (pe_1.title = 'Debt Sale'::text) THEN (lower(replace(CASE WHEN (pce_1.title = 'Owner'::text) THEN lscf.owner WHEN (pce_1.title = 'Marketing'::text) THEN lscf.mktg_channel1 WHEN (pce_1.title = 'Pricing'::text) THEN lscf.pricing WHEN (pce_1.title = 'PaymentType'::text) THEN lscf.payment_type WHEN (pce_1.title = 'Servicer'::text) THEN lscf.servicer WHEN (pce_1.title = 'Customer Type'::text) THEN lscf.customertype WHEN (pce_1.title = 'EV Type'::text) THEN lscf.ev_type ELSE NULL::text END, ' '::text, ''::text)) <> lower(replace(spe.title, ' '::text, ''::text))) ELSE NULL::boolean END
  • Rows Removed by Filter: 13,927
50. 25.675 2,011.208 ↑ 1.2 13,927 1

Nested Loop (cost=20,997.96..38,002.99 rows=16,844 width=115) (actual time=527.309..2,011.208 rows=13,927 loops=1)

51. 37.048 1,347.588 ↓ 1.1 11,599 1

Nested Loop (cost=20,997.53..32,052.14 rows=10,980 width=115) (actual time=526.065..1,347.588 rows=11,599 loops=1)

  • Join Filter: (NULLIF(CASE WHEN (pce_1.title = 'Owner'::text) THEN lscf.owner WHEN (pce_1.title = 'Marketing'::text) THEN lscf.mktg_channel1 WHEN (pce_1.title = 'Pricing'::text) THEN lscf.pricing WHEN (pce_1.title = 'PaymentType'::text) THEN lscf.payment_type WHEN (pce_1.title = 'Servicer'::text) THEN lscf.servicer WHEN (pce_1.title = 'Customer Type'::text) THEN lscf.customertype WHEN (pce_1.title = 'EV Type'::text) THEN lscf.ev_type ELSE NULL::text END, ''::text) IS NOT NULL)
  • Rows Removed by Join Filter: 1,872
52. 18.981 892.939 ↓ 1.2 13,471 1

Hash Join (cost=20,997.11..24,643.69 rows=11,036 width=45) (actual time=523.444..892.939 rows=13,471 loops=1)

  • Hash Cond: (lp_1.loan_id = ls_1.loan_id)
53. 17.092 354.578 ↓ 1.2 13,489 1

Hash Join (cost=4.44..3,499.28 rows=11,036 width=21) (actual time=2.441..354.578 rows=13,489 loops=1)

  • Hash Cond: (lp_1.portfolio_id = pe_1.id)
54. 337.348 337.348 ↑ 1.0 15,364 1

Index Scan using loan__portfolio_lastupdated on loan__portfolio lp_1 (cost=0.43..3,326.60 rows=15,765 width=8) (actual time=2.286..337.348 rows=15,364 loops=1)

  • Index Cond: (lastupdated >= (now() - '7 days'::interval))
55. 0.028 0.138 ↓ 1.0 43 1

Hash (cost=3.50..3.50 rows=41 width=21) (actual time=0.137..0.138 rows=43 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
56. 0.056 0.110 ↓ 1.0 43 1

Hash Join (cost=1.27..3.50 rows=41 width=21) (actual time=0.038..0.110 rows=43 loops=1)

  • Hash Cond: (pe_1.category_id = pce_1.id)
57. 0.030 0.030 ↑ 1.0 59 1

Seq Scan on portfolio_entity pe_1 (cost=0.00..1.59 rows=59 width=16) (actual time=0.007..0.030 rows=59 loops=1)

58. 0.009 0.024 ↑ 1.0 7 1

Hash (cost=1.19..1.19 rows=7 width=13) (actual time=0.024..0.024 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
59. 0.015 0.015 ↑ 1.0 7 1

Seq Scan on portfolio_category_entity pce_1 (cost=0.00..1.19 rows=7 width=13) (actual time=0.008..0.015 rows=7 loops=1)

  • Filter: (title = ANY ('{Owner,Marketing,Pricing,PaymentType,Servicer,""Customer Type"",""EV Type""}'::text[]))
  • Rows Removed by Filter: 3
60. 249.210 519.380 ↓ 1.0 385,471 1

Hash (cost=16,185.63..16,185.63 rows=384,563 width=24) (actual time=519.380..519.380 rows=385,471 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,177kB
61. 270.170 270.170 ↓ 1.0 385,471 1

Seq Scan on loan_settings_entity ls_1 (cost=0.00..16,185.63 rows=384,563 width=24) (actual time=0.007..270.170 rows=385,471 loops=1)

62. 417.601 417.601 ↑ 1.0 1 13,471

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf (cost=0.42..0.64 rows=1 width=82) (actual time=0.030..0.031 rows=1 loops=13,471)

  • Index Cond: (loan_settings_id = ls_1.id)
63. 637.945 637.945 ↑ 2.0 1 11,599

Index Scan using loan__sub_portfolio_loan_id on loan__sub_portfolio lsp (cost=0.42..0.52 rows=2 width=8) (actual time=0.054..0.055 rows=1 loops=11,599)

  • Index Cond: (loan_id = ls_1.loan_id)
64. 0.006 0.583 ↑ 1.0 1 1

Hash (cost=23.88..23.88 rows=1 width=17) (actual time=0.583..0.583 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
65. 0.577 0.577 ↑ 1.0 1 1

Seq Scan on sub_portfolio_entity spe (cost=0.00..23.88 rows=1 width=17) (actual time=0.575..0.577 rows=1 loops=1)

  • Filter: (id = 12)
  • Rows Removed by Filter: 17
66. 0.002 16,319.705 ↓ 0.0 0 1

Subquery Scan on *SELECT* 8 (cost=428,244.77..555,814.05 rows=2,685,669 width=116) (actual time=16,319.704..16,319.705 rows=0 loops=1)

67. 3,713.376 16,319.703 ↓ 0.0 0 1

GroupAggregate (cost=428,244.77..522,243.18 rows=2,685,669 width=121) (actual time=16,319.702..16,319.703 rows=0 loops=1)

  • Group Key: lp_2.loan_id, pce_2.title, lse_4.loan_status_id
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 2,685,648
68. 5,148.854 12,606.327 ↑ 1.0 2,685,648 1

Sort (cost=428,244.77..434,958.94 rows=2,685,669 width=29) (actual time=11,506.258..12,606.327 rows=2,685,648 loops=1)

  • Sort Key: lp_2.loan_id, pce_2.title, lse_4.loan_status_id
  • Sort Method: quicksort Memory: 323,088kB
69. 2,835.796 7,457.473 ↑ 1.0 2,685,648 1

Hash Join (cost=20,997.03..141,457.62 rows=2,685,669 width=29) (actual time=489.235..7,457.473 rows=2,685,648 loops=1)

  • Hash Cond: (lp_2.loan_id = lse_4.loan_id)
70. 2,813.495 4,135.777 ↑ 1.0 2,685,669 1

Hash Join (cost=4.36..83,537.00 rows=2,685,669 width=21) (actual time=0.416..4,135.777 rows=2,685,669 loops=1)

  • Hash Cond: (lp_2.portfolio_id = pe_2.id)
71. 1,322.105 1,322.105 ↑ 1.0 2,685,669 1

Seq Scan on loan__portfolio lp_2 (cost=0.00..46,604.69 rows=2,685,669 width=8) (actual time=0.010..1,322.105 rows=2,685,669 loops=1)

72. 0.041 0.177 ↑ 1.0 59 1

Hash (cost=3.63..3.63 rows=59 width=21) (actual time=0.177..0.177 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
73. 0.084 0.136 ↑ 1.0 59 1

Hash Join (cost=1.23..3.63 rows=59 width=21) (actual time=0.056..0.136 rows=59 loops=1)

  • Hash Cond: (pe_2.category_id = pce_2.id)
74. 0.030 0.030 ↑ 1.0 59 1

Seq Scan on portfolio_entity pe_2 (cost=0.00..1.59 rows=59 width=16) (actual time=0.007..0.030 rows=59 loops=1)

75. 0.011 0.022 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=13) (actual time=0.021..0.022 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
76. 0.011 0.011 ↑ 1.0 10 1

Seq Scan on portfolio_category_entity pce_2 (cost=0.00..1.10 rows=10 width=13) (actual time=0.005..0.011 rows=10 loops=1)

77. 240.689 485.900 ↓ 1.0 385,471 1

Hash (cost=16,185.63..16,185.63 rows=384,563 width=16) (actual time=485.899..485.900 rows=385,471 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 22,165kB
78. 245.211 245.211 ↓ 1.0 385,471 1

Seq Scan on loan_settings_entity lse_4 (cost=0.00..16,185.63 rows=384,563 width=16) (actual time=0.007..245.211 rows=385,471 loops=1)

79. 0.002 1,835.735 ↓ 0.0 0 1

Subquery Scan on *SELECT* 9 (cost=46.35..10,547.98 rows=114 width=116) (actual time=1,835.735..1,835.735 rows=0 loops=1)

80. 1.926 1,835.733 ↓ 0.0 0 1

Nested Loop (cost=46.35..10,546.56 rows=114 width=112) (actual time=1,835.733..1,835.733 rows=0 loops=1)

81. 9.909 1,825.987 ↓ 3.6 1,955 1

Nested Loop (cost=45.93..10,194.03 rows=542 width=26) (actual time=124.169..1,825.987 rows=1,955 loops=1)

82. 1,705.168 1,800.398 ↓ 3.5 1,960 1

Bitmap Heap Scan on loan_setup_entity ls_2 (cost=45.50..5,966.44 rows=556 width=10) (actual time=118.305..1,800.398 rows=1,960 loops=1)

  • Recheck Cond: (contract_date >= (CURRENT_DATE - '10 days'::interval))
  • Filter: (loan_rate > '25'::numeric)
  • Rows Removed by Filter: 1,361
  • Heap Blocks: exact=2,689
83. 95.230 95.230 ↓ 9.3 15,988 1

Bitmap Index Scan on loan_setup_entity_contract_date (cost=0.00..45.37 rows=1,725 width=0) (actual time=95.230..95.230 rows=15,988 loops=1)

  • Index Cond: (contract_date >= (CURRENT_DATE - '10 days'::interval))
84. 15.680 15.680 ↑ 1.0 1 1,960

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_5 (cost=0.42..7.59 rows=1 width=24) (actual time=0.007..0.008 rows=1 loops=1,960)

  • Index Cond: (loan_id = ls_2.loan_id)
  • Filter: (loan_sub_status_id <> 38)
  • Rows Removed by Filter: 0
85. 7.820 7.820 ↓ 0.0 0 1,955

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cfe (cost=0.42..0.65 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1,955)

  • Index Cond: (loan_settings_id = lse_5.id)
  • Filter: (state_code = '35'::text)
  • Rows Removed by Filter: 1
86. 0.002 45.816 ↓ 0.0 0 1

Subquery Scan on *SELECT* 10 (cost=46.44..13,145.39 rows=415 width=116) (actual time=45.815..45.816 rows=0 loops=1)

87. 2.598 45.814 ↓ 0.0 0 1

Nested Loop (cost=46.44..13,140.20 rows=415 width=112) (actual time=45.814..45.814 rows=0 loops=1)

88. 5.546 33.441 ↓ 2.2 1,955 1

Nested Loop (cost=46.02..12,557.02 rows=894 width=26) (actual time=3.447..33.441 rows=1,955 loops=1)

89. 11.166 14.175 ↓ 2.1 1,960 1

Bitmap Heap Scan on loan_setup_entity ls_3 (cost=45.59..5,966.53 rows=916 width=10) (actual time=3.429..14.175 rows=1,960 loops=1)

  • Recheck Cond: (contract_date >= (CURRENT_DATE - '10 days'::interval))
  • Filter: (loan_rate > 9.9)
  • Rows Removed by Filter: 1,361
  • Heap Blocks: exact=2,689
90. 3.009 3.009 ↓ 9.3 15,988 1

Bitmap Index Scan on loan_setup_entity_contract_date (cost=0.00..45.37 rows=1,725 width=0) (actual time=3.009..3.009 rows=15,988 loops=1)

  • Index Cond: (contract_date >= (CURRENT_DATE - '10 days'::interval))
91. 13.720 13.720 ↑ 1.0 1 1,960

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_6 (cost=0.42..7.18 rows=1 width=24) (actual time=0.006..0.007 rows=1 loops=1,960)

  • Index Cond: (loan_id = ls_3.loan_id)
  • Filter: (loan_sub_status_id <> 38)
  • Rows Removed by Filter: 0
92. 9.775 9.775 ↓ 0.0 0 1,955

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cfe_1 (cost=0.42..0.65 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1,955)

  • Index Cond: (loan_settings_id = lse_6.id)
  • Filter: (state_code = '43'::text)
  • Rows Removed by Filter: 1
93. 0.003 3,829.661 ↓ 0.0 0 1

Subquery Scan on *SELECT* 11 (cost=0.84..37,114.98 rows=1,923 width=106) (actual time=3,829.660..3,829.661 rows=0 loops=1)

94. 0.001 3,829.658 ↓ 0.0 0 1

Nested Loop (cost=0.84..37,090.94 rows=1,923 width=102) (actual time=3,829.658..3,829.658 rows=0 loops=1)

95. 0.002 3,829.657 ↓ 0.0 0 1

Nested Loop (cost=0.42..35,955.21 rows=1,923 width=26) (actual time=3,829.657..3,829.657 rows=0 loops=1)

96. 3,829.655 3,829.655 ↓ 0.0 0 1

Seq Scan on customer_entity ce_1 (cost=0.00..25,410.64 rows=1,359 width=26) (actual time=3,829.655..3,829.655 rows=0 loops=1)

  • Filter: (NULLIF(email, ''::text) IS NULL)
  • Rows Removed by Filter: 271,731
97. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (customer_id = ce_1.id)
98. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (loan_id = la.loan_id)
99. 0.002 0.012 ↓ 0.0 0 1

Subquery Scan on *SELECT* 12 (cost=0.55..16.61 rows=1 width=116) (actual time=0.012..0.012 rows=0 loops=1)

100. 0.002 0.010 ↓ 0.0 0 1

Nested Loop (cost=0.55..16.59 rows=1 width=181) (actual time=0.010..0.010 rows=0 loops=1)

101. 0.008 0.008 ↓ 0.0 0 1

Index Scan using loan_autopay_entity_loan_id_cond on loan_autopay_entity la_1 (cost=0.12..8.14 rows=1 width=105) (actual time=0.008..0.008 rows=0 loops=1)

102. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (loan_id = la_1.loan_id)
103. 0.002 81.629 ↓ 0.0 0 1

Subquery Scan on *SELECT* 13 (cost=0.85..72.42 rows=3 width=116) (actual time=81.628..81.629 rows=0 loops=1)

104. 0.003 81.627 ↓ 0.0 0 1

Nested Loop (cost=0.85..72.38 rows=3 width=112) (actual time=81.626..81.627 rows=0 loops=1)

105. 81.624 81.624 ↓ 0.0 0 1

Index Scan using loan_autopay_entity_apply_date on loan_autopay_entity lae_1 (cost=0.43..47.02 rows=3 width=8) (actual time=81.624..81.624 rows=0 loops=1)

  • Index Cond: (apply_date IS NULL)
  • Filter: ((status ~~ '%pending%'::text) AND (active = 1) AND (deleted = 0))
  • Rows Removed by Filter: 9
106. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (loan_id = lae_1.loan_id)
107. 0.002 40,735.404 ↓ 0.0 0 1

Subquery Scan on cte (cost=129,527.23..129,551.71 rows=68 width=116) (actual time=40,735.404..40,735.404 rows=0 loops=1)

108. 1.681 40,735.402 ↓ 0.0 0 1

GroupAggregate (cost=129,527.23..129,548.82 rows=68 width=90) (actual time=40,735.401..40,735.402 rows=0 loops=1)

  • Group Key: lse_10.loan_id, lse_10.loan_status_id, ls_4.first_payment_date, cfe_2.fpd_date, (CASE WHEN (COALESCE(ls_4.til_payment_schedule, ''::text) <> ''::text) THEN dw_reporting_meta.text_to_numeric((((ls_4.til_payment_schedule)::json -> 0) ->> 'payment'::text)) ELSE round((ls_4.til_total_of_payments / (ls_4.loan_term - '1'::numeric)), 2) END)
  • Filter: (((max(pe_3.apply_date) IS NULL) AND ((cfe_2.fpd_date IS NULL) OR (cfe_2.fpd_date = ''::text))) OR ((sum(pe_3.amount) < (CASE WHEN (COALESCE(ls_4.til_payment_schedule, ''::text) <> ''::text) THEN dw_reporting_meta.text_to_numeric((((ls_4.til_payment_schedule)::json -> 0) ->> 'payment'::text)) ELSE round((ls_4.til_total_of_payments / (ls_4.loan_term - '1'::numeric)), 2) END)) AND ((cfe_2.fpd_date IS NULL) OR (cfe_2.fpd_date = ''::text))) OR ((cfe_2.fpd_date IS NOT NULL) AND (cfe_2.fpd_date <> ''::text) AND (sum(pe_3.amount) >= (CASE WHEN (COALESCE(ls_4.til_payment_schedule, ''::text) <> ''::text) THEN dw_reporting_meta.text_to_numeric((((ls_4.til_payment_schedule)::json -> 0) ->> 'payment'::text)) ELSE round((ls_4.til_total_of_payments / (ls_4.loan_term - '1'::numeric)), 2) END)) AND (max(pe_3.apply_date) IS NOT NULL)))
  • Rows Removed by Filter: 1,079
109. 3.199 40,733.721 ↓ 16.0 1,087 1

Sort (cost=129,527.23..129,527.40 rows=68 width=142) (actual time=40,733.333..40,733.721 rows=1,087 loops=1)

  • Sort Key: lse_10.loan_id, ls_4.first_payment_date, cfe_2.fpd_date, (CASE WHEN (COALESCE(ls_4.til_payment_schedule, ''::text) <> ''::text) THEN dw_reporting_meta.text_to_numeric((((ls_4.til_payment_schedule)::json -> 0) ->> 'payment'::text)) ELSE round((ls_4.til_total_of_payments / (ls_4.loan_term - '1'::numeric)), 2) END)
  • Sort Method: quicksort Memory: 337kB
110. 39.179 40,730.522 ↓ 16.0 1,087 1

Nested Loop (cost=5,605.50..129,525.16 rows=68 width=142) (actual time=424.825..40,730.522 rows=1,087 loops=1)

111. 39.099 40,471.769 ↓ 16.0 1,087 1

Nested Loop (cost=5,605.08..129,375.71 rows=68 width=116) (actual time=424.416..40,471.769 rows=1,087 loops=1)

  • Join Filter: ((pe_3.apply_date <= ls_4.first_payment_date) AND ((pe_3.apply_date IS NULL) OR (pe_3.apply_date <= ls_4.first_payment_date)))
  • Rows Removed by Join Filter: 9,867
112. 27.101 10,026.014 ↓ 18.4 11,312 1

Hash Join (cost=5,604.65..127,365.83 rows=616 width=38) (actual time=206.218..10,026.014 rows=11,312 loops=1)

  • Hash Cond: (pe_3.entity_id = lse_10.loan_id)
113. 9,807.102 9,807.102 ↓ 2.6 11,677 1

Seq Scan on payment_entity pe_3 (cost=0.00..121,737.93 rows=4,555 width=14) (actual time=14.312..9,807.102 rows=11,677 loops=1)

  • Filter: ((nacha_return_code IS NULL) AND (deleted = 0) AND (active = 1) AND (lastupdated > (CURRENT_DATE - 10)))
  • Rows Removed by Filter: 1,575,909
114. 36.877 191.811 ↓ 1.0 53,496 1

Hash (cost=4,954.27..4,954.27 rows=52,031 width=24) (actual time=191.811..191.811 rows=53,496 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,438kB
115. 154.934 154.934 ↓ 1.0 53,496 1

Index Scan using loan_settings_entity_id_cond on loan_settings_entity lse_10 (cost=0.29..4,954.27 rows=52,031 width=24) (actual time=0.033..154.934 rows=53,496 loops=1)

116. 30,406.656 30,406.656 ↑ 1.0 1 11,312

Index Scan using loan_setup_entity_loan_id on loan_setup_entity ls_4 (cost=0.42..3.25 rows=1 width=86) (actual time=2.687..2.688 rows=1 loops=11,312)

  • Index Cond: (loan_id = lse_10.loan_id)
  • Filter: (first_payment_date < CURRENT_DATE)
  • Rows Removed by Filter: 0
117. 219.574 219.574 ↑ 1.0 1 1,087

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cfe_2 (cost=0.42..1.93 rows=1 width=6) (actual time=0.202..0.202 rows=1 loops=1,087)

  • Index Cond: (loan_settings_id = lse_10.id)
118. 0.002 5,335.827 ↓ 0.0 0 1

Subquery Scan on *SELECT* 15 (cost=3,239.00..6,961.39 rows=299 width=116) (actual time=5,335.827..5,335.827 rows=0 loops=1)

119. 0.002 5,335.825 ↓ 0.0 0 1

Nested Loop (cost=3,239.00..6,957.65 rows=299 width=112) (actual time=5,335.825..5,335.825 rows=0 loops=1)

120. 4,653.907 5,335.823 ↓ 0.0 0 1

Bitmap Heap Scan on loan_autopay_entity a (cost=3,238.58..4,496.62 rows=299 width=16) (actual time=5,335.823..5,335.823 rows=0 loops=1)

  • Recheck Cond: ((process_datetime >= CURRENT_DATE) AND (process_datetime <= (CURRENT_DATE + 6)) AND (status = 'autopay.status.pending'::bpchar))
  • Filter: ((deleted = 0) AND (dw_reporting_meta.prev_business_date(apply_date) <> (process_datetime)::date))
  • Rows Removed by Filter: 12,322
  • Heap Blocks: exact=3,891
121. 0.629 681.916 ↓ 0.0 0 1

BitmapAnd (cost=3,238.58..3,238.58 rows=301 width=0) (actual time=681.916..681.916 rows=0 loops=1)

122. 569.325 569.325 ↓ 1.8 21,085 1

Bitmap Index Scan on loan_autopay_entity_process_datetime (cost=0.00..280.56 rows=11,613 width=0) (actual time=569.325..569.325 rows=21,085 loops=1)

  • Index Cond: ((process_datetime >= CURRENT_DATE) AND (process_datetime <= (CURRENT_DATE + 6)))
123. 111.962 111.962 ↓ 1.0 47,167 1

Bitmap Index Scan on loan_autopay_entity_status_cond_autopay_status_pending (cost=0.00..2,957.61 rows=45,840 width=0) (actual time=111.962..111.962 rows=47,167 loops=1)

124. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (loan_id = a.loan_id)
125. 0.002 1,408.798 ↓ 0.0 0 1

Nested Loop (cost=1.28..8,555.07 rows=13 width=116) (actual time=1,408.797..1,408.798 rows=0 loops=1)

126. 12.655 1,408.796 ↓ 0.0 0 1

Nested Loop (cost=0.85..8,548.58 rows=13 width=8) (actual time=1,408.796..1,408.796 rows=0 loops=1)

127. 8.468 1,117.102 ↓ 395.8 5,937 1

Nested Loop (cost=0.43..8,538.60 rows=15 width=8) (actual time=234.659..1,117.102 rows=5,937 loops=1)

128. 229.958 229.958 ↓ 395.8 5,937 1

Seq Scan on returnfilerecord rr (cost=0.00..8,411.89 rows=15 width=8) (actual time=221.763..229.958 rows=5,937 loops=1)

  • Filter: (((returncode)::text !~~ 'C%'::text) AND (product = 'BALANCE_CREDIT'::text) AND (returndate > (CURRENT_DATE - 30)))
  • Rows Removed by Filter: 213,937
129. 878.676 878.676 ↑ 1.0 1 5,937

Index Scan using achfilerecord_achfilerecord_id_unique on achfilerecord fr (cost=0.43..8.45 rows=1 width=8) (actual time=0.148..0.148 rows=1 loops=5,937)

  • Index Cond: (achfilerecord_id = rr.originalachrecord_id)
  • Filter: (product = 'BALANCE_CREDIT'::text)
130. 279.039 279.039 ↓ 0.0 0 5,937

Index Scan using payment_entity_id on payment_entity p (cost=0.43..0.66 rows=1 width=8) (actual time=0.047..0.047 rows=0 loops=5,937)

  • Index Cond: (id = fr.payment_id)
  • Filter: (reverse_reason IS NULL)
  • Rows Removed by Filter: 1
131. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (loan_id = p.entity_id)
132. 0.107 149.377 ↓ 0.0 0 1

Nested Loop (cost=0.85..7,990.02 rows=2 width=116) (actual time=149.377..149.377 rows=0 loops=1)

133. 4.621 146.318 ↓ 36.0 72 1

Nested Loop (cost=0.43..7,988.65 rows=2 width=8) (actual time=115.499..146.318 rows=72 loops=1)

134. 117.949 117.949 ↓ 395.8 5,937 1

Seq Scan on returnfilerecord rr_1 (cost=0.00..7,861.90 rows=15 width=8) (actual time=112.643..117.949 rows=5,937 loops=1)

  • Filter: ((product = 'BALANCE_CREDIT'::text) AND (returndate > (CURRENT_DATE - 30)))
  • Rows Removed by Filter: 213,937
135. 23.748 23.748 ↓ 0.0 0 5,937

Index Scan using achfilerecord_achfilerecord_id_unique on achfilerecord fr_1 (cost=0.43..8.45 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=5,937)

  • Index Cond: (achfilerecord_id = rr_1.originalachrecord_id)
  • Filter: ((isorigination = 1) AND (product = 'BALANCE_CREDIT'::text))
  • Rows Removed by Filter: 1
136. 2.952 2.952 ↓ 0.0 0 72

Index Scan using loan_settings_entity_loan_id on loan_settings_entity ls_6 (cost=0.42..0.67 rows=1 width=16) (actual time=0.041..0.041 rows=0 loops=72)

  • Index Cond: (loan_id = fr_1.loan_id)
  • Filter: (loan_sub_status_id <> 41)
  • Rows Removed by Filter: 1
137. 0.003 139.093 ↓ 0.0 0 1

Hash Join (cost=29,954.73..125,151.00 rows=132,271 width=116) (actual time=139.092..139.093 rows=0 loops=1)

  • Hash Cond: (fr_2.loan_id = ls_7.loan_id)
138. 0.017 139.090 ↓ 0.0 0 1

Hash Join (cost=8,962.06..101,347.58 rows=132,271 width=8) (actual time=139.090..139.090 rows=0 loops=1)

  • Hash Cond: (ltrim(fr_2.transaction_id, ' '::text) = ltrim(rr_2.transaction_id, ' '::text))
139. 0.765 0.765 ↑ 1,763,619.0 1 1

Seq Scan on achfilerecord fr_2 (cost=0.00..73,426.61 rows=1,763,619 width=14) (actual time=0.765..0.765 rows=1 loops=1)

  • Filter: (product = 'BALANCE_CREDIT'::text)
140. 0.002 138.308 ↓ 0.0 0 1

Hash (cost=8,961.88..8,961.88 rows=15 width=14) (actual time=138.308..138.308 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
141. 138.306 138.306 ↓ 0.0 0 1

Seq Scan on returnfilerecord rr_2 (cost=0.00..8,961.88 rows=15 width=14) (actual time=138.306..138.306 rows=0 loops=1)

  • Filter: ((product = 'BALANCE_CREDIT'::text) AND (ltrim(transaction_id, ' '::text) <> transaction_id) AND (returndate > (CURRENT_DATE - 30)))
  • Rows Removed by Filter: 219,874
142. 0.000 0.000 ↓ 0.0 0

Hash (cost=16,185.63..16,185.63 rows=384,563 width=16) (never executed)

143. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_settings_entity ls_7 (cost=0.00..16,185.63 rows=384,563 width=16) (never executed)

144. 0.003 331,045.020 ↓ 0.0 0 1

Subquery Scan on *SELECT* 19 (cost=467,079.47..877,146.83 rows=17,142,198 width=116) (actual time=331,045.019..331,045.020 rows=0 loops=1)

145. 12.639 331,045.017 ↓ 0.0 0 1

Hash Join (cost=467,079.47..662,869.36 rows=17,142,198 width=112) (actual time=331,045.017..331,045.017 rows=0 loops=1)

  • Hash Cond: (loan_autopay_entity_1.loan_id = tx.loan_id)
  • Join Filter: ((min(loan_autopay_entity_1.apply_date)) <> tx.date)
  • Rows Removed by Join Filter: 10,976
146. 21.157 75,581.794 ↑ 2.9 10,976 1

Merge Anti Join (cost=45,840.31..70,122.63 rows=31,782 width=24) (actual time=64,619.721..75,581.794 rows=10,976 loops=1)

  • Merge Cond: (loan_autopay_entity_1.loan_id = loan_autopay_entity.loan_id)
  • Join Filter: (loan_autopay_entity.apply_date <= (min(loan_autopay_entity_1.apply_date)))
  • Rows Removed by Join Filter: 7,975
147. 11.971 64,623.432 ↑ 3.4 11,198 1

Sort (cost=45,839.90..45,935.24 rows=38,139 width=24) (actual time=64,619.299..64,623.432 rows=11,198 loops=1)

  • Sort Key: loan_autopay_entity_1.loan_id
  • Sort Method: quicksort Memory: 1,259kB
148. 183.335 64,611.461 ↑ 3.4 11,198 1

Hash Join (cost=25,742.60..42,937.71 rows=38,139 width=24) (actual time=64,000.876..64,611.461 rows=11,198 loops=1)

  • Hash Cond: (lse_12.loan_id = loan_autopay_entity_1.loan_id)
149. 631.291 631.291 ↓ 1.0 385,471 1

Seq Scan on loan_settings_entity lse_12 (cost=0.00..16,185.63 rows=384,563 width=16) (actual time=0.015..631.291 rows=385,471 loops=1)

150. 11.357 63,796.835 ↑ 3.4 11,198 1

Hash (cost=25,265.86..25,265.86 rows=38,139 width=8) (actual time=63,796.835..63,796.835 rows=11,198 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 950kB
151. 74.193 63,785.478 ↑ 3.4 11,198 1

GroupAggregate (cost=0.41..24,884.47 rows=38,139 width=8) (actual time=68.429..63,785.478 rows=11,198 loops=1)

  • Group Key: loan_autopay_entity_1.loan_id
  • Filter: (((min(loan_autopay_entity_1.apply_date) - CURRENT_DATE) >= 0) AND ((min(loan_autopay_entity_1.apply_date) - CURRENT_DATE) <= 5))
  • Rows Removed by Filter: 13,165
152. 63,711.285 63,711.285 ↑ 1.6 27,398 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity loan_autopay_entity_1 (cost=0.41..24,075.54 rows=42,754 width=8) (actual time=0.647..63,711.285 rows=27,398 loops=1)

  • Filter: (name = 'Autopay'::text)
  • Rows Removed by Filter: 19,598
153. 10,937.205 10,937.205 ↓ 6.2 19,066 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity (cost=0.41..24,075.54 rows=3,087 width=8) (actual time=0.024..10,937.205 rows=19,066 loops=1)

  • Filter: (name <> 'Autopay'::text)
  • Rows Removed by Filter: 26,788
154. 102.747 255,450.584 ↓ 2.5 271,943 1

Hash (cost=419,883.99..419,883.99 rows=108,414 width=12) (actual time=255,450.584..255,450.584 rows=271,943 loops=1)

  • Buckets: 524,288 (originally 131072) Batches: 1 (originally 1) Memory Usage: 15,782kB
155. 116.303 255,347.837 ↓ 2.5 271,943 1

Subquery Scan on tx (cost=417,715.71..419,883.99 rows=108,414 width=12) (actual time=255,112.918..255,347.837 rows=271,943 loops=1)

156. 1,084.598 255,231.534 ↓ 2.5 271,943 1

HashAggregate (cost=417,715.71..418,799.85 rows=108,414 width=12) (actual time=255,112.915..255,231.534 rows=271,943 loops=1)

  • Group Key: loan_tx.entity_id
157. 254,146.936 254,146.936 ↑ 1.5 1,102,462 1

Index Scan using loan_tx_date1 on loan_tx (cost=0.44..409,304.01 rows=1,682,340 width=12) (actual time=131.660..254,146.936 rows=1,102,462 loops=1)

  • Index Cond: (date > CURRENT_DATE)
  • Filter: (type = 'scheduledPayment'::bpchar)
  • Rows Removed by Filter: 1,102,339
158. 0.001 10,307.007 ↓ 0.0 0 1

Subquery Scan on *SELECT* 20 (cost=139,579.41..184,190.66 rows=5,280 width=116) (actual time=10,307.006..10,307.007 rows=0 loops=1)

159. 0.003 10,307.006 ↓ 0.0 0 1

Nested Loop Anti Join (cost=139,579.41..184,124.66 rows=5,280 width=112) (actual time=10,307.005..10,307.006 rows=0 loops=1)

160. 691.710 10,307.003 ↓ 0.0 0 1

Hash Right Join (cost=139,578.98..181,269.68 rows=5,951 width=24) (actual time=10,307.003..10,307.003 rows=0 loops=1)

  • Hash Cond: (pcf.payment_id = p_1.id)
  • Filter: ((pcf.endpoint = ''::text) OR (pcf.endpoint IS NULL))
  • Rows Removed by Filter: 245,566
161. 1,111.057 1,111.057 ↑ 1.0 1,577,564 1

Seq Scan on payment_custom_fields pcf (cost=0.00..33,576.56 rows=1,601,856 width=18) (actual time=8.354..1,111.057 rows=1,577,564 loops=1)

162. 116.358 8,504.236 ↓ 1.5 245,566 1

Hash (cost=137,471.81..137,471.81 rows=168,574 width=24) (actual time=8,504.235..8,504.236 rows=245,566 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 15,478kB
163. 628.781 8,387.878 ↓ 1.5 245,566 1

Hash Join (cost=5,604.65..137,471.81 rows=168,574 width=24) (actual time=519.146..8,387.878 rows=245,566 loops=1)

  • Hash Cond: (p_1.entity_id = lse_13.loan_id)
164. 7,245.013 7,245.013 ↓ 1.0 1,304,143 1

Seq Scan on payment_entity p_1 (cost=0.00..125,509.15 rows=1,245,937 width=16) (actual time=4.927..7,245.013 rows=1,304,143 loops=1)

  • Filter: ((child IS NULL) AND (apply_date >= '2016-01-01'::date) AND (((reverse_reason)::text = 'nachaErrorCode'::text) OR (reverse_reason IS NULL)) AND (payment_method_id = 4) AND (deleted = 0) AND (apply_date <= CURRENT_DATE))
  • Rows Removed by Filter: 283,443
165. 20.803 514.084 ↓ 1.0 53,496 1

Hash (cost=4,954.27..4,954.27 rows=52,031 width=16) (actual time=514.083..514.084 rows=53,496 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,020kB
166. 493.281 493.281 ↓ 1.0 53,496 1

Index Scan using loan_settings_entity_id_cond on loan_settings_entity lse_13 (cost=0.29..4,954.27 rows=52,031 width=16) (actual time=5.255..493.281 rows=53,496 loops=1)

167. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (payment_id = p_1.id)
  • Filter: (product = 'BALANCE_CREDIT'::text)
168. 0.001 11,195.683 ↓ 0.0 0 1

Subquery Scan on *SELECT* 21 (cost=83,487.75..255,747.61 rows=239,259 width=116) (actual time=11,195.683..11,195.683 rows=0 loops=1)

169. 56.553 11,195.682 ↓ 0.0 0 1

Hash Join (cost=83,487.75..252,756.87 rows=239,259 width=112) (actual time=11,195.681..11,195.682 rows=0 loops=1)

  • Hash Cond: (l.loan_id = lse_14.loan_id)
170. 58.121 10,801.060 ↑ 2.7 141,798 1

Subquery Scan on l (cost=62,363.83..226,613.79 rows=381,408 width=8) (actual time=10,701.912..10,801.060 rows=141,798 loops=1)

171. 280.419 10,742.939 ↑ 2.7 141,798 1

HashSetOp Except (cost=62,363.83..222,799.71 rows=381,408 width=12) (actual time=10,701.910..10,742.939 rows=141,798 loops=1)

172. 234.890 10,462.520 ↓ 1.0 627,360 1

Append (cost=62,363.83..219,692.79 rows=621,384 width=12) (actual time=4,904.268..10,462.520 rows=627,360 loops=1)

173. 153.978 5,216.461 ↓ 1.0 384,579 1

Subquery Scan on *SELECT* 1 (cost=62,363.83..69,991.99 rows=381,408 width=12) (actual time=4,904.267..5,216.461 rows=384,579 loops=1)

174. 372.764 5,062.483 ↓ 1.0 384,579 1

HashAggregate (cost=62,363.83..66,177.91 rows=381,408 width=8) (actual time=4,904.266..5,062.483 rows=384,579 loops=1)

  • Group Key: ls_8.loan_id, ls_8.contract_date
175. 4,689.719 4,689.719 ↓ 1.0 384,579 1

Seq Scan on loan_setup_entity ls_8 (cost=0.00..60,456.79 rows=381,408 width=8) (actual time=0.618..4,689.719 rows=384,579 loops=1)

  • Filter: ((contract_date >= '2016-01-01'::date) AND (contract_date <= CURRENT_DATE))
  • Rows Removed by Filter: 892
176. 98.823 5,011.169 ↓ 1.0 242,781 1

Subquery Scan on *SELECT* 2 (cost=144,901.27..149,700.80 rows=239,976 width=12) (actual time=4,813.879..5,011.169 rows=242,781 loops=1)

177. 233.940 4,912.346 ↓ 1.0 242,781 1

HashAggregate (cost=144,901.27..147,301.04 rows=239,976 width=8) (actual time=4,813.877..4,912.346 rows=242,781 loops=1)

  • Group Key: ls_9.loan_id, ls_9.contract_date
178. 195.781 4,678.406 ↓ 1.0 242,781 1

Hash Join (cost=65,224.39..143,701.39 rows=239,976 width=8) (actual time=444.903..4,678.406 rows=242,781 loops=1)

  • Hash Cond: (afr.loan_id = ls_9.loan_id)
179. 4,042.580 4,042.580 ↓ 1.0 243,170 1

Seq Scan on achfilerecord afr (cost=0.00..77,842.13 rows=241,851 width=4) (actual time=0.015..4,042.580 rows=243,170 loops=1)

  • Filter: ((isorigination = 1) AND (product = 'BALANCE_CREDIT'::text))
  • Rows Removed by Filter: 1,523,708
180. 136.011 440.045 ↓ 1.0 384,579 1

Hash (cost=60,456.79..60,456.79 rows=381,408 width=8) (actual time=440.044..440.045 rows=384,579 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 19,119kB
181. 304.034 304.034 ↓ 1.0 384,579 1

Seq Scan on loan_setup_entity ls_9 (cost=0.00..60,456.79 rows=381,408 width=8) (actual time=0.010..304.034 rows=384,579 loops=1)

  • Filter: ((contract_date >= '2016-01-01'::date) AND (contract_date <= CURRENT_DATE))
  • Rows Removed by Filter: 892
182. 85.193 338.069 ↓ 1.0 243,201 1

Hash (cost=18,108.44..18,108.44 rows=241,238 width=16) (actual time=338.069..338.069 rows=243,201 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 13,449kB
183. 252.876 252.876 ↓ 1.0 243,201 1

Seq Scan on loan_settings_entity lse_14 (cost=0.00..18,108.44 rows=241,238 width=16) (actual time=0.034..252.876 rows=243,201 loops=1)

  • Filter: ((loan_status_id = ANY ('{4,5}'::bigint[])) OR (loan_sub_status_id = 41))
  • Rows Removed by Filter: 142,270
184. 0.002 3,338.031 ↓ 0.0 0 1

Subquery Scan on *SELECT* 22 (cost=74,592.86..231,558.87 rows=59,845 width=116) (actual time=3,338.030..3,338.031 rows=0 loops=1)

185. 38.289 3,338.029 ↓ 0.0 0 1

Nested Loop (cost=74,592.86..230,810.81 rows=59,845 width=112) (actual time=3,338.029..3,338.029 rows=0 loops=1)

186. 61.704 2,905.696 ↓ 1.2 65,674 1

Hash Join (cost=74,592.43..182,166.26 rows=53,250 width=24) (actual time=1,416.637..2,905.696 rows=65,674 loops=1)

  • Hash Cond: (p_2.entity_id = lse_15.loan_id)
187. 1,014.855 2,560.646 ↓ 1.2 65,674 1

Hash Left Join (cost=53,599.76..160,441.41 rows=53,250 width=16) (actual time=1,131.016..2,560.646 rows=65,674 loops=1)

  • Hash Cond: (p_2.id = pcf_1.payment_id)
  • Filter: ((pcf_1.endpoint = ''::text) OR (pcf_1.endpoint IS NULL))
  • Rows Removed by Filter: 1,521,912
188. 426.166 426.166 ↓ 1.1 1,587,586 1

Seq Scan on payment_entity p_2 (cost=0.00..102,881.86 rows=1,508,486 width=16) (actual time=0.007..426.166 rows=1,587,586 loops=1)

189. 644.030 1,119.625 ↑ 1.0 1,577,564 1

Hash (cost=33,576.56..33,576.56 rows=1,601,856 width=18) (actual time=1,119.625..1,119.625 rows=1,577,564 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 93,472kB
190. 475.595 475.595 ↑ 1.0 1,577,564 1

Seq Scan on payment_custom_fields pcf_1 (cost=0.00..33,576.56 rows=1,601,856 width=18) (actual time=0.009..475.595 rows=1,577,564 loops=1)

191. 139.739 283.346 ↓ 1.0 385,471 1

Hash (cost=16,185.63..16,185.63 rows=384,563 width=16) (actual time=283.346..283.346 rows=385,471 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 22,165kB
192. 143.607 143.607 ↓ 1.0 385,471 1

Seq Scan on loan_settings_entity lse_15 (cost=0.00..16,185.63 rows=384,563 width=16) (actual time=0.010..143.607 rows=385,471 loops=1)

193. 394.044 394.044 ↓ 0.0 0 65,674

Index Scan using achfilerecord_payment_id on achfilerecord ach (cost=0.43..0.80 rows=10 width=4) (actual time=0.006..0.006 rows=0 loops=65,674)

  • Index Cond: (payment_id = p_2.id)
  • Filter: ((effectivedate > '2016-11-01'::date) AND (product = 'BALANCE_CREDIT'::text))
  • Rows Removed by Filter: 0
194. 87.725 1,207.884 ↓ 0.0 0 1

Hash Join (cost=82,138.69..171,005.18 rows=87,516 width=116) (actual time=1,207.883..1,207.884 rows=0 loops=1)

  • Hash Cond: (ach_1.loan_id = ls_10.loan_id)
195. 526.098 526.098 ↑ 1.0 232,252 1

Seq Scan on achfilerecord ach_1 (cost=0.00..86,673.18 rows=234,819 width=4) (actual time=0.057..526.098 rows=232,252 loops=1)

  • Filter: ((effectivedate >= '2016-10-01'::date) AND (loan_id <> 58836) AND (product = 'BALANCE_CREDIT'::text) AND (isorigination = 1))
  • Rows Removed by Filter: 1,534,626
196. 56.695 594.061 ↑ 1.0 142,270 1

Hash (cost=80,347.94..80,347.94 rows=143,260 width=24) (actual time=594.061..594.061 rows=142,270 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,829kB
197. 250.958 537.366 ↑ 1.0 142,270 1

Hash Join (cost=19,900.01..80,347.94 rows=143,260 width=24) (actual time=159.383..537.366 rows=142,270 loops=1)

  • Hash Cond: (ls_10.loan_id = lse_16.loan_id)
198. 128.137 128.137 ↓ 1.0 385,471 1

Seq Scan on loan_setup_entity ls_10 (cost=0.00..57,573.88 rows=384,388 width=8) (actual time=0.005..128.137 rows=385,471 loops=1)

199. 50.022 158.271 ↑ 1.0 142,270 1

Hash (cost=18,108.44..18,108.44 rows=143,325 width=16) (actual time=158.271..158.271 rows=142,270 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 8,717kB
200. 108.249 108.249 ↑ 1.0 142,270 1

Seq Scan on loan_settings_entity lse_16 (cost=0.00..18,108.44 rows=143,325 width=16) (actual time=0.007..108.249 rows=142,270 loops=1)

  • Filter: ((loan_status_id <> ALL ('{4,5}'::bigint[])) AND (loan_sub_status_id <> 41))
  • Rows Removed by Filter: 243,201
201. 0.002 10,646.686 ↓ 0.0 0 1

Subquery Scan on *SELECT* 24 (cost=95,472.70..102,262.40 rows=1 width=116) (actual time=10,646.685..10,646.686 rows=0 loops=1)

202. 0.002 10,646.684 ↓ 0.0 0 1

Nested Loop (cost=95,472.70..102,262.38 rows=1 width=110) (actual time=10,646.684..10,646.684 rows=0 loops=1)

203. 448.947 10,646.682 ↓ 0.0 0 1

Nested Loop (cost=95,472.28..102,261.60 rows=1 width=38) (actual time=10,646.682..10,646.682 rows=0 loops=1)

204. 788.741 2,469.545 ↓ 1,545,638.0 1,545,638 1

Hash Right Join (cost=95,471.85..102,260.93 rows=1 width=4) (actual time=1,808.300..2,469.545 rows=1,545,638 loops=1)

  • Hash Cond: (rr_3.originalachrecord_id = fr_3.achfilerecord_id)
  • Filter: (rr_3.returnfilerecord_id IS NULL)
  • Rows Removed by Filter: 219,736
205. 113.555 113.555 ↑ 1.0 219,754 1

Seq Scan on returnfilerecord rr_3 (cost=0.00..6,211.94 rows=219,863 width=8) (actual time=1.221..113.555 rows=219,754 loops=1)

  • Filter: (product = 'BALANCE_CREDIT'::text)
  • Rows Removed by Filter: 120
206. 708.193 1,567.249 ↓ 1.0 1,765,088 1

Hash (cost=73,426.61..73,426.61 rows=1,763,619 width=8) (actual time=1,567.249..1,567.249 rows=1,765,088 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 85,333kB
207. 859.056 859.056 ↓ 1.0 1,765,088 1

Seq Scan on achfilerecord fr_3 (cost=0.00..73,426.61 rows=1,763,619 width=8) (actual time=0.010..859.056 rows=1,765,088 loops=1)

  • Filter: (product = 'BALANCE_CREDIT'::text)
  • Rows Removed by Filter: 1,790
208. 7,728.190 7,728.190 ↓ 0.0 0 1,545,638

Index Scan using payment_entity_id_conditional on payment_entity p_3 (cost=0.43..0.66 rows=1 width=42) (actual time=0.005..0.005 rows=0 loops=1,545,638)

  • Index Cond: (id = fr_3.payment_id)
  • Filter: ((child IS NULL) AND (apply_date >= '2017-06-01'::date) AND ((reverse_reason)::text = 'payment.reverse.nachaErrorCode'::text))
  • Rows Removed by Filter: 1
209. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (loan_id = p_3.entity_id)
  • Filter: (loan_id <> 58239)
210. 0.001 4,968.822 ↓ 0.0 0 1

Subquery Scan on *SELECT* 25 (cost=8,961.08..35,245.53 rows=558 width=116) (actual time=4,968.822..4,968.822 rows=0 loops=1)

211. 3.702 4,968.821 ↓ 0.0 0 1

Hash Anti Join (cost=8,961.08..35,238.56 rows=558 width=112) (actual time=4,968.821..4,968.821 rows=0 loops=1)

  • Hash Cond: (fr_4.achfilerecord_id = rr_4.originalachrecord_id)
212. 4.905 4,807.377 ↓ 4.2 2,682 1

Nested Loop (cost=0.85..26,266.82 rows=637 width=20) (actual time=164.865..4,807.377 rows=2,682 loops=1)

  • Join Filter: (lse_18.loan_id = fr_4.loan_id)
213. 3.920 1,565.298 ↓ 2.6 2,682 1

Nested Loop (cost=0.42..24,934.74 rows=1,013 width=24) (actual time=159.705..1,565.298 rows=2,682 loops=1)

214. 69.428 69.428 ↓ 2.6 2,815 1

Seq Scan on loan_settings_entity lse_18 (cost=0.00..17,147.04 rows=1,066 width=16) (actual time=0.073..69.428 rows=2,815 loops=1)

  • Filter: (loan_sub_status_id = 41)
  • Rows Removed by Filter: 382,656
215. 1,491.950 1,491.950 ↑ 1.0 1 2,815

Index Scan using loan_setup_entity_loan_id on loan_setup_entity ls_11 (cost=0.42..7.31 rows=1 width=8) (actual time=0.530..0.530 rows=1 loops=2,815)

  • Index Cond: (loan_id = lse_18.loan_id)
  • Filter: (contract_date >= '2016-10-01'::date)
  • Rows Removed by Filter: 0
216. 3,237.174 3,237.174 ↑ 1.0 1 2,682

Index Scan using achfilerecord_loan_id on achfilerecord fr_4 (cost=0.43..1.30 rows=1 width=8) (actual time=1.206..1.207 rows=1 loops=2,682)

  • Index Cond: (loan_id = ls_11.loan_id)
  • Filter: ((isorigination = 1) AND (product = 'BALANCE_CREDIT'::text))
  • Rows Removed by Filter: 0
217. 72.624 157.742 ↑ 1.0 219,736 1

Hash (cost=6,211.94..6,211.94 rows=219,863 width=4) (actual time=157.742..157.742 rows=219,736 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,774kB
218. 85.118 85.118 ↑ 1.0 219,754 1

Seq Scan on returnfilerecord rr_4 (cost=0.00..6,211.94 rows=219,863 width=4) (actual time=0.010..85.118 rows=219,754 loops=1)

  • Filter: (product = 'BALANCE_CREDIT'::text)
  • Rows Removed by Filter: 120
219. 0.002 275.713 ↓ 0.0 0 1

Subquery Scan on *SELECT* 26 (cost=32,039.26..39,592.94 rows=29,588 width=116) (actual time=275.712..275.713 rows=0 loops=1)

220. 0.561 275.711 ↓ 0.0 0 1

Hash Join (cost=32,039.26..39,223.09 rows=29,588 width=112) (actual time=275.711..275.711 rows=0 loops=1)

  • Hash Cond: (p_4.entity_id = lse_19.loan_id)
221. 1.716 1.716 ↑ 171,569.0 1 1

Seq Scan on charge_entity p_4 (cost=0.00..5,948.69 rows=171,569 width=12) (actual time=1.716..1.716 rows=1 loops=1)

222. 0.001 273.434 ↓ 0.0 0 1

Hash (cost=31,210.24..31,210.24 rows=66,321 width=20) (actual time=273.434..273.434 rows=0 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 1,024kB
223. 0.003 273.433 ↓ 0.0 0 1

Hash Join (cost=28,971.91..31,210.24 rows=66,321 width=20) (actual time=273.433..273.433 rows=0 loops=1)

  • Hash Cond: (charge_entity.entity_id = lse_19.loan_id)
224. 117.261 273.430 ↓ 0.0 0 1

HashAggregate (cost=7,979.24..8,642.45 rows=66,321 width=16) (actual time=273.430..273.430 rows=0 loops=1)

  • Group Key: charge_entity.entity_id, charge_entity.related_payment_id
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 156,544
225. 156.169 156.169 ↓ 1.0 156,544 1

Seq Scan on charge_entity (cost=0.00..6,806.53 rows=156,361 width=8) (actual time=0.005..156.169 rows=156,544 loops=1)

  • Filter: ((related_payment_id IS NOT NULL) AND (deleted = 0) AND (active = 1))
  • Rows Removed by Filter: 15,228
226. 0.000 0.000 ↓ 0.0 0

Hash (cost=16,185.63..16,185.63 rows=384,563 width=16) (never executed)

227. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_settings_entity lse_19 (cost=0.00..16,185.63 rows=384,563 width=16) (never executed)

228. 0.002 4,546.262 ↓ 0.0 0 1

Subquery Scan on *SELECT* 27 (cost=53,601.87..219,034.31 rows=1 width=116) (actual time=4,546.261..4,546.262 rows=0 loops=1)

229. 7.704 4,546.260 ↓ 0.0 0 1

Nested Loop (cost=53,601.87..219,034.30 rows=1 width=112) (actual time=4,546.259..4,546.260 rows=0 loops=1)

  • Join Filter: (lc.customer_id = en.customer_id)
  • Rows Removed by Join Filter: 38,147
230. 0.006 3,336.186 ↑ 1.0 1 1

Nested Loop (cost=53,601.87..181,771.78 rows=1 width=24) (actual time=2,999.151..3,336.186 rows=1 loops=1)

231. 0.006 3,332.932 ↑ 1.0 1 1

Nested Loop (cost=53,601.45..181,771.10 rows=1 width=20) (actual time=2,995.901..3,332.932 rows=1 loops=1)

232. 0.006 3,329.051 ↑ 1.0 1 1

Nested Loop (cost=53,601.03..181,770.59 rows=1 width=24) (actual time=2,992.022..3,329.051 rows=1 loops=1)

233. 0.005 3,324.056 ↑ 1.0 1 1

Nested Loop (cost=53,600.60..181,770.10 rows=1 width=16) (actual time=2,987.029..3,324.056 rows=1 loops=1)

234. 0.009 3,322.822 ↑ 1.0 1 1

Nested Loop (cost=53,600.18..181,769.65 rows=1 width=12) (actual time=2,985.797..3,322.822 rows=1 loops=1)

235. 647.791 3,319.139 ↑ 1.0 1 1

Hash Left Join (cost=53,599.76..181,769.16 rows=1 width=8) (actual time=2,982.118..3,319.139 rows=1 loops=1)

  • Hash Cond: (p_5.id = t5.payment_id)
  • Filter: (t5.endpoint IS NULL)
  • Rows Removed by Filter: 1,225,644
236. 1,378.375 1,378.375 ↓ 1.2 1,225,645 1

Seq Scan on payment_entity p_5 (cost=0.00..125,509.15 rows=1,013,425 width=12) (actual time=0.151..1,378.375 rows=1,225,645 loops=1)

  • Filter: ((child IS NULL) AND (apply_date > '2017-01-01'::date) AND (((reverse_reason)::text <> ALL ('{payment.reverse.clericalError,payment.reverse.other}'::text[])) OR (reverse_reason IS NULL)) AND (active = 1) AND (deleted = 0) AND ((entity_type)::text = 'Entity.Loan'::text) AND (payment_method_id = 4))
  • Rows Removed by Filter: 361,941
237. 740.412 1,292.973 ↑ 1.0 1,577,564 1

Hash (cost=33,576.56..33,576.56 rows=1,601,856 width=18) (actual time=1,292.972..1,292.973 rows=1,577,564 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 93,472kB
238. 552.561 552.561 ↑ 1.0 1,577,564 1

Seq Scan on payment_custom_fields t5 (cost=0.00..33,576.56 rows=1,601,856 width=18) (actual time=0.008..552.561 rows=1,577,564 loops=1)

239. 3.674 3.674 ↑ 1.0 1 1

Index Only Scan using loan_entity_loan_id on loan_entity le_1 (cost=0.42..0.47 rows=1 width=4) (actual time=3.672..3.674 rows=1 loops=1)

  • Index Cond: (id = p_5.entity_id)
  • Heap Fetches: 1
240. 1.229 1.229 ↑ 1.0 1 1

Index Only Scan using loan_setup_entity_loan_id on loan_setup_entity ls_12 (cost=0.42..0.45 rows=1 width=4) (actual time=1.229..1.229 rows=1 loops=1)

  • Index Cond: (loan_id = p_5.entity_id)
  • Heap Fetches: 0
241. 4.989 4.989 ↑ 1.0 1 1

Index Scan using loan__customer_loan_id on loan__customer lc (cost=0.42..0.48 rows=1 width=8) (actual time=4.988..4.989 rows=1 loops=1)

  • Index Cond: (loan_id = p_5.entity_id)
242. 3.875 3.875 ↑ 1.0 1 1

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_20 (cost=0.42..0.49 rows=1 width=16) (actual time=3.874..3.875 rows=1 loops=1)

  • Index Cond: (loan_id = p_5.entity_id)
243. 3.248 3.248 ↑ 1.0 1 1

Index Only Scan using customer_entity_id on customer_entity c (cost=0.42..0.68 rows=1 width=4) (actual time=3.246..3.248 rows=1 loops=1)

  • Index Cond: (id = lc.customer_id)
  • Heap Fetches: 1
244. 14.848 1,202.370 ↑ 5.2 38,147 1

Subquery Scan on en (cost=0.00..34,789.69 rows=197,826 width=4) (actual time=1,164.105..1,202.370 rows=38,147 loops=1)

245. 317.765 1,187.522 ↑ 5.2 38,147 1

HashSetOp Except (cost=0.00..32,811.43 rows=197,826 width=8) (actual time=1,164.104..1,187.522 rows=38,147 loops=1)

246. 243.921 869.757 ↓ 1.1 610,109 1

Append (cost=0.00..31,440.44 rows=548,396 width=8) (actual time=1.356..869.757 rows=610,109 loops=1)

247. 168.874 387.957 ↑ 1.0 376,778 1

Subquery Scan on *SELECT* 1_1 (cost=0.00..16,339.51 rows=376,778 width=8) (actual time=1.354..387.957 rows=376,778 loops=1)

248. 219.083 219.083 ↑ 1.0 376,778 1

Seq Scan on payment_account_entity (cost=0.00..12,571.73 rows=376,778 width=4) (actual time=1.353..219.083 rows=376,778 loops=1)

  • Filter: ((entity_type)::text = 'Entity.Customer'::text)
249. 95.134 237.879 ↓ 1.4 233,331 1

Subquery Scan on *SELECT* 2_1 (cost=0.42..15,100.93 rows=171,618 width=8) (actual time=4.027..237.879 rows=233,331 loops=1)

250. 142.745 142.745 ↓ 1.4 233,331 1

Index Scan using payment_account_entity_lastupdated on payment_account_entity payment_account_entity_1 (cost=0.42..13,384.75 rows=171,618 width=4) (actual time=4.025..142.745 rows=233,331 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 38,229
251. 0.003 6,637.164 ↓ 0.0 0 1

Hash Join (cost=43.78..38,184.30 rows=21,251 width=116) (actual time=6,637.163..6,637.164 rows=0 loops=1)

  • Hash Cond: (lse_21.loan_status_id = s.id)
252. 100.708 6,637.161 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.28..37,850.59 rows=21,251 width=16) (actual time=6,637.161..6,637.161 rows=0 loops=1)

  • Filter: ((cf_4.paidnotifdate IS NULL) OR (cf_4.paidnotifdate = ''::text))
  • Rows Removed by Filter: 88,941
253. 117.334 310.583 ↓ 3.2 88,941 1

Hash Join (cost=1.86..18,632.15 rows=27,724 width=24) (actual time=6.794..310.583 rows=88,941 loops=1)

  • Hash Cond: (lse_21.loan_sub_status_id = ss.id)
254. 186.524 186.524 ↑ 1.0 321,512 1

Seq Scan on loan_settings_entity lse_21 (cost=0.00..17,147.04 rows=321,604 width=32) (actual time=0.011..186.524 rows=321,512 loops=1)

  • Filter: (closed_date > '2016-10-01'::date)
  • Rows Removed by Filter: 63,959
255. 0.010 6.725 ↑ 1.0 5 1

Hash (cost=1.80..1.80 rows=5 width=4) (actual time=6.725..6.725 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
256. 6.715 6.715 ↑ 1.0 5 1

Seq Scan on loan_sub_status_entity ss (cost=0.00..1.80 rows=5 width=4) (actual time=6.708..6.715 rows=5 loops=1)

  • Filter: (title = ANY ('{""Full Term Paid"",""Early Payoff"",Refinanced}'::text[]))
  • Rows Removed by Filter: 53
257. 6,225.870 6,225.870 ↑ 1.0 1 88,941

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cf_4 (cost=0.42..0.68 rows=1 width=7) (actual time=0.070..0.070 rows=1 loops=88,941)

  • Index Cond: (lse_21.id = loan_settings_id)
258. 0.000 0.000 ↓ 0.0 0

Hash (cost=24.00..24.00 rows=1,400 width=4) (never executed)

259. 0.000 0.000 ↓ 0.0 0

Seq Scan on loan_status_entity s (cost=0.00..24.00 rows=1,400 width=4) (never executed)

260. 175.753 5,056.711 ↓ 0.0 0 1

Nested Loop (cost=21,954.49..118,921.75 rows=1 width=116) (actual time=5,056.711..5,056.711 rows=0 loops=1)

261. 223.744 3,696.670 ↓ 296,072.0 296,072 1

Hash Right Join (cost=21,954.06..118,921.10 rows=1 width=24) (actual time=3,555.995..3,696.670 rows=296,072 loops=1)

  • Hash Cond: (f.loan_id = lse_22.loan_id)
  • Filter: (f.id IS NULL)
  • Rows Removed by Filter: 159,813
262. 3,171.490 3,171.490 ↑ 1.2 159,813 1

Seq Scan on loan_document_entity f (cost=0.00..94,324.43 rows=192,190 width=8) (actual time=33.163..3,171.490 rows=159,813 loops=1)

  • Filter: ((active = 1) AND (archived = 0) AND (deleted = 0) AND ((filename ~~ 'marked-paid%'::text) OR (filename ~~ 'paid-off-notice%'::text)))
  • Rows Removed by Filter: 1,949,053
263. 144.519 301.436 ↓ 1.0 385,470 1

Hash (cost=17,147.04..17,147.04 rows=384,562 width=24) (actual time=301.436..301.436 rows=385,470 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,177kB
264. 156.917 156.917 ↓ 1.0 385,470 1

Seq Scan on loan_settings_entity lse_22 (cost=0.00..17,147.04 rows=384,562 width=24) (actual time=0.007..156.917 rows=385,470 loops=1)

  • Filter: (loan_id <> 79895)
  • Rows Removed by Filter: 1
265. 1,184.288 1,184.288 ↓ 0.0 0 296,072

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cf_5 (cost=0.42..0.64 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=296,072)

  • Index Cond: (loan_settings_id = lse_22.id)
  • Filter: ((paidnotifdate IS NOT NULL) AND (paidnotifdate <> ''::text))
  • Rows Removed by Filter: 1
266. 27.483 861.589 ↓ 0.0 0 1

Nested Loop Left Join (cost=20,993.09..133,531.19 rows=26,969 width=116) (actual time=861.589..861.589 rows=0 loops=1)

  • Filter: ((cf_6.paidnotifdate IS NULL) OR (cf_6.paidnotifdate = ''::text))
  • Rows Removed by Filter: 27,763
267. 22.248 778.580 ↑ 1.3 27,763 1

Hash Join (cost=20,992.67..110,528.59 rows=35,183 width=24) (actual time=307.696..778.580 rows=27,763 loops=1)

  • Hash Cond: (f_1.loan_id = lse_23.loan_id)
268. 457.057 457.057 ↑ 1.3 27,763 1

Seq Scan on loan_document_entity f_1 (cost=0.00..89,052.16 rows=35,183 width=4) (actual time=5.936..457.057 rows=27,763 loops=1)

  • Filter: ((filename ~~ 'paid-off-notice%'::text) AND (active = 1) AND (archived = 0) AND (deleted = 0))
  • Rows Removed by Filter: 2,081,103
269. 149.839 299.275 ↓ 1.0 385,471 1

Hash (cost=16,185.63..16,185.63 rows=384,563 width=24) (actual time=299.275..299.275 rows=385,471 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,177kB
270. 149.436 149.436 ↓ 1.0 385,471 1

Seq Scan on loan_settings_entity lse_23 (cost=0.00..16,185.63 rows=384,563 width=24) (actual time=0.005..149.436 rows=385,471 loops=1)

271. 55.526 55.526 ↑ 1.0 1 27,763

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cf_6 (cost=0.42..0.64 rows=1 width=7) (actual time=0.002..0.002 rows=1 loops=27,763)

  • Index Cond: (lse_23.id = loan_settings_id)
272. 0.025 704.644 ↓ 0.0 0 1

Hash Join (cost=23,671.78..43,152.11 rows=82,043 width=116) (actual time=704.644..704.644 rows=0 loops=1)

  • Hash Cond: (lse_24.loan_status_id = s_1.id)
273. 28.573 703.678 ↓ 0.0 0 1

Hash Join (cost=23,630.28..41,982.52 rows=82,043 width=16) (actual time=703.677..703.678 rows=0 loops=1)

  • Hash Cond: (lse_24.loan_sub_status_id = ss_1.id)
274. 213.147 675.014 ↑ 1.0 89,318 1

Hash Join (cost=23,627.83..40,822.94 rows=89,783 width=24) (actual time=338.011..675.014 rows=89,318 loops=1)

  • Hash Cond: (lse_24.id = cf_7.loan_settings_id)
275. 124.463 124.463 ↓ 1.0 385,471 1

Seq Scan on loan_settings_entity lse_24 (cost=0.00..16,185.63 rows=384,563 width=32) (actual time=0.006..124.463 rows=385,471 loops=1)

276. 46.555 337.404 ↑ 1.0 89,318 1

Hash (cost=22,499.58..22,499.58 rows=90,260 width=4) (actual time=337.403..337.404 rows=89,318 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,165kB
277. 290.849 290.849 ↑ 1.0 89,318 1

Seq Scan on loan_settings_custom_fields cf_7 (cost=0.00..22,499.58 rows=90,260 width=4) (actual time=0.015..290.849 rows=89,318 loops=1)

  • Filter: ((paidnotifdate IS NOT NULL) AND (paidnotifdate <> ''::text))
  • Rows Removed by Filter: 296,153
278. 0.045 0.091 ↑ 1.0 53 1

Hash (cost=1.80..1.80 rows=53 width=4) (actual time=0.091..0.091 rows=53 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
279. 0.046 0.046 ↑ 1.0 53 1

Seq Scan on loan_sub_status_entity ss_1 (cost=0.00..1.80 rows=53 width=4) (actual time=0.010..0.046 rows=53 loops=1)

  • Filter: (title <> ALL ('{""Full Term Paid"",""Early Payoff"",Refinanced}'::text[]))
  • Rows Removed by Filter: 5
280. 0.011 0.941 ↑ 155.6 9 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 17kB
281. 0.930 0.930 ↑ 155.6 9 1

Seq Scan on loan_status_entity s_1 (cost=0.00..24.00 rows=1,400 width=4) (actual time=0.924..0.930 rows=9 loops=1)

282. 0.002 10,742.700 ↓ 0.0 0 1

Nested Loop Left Join (cost=21,168.36..21,253.77 rows=1 width=116) (actual time=10,742.700..10,742.700 rows=0 loops=1)

  • Filter: (ne.id IS NULL)
283. 145.071 10,742.698 ↓ 0.0 0 1

Nested Loop (cost=21,167.93..21,249.02 rows=1 width=16) (actual time=10,742.698..10,742.698 rows=0 loops=1)

284. 415.866 4,501.249 ↓ 320,862.0 320,862 1

Nested Loop (cost=21,167.51..21,247.83 rows=1 width=24) (actual time=376.773..4,501.249 rows=320,862 loops=1)

285. 191.101 555.967 ↓ 320,856.0 320,856 1

Hash Right Join (cost=21,167.09..21,247.34 rows=1 width=24) (actual time=374.220..555.967 rows=320,856 loops=1)

  • Hash Cond: ((replace(replace(""substring""((sne.note_title)::text, '\d{1,7}\''\.$'::text), ''''::text, ''::text), '.'::text, ''::text))::integer = lse_25.loan_id)
  • Filter: (sne.id IS NULL)
  • Rows Removed by Filter: 656
286. 7.013 7.013 ↑ 1.0 656 1

Seq Scan on system_note_customer_email sne (cost=0.00..68.20 rows=656 width=157) (actual time=1.008..7.013 rows=656 loops=1)

  • Filter: (note_data ~~* '%Paid Off Notice%'::text)
287. 144.688 357.853 ↑ 1.0 321,512 1

Hash (cost=17,147.04..17,147.04 rows=321,604 width=24) (actual time=357.852..357.853 rows=321,512 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,679kB
288. 213.165 213.165 ↑ 1.0 321,512 1

Seq Scan on loan_settings_entity lse_25 (cost=0.00..17,147.04 rows=321,604 width=24) (actual time=0.010..213.165 rows=321,512 loops=1)

  • Filter: (closed_date > '2016-10-01'::date)
  • Rows Removed by Filter: 63,959
289. 3,529.416 3,529.416 ↑ 1.0 1 320,856

Index Only Scan using loan__customer_loan_id on loan__customer lc_1 (cost=0.42..0.48 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=320,856)

  • Index Cond: (loan_id = lse_25.loan_id)
  • Heap Fetches: 73,799
290. 6,096.378 6,096.378 ↓ 0.0 0 320,862

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cf_8 (cost=0.42..1.19 rows=1 width=4) (actual time=0.019..0.019 rows=0 loops=320,862)

  • Index Cond: (loan_settings_id = lse_25.id)
  • Filter: ((dw_reporting_meta.text_to_date(paidnotifdate) < CURRENT_DATE) AND (dw_reporting_meta.text_to_date(paidnotifdate) > (CURRENT_DATE - '5 days'::interval)))
  • Rows Removed by Filter: 1
291. 0.000 0.000 ↓ 0.0 0

Index Scan using note_entity_parent_id_parent_type on note_entity ne (cost=0.43..4.74 rows=1 width=8) (never executed)

  • Index Cond: ((parent_id = lse_25.loan_id) AND ((parent_type)::text = 'Entity.Loan'::text))
  • Filter: (category_id = 82)
292. 19.523 111,088.472 ↓ 0.0 0 1

Nested Loop (cost=38,676.60..305,111.85 rows=3,824 width=68) (actual time=111,088.472..111,088.472 rows=0 loops=1)

  • Join Filter: (cfe1.custom_field_value <> cfe2.custom_field_value)
  • Rows Removed by Join Filter: 8,999
293. 56.785 109,890.080 ↓ 1.3 8,999 1

Nested Loop (cost=38,676.17..260,118.33 rows=7,102 width=40) (actual time=377.306..109,890.080 rows=8,999 loops=1)

294. 222.568 92,902.495 ↓ 1.8 23,515 1

Merge Join (cost=38,675.75..178,678.61 rows=12,865 width=28) (actual time=353.538..92,902.495 rows=23,515 loops=1)

  • Merge Cond: (lse_26.id = cfe3.entity_id)
295. 224.265 352.699 ↑ 1.0 240,386 1

Sort (cost=38,675.29..39,277.38 rows=240,839 width=24) (actual time=243.689..352.699 rows=240,386 loops=1)

  • Sort Key: lse_26.id
  • Sort Method: quicksort Memory: 24,925kB
296. 128.434 128.434 ↑ 1.0 240,386 1

Seq Scan on loan_settings_entity lse_26 (cost=0.00..17,147.04 rows=240,839 width=24) (actual time=0.036..128.434 rows=240,386 loops=1)

  • Filter: (loan_status_id = ANY ('{4,5}'::bigint[]))
  • Rows Removed by Filter: 145,085
297. 92,327.228 92,327.228 ↓ 4.1 83,896 1

Index Scan using custom_field__entity_entity_id_purchase_date_indx on custom_field__entity cfe3 (cost=0.42..172,088.31 rows=20,542 width=4) (actual time=28.635..92,327.228 rows=83,896 loops=1)

  • Filter: ((custom_field_value IS NULL) OR (custom_field_value = ''::text))
  • Rows Removed by Filter: 104,884
298. 16,930.800 16,930.800 ↓ 0.0 0 23,515

Index Scan using custom_field__entity_entity_id_original_owner_indx on custom_field__entity cfe1 (cost=0.42..6.31 rows=2 width=12) (actual time=0.720..0.720 rows=0 loops=23,515)

  • Index Cond: (entity_id = lse_26.id)
  • Filter: (custom_field_value <> 'SunUp Financial'::text)
  • Rows Removed by Filter: 1
299. 1,178.869 1,178.869 ↑ 2.0 1 8,999

Index Scan using custom_field__entity_entity_id_current_owner_indx on custom_field__entity cfe2 (cost=0.42..6.31 rows=2 width=12) (actual time=0.130..0.131 rows=1 loops=8,999)

  • Index Cond: (entity_id = lse_26.id)
  • Filter: (custom_field_value <> 'SPV2'::text)
300. 0.002 24,855.685 ↓ 0.0 0 1

Subquery Scan on *SELECT* 34 (cost=24,031.79..1,170,500.77 rows=31 width=116) (actual time=24,855.685..24,855.685 rows=0 loops=1)

301. 136.672 24,855.683 ↓ 0.0 0 1

Hash Join (cost=24,031.79..1,170,500.15 rows=31 width=96) (actual time=24,855.682..24,855.683 rows=0 loops=1)

  • Hash Cond: (lsa_1.loan_id = lse_27.loan_id)
302.          

CTE max_date_qry

303. 0.006 9.712 ↑ 1.0 1 1

Result (cost=0.77..0.78 rows=1 width=4) (actual time=9.711..9.712 rows=1 loops=1)

304.          

Initplan (for Result)

305. 0.005 9.706 ↑ 1.0 1 1

Limit (cost=0.57..0.77 rows=1 width=4) (actual time=9.703..9.706 rows=1 loops=1)

306. 9.701 9.701 ↑ 203,727,347.0 1 1

Index Only Scan Backward using loan_status_archive_date_loan_id_rename on loan_status_archive (cost=0.57..40,184,582.14 rows=203,727,347 width=4) (actual time=9.701..9.701 rows=1 loops=1)

  • Index Cond: ((date IS NOT NULL) AND (date <= CURRENT_DATE))
  • Heap Fetches: 1
307. 188.784 24,643.512 ↓ 70.0 191,400 1

Nested Loop (cost=17,726.99..1,164,184.62 rows=2,735 width=26) (actual time=403.823..24,643.512 rows=191,400 loops=1)

308. 9.717 9.717 ↑ 1.0 1 1

CTE Scan on max_date_qry (cost=0.00..0.02 rows=1 width=4) (actual time=9.715..9.717 rows=1 loops=1)

309. 24,067.340 24,445.011 ↓ 70.0 191,400 1

Bitmap Heap Scan on loan_status_archive lsa_1 (cost=17,726.99..1,164,157.25 rows=2,735 width=30) (actual time=394.032..24,445.011 rows=191,400 loops=1)

  • Recheck Cond: ((date = max_date_qry.max_date) OR (date = (max_date_qry.max_date - 1)))
  • Filter: ((last_payment_date IS NOT NULL) AND (next_payment_date IS NULL) AND (payoff >= '0'::numeric) AND (payoff <= 0.09) AND ((CURRENT_DATE - last_payment_date) > 0))
  • Rows Removed by Filter: 572,793
  • Heap Blocks: exact=65,631
310. 0.010 377.671 ↓ 0.0 0 1

BitmapOr (cost=17,726.99..17,726.99 rows=328,063 width=0) (actual time=377.670..377.671 rows=0 loops=1)

311. 191.011 191.011 ↓ 4.7 764,285 1

Bitmap Index Scan on loan_status_archive_date_loan_id_rename (cost=0.00..8,862.81 rows=164,032 width=0) (actual time=191.011..191.011 rows=764,285 loops=1)

  • Index Cond: (date = max_date_qry.max_date)
312. 186.650 186.650 ↓ 2.3 382,033 1

Bitmap Index Scan on loan_status_archive_date_loan_id_rename (cost=0.00..8,862.81 rows=164,032 width=0) (actual time=186.650..186.650 rows=382,033 loops=1)

  • Index Cond: (date = (max_date_qry.max_date - 1))
313. 20.381 75.499 ↓ 7.4 32,433 1

Hash (cost=6,249.21..6,249.21 rows=4,385 width=16) (actual time=75.498..75.499 rows=32,433 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,777kB
314. 55.118 55.118 ↓ 7.4 32,433 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_27 (cost=0.29..6,249.21 rows=4,385 width=16) (actual time=0.907..55.118 rows=32,433 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 121
315. 33,385.015 33,385.015 ↓ 3.0 3 1

CTE Scan on y (cost=637,499.04..637,499.07 rows=1 width=116) (actual time=33,384.255..33,385.015 rows=3 loops=1)

  • Filter: (business_days_since_last_payment > days_to_clear)
  • Rows Removed by Filter: 236
316.          

CTE payment_date

317. 21,785.050 30,815.925 ↓ 1.0 1,356,009 1

WindowAgg (cost=241,755.04..591,054.85 rows=1,293,703 width=24) (actual time=8,443.896..30,815.925 rows=1,356,009 loops=1)

318. 1,965.093 9,030.875 ↓ 1.0 1,356,009 1

Sort (cost=241,755.04..244,989.29 rows=1,293,703 width=12) (actual time=8,438.421..9,030.875 rows=1,356,009 loops=1)

  • Sort Key: payment_entity_3.entity_id, payment_entity_3.apply_date DESC
  • Sort Method: quicksort Memory: 112,715kB
319. 7,065.782 7,065.782 ↓ 1.0 1,356,009 1

Seq Scan on payment_entity payment_entity_3 (cost=0.00..110,424.29 rows=1,293,703 width=12) (actual time=3.082..7,065.782 rows=1,356,009 loops=1)

  • Filter: ((child IS NULL) AND (deleted = 0) AND (active = 1))
  • Rows Removed by Filter: 231,577
320.          

CTE y

321. 0.964 33,384.814 ↓ 239.0 239 1

GroupAggregate (cost=46,444.17..46,444.19 rows=1 width=28) (actual time=33,383.642..33,384.814 rows=239 loops=1)

  • Group Key: x_2.loan_id, x_2.days_to_clear, x_2.loan_status_id
322. 1.189 33,383.850 ↓ 734.0 734 1

Sort (cost=46,444.17..46,444.17 rows=1 width=24) (actual time=33,383.595..33,383.850 rows=734 loops=1)

  • Sort Key: x_2.loan_id, x_2.days_to_clear, x_2.loan_status_id
  • Sort Method: quicksort Memory: 82kB
323. 0.691 33,382.661 ↓ 734.0 734 1

Subquery Scan on x_2 (cost=17,161.21..46,444.16 rows=1 width=24) (actual time=19,803.148..33,382.661 rows=734 loops=1)

324. 20.868 33,381.970 ↓ 734.0 734 1

Nested Loop (cost=17,161.21..46,444.15 rows=1 width=32) (actual time=19,803.146..33,381.970 rows=734 loops=1)

  • Join Filter: (lse_41.loan_id = ls_13.loan_id)
325. 2.072 33,229.716 ↓ 734.0 734 1

Nested Loop (cost=17,160.79..46,439.61 rows=1 width=36) (actual time=19,799.759..33,229.716 rows=734 loops=1)

  • Join Filter: (lse_41.loan_id = lsa_7.loan_id)
326. 109.323 32,735.894 ↓ 19.4 350 1

Hash Join (cost=17,160.36..46,293.12 rows=18 width=28) (actual time=19,759.182..32,735.894 rows=350 loops=1)

  • Hash Cond: (pd.entity_id = lse_41.loan_id)
327. 32,109.576 32,109.576 ↓ 31.8 205,702 1

CTE Scan on payment_date pd (cost=0.00..29,108.32 rows=6,469 width=12) (actual time=8,443.901..32,109.576 rows=205,702 loops=1)

  • Filter: (row_number = 1)
  • Rows Removed by Filter: 1,150,307
328. 0.221 516.995 ↑ 3.0 350 1

Hash (cost=17,147.04..17,147.04 rows=1,066 width=16) (actual time=516.994..516.995 rows=350 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 33kB
329. 516.774 516.774 ↑ 3.0 350 1

Seq Scan on loan_settings_entity lse_41 (cost=0.00..17,147.04 rows=1,066 width=16) (actual time=362.355..516.774 rows=350 loops=1)

  • Filter: (loan_sub_status_id = 39)
  • Rows Removed by Filter: 385,121
330. 491.750 491.750 ↓ 2.0 2 350

Index Scan using loan_status_archive_active_sub_status_id_39_rename on loan_status_archive lsa_7 (cost=0.43..8.13 rows=1 width=12) (actual time=0.863..1.405 rows=2 loops=350)

  • Index Cond: ((loan_id = pd.entity_id) AND (date >= pd.payment_date) AND (date <= (CURRENT_DATE - 1)))
  • Filter: (payoff < 0.10)
331. 131.386 131.386 ↑ 1.0 1 734

Index Only Scan using loan_setup_entity_loan_id on loan_setup_entity ls_13 (cost=0.42..4.26 rows=1 width=4) (actual time=0.179..0.179 rows=1 loops=734)

  • Index Cond: (loan_id = lsa_7.loan_id)
  • Heap Fetches: 15
332. 0.001 1,168.218 ↓ 0.0 0 1

Subquery Scan on *SELECT* 36 (cost=0.87..60,596.07 rows=1,985 width=116) (actual time=1,168.218..1,168.218 rows=0 loops=1)

333. 24.735 1,168.217 ↓ 0.0 0 1

Nested Loop (cost=0.87..60,566.29 rows=1,985 width=108) (actual time=1,168.216..1,168.217 rows=0 loops=1)

334. 968.490 968.490 ↓ 7.2 21,874 1

Index Scan using custom_field__entity_last_updated on custom_field__entity cfe_3 (cost=0.44..37,896.22 rows=3,018 width=12) (actual time=36.618..968.490 rows=21,874 loops=1)

  • Index Cond: (lastupdated >= (now() - '15 days'::interval))
  • Filter: ((entity_type = 'Entity.Payment'::bpchar) AND (custom_field_id = 66))
  • Rows Removed by Filter: 254,559
335. 174.992 174.992 ↓ 0.0 0 21,874

Index Scan using payment_entity_id on payment_entity pe_4 (cost=0.43..7.50 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=21,874)

  • Index Cond: (id = cfe_3.entity_id)
  • Filter: ((reverse_date IS NULL) AND (apply_date >= '2017-01-01'::date) AND (CASE WHEN ((parent IS NOT NULL) AND (comments IS NOT NULL) AND ((status)::text = 'payment.status.none'::text)) THEN NULL::integer ELSE 1 END IS NOT NULL) AND (payment_method_id = 4) AND (active = 1) AND (deleted = 0) AND (apply_date <= CURRENT_DATE) AND (((entity_id)::text || (display_id)::text) <> regexp_replace(cfe_3.custom_field_value, '\r'::text, ''::text)) AND (((entity_id)::text || ((display_id - 1))::text) <> regexp_replace(cfe_3.custom_field_value, '\r'::text, ''::text)))
  • Rows Removed by Filter: 1
336. 0.122 649.527 ↓ 0.0 0 1

Hash Join (cost=23,738.23..40,933.35 rows=21,656 width=69) (actual time=649.527..649.527 rows=0 loops=1)

  • Hash Cond: (lse_28.id = scf.loan_settings_id)
337. 0.008 0.008 ↑ 384,563.0 1 1

Seq Scan on loan_settings_entity lse_28 (cost=0.00..16,185.63 rows=384,563 width=24) (actual time=0.007..0.008 rows=1 loops=1)

338. 0.002 649.397 ↓ 0.0 0 1

Hash (cost=23,466.09..23,466.09 rows=21,771 width=21) (actual time=649.397..649.397 rows=0 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 256kB
339. 649.395 649.395 ↓ 0.0 0 1

Seq Scan on loan_settings_custom_fields scf (cost=0.00..23,466.09 rows=21,771 width=21) (actual time=649.394..649.395 rows=0 loops=1)

  • Filter: (((pledge_date IS NULL) OR (pledge_date = ''::text)) AND (owner = 'SPV2'::text))
  • Rows Removed by Filter: 385,471
340. 0.203 3,234.084 ↓ 124.0 124 1

Result (cost=124.17..6,422.29 rows=1 width=87) (actual time=272.776..3,234.084 rows=124 loops=1)

  • One-Time Filter: (NOT (hashed SubPlan 6))
341. 0.790 3,233.233 ↓ 124.0 124 1

Nested Loop (cost=124.17..6,422.29 rows=1 width=87) (actual time=272.091..3,233.233 rows=124 loops=1)

342. 3.472 2,866.171 ↓ 194.0 194 1

Nested Loop (cost=122.13..6,412.41 rows=1 width=27) (actual time=75.374..2,866.171 rows=194 loops=1)

343. 7.643 2,474.734 ↓ 2,503.0 2,503 1

Nested Loop (cost=121.70..6,404.31 rows=1 width=32) (actual time=45.199..2,474.734 rows=2,503 loops=1)

  • Join Filter: (lse_29.loan_id = lset.loan_id)
344. 44.776 344.493 ↓ 3,457.0 3,457 1

Nested Loop (cost=121.28..6,396.49 rows=1 width=28) (actual time=44.164..344.493 rows=3,457 loops=1)

345. 30.056 118.541 ↓ 30,196.0 30,196 1

Merge Left Join (cost=120.99..6,388.82 rows=1 width=24) (actual time=38.300..118.541 rows=30,196 loops=1)

  • Merge Cond: (lse_29.loan_id = pe_5.entity_id)
  • Filter: (pe_5.id IS NULL)
  • Rows Removed by Filter: 2,268
346. 49.556 49.556 ↓ 7.4 32,433 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_29 (cost=0.29..6,253.34 rows=4,385 width=24) (actual time=0.662..49.556 rows=32,433 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 121
347. 2.793 38.929 ↓ 3.2 2,396 1

Sort (cost=120.70..122.56 rows=746 width=8) (actual time=37.628..38.929 rows=2,396 loops=1)

  • Sort Key: pe_5.entity_id
  • Sort Method: quicksort Memory: 209kB
348. 36.136 36.136 ↓ 3.2 2,396 1

Index Scan using payment_entity_apply_date on payment_entity pe_5 (cost=0.43..85.10 rows=746 width=8) (actual time=5.883..36.136 rows=2,396 loops=1)

  • Index Cond: (apply_date = (now())::date)
349. 181.176 181.176 ↓ 0.0 0 30,196

Index Scan using rtc_note_parent_id on rtc_note rtc (cost=0.29..7.66 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=30,196)

  • Index Cond: (parent_id = lse_29.loan_id)
  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
350. 2,122.598 2,122.598 ↑ 1.0 1 3,457

Index Scan using loan_setup_entity_loan_id on loan_setup_entity lset (cost=0.42..7.80 rows=1 width=4) (actual time=0.614..0.614 rows=1 loops=3,457)

  • Index Cond: (loan_id = rtc.parent_id)
  • Filter: ((CURRENT_DATE - first_payment_date) > 90)
  • Rows Removed by Filter: 0
351. 387.965 387.965 ↓ 0.0 0 2,503

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lcf (cost=0.42..8.11 rows=1 width=7) (actual time=0.155..0.155 rows=0 loops=2,503)

  • Index Cond: (loan_settings_id = lse_29.id)
  • Filter: ((now())::date > (dw_reporting_meta.text_to_date(rtc_notice_date) + 30))
  • Rows Removed by Filter: 1
352. 366.272 366.272 ↑ 1.0 1 194

Index Scan using loan_status_archive_loan_id_date_rename on loan_status_archive lsa_2 (cost=0.57..8.40 rows=1 width=8) (actual time=1.888..1.888 rows=1 loops=194)

  • Index Cond: ((loan_id = lset.loan_id) AND (date = CURRENT_DATE))
  • Filter: (days_past_due > 90)
  • Rows Removed by Filter: 0
353.          

SubPlan (for Result)

354. 0.648 0.648 ↑ 1.0 37 1

Seq Scan on bank_holidays bank_holidays_3 (cost=0.00..1.37 rows=37 width=4) (actual time=0.631..0.648 rows=37 loops=1)

355. 0.011 20,705.446 ↑ 2,243.9 7 1

Result (cost=78,751.37..151,771.61 rows=15,707 width=87) (actual time=19,986.984..20,705.446 rows=7 loops=1)

356. 0.010 20,705.435 ↑ 2,243.9 7 1

Append (cost=78,751.37..151,614.54 rows=15,707 width=51) (actual time=19,986.980..20,705.435 rows=7 loops=1)

357. 195.369 19,987.186 ↑ 1,227.0 1 1

Hash Right Join (cost=78,751.37..83,267.55 rows=1,227 width=51) (actual time=19,986.979..19,987.186 rows=1 loops=1)

  • Hash Cond: (rtc_1.parent_id = lse_30.loan_id)
  • Filter: ((lsa_3.loan_id IS NOT NULL) OR (pe_6.id IS NOT NULL) OR (rtc_1.id IS NULL) OR (lcf_1.loan_settings_id IS NOT NULL) OR (hashed SubPlan 5))
  • Rows Removed by Filter: 9,746
358. 84.035 84.035 ↑ 1.0 95,042 1

Seq Scan on rtc_note rtc_1 (cost=0.00..4,071.03 rows=95,042 width=8) (actual time=0.886..84.035 rows=95,042 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
359. 15.200 19,707.759 ↓ 7.9 9,727 1

Hash (cost=78,734.57..78,734.57 rows=1,227 width=42) (actual time=19,707.759..19,707.759 rows=9,727 loops=1)

  • Buckets: 16,384 (originally 2048) Batches: 1 (originally 1) Memory Usage: 689kB
360. 59.530 19,692.559 ↓ 7.9 9,727 1

Nested Loop Left Join (cost=2.80..78,734.57 rows=1,227 width=42) (actual time=13.826..19,692.559 rows=9,727 loops=1)

  • Join Filter: ((lsa_3.days_past_due <= 90) AND (pe_6.apply_date = dw_reporting_meta.text_to_date(lscf_1.called_due_notice_date)))
  • Rows Removed by Join Filter: 61,809
361. 25.294 18,922.958 ↓ 7.9 9,727 1

Nested Loop Left Join (cost=2.38..62,358.38 rows=1,227 width=38) (actual time=5.384..18,922.958 rows=9,727 loops=1)

362. 220.149 18,566.946 ↓ 7.9 9,727 1

Nested Loop Left Join (cost=1.95..52,227.03 rows=1,227 width=39) (actual time=5.325..18,566.946 rows=9,727 loops=1)

  • Join Filter: (NOT (alternatives: SubPlan 3 or hashed SubPlan 4))
  • Rows Removed by Join Filter: 51
363. 23.418 400.417 ↓ 7.9 9,727 1

Nested Loop (cost=1.13..24,486.89 rows=1,227 width=31) (actual time=1.756..400.417 rows=9,727 loops=1)

364. 17.225 240.821 ↓ 7.9 9,727 1

Nested Loop (cost=0.71..14,674.56 rows=1,227 width=28) (actual time=0.906..240.821 rows=9,727 loops=1)

365. 77.691 77.691 ↓ 7.9 9,727 1

Index Scan using loan_settings_entity_id_cond on loan_settings_entity lse_30 (cost=0.29..9,394.24 rows=1,228 width=24) (actual time=0.832..77.691 rows=9,727 loops=1)

  • Filter: (loan_sub_status_id = 55)
  • Rows Removed by Filter: 43,769
366. 145.905 145.905 ↑ 1.0 1 9,727

Index Only Scan using loan_setup_entity_loan_id on loan_setup_entity lset_1 (cost=0.42..4.30 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=9,727)

  • Index Cond: (loan_id = lse_30.loan_id)
  • Heap Fetches: 33
367. 136.178 136.178 ↑ 1.0 1 9,727

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_1 (cost=0.42..8.00 rows=1 width=7) (actual time=0.014..0.014 rows=1 loops=9,727)

  • Index Cond: (loan_settings_id = lse_30.id)
368. 17,936.588 17,936.588 ↓ 0.0 0 9,727

Index Scan using loan_status_archive_date_loan_id_rename on loan_status_archive lsa_3 (cost=0.82..8.84 rows=1 width=16) (actual time=1.844..1.844 rows=0 loops=9,727)

  • Index Cond: ((date = dw_reporting_meta.text_to_date(lscf_1.called_due_notice_date)) AND (loan_id = lse_30.loan_id))
  • Filter: (days_past_due <= 90)
  • Rows Removed by Filter: 1
369.          

SubPlan (for Nested Loop Left Join)

370. 9.792 9.792 ↑ 1.0 1 51

Index Scan using payment_entity_entity_id on payment_entity payment_entity_1 (cost=0.43..13.76 rows=1 width=0) (actual time=0.192..0.192 rows=1 loops=51)

  • Index Cond: (entity_id = lset_1.loan_id)
  • Filter: ((reverse_reason IS NULL) AND (deleted = 0) AND (((created - '06:00:00'::interval))::date = dw_reporting_meta.text_to_date(lscf_1.called_due_notice_date)))
  • Rows Removed by Filter: 7
371. 0.000 0.000 ↓ 0.0 0

Seq Scan on payment_entity payment_entity_2 (cost=0.00..113,122.99 rows=1,293,982 width=8) (never executed)

  • Filter: ((reverse_reason IS NULL) AND (deleted = 0))
372. 330.718 330.718 ↓ 0.0 0 9,727

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lcf_1 (cost=0.42..8.26 rows=1 width=7) (actual time=0.034..0.034 rows=0 loops=9,727)

  • Index Cond: (loan_settings_id = lse_30.id)
  • Filter: ((dw_reporting_meta.text_to_date(rtc_notice_date) + 30) > (now())::date)
  • Rows Removed by Filter: 1
373. 710.071 710.071 ↑ 1.7 6 9,727

Index Scan using payment_entity_entity_id on payment_entity pe_6 (cost=0.43..10.70 rows=10 width=12) (actual time=0.059..0.073 rows=6 loops=9,727)

  • Index Cond: (entity_id = lse_30.loan_id)
374.          

SubPlan (for Hash Right Join)

375. 0.023 0.023 ↑ 1.0 37 1

Seq Scan on bank_holidays bank_holidays_2 (cost=0.00..1.37 rows=37 width=4) (actual time=0.007..0.023 rows=37 loops=1)

376. 41.329 326.586 ↑ 1,251.0 6 1

Hash Join (cost=4,335.71..27,246.16 rows=7,506 width=51) (actual time=266.575..326.586 rows=6 loops=1)

  • Hash Cond: (cf_9.loan_settings_id = lse_31.id)
377. 229.513 229.513 ↑ 1.0 88,707 1

Seq Scan on loan_settings_custom_fields cf_9 (cost=0.00..22,499.58 rows=89,551 width=7) (actual time=0.010..229.513 rows=88,707 loops=1)

  • Filter: (called_due_notice_date <> ''::text)
  • Rows Removed by Filter: 296,764
378. 18.117 55.744 ↓ 1.0 32,433 1

Hash (cost=3,930.63..3,930.63 rows=32,406 width=24) (actual time=55.743..55.744 rows=32,433 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,030kB
379. 37.627 37.627 ↓ 1.0 32,433 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_31 (cost=0.29..3,930.63 rows=32,406 width=24) (actual time=0.020..37.627 rows=32,433 loops=1)

  • Filter: (loan_sub_status_id = 20)
  • Rows Removed by Filter: 121
380. 82.176 391.653 ↓ 0.0 0 1

Hash Join (cost=17,260.49..40,943.76 rows=6,974 width=51) (actual time=391.653..391.653 rows=0 loops=1)

  • Hash Cond: (cf_10.loan_settings_id = lse_32.id)
381. 246.899 246.899 ↑ 1.0 296,764 1

Seq Scan on loan_settings_custom_fields cf_10 (cost=0.00..22,499.58 rows=297,055 width=7) (actual time=0.009..246.899 rows=296,764 loops=1)

  • Filter: (called_due_notice_date = ''::text)
  • Rows Removed by Filter: 88,707
382. 3.475 62.578 ↓ 1.1 9,727 1

Hash (cost=17,147.04..17,147.04 rows=9,076 width=24) (actual time=62.577..62.578 rows=9,727 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 660kB
383. 59.103 59.103 ↓ 1.1 9,727 1

Seq Scan on loan_settings_entity lse_32 (cost=0.00..17,147.04 rows=9,076 width=24) (actual time=1.521..59.103 rows=9,727 loops=1)

  • Filter: (loan_sub_status_id = 55)
  • Rows Removed by Filter: 375,744
384. 0.008 169,900.469 ↑ 5,863.0 3 1

Subquery Scan on x_1 (cost=0.57..868,062.97 rows=17,589 width=116) (actual time=166,357.292..169,900.469 rows=3 loops=1)

385. 0.013 169,900.461 ↑ 5,863.0 3 1

Append (cost=0.57..867,887.08 rows=17,589 width=56) (actual time=166,357.289..169,900.461 rows=3 loops=1)

386. 0.002 166,159.201 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_2 (cost=0.57..808,659.72 rows=17,296 width=56) (actual time=166,159.200..166,159.201 rows=0 loops=1)

387. 212.203 166,159.199 ↓ 0.0 0 1

Nested Loop (cost=0.57..808,357.04 rows=17,296 width=44) (actual time=166,159.199..166,159.199 rows=0 loops=1)

388. 98.706 98.706 ↑ 1.0 95,042 1

Seq Scan on rtc_note rtc_2 (cost=0.00..3,833.42 rows=95,042 width=12) (actual time=0.008..98.706 rows=95,042 loops=1)

389. 165,848.290 165,848.290 ↓ 0.0 0 95,042

Index Scan using loan_status_archive_loan_id_date_rename on loan_status_archive lsa_4 (cost=0.57..8.45 rows=1 width=28) (actual time=1.745..1.745 rows=0 loops=95,042)

  • Index Cond: ((loan_id = rtc_2.parent_id) AND (date = (rtc_2.lastupdated)::date))
  • Filter: ((loan_status_id <> 4) AND (loan_sub_status_id <> 20) AND (rtc_2.lastupdated > lastupdated))
  • Rows Removed by Filter: 1
390. 0.007 225.277 ↑ 1.0 1 1

Nested Loop (cost=6,309.21..21,975.94 rows=1 width=56) (actual time=198.085..225.277 rows=1 loops=1)

391. 0.023 225.254 ↑ 1.0 1 1

Nested Loop Left Join (cost=6,308.79..21,971.66 rows=1 width=36) (actual time=198.064..225.254 rows=1 loops=1)

  • Join Filter: (pe_7.apply_date = (rtc_3.lastupdated)::date)
  • Rows Removed by Join Filter: 11
  • Filter: (pe_7.id IS NULL)
  • Rows Removed by Filter: 2
392. 4.094 219.360 ↑ 64.7 3 1

Nested Loop (cost=6,308.36..19,919.33 rows=194 width=44) (actual time=154.600..219.360 rows=3 loops=1)

  • Join Filter: (lse_33.loan_id = lsa_5.loan_id)
393. 53.153 187.442 ↓ 3.2 3,478 1

Hash Join (cost=6,307.79..10,746.06 rows=1,084 width=36) (actual time=147.470..187.442 rows=3,478 loops=1)

  • Hash Cond: (rtc_3.parent_id = lse_33.loan_id)
394. 61.101 61.101 ↑ 1.0 95,042 1

Seq Scan on rtc_note rtc_3 (cost=0.00..4,071.03 rows=95,042 width=12) (actual time=0.014..61.101 rows=95,042 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
395. 24.187 73.188 ↓ 7.4 32,433 1

Hash (cost=6,252.98..6,252.98 rows=4,385 width=24) (actual time=73.188..73.188 rows=32,433 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,030kB
396. 49.001 49.001 ↓ 7.4 32,433 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_33 (cost=0.29..6,252.98 rows=4,385 width=24) (actual time=0.027..49.001 rows=32,433 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 121
397. 27.824 27.824 ↓ 0.0 0 3,478

Index Scan using loan_status_archive_loan_id_date_rename on loan_status_archive lsa_5 (cost=0.57..8.45 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=3,478)

  • Index Cond: ((loan_id = rtc_3.parent_id) AND (date = (rtc_3.lastupdated)::date))
  • Filter: (days_past_due < 60)
  • Rows Removed by Filter: 1
398. 5.871 5.871 ↓ 4.0 4 3

Index Scan using payment_entity_entity_id on payment_entity pe_7 (cost=0.43..10.56 rows=1 width=12) (actual time=0.312..1.957 rows=4 loops=3)

  • Index Cond: (entity_id = lse_33.loan_id)
  • Filter: ((reverse_date IS NULL) AND (payment_method_id = 3))
  • Rows Removed by Filter: 19
399. 0.016 0.016 ↑ 1.0 1 1

Index Only Scan using loan_setup_entity_loan_id on loan_setup_entity lset_2 (cost=0.42..4.27 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: (loan_id = lse_33.loan_id)
  • Heap Fetches: 0
400. 23.432 1,709.452 ↓ 0.0 0 1

Nested Loop (cost=4,069.97..8,514.82 rows=1 width=56) (actual time=1,709.452..1,709.452 rows=0 loops=1)

401. 44.135 1,425.407 ↓ 28,957.0 28,957 1

Nested Loop (cost=4,069.55..8,509.28 rows=1 width=32) (actual time=172.707..1,425.407 rows=28,957 loops=1)

402. 72.126 194.035 ↓ 28,957.0 28,957 1

Hash Right Join (cost=4,069.13..8,507.40 rows=1 width=32) (actual time=172.668..194.035 rows=28,957 loops=1)

  • Hash Cond: (rtc_4.parent_id = lse_34.loan_id)
  • Filter: (rtc_4.id IS NULL)
  • Rows Removed by Filter: 3,478
403. 55.248 55.248 ↑ 1.0 95,042 1

Seq Scan on rtc_note rtc_4 (cost=0.00..4,071.03 rows=95,042 width=8) (actual time=0.010..55.248 rows=95,042 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
404. 22.333 66.661 ↓ 7.4 32,433 1

Hash (cost=4,014.31..4,014.31 rows=4,385 width=32) (actual time=66.660..66.661 rows=32,433 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,284kB
405. 44.328 44.328 ↓ 7.4 32,433 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_34 (cost=0.29..4,014.31 rows=4,385 width=32) (actual time=0.016..44.328 rows=32,433 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 121
406. 1,187.237 1,187.237 ↑ 1.0 1 28,957

Index Only Scan using loan_setup_entity_loan_id on loan_setup_entity lset_3 (cost=0.42..1.88 rows=1 width=4) (actual time=0.041..0.041 rows=1 loops=28,957)

  • Index Cond: (loan_id = lse_34.loan_id)
  • Heap Fetches: 4,478
407. 260.613 260.613 ↓ 0.0 0 28,957

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lcf_2 (cost=0.42..5.54 rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=28,957)

  • Index Cond: ((loan_settings_id = lse_34.id) AND (loan_settings_id IS NOT NULL))
  • Filter: (rtc_notice_date <> ''::text)
  • Rows Removed by Filter: 1
408. 0.002 203.741 ↓ 0.0 0 1

Nested Loop (cost=4,069.84..9,064.76 rows=89 width=56) (actual time=203.741..203.741 rows=0 loops=1)

  • Join Filter: (lse_35.loan_id = lset_4.loan_id)
409. 4.479 203.739 ↓ 0.0 0 1

Nested Loop (cost=4,069.42..9,011.31 rows=89 width=32) (actual time=203.739..203.739 rows=0 loops=1)

  • Join Filter: (lse_35.loan_id = rtc2.parent_id)
410. 55.397 174.914 ↓ 3.2 3,478 1

Hash Join (cost=4,069.13..8,507.40 rows=1,084 width=36) (actual time=135.049..174.914 rows=3,478 loops=1)

  • Hash Cond: (rtc_5.parent_id = lse_35.loan_id)
411. 55.881 55.881 ↑ 1.0 95,042 1

Seq Scan on rtc_note rtc_5 (cost=0.00..4,071.03 rows=95,042 width=12) (actual time=0.009..55.881 rows=95,042 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
412. 20.404 63.636 ↓ 7.4 32,433 1

Hash (cost=4,014.31..4,014.31 rows=4,385 width=24) (actual time=63.635..63.636 rows=32,433 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,030kB
413. 43.232 43.232 ↓ 7.4 32,433 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_35 (cost=0.29..4,014.31 rows=4,385 width=24) (actual time=0.017..43.232 rows=32,433 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 121
414. 24.346 24.346 ↓ 0.0 0 3,478

Index Scan using rtc_note_parent_id on rtc_note rtc2 (cost=0.29..0.45 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=3,478)

  • Index Cond: (parent_id = rtc_5.parent_id)
  • Filter: (((parent_type)::text = 'Entity.Loan'::text) AND ((lastupdated)::date > (rtc_5.lastupdated)::date))
  • Rows Removed by Filter: 1
415. 0.000 0.000 ↓ 0.0 0

Index Only Scan using loan_setup_entity_loan_id on loan_setup_entity lset_4 (cost=0.42..0.59 rows=1 width=4) (never executed)

  • Index Cond: (loan_id = rtc_5.parent_id)
  • Heap Fetches: 0
416. 15.220 1,493.158 ↓ 2.0 2 1

Nested Loop (cost=4,069.98..11,071.09 rows=1 width=56) (actual time=929.873..1,493.158 rows=2 loops=1)

  • Join Filter: ((pe_8.lastupdated < rtc_6.lastupdated) AND ((rtc_6.lastupdated)::date = pe_8.apply_date))
  • Rows Removed by Join Filter: 21,006
417. 7.806 211.946 ↓ 3.2 3,478 1

Nested Loop (cost=4,069.55..9,158.45 rows=1,084 width=40) (actual time=138.173..211.946 rows=3,478 loops=1)

  • Join Filter: (lse_36.loan_id = lset_5.loan_id)
418. 59.285 183.272 ↓ 3.2 3,478 1

Hash Join (cost=4,069.13..8,507.40 rows=1,084 width=36) (actual time=138.147..183.272 rows=3,478 loops=1)

  • Hash Cond: (rtc_6.parent_id = lse_36.loan_id)
419. 57.771 57.771 ↑ 1.0 95,042 1

Seq Scan on rtc_note rtc_6 (cost=0.00..4,071.03 rows=95,042 width=12) (actual time=0.011..57.771 rows=95,042 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
420. 20.049 66.216 ↓ 7.4 32,433 1

Hash (cost=4,014.31..4,014.31 rows=4,385 width=24) (actual time=66.215..66.216 rows=32,433 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,030kB
421. 46.167 46.167 ↓ 7.4 32,433 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_36 (cost=0.29..4,014.31 rows=4,385 width=24) (actual time=0.017..46.167 rows=32,433 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 121
422. 20.868 20.868 ↑ 1.0 1 3,478

Index Only Scan using loan_setup_entity_loan_id on loan_setup_entity lset_5 (cost=0.42..0.59 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=3,478)

  • Index Cond: (loan_id = rtc_6.parent_id)
  • Heap Fetches: 6
423. 1,265.992 1,265.992 ↑ 1.7 6 3,478

Index Scan using payment_entity_entity_id on payment_entity pe_8 (cost=0.43..1.59 rows=10 width=16) (actual time=0.076..0.364 rows=6 loops=3,478)

  • Index Cond: (entity_id = lset_5.loan_id)
  • Filter: (deleted = 0)
  • Rows Removed by Filter: 0
424. 0.002 109.619 ↓ 0.0 0 1

Nested Loop (cost=4,071.38..8,597.83 rows=201 width=56) (actual time=109.619..109.619 rows=0 loops=1)

  • Join Filter: (lse_37.loan_id = lset_6.loan_id)
425. 0.003 109.617 ↓ 0.0 0 1

Hash Join (cost=4,070.96..8,477.10 rows=201 width=28) (actual time=109.617..109.617 rows=0 loops=1)

  • Hash Cond: (rtc_7.parent_id = lse_37.loan_id)
426. 55.062 109.614 ↓ 0.0 0 1

Hash Join (cost=1.83..4,340.04 rows=17,583 width=4) (actual time=109.614..109.614 rows=0 loops=1)

  • Hash Cond: ((rtc_7.created)::date = bank_holidays.holiday_date)
427. 54.496 54.496 ↑ 1.0 95,042 1

Seq Scan on rtc_note rtc_7 (cost=0.00..4,071.03 rows=95,042 width=12) (actual time=0.016..54.496 rows=95,042 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
428. 0.027 0.056 ↑ 1.0 37 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
429. 0.029 0.029 ↑ 1.0 37 1

Seq Scan on bank_holidays (cost=0.00..1.37 rows=37 width=4) (actual time=0.013..0.029 rows=37 loops=1)

430. 0.000 0.000 ↓ 0.0 0

Hash (cost=4,014.31..4,014.31 rows=4,385 width=24) (never executed)

431. 0.000 0.000 ↓ 0.0 0

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_37 (cost=0.29..4,014.31 rows=4,385 width=24) (never executed)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
432. 0.000 0.000 ↓ 0.0 0

Index Only Scan using loan_setup_entity_loan_id on loan_setup_entity lset_6 (cost=0.42..0.59 rows=1 width=4) (never executed)

  • Index Cond: (loan_id = rtc_7.parent_id)
  • Heap Fetches: 0
433. 0.079 16,581.548 ↓ 15.0 15 1

Result (cost=10,895.23..145,924.41 rows=1 width=70) (actual time=4,781.336..16,581.548 rows=15 loops=1)

  • One-Time Filter: (NOT (hashed SubPlan 2))
434. 0.112 16,581.443 ↓ 15.0 15 1

Nested Loop Anti Join (cost=10,895.23..145,924.41 rows=1 width=70) (actual time=4,781.272..16,581.443 rows=15 loops=1)

435. 0.084 16,581.016 ↓ 15.0 15 1

Nested Loop Anti Join (cost=10,893.34..145,915.01 rows=1 width=43) (actual time=4,781.233..16,581.016 rows=15 loops=1)

  • Join Filter: (note_entity.id < rtc_8.id)
436. 18.941 16,231.102 ↓ 15.0 15 1

Nested Loop (cost=10,892.90..145,849.58 rows=1 width=43) (actual time=4,755.797..16,231.102 rows=15 loops=1)

437. 20.045 7,375.463 ↓ 7,527.0 7,527 1

Nested Loop (cost=10,892.33..145,841.30 rows=1 width=47) (actual time=2,322.358..7,375.463 rows=7,527 loops=1)

438. 808.816 7,302.729 ↓ 7,527.0 7,527 1

Hash Join (cost=10,891.91..145,837.01 rows=1 width=43) (actual time=2,322.329..7,302.729 rows=7,527 loops=1)

  • Hash Cond: ((a_1.epic_key)::text = (lse_38.loan_id)::text)
439. 6,265.683 6,265.683 ↑ 1.0 934,774 1

Seq Scan on applicants a_1 (cost=0.00..129,102.74 rows=934,774 width=10) (actual time=0.047..6,265.683 rows=934,774 loops=1)

440. 20.152 228.230 ↓ 26,741.0 26,741 1

Hash (cost=10,891.90..10,891.90 rows=1 width=43) (actual time=228.230..228.230 rows=26,741 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,719kB
441. 70.571 208.078 ↓ 26,741.0 26,741 1

Hash Right Join (cost=6,459.04..10,891.90 rows=1 width=43) (actual time=193.054..208.078 rows=26,741 loops=1)

  • Hash Cond: (rtc_8.parent_id = lse_38.loan_id)
  • Filter: (rtc_8.id IS NULL)
  • Rows Removed by Filter: 3,457
442. 61.874 61.874 ↑ 1.0 95,042 1

Seq Scan on rtc_note rtc_8 (cost=0.00..4,071.03 rows=95,042 width=23) (actual time=0.011..61.874 rows=95,042 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
443. 19.834 75.633 ↓ 13.8 30,196 1

Hash (cost=6,431.64..6,431.64 rows=2,192 width=24) (actual time=75.633..75.633 rows=30,196 loops=1)

  • Buckets: 32,768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,908kB
444. 52.529 55.799 ↓ 13.8 30,196 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_38 (cost=87.26..6,431.64 rows=2,192 width=24) (actual time=4.935..55.799 rows=30,196 loops=1)

  • Filter: ((NOT (hashed SubPlan 1)) AND (loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 2,358
445.          

SubPlan (for Index Scan)

446. 3.270 3.270 ↓ 3.2 2,396 1

Index Scan using payment_entity_apply_date on payment_entity (cost=0.43..85.10 rows=746 width=4) (actual time=0.053..3.270 rows=2,396 loops=1)

  • Index Cond: (apply_date = CURRENT_DATE)
447. 52.689 52.689 ↑ 1.0 1 7,527

Index Only Scan using loan_setup_entity_loan_id on loan_setup_entity lset_7 (cost=0.42..4.29 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=7,527)

  • Index Cond: (loan_id = lse_38.loan_id)
  • Heap Fetches: 17
448. 8,836.698 8,836.698 ↓ 0.0 0 7,527

Index Scan using loan_status_archive_loan_id_date_rename on loan_status_archive lsa_6 (cost=0.57..8.28 rows=1 width=8) (actual time=1.174..1.174 rows=0 loops=7,527)

  • Index Cond: ((loan_id = lset_7.loan_id) AND (date = CURRENT_DATE))
  • Filter: (days_past_due >= 61)
  • Rows Removed by Filter: 1
449. 349.830 349.830 ↓ 0.0 0 15

Index Scan using note_entity_parent_id_parent_type on note_entity (cost=0.43..65.41 rows=1 width=8) (actual time=23.322..23.322 rows=0 loops=15)

  • Index Cond: (parent_id = lse_38.loan_id)
  • Filter: ((category_id = 85) AND (author_id = 619) AND (subject = 'RTC Email sent'::text))
  • Rows Removed by Filter: 23
450. 0.315 0.315 ↓ 0.0 0 15

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lcf_3 (cost=0.42..7.92 rows=1 width=7) (actual time=0.021..0.021 rows=0 loops=15)

  • Index Cond: (loan_settings_id = lse_38.id)
  • Filter: (rtc_notice_date <> ''::text)
  • Rows Removed by Filter: 1
451.          

SubPlan (for Result)

452. 0.026 0.026 ↑ 1.0 37 1

Seq Scan on bank_holidays bank_holidays_1 (cost=0.00..1.37 rows=37 width=4) (actual time=0.010..0.026 rows=37 loops=1)

453. 0.003 33,960.597 ↓ 0.0 0 1

Subquery Scan on *SELECT* 42 (cost=2.41..19,662.75 rows=25 width=151) (actual time=33,960.596..33,960.597 rows=0 loops=1)

454. 30.438 33,960.594 ↓ 0.0 0 1

Nested Loop Anti Join (cost=2.41..19,662.37 rows=25 width=146) (actual time=33,960.594..33,960.594 rows=0 loops=1)

455. 32.760 13,044.531 ↓ 629.5 17,625 1

Nested Loop Semi Join (cost=1.98..19,436.31 rows=28 width=110) (actual time=21.349..13,044.531 rows=17,625 loops=1)

456. 44.758 9,784.011 ↓ 67.9 17,932 1

Nested Loop (cost=1.56..18,715.45 rows=264 width=130) (actual time=7.413..9,784.011 rows=17,932 loops=1)

457. 53.909 8,916.725 ↓ 51.9 19,584 1

Nested Loop (cost=1.13..18,380.57 rows=377 width=126) (actual time=2.425..8,916.725 rows=19,584 loops=1)

458. 77.579 8,686.560 ↓ 51.9 19,584 1

Nested Loop (cost=0.71..17,919.30 rows=377 width=110) (actual time=0.730..8,686.560 rows=19,584 loops=1)

459. 72.547 72.547 ↓ 13.3 27,626 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity settings (cost=0.29..4,097.99 rows=2,073 width=8) (actual time=0.025..72.547 rows=27,626 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20) AND (autopay_enabled = 1))
  • Rows Removed by Filter: 4,928
460. 8,536.434 8,536.434 ↑ 1.0 1 27,626

Index Scan using loan_setup_entity_loan_id on loan_setup_entity setup (cost=0.42..6.67 rows=1 width=102) (actual time=0.309..0.309 rows=1 loops=27,626)

  • Index Cond: (loan_id = settings.loan_id)
  • Filter: (til_payment_schedule ~~ '%,{""count"":1%'::text)
  • Rows Removed by Filter: 0
461. 176.256 176.256 ↑ 1.0 1 19,584

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_39 (cost=0.42..1.21 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=19,584)

  • Index Cond: (loan_id = setup.loan_id)
462. 822.528 822.528 ↑ 1.0 1 19,584

Index Scan using loan__portfolio_loan_id on loan__portfolio port (cost=0.43..0.88 rows=1 width=4) (actual time=0.039..0.042 rows=1 loops=19,584)

  • Index Cond: (loan_id = lse_39.loan_id)
  • Filter: ((deleted = 0) AND (portfolio_id = ANY ('{10,17,49}'::integer[])))
  • Rows Removed by Filter: 6
463. 3,227.760 3,227.760 ↑ 1.0 1 17,932

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity lae_2 (cost=0.41..2.73 rows=1 width=4) (actual time=0.180..0.180 rows=1 loops=17,932)

  • Index Cond: (loan_id = lse_39.loan_id)
  • Filter: ((deleted = 0) AND (name = 'Autopay'::text) AND (type = 'autopay.type.recurring'::bpchar))
  • Rows Removed by Filter: 0
464. 20,885.625 20,885.625 ↑ 1.0 1 17,625

Index Scan using loan_autopay_entity_loan_id on loan_autopay_entity lae1 (cost=0.43..8.07 rows=1 width=4) (actual time=1.185..1.185 rows=1 loops=17,625)

  • Index Cond: (loan_id = setup.loan_id)
  • Filter: ((name = 'Autopay - Final Payment'::text) AND (deleted = 0))
  • Rows Removed by Filter: 4
465. 0.001 20,958.847 ↓ 0.0 0 1

Subquery Scan on *SELECT* 43 (cost=0.85..114,231.57 rows=1 width=116) (actual time=20,958.847..20,958.847 rows=0 loops=1)

466. 33.280 20,958.846 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.85..114,231.55 rows=1 width=108) (actual time=20,958.845..20,958.846 rows=0 loops=1)

  • Filter: (ne_1.id IS NULL)
  • Rows Removed by Filter: 18,677
467. 82.034 82.034 ↑ 1.0 18,677 1

Index Scan using payment_receipt_audit_created_at on payment_receipt_audit au (cost=0.43..3,069.02 rows=19,099 width=12) (actual time=6.630..82.034 rows=18,677 loops=1)

  • Index Cond: (created_at >= (CURRENT_DATE - 10))
  • Filter: ((loan_id <> 249271) AND (loan_id <> ALL ('{312432,301017,299218,242424,312946,275707,256470}'::integer[])))
468. 20,843.532 20,843.532 ↑ 1.0 1 18,677

Index Scan using note_entity_conditional_idx3 on note_entity ne_1 (cost=0.42..5.81 rows=1 width=232) (actual time=1.081..1.116 rows=1 loops=18,677)

  • Index Cond: (parent_id = au.loan_id)
  • Filter: (dw_reporting_meta.text_to_integer(split_part(split_part(body, 'for payment'::text, 2), 'with'::text, 1)) = au.payment_display_id)
  • Rows Removed by Filter: 6
469. 0.151 30.237 ↓ 0.0 0 1

Nested Loop (cost=0.71..11,782.34 rows=379 width=86) (actual time=30.237..30.237 rows=0 loops=1)

470. 27.545 27.545 ↑ 8.8 121 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_40 (cost=0.29..3,930.63 rows=1,066 width=24) (actual time=27.168..27.545 rows=121 loops=1)

  • Filter: (loan_sub_status_id = 14)
  • Rows Removed by Filter: 32,433
471. 2.541 2.541 ↓ 0.0 0 121

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_2 (cost=0.42..7.37 rows=1 width=6) (actual time=0.021..0.021 rows=0 loops=121)

  • Index Cond: (loan_settings_id = lse_40.id)
  • Filter: (esigned <> '1'::text)
  • Rows Removed by Filter: 1
Planning time : 741.553 ms
Execution time : 940,459.216 ms