explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bF8m

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.511 383,380.747 ↑ 25,986.6 794 1

Append (cost=19,579.63..7,172,416.44 rows=20,633,391 width=116) (actual time=647.780..383,380.747 rows=794 loops=1)

2. 0.143 1.273 ↓ 0.0 0 1

Hash Join (cost=19,579.63..36,946.03 rows=68,513 width=92) (actual time=1.273..1.273 rows=0 loops=1)

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

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

4. 0.001 1.124 ↓ 0.0 0 1

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

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

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

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

7. 0.553 0.553 ↓ 0.0 0 1

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

8. 0.566 0.566 ↓ 0.0 0 1

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

9. 44.227 177.137 ↓ 0.0 0 1

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

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

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

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

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

12. 19.070 56.386 ↓ 1.0 53,499 1

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

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

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

14. 106.322 469.620 ↑ 86.7 3 1

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

15. 90.660 90.660 ↓ 70.7 136,319 1

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

16. 272.638 272.638 ↓ 0.0 0 136,319

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

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

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

18. 0.005 1.860 ↑ 1,923.0 1 1

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

19. 1.843 1.843 ↑ 1,927.0 1 1

Index Scan using loan_settings_custom_fields_loan_settings_id_cond4 on loan_settings_custom_fields cf_3 (cost=0.25..2,117.57 rows=1,927 width=15) (actual time=1.253..1.843 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.011..0.012 rows=1 loops=1)

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

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

  • Index Cond: (id = lse_2.loan_id)
  • Filter: (deleted = 0)
  • Rows Removed by Filter: 1
22. 0.002 9,605.049 ↓ 0.0 0 1

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

23. 93.614 9,605.047 ↓ 0.0 0 1

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

24. 73.410 963.540 ↓ 16.6 161,281 1

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

25. 0.034 1.230 ↓ 4.0 4 1

Hash Join (cost=1.19..3.11 rows=1 width=21) (actual time=1.187..1.230 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.621 0.621 ↑ 1.0 4 1

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

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

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

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

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

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

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

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

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

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

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

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

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

33. 0.001 211.004 ↓ 0.0 0 1

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

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

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

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

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

36. 24.869 181.011 ↓ 5.3 11,955 1

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

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

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

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

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

39. 0.006 1.310 ↑ 1.0 4 1

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

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

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

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

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

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

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

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

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

44. 0.000 0.000 ↓ 0.0 0

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

45. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (loan_id = cie.entity_id)
46. 0.002 659.895 ↓ 0.0 0 1

Subquery Scan on x (cost=36,132.65..36,452.96 rows=7,118 width=116) (actual time=659.895..659.895 rows=0 loops=1)

47. 0.001 659.893 ↓ 0.0 0 1

Unique (cost=36,132.65..36,257.21 rows=7,118 width=82) (actual time=659.893..659.893 rows=0 loops=1)

48. 0.021 659.892 ↓ 0.0 0 1

Sort (cost=36,132.65..36,150.44 rows=7,118 width=82) (actual time=659.892..659.892 rows=0 loops=1)

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

Hash Left Join (cost=21,021.85..35,677.19 rows=7,118 width=82) (actual time=659.870..659.871 rows=0 loops=1)

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

Nested Loop (cost=20,997.96..35,474.38 rows=14,237 width=115) (actual time=277.461..641.866 rows=13,389 loops=1)

51. 15.364 342.601 ↓ 1.2 11,112 1

Nested Loop (cost=20,997.53..30,443.35 rows=9,283 width=115) (actual time=276.286..342.601 rows=11,112 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,804
52. 9.502 301.405 ↓ 1.4 12,916 1

Hash Join (cost=20,997.11..24,216.62 rows=9,330 width=45) (actual time=276.259..301.405 rows=12,916 loops=1)

  • Hash Cond: (lp_1.loan_id = ls_1.loan_id)
53. 8.180 16.355 ↓ 1.3 12,916 1

Hash Join (cost=4.44..3,094.17 rows=9,732 width=21) (actual time=0.136..16.355 rows=12,916 loops=1)

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

Index Scan using loan__portfolio_lastupdated on loan__portfolio lp_1 (cost=0.43..2,941.41 rows=13,903 width=8) (actual time=0.039..8.089 rows=14,720 loops=1)

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

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

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

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

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

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

58. 0.007 0.017 ↑ 1.0 7 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
59. 0.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. 139.345 275.548 ↓ 1.0 385,366 1

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

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

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

62. 25.832 25.832 ↑ 1.0 1 12,916

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=12,916)

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

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.025..0.026 rows=1 loops=11,112)

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

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

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

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

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

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

67. 1,951.074 8,417.075 ↓ 0.0 0 1

