explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QLoc

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=733,096,091,502.40..4,259,368,374,787,268.50 rows=33,597,933,458,332,840 width=1,404) (actual rows= loops=)

  • Merge Cond: (s.loan_number = b.loan)
2.          

CTE loan_info

3. 0.000 0.000 ↓ 0.0

Sort (cost=8.47..8.48 rows=1 width=77) (actual rows= loops=)

  • Sort Key: loans_loaninfo.user_id, loans_loaninfo.date_of_application
4. 0.000 0.000 ↓ 0.0

Index Scan using loans_loaninfo_pkey on loans_loaninfo (cost=0.42..8.46 rows=1 width=77) (actual rows= loops=)

  • Index Cond: (id = 545957)
5.          

CTE smsinfo

6. 0.000 0.000 ↓ 0.0

Unique (cost=4,474,663.95..4,496,345.16 rows=1,238,926 width=122) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Sort (cost=4,474,663.95..4,477,761.27 rows=1,238,926 width=122) (actual rows= loops=)

  • Sort Key: deviceinfo_archivedsmsinfo.id, deviceinfo_archivedsmsinfo.address, deviceinfo_archivedsmsinfo.date, deviceinfo_archivedsmsinfo.body, deviceinfo_archivedsmsinfo.type, deviceinfo_archivedsmsinfo.user_id
8. 0.000 0.000 ↓ 0.0

Append (cost=24,503.25..4,188,364.56 rows=1,238,926 width=122) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Gather (cost=24,503.25..4,168,164.97 rows=1,238,539 width=122) (actual rows= loops=)

  • Workers Planned: 2
10. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on deviceinfo_archivedsmsinfo (cost=23,503.25..4,043,311.07 rows=516,058 width=122) (actual rows= loops=)

  • Recheck Cond: (user_id = 94147)
11. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on deviceinfo_archivedsmsinfo_e8701ad4 (cost=0.00..23,193.61 rows=1,238,539 width=0) (actual rows= loops=)

  • Index Cond: (user_id = 94147)
12. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on deviceinfo_smsinfo (cost=107.56..1,615.70 rows=387 width=166) (actual rows= loops=)

  • Recheck Cond: (user_id = 94147)
13. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on deviceinfo_smsinfo_e8701ad4 (cost=0.00..107.46 rows=387 width=0) (actual rows= loops=)

  • Index Cond: (user_id = 94147)
14.          

CTE locationinfo

15. 0.000 0.000 ↓ 0.0

Unique (cost=404,486.17..406,495.57 rows=114,823 width=172) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Sort (cost=404,486.17..404,773.22 rows=114,823 width=172) (actual rows= loops=)

  • Sort Key: deviceinfo_locationinfo.id, deviceinfo_locationinfo.latitude, deviceinfo_locationinfo.longitude, deviceinfo_locationinfo.accuracy, deviceinfo_locationinfo.date, deviceinfo_locationinfo.user_id
17. 0.000 0.000 ↓ 0.0

Append (cost=17.52..385,021.84 rows=114,823 width=172) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on deviceinfo_locationinfo (cost=17.52..551.14 rows=141 width=55) (actual rows= loops=)

  • Recheck Cond: (user_id = 94147)
19. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on deviceinfo_locationinfo_e8701ad4 (cost=0.00..17.48 rows=141 width=0) (actual rows= loops=)

  • Index Cond: (user_id = 94147)
20. 0.000 0.000 ↓ 0.0

Gather (cost=5,517.35..382,748.36 rows=114,682 width=172) (actual rows= loops=)

  • Workers Planned: 2
21. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on deviceinfo_archivedlocationinfo (cost=4,517.35..370,280.16 rows=47,784 width=172) (actual rows= loops=)

  • Recheck Cond: (user_id = 94147)
22. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on deviceinfo_archivedlocationinfo_e8701ad4 (cost=0.00..4,488.68 rows=114,682 width=0) (actual rows= loops=)

  • Index Cond: (user_id = 94147)
23.          

CTE smsinfo_balance

24. 0.000 0.000 ↓ 0.0

CTE Scan on smsinfo (cost=0.00..92,919.45 rows=1,359 width=122) (actual rows= loops=)

  • Filter: ((((address)::text = 'Diamond'::text) AND (body ~~ '%Alert%'::text)) OR (((address)::text = 'GTBank'::text) AND (body ~~ '%Acct%'::text)) OR (((address)::text = 'FirstBank'::text) AND (body ~~ '%Acct%'::text)) OR (((address)::text = 'AccessBank'::text) AND (body ~~ '%Amt%'::text) AND (body !~~ '%MonthEnd%'::text)) OR (((address)::text = 'StanbicIBTC'::text) AND (body ~~ '%A/C%'::text)) OR (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text)) OR (((address)::text = 'ZENITHBANK'::text) AND (body ~~ '%AlertZ%'::text)) OR (((address)::text = 'FCMB'::text) AND (body ~~ '%Alert%'::text)) OR (((address)::text = 'UBA'::text) AND (body ~~ '%Txn%'::text)) OR (((address)::text = 'UNIONBANK'::text) AND (body ~~ '%Amt%'::text) AND (body !~~ '%MonthEnd%'::text)))
25.          

CTE calendar

26. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..5.02 rows=1,000 width=8) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

28.          

CTE balances

29. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=455.80..483.30 rows=1,000 width=98) (actual rows= loops=)

  • Group Key: smsinfo_balance.user_id, ((t1.date_calendar)::date), b_1.address
30. 0.000 0.000 ↓ 0.0

Sort (cost=455.80..458.30 rows=1,000 width=98) (actual rows= loops=)

  • Sort Key: smsinfo_balance.user_id, ((t1.date_calendar)::date), b_1.address
31. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=379.54..405.97 rows=1,000 width=98) (actual rows= loops=)

  • Merge Cond: ((smsinfo_balance.user_id = b_1.user_id) AND (((t1.date_calendar)::date) = b_1.date))
32. 0.000 0.000 ↓ 0.0

Sort (cost=189.91..192.41 rows=1,000 width=12) (actual rows= loops=)

  • Sort Key: smsinfo_balance.user_id, ((t1.date_calendar)::date)
33. 0.000 0.000 ↓ 0.0

Nested Loop (cost=110.06..140.08 rows=1,000 width=12) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Unique (cost=110.06..110.07 rows=1 width=4) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Sort (cost=110.06..110.06 rows=1 width=4) (actual rows= loops=)

  • Sort Key: smsinfo_balance.user_id
36. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=0.42..110.05 rows=1 width=4) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

