explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nXu6 : Optimization for: plan #bF8m

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.429 558,543.586 ↑ 35,818.3 575 1

Append (cost=19,638.47..7,112,485.20 rows=20,595,527 width=116) (actual time=809.111..558,543.586 rows=575 loops=1)

2. 0.214 4.535 ↓ 0.0 0 1

Hash Join (cost=19,638.47..37,004.87 rows=68,512 width=92) (actual time=4.535..4.535 rows=0 loops=1)

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

4. 0.001 4.317 ↓ 0.0 0 1

Hash (cost=18,777.69..18,777.69 rows=68,862 width=12) (actual time=4.317..4.317 rows=0 loops=1)

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

Bitmap Heap Scan on loan_settings_custom_fields cf (cost=42.69..18,777.69 rows=68,862 width=12) (actual time=4.316..4.316 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 4.313 ↓ 0.0 0 1

BitmapOr (cost=42.69..42.69 rows=69,600 width=0) (actual time=4.313..4.313 rows=0 loops=1)

7. 0.435 0.435 ↓ 0.0 0 1

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

8. 3.877 3.877 ↓ 0.0 0 1

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

9. 38.754 179.141 ↓ 0.0 0 1

Hash Join (cost=7,929.36..27,784.77 rows=19,220 width=91) (actual time=179.140..179.141 rows=0 loops=1)

  • Hash Cond: (cf_1.loan_settings_id = lse.id)
10. 58.191 80.611 ↑ 1.0 142,242 1

Bitmap Heap Scan on loan_settings_custom_fields cf_1 (cost=2,324.71..21,452.49 rows=142,782 width=11) (actual time=24.824..80.611 rows=142,242 loops=1)

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

Bitmap Index Scan on loan_settings_custom_fields_loan_settings_id_indx1 (cost=0.00..2,289.02 rows=142,782 width=0) (actual time=22.420..22.420 rows=142,330 loops=1)

12. 17.902 59.776 ↓ 1.0 53,498 1

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

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

14. 124.092 625.737 ↑ 130.5 2 1

Nested Loop (cost=0.84..14,715.70 rows=261 width=91) (actual time=625.432..625.737 rows=2 loops=1)

15. 92.640 92.640 ↓ 70.5 136,335 1

Index Scan using loan_settings_custom_fields_loan_settings_id_cond3 on loan_settings_custom_fields cf_2 (cost=0.42..2,173.02 rows=1,933 width=11) (actual time=2.089..92.640 rows=136,335 loops=1)

16. 409.005 409.005 ↓ 0.0 0 136,335

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

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

Nested Loop (cost=1.09..15,704.88 rows=1,923 width=95) (actual time=2.551..2.551 rows=0 loops=1)

18. 0.005 1.836 ↑ 1,923.0 1 1

Nested Loop (cost=0.67..14,665.87 rows=1,923 width=27) (actual time=1.209..1.836 rows=1 loops=1)

19. 1.819 1.819 ↑ 1,933.0 1 1

Index Scan using loan_settings_custom_fields_loan_settings_id_cond4 on loan_settings_custom_fields cf_3 (cost=0.25..2,123.19 rows=1,933 width=15) (actual time=1.192..1.819 rows=1 loops=1)

20. 0.012 0.012 ↑ 1.0 1 1

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

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

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

Subquery Scan on *SELECT* 5 (cost=1,170.73..100,131.34 rows=522 width=92) (actual time=18,440.336..18,440.336 rows=0 loops=1)

23. 182.738 18,440.335 ↓ 0.0 0 1

Nested Loop (cost=1,170.73..100,123.51 rows=522 width=84) (actual time=18,440.335..18,440.335 rows=0 loops=1)

24. 88.254 3,742.577 ↓ 16.6 161,278 1

Nested Loop (cost=1,170.16..22,357.75 rows=9,731 width=21) (actual time=18.371..3,742.577 rows=161,278 loops=1)

25. 0.036 1.303 ↓ 4.0 4 1

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

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

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

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

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

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

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

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

Bitmap Heap Scan on loan__portfolio lp (cost=1,168.97..21,715.24 rows=63,941 width=8) (actual time=10.110..913.255 rows=40,320 loops=4)

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

Bitmap Index Scan on loan__portfolio_portfolio_id (cost=0.00..1,152.99 rows=63,941 width=0) (actual time=8.558..8.558 rows=40,320 loops=4)

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

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

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

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

33. 0.001 247.102 ↓ 0.0 0 1

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

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

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

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

Nested Loop (cost=2.42..10,148.75 rows=614 width=33) (actual time=247.100..247.100 rows=0 loops=1)

36. 26.021 124.475 ↓ 5.3 11,967 1

Nested Loop (cost=2.01..2,317.71 rows=2,247 width=25) (actual time=84.473..124.475 rows=11,967 loops=1)

  • Join Filter: (cle.id = cie.checklist_item_id)
  • Rows Removed by Join Filter: 83,658
37. 0.025 1.194 ↑ 1.0 4 1

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

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

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

39. 0.005 0.565 ↑ 1.0 4 1

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

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

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

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

Index Scan using checklist_item__entity_checklist_id_cond1 on checklist_item__entity cie (cost=0.42..368.25 rows=16,504 width=16) (actual time=0.312..24.315 rows=23,906 loops=4)

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

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.010..0.010 rows=0 loops=11,967)

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

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

44. 0.000 0.000 ↓ 0.0 0

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

45. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (loan_id = cie.entity_id)
46. 0.016 1,051.143 ↑ 3,633.0 2 1

Subquery Scan on x (cost=36,434.86..36,761.83 rows=7,266 width=116) (actual time=1,051.136..1,051.143 rows=2 loops=1)

47. 0.004 1,051.127 ↑ 3,633.0 2 1

Unique (cost=36,434.86..36,562.02 rows=7,266 width=82) (actual time=1,051.124..1,051.127 rows=2 loops=1)

48. 0.026 1,051.123 ↑ 3,633.0 2 1

Sort (cost=36,434.86..36,453.03 rows=7,266 width=82) (actual time=1,051.122..1,051.123 rows=2 loops=1)

  • Sort Key: ls_1.loan_id, ls_1.loan_status_id, pe_1.title, spe.title, pce_1.title, (CASE WHEN (pce_1.title = 'Owner'::text) THEN lscf.owner WHEN (pce_1.title = 'Marketing'::text) THEN lscf.mktg_channel1 WHEN (pce_1.title = 'Pricing'::text) THEN lscf.pricing WHEN (pce_1.title = 'PaymentType'::text) THEN lscf.payment_type WHEN (pce_1.title = 'Servicer'::text) THEN lscf.servicer WHEN (pce_1.title = 'Customer Type'::text) THEN lscf.customertype WHEN (pce_1.title = 'EV Type'::text) THEN lscf.ev_type ELSE NULL::text END)
  • Sort Method: quicksort Memory: 25kB
49. 20.632 1,051.097 ↑ 3,633.0 2 1

