explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4gFi

Settings
# exclusive inclusive rows x rows loops node
1. 0.545 444,047.210 ↑ 25,986.4 794 1

Append (cost=19,608.65..7,458,470.67 rows=20,633,176 width=116) (actual time=673.420..444,047.210 rows=794 loops=1)

2. 0.171 0.190 ↓ 0.0 0 1

Hash Join (cost=19,608.65..36,975.05 rows=68,513 width=92) (actual time=0.190..0.190 rows=0 loops=1)

  • Hash Cond: (ls.id = cf.loan_settings_id)
3. 0.007 0.007 ↑ 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.007..0.007 rows=1 loops=1)

4. 0.001 0.012 ↓ 0.0 0 1

Hash (cost=18,739.53..18,739.53 rows=69,530 width=12) (actual time=0.012..0.012 rows=0 loops=1)

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

Bitmap Heap Scan on loan_settings_custom_fields cf (cost=43.02..18,739.53 rows=69,530 width=12) (actual time=0.011..0.011 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.009 ↓ 0.0 0 1

BitmapOr (cost=43.02..43.02 rows=70,275 width=0) (actual time=0.009..0.009 rows=0 loops=1)

7. 0.006 0.006 ↓ 0.0 0 1

Bitmap Index Scan on loan_settings_custom_fields_loan_settings_id_cond (cost=0.00..4.13 rows=65,859 width=0) (actual time=0.006..0.006 rows=0 loops=1)

8. 0.002 0.002 ↓ 0.0 0 1

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

9. 51.386 272.329 ↓ 0.0 0 1

Hash Join (cost=5,604.65..28,506.89 rows=19,220 width=91) (actual time=272.328..272.329 rows=0 loops=1)

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

Seq Scan on loan_settings_custom_fields cf_1 (cost=0.00..22,169.41 rows=144,167 width=11) (actual time=0.007..165.251 rows=142,156 loops=1)

  • Filter: ((funded_date IS NULL) OR (funded_date = ''::text))
  • Rows Removed by Filter: 243,210
11. 19.324 55.692 ↓ 1.0 53,499 1

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

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

13. 49.507 401.152 ↑ 86.7 3 1

Nested Loop (cost=0.84..14,829.89 rows=260 width=91) (actual time=400.898..401.152 rows=3 loops=1)

14. 79.007 79.007 ↓ 69.9 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,191.07 rows=1,951 width=11) (actual time=0.015..79.007 rows=136,319 loops=1)

15. 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.47 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
16. 0.001 0.032 ↓ 0.0 0 1

Nested Loop (cost=1.09..15,818.41 rows=1,922 width=95) (actual time=0.032..0.032 rows=0 loops=1)

17. 0.005 0.020 ↑ 1,922.0 1 1

Nested Loop (cost=0.67..14,779.97 rows=1,922 width=27) (actual time=0.018..0.020 rows=1 loops=1)

18. 0.008 0.008 ↑ 1,951.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,141.15 rows=1,951 width=15) (actual time=0.007..0.008 rows=1 loops=1)

19. 0.007 0.007 ↑ 1.0 1 1

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

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

  • Index Cond: (id = lse_2.loan_id)
  • Filter: (deleted = 0)
  • Rows Removed by Filter: 1
21. 0.002 669.304 ↓ 0.0 0 1

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

22. 160.780 669.302 ↓ 0.0 0 1

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

23. 71.624 185.960 ↓ 16.6 161,281 1

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

24. 0.033 0.084 ↓ 4.0 4 1

Hash Join (cost=1.19..3.11 rows=1 width=21) (actual time=0.040..0.084 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[]))))
25. 0.039 0.039 ↑ 1.0 4 1

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.008 0.008 ↓ 2.0 2 1

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

  • Filter: ((title = ANY ('{Servicer,Owner}'::text[])) AND ((title = 'Servicer'::text) OR (title = 'Owner'::text)))
  • Rows Removed by Filter: 8
28. 102.952 114.252 ↑ 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=4.377..28.563 rows=40,320 loops=4)

  • Recheck Cond: (portfolio_id = pe.id)
  • Heap Blocks: exact=43,340
29. 11.300 11.300 ↑ 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=2.825..2.825 rows=40,320 loops=4)

  • Index Cond: (portfolio_id = pe.id)
30. 322.562 322.562 ↓ 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.002..0.002 rows=0 loops=161,281)

  • Index Cond: ((loan_id = lp.loan_id) AND (date = CURRENT_DATE))
31. 0.002 98.901 ↓ 0.0 0 1

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

32. 0.001 98.899 ↓ 0.0 0 1

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

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

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

  • Hash Cond: (lae.primary_payment_method_id = cp.id)
34. 9.238 98.897 ↓ 0.0 0 1

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

35. 24.583 77.704 ↓ 5.3 11,955 1

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

  • Join Filter: (cle.id = cie.checklist_item_id)
  • Rows Removed by Join Filter: 83,620
36. 0.027 0.053 ↑ 1.0 4 1

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

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

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

38. 0.005 0.014 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 0.009 0.009 ↑ 1.0 4 1

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

  • Filter: (title = 'ACH Revoke'::text)
  • Rows Removed by Filter: 39
40. 53.068 53.068 ↓ 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.012..13.267 rows=23,894 loops=4)

  • Index Cond: (checklist_id = ce.id)
  • Filter: (entity_type = 'Entity.Loan'::bpchar)
41. 11.955 11.955 ↓ 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.001..0.001 rows=0 loops=11,955)

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

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

43. 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)

44. 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)
45. 0.002 398.307 ↓ 0.0 0 1

Subquery Scan on x (cost=36,271.81..36,595.27 rows=7,188 width=116) (actual time=398.306..398.307 rows=0 loops=1)

46. 0.001 398.305 ↓ 0.0 0 1

Unique (cost=36,271.81..36,397.60 rows=7,188 width=82) (actual time=398.305..398.305 rows=0 loops=1)

47. 0.020 398.304 ↓ 0.0 0 1