CTE Scan on smsinfo_balance (cost=0.00..44.17 rows=1 width=4) (actual rows= loops=)

  • Filter: ((type = 1) AND (upper(body) ~~ '%ALERT%'::text) AND (upper(body) ~~ '%BAL%'::text))
38. 0.000 0.000 ↓ 0.0

Index Scan using loans_loaninfo_e8701ad4 on loans_loaninfo loans_loaninfo_1 (cost=0.42..33.15 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (user_id = smsinfo_balance.user_id)
  • Filter: ((cardinal = 1) AND ((status)::text = ANY ('{PAID,DEFAULTED,OVERDUE}'::text[])))
39. 0.000 0.000 ↓ 0.0

CTE Scan on calendar t1 (cost=0.00..20.00 rows=1,000 width=8) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Sort (cost=189.64..193.03 rows=1,359 width=98) (actual rows= loops=)

  • Sort Key: b_1.user_id, b_1.date
41. 0.000 0.000 ↓ 0.0

Subquery Scan on b_1 (cost=0.00..118.91 rows=1,359 width=98) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Result (cost=0.00..105.32 rows=1,359 width=146) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..61.16 rows=1,359 width=102) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

CTE Scan on smsinfo_balance smsinfo_balance_1 (cost=0.00..27.18 rows=1,359 width=102) (actual rows= loops=)

45.          

CTE daily_balance

46. 0.000 0.000 ↓ 0.0

HashAggregate (cost=27.50..30.00 rows=200 width=40) (actual rows= loops=)

  • Group Key: balances.user_id, balances.date_calendar
47. 0.000 0.000 ↓ 0.0

CTE Scan on balances (cost=0.00..20.00 rows=1,000 width=40) (actual rows= loops=)

48.          

CTE words

49. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=467,273.08..491,589.88 rows=607,920 width=40) (actual rows= loops=)

  • Group Key: t1_1.user_id, t2.avg_words_inc, t3.avg_words_out, t4.id
50. 0.000 0.000 ↓ 0.0

Sort (cost=467,273.08..468,792.88 rows=607,920 width=80) (actual rows= loops=)

  • Sort Key: t1_1.user_id, t2.avg_words_inc, t3.avg_words_out, t4.id
51. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=214,858.80..354,845.66 rows=607,920 width=80) (actual rows= loops=)

  • Hash Cond: ((t1_1.user_id = t3.user_id) AND (t4.id = t3.loan_id))
52. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=135,743.46..272,538.73 rows=607,920 width=72) (actual rows= loops=)

  • Hash Cond: ((t1_1.user_id = t2.user_id) AND (t4.id = t2.loan_id))
53. 0.000 0.000 ↓ 0.0

Hash Join (cost=56,628.12..190,231.81 rows=607,920 width=64) (actual rows= loops=)

  • Hash Cond: (t1_1.user_id = t4.user_id)
  • Join Filter: (t1_1.date < t4.date_of_application)
54. 0.000 0.000 ↓ 0.0

Hash Join (cost=25,272.21..76,509.35 rows=197,176 width=64) (actual rows= loops=)

  • Hash Cond: (t1_1.user_id = loans_loaninfo_2.user_id)
55. 0.000 0.000 ↓ 0.0

CTE Scan on smsinfo t1_1 (cost=0.00..48,008.38 rows=394,352 width=60) (actual rows= loops=)

  • Filter: (((address)::text ~ '[0-9]'::text) AND (length((address)::text) > 5) AND ((address)::text <> ALL ('{+2348068293241,+2348133875910,Etisalat2,+2348106677417,+2349036141146,9mobile,4XRECHARGE,MTNAwuf4U,MTN180Tips}'::text[])))
56. 0.000 0.000 ↓ 0.0

Hash (cost=24,740.43..24,740.43 rows=42,542 width=4) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

HashAggregate (cost=24,315.01..24,740.43 rows=42,542 width=4) (actual rows= loops=)

  • Group Key: loans_loaninfo_2.user_id
58. 0.000 0.000 ↓ 0.0

Seq Scan on loans_loaninfo loans_loaninfo_2 (cost=0.00..24,134.17 rows=72,337 width=4) (actual rows= loops=)

  • Filter: ((status)::text = ANY ('{PAID,DEFAULTED,OVERDUE}'::text[]))
59. 0.000 0.000 ↓ 0.0

Hash (cost=22,147.85..22,147.85 rows=529,685 width=16) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Seq Scan on loans_loaninfo t4 (cost=0.00..22,147.85 rows=529,685 width=16) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Hash (cost=79,069.74..79,069.74 rows=3,040 width=16) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Subquery Scan on t2 (cost=78,932.94..79,069.74 rows=3,040 width=16) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=78,932.94..79,039.34 rows=3,040 width=16) (actual rows= loops=)

  • Group Key: a_1.user_id, b_2.id
64. 0.000 0.000 ↓ 0.0

Sort (cost=78,932.94..78,940.54 rows=3,040 width=56) (actual rows= loops=)

  • Sort Key: a_1.user_id, b_2.id
65. 0.000 0.000 ↓ 0.0

Nested Loop (cost=25,038.81..78,757.08 rows=3,040 width=56) (actual rows= loops=)

  • Join Filter: ((a_1.date < b_2.date_of_application) AND (a_1.user_id = b_2.user_id))
66. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=25,038.38..76,306.89 rows=986 width=64) (actual rows= loops=)

  • Hash Cond: (a_1.user_id = loans_loaninfo_3.user_id)
67. 0.000 0.000 ↓ 0.0

CTE Scan on smsinfo a_1 (cost=0.00..51,105.70 rows=1,972 width=60) (actual rows= loops=)

  • Filter: (((address)::text ~ '[0-9]'::text) AND (type = 1) AND (length((address)::text) > 5) AND ((address)::text <> ALL ('{+2348068293241,+2348133875910,Etisalat2,+2348106677417,+2349036141146,9mobile,4XRECHARGE,MTNAwuf4U,MTN180Tips}'::text[])))
68. 0.000 0.000 ↓ 0.0

Hash (cost=24,134.17..24,134.17 rows=72,337 width=4) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Seq Scan on loans_loaninfo loans_loaninfo_3 (cost=0.00..24,134.17 rows=72,337 width=4) (actual rows= loops=)

  • Filter: ((status)::text = ANY ('{PAID,DEFAULTED,OVERDUE}'::text[]))
70. 0.000 0.000 ↓ 0.0

Index Scan using loans_loaninfo_e8701ad4 on loans_loaninfo b_2 (cost=0.42..2.35 rows=9 width=16) (actual rows= loops=)

  • Index Cond: (user_id = loans_loaninfo_3.user_id)