Hash Left Join (cost=21,021.85..35,968.86 rows=7,266 width=82) (actual time=1,051.054..1,051.097 rows=2 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,795
50. 18.688 1,028.589 ↑ 1.1 13,797 1

Nested Loop (cost=20,997.96..35,762.34 rows=14,533 width=115) (actual time=385.877..1,028.589 rows=13,797 loops=1)

51. 19.662 469.401 ↓ 1.2 11,500 1

Nested Loop (cost=20,997.53..30,627.70 rows=9,474 width=115) (actual time=384.743..469.401 rows=11,500 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,853
52. 10.464 423.033 ↓ 1.4 13,353 1

Hash Join (cost=20,997.11..24,267.69 rows=9,522 width=45) (actual time=384.718..423.033 rows=13,353 loops=1)

  • Hash Cond: (lp_1.loan_id = ls_1.loan_id)
53. 9.112 30.092 ↓ 1.3 13,371 1

Hash Join (cost=4.44..3,142.52 rows=9,943 width=21) (actual time=1.487..30.092 rows=13,371 loops=1)

  • Hash Cond: (lp_1.portfolio_id = pe_1.id)
54. 20.896 20.896 ↓ 1.1 15,227 1

Index Scan using loan__portfolio_lastupdated on loan__portfolio lp_1 (cost=0.43..2,986.53 rows=14,204 width=8) (actual time=1.393..20.896 rows=15,227 loops=1)

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

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

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

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

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

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

58. 0.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
59. 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
60. 130.332 382.477 ↓ 1.0 385,452 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,176kB
61. 252.145 252.145 ↓ 1.0 385,452 1

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

62. 26.706 26.706 ↑ 1.0 1 13,353

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

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

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.047..0.047 rows=1 loops=11,500)

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

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

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

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

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

Subquery Scan on *SELECT* 8 (cost=412,592.00..534,758.58 rows=2,571,928 width=116) (actual time=8,418.409..8,418.410 rows=0 loops=1)

67. 1,955.182 8,418.408 ↓ 0.0 0 1

GroupAggregate (cost=412,592.00..502,609.48 rows=2,571,928 width=121) (actual time=8,418.408..8,418.408 rows=0 loops=1)

  • Group Key: lp_2.loan_id, pce_2.title, lse_4.loan_status_id
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 2,685,515
68. 2,699.781 6,463.226 ↓ 1.0 2,685,515 1

Sort (cost=412,592.00..419,021.82 rows=2,571,928 width=29) (actual time=5,898.816..6,463.226 rows=2,685,515 loops=1)

  • Sort Key: lp_2.loan_id, pce_2.title, lse_4.loan_status_id
  • Sort Method: quicksort Memory: 323,076kB
69. 1,366.727 3,763.445 ↓ 1.0 2,685,515 1

Hash Join (cost=20,997.03..138,753.44 rows=2,571,928 width=29) (actual time=258.718..3,763.445 rows=2,685,515 loops=1)

  • Hash Cond: (lp_2.portfolio_id = pe_2.id)
70. 1,482.906 2,396.625 ↓ 1.0 2,685,515 1

Hash Join (cost=20,992.67..103,385.07 rows=2,571,928 width=16) (actual time=258.616..2,396.625 rows=2,685,515 loops=1)

  • Hash Cond: (lp_2.loan_id = lse_4.loan_id)
71. 655.673 655.673 ↑ 1.0 2,685,536 1

Seq Scan on loan__portfolio lp_2 (cost=0.00..46,602.36 rows=2,685,536 width=8) (actual time=0.005..655.673 rows=2,685,536 loops=1)

72. 122.305 258.046 ↓ 1.0 385,452 1

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

  • Buckets: 524,288 Batches: 1 Memory Usage: 22,165kB
73. 135.741 135.741 ↓ 1.0 385,452 1

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

74. 0.024 0.093 ↑ 1.0 59 1

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

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

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

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

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

77. 0.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
78. 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)

79. 0.001 100.927 ↓ 0.0 0 1

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

80. 1.636 100.926 ↓ 0.0 0 1

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

81. 2.909 95.396 ↓ 3.6 1,947 1

Nested Loop (cost=45.93..10,194.03 rows=542 width=26) (actual time=19.906..95.396 rows=1,947 loops=1)

82. 69.601 88.583 ↓ 3.5 1,952 1

Bitmap Heap Scan on loan_setup_entity ls_2 (cost=45.50..5,966.44 rows=556 width=10) (actual time=19.888..88.583 rows=1,952 loops=1)

  • Recheck Cond: (contract_date >= (CURRENT_DATE - '10 days'::interval))
  • Filter: (loan_rate > '25'::numeric)
  • Rows Removed by Filter: 1,350
  • Heap Blocks: exact=2,515
83. 18.982 18.982 ↓ 8.7 14,922 1

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

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

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

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

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

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

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

87. 1.052 15.076 ↓ 0.0 0 1

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

88. 2.259 10.130 ↓ 2.2 1,947 1

Nested Loop (cost=46.02..12,557.02 rows=894 width=26) (actual time=1.153..10.130 rows=1,947 loops=1)

89. 3.115 3.967 ↓ 2.1 1,952 1

Bitmap Heap Scan on loan_setup_entity ls_3 (cost=45.59..5,966.53 rows=916 width=10) (actual time=1.145..3.967 rows=1,952 loops=1)

  • Recheck Cond: (contract_date >= (CURRENT_DATE - '10 days'::interval))
  • Filter: (loan_rate > 9.9)
  • Rows Removed by Filter: 1,350
  • Heap Blocks: exact=2,515
90. 0.852 0.852 ↓ 8.7 14,922 1

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

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

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

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

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

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

Subquery Scan on *SELECT* 11 (cost=0.84..37,109.83 rows=1,923 width=106) (actual time=291.980..291.980 rows=0 loops=1)

94. 0.001 291.979 ↓ 0.0 0 1

Nested Loop (cost=0.84..37,085.79 rows=1,923 width=102) (actual time=291.979..291.979 rows=0 loops=1)

95. 0.001 291.978 ↓ 0.0 0 1

Nested Loop (cost=0.42..35,950.06 rows=1,923 width=26) (actual time=291.978..291.978 rows=0 loops=1)

96. 291.977 291.977 ↓ 0.0 0 1

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

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

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

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

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

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

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

100. 0.001 0.009 ↓ 0.0 0 1

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

101. 0.008 0.008 ↓ 0.0 0 1

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

102. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (loan_id = la_1.loan_id)
103. 0.001 1.508 ↓ 0.0 0 1

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

104. 0.000 1.507 ↓ 0.0 0 1

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

105. 1.507 1.507 ↓ 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=1.506..1.507 rows=0 loops=1)

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

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

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

Subquery Scan on cte (cost=131,419.42..131,444.98 rows=71 width=116) (actual time=2,757.291..2,757.292 rows=0 loops=1)

108. 1.011 2,757.290 ↓ 0.0 0 1

GroupAggregate (cost=131,419.42..131,441.96 rows=71 width=90) (actual time=2,757.290..2,757.290 rows=0 loops=1)

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

Sort (cost=131,419.42..131,419.60 rows=71 width=142) (actual time=2,756.026..2,756.279 rows=1,087 loops=1)

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

Nested Loop (cost=5,605.50..131,417.24 rows=71 width=142) (actual time=65.845..2,754.916 rows=1,087 loops=1)

111. 6.444 2,742.495 ↓ 15.3 1,087 1

Nested Loop (cost=5,605.08..131,262.96 rows=71 width=116) (actual time=65.768..2,742.495 rows=1,087 loops=1)

  • Join Filter: ((pe_3.apply_date <= ls_4.first_payment_date) AND ((pe_3.apply_date IS NULL) OR (pe_3.apply_date <= ls_4.first_payment_date)))
  • Rows Removed by Join Filter: 9,862
112. 9.997 2,566.431 ↓ 17.4 11,308 1

Hash Join (cost=5,604.65..129,145.41 rows=649 width=38) (actual time=55.804..2,566.431 rows=11,308 loops=1)

  • Hash Cond: (pe_3.entity_id = lse_10.loan_id)
113. 2,500.939 2,500.939 ↓ 2.4 11,672 1

Seq Scan on payment_entity pe_3 (cost=0.00..123,516.29 rows=4,794 width=14) (actual time=0.231..2,500.939 rows=11,672 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,907
114. 18.756 55.495 ↓ 1.0 53,498 1

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

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

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

116. 169.620 169.620 ↑ 1.0 1 11,308

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.014..0.015 rows=1 loops=11,308)

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

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

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

Subquery Scan on *SELECT* 15 (cost=3,234.18..6,943.71 rows=298 width=116) (actual time=352.678..352.679 rows=0 loops=1)

119. 0.002 352.678 ↓ 0.0 0 1

Nested Loop (cost=3,234.18..6,939.99 rows=298 width=112) (actual time=352.678..352.678 rows=0 loops=1)

120. 219.448 352.676 ↓ 0.0 0 1