GroupAggregate (cost=412,842.77..502,927.31 rows=2,573,844 width=121) (actual time=8,417.075..8,417.075 rows=0 loops=1)

  • Group Key: lp_2.loan_id, pce_2.title, lse_4.loan_status_id
  • Filter: (count(*) > 1)
  • Rows Removed by Filter: 2,684,913
68. 2,692.959 6,466.001 ↓ 1.0 2,684,913 1

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

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

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

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

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

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

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

72. 135.186 271.761 ↓ 1.0 385,366 1

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

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

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

74. 0.024 0.097 ↑ 1.0 59 1

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

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

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

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

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

77. 0.007 0.013 ↑ 1.0 10 1

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

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

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

79. 0.001 69.238 ↓ 0.0 0 1

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

80. 1.215 69.237 ↓ 0.0 0 1

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

81. 2.318 64.226 ↓ 3.5 1,898 1

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

82. 9.610 58.102 ↓ 3.4 1,903 1

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

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

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

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

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

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

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

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

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

87. 1.019 15.079 ↓ 0.0 0 1

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

88. 2.077 10.264 ↓ 2.1 1,898 1

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

89. 3.508 4.381 ↓ 2.1 1,903 1

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

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

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

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

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

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

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

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

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

94. 0.002 893.775 ↓ 0.0 0 1

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

95. 0.001 893.773 ↓ 0.0 0 1

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

96. 893.772 893.772 ↓ 0.0 0 1

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

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

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

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

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

  • Index Cond: (loan_id = la.loan_id)
99. 0.001 0.013 ↓ 0.0 0 1

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

100. 0.002 0.012 ↓ 0.0 0 1

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

101. 0.010 0.010 ↓ 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.010..0.010 rows=0 loops=1)

102. 0.000 0.000 ↓ 0.0 0

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

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

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

104. 0.001 0.053 ↓ 0.0 0 1

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

105. 0.052 0.052 ↓ 0.0 0 1