71. 0.000 0.000 ↓ 0.0

Hash (cost=79,069.74..79,069.74 rows=3,040 width=16) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Subquery Scan on t3 (cost=78,932.94..79,069.74 rows=3,040 width=16) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=78,932.94..79,039.34 rows=3,040 width=16) (actual rows= loops=)

  • Group Key: a_2.user_id, b_3.id
74. 0.000 0.000 ↓ 0.0

Sort (cost=78,932.94..78,940.54 rows=3,040 width=56) (actual rows= loops=)

  • Sort Key: a_2.user_id, b_3.id
75. 0.000 0.000 ↓ 0.0

Nested Loop (cost=25,038.81..78,757.08 rows=3,040 width=56) (actual rows= loops=)

  • Join Filter: ((a_2.date < b_3.date_of_application) AND (a_2.user_id = b_3.user_id))
76. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=25,038.38..76,306.89 rows=986 width=64) (actual rows= loops=)

  • Hash Cond: (a_2.user_id = loans_loaninfo_4.user_id)
77. 0.000 0.000 ↓ 0.0

CTE Scan on smsinfo a_2 (cost=0.00..51,105.70 rows=1,972 width=60) (actual rows= loops=)

  • Filter: (((address)::text ~ '[0-9]'::text) AND (type = 1) AND (length((address)::text) > 5) AND ((address)::text <> ALL ('{+2348068293241,+2348133875910,Etisalat2,+2348106677417,+2349036141146,9mobile,4XRECHARGE,MTNAwuf4U,MTN180Tips}'::text[])))
78. 0.000 0.000 ↓ 0.0

Hash (cost=24,134.17..24,134.17 rows=72,337 width=4) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Seq Scan on loans_loaninfo loans_loaninfo_4 (cost=0.00..24,134.17 rows=72,337 width=4) (actual rows= loops=)

  • Filter: ((status)::text = ANY ('{PAID,DEFAULTED,OVERDUE}'::text[]))
80. 0.000 0.000 ↓ 0.0

Index Scan using loans_loaninfo_e8701ad4 on loans_loaninfo b_3 (cost=0.42..2.35 rows=9 width=16) (actual rows= loops=)

  • Index Cond: (user_id = loans_loaninfo_4.user_id)
81.          

CTE locations

82. 0.000 0.000 ↓ 0.0

Unique (cost=96,458.58..96,903.59 rows=16,182 width=200) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Sort (cost=96,458.58..96,499.04 rows=16,182 width=200) (actual rows= loops=)

  • Sort Key: a_3.user_id, b_4.id, a_3.date, b_4.date_of_application, (date_part('hour'::text, a_3.date)), (date_part('dow'::text, a_3.date)), a_3.longitude, a_3.latitude, (st_setsrid(st_makepoint((a_3.longitude)::double precision, (a_3.latitude)::double precision), 4326)), (CASE WHEN (((date_part('hour'::text, a_3.date) >= '10'::double precision) AND (date_part('hour'::text, a_3.date) <= '12'::double precision)) OR ((date_part('hour'::text, a_3.date) >= '14'::double precision) AND (date_part('hour'::text, a_3.date) <= '16'::double precision))) THEN 'WORK'::text WHEN (((date_part('hour'::text, a_3.date) >= '1'::double precision) AND (date_part('hour'::text, a_3.date) <= '4'::double precision)) OR ((date_part('hour'::text, a_3.date) >= '23'::double precision) AND (date_part('hour'::text, a_3.date) <= '24'::double precision))) THEN 'HOME'::text ELSE NULL::text END)
84. 0.000 0.000 ↓ 0.0

Append (cost=44,844.23..95,327.29 rows=16,182 width=200) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Merge Join (cost=44,844.23..47,542.28 rows=8,091 width=200) (actual rows= loops=)

  • Merge Cond: (a_3.user_id = b_4.user_id)
  • Join Filter: ((a_3.date < b_4.date_of_application) AND (a_3.date >= (b_4.date_of_application - '14 days'::interval)))
86. 0.000 0.000 ↓ 0.0

Sort (cost=18,621.29..18,908.35 rows=114,823 width=108) (actual rows= loops=)

  • Sort Key: a_3.user_id
87. 0.000 0.000 ↓ 0.0

CTE Scan on locationinfo a_3 (cost=0.00..2,296.46 rows=114,823 width=108) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Sort (cost=26,222.94..26,313.74 rows=36,319 width=16) (actual rows= loops=)

  • Sort Key: b_4.user_id
89. 0.000 0.000 ↓ 0.0

Seq Scan on loans_loaninfo b_4 (cost=0.00..23,472.06 rows=36,319 width=16) (actual rows= loops=)

  • Filter: (cardinal = ANY ('{1,2}'::integer[]))
90. 0.000 0.000 ↓ 0.0

Merge Join (cost=44,844.23..47,542.28 rows=8,091 width=200) (actual rows= loops=)

  • Merge Cond: (a_4.user_id = b_5.user_id)
  • Join Filter: ((a_4.date < b_5.date_of_application) AND (a_4.date >= (b_5.date_of_application - '14 days'::interval)))
91. 0.000 0.000 ↓ 0.0

Sort (cost=18,621.29..18,908.35 rows=114,823 width=108) (actual rows= loops=)

  • Sort Key: a_4.user_id
92. 0.000 0.000 ↓ 0.0

CTE Scan on locationinfo a_4 (cost=0.00..2,296.46 rows=114,823 width=108) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Sort (cost=26,222.94..26,313.74 rows=36,319 width=16) (actual rows= loops=)

  • Sort Key: b_5.user_id
94. 0.000 0.000 ↓ 0.0

Seq Scan on loans_loaninfo b_5 (cost=0.00..23,472.06 rows=36,319 width=16) (actual rows= loops=)

  • Filter: (cardinal = ANY ('{1,2}'::integer[]))
95.          

CTE areas

96. 0.000 0.000 ↓ 0.0

Seq Scan on gis_areas (cost=0.00..2,603.03 rows=438 width=76) (actual rows= loops=)

  • Filter: (level = 5)
97.          

CTE maps

98. 0.000 0.000 ↓ 0.0

Sort (cost=1,931,705.59..1,931,711.50 rows=2,363 width=276) (actual rows= loops=)

  • Sort Key: locations.user_id, locations.date
99. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..1,931,573.19 rows=2,363 width=276) (actual rows= loops=)

  • Join Filter: ((areas.the_geom ~ locations.coordinate) AND _st_contains(areas.the_geom, locations.coordinate))
100. 0.000 0.000 ↓ 0.0

CTE Scan on areas (cost=0.00..8.76 rows=438 width=76) (actual rows= loops=)