Bitmap Heap Scan on loan_autopay_entity a (cost=3,233.75..4,487.66 rows=298 width=16) (actual time=352.676..352.676 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,323
  • Heap Blocks: exact=3,888
121. 0.401 133.228 ↓ 0.0 0 1

BitmapAnd (cost=3,233.75..3,233.75 rows=300 width=0) (actual time=133.228..133.228 rows=0 loops=1)

122. 120.021 120.021 ↓ 1.8 20,917 1

Bitmap Index Scan on loan_autopay_entity_process_datetime (cost=0.00..280.29 rows=11,585 width=0) (actual time=120.021..120.021 rows=20,917 loops=1)

  • Index Cond: ((process_datetime >= CURRENT_DATE) AND (process_datetime <= (CURRENT_DATE + 6)))
123. 12.806 12.806 ↓ 1.0 46,997 1

Bitmap Index Scan on loan_autopay_entity_status_cond_autopay_status_pending (cost=0.00..2,953.07 rows=45,731 width=0) (actual time=12.806..12.806 rows=46,997 loops=1)

124. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (loan_id = a.loan_id)
125. 0.001 595.381 ↓ 0.0 0 1

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

126. 5.113 595.380 ↓ 0.0 0 1

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

127. 3.518 452.867 ↓ 381.7 5,725 1

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

128. 214.624 214.624 ↓ 381.7 5,725 1

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

  • Filter: (((returncode)::text !~~ 'C%'::text) AND (product = 'BALANCE_CREDIT'::text) AND (returndate > (CURRENT_DATE - 30)))
  • Rows Removed by Filter: 213,937
129. 234.725 234.725 ↑ 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.041..0.041 rows=1 loops=5,725)

  • Index Cond: (achfilerecord_id = rr.originalachrecord_id)
  • Filter: (product = 'BALANCE_CREDIT'::text)
130. 137.400 137.400 ↓ 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.024..0.024 rows=0 loops=5,725)

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

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

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

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

133. 3.933 70.342 ↓ 35.5 71 1

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

134. 54.959 54.959 ↓ 381.7 5,725 1

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

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

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

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

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

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

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

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

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

  • Hash Cond: (ltrim(fr_2.transaction_id, ' '::text) = ltrim(rr_2.transaction_id, ' '::text))
139. 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)
140. 0.001 66.980 ↓ 0.0 0 1

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

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

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

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

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

143. 0.000 0.000 ↓ 0.0 0

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

144. 0.001 36,178.610 ↓ 0.0 0 1

Subquery Scan on *SELECT* 19 (cost=412,997.45..823,843.52 rows=17,178,507 width=116) (actual time=36,178.610..36,178.610 rows=0 loops=1)

145. 7.428 36,178.609 ↓ 0.0 0 1

Hash Join (cost=412,997.45..609,112.18 rows=17,178,507 width=112) (actual time=36,178.609..36,178.609 rows=0 loops=1)

  • Hash Cond: (loan_autopay_entity_1.loan_id = tx.loan_id)
  • Join Filter: ((min(loan_autopay_entity_1.apply_date)) <> tx.date)
  • Rows Removed by Join Filter: 10,978
146. 15.459 842.864 ↑ 2.9 10,978 1

Merge Anti Join (cost=30,517.72..54,761.98 rows=31,716 width=24) (actual time=490.856..842.864 rows=10,978 loops=1)

  • Merge Cond: (loan_autopay_entity_1.loan_id = loan_autopay_entity.loan_id)
  • Join Filter: (loan_autopay_entity.apply_date <= (min(loan_autopay_entity_1.apply_date)))
  • Rows Removed by Join Filter: 7,976
147. 8.603 384.976 ↑ 3.4 11,200 1

Sort (cost=30,517.31..30,612.45 rows=38,059 width=24) (actual time=381.525..384.976 rows=11,200 loops=1)

  • Sort Key: loan_autopay_entity_1.loan_id
  • Sort Method: quicksort Memory: 1,260kB
148. 124.331 376.373 ↑ 3.4 11,200 1

Hash Join (cost=10,426.67..27,621.79 rows=38,059 width=24) (actual time=223.711..376.373 rows=11,200 loops=1)

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

Seq Scan on loan_settings_entity lse_12 (cost=0.00..16,185.63 rows=384,563 width=16) (actual time=0.004..94.394 rows=385,452 loops=1)

150. 3.966 157.648 ↑ 3.4 11,200 1