Index Scan using loan_autopay_entity_apply_date on loan_autopay_entity lae_1 (cost=0.43..47.02 rows=3 width=8) (actual time=0.052..0.052 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 1,022.986 ↓ 0.0 0 1

Subquery Scan on cte (cost=131,419.14..131,444.70 rows=71 width=116) (actual time=1,022.985..1,022.986 rows=0 loops=1)

108. 1.032 1,022.984 ↓ 0.0 0 1

GroupAggregate (cost=131,419.14..131,441.68 rows=71 width=90) (actual time=1,022.984..1,022.984 rows=0 loops=1)

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

Sort (cost=131,419.14..131,419.31 rows=71 width=142) (actual time=1,021.685..1,021.952 rows=1,116 loops=1)

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

Nested Loop (cost=5,605.50..131,416.95 rows=71 width=142) (actual time=63.734..1,020.582 rows=1,116 loops=1)

111. 10.020 1,008.693 ↓ 15.7 1,116 1

Nested Loop (cost=5,605.08..131,262.96 rows=71 width=116) (actual time=63.661..1,008.693 rows=1,116 loops=1)

  • Join Filter: ((pe_3.apply_date <= ls_4.first_payment_date) AND ((pe_3.apply_date IS NULL) OR (pe_3.apply_date <= ls_4.first_payment_date)))
  • Rows Removed by Join Filter: 9,998
112. 9.838 884.013 ↓ 17.7 11,466 1

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

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

Seq Scan on payment_entity pe_3 (cost=0.00..123,516.29 rows=4,794 width=14) (actual time=0.194..818.651 rows=11,830 loops=1)

  • Filter: ((nacha_return_code IS NULL) AND (deleted = 0) AND (active = 1) AND (lastupdated > (CURRENT_DATE - 10)))
  • Rows Removed by Filter: 1,575,694
114. 19.157 55.524 ↓ 1.0 53,499 1

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

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

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

116. 114.660 114.660 ↑ 1.0 1 11,466

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

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

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

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

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

119. 0.002 308.274 ↓ 0.0 0 1

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

120. 212.549 308.272 ↓ 0.0 0 1

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

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

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

122. 89.912 89.912 ↓ 1.8 20,875 1

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

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

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

124. 0.000 0.000 ↓ 0.0 0

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

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

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

126. 4.060 588.388 ↓ 0.0 0 1

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

127. 3.044 492.728 ↓ 381.7 5,725 1

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

128. 111.834 111.834 ↓ 381.7 5,725 1

Seq Scan on returnfilerecord rr (cost=0.00..8,402.17 rows=15 width=8) (actual time=108.084..111.834 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. 377.850 377.850 ↑ 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.066..0.066 rows=1 loops=5,725)

  • Index Cond: (achfilerecord_id = rr.originalachrecord_id)
  • Filter: (product = 'BALANCE_CREDIT'::text)
130. 91.600 91.600 ↓ 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.016..0.016 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.064 71.173 ↓ 0.0 0 1

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

133. 4.069 70.896 ↓ 35.5 71 1

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

134. 55.377 55.377 ↓ 381.7 5,725 1

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

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

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

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

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

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

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

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

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

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

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

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

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

Seq Scan on returnfilerecord rr_2 (cost=0.00..8,951.53 rows=15 width=14) (actual time=67.789..67.789 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.007 57,436.051 ↑ 2,151,827.0 8 1

Subquery Scan on *SELECT* 19 (cost=472,706.92..884,403.68 rows=17,214,616 width=116) (actual time=57,381.901..57,436.051 rows=8 loops=1)

145. 6.115 57,436.044 ↑ 2,151,827.0 8 1

Hash Join (cost=472,706.92..669,220.98 rows=17,214,616 width=112) (actual time=57,381.899..57,436.044 rows=8 loops=1)

  • Hash Cond: (loan_autopay_entity_1.loan_id = tx.loan_id)
  • Join Filter: ((min(loan_autopay_entity_1.apply_date)) <> tx.date)
  • Rows Removed by Join Filter: 11,016
146. 13.270 429.188 ↑ 2.9 11,024 1

Merge Anti Join (cost=45,840.31..70,122.63 rows=31,782 width=24) (actual time=380.446..429.188 rows=11,024 loops=1)

  • Merge Cond: (loan_autopay_entity_1.loan_id = loan_autopay_entity.loan_id)
  • Join Filter: (loan_autopay_entity.apply_date <= (min(loan_autopay_entity_1.apply_date)))
  • Rows Removed by Join Filter: 8,001
147. 8.231 383.231 ↑ 3.4 11,249 1

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

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

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

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

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

150. 4.267 164.308 ↑ 3.4 11,249 1

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

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

GroupAggregate (cost=0.41..24,884.47 rows=38,139 width=8) (actual time=1.057..160.041 rows=11,249 loops=1)

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

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity loan_autopay_entity_1 (cost=0.41..24,075.54 rows=42,754 width=8) (actual time=0.018..135.080 rows=27,522 loops=1)

  • Filter: (name = 'Autopay'::text)
  • Rows Removed by Filter: 19,648
153. 32.687 32.687 ↓ 6.2 19,115 1

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity (cost=0.41..24,075.54 rows=3,087 width=8) (actual time=0.019..32.687 rows=19,115 loops=1)

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

Hash (cost=425,505.70..425,505.70 rows=108,872 width=12) (actual time=57,000.741..57,000.741 rows=271,943 loops=1)

  • Buckets: 524,288 (originally 131072) Batches: 1 (originally 1) Memory Usage: 15,782kB
155. 107.278 56,901.829 ↓ 2.5 271,943 1

Subquery Scan on tx (cost=423,328.26..425,505.70 rows=108,872 width=12) (actual time=56,684.081..56,901.829 rows=271,943 loops=1)

156. 737.045 56,794.551 ↓ 2.5 271,943 1

HashAggregate (cost=423,328.26..424,416.98 rows=108,872 width=12) (actual time=56,684.080..56,794.551 rows=271,943 loops=1)

  • Group Key: loan_tx.entity_id
157. 56,057.506 56,057.506 ↑ 1.5 1,102,462 1

Index Scan using loan_tx_date1 on loan_tx (cost=0.44..414,828.59 rows=1,699,934 width=12) (actual time=55.491..56,057.506 rows=1,102,462 loops=1)

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

Subquery Scan on *SELECT* 20 (cost=141,998.91..186,861.73 rows=5,592 width=116) (actual time=3,040.057..3,047.380 rows=4 loops=1)

159. 0.019 3,047.375 ↑ 1,398.0 4 1

Nested Loop Anti Join (cost=141,998.91..186,791.83 rows=5,592 width=112) (actual time=3,040.055..3,047.375 rows=4 loops=1)

160. 678.522 3,046.816 ↑ 1,565.5 4 1

Hash Right Join (cost=141,998.48..183,795.07 rows=6,262 width=24) (actual time=3,040.029..3,046.816 rows=4 loops=1)

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

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

162. 98.556 1,776.024 ↓ 1.4 245,787 1

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 15,490kB
163. 552.345 1,677.468 ↓ 1.4 245,787 1

Hash Join (cost=5,604.65..139,780.89 rows=177,407 width=24) (actual time=55.254..1,677.468 rows=245,787 loops=1)

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

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

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

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

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

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

167. 0.540 0.540 ↓ 0.0 0 4

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

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

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

169. 54.530 5,828.494 ↓ 0.0 0 1

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

  • Hash Cond: (l.loan_id = lse_14.loan_id)
170. 56.263 5,561.974 ↑ 2.7 141,798 1

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

171. 262.106 5,505.711 ↑ 2.7 141,798 1

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

172. 226.531 5,243.605 ↓ 1.0 627,360 1

Append (cost=62,363.83..219,692.79 rows=621,384 width=12) (actual time=1,462.583..5,243.605 rows=627,360 loops=1)

173. 149.733 1,766.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=1,462.581..1,766.814 rows=384,579 loops=1)

174. 367.883 1,617.081 ↓ 1.0 384,579 1

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

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

  • Filter: ((contract_date >= '2016-01-01'::date) AND (contract_date <= CURRENT_DATE))
  • Rows Removed by Filter: 787
176. 93.791 3,250.260 ↓ 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,062.102..3,250.260 rows=242,781 loops=1)

