explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JqXv

Settings
# exclusive inclusive rows x rows loops node
1. 0.599 360,453.532 ↑ 25,986.3 794 1

Append (cost=19,579.63..7,456,934.25 rows=20,633,154 width=116) (actual time=647.900..360,453.532 rows=794 loops=1)

2. 0.146 0.183 ↓ 0.0 0 1

Hash Join (cost=19,579.63..36,946.03 rows=68,513 width=92) (actual time=0.182..0.183 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.006..0.006 rows=1 loops=1)

4. 0.001 0.031 ↓ 0.0 0 1

Hash (cost=18,721.43..18,721.43 rows=68,656 width=12) (actual time=0.031..0.031 rows=0 loops=1)

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

Bitmap Heap Scan on loan_settings_custom_fields cf (cost=42.59..18,721.43 rows=68,656 width=12) (actual time=0.030..0.030 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.028 ↓ 0.0 0 1

BitmapOr (cost=42.59..42.59 rows=69,392 width=0) (actual time=0.028..0.028 rows=0 loops=1)

7. 0.020 0.020 ↓ 0.0 0 1

Bitmap Index Scan on loan_settings_custom_fields_loan_settings_id_cond (cost=0.00..4.13 rows=65,031 width=0) (actual time=0.020..0.020 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,361 width=0) (actual time=0.007..0.007 rows=0 loops=1)

9. 47.728 170.401 ↓ 0.0 0 1

Hash Join (cost=7,923.44..27,719.89 rows=19,220 width=91) (actual time=170.401..170.401 rows=0 loops=1)

  • Hash Cond: (cf_1.loan_settings_id = lse.id)
10. 59.970 67.451 ↑ 1.0 142,156 1

Bitmap Heap Scan on loan_settings_custom_fields cf_1 (cost=2,318.79..21,389.21 rows=142,354 width=11) (actual time=9.878..67.451 rows=142,156 loops=1)

  • Recheck Cond: ((funded_date IS NULL) OR (funded_date = ''::text))
  • Heap Blocks: exact=16,086
11. 7.481 7.481 ↑ 1.0 142,156 1

Bitmap Index Scan on loan_settings_custom_fields_loan_settings_id_indx1 (cost=0.00..2,283.20 rows=142,354 width=0) (actual time=7.481..7.481 rows=142,156 loops=1)

12. 19.258 55.222 ↓ 1.0 53,499 1

Hash (cost=4,954.27..4,954.27 rows=52,031 width=24) (actual time=55.221..55.222 rows=53,499 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,438kB
13. 35.964 35.964 ↓ 1.0 53,499 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.012..35.964 rows=53,499 loops=1)

14. 112.561 477.571 ↑ 86.7 3 1

Nested Loop (cost=0.84..14,675.34 rows=260 width=91) (actual time=477.313..477.571 rows=3 loops=1)

15. 92.372 92.372 ↓ 70.7 136,319 1

Index Scan using loan_settings_custom_fields_loan_settings_id_cond3 on loan_settings_custom_fields cf_2 (cost=0.42..2,167.37 rows=1,927 width=11) (actual time=0.730..92.372 rows=136,319 loops=1)

16. 272.638 272.638 ↓ 0.0 0 136,319

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.002..0.002 rows=0 loops=136,319)

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

Nested Loop (cost=1.09..15,664.51 rows=1,923 width=95) (actual time=1.178..1.179 rows=0 loops=1)

18. 0.005 0.642 ↑ 1,923.0 1 1

Nested Loop (cost=0.67..14,625.54 rows=1,923 width=27) (actual time=0.062..0.642 rows=1 loops=1)

19. 0.629 0.629 ↑ 1,927.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,117.57 rows=1,927 width=15) (actual time=0.051..0.629 rows=1 loops=1)

20. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

  • Index Cond: (id = cf_3.loan_settings_id)
  • Filter: (loan_id > 2)
21. 0.535 0.535 ↓ 0.0 0 1

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

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

Subquery Scan on *SELECT* 5 (cost=1,170.61..100,111.06 rows=523 width=92) (actual time=8,227.454..8,227.454 rows=0 loops=1)

23. 113.678 8,227.453 ↓ 0.0 0 1

Nested Loop (cost=1,170.61..100,103.22 rows=523 width=84) (actual time=8,227.453..8,227.453 rows=0 loops=1)

24. 72.982 211.006 ↓ 16.6 161,281 1

Nested Loop (cost=1,170.04..22,353.30 rows=9,729 width=21) (actual time=6.948..211.006 rows=161,281 loops=1)

25. 0.030 0.080 ↓ 4.0 4 1

Hash Join (cost=1.19..3.11 rows=1 width=21) (actual time=0.038..0.080 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.037 0.037 ↑ 1.0 4 1

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

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

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

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

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

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

Bitmap Heap Scan on loan__portfolio lp (cost=1,168.86..21,710.93 rows=63,926 width=8) (actual time=8.185..34.486 rows=40,320 loops=4)

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

Bitmap Index Scan on loan__portfolio_portfolio_id (cost=0.00..1,152.88 rows=63,926 width=0) (actual time=6.569..6.569 rows=40,320 loops=4)

  • Index Cond: (portfolio_id = pe.id)
31. 7,902.769 7,902.769 ↓ 0.0 0 161,281

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.049..0.049 rows=0 loops=161,281)

  • Index Cond: ((loan_id = lp.loan_id) AND (date = CURRENT_DATE))
32. 0.001 123.547 ↓ 0.0 0 1

Subquery Scan on *SELECT* 6 (cost=194.32..10,350.88 rows=15 width=116) (actual time=123.547..123.547 rows=0 loops=1)

33. 0.001 123.546 ↓ 0.0 0 1

Nested Loop (cost=194.32..10,350.69 rows=15 width=112) (actual time=123.546..123.546 rows=0 loops=1)

  • Join Filter: (lae.loan_id = lse_3.loan_id)
34. 0.001 123.545 ↓ 0.0 0 1

Hash Join (cost=193.90..10,338.87 rows=15 width=29) (actual time=123.545..123.545 rows=0 loops=1)

  • Hash Cond: (lae.primary_payment_method_id = cp.id)
35. 6.737 123.544 ↓ 0.0 0 1

Nested Loop (cost=2.42..10,144.95 rows=615 width=33) (actual time=123.544..123.544 rows=0 loops=1)

36. 24.480 80.942 ↓ 5.3 11,955 1

Nested Loop (cost=2.01..2,316.47 rows=2,246 width=25) (actual time=45.046..80.942 rows=11,955 loops=1)

  • Join Filter: (cle.id = cie.checklist_item_id)
  • Rows Removed by Join Filter: 83,620
37. 0.030 1.218 ↑ 1.0 4 1

Hash Join (cost=1.59..19.50 rows=4 width=29) (actual time=1.196..1.218 rows=4 loops=1)

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

Seq Scan on checklist_entity ce (cost=0.00..14.50 rows=450 width=4) (actual time=0.635..0.641 rows=19 loops=1)

39. 0.005 0.547 ↑ 1.0 4 1

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

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

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

  • Filter: (title = 'ACH Revoke'::text)
  • Rows Removed by Filter: 39
41. 55.244 55.244 ↓ 1.4 23,894 4

Index Scan using checklist_item__entity_checklist_id_cond1 on checklist_item__entity cie (cost=0.42..368.05 rows=16,495 width=16) (actual time=0.164..13.811 rows=23,894 loops=4)

  • Index Cond: (checklist_id = ce.id)
  • Filter: (entity_type = 'Entity.Loan'::bpchar)
42. 35.865 35.865 ↓ 0.0 0 11,955

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.003..0.003 rows=0 loops=11,955)

  • Index Cond: (loan_id = cie.entity_id)
  • Filter: (deleted = 0)
  • Rows Removed by Filter: 0
43. 0.000 0.000 ↓ 0.0 0

Hash (cost=141.21..141.21 rows=4,021 width=4) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Seq Scan on customer_payment_method_entity cp (cost=0.00..141.21 rows=4,021 width=4) (never executed)

45. 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)
46. 0.001 647.745 ↓ 0.0 0 1

Subquery Scan on x (cost=36,227.85..36,550.32 rows=7,166 width=116) (actual time=647.745..647.745 rows=0 loops=1)

47. 0.001 647.744 ↓ 0.0 0 1

Unique (cost=36,227.85..36,353.26 rows=7,166 width=82) (actual time=647.744..647.744 rows=0 loops=1)

48. 0.022 647.743 ↓ 0.0 0 1

Sort (cost=36,227.85..36,245.77 rows=7,166 width=82) (actual time=647.742..647.743 rows=0 loops=1)

  • Sort Key: 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)
  • Sort Method: quicksort Memory: 25kB
49. 20.814 647.721 ↓ 0.0 0 1

Hash Left Join (cost=21,021.85..35,768.98 rows=7,166 width=82) (actual time=647.720..647.721 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,517
50. 26.517 626.893 ↑ 1.1 13,517 1

Nested Loop (cost=20,997.96..35,564.97 rows=14,332 width=115) (actual time=279.056..626.893 rows=13,517 loops=1)

51. 21.041 353.646 ↓ 1.2 11,215 1

Nested Loop (cost=20,997.53..30,500.34 rows=9,345 width=115) (actual time=279.027..353.646 rows=11,215 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,824
52. 10.258 306.527 ↓ 1.4 13,039 1

Hash Join (cost=20,997.11..24,232.23 rows=9,392 width=45) (actual time=279.000..306.527 rows=13,039 loops=1)

  • Hash Cond: (lp_1.loan_id = ls_1.loan_id)
53. 8.802 17.991 ↓ 1.3 13,039 1

Hash Join (cost=4.44..3,108.91 rows=9,797 width=21) (actual time=0.142..17.991 rows=13,039 loops=1)

  • Hash Cond: (lp_1.portfolio_id = pe_1.id)
54. 9.103 9.103 ↓ 1.1 14,863 1

Index Scan using loan__portfolio_lastupdated on loan__portfolio lp_1 (cost=0.43..2,955.15 rows=13,996 width=8) (actual time=0.047..9.103 rows=14,863 loops=1)

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

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

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

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

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

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

58. 0.005 0.017 ↑ 1.0 7 1

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

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

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

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

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,171kB
61. 137.057 137.057 ↓ 1.0 385,366 1

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

62. 26.078 26.078 ↑ 1.0 1 13,039

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.002..0.002 rows=1 loops=13,039)

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

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.022..0.022 rows=1 loops=11,215)

  • Index Cond: (loan_id = ls_1.loan_id)
64. 0.002 0.014 ↑ 1.0 1 1

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

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

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

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