101. 0.000 0.000 ↓ 0.0

CTE Scan on locations (cost=0.00..323.64 rows=16,182 width=200) (actual rows= loops=)

102.          

CTE frequent

103. 0.000 0.000 ↓ 0.0

CTE Scan on maps (cost=0.00..47.26 rows=2,363 width=192) (actual rows= loops=)

104.          

CTE account_user

105. 0.000 0.000 ↓ 0.0

Seq Scan on accounts_user (cost=0.00..65,301.98 rows=241,446 width=971) (actual rows= loops=)

106.          

CTE device_info

107. 0.000 0.000 ↓ 0.0

Unique (cost=72,415.23..74,016.14 rows=180,707 width=84) (actual rows= loops=)

108. 0.000 0.000 ↓ 0.0

Sort (cost=72,415.23..73,215.69 rows=320,183 width=84) (actual rows= loops=)

  • Sort Key: deviceinfo_systeminfo.user_id, deviceinfo_systeminfo.date_created DESC
109. 0.000 0.000 ↓ 0.0

Seq Scan on deviceinfo_systeminfo (cost=0.00..27,813.84 rows=320,183 width=84) (actual rows= loops=)

110.          

CTE installed_apps

111. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=1,214,396.63..1,281,916.33 rows=92,090 width=12) (actual rows= loops=)

  • Group Key: deviceinfo_installedapplicationsinfo.user_id
112. 0.000 0.000 ↓ 0.0

Gather Merge (cost=1,214,396.63..1,280,074.53 rows=184,180 width=12) (actual rows= loops=)

  • Workers Planned: 2
113. 0.000 0.000 ↓ 0.0

Partial GroupAggregate (cost=1,213,396.60..1,257,815.57 rows=92,090 width=12) (actual rows= loops=)

  • Group Key: deviceinfo_installedapplicationsinfo.user_id
114. 0.000 0.000 ↓ 0.0

Sort (cost=1,213,396.60..1,227,895.96 rows=5,799,742 width=30) (actual rows= loops=)

  • Sort Key: deviceinfo_installedapplicationsinfo.user_id
115. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on deviceinfo_installedapplicationsinfo (cost=0.00..284,337.42 rows=5,799,742 width=30) (actual rows= loops=)

116.          

CTE number_sms

117. 0.000 0.000 ↓ 0.0

Subquery Scan on a_5 (cost=29,596.34..29,612.38 rows=200 width=24) (actual rows= loops=)

118. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=29,596.34..29,610.38 rows=200 width=32) (actual rows= loops=)

  • Group Key: smsinfo_1.user_id, loan_info.loan
119. 0.000 0.000 ↓ 0.0

Sort (cost=29,596.34..29,598.06 rows=688 width=12) (actual rows= loops=)

  • Sort Key: smsinfo_1.user_id, loan_info.loan
120. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.04..29,563.92 rows=688 width=12) (actual rows= loops=)

  • Hash Cond: (smsinfo_1.user_id = loan_info.user_id)
  • Join Filter: ((smsinfo_1.date < (loan_info.date_of_approval)::date) AND (smsinfo_1.date > ((loan_info.date_of_approval)::date - 30)))
121. 0.000 0.000 ↓ 0.0

CTE Scan on smsinfo smsinfo_1 (cost=0.00..24,778.52 rows=1,238,926 width=16) (actual rows= loops=)

122. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=16) (actual rows= loops=)

123. 0.000 0.000 ↓ 0.0

CTE Scan on loan_info (cost=0.00..0.02 rows=1 width=16) (actual rows= loops=)

  • Filter: (cardinal = 1)
124.          

CTE app_competitor

125. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=1,174,748.63..1,495,664.12 rows=92,090 width=204) (actual rows= loops=)

  • Group Key: deviceinfo_installedapplicationsinfo_1.user_id
126. 0.000 0.000 ↓ 0.0

Gather Merge (cost=1,174,748.63..1,486,915.57 rows=184,180 width=52) (actual rows= loops=)

  • Workers Planned: 2
127. 0.000 0.000 ↓ 0.0

Partial GroupAggregate (cost=1,173,748.60..1,464,656.60 rows=92,090 width=52) (actual rows= loops=)

  • Group Key: deviceinfo_installedapplicationsinfo_1.user_id
128. 0.000 0.000 ↓ 0.0

Sort (cost=1,173,748.60..1,188,247.96 rows=5,799,742 width=17) (actual rows= loops=)

  • Sort Key: deviceinfo_installedapplicationsinfo_1.user_id
129. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on deviceinfo_installedapplicationsinfo deviceinfo_installedapplicationsinfo_1 (cost=0.00..284,337.42 rows=5,799,742 width=17) (actual rows= loops=)

130.          

CTE customer_competitor

131. 0.000 0.000 ↓ 0.0

HashAggregate (cost=46,459.73..46,463.23 rows=200 width=100) (actual rows= loops=)

  • Group Key: smsinfo_2.user_id
132. 0.000 0.000 ↓ 0.0

CTE Scan on smsinfo smsinfo_2 (cost=0.00..24,778.52 rows=1,238,926 width=62) (actual rows= loops=)

133.          

CTE sms_balance

134. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=6,378.81..6,382.85 rows=200 width=36) (actual rows= loops=)

  • Group Key: n_1.id
135. 0.000 0.000 ↓ 0.0

Sort (cost=6,378.81..6,379.32 rows=206 width=36) (actual rows= loops=)

  • Sort Key: n_1.id
136. 0.000 0.000 ↓ 0.0

Subquery Scan on n_1 (cost=6,366.77..6,370.89 rows=206 width=36) (actual rows= loops=)

137. 0.000 0.000 ↓ 0.0

Unique (cost=6,366.77..6,368.83 rows=206 width=84) (actual rows= loops=)

138. 0.000 0.000 ↓ 0.0

Sort (cost=6,366.77..6,367.29 rows=206 width=84) (actual rows= loops=)

  • Sort Key: q_1.user_id, q_1.date_calendar, q_1.id
139. 0.000 0.000 ↓ 0.0

WindowAgg (cost=6,354.22..6,358.86 rows=206 width=84) (actual rows= loops=)

140. 0.000 0.000 ↓ 0.0

Sort (cost=6,354.22..6,354.74 rows=206 width=52) (actual rows= loops=)

  • Sort Key: q_1.value_partition, q_1.user_id, q_1.date_calendar
141. 0.000 0.000 ↓ 0.0

Subquery Scan on q_1 (cost=6,340.12..6,346.30 rows=206 width=52) (actual rows= loops=)

142. 0.000 0.000 ↓ 0.0