177. 220.426 3,156.469 ↓ 1.0 242,781 1

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

  • Group Key: ls_9.loan_id, ls_9.contract_date
178. 183.947 2,936.043 ↓ 1.0 242,781 1

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

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

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

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

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

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

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

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

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

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

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

185. 33.167 3,305.639 ↓ 0.0 0 1

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

186. 62.204 2,813.188 ↓ 1.2 65,612 1

Hash Join (cost=74,586.31..183,196.36 rows=56,040 width=24) (actual time=1,393.569..2,813.188 rows=65,612 loops=1)

  • Hash Cond: (p_2.entity_id = lse_15.loan_id)
187. 974.059 2,465.939 ↓ 1.2 65,612 1

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

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

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

189. 638.935 1,094.940 ↑ 1.0 1,577,494 1

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

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

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

191. 144.941 285.045 ↓ 1.0 385,366 1

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

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

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

193. 459.284 459.284 ↓ 0.0 0 65,612

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

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

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

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

Seq Scan on achfilerecord ach_1 (cost=0.00..86,673.18 rows=234,819 width=4) (actual time=0.057..525.554 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. 52.856 559.930 ↑ 1.0 142,165 1

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

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

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

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

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

199. 51.156 155.073 ↑ 1.0 142,165 1

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

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

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

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

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

202. 0.001 9,893.039 ↓ 0.0 0 1

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

203. 1,322.699 9,893.038 ↓ 0.0 0 1

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

204. 783.239 2,386.939 ↓ 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,726.604..2,386.939 rows=1,545,850 loops=1)

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

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

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

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

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

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

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

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

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

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

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

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

211. 3.373 1,630.617 ↓ 0.0 0 1

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

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

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

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

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

214. 57.548 57.548 ↓ 2.6 2,814 1

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

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

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

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

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

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

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

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

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

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

220. 0.473 246.756 ↓ 0.0 0 1

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

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

222. 0.001 240.096 ↓ 0.0 0 1

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

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

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

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

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

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

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

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

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

227. 0.000 0.000 ↓ 0.0 0

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

228. 0.001 3,932.589 ↓ 0.0 0 1

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

229. 17.091 3,932.588 ↓ 0.0 0 1

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

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

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

231. 0.009 2,734.684 ↓ 2.0 2 1

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

232. 0.007 2,734.643 ↓ 2.0 2 1

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

233. 0.010 2,730.630 ↓ 2.0 2 1

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

234. 0.015 2,730.590 ↓ 2.0 2 1

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

235. 597.915 2,725.659 ↓ 2.0 2 1

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

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

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

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

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

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

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

239. 4.916 4.916 ↑ 1.0 1 2

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

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

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

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

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

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

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

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

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

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

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

245. 323.832 1,146.572 ↑ 5.2 38,104 2

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

246. 222.458 822.740 ↓ 1.1 610,060 1

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

247. 145.309 363.423 ↑ 1.0 376,728 1

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

248. 218.114 218.114 ↑ 1.0 376,728 1

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

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

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

250. 143.675 143.675 ↓ 1.3 233,332 1

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

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

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

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

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

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

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

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

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

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

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

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