Sort (cost=36,271.81..36,289.78 rows=7,188 width=82) (actual time=398.304..398.304 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
48. 17.508 398.284 ↓ 0.0 0 1

Hash Left Join (cost=21,021.85..35,811.36 rows=7,188 width=82) (actual time=398.284..398.284 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,590
49. 14.220 380.765 ↑ 1.1 13,590 1

Nested Loop (cost=20,997.96..35,606.81 rows=14,375 width=115) (actual time=279.873..380.765 rows=13,590 loops=1)

50. 14.287 344.009 ↓ 1.2 11,268 1

Nested Loop (cost=20,997.53..30,527.00 rows=9,373 width=115) (actual time=279.861..344.009 rows=11,268 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,834
51. 9.372 303.518 ↓ 1.4 13,102 1

Hash Join (cost=20,997.11..24,240.21 rows=9,420 width=45) (actual time=279.837..303.518 rows=13,102 loops=1)

  • Hash Cond: (lp_1.loan_id = ls_1.loan_id)
52. 7.887 15.029 ↓ 1.3 13,102 1

Hash Join (cost=4.44..3,116.49 rows=9,827 width=21) (actual time=0.139..15.029 rows=13,102 loops=1)

  • Hash Cond: (lp_1.portfolio_id = pe_1.id)
53. 7.056 7.056 ↓ 1.1 14,936 1

Index Scan using loan__portfolio_lastupdated on loan__portfolio lp_1 (cost=0.43..2,962.29 rows=14,038 width=8) (actual time=0.043..7.056 rows=14,936 loops=1)

  • Index Cond: (lastupdated >= (now() - '7 days'::interval))
54. 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
55. 0.033 0.068 ↓ 1.0 43 1

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

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

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

57. 0.006 0.016 ↑ 1.0 7 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
58. 0.010 0.010 ↑ 1.0 7 1

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

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

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,171kB
60. 136.944 136.944 ↓ 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..136.944 rows=385,366 loops=1)

61. 26.204 26.204 ↑ 1.0 1 13,102

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,102)

  • Index Cond: (loan_settings_id = ls_1.id)
62. 22.536 22.536 ↑ 2.0 1 11,268

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

  • Index Cond: (loan_id = ls_1.loan_id)
63. 0.003 0.011 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
64. 0.008 0.008 ↑ 1.0 1 1

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

  • Filter: (id = 12)
  • Rows Removed by Filter: 17
65. 0.002 8,489.193 ↓ 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,489.193..8,489.193 rows=0 loops=1)

66. 1,962.588 8,489.191 ↓ 0.0 0 1

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

Sort (cost=412,842.77..419,277.38 rows=2,573,844 width=29) (actual time=5,968.050..6,526.603 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
68. 1,364.197 3,754.874 ↓ 1.0 2,684,913 1

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

  • Hash Cond: (lp_2.portfolio_id = pe_2.id)
69. 1,480.084 2,390.580 ↓ 1.0 2,684,913 1

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

  • Hash Cond: (lp_2.loan_id = lse_4.loan_id)
70. 634.324 634.324 ↑ 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.005..634.324 rows=2,684,913 loops=1)

71. 138.775 276.172 ↓ 1.0 385,366 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 22,161kB
72. 137.397 137.397 ↓ 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.005..137.397 rows=385,366 loops=1)

73. 0.024 0.097 ↑ 1.0 59 1

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

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

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

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

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

76. 0.006 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
77. 0.007 0.007 ↑ 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.007 rows=10 loops=1)

78. 0.001 15.619 ↓ 0.0 0 1

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

79. 1.117 15.618 ↓ 0.0 0 1

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

80. 2.221 10.705 ↓ 3.5 1,898 1

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

81. 3.789 4.678 ↓ 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=1.183..4.678 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
82. 0.889 0.889 ↓ 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.888..0.889 rows=14,823 loops=1)

  • Index Cond: (contract_date >= (CURRENT_DATE - '10 days'::interval))
83. 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
84. 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
85. 0.001 15.561 ↓ 0.0 0 1

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

86. 1.085 15.560 ↓ 0.0 0 1

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

87. 2.230 10.679 ↓ 2.1 1,898 1

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

88. 3.782 4.643 ↓ 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.149..4.643 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
89. 0.861 0.861 ↓ 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.861..0.861 rows=14,823 loops=1)

  • Index Cond: (contract_date >= (CURRENT_DATE - '10 days'::interval))
90. 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
91. 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
92. 0.001 80.838 ↓ 0.0 0 1

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

93. 0.001 80.837 ↓ 0.0 0 1

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

94. 0.001 80.836 ↓ 0.0 0 1

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

95. 80.835 80.835 ↓ 0.0 0 1

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

  • Filter: (NULLIF(email, ''::text) IS NULL)
  • Rows Removed by Filter: 271,680
96. 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)
97. 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)
98. 0.001 0.008 ↓ 0.0 0 1

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

99. 0.001 0.007 ↓ 0.0 0 1

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

100. 0.006 0.006 ↓ 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.006..0.006 rows=0 loops=1)

101. 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)
102. 0.000 0.021 ↓ 0.0 0 1

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

103. 0.001 0.021 ↓ 0.0 0 1

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

104. 0.020 0.020 ↓ 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.020..0.020 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
105. 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)
106. 0.002 955.941 ↓ 0.0 0 1

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

107. 1.059 955.939 ↓ 0.0 0 1

GroupAggregate (cost=129,525.26..129,546.85 rows=68 width=90) (actual time=955.939..955.939 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
108. 1.327 954.880 ↓ 16.4 1,116 1

Sort (cost=129,525.26..129,525.43 rows=68 width=142) (actual time=954.614..954.880 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
109. 8.290 953.553 ↓ 16.4 1,116 1

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

110. 12.303 941.915 ↓ 16.4 1,116 1

Nested Loop (cost=5,605.08..129,375.71 rows=68 width=116) (actual time=63.646..941.915 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
111. 9.966 895.214 ↓ 18.6 11,466 1

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

  • Hash Cond: (pe_3.entity_id = lse_10.loan_id)
112. 829.804 829.804 ↓ 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.222..829.804 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
113. 19.196 55.444 ↓ 1.0 53,499 1

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

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

115. 34.398 34.398 ↑ 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.003..0.003 rows=1 loops=11,466)

  • Index Cond: (loan_id = lse_10.loan_id)
  • Filter: (first_payment_date < CURRENT_DATE)
  • Rows Removed by Filter: 0
116. 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)
117. 0.001 189.877 ↓ 0.0 0 1

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

118. 0.002 189.876 ↓ 0.0 0 1

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

119. 182.111 189.874 ↓ 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=189.874..189.874 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
120. 0.412 7.763 ↓ 0.0 0 1

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

121. 2.020 2.020 ↓ 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=2.020..2.020 rows=20,875 loops=1)

  • Index Cond: ((process_datetime >= CURRENT_DATE) AND (process_datetime <= (CURRENT_DATE + 6)))