Hash (cost=9,950.93..9,950.93 rows=38,059 width=8) (actual time=157.648..157.648 rows=11,200 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 950kB
151. 19.519 153.682 ↑ 3.4 11,200 1

GroupAggregate (cost=0.29..9,570.34 rows=38,059 width=8) (actual time=1.727..153.682 rows=11,200 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,163
152. 134.163 134.163 ↑ 1.6 27,398 1

Index Only Scan using loan_autopay_entity_loan_id_apply_date_pending on loan_autopay_entity loan_autopay_entity_1 (cost=0.29..8,763.23 rows=42,652 width=8) (actual time=0.697..134.163 rows=27,398 loops=1)

  • Heap Fetches: 22,228
153. 442.429 442.429 ↓ 6.2 19,062 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity (cost=0.41..24,037.93 rows=3,079 width=8) (actual time=0.019..442.429 rows=19,062 loops=1)

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

Hash (cost=381,118.83..381,118.83 rows=108,872 width=12) (actual time=35,328.317..35,328.317 rows=271,943 loops=1)

  • Buckets: 524,288 (originally 131072) Batches: 1 (originally 1) Memory Usage: 15,782kB
155. 107.012 35,234.100 ↓ 2.5 271,943 1

Subquery Scan on tx (cost=378,941.39..381,118.83 rows=108,872 width=12) (actual time=35,020.075..35,234.100 rows=271,943 loops=1)

156. 827.391 35,127.088 ↓ 2.5 271,943 1

HashAggregate (cost=378,941.39..380,030.11 rows=108,872 width=12) (actual time=35,020.073..35,127.088 rows=271,943 loops=1)

  • Group Key: loan_tx.entity_id
157. 34,299.697 34,299.697 ↑ 1.5 1,102,462 1

Index Scan using loan_tx_date_scheduled on loan_tx (cost=0.44..370,441.72 rows=1,699,934 width=12) (actual time=4.399..34,299.697 rows=1,102,462 loops=1)

  • Index Cond: (date > CURRENT_DATE)
158. 0.001 2,866.156 ↓ 0.0 0 1

Subquery Scan on *SELECT* 20 (cost=141,998.91..186,861.73 rows=5,592 width=116) (actual time=2,866.156..2,866.156 rows=0 loops=1)

159. 0.002 2,866.155 ↓ 0.0 0 1

Nested Loop Anti Join (cost=141,998.91..186,791.83 rows=5,592 width=112) (actual time=2,866.155..2,866.155 rows=0 loops=1)

160. 600.488 2,866.153 ↓ 0.0 0 1

Hash Right Join (cost=141,998.48..183,795.07 rows=6,262 width=24) (actual time=2,866.153..2,866.153 rows=0 loops=1)

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

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

162. 98.666 1,749.248 ↓ 1.4 245,568 1

Hash (cost=139,780.89..139,780.89 rows=177,407 width=24) (actual time=1,749.248..1,749.248 rows=245,568 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 15,478kB
163. 507.922 1,650.582 ↓ 1.4 245,568 1

Hash Join (cost=5,604.65..139,780.89 rows=177,407 width=24) (actual time=55.522..1,650.582 rows=245,568 loops=1)

  • Hash Cond: (p_1.entity_id = lse_13.loan_id)
164. 1,087.325 1,087.325 ↑ 1.0 1,304,143 1

Seq Scan on payment_entity p_1 (cost=0.00..127,485.10 rows=1,311,219 width=16) (actual time=0.071..1,087.325 rows=1,304,143 loops=1)

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

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

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

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

167. 0.000 0.000 ↓ 0.0 0

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

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

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

169. 52.886 7,213.446 ↓ 0.0 0 1

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

  • Hash Cond: (l.loan_id = lse_14.loan_id)
170. 60.920 6,951.222 ↑ 2.7 141,798 1

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

171. 265.429 6,890.302 ↑ 2.7 141,798 1

HashSetOp Except (cost=62,363.83..222,799.71 rows=381,408 width=12) (actual time=6,848.491..6,890.302 rows=141,798 loops=1)

172. 226.677 6,624.873 ↓ 1.0 627,360 1

Append (cost=62,363.83..219,692.79 rows=621,384 width=12) (actual time=2,092.417..6,624.873 rows=627,360 loops=1)

173. 152.320 2,398.814 ↓ 1.0 384,579 1

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

174. 362.532 2,246.494 ↓ 1.0 384,579 1

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

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

  • Filter: ((contract_date >= '2016-01-01'::date) AND (contract_date <= CURRENT_DATE))
  • Rows Removed by Filter: 873
176. 93.376 3,999.382 ↓ 1.0 242,781 1

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

177. 212.086 3,906.006 ↓ 1.0 242,781 1

HashAggregate (cost=144,901.27..147,301.04 rows=239,976 width=8) (actual time=3,817.615..3,906.006 rows=242,781 loops=1)

  • Group Key: ls_9.loan_id, ls_9.contract_date
178. 183.193 3,693.920 ↓ 1.0 242,781 1

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

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

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

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

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

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

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

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

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

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

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

185. 39.249 3,150.015 ↓ 0.0 0 1

Nested Loop (cost=74,586.74..233,940.37 rows=59,845 width=112) (actual time=3,150.015..3,150.015 rows=0 loops=1)

186. 57.423 2,716.764 ↓ 1.2 65,667 1

Hash Join (cost=74,586.31..183,196.36 rows=56,040 width=24) (actual time=1,334.221..2,716.764 rows=65,667 loops=1)

  • Hash Cond: (p_2.entity_id = lse_15.loan_id)
187. 936.110 2,397.665 ↓ 1.2 65,667 1

Hash Left Join (cost=53,593.64..161,433.15 rows=56,040 width=16) (actual time=1,070.460..2,397.665 rows=65,667 loops=1)

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

Seq Scan on payment_entity p_2 (cost=0.00..103,672.24 rows=1,587,524 width=16) (actual time=0.005..401.352 rows=1,587,579 loops=1)

189. 605.762 1,060.203 ↑ 1.0 1,577,543 1

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 93,471kB
190. 454.441 454.441 ↑ 1.0 1,577,543 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..454.441 rows=1,577,543 loops=1)

191. 125.278 261.676 ↓ 1.0 385,452 1

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

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

Seq Scan on loan_settings_entity lse_15 (cost=0.00..16,185.63 rows=384,563 width=16) (actual time=0.012..136.398 rows=385,452 loops=1)

193. 394.002 394.002 ↓ 0.0 0 65,667

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

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

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

  • Hash Cond: (ach_1.loan_id = ls_10.loan_id)
195. 507.202 507.202 ↑ 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..507.202 rows=232,252 loops=1)

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

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,828kB
197. 223.760 491.132 ↑ 1.0 142,251 1

Hash Join (cost=19,900.01..80,347.94 rows=143,260 width=24) (actual time=151.962..491.132 rows=142,251 loops=1)

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

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

199. 46.463 150.887 ↑ 1.0 142,251 1

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

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

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

  • Filter: ((loan_status_id <> ALL ('{4,5}'::bigint[])) AND (loan_sub_status_id <> 41))
  • Rows Removed by Filter: 243,201
201. 0.162 10,445.952 ↓ 211.0 211 1

Subquery Scan on *SELECT* 24 (cost=95,472.70..102,254.54 rows=1 width=116) (actual time=2,253.722..10,445.952 rows=211 loops=1)

202. 0.503 10,445.790 ↓ 211.0 211 1

Nested Loop (cost=95,472.70..102,254.53 rows=1 width=110) (actual time=2,253.720..10,445.790 rows=211 loops=1)

203. 376.797 10,443.177 ↓ 211.0 211 1

Nested Loop (cost=95,472.28..102,253.76 rows=1 width=38) (actual time=2,253.694..10,443.177 rows=211 loops=1)

204. 759.121 2,337.130 ↓ 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,705.011..2,337.130 rows=1,545,850 loops=1)

  • Hash Cond: (rr_3.originalachrecord_id = fr_3.achfilerecord_id)
  • Filter: (rr_3.returnfilerecord_id IS NULL)
  • Rows Removed by Filter: 219,524
205. 84.992 84.992 ↑ 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.008..84.992 rows=219,542 loops=1)

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

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

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

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

  • Filter: (product = 'BALANCE_CREDIT'::text)
  • Rows Removed by Filter: 1,790
208. 7,729.250 7,729.250 ↓ 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.005..0.005 rows=0 loops=1,545,850)

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

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_17 (cost=0.42..0.75 rows=1 width=16) (actual time=0.009..0.010 rows=1 loops=211)

  • Index Cond: (loan_id = p_3.entity_id)
  • Filter: (loan_id <> 58239)
210. 0.003 1,949.353 ↑ 558.0 1 1

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

211. 3.314 1,949.350 ↑ 558.0 1 1

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

  • Hash Cond: (fr_4.achfilerecord_id = rr_4.originalachrecord_id)
212. 4.606 1,799.530 ↓ 4.2 2,682 1

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

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

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

214. 66.059 66.059 ↓ 2.6 2,815 1

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

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

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

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

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

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

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

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

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

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

220. 0.543 248.010 ↓ 0.0 0 1

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

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

222. 0.001 241.573 ↓ 0.0 0 1

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

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

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

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

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

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

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

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

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

227. 0.000 0.000 ↓ 0.0 0

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

228. 0.001 3,810.050 ↓ 0.0 0 1

Subquery Scan on *SELECT* 27 (cost=53,595.76..221,261.72 rows=1 width=116) (actual time=3,810.050..3,810.050 rows=0 loops=1)

229. 23.083 3,810.049 ↓ 0.0 0 1

Nested Loop (cost=53,595.76..221,261.71 rows=1 width=112) (actual time=3,810.049..3,810.049 rows=0 loops=1)

  • Join Filter: (lc.customer_id = en.customer_id)
  • Rows Removed by Join Filter: 114,441
230. 0.010 2,656.308 ↓ 3.0 3 1

Nested Loop (cost=53,595.76..183,880.99 rows=1 width=24) (actual time=2,542.337..2,656.308 rows=3 loops=1)

231. 0.011 2,654.279 ↓ 3.0 3 1

Nested Loop (cost=53,595.33..183,880.31 rows=1 width=20) (actual time=2,541.733..2,654.279 rows=3 loops=1)

232. 0.010 2,654.223 ↓ 3.0 3 1

Nested Loop (cost=53,594.91..183,879.80 rows=1 width=24) (actual time=2,541.707..2,654.223 rows=3 loops=1)

233. 0.009 2,648.789 ↓ 3.0 3 1

Nested Loop (cost=53,594.49..183,879.31 rows=1 width=16) (actual time=2,540.608..2,648.789 rows=3 loops=1)

234. 0.014 2,648.207 ↓ 3.0 3 1

Nested Loop (cost=53,594.07..183,878.86 rows=1 width=12) (actual time=2,540.589..2,648.207 rows=3 loops=1)

235. 582.538 2,642.523 ↓ 3.0 3 1

Hash Left Join (cost=53,593.64..183,878.38 rows=1 width=8) (actual time=2,539.225..2,642.523 rows=3 loops=1)

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

Seq Scan on payment_entity p_5 (cost=0.00..127,485.10 rows=1,066,524 width=12) (actual time=0.151..1,015.544 rows=1,225,645 loops=1)

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

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

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 93,471kB
238. 453.824 453.824 ↑ 1.0 1,577,543 1

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

239. 5.670 5.670 ↑ 1.0 1 3

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

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

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.190..0.191 rows=1 loops=3)

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

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

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

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.014..0.015 rows=1 loops=3)

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

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

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

Subquery Scan on en (cost=0.00..34,896.17 rows=198,763 width=4) (actual time=340.362..376.886 rows=38,147 loops=3)

245. 316.287 1,086.378 ↑ 5.2 38,147 3

HashSetOp Except (cost=0.00..32,908.54 rows=198,763 width=8) (actual time=340.361..362.126 rows=38,147 loops=3)

246. 222.819 770.091 ↓ 1.1 610,101 1