Seq Scan on loan_sub_status_entity ss (cost=0.00..1.80 rows=5 width=4) (actual time=0.745..0.751 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. 99.755 4,502.543 ↓ 0.0 0 1

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

261. 219.217 3,218.924 ↓ 295,966.0 295,966 1

Hash Right Join (cost=21,954.06..118,897.85 rows=1 width=24) (actual time=3,078.354..3,218.924 rows=295,966 loops=1)

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

Seq Scan on loan_document_entity f (cost=0.00..94,315.22 rows=191,168 width=8) (actual time=22.649..2,705.764 rows=159,815 loops=1)

  • Filter: ((active = 1) AND (archived = 0) AND (deleted = 0) AND ((filename ~~ 'marked-paid%'::text) OR (filename ~~ 'paid-off-notice%'::text)))
  • Rows Removed by Filter: 1,948,809
263. 146.272 293.943 ↓ 1.0 385,365 1

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

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

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

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

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

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

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

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

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

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

Seq Scan on loan_document_entity f_1 (cost=0.00..89,043.42 rows=35,014 width=4) (actual time=5.839..392.859 rows=27,763 loops=1)

  • Filter: ((filename ~~ 'paid-off-notice%'::text) AND (active = 1) AND (archived = 0) AND (deleted = 0))
  • Rows Removed by Filter: 2,080,861
269. 144.953 281.953 ↓ 1.0 385,366 1

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

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

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

271. 55.526 55.526 ↑ 1.0 1 27,763

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

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

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

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

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

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

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

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

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

276. 31.607 212.612 ↓ 1.0 89,318 1

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

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

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

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

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

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

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

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

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

282. 0.001 8,195.499 ↓ 0.0 0 1

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

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

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

284. 292.928 3,655.387 ↓ 320,847.0 320,847 1

Nested Loop (cost=21,167.51..21,247.83 rows=1 width=24) (actual time=303.532..3,655.387 rows=320,847 loops=1)

285. 182.477 474.890 ↓ 320,841.0 320,841 1

Hash Right Join (cost=21,167.09..21,247.34 rows=1 width=24) (actual time=301.702..474.890 rows=320,841 loops=1)

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

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

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

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

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

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

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

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

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

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

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

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

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

Nested Loop (cost=38,676.60..304,836.08 rows=3,820 width=68) (actual time=8,333.748..8,333.749 rows=0 loops=1)

  • Join Filter: (cfe1.custom_field_value <> cfe2.custom_field_value)
  • Rows Removed by Join Filter: 9,002
293. 32.105 8,266.646 ↓ 1.3 9,002 1

Nested Loop (cost=38,676.17..259,925.56 rows=7,096 width=40) (actual time=232.865..8,266.646 rows=9,002 loops=1)

294. 98.899 6,635.725 ↓ 1.8 23,512 1

Merge Join (cost=38,675.75..178,599.03 rows=12,860 width=28) (actual time=232.291..6,635.725 rows=23,512 loops=1)

  • Merge Cond: (lse_26.id = cfe3.entity_id)
295. 171.581 290.029 ↑ 1.0 240,374 1

Sort (cost=38,675.29..39,277.38 rows=240,839 width=24) (actual time=228.154..290.029 rows=240,374 loops=1)

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

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

  • Filter: (loan_status_id = ANY ('{4,5}'::bigint[]))
  • Rows Removed by Filter: 144,979
297. 6,246.797 6,246.797 ↓ 4.1 83,888 1

Index Scan using custom_field__entity_entity_id_purchase_date_indx on custom_field__entity cfe3 (cost=0.42..171,993.37 rows=20,535 width=4) (actual time=0.064..6,246.797 rows=83,888 loops=1)

  • Filter: ((custom_field_value IS NULL) OR (custom_field_value = ''::text))
  • Rows Removed by Filter: 104,849
298. 1,598.816 1,598.816 ↓ 0.0 0 23,512

Index Scan using custom_field__entity_entity_id_original_owner_indx on custom_field__entity cfe1 (cost=0.42..6.30 rows=2 width=12) (actual time=0.068..0.068 rows=0 loops=23,512)

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

Index Scan using custom_field__entity_entity_id_current_owner_indx on custom_field__entity cfe2 (cost=0.42..6.30 rows=2 width=12) (actual time=0.006..0.006 rows=1 loops=9,002)

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

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

301. 81.463 23,787.131 ↓ 0.0 0 1

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

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

CTE max_date_qry

303. 0.005 2.280 ↑ 1.0 1 1

Result (cost=0.72..0.73 rows=1 width=4) (actual time=2.279..2.280 rows=1 loops=1)

304.          

Initplan (for Result)

305. 0.002 2.275 ↑ 1.0 1 1

Limit (cost=0.57..0.72 rows=1 width=4) (actual time=2.274..2.275 rows=1 loops=1)

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

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

Nested Loop (cost=17,364.93..1,145,807.36 rows=2,654 width=26) (actual time=285.629..23,667.834 rows=191,401 loops=1)

308. 2.284 2.284 ↑ 1.0 1 1

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

309. 23,287.821 23,564.403 ↓ 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=283.341..23,564.403 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.009 276.582 ↓ 0.0 0 1

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

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

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

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

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

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

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

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

CTE Scan on y (cost=665,908.07..665,908.13 rows=1 width=116) (actual time=15,795.799..15,796.222 rows=4 loops=1)

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

CTE payment_date

317. 12,080.784 14,350.315 ↑ 1.0 1,356,175 1

WindowAgg (cost=250,323.27..617,924.76 rows=1,361,487 width=24) (actual time=1,812.708..14,350.315 rows=1,356,175 loops=1)

318. 1,316.769 2,269.531 ↑ 1.0 1,356,175 1

Sort (cost=250,323.27..253,726.99 rows=1,361,487 width=12) (actual time=1,812.584..2,269.531 rows=1,356,175 loops=1)

  • Sort Key: payment_entity_3.entity_id, payment_entity_3.apply_date DESC
  • Sort Method: quicksort Memory: 112,723kB
319. 952.762 952.762 ↑ 1.0 1,356,175 1

Seq Scan on payment_entity payment_entity_3 (cost=0.00..111,609.86 rows=1,361,487 width=12) (actual time=0.010..952.762 rows=1,356,175 loops=1)

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

CTE y

321. 0.515 15,796.108 ↓ 119.5 239 1

GroupAggregate (cost=47,983.27..47,983.31 rows=2 width=28) (actual time=15,795.465..15,796.108 rows=239 loops=1)

  • Group Key: x_2.loan_id, x_2.days_to_clear, x_2.loan_status_id
322. 0.680 15,795.593 ↓ 366.5 733 1

Sort (cost=47,983.27..47,983.27 rows=2 width=24) (actual time=15,795.448..15,795.593 rows=733 loops=1)

  • Sort Key: x_2.loan_id, x_2.days_to_clear, x_2.loan_status_id
  • Sort Method: quicksort Memory: 82kB
323. 0.470 15,794.913 ↓ 366.5 733 1

Subquery Scan on x_2 (cost=17,161.21..47,983.26 rows=2 width=24) (actual time=8,377.656..15,794.913 rows=733 loops=1)

324. 9.977 15,794.443 ↓ 366.5 733 1

Nested Loop (cost=17,161.21..47,983.24 rows=2 width=32) (actual time=8,377.654..15,794.443 rows=733 loops=1)

  • Join Filter: (lse_41.loan_id = ls_13.loan_id)
325. 1.332 15,779.335 ↓ 366.5 733 1

Nested Loop (cost=17,160.79..47,974.16 rows=2 width=36) (actual time=8,377.612..15,779.335 rows=733 loops=1)

  • Join Filter: (lse_41.loan_id = lsa_7.loan_id)
326. 79.263 15,466.483 ↓ 18.5 352 1

Hash Join (cost=17,160.36..47,819.54 rows=19 width=28) (actual time=8,348.192..15,466.483 rows=352 loops=1)

  • Hash Cond: (pd.entity_id = lse_41.loan_id)
327. 15,335.465 15,335.465 ↓ 30.2 205,740 1

CTE Scan on payment_date pd (cost=0.00..30,633.46 rows=6,807 width=12) (actual time=1,812.711..15,335.465 rows=205,740 loops=1)

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

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

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

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

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

Index Scan using loan_status_archive_active_sub_status_id_39_rename on loan_status_archive lsa_7 (cost=0.43..8.13 rows=1 width=12) (actual time=0.423..0.885 rows=2 loops=352)

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

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

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

Subquery Scan on *SELECT* 36 (cost=0.87..60,542.72 rows=1,984 width=116) (actual time=202.157..223.471 rows=3 loops=1)

333. 23.744 223.466 ↑ 661.3 3 1

Nested Loop (cost=0.87..60,512.96 rows=1,984 width=108) (actual time=202.156..223.466 rows=3 loops=1)

334. 134.310 134.310 ↓ 7.2 21,804 1

Index Scan using custom_field__entity_last_updated on custom_field__entity cfe_3 (cost=0.44..37,855.92 rows=3,016 width=12) (actual time=2.842..134.310 rows=21,804 loops=1)

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

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

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

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

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

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

338. 0.001 166.091 ↓ 0.0 0 1

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

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

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

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

Result (cost=127.67..6,416.34 rows=1 width=87) (actual time=2.814..152.642 rows=639 loops=1)

  • One-Time Filter: (NOT (hashed SubPlan 6))
341. 0.731 152.260 ↓ 639.0 639 1

Nested Loop (cost=127.67..6,416.34 rows=1 width=87) (actual time=2.765..152.260 rows=639 loops=1)

342. 2.992 147.275 ↓ 709.0 709 1

Nested Loop (cost=125.63..6,406.46 rows=1 width=27) (actual time=2.696..147.275 rows=709 loops=1)

343. 3.421 115.303 ↓ 2,898.0 2,898 1

Nested Loop (cost=125.21..6,398.36 rows=1 width=32) (actual time=2.528..115.303 rows=2,898 loops=1)

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

Nested Loop (cost=124.79..6,390.55 rows=1 width=28) (actual time=2.516..100.596 rows=3,762 loops=1)

345. 16.070 44.564 ↓ 30,742.0 30,742 1

Merge Left Join (cost=124.49..6,382.88 rows=1 width=24) (actual time=2.504..44.564 rows=30,742 loops=1)

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

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

  • Filter: ((loan_status_id = 4) AND (loan_sub_status_id = 20))
  • Rows Removed by Filter: 42
347. 1.626 3.223 ↓ 3.0 2,352 1

Sort (cost=124.20..126.17 rows=785 width=8) (actual time=2.484..3.223 rows=2,352 loops=1)

  • Sort Key: pe_5.entity_id
  • Sort Method: quicksort Memory: 207kB
348. 1.597 1.597 ↓ 3.0 2,352 1

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

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

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

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

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

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

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

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

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

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

SubPlan (for Result)

354. 0.023 0.023 ↑ 1.0 37 1

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

355. 0.008 14,620.160 ↑ 2,232.7 7 1

Result (cost=5,251.99..151,785.32 rows=15,629 width=87) (actual time=3,959.461..14,620.160 rows=7 loops=1)

356. 0.009 14,620.152 ↑ 2,232.7 7 1

Append (cost=5,251.99..151,629.03 rows=15,629 width=51) (actual time=3,959.459..14,620.152 rows=7 loops=1)

357. 85.188 13,992.608 ↑ 1,227.0 1 1

Hash Left Join (cost=5,251.99..84,080.89 rows=1,227 width=51) (actual time=3,959.458..13,992.608 rows=1 loops=1)

  • Hash Cond: (lse_30.loan_id = rtc_1.parent_id)
  • Filter: ((lsa_3.loan_id IS NOT NULL) OR (pe_6.id IS NOT NULL) OR (rtc_1.id IS NULL) OR (lcf_1.loan_settings_id IS NOT NULL) OR (hashed SubPlan 5))
  • Rows Removed by Filter: 9,229
358. 32.984 13,769.623 ↓ 7.5 9,210 1

Nested Loop Left Join (cost=2.80..78,726.09 rows=1,227 width=42) (actual time=2.682..13,769.623 rows=9,210 loops=1)

  • Join Filter: ((lsa_3.days_past_due <= 90) AND (pe_6.apply_date = dw_reporting_meta.text_to_date(lscf_1.called_due_notice_date)))
  • Rows Removed by Join Filter: 58,886
359. 16.269 13,257.719 ↓ 7.5 9,210 1

Nested Loop Left Join (cost=2.38..62,349.90 rows=1,227 width=38) (actual time=2.654..13,257.719 rows=9,210 loops=1)

360. 80.451 13,084.880 ↓ 7.5 9,210 1

Nested Loop Left Join (cost=1.95..52,222.55 rows=1,227 width=39) (actual time=2.607..13,084.880 rows=9,210 loops=1)

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

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

362. 12.377 98.798 ↓ 7.5 9,210 1

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

363. 40.371 40.371 ↓ 7.5 9,210 1

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

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

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

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

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

  • Index Cond: (loan_settings_id = lse_30.id)
366. 12,857.160 12,857.160 ↓ 0.0 0 9,210

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

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

SubPlan (for Nested Loop Left Join)

368. 6.681 6.681 ↑ 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.131..0.131 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. 156.570 156.570 ↓ 0.0 0 9,210

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

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

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

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

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 4,729kB
373. 105.279 105.279 ↑ 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=4.081..105.279 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.066 254.624 ↑ 1,233.0 6 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Filter: (loan_sub_status_id = 55)
  • Rows Removed by Filter: 376,156
384. 0.007 147,501.253 ↑ 4,104.2 4 1

Subquery Scan on x_1 (cost=0.57..866,087.59 rows=16,417 width=116) (actual time=146,827.343..147,501.253 rows=4 loops=1)

385. 0.009 147,501.246 ↑ 4,104.2 4 1

Append (cost=0.57..865,923.42 rows=16,417 width=56) (actual time=146,827.342..147,501.246 rows=4 loops=1)

386. 0.001 146,719.245 ↓ 0.0 0 1

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

387. 160.718 146,719.244 ↓ 0.0 0 1

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

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

389. 146,510.805 146,510.805 ↓ 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.545..1.545 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 122.225 ↑ 1.0 1 1

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

391. 0.011 122.203 ↑ 1.0 1 1

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

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

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

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

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

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

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

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

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

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

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

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

  • Index Cond: ((loan_id = rtc_3.parent_id) AND (date = (rtc_3.lastupdated)::date))
  • Filter: (days_past_due < 60)
  • Rows Removed by Filter: 1
398. 0.105 0.105 ↓ 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.014..0.035 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.017 0.017 ↑ 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.017..0.017 rows=1 loops=1)

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

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