122. 5.331 5.331 ↓ 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.331..5.331 rows=47,170 loops=1)

123. 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)
124. 0.001 95.470 ↓ 0.0 0 1

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

125. 4.325 95.469 ↓ 0.0 0 1

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

126. 6.377 79.694 ↓ 381.7 5,725 1

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

127. 61.867 61.867 ↓ 381.7 5,725 1

Seq Scan on returnfilerecord rr (cost=0.00..8,402.17 rows=15 width=8) (actual time=58.696..61.867 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
128. 11.450 11.450 ↑ 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.002..0.002 rows=1 loops=5,725)

  • Index Cond: (achfilerecord_id = rr.originalachrecord_id)
  • Filter: (product = 'BALANCE_CREDIT'::text)
129. 11.450 11.450 ↓ 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.002..0.002 rows=0 loops=5,725)

  • Index Cond: (id = fr.payment_id)
  • Filter: (reverse_reason IS NULL)
  • Rows Removed by Filter: 1
130. 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)
131. 0.061 70.886 ↓ 0.0 0 1

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

132. 4.047 70.612 ↓ 35.5 71 1

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

133. 55.115 55.115 ↓ 381.7 5,725 1

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

  • Filter: ((product = 'BALANCE_CREDIT'::text) AND (returndate > (CURRENT_DATE - 30)))
  • Rows Removed by Filter: 213,937
134. 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
135. 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
136. 0.001 67.873 ↓ 0.0 0 1

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

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
140. 67.849 67.849 ↓ 0.0 0 1

Seq Scan on returnfilerecord rr_2 (cost=0.00..8,951.53 rows=15 width=14) (actual time=67.849..67.849 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
141. 0.000 0.000 ↓ 0.0 0

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

142. 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)

143. 0.007 7,228.823 ↑ 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=7,174.653..7,228.823 rows=8 loops=1)

144. 6.008 7,228.816 ↑ 2,151,827.0 8 1

Hash Join (cost=472,703.93..669,217.99 rows=17,214,616 width=112) (actual time=7,174.652..7,228.816 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
145. 13.433 335.600 ↑ 2.9 11,024 1

Merge Anti Join (cost=45,840.31..70,122.63 rows=31,782 width=24) (actual time=286.729..335.600 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
146. 8.236 289.552 ↑ 3.4 11,249 1

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

  • Sort Key: loan_autopay_entity_1.loan_id
  • Sort Method: quicksort Memory: 1,263kB
147. 125.875 281.316 ↑ 3.4 11,249 1

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

  • Hash Cond: (lse_12.loan_id = loan_autopay_entity_1.loan_id)
148. 90.338 90.338 ↓ 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.004..90.338 rows=385,366 loops=1)

149. 4.094 65.103 ↑ 3.4 11,249 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 952kB
150. 24.561 61.009 ↑ 3.4 11,249 1

GroupAggregate (cost=0.41..24,884.47 rows=38,139 width=8) (actual time=0.654..61.009 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
151. 36.448 36.448 ↑ 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.016..36.448 rows=27,522 loops=1)

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

  • Filter: (name <> 'Autopay'::text)
  • Rows Removed by Filter: 26,909
153. 97.761 6,887.208 ↓ 2.5 271,943 1

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

  • Buckets: 524,288 (originally 131072) Batches: 1 (originally 1) Memory Usage: 15,782kB
154. 106.995 6,789.447 ↓ 2.5 271,943 1

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

155. 665.970 6,682.452 ↓ 2.5 271,943 1

HashAggregate (cost=423,325.28..424,414.00 rows=108,872 width=12) (actual time=6,573.347..6,682.452 rows=271,943 loops=1)

  • Group Key: loan_tx.entity_id
156. 6,016.482 6,016.482 ↑ 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=0.216..6,016.482 rows=1,102,462 loops=1)

  • Index Cond: (date > CURRENT_DATE)
  • Filter: (type = 'scheduledPayment'::bpchar)
  • Rows Removed by Filter: 1,102,339
157. 0.004 3,133.738 ↑ 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,127.333..3,133.738 rows=4 loops=1)

158. 0.019 3,133.734 ↑ 1,320.0 4 1

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

159. 646.129 3,133.683 ↑ 1,487.8 4 1

Hash Right Join (cost=139,578.98..181,265.16 rows=5,951 width=24) (actual time=3,127.305..3,133.683 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
160. 726.697 726.697 ↑ 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.010..726.697 rows=1,577,494 loops=1)

161. 102.006 1,760.857 ↓ 1.5 245,787 1

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 15,490kB
162. 540.650 1,658.851 ↓ 1.5 245,787 1

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

  • Hash Cond: (p_1.entity_id = lse_13.loan_id)
163. 1,063.327 1,063.327 ↓ 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.071..1,063.327 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
164. 17.715 54.874 ↓ 1.0 53,499 1

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

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

166. 0.032 0.032 ↓ 0.0 0 4

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

  • Index Cond: (payment_id = p_1.id)
  • Filter: (product = 'BALANCE_CREDIT'::text)
167. 0.001 5,533.224 ↓ 0.0 0 1

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

168. 52.225 5,533.223 ↓ 0.0 0 1

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

  • Hash Cond: (l.loan_id = lse_14.loan_id)
169. 56.232 5,268.668 ↑ 2.7 141,798 1

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

170. 267.067 5,212.436 ↑ 2.7 141,798 1

HashSetOp Except (cost=62,363.83..222,799.71 rows=381,408 width=12) (actual time=5,173.449..5,212.436 rows=141,798 loops=1)

171. 225.571 4,945.369 ↓ 1.0 627,360 1

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

172. 150.949 1,943.270 ↓ 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,638.290..1,943.270 rows=384,579 loops=1)

173. 362.718 1,792.321 ↓ 1.0 384,579 1

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

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

  • Filter: ((contract_date >= '2016-01-01'::date) AND (contract_date <= CURRENT_DATE))
  • Rows Removed by Filter: 787
175. 93.154 2,776.528 ↓ 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,591.382..2,776.528 rows=242,781 loops=1)

176. 217.809 2,683.374 ↓ 1.0 242,781 1

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

  • Group Key: ls_9.loan_id, ls_9.contract_date
177. 183.916 2,465.565 ↓ 1.0 242,781 1

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

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

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

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

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

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

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

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 13,449kB
182. 129.000 129.000 ↓ 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.012..129.000 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
183. 0.001 3,633.724 ↓ 0.0 0 1

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