Subquery Scan on *SELECT* 8 (cost=412,842.77..535,100.36 rows=2,573,844 width=116) (actual time=8,481.153..8,481.153 rows=0 loops=1)

67. 1,953.994 8,481.151 ↓ 0.0 0 1

GroupAggregate (cost=412,842.77..502,927.31 rows=2,573,844 width=121) (actual time=8,481.151..8,481.151 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,684,913
68. 2,775.269 6,527.157 ↓ 1.0 2,684,913 1

Sort (cost=412,842.77..419,277.38 rows=2,573,844 width=29) (actual time=5,968.404..6,527.157 rows=2,684,913 loops=1)

  • Sort Key: lp_2.loan_id, pce_2.title, lse_4.loan_status_id
  • Sort Method: quicksort Memory: 323,024kB
69. 1,344.813 3,751.888 ↓ 1.0 2,684,913 1

Hash Join (cost=20,997.03..138,786.38 rows=2,573,844 width=29) (actual time=273.736..3,751.888 rows=2,684,913 loops=1)

  • Hash Cond: (lp_2.portfolio_id = pe_2.id)
70. 1,498.700 2,406.980 ↓ 1.0 2,684,913 1

Hash Join (cost=20,992.67..103,391.66 rows=2,573,844 width=16) (actual time=273.629..2,406.980 rows=2,684,913 loops=1)

  • Hash Cond: (lp_2.loan_id = lse_4.loan_id)
71. 635.221 635.221 ↑ 1.0 2,684,913 1

Seq Scan on loan__portfolio lp_2 (cost=0.00..46,592.13 rows=2,684,913 width=8) (actual time=0.004..635.221 rows=2,684,913 loops=1)

72. 136.548 273.059 ↓ 1.0 385,366 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 22,161kB
73. 136.511 136.511 ↓ 1.0 385,366 1

Seq Scan on loan_settings_entity lse_4 (cost=0.00..16,185.63 rows=384,563 width=16) (actual time=0.004..136.511 rows=385,366 loops=1)

74. 0.024 0.095 ↑ 1.0 59 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
75. 0.039 0.071 ↑ 1.0 59 1

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

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

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

77. 0.007 0.013 ↑ 1.0 10 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
78. 0.006 0.006 ↑ 1.0 10 1

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

79. 0.001 64.820 ↓ 0.0 0 1

Subquery Scan on *SELECT* 9 (cost=46.35..10,545.77 rows=112 width=116) (actual time=64.820..64.820 rows=0 loops=1)

80. 1.299 64.819 ↓ 0.0 0 1

Nested Loop (cost=46.35..10,544.37 rows=112 width=112) (actual time=64.819..64.819 rows=0 loops=1)

81. 2.478 59.724 ↓ 3.5 1,898 1

Nested Loop (cost=45.93..10,194.03 rows=542 width=26) (actual time=22.854..59.724 rows=1,898 loops=1)

82. 31.525 53.440 ↓ 3.4 1,903 1

Bitmap Heap Scan on loan_setup_entity ls_2 (cost=45.50..5,966.44 rows=556 width=10) (actual time=22.832..53.440 rows=1,903 loops=1)

  • Recheck Cond: (contract_date >= (CURRENT_DATE - '10 days'::interval))
  • Filter: (loan_rate > '25'::numeric)
  • Rows Removed by Filter: 1,313
  • Heap Blocks: exact=2,500
83. 21.915 21.915 ↓ 8.6 14,823 1

Bitmap Index Scan on loan_setup_entity_contract_date (cost=0.00..45.37 rows=1,725 width=0) (actual time=21.915..21.915 rows=14,823 loops=1)

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

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.002..0.002 rows=1 loops=1,903)

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

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cfe (cost=0.42..0.64 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1,898)

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

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

87. 1.107 15.716 ↓ 0.0 0 1

Nested Loop (cost=46.44..13,136.62 rows=415 width=112) (actual time=15.715..15.716 rows=0 loops=1)

88. 2.233 10.813 ↓ 2.1 1,898 1

Nested Loop (cost=46.02..12,557.02 rows=894 width=26) (actual time=1.179..10.813 rows=1,898 loops=1)

89. 3.894 4.774 ↓ 2.1 1,903 1

Bitmap Heap Scan on loan_setup_entity ls_3 (cost=45.59..5,966.53 rows=916 width=10) (actual time=1.169..4.774 rows=1,903 loops=1)

  • Recheck Cond: (contract_date >= (CURRENT_DATE - '10 days'::interval))
  • Filter: (loan_rate > 9.9)
  • Rows Removed by Filter: 1,313
  • Heap Blocks: exact=2,500
90. 0.880 0.880 ↓ 8.6 14,823 1

Bitmap Index Scan on loan_setup_entity_contract_date (cost=0.00..45.37 rows=1,725 width=0) (actual time=0.879..0.880 rows=14,823 loops=1)

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

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.002..0.002 rows=1 loops=1,903)

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

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cfe_1 (cost=0.42..0.64 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1,898)

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

Subquery Scan on *SELECT* 11 (cost=0.84..37,097.26 rows=1,922 width=106) (actual time=540.750..540.750 rows=0 loops=1)

94. 0.001 540.748 ↓ 0.0 0 1

Nested Loop (cost=0.84..37,073.23 rows=1,922 width=102) (actual time=540.748..540.748 rows=0 loops=1)

95. 0.001 540.747 ↓ 0.0 0 1

Nested Loop (cost=0.42..35,938.10 rows=1,922 width=26) (actual time=540.747..540.747 rows=0 loops=1)

96. 540.746 540.746 ↓ 0.0 0 1

Seq Scan on customer_entity ce_1 (cost=0.00..25,406.00 rows=1,358 width=26) (actual time=540.746..540.746 rows=0 loops=1)

  • Filter: (NULLIF(email, ''::text) IS NULL)
  • Rows Removed by Filter: 271,680
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.001 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.009 0.009 ↓ 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.009..0.009 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.001 0.051 ↓ 0.0 0 1

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

104. 0.000 0.050 ↓ 0.0 0 1

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

105. 0.050 0.050 ↓ 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=0.049..0.050 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.001 2,064.676 ↓ 0.0 0 1

Subquery Scan on cte (cost=129,525.26..129,549.74 rows=68 width=116) (actual time=2,064.676..2,064.676 rows=0 loops=1)

108. 1.053 2,064.675 ↓ 0.0 0 1

GroupAggregate (cost=129,525.26..129,546.85 rows=68 width=90) (actual time=2,064.675..2,064.675 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,108
109. 1.438 2,063.622 ↓ 16.4 1,116 1

Sort (cost=129,525.26..129,525.43 rows=68 width=142) (actual time=2,063.349..2,063.622 rows=1,116 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: 345kB
110. 8.699 2,062.184 ↓ 16.4 1,116 1

Nested Loop (cost=5,605.50..129,523.19 rows=68 width=142) (actual time=64.089..2,062.184 rows=1,116 loops=1)

111. 3.853 2,050.137 ↓ 16.4 1,116 1

Nested Loop (cost=5,605.08..129,375.71 rows=68 width=116) (actual time=64.016..2,050.137 rows=1,116 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,998
112. 10.483 1,943.090 ↓ 18.6 11,466 1

Hash Join (cost=5,604.65..127,365.83 rows=616 width=38) (actual time=56.206..1,943.090 rows=11,466 loops=1)

  • Hash Cond: (pe_3.entity_id = lse_10.loan_id)
113. 1,876.668 1,876.668 ↓ 2.6 11,830 1

Seq Scan on payment_entity pe_3 (cost=0.00..121,737.93 rows=4,555 width=14) (actual time=0.188..1,876.668 rows=11,830 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,694
114. 19.356 55.939 ↓ 1.0 53,499 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,438kB
115. 36.583 36.583 ↓ 1.0 53,499 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.012..36.583 rows=53,499 loops=1)

116. 103.194 103.194 ↑ 1.0 1 11,466

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=0.008..0.009 rows=1 loops=11,466)

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

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields cfe_2 (cost=0.42..1.90 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=1,116)

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

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

119. 0.001 289.213 ↓ 0.0 0 1

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

120. 204.050 289.212 ↓ 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=289.211..289.212 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,361
  • Heap Blocks: exact=3,894
121. 0.424 85.162 ↓ 0.0 0 1

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

122. 79.278 79.278 ↓ 1.8 20,875 1

Bitmap Index Scan on loan_autopay_entity_process_datetime (cost=0.00..280.56 rows=11,613 width=0) (actual time=79.278..79.278 rows=20,875 loops=1)

  • Index Cond: ((process_datetime >= CURRENT_DATE) AND (process_datetime <= (CURRENT_DATE + 6)))
123. 5.460 5.460 ↓ 1.0 47,170 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=5.460..5.460 rows=47,170 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.001 492.714 ↓ 0.0 0 1

Nested Loop (cost=1.28..8,545.35 rows=13 width=116) (actual time=492.714..492.714 rows=0 loops=1)

126. 1.908 492.713 ↓ 0.0 0 1

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

127. 2.499 393.480 ↓ 381.7 5,725 1

Nested Loop (cost=0.43..8,528.89 rows=15 width=8) (actual time=158.037..393.480 rows=5,725 loops=1)

128. 161.981 161.981 ↓ 381.7 5,725 1

Seq Scan on returnfilerecord rr (cost=0.00..8,402.17 rows=15 width=8) (actual time=157.301..161.981 rows=5,725 loops=1)

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

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

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

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

  • 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.069 71.536 ↓ 0.0 0 1

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

133. 4.206 71.254 ↓ 35.5 71 1

Nested Loop (cost=0.43..7,979.57 rows=2 width=8) (actual time=54.411..71.254 rows=71 loops=1)

134. 55.598 55.598 ↓ 381.7 5,725 1

Seq Scan on returnfilerecord rr_1 (cost=0.00..7,852.82 rows=15 width=8) (actual time=52.731..55.598 rows=5,725 loops=1)

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

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

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

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.003..0.003 rows=0 loops=71)

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

Hash Join (cost=29,944.38..125,140.65 rows=132,271 width=116) (actual time=68.379..68.379 rows=0 loops=1)

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

Hash Join (cost=8,951.71..101,337.23 rows=132,271 width=8) (actual time=68.377..68.377 rows=0 loops=1)

  • Hash Cond: (ltrim(fr_2.transaction_id, ' '::text) = ltrim(rr_2.transaction_id, ' '::text))
139. 0.008 0.008 ↑ 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.008..0.008 rows=1 loops=1)

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

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

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