WindowAgg (cost=6,340.12..6,344.24 rows=206 width=60) (actual rows= loops=)

143. 0.000 0.000 ↓ 0.0

Sort (cost=6,340.12..6,340.64 rows=206 width=44) (actual rows= loops=)

  • Sort Key: q_2.user_id, q_2.date_calendar
144. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..6,332.21 rows=206 width=44) (actual rows= loops=)

145. 0.000 0.000 ↓ 0.0

CTE Scan on daily_balance q_2 (cost=0.00..4.00 rows=200 width=40) (actual rows= loops=)

146. 0.000 0.000 ↓ 0.0

Index Scan using loans_loaninfo_e8701ad4 on loans_loaninfo t1_2 (cost=0.42..31.63 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (user_id = q_2.user_id)
  • Filter: ((q_2.date_calendar < (date_of_application)::date) AND (q_2.date_calendar > ((date_of_application)::date - '60 days'::interval)))
147.          

CTE frequent_location

148. 0.000 0.000 ↓ 0.0

Unique (cost=76.64..77.82 rows=236 width=44) (actual rows= loops=)

149. 0.000 0.000 ↓ 0.0

Sort (cost=76.64..77.23 rows=236 width=44) (actual rows= loops=)

  • Sort Key: frequent.loan_id, (count(frequent.date)) DESC
150. 0.000 0.000 ↓ 0.0

HashAggregate (cost=64.98..67.34 rows=236 width=44) (actual rows= loops=)

  • Group Key: frequent.loan_id, frequent.name
151. 0.000 0.000 ↓ 0.0

CTE Scan on frequent (cost=0.00..47.26 rows=2,363 width=44) (actual rows= loops=)

152.          

CTE frequent_work

153. 0.000 0.000 ↓ 0.0

Unique (cost=53.84..53.90 rows=12 width=44) (actual rows= loops=)

154. 0.000 0.000 ↓ 0.0

Sort (cost=53.84..53.87 rows=12 width=44) (actual rows= loops=)

  • Sort Key: frequent_1.loan_id, (count(frequent_1.date)) DESC
155. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=53.38..53.62 rows=12 width=44) (actual rows= loops=)

  • Group Key: frequent_1.loan_id, frequent_1.name
156. 0.000 0.000 ↓ 0.0

Sort (cost=53.38..53.41 rows=12 width=44) (actual rows= loops=)

  • Sort Key: frequent_1.loan_id, frequent_1.name
157. 0.000 0.000 ↓ 0.0

CTE Scan on frequent frequent_1 (cost=0.00..53.17 rows=12 width=44) (actual rows= loops=)

  • Filter: (place = 'WORK'::text)
158.          

CTE frequent_home

159. 0.000 0.000 ↓ 0.0

Unique (cost=53.84..53.90 rows=12 width=44) (actual rows= loops=)

160. 0.000 0.000 ↓ 0.0

Sort (cost=53.84..53.87 rows=12 width=44) (actual rows= loops=)

  • Sort Key: frequent_2.loan_id, (count(frequent_2.date)) DESC
161. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=53.38..53.62 rows=12 width=44) (actual rows= loops=)

  • Group Key: frequent_2.loan_id, frequent_2.name
162. 0.000 0.000 ↓ 0.0

Sort (cost=53.38..53.41 rows=12 width=44) (actual rows= loops=)

  • Sort Key: frequent_2.loan_id, frequent_2.name
163. 0.000 0.000 ↓ 0.0

CTE Scan on frequent frequent_2 (cost=0.00..53.17 rows=12 width=44) (actual rows= loops=)

  • Filter: (place = 'HOME'::text)
164.          

CTE sms_last_month

165. 0.000 0.000 ↓ 0.0

Sort (cost=2,046,751.60..2,046,754.79 rows=1,273 width=32) (actual rows= loops=)

  • Sort Key: smsinfo_3.user_id, tl.id, tl.date_of_application
166. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=2,038,554.67..2,046,685.96 rows=1,273 width=32) (actual rows= loops=)

  • Group Key: smsinfo_3.user_id, tl.id
167. 0.000 0.000 ↓ 0.0

Sort (cost=2,038,554.67..2,038,557.85 rows=1,273 width=106) (actual rows= loops=)

  • Sort Key: smsinfo_3.user_id, tl.id
168. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..2,038,489.02 rows=1,273 width=106) (actual rows= loops=)

169. 0.000 0.000 ↓ 0.0