Append (cost=0.00..31,532.20 rows=550,538 width=8) (actual time=1.182..770.091 rows=610,101 loops=1)

247. 146.508 319.890 ↑ 1.0 376,774 1

Subquery Scan on *SELECT* 1_1 (cost=0.00..16,339.41 rows=376,774 width=8) (actual time=1.182..319.890 rows=376,774 loops=1)

248. 173.382 173.382 ↑ 1.0 376,774 1

Seq Scan on payment_account_entity (cost=0.00..12,571.67 rows=376,774 width=4) (actual time=1.181..173.382 rows=376,774 loops=1)

  • Filter: ((entity_type)::text = 'Entity.Customer'::text)
249. 93.359 227.382 ↓ 1.3 233,327 1

Subquery Scan on *SELECT* 2_1 (cost=0.42..15,192.78 rows=173,764 width=8) (actual time=0.621..227.382 rows=233,327 loops=1)

250. 134.023 134.023 ↓ 1.3 233,327 1

Index Scan using payment_account_entity_lastupdated on payment_account_entity payment_account_entity_1 (cost=0.42..13,455.15 rows=173,764 width=4) (actual time=0.620..134.023 rows=233,327 loops=1)

  • Filter: (active = 1)
  • Rows Removed by Filter: 38,229
251. 0.001 1,070.151 ↓ 0.0 0 1

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

  • Hash Cond: (lse_21.loan_status_id = s.id)
252. 106.646 1,070.150 ↓ 0.0 0 1

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

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

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

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

Seq Scan on loan_settings_entity lse_21 (cost=0.00..17,147.04 rows=321,604 width=32) (actual time=0.008..152.101 rows=321,505 loops=1)

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

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

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

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

  • Filter: (title = ANY ('{""Full Term Paid"",""Early Payoff"",Refinanced}'::text[]))
  • Rows Removed by Filter: 53
257. 711.528 711.528 ↑ 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.008..0.008 rows=1 loops=88,941)

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

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

259. 0.000 0.000 ↓ 0.0 0

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

260. 76.059 4,782.184 ↓ 0.0 0 1

Nested Loop (cost=21,954.49..118,916.76 rows=1 width=116) (actual time=4,782.184..4,782.184 rows=0 loops=1)

261. 214.932 3,521.913 ↓ 296,053.0 296,053 1

Hash Right Join (cost=21,954.06..118,916.12 rows=1 width=24) (actual time=3,383.288..3,521.913 rows=296,053 loops=1)

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

Seq Scan on loan_document_entity f (cost=0.00..94,312.03 rows=192,730 width=8) (actual time=30.556..3,028.030 rows=159,813 loops=1)

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