184. 59.685 3,633.723 ↓ 0.0 0 1

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

185. 61.175 3,311.590 ↓ 1.2 65,612 1

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

  • Hash Cond: (p_2.entity_id = lse_15.loan_id)
186. 950.846 2,981.410 ↓ 1.2 65,612 1

Hash Left Join (cost=53,593.64..160,435.29 rows=53,250 width=16) (actual time=1,097.444..2,981.410 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
187. 943.970 943.970 ↓ 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.006..943.970 rows=1,587,524 loops=1)

188. 628.923 1,086.594 ↑ 1.0 1,577,494 1

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 93,470kB
189. 457.671 457.671 ↑ 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.007..457.671 rows=1,577,494 loops=1)

190. 133.109 269.005 ↓ 1.0 385,366 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 22,161kB
191. 135.896 135.896 ↓ 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.012..135.896 rows=385,366 loops=1)

192. 262.448 262.448 ↓ 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.004..0.004 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
193. 87.040 1,167.817 ↓ 0.0 0 1

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

  • Hash Cond: (ach_1.loan_id = ls_10.loan_id)
194. 521.921 521.921 ↑ 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.055..521.921 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
195. 53.117 558.856 ↑ 1.0 142,165 1

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,823kB
196. 234.451 505.739 ↑ 1.0 142,165 1

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

  • Hash Cond: (ls_10.loan_id = lse_16.loan_id)
197. 117.290 117.290 ↓ 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..117.290 rows=385,366 loops=1)

198. 50.428 153.998 ↑ 1.0 142,165 1

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 8,712kB
199. 103.570 103.570 ↑ 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.006..103.570 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
200. 0.001 9,403.520 ↓ 0.0 0 1

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

201. 0.001 9,403.519 ↓ 0.0 0 1

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

202. 750.947 9,403.518 ↓ 0.0 0 1

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

203. 837.299 2,469.171 ↓ 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,757.301..2,469.171 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
204. 77.923 77.923 ↑ 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.923 rows=219,542 loops=1)

  • Filter: (product = 'BALANCE_CREDIT'::text)
  • Rows Removed by Filter: 120
205. 710.633 1,553.949 ↓ 1.0 1,765,088 1

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

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

  • Filter: (product = 'BALANCE_CREDIT'::text)
  • Rows Removed by Filter: 1,790
207. 6,183.400 6,183.400 ↓ 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.004..0.004 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
208. 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)
209. 0.001 1,104.988 ↓ 0.0 0 1

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

210. 2.974 1,104.987 ↓ 0.0 0 1

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

  • Hash Cond: (fr_4.achfilerecord_id = rr_4.originalachrecord_id)
211. 4.156 942.364 ↓ 4.2 2,681 1

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

  • Join Filter: (lse_18.loan_id = fr_4.loan_id)
212. 1.733 182.166 ↓ 2.6 2,681 1

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

213. 56.617 56.617 ↓ 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.617 rows=2,814 loops=1)

  • Filter: (loan_sub_status_id = 41)
  • Rows Removed by Filter: 382,552
214. 123.816 123.816 ↑ 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.044..0.044 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
215. 756.042 756.042 ↑ 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.281..0.282 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
216. 77.357 159.649 ↑ 1.0 219,524 1

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,766kB
217. 82.292 82.292 ↑ 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.292 rows=219,542 loops=1)

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

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

219. 0.649 282.485 ↓ 0.0 0 1

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

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

221. 0.002 281.240 ↓ 0.0 0 1

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

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

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

  • Hash Cond: (charge_entity.entity_id = lse_19.loan_id)
223. 111.333 281.236 ↓ 0.0 0 1

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

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

Seq Scan on charge_entity (cost=0.00..6,806.53 rows=156,361 width=8) (actual time=0.006..169.903 rows=156,403 loops=1)

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

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

226. 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)

227. 0.002 4,051.733 ↓ 0.0 0 1

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

228. 15.397 4,051.731 ↓ 0.0 0 1

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

  • Join Filter: (lc.customer_id = en.customer_id)
  • Rows Removed by Join Filter: 76,208
229. 0.009 2,778.172 ↓ 2.0 2 1

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

230. 0.009 2,775.767 ↓ 2.0 2 1

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

231. 0.011 2,775.728 ↓ 2.0 2 1

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

232. 0.008 2,773.293 ↓ 2.0 2 1

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

233. 0.015 2,773.253 ↓ 2.0 2 1

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

234. 611.792 2,771.304 ↓ 2.0 2 1

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

  • Hash Cond: (p_5.id = t5.payment_id)
  • Filter: (t5.endpoint IS NULL)
  • Rows Removed by Filter: 1,225,850
235. 1,031.087 1,031.087 ↓ 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.155..1,031.087 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
236. 660.963 1,128.425 ↑ 1.0 1,577,494 1

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 93,470kB
237. 467.462 467.462 ↑ 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.013..467.462 rows=1,577,494 loops=1)

238. 1.934 1.934 ↑ 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=0.965..0.967 rows=1 loops=2)

  • Index Cond: (id = p_5.entity_id)
  • Heap Fetches: 2
239. 0.032 0.032 ↑ 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.016..0.016 rows=1 loops=2)

  • Index Cond: (loan_id = p_5.entity_id)
  • Heap Fetches: 0
240. 2.424 2.424 ↑ 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.211..1.212 rows=1 loops=2)

  • Index Cond: (loan_id = p_5.entity_id)
241. 0.030 0.030 ↑ 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.013..0.015 rows=1 loops=2)

  • Index Cond: (loan_id = p_5.entity_id)
242. 2.396 2.396 ↑ 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=1.195..1.198 rows=1 loops=2)

  • Index Cond: (id = lc.customer_id)
  • Heap Fetches: 2
243. 29.414 1,258.162 ↑ 5.2 38,104 2

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

244. 333.556 1,228.748 ↑ 5.2 38,104 2

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

245. 223.132 895.192 ↓ 1.1 610,060 1

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

246. 147.868 415.738 ↑ 1.0 376,728 1

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

247. 267.870 267.870 ↑ 1.0 376,728 1

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

  • Filter: ((entity_type)::text = 'Entity.Customer'::text)
248. 93.016 256.322 ↓ 1.3 233,332 1

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

249. 163.306 163.306 ↓ 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=8.234..163.306 rows=233,332 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 38,186
250. 0.001 675.149 ↓ 0.0 0 1

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

  • Hash Cond: (lse_21.loan_status_id = s.id)