CTE Scan on smsinfo smsinfo_3 (cost=0.00..2,007,060.12 rows=1,239 width=102) (actual rows= loops=)

  • Filter: (((address)::text = ANY ('{Diamond,GTBank,FirstBank,AccessBank,StanbicIBTC,ECOBANK,ZENITHBANK,FCMB,UBA,UNIONBANK}'::text[])) AND (CASE WHEN ((address)::text = 'Diamond'::text) THEN CASE WHEN (body ~~ '%Debit%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'Diamond'::text) AND (body ~~ '%Alert%'::text) AND (body ~~ '%Debit%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'Diamond'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'Diamond'::text) AND (body ~~ '%Alert%'::text) AND (body ~~ '%Credit%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'Diamond'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'GTBank'::text) THEN CASE WHEN (body ~~ '%Dr%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'GTBank'::text) AND (body ~~ '%Acct%'::text) AND (body ~~ '%Dr%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'GTBank'::text) AND (body ~~ '%Acct%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'GTBank'::text) AND (body ~~ '%Acct%'::text) AND (body ~~ '%Cr%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'Cr'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'GTBank'::text) AND (body ~~ '%Acct%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'Cr'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'FirstBank'::text) THEN CASE WHEN (body ~~ '%Debited%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FirstBank'::text) AND (body ~~ '%Acct%'::text) AND (body ~~ '%Debited%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'On'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FirstBank'::text) AND (body ~~ '%Acct%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'On'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'O'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FirstBank'::text) AND (body ~~ '%Acct%'::text) AND (body ~~ '%Credited%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'On'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'Cr'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FirstBank'::text) AND (body ~~ '%Acct%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'On'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'Cr'::text, ''::text)), 'O'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'AccessBank'::text) THEN CASE WHEN (body ~~ '%Dr%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'AccessBank'::text) AND (body ~~ '%Amt%'::text) AND (body ~~ '%Dr%'::text) AND (body !~~ '%MonthEnd%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'AccessBank'::text) AND (body ~~ '%Amt%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'AccessBank'::text) AND (body ~~ '%Amt%'::text) AND (body ~~ '%Cr%'::text) AND (body !~~ '%MonthEnd%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'AccessBank'::text) AND (body ~~ '%Amt%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'C'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'StanbicIBTC'::text) THEN CASE WHEN (body ~~ '%debit%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'StanbicIBTC'::text) AND (body ~~ '%A/C%'::text) AND (body ~~ '%debit%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'occurred'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'StanbicIBTC'::text) AND (body ~~ '%A/C%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'occurred'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'occurred'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'StanbicIBTC'::text) AND (body ~~ '%A/C%'::text) AND (body ~~ '%credit%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'occurred'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'Cr'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'StanbicIBTC'::text) AND (body ~~ '%A/C%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'occurred'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'Cr'::text, ''::text)), 'occurred'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'ECOBANK'::text) THEN CASE WHEN (body ~~ '%Dr%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text) AND ((body ~~ '%Dr%'::text) OR (body ~~ '%debited%'::text))) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'D'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'Dr'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text))) WHEN (body ~~ '%debited%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text) AND (body ~~ '%debited%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'a/c'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'a/c'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'debited'::text)), '\r|\n'::text, ''::text, 'g'::text))) WHEN (body ~~ '%credited%'::text) THEN btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text) AND (body ~~ '%credited%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'a/c'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'a/c'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'credited'::text)), '\r|\n'::text, ''::text, 'g'::text)) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text) AND ((body ~~ '%Cr%'::text) OR (body ~~ '%credited%'::text))) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'C'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'Cr'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'C'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'ZENITHBANK'::text) THEN CASE WHEN (body ~~ '%DR%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ZENITHBANK'::text) AND (body ~~ '%AlertZ%'::text)) THEN ""substring""(body, (strpos(body, 'DR Amt:'::text) + 7), strpos(body, 'REF'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ZENITHBANK'::text) AND (body ~~ '%AlertZ%'::text)) THEN ""substring""(body, (strpos(body, 'DR Amt:'::text) + 7), strpos(body, 'REF'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'Br'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ZENITHBANK'::text) AND (body ~~ '%AlertZ%'::text)) THEN ""substring""(body, (strpos(body, 'CR Amt:'::text) + 7), strpos(body, 'REF'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ZENITHBANK'::text) AND (body ~~ '%AlertZ%'::text)) THEN ""substring""(body, (strpos(body, 'CR Amt:'::text) + 7), strpos(body, 'REF'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'Br'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'FCMB'::text) THEN CASE WHEN (body ~~ '%Debit%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FCMB'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'AMT:'::text) + 4), strpos(body, 'BAL'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FCMB'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'AMT:'::text) + 4), strpos(body, 'BAL'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'BAL:'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FCMB'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'AMT:'::text) + 4), strpos(body, 'BAL'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FCMB'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'AMT:'::text) + 4), strpos(body, 'BAL'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'BAL:'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'UBA'::text) THEN CASE WHEN (body ~~ '%Debit%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Txn%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Txn%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 'Desc'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Txn%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Txn%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 'Desc'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'UBA'::text) THEN CASE WHEN (body ~~ '%Debit%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Rmks'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Rmks'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 'Rmks'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Rmks'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Rmks'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 'Rmks'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'UNIONBANK'::text) THEN CASE WHEN (body ~~ '%Debit%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UNIONBANK'::text) AND (body ~~ '%Amt%'::text) AND (body ~~ '%Debit%'::text) AND (body !~~ '%MonthEnd%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UNIONBANK'::text) AND (body ~~ '%Amt%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UNIONBANK'::text) AND (body ~~ '%Amt%'::text) AND (body ~~ '%Credit%'::text) AND (body !~~ '%MonthEnd%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UNIONBANK'::text) AND (body ~~ '%Amt%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text)) END ELSE NULL::text END ~ '^-?([0-9]+[.]?[0-9]*|[.][0-9]+)$'::text))
170. 0.000 0.000 ↓ 0.0

Index Scan using loans_loaninfo_e8701ad4 on loans_loaninfo tl (cost=0.42..25.36 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (user_id = smsinfo_3.user_id)
  • Filter: ((smsinfo_3.date < (date_of_application)::date) AND (smsinfo_3.date >= ((date_of_application)::date - '30 days'::interval)))
171.          

CTE sms_last_two_months

172. 0.000 0.000 ↓ 0.0

Sort (cost=2,046,779.48..2,046,782.66 rows=1,273 width=32) (actual rows= loops=)

  • Sort Key: smsinfo_4.user_id, tl_1.id, tl_1.date_of_application
173. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=2,038,582.55..2,046,713.83 rows=1,273 width=32) (actual rows= loops=)

  • Group Key: smsinfo_4.user_id, tl_1.id
174. 0.000 0.000 ↓ 0.0

Sort (cost=2,038,582.55..2,038,585.73 rows=1,273 width=106) (actual rows= loops=)

  • Sort Key: smsinfo_4.user_id, tl_1.id
175. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..2,038,516.90 rows=1,273 width=106) (actual rows= loops=)

176. 0.000 0.000 ↓ 0.0