Hash (cost=17,147.04..17,147.04 rows=384,562 width=24) (actual time=278.950..278.951 rows=385,451 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,176kB
264. 146.706 146.706 ↓ 1.0 385,451 1

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

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

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

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

Nested Loop Left Join (cost=20,993.09..133,299.98 rows=26,931 width=116) (actual time=757.079..757.079 rows=0 loops=1)

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

Hash Join (cost=20,992.67..110,515.47 rows=35,030 width=24) (actual time=274.297..678.945 rows=27,763 loops=1)

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

Seq Scan on loan_document_entity f_1 (cost=0.00..89,041.14 rows=35,030 width=4) (actual time=5.815..391.566 rows=27,763 loops=1)

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

Hash (cost=16,185.63..16,185.63 rows=384,563 width=24) (actual time=266.359..266.360 rows=385,452 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 25,176kB
270. 137.025 137.025 ↓ 1.0 385,452 1

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

271. 55.526 55.526 ↑ 1.0 1 27,763

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

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

Hash Join (cost=23,335.58..42,793.73 rows=81,247 width=116) (actual time=481.496..481.496 rows=0 loops=1)

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

Hash Join (cost=23,294.08..41,635.08 rows=81,247 width=16) (actual time=480.728..480.729 rows=0 loops=1)

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

Hash Join (cost=23,291.62..40,486.74 rows=88,911 width=24) (actual time=212.136..459.432 rows=89,318 loops=1)

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

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

276. 28.971 211.559 ↑ 1.0 89,318 1

Hash (cost=22,174.56..22,174.56 rows=89,365 width=4) (actual time=211.559..211.559 rows=89,318 loops=1)

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

Seq Scan on loan_settings_custom_fields cf_7 (cost=0.00..22,174.56 rows=89,365 width=4) (actual time=0.010..182.588 rows=89,318 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
279. 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
280. 0.008 0.687 ↑ 155.6 9 1

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

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

282. 0.002 8,311.876 ↓ 0.0 0 1

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

  • Filter: (ne.id IS NULL)
283. 174.454 8,311.874 ↓ 0.0 0 1

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

284. 305.269 3,966.305 ↓ 320,855.0 320,855 1

Nested Loop (cost=21,167.51..21,247.83 rows=1 width=24) (actual time=287.318..3,966.305 rows=320,855 loops=1)

285. 176.272 452.546 ↓ 320,849.0 320,849 1

Hash Right Join (cost=21,167.09..21,247.34 rows=1 width=24) (actual time=285.323..452.546 rows=320,849 loops=1)

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

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

  • Filter: (note_data ~~* '%Paid Off Notice%'::text)
287. 108.702 269.514 ↑ 1.0 321,505 1

Hash (cost=17,147.04..17,147.04 rows=321,604 width=24) (actual time=269.513..269.514 rows=321,505 loops=1)

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

Seq Scan on loan_settings_entity lse_25 (cost=0.00..17,147.04 rows=321,604 width=24) (actual time=0.008..160.812 rows=321,505 loops=1)

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

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.009..0.010 rows=1 loops=320,849)

  • Index Cond: (loan_id = lse_25.loan_id)
  • Heap Fetches: 72,741
290. 4,171.115 4,171.115 ↓ 0.0 0 320,855

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

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

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

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

Nested Loop (cost=38,676.60..304,954.54 rows=3,822 width=68) (actual time=146,090.016..146,090.016 rows=0 loops=1)

  • Join Filter: (cfe1.custom_field_value <> cfe2.custom_field_value)
  • Rows Removed by Join Filter: 8,999
293. 49.186 145,644.164 ↓ 1.3 8,999 1

Nested Loop (cost=38,676.17..260,008.07 rows=7,099 width=40) (actual time=248.829..145,644.164 rows=8,999 loops=1)

294. 157.471 123,915.070 ↓ 1.8 23,514 1

Merge Join (cost=38,675.75..178,631.85 rows=12,863 width=28) (actual time=246.503..123,915.070 rows=23,514 loops=1)

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

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

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

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

  • Filter: (loan_status_id = ANY ('{4,5}'::bigint[]))
  • Rows Removed by Filter: 145,066
297. 123,443.525 123,443.525 ↓ 4.1 83,894 1

Index Scan using custom_field__entity_entity_id_purchase_date_indx on custom_field__entity cfe3 (cost=0.42..172,025.97 rows=20,539 width=4) (actual time=7.626..123,443.525 rows=83,894 loops=1)

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

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

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

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

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

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

301. 100.170 41,651.280 ↓ 0.0 0 1

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

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

CTE max_date_qry

303. 0.004 0.545 ↑ 1.0 1 1

Result (cost=0.72..0.73 rows=1 width=4) (actual time=0.544..0.545 rows=1 loops=1)

304.          

Initplan (for Result)

305. 0.004 0.541 ↑ 1.0 1 1

Limit (cost=0.57..0.72 rows=1 width=4) (actual time=0.539..0.541 rows=1 loops=1)

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

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

Nested Loop (cost=17,364.93..1,145,807.36 rows=2,654 width=26) (actual time=265.688..41,514.052 rows=191,401 loops=1)

308. 0.551 0.551 ↑ 1.0 1 1

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

309. 41,131.517 41,389.364 ↓ 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=265.133..41,389.364 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.007 257.847 ↓ 0.0 0 1

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

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

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

  • Index Cond: (date = (max_date_qry.max_date - 1))
313. 11.007 37.058 ↓ 7.4 32,431 1

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

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,777kB
314. 26.051 26.051 ↓ 7.4 32,431 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=0.544..26.051 rows=32,431 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 108
315. 27,001.892 27,001.892 ↓ 2.0 2 1

CTE Scan on y (cost=665,908.07..665,908.13 rows=1 width=116) (actual time=27,001.478..27,001.892 rows=2 loops=1)

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

CTE payment_date

317. 12,977.152 25,410.908 ↑ 1.0 1,356,004 1

WindowAgg (cost=250,323.27..617,924.76 rows=1,361,487 width=24) (actual time=11,971.433..25,410.908 rows=1,356,004 loops=1)

318. 1,368.014 12,433.756 ↑ 1.0 1,356,004 1

Sort (cost=250,323.27..253,726.99 rows=1,361,487 width=12) (actual time=11,968.078..12,433.756 rows=1,356,004 loops=1)

  • Sort Key: payment_entity_3.entity_id, payment_entity_3.apply_date DESC
  • Sort Method: quicksort Memory: 112,715kB
319. 11,065.742 11,065.742 ↑ 1.0 1,356,004 1

Seq Scan on payment_entity payment_entity_3 (cost=0.00..111,609.86 rows=1,361,487 width=12) (actual time=6.031..11,065.742 rows=1,356,004 loops=1)

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

CTE y

321. 0.516 27,001.778 ↓ 119.5 239 1

GroupAggregate (cost=47,983.27..47,983.31 rows=2 width=28) (actual time=27,001.131..27,001.778 rows=239 loops=1)

  • Group Key: x_2.loan_id, x_2.days_to_clear, x_2.loan_status_id
322. 0.664 27,001.262 ↓ 367.0 734 1

Sort (cost=47,983.27..47,983.27 rows=2 width=24) (actual time=27,001.113..27,001.262 rows=734 loops=1)

  • Sort Key: x_2.loan_id, x_2.days_to_clear, x_2.loan_status_id
  • Sort Method: quicksort Memory: 82kB
323. 0.492 27,000.598 ↓ 367.0 734 1

Subquery Scan on x_2 (cost=17,161.21..47,983.26 rows=2 width=24) (actual time=19,162.572..27,000.598 rows=734 loops=1)

324. 10.491 27,000.106 ↓ 367.0 734 1

Nested Loop (cost=17,161.21..47,983.24 rows=2 width=32) (actual time=19,162.570..27,000.106 rows=734 loops=1)

  • Join Filter: (lse_41.loan_id = ls_13.loan_id)
325. 1.079 26,985.211 ↓ 367.0 734 1

Nested Loop (cost=17,160.79..47,974.16 rows=2 width=36) (actual time=19,162.524..26,985.211 rows=734 loops=1)

  • Join Filter: (lse_41.loan_id = lsa_7.loan_id)
326. 77.138 26,593.820 ↓ 18.5 351 1

Hash Join (cost=17,160.36..47,819.54 rows=19 width=28) (actual time=19,125.703..26,593.820 rows=351 loops=1)

  • Hash Cond: (pd.entity_id = lse_41.loan_id)
327. 26,465.185 26,465.185 ↓ 30.2 205,702 1

CTE Scan on payment_date pd (cost=0.00..30,633.46 rows=6,807 width=12) (actual time=11,971.437..26,465.185 rows=205,702 loops=1)

  • Filter: (row_number = 1)
  • Rows Removed by Filter: 1,150,302
328. 0.125 51.497 ↑ 3.0 351 1

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

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

Seq Scan on loan_settings_entity lse_41 (cost=0.00..17,147.04 rows=1,066 width=16) (actual time=41.447..51.372 rows=351 loops=1)

  • Filter: (loan_sub_status_id = 39)
  • Rows Removed by Filter: 385,101
330. 390.312 390.312 ↓ 2.0 2 351

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.683..1.112 rows=2 loops=351)

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

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

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

Subquery Scan on *SELECT* 36 (cost=0.87..60,542.77 rows=1,984 width=116) (actual time=930.543..930.543 rows=0 loops=1)

333. 12.560 930.542 ↓ 0.0 0 1

Nested Loop (cost=0.87..60,513.01 rows=1,984 width=108) (actual time=930.542..930.542 rows=0 loops=1)

334. 808.717 808.717 ↓ 7.2 21,853 1

Index Scan using custom_field__entity_last_updated on custom_field__entity cfe_3 (cost=0.44..37,855.97 rows=3,016 width=12) (actual time=27.472..808.717 rows=21,853 loops=1)

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

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

  • 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.051 1,016.546 ↓ 0.0 0 1

Hash Join (cost=23,418.72..40,613.84 rows=22,115 width=69) (actual time=1,016.546..1,016.546 rows=0 loops=1)

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

338. 0.001 1,016.489 ↓ 0.0 0 1

Hash (cost=23,140.88..23,140.88 rows=22,228 width=21) (actual time=1,016.489..1,016.489 rows=0 loops=1)

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

Seq Scan on loan_settings_custom_fields scf (cost=0.00..23,140.88 rows=22,228 width=21) (actual time=1,016.488..1,016.488 rows=0 loops=1)

  • Filter: (((pledge_date IS NULL) OR (pledge_date = ''::text)) AND (owner = 'SPV2'::text))
  • Rows Removed by Filter: 385,452
340. 0.117 751.224 ↓ 124.0 124 1

Result (cost=127.67..6,416.35 rows=1 width=87) (actual time=110.483..751.224 rows=124 loops=1)

  • One-Time Filter: (NOT (hashed SubPlan 6))
341. 0.343 750.649 ↓ 124.0 124 1

Nested Loop (cost=127.67..6,416.35 rows=1 width=87) (actual time=110.000..750.649 rows=124 loops=1)

342. 1.881 533.220 ↓ 194.0 194 1

Nested Loop (cost=125.63..6,406.46 rows=1 width=27) (actual time=26.418..533.220 rows=194 loops=1)

343. 4.673 190.856 ↓ 2,381.0 2,381 1

Nested Loop (cost=125.21..6,398.36 rows=1 width=32) (actual time=8.299..190.856 rows=2,381 loops=1)

  • Join Filter: (lse_29.loan_id = lset.loan_id)
344. 5.976 176.448 ↓ 3,245.0 3,245 1

Nested Loop (cost=124.79..6,390.55 rows=1 width=28) (actual time=8.286..176.448 rows=3,245 loops=1)

345. 16.792 49.696 ↓ 30,194.0 30,194 1

Merge Left Join (cost=124.49..6,382.88 rows=1 width=24) (actual time=6.509..49.696 rows=30,194 loops=1)

  • Merge Cond: (lse_29.loan_id = pe_5.entity_id)
  • Filter: (pe_5.id IS NULL)
  • Rows Removed by Filter: 2,268
346. 25.603 25.603 ↓ 7.4 32,431 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.013..25.603 rows=32,431 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 108
347. 1.821 7.301 ↓ 3.0 2,392 1

Sort (cost=124.20..126.17 rows=785 width=8) (actual time=6.491..7.301 rows=2,392 loops=1)

  • Sort Key: pe_5.entity_id
  • Sort Method: quicksort Memory: 209kB
348. 5.480 5.480 ↓ 3.0 2,392 1

Index Scan using payment_entity_apply_date on payment_entity pe_5 (cost=0.43..86.46 rows=785 width=8) (actual time=2.145..5.480 rows=2,392 loops=1)

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

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

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

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

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

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.143..0.143 rows=0 loops=2,381)

  • 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. 217.086 217.086 ↑ 1.0 1 194

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

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

SubPlan (for Result)

354. 0.458 0.458 ↑ 1.0 37 1

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

355. 0.007 15,198.114 ↑ 2,232.7 7 1

Result (cost=5,251.99..151,930.63 rows=15,629 width=87) (actual time=3,663.923..15,198.114 rows=7 loops=1)

356. 0.008 15,198.107 ↑ 2,232.7 7 1

Append (cost=5,251.99..151,774.34 rows=15,629 width=51) (actual time=3,663.921..15,198.107 rows=7 loops=1)

357. 108.371 14,555.857 ↑ 1,227.0 1 1