251. 70.760 675.148 ↓ 0.0 0 1

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

  • Filter: ((cf_4.paidnotifdate IS NULL) OR (cf_4.paidnotifdate = ''::text))
  • Rows Removed by Filter: 88,941
252. 98.710 248.624 ↓ 3.2 88,941 1

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

  • Hash Cond: (lse_21.loan_sub_status_id = ss.id)
253. 149.885 149.885 ↑ 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..149.885 rows=321,497 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
255. 0.023 0.023 ↑ 1.0 5 1

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

  • Filter: (title = ANY ('{""Full Term Paid"",""Early Payoff"",Refinanced}'::text[]))
  • Rows Removed by Filter: 53
256. 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)
257. 0.000 0.000 ↓ 0.0 0

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

258. 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)

259. 48.830 3,319.951 ↓ 0.0 0 1

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

260. 226.099 2,087.257 ↓ 295,966.0 295,966 1

Hash Right Join (cost=21,954.06..118,897.85 rows=1 width=24) (actual time=1,942.623..2,087.257 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
261. 1,559.775 1,559.775 ↑ 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=32.893..1,559.775 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
262. 152.198 301.383 ↓ 1.0 385,365 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,171kB
263. 149.185 149.185 ↓ 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.008..149.185 rows=385,365 loops=1)

  • Filter: (loan_id <> 79895)
  • Rows Removed by Filter: 1
264. 1,183.864 1,183.864 ↓ 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.004..0.004 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
265. 26.435 791.106 ↓ 0.0 0 1

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

  • Filter: ((cf_6.paidnotifdate IS NULL) OR (cf_6.paidnotifdate = ''::text))
  • Rows Removed by Filter: 27,763
266. 22.527 709.145 ↑ 1.3 27,763 1

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

  • Hash Cond: (f_1.loan_id = lse_23.loan_id)
267. 394.149 394.149 ↑ 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.863..394.149 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
268. 152.811 292.469 ↓ 1.0 385,366 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,171kB
269. 139.658 139.658 ↓ 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.007..139.658 rows=385,366 loops=1)

270. 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)
271. 0.027 503.221 ↓ 0.0 0 1

Hash Join (cost=23,341.26..42,799.39 rows=81,246 width=116) (actual time=503.221..503.221 rows=0 loops=1)

  • Hash Cond: (lse_24.loan_status_id = s_1.id)
272. 21.193 503.167 ↓ 0.0 0 1

Hash Join (cost=23,299.76..41,640.76 rows=81,246 width=16) (actual time=503.167..503.167 rows=0 loops=1)

  • Hash Cond: (lse_24.loan_sub_status_id = ss_1.id)
273. 178.320 481.930 ↓ 1.0 89,318 1

Hash Join (cost=23,297.30..40,492.42 rows=88,911 width=24) (actual time=213.027..481.930 rows=89,318 loops=1)

  • Hash Cond: (lse_24.id = cf_7.loan_settings_id)
274. 91.209 91.209 ↓ 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.209 rows=385,366 loops=1)

275. 32.126 212.401 ↑ 1.0 89,318 1

Hash (cost=22,169.41..22,169.41 rows=90,231 width=4) (actual time=212.401..212.401 rows=89,318 loops=1)

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

Seq Scan on loan_settings_custom_fields cf_7 (cost=0.00..22,169.41 rows=90,231 width=4) (actual time=0.009..180.275 rows=89,318 loops=1)

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

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

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

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

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

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

281. 0.002 6,877.574 ↓ 0.0 0 1

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

  • Filter: (ne.id IS NULL)
282. 278.614 6,877.572 ↓ 0.0 0 1

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

283. 347.660 2,427.947 ↓ 320,847.0 320,847 1

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

284. 183.256 476.082 ↓ 320,841.0 320,841 1

Hash Right Join (cost=21,167.09..21,247.34 rows=1 width=24) (actual time=302.204..476.082 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
285. 4.232 4.232 ↑ 1.0 656 1

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

  • Filter: (note_data ~~* '%Paid Off Notice%'::text)
286. 125.767 288.594 ↑ 1.0 321,497 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 21,678kB
287. 162.827 162.827 ↑ 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.009..162.827 rows=321,497 loops=1)

  • Filter: (closed_date > '2016-10-01'::date)
  • Rows Removed by Filter: 63,869
288. 1,604.205 1,604.205 ↑ 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.005..0.005 rows=1 loops=320,841)

  • Index Cond: (loan_id = lse_25.loan_id)
  • Heap Fetches: 71,796
289. 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
290. 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)
291. 14.166 120,388.259 ↓ 0.0 0 1

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

  • Join Filter: (cfe1.custom_field_value <> cfe2.custom_field_value)
  • Rows Removed by Join Filter: 9,002
292. 33.960 114,954.889 ↓ 1.3 9,002 1

Nested Loop (cost=24,566.81..562,678.20 rows=7,121 width=40) (actual time=271.725..114,954.889 rows=9,002 loops=1)

293. 96.812 67,191.569 ↓ 1.8 23,512 1

Hash Join (cost=24,566.12..445,932.59 rows=12,883 width=28) (actual time=271.677..67,191.569 rows=23,512 loops=1)

  • Hash Cond: (cfe3.entity_id = lse_26.id)
294. 66,844.606 66,882.537 ↓ 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=59.113..66,882.537 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
295. 37.931 37.931 ↑ 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=37.931..37.931 rows=204,507 loops=1)

  • Index Cond: (custom_field_id = 74)
296. 88.565 212.220 ↑ 1.0 240,387 1

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 15,195kB
297. 123.655 123.655 ↑ 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..123.655 rows=240,387 loops=1)

  • Filter: (loan_status_id = ANY ('{4,5}'::bigint[]))
  • Rows Removed by Filter: 144,979
298. 47,729.360 47,729.360 ↓ 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=2.015..2.030 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
299. 5,419.204 5,419.204 ↑ 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.585..0.602 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)
300. 0.002 28,526.047 ↓ 0.0 0 1

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

301. 85.322 28,526.045 ↓ 0.0 0 1

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

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

CTE max_date_qry

303. 0.005 1.040 ↑ 1.0 1 1

Result (cost=0.72..0.73 rows=1 width=4) (actual time=1.038..1.040 rows=1 loops=1)

304.          

Initplan (for Result)

305. 0.002 1.035 ↑ 1.0 1 1

Limit (cost=0.57..0.72 rows=1 width=4) (actual time=1.034..1.035 rows=1 loops=1)