401. 17.976 214.670 ↓ 29,178.0 29,178 1

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

402. 41.135 109.160 ↓ 29,178.0 29,178 1

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

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

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

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

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

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

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

Index Only Scan using loan_setup_entity_loan_id on loan_setup_entity lset_3 (cost=0.42..1.88 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=29,178)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

430. 0.000 0.000 ↓ 0.0 0

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

431. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: (loan_id = rtc_7.parent_id)
  • Heap Fetches: 0
433. 0.198 12,973.511 ↓ 122.0 122 1

Result (cost=10,877.56..145,906.74 rows=1 width=70) (actual time=2,808.984..12,973.511 rows=122 loops=1)

  • One-Time Filter: (NOT (hashed SubPlan 2))
434. 0.514 12,973.298 ↓ 122.0 122 1

Nested Loop Anti Join (cost=10,877.56..145,906.74 rows=1 width=70) (actual time=2,808.940..12,973.298 rows=122 loops=1)

435. 0.389 12,971.320 ↓ 122.0 122 1

Nested Loop Anti Join (cost=10,875.67..145,897.34 rows=1 width=43) (actual time=2,808.917..12,971.320 rows=122 loops=1)

  • Join Filter: (note_entity.id < rtc_8.id)
436. 7.361 11,480.091 ↓ 122.0 122 1