Hash Left Join (cost=5,251.99..84,088.89 rows=1,227 width=51) (actual time=3,663.920..14,555.857 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,745
358. 41.776 14,347.223 ↓ 7.9 9,726 1

Nested Loop Left Join (cost=2.80..78,734.09 rows=1,227 width=42) (actual time=7.069..14,347.223 rows=9,726 loops=1)

  • Join Filter: ((lsa_3.days_past_due <= 90) AND (pe_6.apply_date = dw_reporting_meta.text_to_date(lscf_1.called_due_notice_date)))
  • Rows Removed by Join Filter: 61,792
359. 15.096 13,770.517 ↓ 7.9 9,726 1

Nested Loop Left Join (cost=2.38..62,357.89 rows=1,227 width=38) (actual time=5.249..13,770.517 rows=9,726 loops=1)

360. 98.931 13,551.175 ↓ 7.9 9,726 1

Nested Loop Left Join (cost=1.95..52,226.55 rows=1,227 width=39) (actual time=5.223..13,551.175 rows=9,726 loops=1)

  • Join Filter: (NOT (alternatives: SubPlan 3 or hashed SubPlan 4))
  • Rows Removed by Join Filter: 51
361. 18.756 286.897 ↓ 7.9 9,726 1

Nested Loop (cost=1.13..24,486.40 rows=1,227 width=31) (actual time=3.108..286.897 rows=9,726 loops=1)

362. 19.351 170.881 ↓ 7.9 9,726 1

Nested Loop (cost=0.71..14,674.08 rows=1,227 width=28) (actual time=2.321..170.881 rows=9,726 loops=1)

363. 54.270 54.270 ↓ 7.9 9,726 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=2.309..54.270 rows=9,726 loops=1)

  • Filter: (loan_sub_status_id = 55)
  • Rows Removed by Filter: 43,772
364. 97.260 97.260 ↑ 1.0 1 9,726

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.010..0.010 rows=1 loops=9,726)

  • Index Cond: (loan_id = lse_30.loan_id)
  • Heap Fetches: 33
365. 97.260 97.260 ↑ 1.0 1 9,726

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

  • Index Cond: (loan_settings_id = lse_30.id)
366. 13,159.278 13,159.278 ↓ 0.0 0 9,726

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.353..1.353 rows=0 loops=9,726)

  • 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. 6.069 6.069 ↑ 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.119..0.119 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..114,449.96 rows=1,361,781 width=8) (never executed)

  • Filter: ((reverse_reason IS NULL) AND (deleted = 0))
370. 204.246 204.246 ↓ 0.0 0 9,726

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

  • 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. 534.930 534.930 ↑ 1.7 6 9,726

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

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

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,729kB
373. 70.614 70.614 ↑ 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.593..70.614 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.078 256.035 ↑ 1,233.0 6 1

Hash Join (cost=4,326.06..26,905.52 rows=7,398 width=51) (actual time=199.511..256.035 rows=6 loops=1)

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

Seq Scan on loan_settings_custom_fields cf_9 (cost=0.00..22,174.56 rows=88,244 width=7) (actual time=0.007..195.514 rows=88,707 loops=1)

  • Filter: (called_due_notice_date <> ''::text)
  • Rows Removed by Filter: 296,745
378. 11.301 32.443 ↓ 1.0 32,431 1

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,030kB
379. 21.142 21.142 ↓ 1.0 32,431 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.010..21.142 rows=32,431 loops=1)

  • Filter: (loan_sub_status_id = 20)
  • Rows Removed by Filter: 108
380. 77.869 386.207 ↓ 0.0 0 1

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

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

Seq Scan on loan_settings_custom_fields cf_10 (cost=0.00..22,174.56 rows=298,281 width=7) (actual time=0.007..248.445 rows=296,745 loops=1)

  • Filter: (called_due_notice_date = ''::text)
  • Rows Removed by Filter: 88,707
382. 3.385 59.893 ↓ 1.1 9,726 1

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

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

Seq Scan on loan_settings_entity lse_32 (cost=0.00..17,147.04 rows=9,076 width=24) (actual time=1.411..56.508 rows=9,726 loops=1)

  • Filter: (loan_sub_status_id = 55)
  • Rows Removed by Filter: 375,726
384. 0.178 154,105.424 ↑ 76.4 215 1

Subquery Scan on x_1 (cost=0.57..866,087.59 rows=16,417 width=116) (actual time=145,321.373..154,105.424 rows=215 loops=1)

385. 0.138 154,105.246 ↑ 76.4 215 1

Append (cost=0.57..865,923.42 rows=16,417 width=56) (actual time=145,321.371..154,105.246 rows=215 loops=1)

386. 0.001 145,174.507 ↓ 0.0 0 1

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

387. 185.140 145,174.506 ↓ 0.0 0 1

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

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

389. 144,898.712 144,898.712 ↓ 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.528..1.528 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 160.088 ↑ 1.0 1 1

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

391. 0.018 160.073 ↑ 1.0 1 1

Nested Loop Left Join (cost=6,299.15..21,895.73 rows=1 width=36) (actual time=146.847..160.073 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. 3.227 134.015 ↑ 63.7 3 1

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

  • Join Filter: (lse_33.loan_id = lsa_5.loan_id)
393. 28.076 117.724 ↓ 3.0 3,266 1

Hash Join (cost=6,298.15..10,726.93 rows=1,081 width=36) (actual time=96.807..117.724 rows=3,266 loops=1)

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

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
395. 11.215 35.046 ↓ 7.4 32,431 1

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

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,030kB
396. 23.831 23.831 ↓ 7.4 32,431 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.016..23.831 rows=32,431 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 108
397. 13.064 13.064 ↓ 0.0 0 3,266

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.004..0.004 rows=0 loops=3,266)

  • 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. 26.040 26.040 ↓ 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.822..8.680 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.010 0.010 ↑ 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.010..0.010 rows=1 loops=1)

  • Index Cond: (loan_id = lse_33.loan_id)
  • Heap Fetches: 0
400. 10.566 2,374.284 ↓ 212.0 212 1

Nested Loop (cost=4,060.33..8,495.68 rows=1 width=56) (actual time=180.748..2,374.284 rows=212 loops=1)

401. 39.935 788.700 ↓ 29,167.0 29,167 1

Nested Loop (cost=4,059.90..8,490.14 rows=1 width=32) (actual time=91.375..788.700 rows=29,167 loops=1)

402. 41.933 107.091 ↓ 29,167.0 29,167 1

Hash Right Join (cost=4,059.48..8,488.26 rows=1 width=32) (actual time=91.358..107.091 rows=29,167 loops=1)

  • Hash Cond: (rtc_4.parent_id = lse_34.loan_id)
  • Filter: (rtc_4.id IS NULL)
  • Rows Removed by Filter: 3,266
403. 29.882 29.882 ↑ 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.005..29.882 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
404. 11.833 35.276 ↓ 7.4 32,431 1

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

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,283kB
405. 23.443 23.443 ↓ 7.4 32,431 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.009..23.443 rows=32,431 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 108
406. 641.674 641.674 ↑ 1.0 1 29,167

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

  • Index Cond: (loan_id = lse_34.loan_id)
  • Heap Fetches: 4,310
407. 1,575.018 1,575.018 ↓ 0.0 0 29,167

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.054..0.054 rows=0 loops=29,167)

  • 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 105.222 ↓ 0.0 0 1

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

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

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

  • Join Filter: (lse_35.loan_id = rtc2.parent_id)
410. 27.841 94.060 ↓ 3.0 3,266 1

Hash Join (cost=4,059.48..8,488.26 rows=1,081 width=36) (actual time=73.244..94.060 rows=3,266 loops=1)

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

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
412. 11.073 35.106 ↓ 7.4 32,431 1

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

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,030kB
413. 24.033 24.033 ↓ 7.4 32,431 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.011..24.033 rows=32,431 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 108
414. 9.798 9.798 ↓ 0.0 0 3,266

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

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

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

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

Nested Loop (cost=4,060.33..11,045.12 rows=1 width=56) (actual time=3,462.871..6,230.677 rows=2 loops=1)

  • Join Filter: ((pe_8.lastupdated < rtc_6.lastupdated) AND ((rtc_6.lastupdated)::date = pe_8.apply_date))
  • Rows Removed by Join Filter: 19,808