Seq Scan on returnfilerecord rr_2 (cost=0.00..8,951.53 rows=15 width=14) (actual time=68.352..68.352 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,662
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.009 66,046.949 ↑ 2,151,827.0 8 1

Subquery Scan on *SELECT* 19 (cost=472,703.93..884,400.69 rows=17,214,616 width=116) (actual time=65,991.702..66,046.949 rows=8 loops=1)

145. 6.712 66,046.940 ↑ 2,151,827.0 8 1

Hash Join (cost=472,703.93..669,217.99 rows=17,214,616 width=112) (actual time=65,991.700..66,046.940 rows=8 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: 11,016
146. 13.319 474.357 ↑ 2.9 11,024 1

Merge Anti Join (cost=45,840.31..70,122.63 rows=31,782 width=24) (actual time=425.117..474.357 rows=11,024 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: 8,001
147. 8.426 428.031 ↑ 3.4 11,249 1

Sort (cost=45,839.90..45,935.24 rows=38,139 width=24) (actual time=424.856..428.031 rows=11,249 loops=1)

  • Sort Key: loan_autopay_entity_1.loan_id
  • Sort Method: quicksort Memory: 1,263kB
148. 128.438 419.605 ↑ 3.4 11,249 1

Hash Join (cost=25,742.60..42,937.71 rows=38,139 width=24) (actual time=261.546..419.605 rows=11,249 loops=1)

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

Seq Scan on loan_settings_entity lse_12 (cost=0.00..16,185.63 rows=384,563 width=16) (actual time=0.006..94.053 rows=385,366 loops=1)

150. 4.273 197.114 ↑ 3.4 11,249 1

Hash (cost=25,265.86..25,265.86 rows=38,139 width=8) (actual time=197.114..197.114 rows=11,249 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 952kB
151. 24.657 192.841 ↑ 3.4 11,249 1

GroupAggregate (cost=0.41..24,884.47 rows=38,139 width=8) (actual time=47.263..192.841 rows=11,249 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,226
152. 168.184 168.184 ↑ 1.6 27,522 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.017..168.184 rows=27,522 loops=1)

  • Filter: (name = 'Autopay'::text)
  • Rows Removed by Filter: 19,648
153. 33.007 33.007 ↓ 6.2 19,115 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.020..33.007 rows=19,115 loops=1)

  • Filter: (name <> 'Autopay'::text)
  • Rows Removed by Filter: 26,909
154. 105.805 65,565.871 ↓ 2.5 271,943 1

Hash (cost=425,502.72..425,502.72 rows=108,872 width=12) (actual time=65,565.871..65,565.871 rows=271,943 loops=1)

  • Buckets: 524,288 (originally 131072) Batches: 1 (originally 1) Memory Usage: 15,782kB
155. 114.632 65,460.066 ↓ 2.5 271,943 1

Subquery Scan on tx (cost=423,325.28..425,502.72 rows=108,872 width=12) (actual time=65,226.899..65,460.066 rows=271,943 loops=1)

156. 766.102 65,345.434 ↓ 2.5 271,943 1

HashAggregate (cost=423,325.28..424,414.00 rows=108,872 width=12) (actual time=65,226.897..65,345.434 rows=271,943 loops=1)

  • Group Key: loan_tx.entity_id
157. 64,579.332 64,579.332 ↑ 1.5 1,102,462 1

Index Scan using loan_tx_date1 on loan_tx (cost=0.44..414,826.09 rows=1,699,837 width=12) (actual time=89.386..64,579.332 rows=1,102,462 loops=1)

  • Index Cond: (date > CURRENT_DATE)
  • Filter: (type = 'scheduledPayment'::bpchar)
  • Rows Removed by Filter: 1,102,339
158. 0.005 3,127.303 ↑ 1,320.0 4 1

Subquery Scan on *SELECT* 20 (cost=139,579.41..184,186.14 rows=5,280 width=116) (actual time=3,120.739..3,127.303 rows=4 loops=1)

159. 0.021 3,127.298 ↑ 1,320.0 4 1

Nested Loop Anti Join (cost=139,579.41..184,120.14 rows=5,280 width=112) (actual time=3,120.737..3,127.298 rows=4 loops=1)

160. 677.269 3,127.233 ↑ 1,487.8 4 1

Hash Right Join (cost=139,578.98..181,265.16 rows=5,951 width=24) (actual time=3,120.694..3,127.233 rows=4 loops=1)

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

Seq Scan on payment_custom_fields pcf (cost=0.00..33,572.73 rows=1,601,673 width=18) (actual time=0.009..668.761 rows=1,577,494 loops=1)

162. 100.045 1,781.203 ↓ 1.5 245,787 1

Hash (cost=137,471.81..137,471.81 rows=168,574 width=24) (actual time=1,781.203..1,781.203 rows=245,787 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 15,490kB
163. 560.950 1,681.158 ↓ 1.5 245,787 1

Hash Join (cost=5,604.65..137,471.81 rows=168,574 width=24) (actual time=55.152..1,681.158 rows=245,787 loops=1)

  • Hash Cond: (p_1.entity_id = lse_13.loan_id)
164. 1,065.253 1,065.253 ↓ 1.0 1,304,362 1

Seq Scan on payment_entity p_1 (cost=0.00..125,509.15 rows=1,245,937 width=16) (actual time=0.075..1,065.253 rows=1,304,362 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,162
165. 17.820 54.955 ↓ 1.0 53,499 1

Hash (cost=4,954.27..4,954.27 rows=52,031 width=16) (actual time=54.955..54.955 rows=53,499 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,020kB
166. 37.135 37.135 ↓ 1.0 53,499 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=0.014..37.135 rows=53,499 loops=1)

167. 0.044 0.044 ↓ 0.0 0 4

Index Scan using achfilerecord_payment_id on achfilerecord (cost=0.43..0.81 rows=10 width=4) (actual time=0.011..0.011 rows=0 loops=4)

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

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

169. 56.274 4,645.091 ↓ 0.0 0 1

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

  • Hash Cond: (l.loan_id = lse_14.loan_id)
170. 56.054 4,371.306 ↑ 2.7 141,798 1

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

171. 261.952 4,315.252 ↑ 2.7 141,798 1

HashSetOp Except (cost=62,363.83..222,799.71 rows=381,408 width=12) (actual time=4,276.281..4,315.252 rows=141,798 loops=1)

172. 224.835 4,053.300 ↓ 1.0 627,360 1

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

173. 148.749 1,612.907 ↓ 1.0 384,579 1

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

174. 350.585 1,464.158 ↓ 1.0 384,579 1

HashAggregate (cost=62,363.83..66,177.91 rows=381,408 width=8) (actual time=1,310.941..1,464.158 rows=384,579 loops=1)

  • Group Key: ls_8.loan_id, ls_8.contract_date
175. 1,113.573 1,113.573 ↓ 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.023..1,113.573 rows=384,579 loops=1)

  • Filter: ((contract_date >= '2016-01-01'::date) AND (contract_date <= CURRENT_DATE))
  • Rows Removed by Filter: 787
176. 93.866 2,215.558 ↓ 1.0 242,781 1

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

177. 216.007 2,121.692 ↓ 1.0 242,781 1

HashAggregate (cost=144,901.27..147,301.04 rows=239,976 width=8) (actual time=2,028.889..2,121.692 rows=242,781 loops=1)

  • Group Key: ls_9.loan_id, ls_9.contract_date
178. 186.367 1,905.685 ↓ 1.0 242,781 1

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

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

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

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

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 19,119kB
181. 282.530 282.530 ↓ 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..282.530 rows=384,579 loops=1)

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

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 13,449kB
183. 130.326 130.326 ↓ 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.011..130.326 rows=243,201 loops=1)

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

Subquery Scan on *SELECT* 22 (cost=74,586.74..231,552.75 rows=59,845 width=116) (actual time=3,793.154..3,793.154 rows=0 loops=1)

185. 65.274 3,793.153 ↓ 0.0 0 1

Nested Loop (cost=74,586.74..230,804.69 rows=59,845 width=112) (actual time=3,793.153..3,793.153 rows=0 loops=1)

186. 61.788 3,531.043 ↓ 1.2 65,612 1

Hash Join (cost=74,586.31..182,160.15 rows=53,250 width=24) (actual time=1,378.861..3,531.043 rows=65,612 loops=1)

  • Hash Cond: (p_2.entity_id = lse_15.loan_id)
187. 951.372 3,193.041 ↓ 1.2 65,612 1

Hash Left Join (cost=53,593.64..160,435.29 rows=53,250 width=16) (actual time=1,100.827..3,193.041 rows=65,612 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. 1,149.908 1,149.908 ↓ 1.1 1,587,524 1

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

189. 637.022 1,091.761 ↑ 1.0 1,577,494 1

Hash (cost=33,572.73..33,572.73 rows=1,601,673 width=18) (actual time=1,091.760..1,091.761 rows=1,577,494 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 93,470kB
190. 454.739 454.739 ↑ 1.0 1,577,494 1

Seq Scan on payment_custom_fields pcf_1 (cost=0.00..33,572.73 rows=1,601,673 width=18) (actual time=0.009..454.739 rows=1,577,494 loops=1)

191. 138.718 276.214 ↓ 1.0 385,366 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 22,161kB
192. 137.496 137.496 ↓ 1.0 385,366 1

Seq Scan on loan_settings_entity lse_15 (cost=0.00..16,185.63 rows=384,563 width=16) (actual time=0.013..137.496 rows=385,366 loops=1)

193. 196.836 196.836 ↓ 0.0 0 65,612

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

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

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

  • Hash Cond: (ach_1.loan_id = ls_10.loan_id)
195. 507.053 507.053 ↑ 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.056..507.053 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. 51.823 552.971 ↑ 1.0 142,165 1

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,823kB
197. 231.313 501.148 ↑ 1.0 142,165 1

Hash Join (cost=19,900.01..80,347.94 rows=143,260 width=24) (actual time=152.313..501.148 rows=142,165 loops=1)

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

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

199. 47.975 151.436 ↑ 1.0 142,165 1

Hash (cost=18,108.44..18,108.44 rows=143,325 width=16) (actual time=151.435..151.436 rows=142,165 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 8,712kB
200. 103.461 103.461 ↑ 1.0 142,165 1

Seq Scan on loan_settings_entity lse_16 (cost=0.00..18,108.44 rows=143,325 width=16) (actual time=0.007..103.461 rows=142,165 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 8,199.043 ↓ 0.0 0 1

Subquery Scan on *SELECT* 24 (cost=95,472.70..102,254.55 rows=1 width=116) (actual time=8,199.042..8,199.043 rows=0 loops=1)

202. 0.001 8,199.041 ↓ 0.0 0 1

Nested Loop (cost=95,472.70..102,254.54 rows=1 width=110) (actual time=8,199.041..8,199.041 rows=0 loops=1)

203. 1,211.765 8,199.040 ↓ 0.0 0 1

Nested Loop (cost=95,472.28..102,253.76 rows=1 width=38) (actual time=8,199.040..8,199.040 rows=0 loops=1)

204. 767.607 2,349.725 ↓ 1,545,850.0 1,545,850 1

Hash Right Join (cost=95,471.85..102,253.09 rows=1 width=4) (actual time=1,701.667..2,349.725 rows=1,545,850 loops=1)

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

Seq Scan on returnfilerecord rr_3 (cost=0.00..6,204.76 rows=219,609 width=8) (actual time=0.010..77.463 rows=219,542 loops=1)

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

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 85,333kB
207. 826.623 826.623 ↓ 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.009..826.623 rows=1,765,088 loops=1)

  • Filter: (product = 'BALANCE_CREDIT'::text)
  • Rows Removed by Filter: 1,790
208. 4,637.550 4,637.550 ↓ 0.0 0 1,545,850

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

  • 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.002 827.335 ↓ 0.0 0 1

Subquery Scan on *SELECT* 25 (cost=8,950.73..35,235.18 rows=558 width=116) (actual time=827.335..827.335 rows=0 loops=1)

211. 2.691 827.333 ↓ 0.0 0 1

Hash Anti Join (cost=8,950.73..35,228.21 rows=558 width=112) (actual time=827.333..827.333 rows=0 loops=1)

  • Hash Cond: (fr_4.achfilerecord_id = rr_4.originalachrecord_id)
212. 3.752 670.815 ↓ 4.2 2,681 1

Nested Loop (cost=0.85..26,266.82 rows=637 width=20) (actual time=28.900..670.815 rows=2,681 loops=1)

  • Join Filter: (lse_18.loan_id = fr_4.loan_id)
213. 2.053 289.042 ↓ 2.6 2,681 1

Nested Loop (cost=0.42..24,934.74 rows=1,013 width=24) (actual time=28.874..289.042 rows=2,681 loops=1)

214. 56.241 56.241 ↓ 2.6 2,814 1

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

  • Filter: (loan_sub_status_id = 41)
  • Rows Removed by Filter: 382,552
215. 230.748 230.748 ↑ 1.0 1 2,814

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.082..0.082 rows=1 loops=2,814)

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

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

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

Hash (cost=6,204.76..6,204.76 rows=219,609 width=4) (actual time=153.827..153.827 rows=219,524 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,766kB
218. 82.036 82.036 ↑ 1.0 219,542 1

Seq Scan on returnfilerecord rr_4 (cost=0.00..6,204.76 rows=219,609 width=4) (actual time=0.008..82.036 rows=219,542 loops=1)

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

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

220. 0.479 256.578 ↓ 0.0 0 1

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

  • Hash Cond: (p_4.entity_id = lse_19.loan_id)
221. 0.011 0.011 ↑ 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=0.011..0.011 rows=1 loops=1)

222. 0.001 256.088 ↓ 0.0 0 1

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

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

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

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

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

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

Seq Scan on charge_entity (cost=0.00..6,806.53 rows=156,361 width=8) (actual time=0.004..149.541 rows=156,403 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 3,992.929 ↓ 0.0 0 1

Subquery Scan on *SELECT* 27 (cost=53,595.76..219,141.36 rows=1 width=116) (actual time=3,992.929..3,992.929 rows=0 loops=1)

229. 15.535 3,992.927 ↓ 0.0 0 1

Nested Loop (cost=53,595.76..219,141.34 rows=1 width=112) (actual time=3,992.927..3,992.927 rows=0 loops=1)

  • Join Filter: (lc.customer_id = en.customer_id)
  • Rows Removed by Join Filter: 76,208
230. 0.007 2,721.328 ↓ 2.0 2 1

Nested Loop (cost=53,595.76..181,765.66 rows=1 width=24) (actual time=2,611.616..2,721.328 rows=2 loops=1)

231. 0.010 2,720.229 ↓ 2.0 2 1

Nested Loop (cost=53,595.33..181,764.98 rows=1 width=20) (actual time=2,611.136..2,720.229 rows=2 loops=1)

232. 0.009 2,720.185 ↓ 2.0 2 1

Nested Loop (cost=53,594.91..181,764.47 rows=1 width=24) (actual time=2,611.115..2,720.185 rows=2 loops=1)

233. 0.009 2,716.268 ↓ 2.0 2 1

Nested Loop (cost=53,594.49..181,763.98 rows=1 width=16) (actual time=2,608.452..2,716.268 rows=2 loops=1)

234. 0.018 2,715.275 ↓ 2.0 2 1

Nested Loop (cost=53,594.07..181,763.53 rows=1 width=12) (actual time=2,608.427..2,715.275 rows=2 loops=1)

235. 590.266 2,711.437 ↓ 2.0 2 1

Hash Left Join (cost=53,593.64..181,763.04 rows=1 width=8) (actual time=2,606.549..2,711.437 rows=2 loops=1)

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

Seq Scan on payment_entity p_5 (cost=0.00..125,509.15 rows=1,013,425 width=12) (actual time=0.158..1,026.608 rows=1,225,852 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,672
237. 638.746 1,094.563 ↑ 1.0 1,577,494 1

Hash (cost=33,572.73..33,572.73 rows=1,601,673 width=18) (actual time=1,094.562..1,094.563 rows=1,577,494 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 93,470kB
238. 455.817 455.817 ↑ 1.0 1,577,494 1

Seq Scan on payment_custom_fields t5 (cost=0.00..33,572.73 rows=1,601,673 width=18) (actual time=0.009..455.817 rows=1,577,494 loops=1)

239. 3.820 3.820 ↑ 1.0 1 2

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

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

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=0.492..0.492 rows=1 loops=2)

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

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

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

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=0.015..0.017 rows=1 loops=2)

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

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

  • Index Cond: (id = lc.customer_id)
  • Heap Fetches: 2
244. 29.836 1,256.064 ↑ 5.2 38,104 2

Subquery Scan on en (cost=0.00..34,891.29 rows=198,751 width=4) (actual time=589.219..628.032 rows=38,104 loops=2)

245. 319.404 1,226.228 ↑ 5.2 38,104 2

HashSetOp Except (cost=0.00..32,903.78 rows=198,751 width=8) (actual time=589.218..613.114 rows=38,104 loops=2)

246. 225.839 906.824 ↓ 1.1 610,060 1

Append (cost=0.00..31,527.71 rows=550,427 width=8) (actual time=1.823..906.824 rows=610,060 loops=1)

247. 149.556 444.368 ↑ 1.0 376,728 1

Subquery Scan on *SELECT* 1_1 (cost=0.00..16,337.38 rows=376,728 width=8) (actual time=1.822..444.368 rows=376,728 loops=1)

248. 294.812 294.812 ↑ 1.0 376,728 1

Seq Scan on payment_account_entity (cost=0.00..12,570.10 rows=376,728 width=4) (actual time=1.821..294.812 rows=376,728 loops=1)

  • Filter: ((entity_type)::text = 'Entity.Customer'::text)
249. 93.146 236.617 ↓ 1.3 233,332 1

Subquery Scan on *SELECT* 2_1 (cost=0.42..15,190.33 rows=173,699 width=8) (actual time=1.688..236.617 rows=233,332 loops=1)

250. 143.471 143.471 ↓ 1.3 233,332 1

Index Scan using payment_account_entity_lastupdated on payment_account_entity payment_account_entity_1 (cost=0.42..13,453.34 rows=173,699 width=4) (actual time=1.686..143.471 rows=233,332 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 38,186
251. 0.002 679.906 ↓ 0.0 0 1

Hash Join (cost=43.78..38,055.51 rows=21,314 width=116) (actual time=679.905..679.906 rows=0 loops=1)

  • Hash Cond: (lse_21.loan_status_id = s.id)
252. 75.222 679.904 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.28..37,720.94 rows=21,314 width=16) (actual time=679.904..679.904 rows=0 loops=1)

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

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

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

Seq Scan on loan_settings_entity lse_21 (cost=0.00..17,147.04 rows=321,604 width=32) (actual time=0.010..150.000 rows=321,497 loops=1)

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

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

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

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

  • Filter: (title = ANY ('{""Full Term Paid"",""Early Payoff"",Refinanced}'::text[]))
  • Rows Removed by Filter: 53
257. 355.764 355.764 ↑ 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.004..0.004 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. 184.472 3,150.789 ↓ 0.0 0 1

Nested Loop (cost=21,954.49..118,898.48 rows=1 width=116) (actual time=3,150.788..3,150.789 rows=0 loops=1)

261. 213.390 2,078.419 ↓ 295,966.0 295,966 1

Hash Right Join (cost=21,954.06..118,897.85 rows=1 width=24) (actual time=1,942.176..2,078.419 rows=295,966 loops=1)

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

Seq Scan on loan_document_entity f (cost=0.00..94,315.22 rows=191,168 width=8) (actual time=19.686..1,560.976 rows=159,815 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,948,809
263. 151.753 304.053 ↓ 1.0 385,365 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,171kB
264. 152.300 152.300 ↓ 1.0 385,365 1

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

  • Filter: (loan_id <> 79895)
  • Rows Removed by Filter: 1
265. 887.898 887.898 ↓ 0.0 0 295,966

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.003..0.003 rows=0 loops=295,966)

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

Nested Loop Left Join (cost=20,993.09..133,272.70 rows=26,919 width=116) (actual time=775.112..775.112 rows=0 loops=1)

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

Hash Join (cost=20,992.67..110,517.53 rows=35,014 width=24) (actual time=289.493..694.926 rows=27,763 loops=1)

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

Seq Scan on loan_document_entity f_1 (cost=0.00..89,043.42 rows=35,014 width=4) (actual time=5.834..391.644 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,080,861
269. 144.441 281.873 ↓ 1.0 385,366 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,171kB
270. 137.432 137.432 ↓ 1.0 385,366 1

Seq Scan on loan_settings_entity lse_23 (cost=0.00..16,185.63 rows=384,563 width=24) (actual time=0.005..137.432 rows=385,366 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.035 502.661 ↓ 0.0 0 1

Hash Join (cost=23,265.75..42,723.89 rows=81,247 width=116) (actual time=502.661..502.661 rows=0 loops=1)

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

Hash Join (cost=23,224.25..41,565.24 rows=81,247 width=16) (actual time=501.956..501.956 rows=0 loops=1)

  • Hash Cond: (lse_24.loan_sub_status_id = ss_1.id)
274. 173.416 480.872 ↓ 1.0 89,318 1

Hash Join (cost=23,221.79..40,416.91 rows=88,911 width=24) (actual time=216.775..480.872 rows=89,318 loops=1)

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

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

276. 30.155 216.314 ↓ 1.0 89,318 1

Hash (cost=22,108.08..22,108.08 rows=89,097 width=4) (actual time=216.314..216.314 rows=89,318 loops=1)

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

Seq Scan on loan_settings_custom_fields cf_7 (cost=0.00..22,108.08 rows=89,097 width=4) (actual time=0.011..186.159 rows=89,318 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
279. 0.028 0.028 ↑ 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.008..0.028 rows=53 loops=1)

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

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 17kB
281. 0.659 0.659 ↑ 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.655..0.659 rows=9 loops=1)

282. 0.001 7,209.141 ↓ 0.0 0 1

Nested Loop Left Join (cost=21,168.36..21,253.76 rows=1 width=116) (actual time=7,209.141..7,209.141 rows=0 loops=1)

  • Filter: (ne.id IS NULL)
283. 187.004 7,209.140 ↓ 0.0 0 1

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

284. 464.776 2,851.125 ↓ 320,847.0 320,847 1

Nested Loop (cost=21,167.51..21,247.83 rows=1 width=24) (actual time=296.794..2,851.125 rows=320,847 loops=1)

285. 173.050 461.303 ↓ 320,841.0 320,841 1

Hash Right Join (cost=21,167.09..21,247.34 rows=1 width=24) (actual time=296.740..461.303 rows=320,841 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. 4.267 4.267 ↑ 1.0 656 1

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

  • Filter: (note_data ~~* '%Paid Off Notice%'::text)
287. 121.909 283.986 ↑ 1.0 321,497 1

Hash (cost=17,147.04..17,147.04 rows=321,604 width=24) (actual time=283.986..283.986 rows=321,497 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,678kB
288. 162.077 162.077 ↑ 1.0 321,497 1

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

  • Filter: (closed_date > '2016-10-01'::date)
  • Rows Removed by Filter: 63,869
289. 1,925.046 1,925.046 ↑ 1.0 1 320,841

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.006..0.006 rows=1 loops=320,841)

  • Index Cond: (loan_id = lse_25.loan_id)
  • Heap Fetches: 71,796
290. 4,171.011 4,171.011 ↓ 0.0 0 320,847

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.013..0.013 rows=0 loops=320,847)

  • 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.73 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. 15.342 83,428.296 ↓ 0.0 0 1

Nested Loop (cost=24,567.50..627,244.22 rows=3,840 width=68) (actual time=83,428.296..83,428.296 rows=0 loops=1)

  • Join Filter: (cfe1.custom_field_value <> cfe2.custom_field_value)
  • Rows Removed by Join Filter: 9,002
293. 30.247 79,245.028 ↓ 1.3 9,002 1

Nested Loop (cost=24,566.81..562,678.20 rows=7,121 width=40) (actual time=326.871..79,245.028 rows=9,002 loops=1)

294. 85.772 45,757.205 ↓ 1.8 23,512 1

Hash Join (cost=24,566.12..445,932.59 rows=12,883 width=28) (actual time=320.546..45,757.205 rows=23,512 loops=1)

  • Hash Cond: (cfe3.entity_id = lse_26.id)
295. 45,375.973 45,462.223 ↓ 4.1 83,888 1

Bitmap Heap Scan on custom_field__entity cfe3 (cost=4,408.60..425,569.09 rows=20,571 width=4) (actual time=107.439..45,462.223 rows=83,888 loops=1)

  • Recheck Cond: (custom_field_id = 74)
  • Filter: (((custom_field_value IS NULL) OR (custom_field_value = ''::text)) AND (entity_type = 'Entity.LoanSettings'::bpchar))
  • Rows Removed by Filter: 104,907
  • Heap Blocks: exact=110,353
296. 86.250 86.250 ↑ 1.0 204,507 1

Bitmap Index Scan on dw_reporting_lp_custom_field__entity_custom_field_id (cost=0.00..4,403.45 rows=214,269 width=0) (actual time=86.250..86.250 rows=204,507 loops=1)

  • Index Cond: (custom_field_id = 74)
297. 86.452 209.210 ↑ 1.0 240,387 1

Hash (cost=17,147.04..17,147.04 rows=240,839 width=24) (actual time=209.210..209.210 rows=240,387 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 15,195kB
298. 122.758 122.758 ↑ 1.0 240,387 1

Seq Scan on loan_settings_entity lse_26 (cost=0.00..17,147.04 rows=240,839 width=24) (actual time=0.013..122.758 rows=240,387 loops=1)

  • Filter: (loan_status_id = ANY ('{4,5}'::bigint[]))
  • Rows Removed by Filter: 144,979
299. 33,457.576 33,457.576 ↓ 0.0 0 23,512

Index Scan using custom_field__entity_entity_id_entity_type_custom_field_id_v2 on custom_field__entity cfe1 (cost=0.69..9.04 rows=2 width=12) (actual time=1.409..1.423 rows=0 loops=23,512)

  • Index Cond: ((entity_id = lse_26.id) AND (entity_type = 'Entity.LoanSettings'::bpchar) AND (custom_field_id = 73))
  • Filter: (custom_field_value <> 'SunUp Financial'::text)
  • Rows Removed by Filter: 1
300. 4,167.926 4,167.926 ↑ 2.0 1 9,002

Index Scan using custom_field__entity_entity_id_entity_type_custom_field_id_v2 on custom_field__entity cfe2 (cost=0.69..9.04 rows=2 width=12) (actual time=0.454..0.463 rows=1 loops=9,002)

  • Index Cond: ((entity_id = lse_26.id) AND (entity_type = 'Entity.LoanSettings'::bpchar) AND (custom_field_id = 1))
  • Filter: (custom_field_value <> 'SPV2'::text)
301. 0.001 16,128.138 ↓ 0.0 0 1

Subquery Scan on *SELECT* 34 (cost=23,660.04..1,152,113.47 rows=30 width=116) (actual time=16,128.138..16,128.138 rows=0 loops=1)

302. 81.868 16,128.137 ↓ 0.0 0 1

Hash Join (cost=23,660.04..1,152,112.87 rows=30 width=96) (actual time=16,128.137..16,128.137 rows=0 loops=1)

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

CTE max_date_qry

304. 0.004 1.563 ↑ 1.0 1 1

Result (cost=0.72..0.73 rows=1 width=4) (actual time=1.562..1.563 rows=1 loops=1)

305.          

Initplan (for Result)

306. 0.004 1.559 ↑ 1.0 1 1

Limit (cost=0.57..0.72 rows=1 width=4) (actual time=1.556..1.559 rows=1 loops=1)

307. 1.555 1.555 ↑ 204,117,840.0 1 1

Index Only Scan Backward using loan_status_archive_date_loan_id_rename on loan_status_archive (cost=0.57..29,262,490.53 rows=204,117,840 width=4) (actual time=1.555..1.555 rows=1 loops=1)

  • Index Cond: ((date IS NOT NULL) AND (date <= CURRENT_DATE))
  • Heap Fetches: 1
308. 116.820 16,007.135 ↓ 72.1 191,401 1

Nested Loop (cost=17,364.93..1,145,807.36 rows=2,654 width=26) (actual time=280.104..16,007.135 rows=191,401 loops=1)

309. 1.568 1.568 ↑ 1.0 1 1

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

310. 15,617.522 15,888.747 ↓ 72.1 191,401 1

Bitmap Heap Scan on loan_status_archive lsa_1 (cost=17,364.93..1,145,780.80 rows=2,654 width=30) (actual time=278.531..15,888.747 rows=191,401 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,733
  • Heap Blocks: exact=42,583
311. 0.009 271.225 ↓ 0.0 0 1

BitmapOr (cost=17,364.93..17,364.93 rows=322,461 width=0) (actual time=271.224..271.225 rows=0 loops=1)

312. 142.188 142.188 ↓ 2.4 382,079 1

Bitmap Index Scan on loan_status_archive_date_loan_id_rename (cost=0.00..8,681.80 rows=161,231 width=0) (actual time=142.188..142.188 rows=382,079 loops=1)

  • Index Cond: (date = max_date_qry.max_date)
313. 129.028 129.028 ↓ 2.4 382,055 1

Bitmap Index Scan on loan_status_archive_date_loan_id_rename (cost=0.00..8,681.80 rows=161,231 width=0) (actual time=129.028..129.028 rows=382,055 loops=1)

  • Index Cond: (date = (max_date_qry.max_date - 1))
314. 10.737 39.134 ↓ 7.5 32,958 1

Hash (cost=6,239.57..6,239.57 rows=4,385 width=16) (actual time=39.134..39.134 rows=32,958 loops=1)

  • Buckets: 65,536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,057kB
315. 28.397 28.397 ↓ 7.5 32,958 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_27 (cost=0.29..6,239.57 rows=4,385 width=16) (actual time=1.175..28.397 rows=32,958 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 42
316. 14,593.257 14,593.257 ↓ 4.0 4 1

CTE Scan on y (cost=637,499.04..637,499.07 rows=1 width=116) (actual time=14,592.841..14,593.257 rows=4 loops=1)

  • Filter: (business_days_since_last_payment > days_to_clear)
  • Rows Removed by Filter: 235
317.          

CTE payment_date

318. 10,806.592 13,178.110 ↓ 1.0 1,356,175 1

WindowAgg (cost=241,755.04..591,054.85 rows=1,293,703 width=24) (actual time=1,964.176..13,178.110 rows=1,356,175 loops=1)

319. 1,252.818 2,371.518 ↓ 1.0 1,356,175 1

Sort (cost=241,755.04..244,989.29 rows=1,293,703 width=12) (actual time=1,962.084..2,371.518 rows=1,356,175 loops=1)

  • Sort Key: payment_entity_3.entity_id, payment_entity_3.apply_date DESC
  • Sort Method: quicksort Memory: 112,723kB
320. 1,118.700 1,118.700 ↓ 1.0 1,356,175 1

Seq Scan on payment_entity payment_entity_3 (cost=0.00..110,424.29 rows=1,293,703 width=12) (actual time=0.010..1,118.700 rows=1,356,175 loops=1)

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

CTE y

322. 0.504 14,593.140 ↓ 239.0 239 1

GroupAggregate (cost=46,444.17..46,444.19 rows=1 width=28) (actual time=14,592.507..14,593.140 rows=239 loops=1)

  • Group Key: x_2.loan_id, x_2.days_to_clear, x_2.loan_status_id
323. 0.673 14,592.636 ↓ 733.0 733 1

Sort (cost=46,444.17..46,444.17 rows=1 width=24) (actual time=14,592.492..14,592.636 rows=733 loops=1)

  • Sort Key: x_2.loan_id, x_2.days_to_clear, x_2.loan_status_id
  • Sort Method: quicksort Memory: 82kB
324. 0.526 14,591.963 ↓ 733.0 733 1

Subquery Scan on x_2 (cost=17,161.21..46,444.16 rows=1 width=24) (actual time=7,896.908..14,591.963 rows=733 loops=1)

325. 10.700 14,591.437 ↓ 733.0 733 1

Nested Loop (cost=17,161.21..46,444.15 rows=1 width=32) (actual time=7,896.907..14,591.437 rows=733 loops=1)

  • Join Filter: (lse_41.loan_id = ls_13.loan_id)
326. 1.116 14,575.606 ↓ 733.0 733 1

Nested Loop (cost=17,160.79..46,439.61 rows=1 width=36) (actual time=7,896.858..14,575.606 rows=733 loops=1)

  • Join Filter: (lse_41.loan_id = lsa_7.loan_id)
327. 68.689 14,234.106 ↓ 19.6 352 1

Hash Join (cost=17,160.36..46,293.12 rows=18 width=28) (actual time=7,864.613..14,234.106 rows=352 loops=1)

  • Hash Cond: (pd.entity_id = lse_41.loan_id)
328. 14,115.199 14,115.199 ↓ 31.8 205,740 1

CTE Scan on payment_date pd (cost=0.00..29,108.32 rows=6,469 width=12) (actual time=1,964.179..14,115.199 rows=205,740 loops=1)

  • Filter: (row_number = 1)
  • Rows Removed by Filter: 1,150,435
329. 0.122 50.218 ↑ 3.0 352 1

Hash (cost=17,147.04..17,147.04 rows=1,066 width=16) (actual time=50.218..50.218 rows=352 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 33kB
330. 50.096 50.096 ↑ 3.0 352 1

Seq Scan on loan_settings_entity lse_41 (cost=0.00..17,147.04 rows=1,066 width=16) (actual time=40.285..50.096 rows=352 loops=1)

  • Filter: (loan_sub_status_id = 39)
  • Rows Removed by Filter: 385,014
331. 340.384 340.384 ↓ 2.0 2 352

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.520..0.967 rows=2 loops=352)

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

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.007..0.007 rows=1 loops=733)

  • Index Cond: (loan_id = lsa_7.loan_id)
  • Heap Fetches: 15
333. 0.005 531.186 ↑ 664.7 3 1

Subquery Scan on *SELECT* 36 (cost=0.87..60,793.07 rows=1,994 width=116) (actual time=506.061..531.186 rows=3 loops=1)

334. 8.228 531.181 ↑ 664.7 3 1

Nested Loop (cost=0.87..60,763.16 rows=1,994 width=108) (actual time=506.059..531.181 rows=3 loops=1)

335. 435.737 435.737 ↓ 7.2 21,804 1

Index Scan using custom_field__entity_last_updated on custom_field__entity cfe_3 (cost=0.44..38,006.39 rows=3,031 width=12) (actual time=12.249..435.737 rows=21,804 loops=1)

  • Index Cond: (lastupdated >= (now() - '15 days'::interval))
  • Filter: ((entity_type = 'Entity.Payment'::bpchar) AND (custom_field_id = 66))
  • Rows Removed by Filter: 249,596
336. 87.216 87.216 ↓ 0.0 0 21,804

Index Scan using payment_entity_id on payment_entity pe_4 (cost=0.43..7.49 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=21,804)

  • 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
337. 0.047 164.158 ↓ 0.0 0 1

Hash Join (cost=23,348.50..40,543.62 rows=22,115 width=69) (actual time=164.158..164.158 rows=0 loops=1)

  • Hash Cond: (lse_28.id = scf.loan_settings_id)
338. 0.011 0.011 ↑ 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.010..0.011 rows=1 loops=1)

339. 0.002 164.100 ↓ 0.0 0 1

Hash (cost=23,071.49..23,071.49 rows=22,161 width=21) (actual time=164.100..164.100 rows=0 loops=1)

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

Seq Scan on loan_settings_custom_fields scf (cost=0.00..23,071.49 rows=22,161 width=21) (actual time=164.098..164.098 rows=0 loops=1)

  • Filter: (((pledge_date IS NULL) OR (pledge_date = ''::text)) AND (owner = 'SPV2'::text))
  • Rows Removed by Filter: 385,366
341. 0.524 843.833 ↓ 639.0 639 1

Result (cost=124.17..6,412.64 rows=1 width=87) (actual time=17.628..843.833 rows=639 loops=1)

  • One-Time Filter: (NOT (hashed SubPlan 6))
342. 1.662 843.290 ↓ 639.0 639 1

Nested Loop (cost=124.17..6,412.64 rows=1 width=87) (actual time=17.583..843.290 rows=639 loops=1)

343. 1.481 301.370 ↓ 709.0 709 1

Nested Loop (cost=122.13..6,402.76 rows=1 width=27) (actual time=17.548..301.370 rows=709 loops=1)

344. 4.500 259.317 ↓ 2,898.0 2,898 1

Nested Loop (cost=121.70..6,394.66 rows=1 width=32) (actual time=17.325..259.317 rows=2,898 loops=1)

  • Join Filter: (lse_29.loan_id = lset.loan_id)
345. 25.655 179.577 ↓ 3,762.0 3,762 1

Nested Loop (cost=121.28..6,386.85 rows=1 width=28) (actual time=17.303..179.577 rows=3,762 loops=1)

346. 17.360 61.696 ↓ 30,742.0 30,742 1

Merge Left Join (cost=120.99..6,379.18 rows=1 width=24) (actual time=17.269..61.696 rows=30,742 loops=1)

  • Merge Cond: (lse_29.loan_id = pe_5.entity_id)
  • Filter: (pe_5.id IS NULL)
  • Rows Removed by Filter: 2,242
347. 26.338 26.338 ↓ 7.5 32,958 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_29 (cost=0.29..6,243.70 rows=4,385 width=24) (actual time=0.014..26.338 rows=32,958 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 42
348. 1.745 17.998 ↓ 3.2 2,352 1

Sort (cost=120.70..122.56 rows=746 width=8) (actual time=17.250..17.998 rows=2,352 loops=1)

  • Sort Key: pe_5.entity_id
  • Sort Method: quicksort Memory: 207kB
349. 16.253 16.253 ↓ 3.2 2,352 1

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

  • Index Cond: (apply_date = (now())::date)
350. 92.226 92.226 ↓ 0.0 0 30,742

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

  • Index Cond: (parent_id = lse_29.loan_id)
  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
351. 75.240 75.240 ↑ 1.0 1 3,762

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.020..0.020 rows=1 loops=3,762)

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

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lcf (cost=0.42..8.10 rows=1 width=7) (actual time=0.014..0.014 rows=0 loops=2,898)

  • 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
353. 540.258 540.258 ↑ 1.0 1 709

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=0.762..0.762 rows=1 loops=709)

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

SubPlan (for Result)

355. 0.019 0.019 ↑ 1.0 37 1

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

356. 0.007 9,964.566 ↑ 2,232.7 7 1

Result (cost=5,251.99..151,785.32 rows=15,629 width=87) (actual time=2,701.482..9,964.566 rows=7 loops=1)

357. 0.008 9,964.559 ↑ 2,232.7 7 1

Append (cost=5,251.99..151,629.03 rows=15,629 width=51) (actual time=2,701.480..9,964.559 rows=7 loops=1)

358. 83.971 9,357.165 ↑ 1,227.0 1 1

Hash Left Join (cost=5,251.99..84,080.89 rows=1,227 width=51) (actual time=2,701.479..9,357.165 rows=1 loops=1)

  • Hash Cond: (lse_30.loan_id = rtc_1.parent_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,229
359. 30.801 9,170.320 ↓ 7.5 9,210 1

Nested Loop Left Join (cost=2.80..78,726.09 rows=1,227 width=42) (actual time=4.852..9,170.320 rows=9,210 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: 58,886
360. 10.522 8,835.589 ↓ 7.5 9,210 1

Nested Loop Left Join (cost=2.38..62,349.90 rows=1,227 width=38) (actual time=3.626..8,835.589 rows=9,210 loops=1)

361. 81.002 8,677.707 ↓ 7.5 9,210 1

Nested Loop Left Join (cost=1.95..52,222.55 rows=1,227 width=39) (actual time=3.596..8,677.707 rows=9,210 loops=1)

  • Join Filter: (NOT (alternatives: SubPlan 3 or hashed SubPlan 4))
  • Rows Removed by Join Filter: 51
362. 14.645 145.984 ↓ 7.5 9,210 1

Nested Loop (cost=1.13..24,482.40 rows=1,227 width=31) (actual time=0.049..145.984 rows=9,210 loops=1)

363. 10.044 103.709 ↓ 7.5 9,210 1

Nested Loop (cost=0.71..14,674.08 rows=1,227 width=28) (actual time=0.039..103.709 rows=9,210 loops=1)

364. 38.405 38.405 ↓ 7.5 9,210 1

Index Scan using loan_settings_entity_id_cond on loan_settings_entity lse_30 (cost=0.29..9,393.76 rows=1,228 width=24) (actual time=0.024..38.405 rows=9,210 loops=1)

  • Filter: (loan_sub_status_id = 55)
  • Rows Removed by Filter: 44,289
365. 55.260 55.260 ↑ 1.0 1 9,210

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.006..0.006 rows=1 loops=9,210)

  • Index Cond: (loan_id = lse_30.loan_id)
  • Heap Fetches: 33
366. 27.630 27.630 ↑ 1.0 1 9,210

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_1 (cost=0.42..7.99 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=9,210)

  • Index Cond: (loan_settings_id = lse_30.id)
367. 8,445.570 8,445.570 ↓ 0.0 0 9,210

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=0.917..0.917 rows=0 loops=9,210)

  • 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
368.          

SubPlan (for Nested Loop Left Join)

369. 5.151 5.151 ↑ 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.101..0.101 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
370. 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))
371. 147.360 147.360 ↓ 0.0 0 9,210

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lcf_1 (cost=0.42..8.25 rows=1 width=7) (actual time=0.016..0.016 rows=0 loops=9,210)

  • 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
372. 303.930 303.930 ↑ 1.7 6 9,210

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

  • Index Cond: (entity_id = lse_30.loan_id)
373. 29.720 102.859 ↑ 1.0 94,829 1

Hash (cost=4,062.36..4,062.36 rows=94,829 width=8) (actual time=102.859..102.859 rows=94,829 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,729kB
374. 73.139 73.139 ↑ 1.0 94,829 1

Seq Scan on rtc_note rtc_1 (cost=0.00..4,062.36 rows=94,829 width=8) (actual time=0.644..73.139 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
375.          

SubPlan (for Hash Left Join)

376. 0.015 0.015 ↑ 1.0 37 1

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

377. 23.797 242.450 ↑ 1,233.0 6 1

Hash Join (cost=4,326.06..26,838.04 rows=7,398 width=51) (actual time=187.475..242.450 rows=6 loops=1)

  • Hash Cond: (cf_9.loan_settings_id = lse_31.id)
378. 185.901 185.901 ↓ 1.0 88,191 1

Seq Scan on loan_settings_custom_fields cf_9 (cost=0.00..22,108.08 rows=87,979 width=7) (actual time=0.006..185.901 rows=88,191 loops=1)

  • Filter: (called_due_notice_date <> ''::text)
  • Rows Removed by Filter: 297,175
379. 11.683 32.752 ↓ 1.0 32,958 1

Hash (cost=3,920.99..3,920.99 rows=32,406 width=24) (actual time=32.751..32.752 rows=32,958 loops=1)

  • Buckets: 65,536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2,315kB
380. 21.069 21.069 ↓ 1.0 32,958 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_31 (cost=0.29..3,920.99 rows=32,406 width=24) (actual time=0.009..21.069 rows=32,958 loops=1)

  • Filter: (loan_sub_status_id = 20)
  • Rows Removed by Filter: 42
381. 74.588 364.936 ↓ 0.0 0 1

Hash Join (cost=17,260.49..40,553.80 rows=7,004 width=51) (actual time=364.936..364.936 rows=0 loops=1)

  • Hash Cond: (cf_10.loan_settings_id = lse_32.id)
382. 234.050 234.050 ↑ 1.0 297,175 1

Seq Scan on loan_settings_custom_fields cf_10 (cost=0.00..22,108.08 rows=297,387 width=7) (actual time=0.008..234.050 rows=297,175 loops=1)

  • Filter: (called_due_notice_date = ''::text)
  • Rows Removed by Filter: 88,191
383. 2.983 56.298 ↓ 1.0 9,210 1

Hash (cost=17,147.04..17,147.04 rows=9,076 width=24) (actual time=56.298..56.298 rows=9,210 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 632kB
384. 53.315 53.315 ↓ 1.0 9,210 1

Seq Scan on loan_settings_entity lse_32 (cost=0.00..17,147.04 rows=9,076 width=24) (actual time=1.349..53.315 rows=9,210 loops=1)

  • Filter: (loan_sub_status_id = 55)
  • Rows Removed by Filter: 376,156
385. 0.005 73,542.665 ↑ 4,104.2 4 1

Subquery Scan on x_1 (cost=0.57..866,087.59 rows=16,417 width=116) (actual time=72,881.938..73,542.665 rows=4 loops=1)

386. 0.007 73,542.660 ↑ 4,104.2 4 1

Append (cost=0.57..865,923.42 rows=16,417 width=56) (actual time=72,881.936..73,542.660 rows=4 loops=1)

387. 0.001 72,775.531 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_2 (cost=0.57..806,857.17 rows=16,125 width=56) (actual time=72,775.530..72,775.531 rows=0 loops=1)

388. 90.992 72,775.530 ↓ 0.0 0 1

Nested Loop (cost=0.57..806,574.98 rows=16,125 width=44) (actual time=72,775.529..72,775.530 rows=0 loops=1)

389. 45.524 45.524 ↑ 1.0 94,829 1

Seq Scan on rtc_note rtc_2 (cost=0.00..3,825.29 rows=94,829 width=12) (actual time=0.014..45.524 rows=94,829 loops=1)

390. 72,639.014 72,639.014 ↓ 0.0 0 94,829

Index Scan using loan_status_archive_loan_id_date_rename on loan_status_archive lsa_4 (cost=0.57..8.46 rows=1 width=28) (actual time=0.766..0.766 rows=0 loops=94,829)

  • 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
391. 0.004 119.982 ↑ 1.0 1 1

Nested Loop (cost=6,299.57..21,900.01 rows=1 width=56) (actual time=106.403..119.982 rows=1 loops=1)

392. 0.013 119.966 ↑ 1.0 1 1

Nested Loop Left Join (cost=6,299.15..21,895.73 rows=1 width=36) (actual time=106.388..119.966 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
393. 3.784 119.842 ↑ 63.7 3 1

Nested Loop (cost=6,298.72..19,875.14 rows=191 width=44) (actual time=77.102..119.842 rows=3 loops=1)

  • Join Filter: (lse_33.loan_id = lsa_5.loan_id)
394. 28.954 97.148 ↓ 3.5 3,782 1

Hash Join (cost=6,298.15..10,726.93 rows=1,081 width=36) (actual time=36.121..97.148 rows=3,782 loops=1)

  • Hash Cond: (rtc_3.parent_id = lse_33.loan_id)
395. 32.806 32.806 ↑ 1.0 94,829 1

Seq Scan on rtc_note rtc_3 (cost=0.00..4,062.36 rows=94,829 width=12) (actual time=0.007..32.806 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
396. 11.462 35.388 ↓ 7.5 32,958 1

Hash (cost=6,243.33..6,243.33 rows=4,385 width=24) (actual time=35.388..35.388 rows=32,958 loops=1)

  • Buckets: 65,536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,315kB
397. 23.926 23.926 ↓ 7.5 32,958 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_33 (cost=0.29..6,243.33 rows=4,385 width=24) (actual time=0.012..23.926 rows=32,958 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 42
398. 18.910 18.910 ↓ 0.0 0 3,782

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.005..0.005 rows=0 loops=3,782)

  • Index Cond: ((loan_id = rtc_3.parent_id) AND (date = (rtc_3.lastupdated)::date))
  • Filter: (days_past_due < 60)
  • Rows Removed by Filter: 1
399. 0.111 0.111 ↓ 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.015..0.037 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
400. 0.012 0.012 ↑ 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.012..0.012 rows=1 loops=1)

  • Index Cond: (loan_id = lse_33.loan_id)
  • Heap Fetches: 0
401. 8.454 320.283 ↓ 0.0 0 1

Nested Loop (cost=4,060.33..8,495.68 rows=1 width=56) (actual time=320.282..320.283 rows=0 loops=1)

402. 34.798 224.295 ↓ 29,178.0 29,178 1

Nested Loop (cost=4,059.90..8,490.14 rows=1 width=32) (actual time=91.843..224.295 rows=29,178 loops=1)

403. 35.440 101.963 ↓ 29,178.0 29,178 1

Hash Right Join (cost=4,059.48..8,488.26 rows=1 width=32) (actual time=91.828..101.963 rows=29,178 loops=1)

  • Hash Cond: (rtc_4.parent_id = lse_34.loan_id)
  • Filter: (rtc_4.id IS NULL)
  • Rows Removed by Filter: 3,782
404. 30.560 30.560 ↑ 1.0 94,829 1

Seq Scan on rtc_note rtc_4 (cost=0.00..4,062.36 rows=94,829 width=8) (actual time=0.007..30.560 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
405. 11.817 35.963 ↓ 7.5 32,958 1

Hash (cost=4,004.67..4,004.67 rows=4,385 width=32) (actual time=35.963..35.963 rows=32,958 loops=1)

  • Buckets: 65,536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,572kB
406. 24.146 24.146 ↓ 7.5 32,958 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_34 (cost=0.29..4,004.67 rows=4,385 width=32) (actual time=0.010..24.146 rows=32,958 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 42
407. 87.534 87.534 ↑ 1.0 1 29,178

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.003..0.003 rows=1 loops=29,178)

  • Index Cond: (loan_id = lse_34.loan_id)
  • Heap Fetches: 4,315
408. 87.534 87.534 ↓ 0.0 0 29,178

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lcf_2 (cost=0.42..5.53 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=29,178)

  • 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
409. 0.001 100.163 ↓ 0.0 0 1

Nested Loop (cost=4,060.20..9,044.30 rows=89 width=56) (actual time=100.163..100.163 rows=0 loops=1)

  • Join Filter: (lse_35.loan_id = lset_4.loan_id)
410. 0.533 100.162 ↓ 0.0 0 1

Nested Loop (cost=4,059.77..8,990.83 rows=89 width=32) (actual time=100.162..100.162 rows=0 loops=1)

  • Join Filter: (lse_35.loan_id = rtc2.parent_id)
411. 26.516 92.065 ↓ 3.5 3,782 1

Hash Join (cost=4,059.48..8,488.26 rows=1,081 width=36) (actual time=35.827..92.065 rows=3,782 loops=1)

  • Hash Cond: (rtc_5.parent_id = lse_35.loan_id)
412. 30.469 30.469 ↑ 1.0 94,829 1

Seq Scan on rtc_note rtc_5 (cost=0.00..4,062.36 rows=94,829 width=12) (actual time=0.006..30.469 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
413. 11.012 35.080 ↓ 7.5 32,958 1

Hash (cost=4,004.67..4,004.67 rows=4,385 width=24) (actual time=35.080..35.080 rows=32,958 loops=1)

  • Buckets: 65,536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,315kB
414. 24.068 24.068 ↓ 7.5 32,958 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_35 (cost=0.29..4,004.67 rows=4,385 width=24) (actual time=0.010..24.068 rows=32,958 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 42
415. 7.564 7.564 ↓ 0.0 0 3,782

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

  • 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
416. 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
417. 7.561 166.721 ↓ 3.0 3 1

Nested Loop (cost=4,060.33..11,045.12 rows=1 width=56) (actual time=136.387..166.721 rows=3 loops=1)

  • Join Filter: ((pe_8.lastupdated < rtc_6.lastupdated) AND ((rtc_6.lastupdated)::date = pe_8.apply_date))
  • Rows Removed by Join Filter: 22,707
418. 2.648 98.648 ↓ 3.5 3,782 1

Nested Loop (cost=4,059.90..9,137.78 rows=1,081 width=40) (actual time=33.344..98.648 rows=3,782 loops=1)

  • Join Filter: (lse_36.loan_id = lset_5.loan_id)
419. 26.451 88.436 ↓ 3.5 3,782 1

Hash Join (cost=4,059.48..8,488.26 rows=1,081 width=36) (actual time=33.331..88.436 rows=3,782 loops=1)

  • Hash Cond: (rtc_6.parent_id = lse_36.loan_id)
420. 29.368 29.368 ↑ 1.0 94,829 1

Seq Scan on rtc_note rtc_6 (cost=0.00..4,062.36 rows=94,829 width=12) (actual time=0.004..29.368 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
421. 11.061 32.617 ↓ 7.5 32,958 1

Hash (cost=4,004.67..4,004.67 rows=4,385 width=24) (actual time=32.617..32.617 rows=32,958 loops=1)

  • Buckets: 65,536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,315kB
422. 21.556 21.556 ↓ 7.5 32,958 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_36 (cost=0.29..4,004.67 rows=4,385 width=24) (actual time=0.006..21.556 rows=32,958 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 42
423. 7.564 7.564 ↑ 1.0 1 3,782

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.002..0.002 rows=1 loops=3,782)

  • Index Cond: (loan_id = rtc_6.parent_id)
  • Heap Fetches: 5
424. 60.512 60.512 ↑ 1.7 6 3,782

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

  • Index Cond: (entity_id = lset_5.loan_id)
  • Filter: (deleted = 0)
  • Rows Removed by Filter: 0
425. 0.001 59.973 ↓ 0.0 0 1

Nested Loop (cost=4,061.74..8,578.22 rows=200 width=56) (actual time=59.972..59.973 rows=0 loops=1)

  • Join Filter: (lse_37.loan_id = lset_6.loan_id)
426. 0.001 59.972 ↓ 0.0 0 1

Hash Join (cost=4,061.31..8,458.05 rows=200 width=28) (actual time=59.972..59.972 rows=0 loops=1)

  • Hash Cond: (rtc_7.parent_id = lse_37.loan_id)
427. 30.475 59.971 ↓ 0.0 0 1

Hash Join (cost=1.83..4,330.78 rows=17,543 width=4) (actual time=59.971..59.971 rows=0 loops=1)

  • Hash Cond: ((rtc_7.created)::date = bank_holidays.holiday_date)
428. 29.466 29.466 ↑ 1.0 94,829 1

Seq Scan on rtc_note rtc_7 (cost=0.00..4,062.36 rows=94,829 width=12) (actual time=0.006..29.466 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
429. 0.017 0.030 ↑ 1.0 37 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
430. 0.013 0.013 ↑ 1.0 37 1

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

431. 0.000 0.000 ↓ 0.0 0

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

432. 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,004.67 rows=4,385 width=24) (never executed)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
433. 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
434. 0.140 7,725.650 ↓ 122.0 122 1

Result (cost=10,876.11..145,905.29 rows=1 width=70) (actual time=1,284.487..7,725.650 rows=122 loops=1)

  • One-Time Filter: (NOT (hashed SubPlan 2))
435. 0.317 7,725.497 ↓ 122.0 122 1

Nested Loop Anti Join (cost=10,876.11..145,905.29 rows=1 width=70) (actual time=1,284.454..7,725.497 rows=122 loops=1)

436. 0.236 7,724.082 ↓ 122.0 122 1

Nested Loop Anti Join (cost=10,874.22..145,895.89 rows=1 width=43) (actual time=1,284.433..7,724.082 rows=122 loops=1)

  • Join Filter: (note_entity.id < rtc_8.id)
437. 7.222 6,071.356 ↓ 122.0 122 1

Nested Loop (cost=10,873.79..145,830.47 rows=1 width=43) (actual time=1,258.907..6,071.356 rows=122 loops=1)

438. 13.587 3,081.414 ↓ 7,648.0 7,648 1

Nested Loop (cost=10,873.22..145,822.18 rows=1 width=47) (actual time=864.547..3,081.414 rows=7,648 loops=1)

439. 428.083 3,044.883 ↓ 7,648.0 7,648 1

Hash Join (cost=10,872.79..145,817.89 rows=1 width=43) (actual time=864.514..3,044.883 rows=7,648 loops=1)

  • Hash Cond: ((a_1.epic_key)::text = (lse_38.loan_id)::text)
440. 2,502.625 2,502.625 ↑ 1.0 934,774 1

Seq Scan on applicants a_1 (cost=0.00..129,102.74 rows=934,774 width=10) (actual time=1.133..2,502.625 rows=934,774 loops=1)

441. 11.221 114.175 ↓ 26,982.0 26,982 1

Hash (cost=10,872.78..10,872.78 rows=1 width=43) (actual time=114.175..114.175 rows=26,982 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,732kB
442. 33.930 102.954 ↓ 26,982.0 26,982 1

Hash Right Join (cost=6,449.40..10,872.78 rows=1 width=43) (actual time=95.108..102.954 rows=26,982 loops=1)

  • Hash Cond: (rtc_8.parent_id = lse_38.loan_id)
  • Filter: (rtc_8.id IS NULL)
  • Rows Removed by Filter: 3,762
443. 29.059 29.059 ↑ 1.0 94,829 1

Seq Scan on rtc_note rtc_8 (cost=0.00..4,062.36 rows=94,829 width=23) (actual time=0.006..29.059 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
444. 10.212 39.965 ↓ 14.0 30,742 1

Hash (cost=6,422.00..6,422.00 rows=2,192 width=24) (actual time=39.965..39.965 rows=30,742 loops=1)

  • Buckets: 32,768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,938kB
445. 28.093 29.753 ↓ 14.0 30,742 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_38 (cost=87.26..6,422.00 rows=2,192 width=24) (actual time=2.600..29.753 rows=30,742 loops=1)

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

SubPlan (for Index Scan)

447. 1.660 1.660 ↓ 3.2 2,352 1

Index Scan using payment_entity_apply_date on payment_entity (cost=0.43..85.10 rows=746 width=4) (actual time=0.019..1.660 rows=2,352 loops=1)

  • Index Cond: (apply_date = CURRENT_DATE)
448. 22.944 22.944 ↑ 1.0 1 7,648

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.003..0.003 rows=1 loops=7,648)

  • Index Cond: (loan_id = lse_38.loan_id)
  • Heap Fetches: 19
449. 2,982.720 2,982.720 ↓ 0.0 0 7,648

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=0.390..0.390 rows=0 loops=7,648)

  • Index Cond: ((loan_id = lset_7.loan_id) AND (date = CURRENT_DATE))
  • Filter: (days_past_due >= 61)
  • Rows Removed by Filter: 1
450. 1,652.490 1,652.490 ↓ 0.0 0 122

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

  • 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: 29
451. 1.098 1.098 ↓ 0.0 0 122

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.009..0.009 rows=0 loops=122)

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

SubPlan (for Result)

453. 0.013 0.013 ↑ 1.0 37 1

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

454. 0.002 9,412.787 ↓ 0.0 0 1

Subquery Scan on *SELECT* 42 (cost=2.41..19,647.60 rows=25 width=151) (actual time=9,412.787..9,412.787 rows=0 loops=1)

455. 18.747 9,412.785 ↓ 0.0 0 1

Nested Loop Anti Join (cost=2.41..19,647.22 rows=25 width=146) (actual time=9,412.785..9,412.785 rows=0 loops=1)

456. 19.554 1,970.615 ↓ 632.8 17,717 1

Nested Loop Semi Join (cost=1.98..19,421.17 rows=28 width=110) (actual time=12.927..1,970.615 rows=17,717 loops=1)

457. 20.216 1,175.857 ↓ 68.8 18,028 1

Nested Loop (cost=1.56..18,705.78 rows=262 width=130) (actual time=6.435..1,175.857 rows=18,028 loops=1)

458. 20.950 801.221 ↓ 52.2 19,690 1

Nested Loop (cost=1.13..18,370.93 rows=377 width=126) (actual time=0.613..801.221 rows=19,690 loops=1)

459. 47.551 701.511 ↓ 52.2 19,690 1

Nested Loop (cost=0.71..17,909.65 rows=377 width=110) (actual time=0.078..701.511 rows=19,690 loops=1)

460. 40.644 40.644 ↓ 13.4 27,878 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity settings (cost=0.29..4,088.35 rows=2,073 width=8) (actual time=0.025..40.644 rows=27,878 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20) AND (autopay_enabled = 1))
  • Rows Removed by Filter: 5,122
461. 613.316 613.316 ↑ 1.0 1 27,878

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.022..0.022 rows=1 loops=27,878)

  • Index Cond: (loan_id = settings.loan_id)
  • Filter: (til_payment_schedule ~~ '%,{""count"":1%'::text)
  • Rows Removed by Filter: 0
462. 78.760 78.760 ↑ 1.0 1 19,690

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

  • Index Cond: (loan_id = setup.loan_id)
463. 354.420 354.420 ↑ 1.0 1 19,690

Index Scan using loan__portfolio_loan_id on loan__portfolio port (cost=0.43..0.88 rows=1 width=4) (actual time=0.015..0.018 rows=1 loops=19,690)

  • Index Cond: (loan_id = lse_39.loan_id)
  • Filter: ((deleted = 0) AND (portfolio_id = ANY ('{10,17,49}'::integer[])))
  • Rows Removed by Filter: 6
464. 775.204 775.204 ↑ 1.0 1 18,028

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.043..0.043 rows=1 loops=18,028)

  • 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
465. 7,423.423 7,423.423 ↑ 1.0 1 17,717

Index Scan using loan_autopay_entity_loan_id on loan_autopay_entity lae1 (cost=0.43..8.07 rows=1 width=4) (actual time=0.419..0.419 rows=1 loops=17,717)

  • Index Cond: (loan_id = setup.loan_id)
  • Filter: ((name = 'Autopay - Final Payment'::text) AND (deleted = 0))
  • Rows Removed by Filter: 4
466. 0.001 18,016.498 ↓ 0.0 0 1

Subquery Scan on *SELECT* 43 (cost=0.85..138,590.36 rows=1 width=116) (actual time=18,016.498..18,016.498 rows=0 loops=1)

467. 29.344 18,016.497 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.85..138,590.35 rows=1 width=108) (actual time=18,016.497..18,016.497 rows=0 loops=1)

  • Filter: (ne_1.id IS NULL)
  • Rows Removed by Filter: 18,677
468. 94.587 94.587 ↓ 1.1 18,677 1

Index Scan using payment_receipt_audit_created_at on payment_receipt_audit au (cost=0.43..2,792.43 rows=17,248 width=12) (actual time=6.591..94.587 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[])))
469. 17,892.566 17,892.566 ↑ 1.0 1 18,677

Index Scan using note_entity_conditional_idx3 on note_entity ne_1 (cost=0.42..7.86 rows=1 width=232) (actual time=0.933..0.958 rows=1 loops=18,677)

  • Index Cond: (parent_id = au.loan_id)
  • Filter: ((author_id = 619) AND (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
470. 0.033 16.666 ↓ 0.0 0 1

Nested Loop (cost=0.71..11,772.69 rows=384 width=86) (actual time=16.666..16.666 rows=0 loops=1)

471. 16.549 16.549 ↑ 25.4 42 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_40 (cost=0.29..3,920.99 rows=1,066 width=24) (actual time=16.437..16.549 rows=42 loops=1)

  • Filter: (loan_sub_status_id = 14)
  • Rows Removed by Filter: 32,958
472. 0.084 0.084 ↓ 0.0 0 42

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.002..0.002 rows=0 loops=42)

  • Index Cond: (loan_settings_id = lse_40.id)
  • Filter: (esigned <> '1'::text)
  • Rows Removed by Filter: 1
Planning time : 113.041 ms
Execution time : 360,484.865 ms