306. 1.033 1.033 ↑ 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.032..1.033 rows=1 loops=1)

  • Index Cond: ((date IS NOT NULL) AND (date <= CURRENT_DATE))
  • Heap Fetches: 1
307. 102.067 28,396.171 ↓ 72.1 191,401 1

Nested Loop (cost=17,364.93..1,145,807.36 rows=2,654 width=26) (actual time=287.303..28,396.171 rows=191,401 loops=1)

308. 1.045 1.045 ↑ 1.0 1 1

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

309. 28,014.195 28,293.059 ↓ 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=286.254..28,293.059 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
310. 0.008 278.864 ↓ 0.0 0 1

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

311. 134.927 134.927 ↓ 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=134.927..134.927 rows=382,079 loops=1)

  • Index Cond: (date = max_date_qry.max_date)
312. 143.929 143.929 ↓ 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=143.928..143.929 rows=382,055 loops=1)

  • Index Cond: (date = (max_date_qry.max_date - 1))
313. 11.238 44.552 ↓ 7.5 32,958 1

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

  • Buckets: 65,536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,057kB
314. 33.314 33.314 ↓ 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.114..33.314 rows=32,958 loops=1)

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

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

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

CTE payment_date

317. 10,705.506 13,102.583 ↓ 1.0 1,356,175 1

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

318. 1,350.419 2,397.077 ↓ 1.0 1,356,175 1

Sort (cost=241,755.04..244,989.29 rows=1,293,703 width=12) (actual time=1,941.107..2,397.077 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
319. 1,046.658 1,046.658 ↓ 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.011..1,046.658 rows=1,356,175 loops=1)

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

CTE y

321. 0.507 14,736.975 ↓ 239.0 239 1

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

  • Group Key: x_2.loan_id, x_2.days_to_clear, x_2.loan_status_id
322. 0.702 14,736.468 ↓ 733.0 733 1

Sort (cost=46,444.17..46,444.17 rows=1 width=24) (actual time=14,736.322..14,736.468 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
323. 0.500 14,735.766 ↓ 733.0 733 1

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

324. 11.080 14,735.266 ↓ 733.0 733 1

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

  • Join Filter: (lse_41.loan_id = ls_13.loan_id)
325. 1.367 14,710.992 ↓ 733.0 733 1

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

  • Join Filter: (lse_41.loan_id = lsa_7.loan_id)
326. 78.060 14,158.393 ↓ 19.6 352 1

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

  • Hash Cond: (pd.entity_id = lse_41.loan_id)
327. 14,030.098 14,030.098 ↓ 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,943.429..14,030.098 rows=205,740 loops=1)

  • Filter: (row_number = 1)
  • Rows Removed by Filter: 1,150,435
328. 0.124 50.235 ↑ 3.0 352 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 33kB
329. 50.111 50.111 ↑ 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.171..50.111 rows=352 loops=1)

  • Filter: (loan_sub_status_id = 39)
  • Rows Removed by Filter: 385,014
330. 551.232 551.232 ↓ 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.862..1.566 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)
331. 13.194 13.194 ↑ 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.018..0.018 rows=1 loops=733)

  • Index Cond: (loan_id = lsa_7.loan_id)
  • Heap Fetches: 15
332. 0.005 685.108 ↑ 665.3 3 1

Subquery Scan on *SELECT* 36 (cost=0.87..60,866.17 rows=1,996 width=116) (actual time=653.964..685.108 rows=3 loops=1)

333. 4.116 685.103 ↑ 665.3 3 1

Nested Loop (cost=0.87..60,836.23 rows=1,996 width=108) (actual time=653.962..685.103 rows=3 loops=1)

334. 571.967 571.967 ↓ 7.2 21,804 1

Index Scan using custom_field__entity_last_updated on custom_field__entity cfe_3 (cost=0.44..38,053.41 rows=3,035 width=12) (actual time=41.352..571.967 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,718
335. 109.020 109.020 ↓ 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.005..0.005 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
336. 0.050 164.798 ↓ 0.0 0 1

Hash Join (cost=23,425.63..40,620.75 rows=22,115 width=69) (actual time=164.798..164.798 rows=0 loops=1)

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

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

338. 0.001 164.740 ↓ 0.0 0 1

Hash (cost=23,145.10..23,145.10 rows=22,443 width=21) (actual time=164.740..164.740 rows=0 loops=1)

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

Seq Scan on loan_settings_custom_fields scf (cost=0.00..23,145.10 rows=22,443 width=21) (actual time=164.739..164.739 rows=0 loops=1)

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

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

  • One-Time Filter: (NOT (hashed SubPlan 6))
341. 1.292 1,190.448 ↓ 639.0 639 1

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

342. 1.529 379.478 ↓ 709.0 709 1

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

343. 5.445 340.275 ↓ 2,898.0 2,898 1

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

  • Join Filter: (lse_29.loan_id = lset.loan_id)
344. 14.298 259.590 ↓ 3,762.0 3,762 1

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

345. 16.523 60.840 ↓ 30,742.0 30,742 1

Merge Left Join (cost=120.99..6,379.18 rows=1 width=24) (actual time=17.089..60.840 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
346. 26.418 26.418 ↓ 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.016..26.418 rows=32,958 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 42
347. 1.736 17.899 ↓ 3.2 2,352 1

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

  • Sort Key: pe_5.entity_id
  • Sort Method: quicksort Memory: 207kB
348. 16.163 16.163 ↓ 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.541..16.163 rows=2,352 loops=1)

  • Index Cond: (apply_date = (now())::date)
349. 184.452 184.452 ↓ 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.006..0.006 rows=0 loops=30,742)

  • Index Cond: (parent_id = lse_29.loan_id)
  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
350. 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
351. 37.674 37.674 ↓ 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.013..0.013 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
352. 809.678 809.678 ↑ 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=1.142..1.142 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
353.          

SubPlan (for Result)

354. 0.537 0.537 ↑ 1.0 37 1

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

355. 0.009 18,502.380 ↑ 2,232.7 7 1

Result (cost=5,251.99..151,926.39 rows=15,629 width=87) (actual time=4,395.170..18,502.380 rows=7 loops=1)

356. 0.009 18,502.371 ↑ 2,232.7 7 1

Append (cost=5,251.99..151,770.10 rows=15,629 width=51) (actual time=4,395.169..18,502.371 rows=7 loops=1)

357. 82.154 17,868.312 ↑ 1,227.0 1 1