417. 7.328 134.120 ↓ 3.0 3,266 1

Nested Loop (cost=4,059.90..9,137.78 rows=1,081 width=40) (actual time=71.600..134.120 rows=3,266 loops=1)

  • Join Filter: (lse_36.loan_id = lset_5.loan_id)
418. 33.934 100.664 ↓ 3.0 3,266 1

Hash Join (cost=4,059.48..8,488.26 rows=1,081 width=36) (actual time=71.588..100.664 rows=3,266 loops=1)

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

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

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
420. 11.055 34.081 ↓ 7.4 32,431 1

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

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,030kB
421. 23.026 23.026 ↓ 7.4 32,431 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.011..23.026 rows=32,431 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 108
422. 26.128 26.128 ↑ 1.0 1 3,266

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.008..0.008 rows=1 loops=3,266)

  • Index Cond: (loan_id = rtc_6.parent_id)
  • Heap Fetches: 5
423. 6,081.292 6,081.292 ↑ 1.7 6 3,266

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

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

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

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

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

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

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

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

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

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

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

Seq Scan on bank_holidays (cost=0.00..1.37 rows=37 width=4) (actual time=0.014..0.035 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.042 9,656.760 ↓ 11.0 11 1

Result (cost=10,877.56..145,906.74 rows=1 width=70) (actual time=758.366..9,656.760 rows=11 loops=1)

  • One-Time Filter: (NOT (hashed SubPlan 2))
434. 0.340 9,656.703 ↓ 11.0 11 1

Nested Loop Anti Join (cost=10,877.56..145,906.74 rows=1 width=70) (actual time=758.324..9,656.703 rows=11 loops=1)

435. 0.306 9,655.247 ↓ 124.0 124 1

Nested Loop Anti Join (cost=10,875.67..145,897.34 rows=1 width=43) (actual time=507.206..9,655.247 rows=124 loops=1)

  • Join Filter: (note_entity.id < rtc_8.id)
436. 8.630 7,138.857 ↓ 124.0 124 1

Nested Loop (cost=10,875.24..145,831.92 rows=1 width=43) (actual time=479.538..7,138.857 rows=124 loops=1)

437. 15.012 1,041.944 ↓ 7,639.0 7,639 1

Nested Loop (cost=10,874.67..145,823.64 rows=1 width=47) (actual time=366.648..1,041.944 rows=7,639 loops=1)

438. 575.916 1,004.015 ↓ 7,639.0 7,639 1

Hash Join (cost=10,874.25..145,819.34 rows=1 width=43) (actual time=366.616..1,004.015 rows=7,639 loops=1)

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

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

440. 11.408 118.261 ↓ 26,951.0 26,951 1

Hash (cost=10,874.23..10,874.23 rows=1 width=43) (actual time=118.261..118.261 rows=26,951 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,730kB
441. 34.196 106.853 ↓ 26,951.0 26,951 1

Hash Right Join (cost=6,450.85..10,874.23 rows=1 width=43) (actual time=99.097..106.853 rows=26,951 loops=1)

  • Hash Cond: (rtc_8.parent_id = lse_38.loan_id)
  • Filter: (rtc_8.id IS NULL)
  • Rows Removed by Filter: 3,245
442. 29.924 29.924 ↑ 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.005..29.924 rows=94,829 loops=1)

  • Filter: ((parent_type)::text = 'Entity.Loan'::text)
443. 10.235 42.733 ↓ 13.8 30,194 1

Hash (cost=6,423.45..6,423.45 rows=2,192 width=24) (actual time=42.733..42.733 rows=30,194 loops=1)

  • Buckets: 32,768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,908kB
444. 27.880 32.498 ↓ 13.8 30,194 1

Index Scan using loan_settings_entity_loan_id_active_cond on loan_settings_entity lse_38 (cost=88.71..6,423.45 rows=2,192 width=24) (actual time=5.663..32.498 rows=30,194 loops=1)

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

SubPlan (for Index Scan)

446. 4.618 4.618 ↓ 3.0 2,392 1

Index Scan using payment_entity_apply_date on payment_entity (cost=0.43..86.46 rows=785 width=4) (actual time=0.032..4.618 rows=2,392 loops=1)

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

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

  • Index Cond: (loan_id = lse_38.loan_id)
  • Heap Fetches: 18
448. 6,088.283 6,088.283 ↓ 0.0 0 7,639

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

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

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

  • 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: 28
450. 1.116 1.116 ↑ 1.0 1 124

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

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

SubPlan (for Result)

452. 0.015 0.015 ↑ 1.0 37 1

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

453. 0.001 29,977.839 ↓ 0.0 0 1

Subquery Scan on *SELECT* 42 (cost=2.28..19,610.88 rows=25 width=151) (actual time=29,977.839..29,977.839 rows=0 loops=1)

454. 23.547 29,977.838 ↓ 0.0 0 1

Nested Loop Anti Join (cost=2.28..19,610.50 rows=25 width=146) (actual time=29,977.838..29,977.838 rows=0 loops=1)

455. 22.431 9,913.529 ↓ 629.5 17,626 1

Nested Loop (cost=1.85..19,384.45 rows=28 width=110) (actual time=11.348..9,913.529 rows=17,626 loops=1)

456. 18.448 9,379.915 ↓ 440.7 17,627 1

Nested Loop Semi Join (cost=1.42..19,348.92 rows=40 width=130) (actual time=7.290..9,379.915 rows=17,627 loops=1)

457. 29.877 6,658.875 ↓ 51.9 19,584 1

Nested Loop (cost=1.13..18,370.93 rows=377 width=126) (actual time=1.793..6,658.875 rows=19,584 loops=1)

458. 44.725 6,472.326 ↓ 51.9 19,584 1

Nested Loop (cost=0.71..17,909.65 rows=377 width=110) (actual time=0.161..6,472.326 rows=19,584 loops=1)

459. 46.226 46.226 ↓ 13.3 27,625 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.027..46.226 rows=27,625 loops=1)

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20) AND (autopay_enabled = 1))
  • Rows Removed by Filter: 4,914
460. 6,381.375 6,381.375 ↑ 1.0 1 27,625

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.231..0.231 rows=1 loops=27,625)

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

Index Scan using loan_settings_entity_loan_id on loan_settings_entity lse_39 (cost=0.42..1.21 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=19,584)

  • Index Cond: (loan_id = setup.loan_id)
462. 2,702.592 2,702.592 ↑ 1.0 1 19,584

Index Scan using loan_autopay_entity_loan_id_apply_date_pending on loan_autopay_entity lae_2 (cost=0.29..2.59 rows=1 width=4) (actual time=0.138..0.138 rows=1 loops=19,584)

  • Index Cond: (loan_id = lse_39.loan_id)
  • Filter: ((deleted = 0) AND (type = 'autopay.type.recurring'::bpchar))
463. 511.183 511.183 ↑ 1.0 1 17,627

Index Scan using loan__portfolio_loan_id on loan__portfolio port (cost=0.43..0.88 rows=1 width=4) (actual time=0.026..0.029 rows=1 loops=17,627)

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

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

  • Index Cond: (loan_id = setup.loan_id)
  • Filter: ((name = 'Autopay - Final Payment'::text) AND (deleted = 0))
  • Rows Removed by Filter: 4
465. 0.002 16,525.447 ↓ 0.0 0 1

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

466. 17.102 16,525.445 ↓ 0.0 0 1

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

  • Filter: (ne_1.id IS NULL)
  • Rows Removed by Filter: 18,677
467. 72.583 72.583 ↓ 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=2.131..72.583 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. 16,435.760 16,435.760 ↑ 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.857..0.880 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.080 22.419 ↓ 0.0 0 1

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

470. 16.723 16.723 ↑ 9.9 108 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.453..16.723 rows=108 loops=1)

  • Filter: (loan_sub_status_id = 14)
  • Rows Removed by Filter: 32,431
471. 5.616 5.616 ↓ 0.0 0 108

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.052..0.052 rows=0 loops=108)

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