CTE Scan on smsinfo smsinfo_4 (cost=0.00..2,007,060.12 rows=1,239 width=102) (actual rows= loops=)

  • Filter: (((address)::text = ANY ('{Diamond,GTBank,FirstBank,AccessBank,StanbicIBTC,ECOBANK,ZENITHBANK,FCMB,UBA,UNIONBANK}'::text[])) AND (CASE WHEN ((address)::text = 'Diamond'::text) THEN CASE WHEN (body ~~ '%Debit%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'Diamond'::text) AND (body ~~ '%Alert%'::text) AND (body ~~ '%Debit%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'Diamond'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'Diamond'::text) AND (body ~~ '%Alert%'::text) AND (body ~~ '%Credit%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'Diamond'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'GTBank'::text) THEN CASE WHEN (body ~~ '%Dr%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'GTBank'::text) AND (body ~~ '%Acct%'::text) AND (body ~~ '%Dr%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'GTBank'::text) AND (body ~~ '%Acct%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'GTBank'::text) AND (body ~~ '%Acct%'::text) AND (body ~~ '%Cr%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'Cr'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'GTBank'::text) AND (body ~~ '%Acct%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'Cr'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'FirstBank'::text) THEN CASE WHEN (body ~~ '%Debited%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FirstBank'::text) AND (body ~~ '%Acct%'::text) AND (body ~~ '%Debited%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'On'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FirstBank'::text) AND (body ~~ '%Acct%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'On'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'O'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FirstBank'::text) AND (body ~~ '%Acct%'::text) AND (body ~~ '%Credited%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'On'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'Cr'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FirstBank'::text) AND (body ~~ '%Acct%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'On'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'Cr'::text, ''::text)), 'O'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'AccessBank'::text) THEN CASE WHEN (body ~~ '%Dr%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'AccessBank'::text) AND (body ~~ '%Amt%'::text) AND (body ~~ '%Dr%'::text) AND (body !~~ '%MonthEnd%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'AccessBank'::text) AND (body ~~ '%Amt%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'AccessBank'::text) AND (body ~~ '%Amt%'::text) AND (body ~~ '%Cr%'::text) AND (body !~~ '%MonthEnd%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'AccessBank'::text) AND (body ~~ '%Amt%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'C'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'StanbicIBTC'::text) THEN CASE WHEN (body ~~ '%debit%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'StanbicIBTC'::text) AND (body ~~ '%A/C%'::text) AND (body ~~ '%debit%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'occurred'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'StanbicIBTC'::text) AND (body ~~ '%A/C%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'occurred'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'occurred'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'StanbicIBTC'::text) AND (body ~~ '%A/C%'::text) AND (body ~~ '%credit%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'occurred'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'Cr'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'StanbicIBTC'::text) AND (body ~~ '%A/C%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'occurred'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'Cr'::text, ''::text)), 'occurred'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'ECOBANK'::text) THEN CASE WHEN (body ~~ '%Dr%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text) AND ((body ~~ '%Dr%'::text) OR (body ~~ '%debited%'::text))) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'D'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'Dr'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text))) WHEN (body ~~ '%debited%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text) AND (body ~~ '%debited%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'a/c'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'a/c'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'debited'::text)), '\r|\n'::text, ''::text, 'g'::text))) WHEN (body ~~ '%credited%'::text) THEN btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text) AND (body ~~ '%credited%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'a/c'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'a/c'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'credited'::text)), '\r|\n'::text, ''::text, 'g'::text)) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text) AND ((body ~~ '%Cr%'::text) OR (body ~~ '%credited%'::text))) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'C'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ECOBANK'::text) AND (body ~~ '%a/c%'::text)) THEN ""substring""(body, (strpos(body, 'NGN'::text) + 3), strpos(body, 'Cr'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'C'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'ZENITHBANK'::text) THEN CASE WHEN (body ~~ '%DR%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ZENITHBANK'::text) AND (body ~~ '%AlertZ%'::text)) THEN ""substring""(body, (strpos(body, 'DR Amt:'::text) + 7), strpos(body, 'REF'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ZENITHBANK'::text) AND (body ~~ '%AlertZ%'::text)) THEN ""substring""(body, (strpos(body, 'DR Amt:'::text) + 7), strpos(body, 'REF'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'Br'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ZENITHBANK'::text) AND (body ~~ '%AlertZ%'::text)) THEN ""substring""(body, (strpos(body, 'CR Amt:'::text) + 7), strpos(body, 'REF'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'ZENITHBANK'::text) AND (body ~~ '%AlertZ%'::text)) THEN ""substring""(body, (strpos(body, 'CR Amt:'::text) + 7), strpos(body, 'REF'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'Br'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'FCMB'::text) THEN CASE WHEN (body ~~ '%Debit%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FCMB'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'AMT:'::text) + 4), strpos(body, 'BAL'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FCMB'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'AMT:'::text) + 4), strpos(body, 'BAL'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'BAL:'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FCMB'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'AMT:'::text) + 4), strpos(body, 'BAL'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'FCMB'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'AMT:'::text) + 4), strpos(body, 'BAL'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'BAL:'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'UBA'::text) THEN CASE WHEN (body ~~ '%Debit%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Txn%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Txn%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 'Desc'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Txn%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Txn%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 'Desc'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'UBA'::text) THEN CASE WHEN (body ~~ '%Debit%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Rmks'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Rmks'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 'Rmks'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Rmks'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UBA'::text) AND (body ~~ '%Alert%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Rmks'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'N'::text, ''::text)), 'Rmks'::text)), '\r|\n'::text, ''::text, 'g'::text)) END WHEN ((address)::text = 'UNIONBANK'::text) THEN CASE WHEN (body ~~ '%Debit%'::text) THEN ('-'::text || btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UNIONBANK'::text) AND (body ~~ '%Amt%'::text) AND (body ~~ '%Debit%'::text) AND (body !~~ '%MonthEnd%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UNIONBANK'::text) AND (body ~~ '%Amt%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text))) ELSE btrim(regexp_replace(""substring""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UNIONBANK'::text) AND (body ~~ '%Amt%'::text) AND (body ~~ '%Credit%'::text) AND (body !~~ '%MonthEnd%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 0, ""position""(btrim(replace(replace(replace(CASE WHEN (((address)::text = 'UNIONBANK'::text) AND (body ~~ '%Amt%'::text)) THEN ""substring""(body, (strpos(body, 'Amt:'::text) + 4), strpos(body, 'Desc'::text)) ELSE 'T'::text END, ','::text, ''::text), 'NGN'::text, ''::text), 'CR'::text, ''::text)), 'D'::text)), '\r|\n'::text, ''::text, 'g'::text)) END ELSE NULL::text END ~ '^-?([0-9]+[.]?[0-9]*|[.][0-9]+)$'::text))
177. 0.000 0.000 ↓ 0.0

Index Scan using loans_loaninfo_e8701ad4 on loans_loaninfo tl_1 (cost=0.42..25.38 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (user_id = smsinfo_4.user_id)
  • Filter: ((smsinfo_4.date < ((date_of_application)::date - '30 days'::interval)) AND (smsinfo_4.date >= ((date_of_application)::date - '60 days'::interval)))
178. 0.000 0.000 ↓ 0.0

Sort (cost=91.11..94.29 rows=1,273 width=20) (actual rows= loops=)

  • Sort Key: s.loan_number
179. 0.000 0.000 ↓ 0.0

CTE Scan on sms_last_two_months s (cost=0.00..25.46 rows=1,273 width=20) (actual rows= loops=)

180. 0.000 0.000 ↓ 0.0

Materialize (cost=733,081,479,188.95..156,817,871,882,016.09 rows=5,278,544,141,136,346 width=1,136) (actual rows= loops=)

181. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=733,081,479,188.95..143,621,511,529,175.22 rows=5,278,544,141,136,346 width=1,136) (actual rows= loops=)

  • Merge Cond: (b.loan = r.loan_number)
182. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=733,081,479,097.84..62,370,079,914,342.00 rows=829,307,799,078,766 width=1,120) (actual rows= loops=)

  • Merge Cond: (b.loan = o.loan_id)
183. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=733,081,479,083.82..48,173,454,879,250.80 rows=702,803,219,558,276 width=1,088) (actual rows= loops=)

  • Merge Cond: (b.loan = q.loan_id)
184. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=733,081,479,083.37..35,874,398,536,980.51 rows=702,803,219,558,276 width=1,056) (actual rows= loops=)

  • Merge Cond: (b.loan = p.loan_id)
185. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=733,081,479,082.91..23,575,342,194,710.22 rows=702,803,219,558,276 width=1,024) (actual rows= loops=)

  • Merge Cond: (b.loan = n.id)
186. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=733,081,479,071.27..11,276,285,852,428.75 rows=702,803,219,558,276 width=992) (actual rows= loops=)

  • Merge Cond: (b.loan = m.loan_id)
187. 0.000 0.000 ↓ 0.0

Sort (cost=733,081,393,965.44..733,659,433,197.22 rows=231,215,692,709 width=968) (actual rows= loops=)

  • Sort Key: b.loan
188. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=136,045.28..3,468,919,045.33 rows=231,215,692,709 width=968) (actual rows= loops=)

  • Merge Cond: (b.user_id = a.id)
189. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=11,658.13..14,837.53 rows=211,807 width=530) (actual rows= loops=)

  • Merge Cond: (b.user_id = e.user_id)
190. 0.000 0.000 ↓ 0.0

Sort (cost=2,223.16..2,224.31 rows=460 width=522) (actual rows= loops=)

  • Sort Key: b.user_id
191. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=11.08..2,202.82 rows=460 width=522) (actual rows= loops=)

  • Hash Cond: (k.user_id = b.user_id)
192. 0.000 0.000 ↓ 0.0

CTE Scan on app_competitor k (cost=0.00..1,841.80 rows=92,090 width=204) (actual rows= loops=)

193. 0.000 0.000 ↓ 0.0

Hash (cost=11.06..11.06 rows=1 width=322) (actual rows= loops=)

194. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=4.80..11.06 rows=1 width=322) (actual rows= loops=)

  • Hash Cond: ((to_char(j.loan_id, '999999'::text) || to_char(j.user_id, '999999'::text)) = (to_char(b.loan, '999999'::text) || to_char(b.user_id, '999999'::text)))
195. 0.000 0.000 ↓ 0.0

CTE Scan on number_sms j (cost=0.00..4.00 rows=200 width=24) (actual rows= loops=)

196. 0.000 0.000 ↓ 0.0

Hash (cost=4.79..4.79 rows=1 width=306) (actual rows= loops=)

197. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=0.03..4.79 rows=1 width=306) (actual rows= loops=)

  • Hash Cond: (l.user_id = b.user_id)
198. 0.000 0.000 ↓ 0.0

CTE Scan on customer_competitor l (cost=0.00..4.00 rows=200 width=100) (actual rows= loops=)

199. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=210) (actual rows= loops=)

200. 0.000 0.000 ↓ 0.0

CTE Scan on loan_info b (cost=0.00..0.02 rows=1 width=210) (actual rows= loops=)

201. 0.000 0.000 ↓ 0.0

Sort (cost=9,434.97..9,665.19 rows=92,090 width=12) (actual rows= loops=)

  • Sort Key: e.user_id
202. 0.000 0.000 ↓ 0.0

CTE Scan on installed_apps e (cost=0.00..1,841.80 rows=92,090 width=12) (actual rows= loops=)

203. 0.000 0.000 ↓ 0.0

Materialize (cost=124,387.15..3,943,757.11 rows=218,154,912 width=438) (actual rows= loops=)

204. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=124,387.15..3,398,369.83 rows=218,154,912 width=438) (actual rows= loops=)

  • Merge Cond: (a.id = d.user_id)
205. 0.000 0.000 ↓ 0.0

Sort (cost=90,787.81..91,391.43 rows=241,446 width=284) (actual rows= loops=)

  • Sort Key: a.id
206. 0.000 0.000 ↓ 0.0

CTE Scan on account_user a (cost=0.00..4,828.92 rows=241,446 width=284) (actual rows= loops=)

207. 0.000 0.000 ↓ 0.0

Materialize (cost=33,599.34..34,502.87 rows=180,707 width=158) (actual rows= loops=)

208. 0.000 0.000 ↓ 0.0

Sort (cost=33,599.34..34,051.10 rows=180,707 width=158) (actual rows= loops=)

  • Sort Key: d.user_id
209. 0.000 0.000 ↓ 0.0

CTE Scan on device_info d (cost=0.00..3,614.14 rows=180,707 width=158) (actual rows= loops=)

210. 0.000 0.000 ↓ 0.0

Materialize (cost=85,105.82..88,145.42 rows=607,920 width=28) (actual rows= loops=)

211. 0.000 0.000 ↓ 0.0

Sort (cost=85,105.82..86,625.62 rows=607,920 width=28) (actual rows= loops=)

  • Sort Key: m.loan_id
212. 0.000 0.000 ↓ 0.0

CTE Scan on words m (cost=0.00..12,158.40 rows=607,920 width=28) (actual rows= loops=)

213. 0.000 0.000 ↓ 0.0

Sort (cost=11.64..12.14 rows=200 width=36) (actual rows= loops=)

  • Sort Key: n.id
214. 0.000 0.000 ↓ 0.0

CTE Scan on sms_balance n (cost=0.00..4.00 rows=200 width=36) (actual rows= loops=)

215. 0.000 0.000 ↓ 0.0

Sort (cost=0.46..0.49 rows=12 width=36) (actual rows= loops=)

  • Sort Key: p.loan_id
216. 0.000 0.000 ↓ 0.0

CTE Scan on frequent_work p (cost=0.00..0.24 rows=12 width=36) (actual rows= loops=)

217. 0.000 0.000 ↓ 0.0

Sort (cost=0.46..0.49 rows=12 width=36) (actual rows= loops=)

  • Sort Key: q.loan_id
218. 0.000 0.000 ↓ 0.0

CTE Scan on frequent_home q (cost=0.00..0.24 rows=12 width=36) (actual rows= loops=)

219. 0.000 0.000 ↓ 0.0

Sort (cost=14.02..14.61 rows=236 width=36) (actual rows= loops=)

  • Sort Key: o.loan_id
220. 0.000 0.000 ↓ 0.0

CTE Scan on frequent_location o (cost=0.00..4.72 rows=236 width=36) (actual rows= loops=)

221. 0.000 0.000 ↓ 0.0

Sort (cost=91.11..94.29 rows=1,273 width=20) (actual rows= loops=)

  • Sort Key: r.loan_number
222. 0.000 0.000 ↓ 0.0

CTE Scan on sms_last_month r (cost=0.00..25.46 rows=1,273 width=20) (actual rows= loops=)