Nested Loop (cost=10,875.24..145,831.92 rows=1 width=43) (actual time=2,797.073..11,480.091 rows=122 loops=1)

437. 12.341 5,996.762 ↓ 7,648.0 7,648 1

Nested Loop (cost=10,874.67..145,823.64 rows=1 width=47) (actual time=1,746.992..5,996.762 rows=7,648 loops=1)

438. 444.146 5,961.477 ↓ 7,648.0 7,648 1

Hash Join (cost=10,874.25..145,819.34 rows=1 width=43) (actual time=1,746.963..5,961.477 rows=7,648 loops=1)

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

440. 11.561 121.777 ↓ 26,982.0 26,982 1

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

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

Hash Right Join (cost=6,450.85..10,874.23 rows=1 width=43) (actual time=101.427..110.216 rows=26,982 loops=1)

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

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

Hash (cost=6,423.45..6,423.45 rows=2,192 width=24) (actual time=41.280..41.281 rows=30,742 loops=1)

  • Buckets: 32,768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,938kB
444. 28.917 30.551 ↓ 14.0 30,742 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=2.630..30.551 rows=30,742 loops=1)

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

SubPlan (for Index Scan)

446. 1.634 1.634 ↓ 3.0 2,352 1

Index Scan using payment_entity_apply_date on payment_entity (cost=0.43..86.46 rows=785 width=4) (actual time=0.021..1.634 rows=2,352 loops=1)

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

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

  • Index Cond: (loan_id = lse_38.loan_id)
  • Heap Fetches: 19