Hash Left Join (cost=5,251.99..84,080.89 rows=1,227 width=51) (actual time=4,395.167..17,868.312 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
358. 27.989 17,663.337 ↓ 7.5 9,210 1

Nested Loop Left Join (cost=2.80..78,726.09 rows=1,227 width=42) (actual time=4.184..17,663.337 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
359. 18.739 17,073.538 ↓ 7.5 9,210 1

Nested Loop Left Join (cost=2.38..62,349.90 rows=1,227 width=38) (actual time=2.874..17,073.538 rows=9,210 loops=1)

360. 76.949 16,898.229 ↓ 7.5 9,210 1

Nested Loop Left Join (cost=1.95..52,222.55 rows=1,227 width=39) (actual time=2.846..16,898.229 rows=9,210 loops=1)

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

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

362. 14.879 101.202 ↓ 7.5 9,210 1

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

363. 40.273 40.273 ↓ 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.034..40.273 rows=9,210 loops=1)

  • Filter: (loan_sub_status_id = 55)
  • Rows Removed by Filter: 44,289
364. 46.050 46.050 ↑ 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.005..0.005 rows=1 loops=9,210)

  • Index Cond: (loan_id = lse_30.loan_id)
  • Heap Fetches: 33
365. 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)
366. 16,670.100 16,670.100 ↓ 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=1.810..1.810 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
367.          

SubPlan (for Nested Loop Left Join)

368. 8.925 8.925 ↑ 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.175..0.175 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
369. 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))
370. 156.570 156.570 ↓ 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.017..0.017 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
371. 561.810 561.810 ↑ 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.049..0.061 rows=6 loops=9,210)

  • Index Cond: (entity_id = lse_30.loan_id)
372. 32.035 122.806 ↑ 1.0 94,829 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,729kB
373. 90.771 90.771 ↑ 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.713..90.771 rows=94,829 loops=1)

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

SubPlan (for Hash Left Join)

375. 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)

376. 28.562 253.990 ↑ 1,233.0 6 1

Hash Join (cost=4,326.06..26,903.58 rows=7,398 width=51) (actual time=196.778..253.990 rows=6 loops=1)

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

Seq Scan on loan_settings_custom_fields cf_9 (cost=0.00..22,169.41 rows=89,099 width=7) (actual time=0.008..189.167 rows=88,191 loops=1)

  • Filter: (called_due_notice_date <> ''::text)
  • Rows Removed by Filter: 297,175
378. 12.198 36.261 ↓ 1.0 32,958 1

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

  • Buckets: 65,536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2,315kB
379. 24.063 24.063 ↓ 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.012..24.063 rows=32,958 loops=1)

  • Filter: (loan_sub_status_id = 20)
  • Rows Removed by Filter: 42
380. 79.374 380.060 ↓ 0.0 0 1

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

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

Seq Scan on loan_settings_custom_fields cf_10 (cost=0.00..22,169.41 rows=301,174 width=7) (actual time=0.016..237.703 rows=297,175 loops=1)

  • Filter: (called_due_notice_date = ''::text)
  • Rows Removed by Filter: 88,191
382. 3.609 62.983 ↓ 1.0 9,210 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 632kB
383. 59.374 59.374 ↓ 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.372..59.374 rows=9,210 loops=1)

  • Filter: (loan_sub_status_id = 55)
  • Rows Removed by Filter: 376,156
384. 0.006 160,134.812 ↑ 4,104.2 4 1

Subquery Scan on x_1 (cost=0.57..866,087.59 rows=16,417 width=116) (actual time=159,272.557..160,134.812 rows=4 loops=1)

385. 0.009 160,134.806 ↑ 4,104.2 4 1

Append (cost=0.57..865,923.42 rows=16,417 width=56) (actual time=159,272.555..160,134.806 rows=4 loops=1)

386. 0.001 159,168.117 ↓ 0.0 0 1

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

387. 185.017 159,168.116 ↓ 0.0 0 1

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

388. 49.695 49.695 ↑ 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.010..49.695 rows=94,829 loops=1)

389. 158,933.404 158,933.404 ↓ 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=1.676..1.676 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
390. 0.005 118.559 ↑ 1.0 1 1

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

391. 0.010 118.540 ↑ 1.0 1 1

Nested Loop Left Join (cost=6,299.15..21,895.73 rows=1 width=36) (actual time=104.418..118.540 rows=1 loops=1)

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

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

  • Join Filter: (lse_33.loan_id = lsa_5.loan_id)
393. 30.829 98.898 ↓ 3.5 3,782 1

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

  • Hash Cond: (rtc_3.parent_id = lse_33.loan_id)
394. 31.481 31.481 ↑ 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.010..31.481 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
395. 11.851 36.588 ↓ 7.5 32,958 1

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

  • Buckets: 65,536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,315kB
396. 24.737 24.737 ↓ 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.013..24.737 rows=32,958 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 42
397. 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
398. 0.108 0.108 ↓ 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.036 rows=4 loops=3)

  • Index Cond: (entity_id = lse_33.loan_id)
  • Filter: ((reverse_date IS NULL) AND (payment_method_id = 3))
  • Rows Removed by Filter: 19
399. 0.014 0.014 ↑ 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.014..0.014 rows=1 loops=1)

  • Index Cond: (loan_id = lse_33.loan_id)
  • Heap Fetches: 0
400. 27.448 461.742 ↓ 0.0 0 1

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

401. 31.795 346.760 ↓ 29,178.0 29,178 1

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

402. 42.731 110.719 ↓ 29,178.0 29,178 1

Hash Right Join (cost=4,059.48..8,488.26 rows=1 width=32) (actual time=97.039..110.719 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
403. 30.993 30.993 ↑ 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.015..30.993 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
404. 12.240 36.995 ↓ 7.5 32,958 1

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

  • Buckets: 65,536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,572kB
405. 24.755 24.755 ↓ 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.011..24.755 rows=32,958 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 42
406. 204.246 204.246 ↑ 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.007..0.007 rows=1 loops=29,178)

  • Index Cond: (loan_id = lse_34.loan_id)
  • Heap Fetches: 4,315
407. 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
408. 0.001 112.187 ↓ 0.0 0 1

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

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

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

  • Join Filter: (lse_35.loan_id = rtc2.parent_id)
410. 33.196 102.988 ↓ 3.5 3,782 1

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

  • Hash Cond: (rtc_5.parent_id = lse_35.loan_id)
411. 33.498 33.498 ↑ 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.008..33.498 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
412. 11.356 36.294 ↓ 7.5 32,958 1

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

  • Buckets: 65,536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,315kB
413. 24.938 24.938 ↓ 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.013..24.938 rows=32,958 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 42
414. 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
415. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (loan_id = rtc_5.parent_id)
  • Heap Fetches: 0
416. 9.414 213.124 ↓ 3.0 3 1

Nested Loop (cost=4,060.33..11,045.12 rows=1 width=56) (actual time=167.234..213.124 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
417. 4.473 112.942 ↓ 3.5 3,782 1

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

  • Join Filter: (lse_36.loan_id = lset_5.loan_id)
418. 32.059 100.905 ↓ 3.5 3,782 1

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

  • Hash Cond: (rtc_6.parent_id = lse_36.loan_id)
419. 31.579 31.579 ↑ 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.006..31.579 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
420. 11.713 37.267 ↓ 7.5 32,958 1

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

  • Buckets: 65,536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,315kB
421. 25.554 25.554 ↓ 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.013..25.554 rows=32,958 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 42
422. 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
423. 90.768 90.768 ↑ 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.016..0.024 rows=6 loops=3,782)

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

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

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

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

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

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

  • Hash Cond: ((rtc_7.created)::date = bank_holidays.holiday_date)
427. 30.244 30.244 ↑ 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.007..30.244 rows=94,829 loops=1)

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

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

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

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

430. 0.000 0.000 ↓ 0.0 0

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

431. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: (loan_id = rtc_7.parent_id)
  • Heap Fetches: 0
433. 0.196 14,401.523 ↓ 122.0 122 1

Result (cost=10,876.11..145,905.29 rows=1 width=70) (actual time=1,774.250..14,401.523 rows=122 loops=1)

  • One-Time Filter: (NOT (hashed SubPlan 2))
434. 0.658 14,401.310 ↓ 122.0 122 1

Nested Loop Anti Join (cost=10,876.11..145,905.29 rows=1 width=70) (actual time=1,774.207..14,401.310 rows=122 loops=1)

435. 0.429 14,399.310 ↓ 122.0 122 1

Nested Loop Anti Join (cost=10,874.22..145,895.89 rows=1 width=43) (actual time=1,774.179..14,399.310 rows=122 loops=1)

  • Join Filter: (note_entity.id < rtc_8.id)
436. 12.349 13,013.083 ↓ 122.0 122 1

Nested Loop (cost=10,873.79..145,830.47 rows=1 width=43) (actual time=1,758.036..13,013.083 rows=122 loops=1)

437. 8.330 5,115.646 ↓ 7,648.0 7,648 1

Nested Loop (cost=10,873.22..145,822.18 rows=1 width=47) (actual time=1,341.013..5,115.646 rows=7,648 loops=1)

438. 451.836 5,076.724 ↓ 7,648.0 7,648 1

Hash Join (cost=10,872.79..145,817.89 rows=1 width=43) (actual time=1,340.985..5,076.724 rows=7,648 loops=1)

  • Hash Cond: ((a_1.epic_key)::text = (lse_38.loan_id)::text)
439. 4,477.429 4,477.429 ↑ 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.315..4,477.429 rows=934,774 loops=1)

440. 11.384 147.459 ↓ 26,982.0 26,982 1

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

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,732kB
441. 40.657 136.075 ↓ 26,982.0 26,982 1

Hash Right Join (cost=6,449.40..10,872.78 rows=1 width=43) (actual time=126.623..136.075 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
442. 31.864 31.864 ↑ 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.008..31.864 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
443. 16.953 63.554 ↓ 14.0 30,742 1

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

  • Buckets: 32,768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,938kB
444. 44.968 46.601 ↓ 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.592..46.601 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
445.          

SubPlan (for Index Scan)

446. 1.633 1.633 ↓ 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.020..1.633 rows=2,352 loops=1)

  • Index Cond: (apply_date = CURRENT_DATE)
447. 30.592 30.592 ↑ 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.004..0.004 rows=1 loops=7,648)

  • Index Cond: (loan_id = lse_38.loan_id)
  • Heap Fetches: 19
448. 7,885.088 7,885.088 ↓ 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=1.031..1.031 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
449. 1,385.798 1,385.798 ↓ 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=11.359..11.359 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
450. 1.342 1.342 ↓ 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.011..0.011 rows=0 loops=122)

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

SubPlan (for Result)

452. 0.017 0.017 ↑ 1.0 37 1

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

453. 0.002 20,575.710 ↓ 0.0 0 1

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

454. 9.531 20,575.708 ↓ 0.0 0 1

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

455. 11.637 4,053.933 ↓ 632.8 17,717 1

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

456. 31.024 2,365.692 ↓ 68.8 18,028 1

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

457. 23.375 1,862.108 ↓ 52.2 19,690 1

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

458. 28.148 1,740.283 ↓ 52.2 19,690 1

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

459. 39.455 39.455 ↓ 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.020..39.455 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
460. 1,672.680 1,672.680 ↑ 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.060..0.060 rows=1 loops=27,878)

  • Index Cond: (loan_id = settings.loan_id)
  • Filter: (til_payment_schedule ~~ '%,{""count"":1%'::text)
  • Rows Removed by Filter: 0
461. 98.450 98.450 ↑ 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.004..0.005 rows=1 loops=19,690)

  • Index Cond: (loan_id = setup.loan_id)
462. 472.560 472.560 ↑ 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.022..0.024 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
463. 1,676.604 1,676.604 ↑ 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.093..0.093 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
464. 16,512.244 16,512.244 ↑ 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.932..0.932 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
465. 0.001 5,194.205 ↓ 0.0 0 1

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

466. 23.175 5,194.204 ↓ 0.0 0 1

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

  • Filter: (ne_1.id IS NULL)
  • Rows Removed by Filter: 18,677
467. 53.531 53.531 ↓ 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=1.355..53.531 rows=18,677 loops=1)

  • Index Cond: (created_at >= (CURRENT_DATE - 10))
  • Filter: ((loan_id <> 249271) AND (loan_id <> ALL ('{312432,301017,299218,242424,312946,275707,256470}'::integer[])))
468. 5,117.498 5,117.498 ↑ 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.268..0.274 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
469. 0.038 16.728 ↓ 0.0 0 1

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

470. 16.606 16.606 ↑ 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.495..16.606 rows=42 loops=1)

  • Filter: (loan_sub_status_id = 14)
  • Rows Removed by Filter: 32,958
471. 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 : 53.696 ms
Execution time : 444,091.440 ms