448. 5,475.968 5,475.968 ↓ 0.0 0 7,648

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

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

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

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

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

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

SubPlan (for Result)

452. 0.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.002 26,233.780 ↓ 0.0 0 1

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

454. 22.838 26,233.778 ↓ 0.0 0 1

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

455. 11.367 5,411.182 ↓ 632.8 17,717 1

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

456. 20.600 2,262.943 ↓ 68.8 18,028 1

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

457. 27.422 1,868.233 ↓ 52.2 19,690 1

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

458. 46.429 1,702.981 ↓ 52.2 19,690 1

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

459. 39.628 39.628 ↓ 13.4 27,878 1

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

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

Index Scan using loan_setup_entity_loan_id on loan_setup_entity setup (cost=0.42..6.67 rows=1 width=102) (actual time=0.058..0.058 rows=1 loops=27,878)

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

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

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

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

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

Index Scan using loan_autopay_entity_status_cond_autopay_status_pending on loan_autopay_entity lae_2 (cost=0.41..2.73 rows=1 width=4) (actual time=0.174..0.174 rows=1 loops=18,028)

  • Index Cond: (loan_id = lse_39.loan_id)
  • Filter: ((deleted = 0) AND (name = 'Autopay'::text) AND (type = 'autopay.type.recurring'::bpchar))
  • Rows Removed by Filter: 0
464. 20,799.758 20,799.758 ↑ 1.0 1 17,717

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

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

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

466. 10.168 9,473.718 ↓ 0.0 0 1

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

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

Index Scan using payment_receipt_audit_created_at on payment_receipt_audit au (cost=0.43..2,792.43 rows=17,248 width=12) (actual time=1.406..50.342 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. 9,413.208 9,413.208 ↑ 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.491..0.504 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.041 16.794 ↓ 0.0 0 1

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

470. 16.669 16.669 ↑ 25.4 42 1

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

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

Index Scan using loan_settings_custom_fields_loan_settings_id on loan_settings_custom_fields lscf_2 (cost=0.42..7.37 rows=1 width=6) (actual time=0.002..0.002 rows=0 loops=42)

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