explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CLnr : big query

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.727 105,665.636 ↑ 560,822.4 471 1

Sort (cost=451,773,439.54..452,433,807.90 rows=264,147,345 width=659) (actual time=105,665.529..105,665.636 rows=471 loops=1)

  • Sort Key: l.id, ae.adjustment_date
  • Sort Method: quicksort Memory: 150kB
2.          

CTE adjustment_events

3. 9,197.190 9,197.190 ↑ 2.1 471 1

Function Scan on fn_community_advantage_adjustment_events (cost=0.25..10.25 rows=1,000 width=145) (actual time=9,197.101..9,197.190 rows=471 loops=1)

4.          

CTE last_confirmed_community_advantage_loan

5. 0.286 3.365 ↑ 1.0 299 1

Unique (cost=218.96..221.12 rows=299 width=48) (actual time=2.838..3.365 rows=299 loops=1)

6. 0.803 3.079 ↓ 2.3 999 1

Sort (cost=218.96..220.04 rows=431 width=48) (actual time=2.837..3.079 rows=999 loops=1)

  • Sort Key: carl.loan_id, carr.report_date DESC, carl.id DESC
  • Sort Method: quicksort Memory: 103kB
7. 1.719 2.276 ↓ 2.3 999 1

Hash Join (cost=21.32..200.10 rows=431 width=48) (actual time=0.081..2.276 rows=999 loops=1)

  • Hash Cond: (carl.community_advantage_report_request_id = carr.id)
8. 0.537 0.537 ↑ 1.0 1,298 1

Seq Scan on community_advantage_report_loans carl (cost=0.00..160.98 rows=1,298 width=779) (actual time=0.015..0.537 rows=1,298 loops=1)

9. 0.007 0.020 ↑ 75.3 3 1

Hash (cost=18.50..18.50 rows=226 width=8) (actual time=0.020..0.020 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.013 0.013 ↑ 75.3 3 1

Seq Scan on community_advantage_report_requests carr (cost=0.00..18.50 rows=226 width=8) (actual time=0.010..0.013 rows=3 loops=1)

  • Filter: ((confirmed_at IS NOT NULL) AND (report_date < '2018-07-31'::date))
  • Rows Removed by Filter: 1
11.          

CTE last_reporting_period_info

12. 0.207 2,167.475 ↓ 1.0 308 1

Hash Full Join (cost=78,132.44..78,140.36 rows=299 width=40) (actual time=2,166.621..2,167.475 rows=308 loops=1)

  • Hash Cond: (lccal.loan_id = date_of_first_ld.loan_id)
13. 3.506 3.506 ↑ 1.0 299 1

CTE Scan on last_confirmed_community_advantage_loan lccal (cost=0.00..5.98 rows=299 width=40) (actual time=2.841..3.506 rows=299 loops=1)

14. 0.137 2,163.762 ↓ 5.6 308 1

Hash (cost=78,131.75..78,131.75 rows=55 width=40) (actual time=2,163.762..2,163.762 rows=308 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
15. 0.147 2,163.625 ↓ 5.6 308 1

Subquery Scan on date_of_first_ld (cost=78,130.93..78,131.75 rows=55 width=40) (actual time=2,136.313..2,163.625 rows=308 loops=1)

16. 12.240 2,163.478 ↓ 5.6 308 1

Unique (cost=78,130.93..78,131.20 rows=55 width=44) (actual time=2,136.311..2,163.478 rows=308 loops=1)

17. 80.545 2,151.238 ↓ 1,080.7 59,438 1

Sort (cost=78,130.93..78,131.07 rows=55 width=44) (actual time=2,136.308..2,151.238 rows=59,438 loops=1)

  • Sort Key: transactions.loan_id, transactions.id
  • Sort Method: external sort Disk: 1880kB
18. 109.276 2,070.693 ↓ 1,080.7 59,438 1

Hash Semi Join (cost=70,373.10..78,129.34 rows=55 width=44) (actual time=1,929.139..2,070.693 rows=59,438 loops=1)

  • Hash Cond: (transactions.id = x.id)
19. 18.500 1,707.297 ↓ 1,061.4 59,438 1

Hash Join (cost=52,450.61..60,205.40 rows=56 width=118) (actual time=1,674.491..1,707.297 rows=59,438 loops=1)

  • Hash Cond: (transactions.loan_id = lat.loan_id)
20. 19.535 19.535 ↓ 1.0 3,704 1

Seq Scan on transactions (cost=0.00..7,740.46 rows=3,672 width=86) (actual time=0.077..19.535 rows=3,704 loops=1)

  • Filter: ((date <= '2018-07-31'::date) AND ((code)::text = 'LD'::text))
  • Rows Removed by Filter: 78512
21. 5.834 1,669.262 ↓ 596.7 15,515 1

Hash (cost=52,450.29..52,450.29 rows=26 width=36) (actual time=1,669.262..1,669.262 rows=15,515 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 795kB
22. 7.248 1,663.428 ↓ 596.7 15,515 1

Subquery Scan on lat (cost=52,443.20..52,450.29 rows=26 width=36) (actual time=1,583.944..1,663.428 rows=15,515 loops=1)

23. 68.221 1,656.180 ↓ 596.7 15,515 1

Result (cost=52,443.20..52,450.03 rows=26 width=140) (actual time=1,583.942..1,656.180 rows=15,515 loops=1)

24. 12.903 1,587.959 ↓ 596.7 15,515 1

Sort (cost=52,443.20..52,443.27 rows=26 width=112) (actual time=1,583.877..1,587.959 rows=15,515 loops=1)

  • Sort Key: transactions_1.id
  • Sort Method: quicksort Memory: 1112kB
25. 17.438 1,575.056 ↓ 596.7 15,515 1

Nested Loop (cost=18,009.72..52,442.59 rows=26 width=112) (actual time=281.180..1,575.056 rows=15,515 loops=1)

26. 14.931 338.400 ↓ 7.4 15,834 1

Hash Semi Join (cost=18,009.47..25,698.29 rows=2,131 width=16) (actual time=263.489..338.400 rows=15,834 loops=1)

  • Hash Cond: (transactions_1.id = x_1.id)
27. 35.477 61.487 ↓ 7.5 16,312 1

Hash Join (cost=86.98..7,746.40 rows=2,167 width=12) (actual time=0.993..61.487 rows=16,312 loops=1)

  • Hash Cond: (transactions_1.loan_id = l_1.id)
28. 25.029 25.029 ↓ 1.0 82,216 1

Seq Scan on transactions transactions_1 (cost=0.00..7,329.64 rows=82,164 width=8) (actual time=0.001..25.029 rows=82,216 loops=1)

29. 0.112 0.981 ↓ 6.3 319 1

Hash (cost=86.34..86.34 rows=51 width=8) (actual time=0.981..0.981 rows=319 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
30. 0.869 0.869 ↓ 6.3 319 1

Seq Scan on loans l_1 (cost=0.00..86.34 rows=51 width=8) (actual time=0.007..0.869 rows=319 loops=1)

  • Filter: (is_community_advantage AND (community_advantage_approved_date IS NOT NULL))
  • Rows Removed by Filter: 1615
31. 28.478 261.982 ↑ 1.0 80,131 1

Hash (cost=16,912.52..16,912.52 rows=80,798 width=4) (actual time=261.982..261.982 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3842kB
32. 42.156 233.504 ↑ 1.0 80,131 1

Subquery Scan on x_1 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=106.959..233.504 rows=80,131 loops=1)

  • Filter: (((x_1.code)::text !~~ 'RV%'::text) AND ((COALESCE(x_1.next_code, ''::character varying))::text !~~ 'RV%'::text))
  • Rows Removed by Filter: 2085
33. 62.329 191.348 ↓ 1.0 82,216 1

WindowAgg (cost=14,036.78..15,680.06 rows=82,164 width=43) (actual time=106.943..191.348 rows=82,216 loops=1)

34. 95.295 129.019 ↓ 1.0 82,216 1

Sort (cost=14,036.78..14,242.19 rows=82,164 width=11) (actual time=106.915..129.019 rows=82,216 loops=1)

  • Sort Key: transactions_2.loan_id, transactions_2.id
  • Sort Method: external sort Disk: 1696kB
35. 33.724 33.724 ↓ 1.0 82,216 1

Seq Scan on transactions transactions_2 (cost=0.00..7,329.64 rows=82,164 width=11) (actual time=0.003..33.724 rows=82,216 loops=1)

36. 1,219.218 1,219.218 ↑ 5.0 1 15,834

Function Scan on fn_prior_transaction pt (cost=0.25..12.75 rows=5 width=4) (actual time=0.076..0.077 rows=1 loops=15,834)

  • Filter: (transactions_1.id = reference_transaction_id)
37. 28.703 254.120 ↑ 1.0 80,131 1

Hash (cost=16,912.52..16,912.52 rows=80,798 width=4) (actual time=254.120..254.120 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3842kB
38. 41.475 225.417 ↑ 1.0 80,131 1

Subquery Scan on x (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=99.313..225.417 rows=80,131 loops=1)

  • Filter: (((x.code)::text !~~ 'RV%'::text) AND ((COALESCE(x.next_code, ''::character varying))::text !~~ 'RV%'::text))
  • Rows Removed by Filter: 2085
39. 62.748 183.942 ↓ 1.0 82,216 1

WindowAgg (cost=14,036.78..15,680.06 rows=82,164 width=43) (actual time=99.300..183.942 rows=82,216 loops=1)

40. 87.434 121.194 ↓ 1.0 82,216 1

Sort (cost=14,036.78..14,242.19 rows=82,164 width=11) (actual time=99.284..121.194 rows=82,216 loops=1)

  • Sort Key: transactions_3.loan_id, transactions_3.id
  • Sort Method: external sort Disk: 1696kB
41. 33.760 33.760 ↓ 1.0 82,216 1

Seq Scan on transactions transactions_3 (cost=0.00..7,329.64 rows=82,164 width=11) (actual time=0.013..33.760 rows=82,216 loops=1)

42.          

CTE last_payment_before_adjustment_date

43. 3.437 374.588 ↑ 24.0 471 1

Unique (cost=31,011.10..31,095.73 rows=11,284 width=126) (actual time=368.079..374.588 rows=471 loops=1)

44. 17.880 371.151 ↓ 1.2 13,745 1

Sort (cost=31,011.10..31,039.31 rows=11,284 width=126) (actual time=368.076..371.151 rows=13,745 loops=1)

  • Sort Key: transactions_4.loan_id, ae_1.adjustment_date, transactions_4.id DESC
  • Sort Method: quicksort Memory: 2783kB
45. 8.628 353.271 ↓ 1.2 13,745 1

Hash Semi Join (cost=28,510.87..30,251.58 rows=11,284 width=126) (actual time=333.857..353.271 rows=13,745 loops=1)

  • Hash Cond: (transactions_4.id = x_2.id)
46. 11.439 89.883 ↓ 1.2 13,961 1

Hash Join (cost=10,588.38..12,173.43 rows=11,475 width=126) (actual time=78.578..89.883 rows=13,961 loops=1)

  • Hash Cond: (ae_1.loan_id = transactions_4.loan_id)
  • Join Filter: (transactions_4.date <= ae_1.adjustment_date)
  • Rows Removed by Join Filter: 1817
47. 0.172 0.172 ↑ 2.1 471 1

CTE Scan on adjustment_events ae_1 (cost=0.00..20.00 rows=1,000 width=44) (actual time=0.002..0.172 rows=471 loops=1)

48. 30.831 78.272 ↑ 1.0 64,110 1

Hash (cost=8,870.22..8,870.22 rows=65,613 width=86) (actual time=78.272..78.272 rows=64,110 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 4229kB
49. 47.441 47.441 ↑ 1.0 64,110 1

Seq Scan on transactions transactions_4 (cost=0.00..8,870.22 rows=65,613 width=86) (actual time=0.013..47.441 rows=64,110 loops=1)

  • Filter: ((code)::text = ANY ('{PM,PMI,PMP,PMM,PRE,LP,LPI,LPP,LPM,AT,ATI,ATP,ATM,SK,RC}'::text[]))
  • Rows Removed by Filter: 18106
50. 29.531 254.760 ↑ 1.0 80,131 1

Hash (cost=16,912.52..16,912.52 rows=80,798 width=4) (actual time=254.760..254.760 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3842kB
51. 41.752 225.229 ↑ 1.0 80,131 1

Subquery Scan on x_2 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=99.612..225.229 rows=80,131 loops=1)

  • Filter: (((x_2.code)::text !~~ 'RV%'::text) AND ((COALESCE(x_2.next_code, ''::character varying))::text !~~ 'RV%'::text))
  • Rows Removed by Filter: 2085
52. 61.793 183.477 ↓ 1.0 82,216 1

WindowAgg (cost=14,036.78..15,680.06 rows=82,164 width=43) (actual time=99.599..183.477 rows=82,216 loops=1)

53. 88.125 121.684 ↓ 1.0 82,216 1

Sort (cost=14,036.78..14,242.19 rows=82,164 width=11) (actual time=99.579..121.684 rows=82,216 loops=1)

  • Sort Key: transactions_5.loan_id, transactions_5.id
  • Sort Method: external sort Disk: 1696kB
54. 33.559 33.559 ↓ 1.0 82,216 1

Seq Scan on transactions transactions_5 (cost=0.00..7,329.64 rows=82,164 width=11) (actual time=0.008..33.559 rows=82,216 loops=1)

55.          

CTE last_transaction_before_adjustment_date

56. 5.659 436.879 ↓ 1.3 264 1

Unique (cost=31,133.71..31,204.36 rows=200 width=153) (actual time=423.658..436.879 rows=264 loops=1)

57. 48.744 431.220 ↓ 1.5 21,815 1

Sort (cost=31,133.71..31,169.04 rows=14,130 width=153) (actual time=423.656..431.220 rows=21,815 loops=1)

  • Sort Key: ae_2.transaction_id, transactions_6.payment_period DESC
  • Sort Method: external sort Disk: 3208kB
58. 13.891 382.476 ↓ 1.5 21,815 1

Hash Semi Join (cost=27,724.18..30,159.70 rows=14,130 width=153) (actual time=349.214..382.476 rows=21,815 loops=1)

  • Hash Cond: (transactions_6.id = x_3.id)
59. 20.027 114.766 ↓ 1.5 22,246 1

Hash Join (cost=9,801.69..12,042.29 rows=14,369 width=153) (actual time=94.882..114.766 rows=22,246 loops=1)

  • Hash Cond: (ae_2.loan_id = transactions_6.loan_id)
  • Join Filter: (transactions_6.date <= ae_2.adjustment_date)
  • Rows Removed by Join Filter: 2979
60. 0.184 0.184 ↑ 2.1 471 1

CTE Scan on adjustment_events ae_2 (cost=0.00..20.00 rows=1,000 width=44) (actual time=0.001..0.184 rows=471 loops=1)

61. 47.603 94.555 ↓ 1.0 82,216 1

Hash (cost=7,329.64..7,329.64 rows=82,164 width=113) (actual time=94.555..94.555 rows=82,216 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3305kB
62. 46.952 46.952 ↓ 1.0 82,216 1

Seq Scan on transactions transactions_6 (cost=0.00..7,329.64 rows=82,164 width=113) (actual time=0.013..46.952 rows=82,216 loops=1)

63. 28.052 253.819 ↑ 1.0 80,131 1

Hash (cost=16,912.52..16,912.52 rows=80,798 width=4) (actual time=253.819..253.819 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3842kB
64. 41.648 225.767 ↑ 1.0 80,131 1

Subquery Scan on x_3 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=99.658..225.767 rows=80,131 loops=1)

  • Filter: (((x_3.code)::text !~~ 'RV%'::text) AND ((COALESCE(x_3.next_code, ''::character varying))::text !~~ 'RV%'::text))
  • Rows Removed by Filter: 2085
65. 62.496 184.119 ↓ 1.0 82,216 1

WindowAgg (cost=14,036.78..15,680.06 rows=82,164 width=43) (actual time=99.646..184.119 rows=82,216 loops=1)

66. 88.086 121.623 ↓ 1.0 82,216 1

Sort (cost=14,036.78..14,242.19 rows=82,164 width=11) (actual time=99.620..121.623 rows=82,216 loops=1)

  • Sort Key: transactions_7.loan_id, transactions_7.id
  • Sort Method: external sort Disk: 1696kB
67. 33.537 33.537 ↓ 1.0 82,216 1

Seq Scan on transactions transactions_7 (cost=0.00..7,329.64 rows=82,164 width=11) (actual time=0.007..33.537 rows=82,216 loops=1)

68.          

CTE guaranteed_sub_loans

69. 0.190 4.554 ↑ 1.0 264 1

Unique (cost=236.26..238.31 rows=273 width=22) (actual time=4.254..4.554 rows=264 loops=1)

70. 0.354 4.364 ↓ 1.7 471 1

Sort (cost=236.26..236.95 rows=273 width=22) (actual time=4.253..4.364 rows=471 loops=1)

  • Sort Key: parents.id, ae_3.transaction_id
  • Sort Method: quicksort Memory: 61kB
71. 0.971 4.010 ↓ 1.7 471 1

Hash Join (cost=130.31..225.22 rows=273 width=22) (actual time=2.005..4.010 rows=471 loops=1)

  • Hash Cond: (children.participation_loan_id = parents.id)
72. 1.079 1.079 ↑ 1.0 1,613 1

Seq Scan on loans children (cost=0.00..86.34 rows=1,619 width=14) (actual time=0.014..1.079 rows=1,613 loops=1)

  • Filter: (NOT is_community_advantage_lender_loan)
  • Rows Removed by Filter: 321
73. 0.166 1.960 ↓ 2.9 471 1

Hash (cost=128.27..128.27 rows=163 width=44) (actual time=1.960..1.960 rows=471 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 64kB
74. 0.249 1.794 ↓ 2.9 471 1

Hash Left Join (cost=96.78..128.27 rows=163 width=44) (actual time=1.170..1.794 rows=471 loops=1)

  • Hash Cond: (ae_3.transaction_id = ltbad_1.transaction_id)
75. 0.263 1.362 ↓ 2.9 471 1

Hash Join (cost=90.28..115.66 rows=163 width=12) (actual time=0.978..1.362 rows=471 loops=1)

  • Hash Cond: (ae_3.loan_id = parents.id)
76. 0.136 0.136 ↑ 2.1 471 1

CTE Scan on adjustment_events ae_3 (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.003..0.136 rows=471 loops=1)

77. 0.110 0.963 ↓ 1.0 321 1

Hash (cost=86.34..86.34 rows=315 width=4) (actual time=0.963..0.963 rows=321 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
78. 0.853 0.853 ↓ 1.0 321 1

Seq Scan on loans parents (cost=0.00..86.34 rows=315 width=4) (actual time=0.004..0.853 rows=321 loops=1)

  • Filter: is_community_advantage
  • Rows Removed by Filter: 1613
79. 0.081 0.183 ↓ 1.3 264 1

Hash (cost=4.00..4.00 rows=200 width=36) (actual time=0.183..0.183 rows=264 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
80. 0.102 0.102 ↓ 1.3 264 1

CTE Scan on last_transaction_before_adjustment_date ltbad_1 (cost=0.00..4.00 rows=200 width=36) (actual time=0.001..0.102 rows=264 loops=1)

81.          

CTE indexed_adjustment_events_and_dates

82. 0.444 11,742.034 ↑ 3.2 471 1

WindowAgg (cost=1,073.31..1,219.35 rows=1,495 width=161) (actual time=11,740.379..11,742.034 rows=471 loops=1)

83. 0.406 11,741.590 ↑ 3.2 471 1

Merge Left Join (cost=1,073.31..1,193.18 rows=1,495 width=153) (actual time=11,740.369..11,741.590 rows=471 loops=1)

  • Merge Cond: (ae_4.loan_id = lrpi_1.loan_id)
84. 0.451 9,573.259 ↑ 2.1 471 1

Merge Left Join (cost=1,055.03..1,149.98 rows=1,000 width=149) (actual time=9,572.573..9,573.259 rows=471 loops=1)

  • Merge Cond: ((ae_4.loan_id = lpbad.loan_id) AND (ae_4.adjustment_date = lpbad.adjustment_date))
85. 0.268 9,197.697 ↑ 2.1 471 1

Sort (cost=69.83..72.33 rows=1,000 width=145) (actual time=9,197.580..9,197.697 rows=471 loops=1)

  • Sort Key: ae_4.loan_id, ae_4.adjustment_date
  • Sort Method: quicksort Memory: 76kB
86. 9,197.429 9,197.429 ↑ 2.1 471 1

CTE Scan on adjustment_events ae_4 (cost=0.00..20.00 rows=1,000 width=145) (actual time=9,197.104..9,197.429 rows=471 loops=1)

87. 0.254 375.111 ↑ 24.0 471 1

Sort (cost=985.21..1,013.42 rows=11,284 width=12) (actual time=374.986..375.111 rows=471 loops=1)

  • Sort Key: lpbad.loan_id, lpbad.adjustment_date
  • Sort Method: quicksort Memory: 47kB
88. 374.857 374.857 ↑ 24.0 471 1

CTE Scan on last_payment_before_adjustment_date lpbad (cost=0.00..225.68 rows=11,284 width=12) (actual time=368.082..374.857 rows=471 loops=1)

89. 0.259 2,167.925 ↓ 1.8 547 1

Sort (cost=18.27..19.02 rows=299 width=8) (actual time=2,167.792..2,167.925 rows=547 loops=1)

  • Sort Key: lrpi_1.loan_id
  • Sort Method: quicksort Memory: 39kB
90. 2,167.666 2,167.666 ↓ 1.0 308 1

CTE Scan on last_reporting_period_info lrpi_1 (cost=0.00..5.98 rows=299 width=8) (actual time=2,166.624..2,167.666 rows=308 loops=1)

91.          

CTE interest_rates_and_dates

92. 0.598 11,743.846 ↑ 3.2 471 1

Merge Left Join (cost=217.46..266.61 rows=1,495 width=93) (actual time=11,743.028..11,743.846 rows=471 loops=1)

  • Merge Cond: ((iaead1.loan_id = iaead2.loan_id) AND (iaead1.r = ((iaead2.r + 1))))
93. 0.333 11,742.791 ↑ 3.2 471 1

Sort (cost=108.73..112.47 rows=1,495 width=89) (actual time=11,742.688..11,742.791 rows=471 loops=1)

  • Sort Key: iaead1.loan_id, iaead1.r
  • Sort Method: quicksort Memory: 61kB
94. 11,742.458 11,742.458 ↑ 3.2 471 1

CTE Scan on indexed_adjustment_events_and_dates iaead1 (cost=0.00..29.90 rows=1,495 width=89) (actual time=11,740.382..11,742.458 rows=471 loops=1)

95. 0.276 0.457 ↑ 3.2 471 1

Sort (cost=108.73..112.47 rows=1,495 width=16) (actual time=0.329..0.457 rows=471 loops=1)

  • Sort Key: iaead2.loan_id, ((iaead2.r + 1))
  • Sort Method: quicksort Memory: 61kB
96. 0.181 0.181 ↑ 3.2 471 1

CTE Scan on indexed_adjustment_events_and_dates iaead2 (cost=0.00..29.90 rows=1,495 width=16) (actual time=0.004..0.181 rows=471 loops=1)

97.          

CTE total_disbursed_amount

98. 0.768 284.868 ↓ 2.4 471 1

GroupAggregate (cost=25,812.47..25,822.24 rows=200 width=44) (actual time=283.729..284.868 rows=471 loops=1)

  • Group Key: ae_5.transaction_id, ae_5.adjustment_type
99. 1.076 284.100 ↓ 2.5 1,675 1

Sort (cost=25,812.47..25,814.16 rows=677 width=44) (actual time=283.706..284.100 rows=1,675 loops=1)

  • Sort Key: ae_5.transaction_id, ae_5.adjustment_type
  • Sort Method: quicksort Memory: 179kB
100. 1.544 283.024 ↓ 2.5 1,675 1

Hash Semi Join (cost=25,506.74..25,780.64 rows=677 width=44) (actual time=281.076..283.024 rows=1,675 loops=1)

  • Hash Cond: (transactions_8.id = x_4.id)
101. 0.819 18.936 ↓ 2.4 1,675 1

Hash Join (cost=7,584.25..7,848.81 rows=688 width=48) (actual time=18.004..18.936 rows=1,675 loops=1)

  • Hash Cond: (ae_5.loan_id = transactions_8.loan_id)
  • Join Filter: (transactions_8.date <= ae_5.adjustment_date)
  • Rows Removed by Join Filter: 6
102. 0.133 0.133 ↑ 2.1 471 1

CTE Scan on adjustment_events ae_5 (cost=0.00..20.00 rows=1,000 width=44) (actual time=0.001..0.133 rows=471 loops=1)

103. 1.588 17.984 ↑ 1.0 3,811 1

Hash (cost=7,535.05..7,535.05 rows=3,936 width=20) (actual time=17.984..17.984 rows=3,811 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 226kB
104. 16.396 16.396 ↑ 1.0 3,811 1

Seq Scan on transactions transactions_8 (cost=0.00..7,535.05 rows=3,936 width=20) (actual time=0.008..16.396 rows=3,811 loops=1)

  • Filter: ((code)::text = 'LD'::text)
  • Rows Removed by Filter: 78405
105. 29.329 262.544 ↑ 1.0 80,131 1

Hash (cost=16,912.52..16,912.52 rows=80,798 width=4) (actual time=262.544..262.544 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3842kB
106. 41.687 233.215 ↑ 1.0 80,131 1

Subquery Scan on x_4 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=106.779..233.215 rows=80,131 loops=1)

  • Filter: (((x_4.code)::text !~~ 'RV%'::text) AND ((COALESCE(x_4.next_code, ''::character varying))::text !~~ 'RV%'::text))
  • Rows Removed by Filter: 2085
107. 62.504 191.528 ↓ 1.0 82,216 1

WindowAgg (cost=14,036.78..15,680.06 rows=82,164 width=43) (actual time=106.765..191.528 rows=82,216 loops=1)

108. 95.004 129.024 ↓ 1.0 82,216 1

Sort (cost=14,036.78..14,242.19 rows=82,164 width=11) (actual time=106.737..129.024 rows=82,216 loops=1)

  • Sort Key: transactions_9.loan_id, transactions_9.id
  • Sort Method: external sort Disk: 1696kB
109. 34.020 34.020 ↓ 1.0 82,216 1

Seq Scan on transactions transactions_9 (cost=0.00..7,329.64 rows=82,164 width=11) (actual time=0.005..34.020 rows=82,216 loops=1)

110.          

CTE total_disbursed_this_period

111. 0.011 275.650 ↑ 200.0 1 1

GroupAggregate (cost=26,546.79..26,566.66 rows=200 width=16) (actual time=275.650..275.650 rows=1 loops=1)

  • Group Key: ae_6.loan_id, ae_6.transaction_id
112. 0.008 275.639 ↑ 1,687.0 1 1

Sort (cost=26,546.79..26,551.01 rows=1,687 width=16) (actual time=275.639..275.639 rows=1 loops=1)

  • Sort Key: ae_6.loan_id, ae_6.transaction_id
  • Sort Method: quicksort Memory: 25kB
113. 0.743 275.631 ↑ 1,687.0 1 1

Hash Join (cost=25,555.33..26,456.37 rows=1,687 width=16) (actual time=275.610..275.631 rows=1 loops=1)

  • Hash Cond: (ae_6.transaction_id = irad_1.transaction_id)
  • Join Filter: ((transactions_10.date >= irad_1.period_start_date) AND (transactions_10.date <= irad_1.interest_to_date))
  • Rows Removed by Join Filter: 3136
114. 1.513 274.581 ↑ 1.2 1,681 1

Hash Semi Join (cost=25,506.74..25,794.16 rows=2,031 width=20) (actual time=272.726..274.581 rows=1,681 loops=1)

  • Hash Cond: (transactions_10.id = x_5.id)
115. 0.726 19.158 ↑ 1.2 1,681 1

Hash Join (cost=7,584.25..7,843.65 rows=2,065 width=24) (actual time=18.306..19.158 rows=1,681 loops=1)

  • Hash Cond: (ae_6.loan_id = transactions_10.loan_id)
116. 0.163 0.163 ↑ 2.1 471 1

CTE Scan on adjustment_events ae_6 (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.001..0.163 rows=471 loops=1)

117. 1.518 18.269 ↑ 1.0 3,811 1

Hash (cost=7,535.05..7,535.05 rows=3,936 width=20) (actual time=18.269..18.269 rows=3,811 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 226kB
118. 16.751 16.751 ↑ 1.0 3,811 1

Seq Scan on transactions transactions_10 (cost=0.00..7,535.05 rows=3,936 width=20) (actual time=0.021..16.751 rows=3,811 loops=1)

  • Filter: ((code)::text = 'LD'::text)
  • Rows Removed by Filter: 78405
119. 28.142 253.910 ↑ 1.0 80,131 1

Hash (cost=16,912.52..16,912.52 rows=80,798 width=4) (actual time=253.910..253.910 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3842kB
120. 41.655 225.768 ↑ 1.0 80,131 1

Subquery Scan on x_5 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=99.393..225.768 rows=80,131 loops=1)

  • Filter: (((x_5.code)::text !~~ 'RV%'::text) AND ((COALESCE(x_5.next_code, ''::character varying))::text !~~ 'RV%'::text))
  • Rows Removed by Filter: 2085
121. 62.581 184.113 ↓ 1.0 82,216 1

WindowAgg (cost=14,036.78..15,680.06 rows=82,164 width=43) (actual time=99.378..184.113 rows=82,216 loops=1)

122. 87.581 121.532 ↓ 1.0 82,216 1

Sort (cost=14,036.78..14,242.19 rows=82,164 width=11) (actual time=99.354..121.532 rows=82,216 loops=1)

  • Sort Key: transactions_11.loan_id, transactions_11.id
  • Sort Method: external sort Disk: 1696kB
123. 33.951 33.951 ↓ 1.0 82,216 1

Seq Scan on transactions transactions_11 (cost=0.00..7,329.64 rows=82,164 width=11) (actual time=0.006..33.951 rows=82,216 loops=1)

124. 0.139 0.307 ↑ 3.2 471 1

Hash (cost=29.90..29.90 rows=1,495 width=16) (actual time=0.307..0.307 rows=471 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 40kB
125. 0.168 0.168 ↑ 3.2 471 1

CTE Scan on interest_rates_and_dates irad_1 (cost=0.00..29.90 rows=1,495 width=16) (actual time=0.002..0.168 rows=471 loops=1)

126.          

CTE total_paid_this_period

127. 1.081 358.543 ↑ 120.7 233 1

GroupAggregate (cost=38,905.04..40,099.97 rows=28,116 width=72) (actual time=357.381..358.543 rows=233 loops=1)

  • Group Key: transactions_12.loan_id, ae_7.transaction_id
128. 0.351 357.462 ↑ 64.6 435 1

Sort (cost=38,905.04..38,975.33 rows=28,116 width=149) (actual time=357.346..357.462 rows=435 loops=1)

  • Sort Key: transactions_12.loan_id, ae_7.transaction_id
  • Sort Method: quicksort Memory: 140kB
129. 0.801 357.111 ↑ 64.6 435 1

Hash Semi Join (cost=29,265.43..36,827.40 rows=28,116 width=149) (actual time=342.570..357.111 rows=435 loops=1)

  • Hash Cond: (transactions_12.id = x_6.id)
130. 13.540 103.052 ↑ 65.4 437 1

Hash Join (cost=11,342.94..18,517.06 rows=28,591 width=153) (actual time=88.827..103.052 rows=437 loops=1)

  • Hash Cond: (ae_7.loan_id = transactions_12.loan_id)
  • Join Filter: ((transactions_12.date >= irad_2.period_start_date) AND (transactions_12.date <= irad_2.interest_to_date))
  • Rows Removed by Join Filter: 29599
131. 0.546 1.484 ↑ 8.4 885 1

Merge Join (cost=178.56..295.68 rows=7,475 width=20) (actual time=0.589..1.484 rows=885 loops=1)

  • Merge Cond: (ae_7.transaction_id = irad_2.transaction_id)
132. 0.230 0.374 ↑ 2.1 471 1

Sort (cost=69.83..72.33 rows=1,000 width=8) (actual time=0.267..0.374 rows=471 loops=1)

  • Sort Key: ae_7.transaction_id
  • Sort Method: quicksort Memory: 47kB
133. 0.144 0.144 ↑ 2.1 471 1

CTE Scan on adjustment_events ae_7 (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.001..0.144 rows=471 loops=1)

134. 0.400 0.564 ↑ 1.7 884 1

Sort (cost=108.73..112.47 rows=1,495 width=16) (actual time=0.320..0.564 rows=884 loops=1)

  • Sort Key: irad_2.transaction_id
  • Sort Method: quicksort Memory: 61kB
135. 0.164 0.164 ↑ 3.2 471 1

CTE Scan on interest_rates_and_dates irad_2 (cost=0.00..29.90 rows=1,495 width=16) (actual time=0.002..0.164 rows=471 loops=1)

136. 39.855 88.028 ↑ 1.0 64,110 1

Hash (cost=8,870.22..8,870.22 rows=65,613 width=153) (actual time=88.028..88.028 rows=64,110 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 3114kB
137. 48.173 48.173 ↑ 1.0 64,110 1

Seq Scan on transactions transactions_12 (cost=0.00..8,870.22 rows=65,613 width=153) (actual time=0.021..48.173 rows=64,110 loops=1)

  • Filter: ((code)::text = ANY ('{PM,PMI,PMP,PMM,PRE,LP,LPI,LPP,LPM,AT,ATI,ATP,ATM,SK,RC}'::text[]))
  • Rows Removed by Filter: 18106
138. 28.398 253.258 ↑ 1.0 80,131 1

Hash (cost=16,912.52..16,912.52 rows=80,798 width=4) (actual time=253.258..253.258 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3842kB
139. 41.561 224.860 ↑ 1.0 80,131 1

Subquery Scan on x_6 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=99.478..224.860 rows=80,131 loops=1)

  • Filter: (((x_6.code)::text !~~ 'RV%'::text) AND ((COALESCE(x_6.next_code, ''::character varying))::text !~~ 'RV%'::text))
  • Rows Removed by Filter: 2085
140. 62.112 183.299 ↓ 1.0 82,216 1

WindowAgg (cost=14,036.78..15,680.06 rows=82,164 width=43) (actual time=99.466..183.299 rows=82,216 loops=1)

141. 87.573 121.187 ↓ 1.0 82,216 1

Sort (cost=14,036.78..14,242.19 rows=82,164 width=11) (actual time=99.441..121.187 rows=82,216 loops=1)

  • Sort Key: transactions_13.loan_id, transactions_13.id
  • Sort Method: external sort Disk: 1696kB
142. 33.614 33.614 ↓ 1.0 82,216 1

Seq Scan on transactions transactions_13 (cost=0.00..7,329.64 rows=82,164 width=11) (actual time=0.007..33.614 rows=82,216 loops=1)

143.          

CTE guaranteed_total_paid_this_period

144. 0.213 16,901.317 ↓ 6.2 234 1

GroupAggregate (cost=22,720.31..22,721.45 rows=38 width=104) (actual time=16,901.032..16,901.317 rows=234 loops=1)

  • Group Key: transactions_14.loan_id, ae_8.transaction_id, ae_8.adjustment_type
145. 0.157 16,901.104 ↓ 6.2 234 1

Sort (cost=22,720.31..22,720.40 rows=38 width=56) (actual time=16,901.022..16,901.104 rows=234 loops=1)

  • Sort Key: transactions_14.loan_id, ae_8.transaction_id, ae_8.adjustment_type
  • Sort Method: quicksort Memory: 43kB
146. 0.176 16,900.947 ↓ 6.2 234 1

Unique (cost=22,718.65..22,718.93 rows=38 width=56) (actual time=16,900.679..16,900.947 rows=234 loops=1)

147. 0.658 16,900.771 ↓ 11.8 449 1

Sort (cost=22,718.65..22,718.74 rows=38 width=56) (actual time=16,900.677..16,900.771 rows=449 loops=1)

  • Sort Key: ae_8.transaction_id, ae_8.adjustment_type
  • Sort Method: quicksort Memory: 60kB
148. 4.263 16,900.113 ↓ 11.8 449 1

Hash Semi Join (cost=18,393.15..22,717.65 rows=38 width=56) (actual time=308.698..16,900.113 rows=449 loops=1)

  • Hash Cond: (transactions_14.id = x_7.id)
149. 168.899 16,643.231 ↓ 11.9 451 1

Merge Join (cost=470.66..4,794.08 rows=38 width=185) (actual time=55.539..16,643.231 rows=451 loops=1)

  • Merge Cond: (parents_1.id = children_1.participation_loan_id)
  • Join Filter: (transactions_14.loan_id = children_1.id)
  • Rows Removed by Join Filter: 311704
150. 7,786.327 16,391.999 ↓ 7.1 312,155 1

Nested Loop (cost=298.02..25,067.45 rows=43,742 width=193) (actual time=27.597..16,391.999 rows=312,155 loops=1)

  • Join Filter: ((transactions_14.date >= irad_3.interest_from_date) AND (transactions_14.date <= irad_3.interest_to_date))
  • Rows Removed by Join Filter: 29883655
151. 1.363 4.741 ↓ 78.5 471 1

Merge Join (cost=298.02..299.84 rows=6 width=56) (actual time=2.411..4.741 rows=471 loops=1)

  • Merge Cond: (ae_8.loan_id = parents_1.id)
152. 0.624 1.957 ↓ 12.7 471 1

Sort (cost=198.61..198.70 rows=37 width=52) (actual time=1.504..1.957 rows=471 loops=1)

  • Sort Key: ae_8.loan_id
  • Sort Method: quicksort Memory: 61kB
153. 0.478 1.333 ↓ 12.7 471 1

Merge Join (cost=178.56..197.64 rows=37 width=52) (actual time=0.634..1.333 rows=471 loops=1)

  • Merge Cond: ((ae_8.transaction_id = irad_3.transaction_id) AND (ae_8.adjustment_type = irad_3.adjustment_type))
154. 0.266 0.406 ↑ 2.1 471 1

Sort (cost=69.83..72.33 rows=1,000 width=40) (actual time=0.304..0.406 rows=471 loops=1)

  • Sort Key: ae_8.transaction_id, ae_8.adjustment_type
  • Sort Method: quicksort Memory: 61kB
155. 0.140 0.140 ↑ 2.1 471 1

CTE Scan on adjustment_events ae_8 (cost=0.00..20.00 rows=1,000 width=40) (actual time=0.002..0.140 rows=471 loops=1)

156. 0.293 0.449 ↑ 3.2 471 1

Sort (cost=108.73..112.47 rows=1,495 width=48) (actual time=0.326..0.449 rows=471 loops=1)

  • Sort Key: irad_3.transaction_id, irad_3.adjustment_type
  • Sort Method: quicksort Memory: 61kB
157. 0.156 0.156 ↑ 3.2 471 1

CTE Scan on interest_rates_and_dates irad_3 (cost=0.00..29.90 rows=1,495 width=48) (actual time=0.001..0.156 rows=471 loops=1)

158. 0.644 1.421 ↓ 1.8 555 1

Sort (cost=99.41..100.20 rows=315 width=4) (actual time=0.903..1.421 rows=555 loops=1)

  • Sort Key: parents_1.id
  • Sort Method: quicksort Memory: 40kB
159. 0.777 0.777 ↓ 1.0 321 1

Seq Scan on loans parents_1 (cost=0.00..86.34 rows=315 width=4) (actual time=0.005..0.777 rows=321 loops=1)

  • Filter: is_community_advantage
  • Rows Removed by Filter: 1613
160. 8,551.807 8,600.931 ↑ 1.0 64,110 471

Materialize (cost=0.00..10,672.28 rows=65,613 width=153) (actual time=0.003..18.261 rows=64,110 loops=471)

161. 49.124 49.124 ↑ 1.0 64,110 1

Seq Scan on transactions transactions_14 (cost=0.00..8,870.22 rows=65,613 width=153) (actual time=0.014..49.124 rows=64,110 loops=1)

  • Filter: ((code)::text = ANY ('{PM,PMI,PMP,PMM,PRE,LP,LPI,LPP,LPM,AT,ATI,ATP,ATM,SK,RC}'::text[]))
  • Rows Removed by Filter: 18106
162. 81.166 82.333 ↓ 192.9 312,243 1

Sort (cost=172.64..176.69 rows=1,619 width=8) (actual time=1.671..82.333 rows=312,243 loops=1)

  • Sort Key: children_1.participation_loan_id
  • Sort Method: quicksort Memory: 124kB
163. 1.167 1.167 ↑ 1.0 1,613 1

Seq Scan on loans children_1 (cost=0.00..86.34 rows=1,619 width=8) (actual time=0.008..1.167 rows=1,613 loops=1)

  • Filter: (NOT is_community_advantage_lender_loan)
  • Rows Removed by Filter: 321
164. 28.489 252.619 ↑ 1.0 80,131 1

Hash (cost=16,912.52..16,912.52 rows=80,798 width=4) (actual time=252.619..252.619 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3842kB
165. 41.113 224.130 ↑ 1.0 80,131 1

Subquery Scan on x_7 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=98.698..224.130 rows=80,131 loops=1)

  • Filter: (((x_7.code)::text !~~ 'RV%'::text) AND ((COALESCE(x_7.next_code, ''::character varying))::text !~~ 'RV%'::text))
  • Rows Removed by Filter: 2085
166. 62.398 183.017 ↓ 1.0 82,216 1

WindowAgg (cost=14,036.78..15,680.06 rows=82,164 width=43) (actual time=98.685..183.017 rows=82,216 loops=1)

167. 86.652 120.619 ↓ 1.0 82,216 1

Sort (cost=14,036.78..14,242.19 rows=82,164 width=11) (actual time=98.662..120.619 rows=82,216 loops=1)

  • Sort Key: transactions_15.loan_id, transactions_15.id
  • Sort Method: external sort Disk: 1696kB
168. 33.967 33.967 ↓ 1.0 82,216 1

Seq Scan on transactions transactions_15 (cost=0.00..7,329.64 rows=82,164 width=11) (actual time=0.007..33.967 rows=82,216 loops=1)

169.          

CTE current_period_last_payment

170. 0.145 323.180 ↑ 8.6 222 1

Unique (cost=35,869.04..36,009.62 rows=1,906 width=17) (actual time=322.929..323.180 rows=222 loops=1)

171. 0.288 323.035 ↑ 64.6 435 1

Sort (cost=35,869.04..35,939.33 rows=28,116 width=17) (actual time=322.929..323.035 rows=435 loops=1)

  • Sort Key: transactions_16.loan_id, transactions_16.id DESC
  • Sort Method: quicksort Memory: 58kB
172. 0.805 322.747 ↑ 64.6 435 1

Hash Semi Join (cost=27,791.43..33,791.40 rows=28,116 width=17) (actual time=318.212..322.747 rows=435 loops=1)

  • Hash Cond: (transactions_16.id = x_8.id)
173. 3.815 68.348 ↑ 65.4 437 1

Hash Join (cost=9,868.94..15,481.06 rows=28,591 width=16) (actual time=64.110..68.348 rows=437 loops=1)

  • Hash Cond: (ae_9.loan_id = transactions_16.loan_id)
  • Join Filter: ((transactions_16.date >= irad_4.period_start_date) AND (transactions_16.date <= irad_4.interest_to_date))
  • Rows Removed by Join Filter: 29599
174. 0.612 1.512 ↑ 8.4 885 1

Merge Join (cost=178.56..295.68 rows=7,475 width=20) (actual time=0.575..1.512 rows=885 loops=1)

  • Merge Cond: (ae_9.transaction_id = irad_4.transaction_id)
175. 0.252 0.399 ↑ 2.1 471 1

Sort (cost=69.83..72.33 rows=1,000 width=8) (actual time=0.275..0.399 rows=471 loops=1)

  • Sort Key: ae_9.transaction_id
  • Sort Method: quicksort Memory: 47kB
176. 0.147 0.147 ↑ 2.1 471 1

CTE Scan on adjustment_events ae_9 (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.002..0.147 rows=471 loops=1)

177. 0.346 0.501 ↑ 1.7 884 1

Sort (cost=108.73..112.47 rows=1,495 width=16) (actual time=0.298..0.501 rows=884 loops=1)

  • Sort Key: irad_4.transaction_id
  • Sort Method: quicksort Memory: 61kB
178. 0.155 0.155 ↑ 3.2 471 1

CTE Scan on interest_rates_and_dates irad_4 (cost=0.00..29.90 rows=1,495 width=16) (actual time=0.002..0.155 rows=471 loops=1)

179. 22.075 63.021 ↑ 1.0 64,110 1

Hash (cost=8,870.22..8,870.22 rows=65,613 width=12) (actual time=63.021..63.021 rows=64,110 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3779kB
180. 40.946 40.946 ↑ 1.0 64,110 1

Seq Scan on transactions transactions_16 (cost=0.00..8,870.22 rows=65,613 width=12) (actual time=0.020..40.946 rows=64,110 loops=1)

  • Filter: ((code)::text = ANY ('{PM,PMI,PMP,PMM,PRE,LP,LPI,LPP,LPM,AT,ATI,ATP,ATM,SK,RC}'::text[]))
  • Rows Removed by Filter: 18106
181. 27.806 253.594 ↑ 1.0 80,131 1

Hash (cost=16,912.52..16,912.52 rows=80,798 width=4) (actual time=253.594..253.594 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3842kB
182. 41.808 225.788 ↑ 1.0 80,131 1

Subquery Scan on x_8 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=99.833..225.788 rows=80,131 loops=1)

  • Filter: (((x_8.code)::text !~~ 'RV%'::text) AND ((COALESCE(x_8.next_code, ''::character varying))::text !~~ 'RV%'::text))
  • Rows Removed by Filter: 2085
183. 62.324 183.980 ↓ 1.0 82,216 1

WindowAgg (cost=14,036.78..15,680.06 rows=82,164 width=43) (actual time=99.820..183.980 rows=82,216 loops=1)

184. 87.605 121.656 ↓ 1.0 82,216 1

Sort (cost=14,036.78..14,242.19 rows=82,164 width=11) (actual time=99.799..121.656 rows=82,216 loops=1)

  • Sort Key: transactions_17.loan_id, transactions_17.id
  • Sort Method: external sort Disk: 1696kB
185. 34.051 34.051 ↓ 1.0 82,216 1

Seq Scan on transactions transactions_17 (cost=0.00..7,329.64 rows=82,164 width=11) (actual time=0.006..34.051 rows=82,216 loops=1)

186.          

CTE dates_when_deliquency_started

187. 0.008 12,710.827 ↑ 16.7 12 1

Unique (cost=577,343.42..577,636.84 rows=200 width=16) (actual time=12,710.806..12,710.827 rows=12 loops=1)

188. 0.039 12,710.819 ↑ 3,451.9 17 1

Sort (cost=577,343.42..577,490.13 rows=58,683 width=16) (actual time=12,710.805..12,710.819 rows=17 loops=1)

  • Sort Key: transactions_18.loan_id, transactions_18.id DESC
  • Sort Method: quicksort Memory: 25kB
189. 0.366 12,710.780 ↑ 3,451.9 17 1

Nested Loop (cost=43,225.16..572,695.54 rows=58,683 width=16) (actual time=12,667.667..12,710.780 rows=17 loops=1)

190. 9.865 12,684.278 ↑ 133.4 264 1

Unique (cost=43,224.91..43,488.99 rows=35,210 width=80) (actual time=12,664.799..12,684.278 rows=264 loops=1)

191. 38.342 12,674.413 ↓ 1.1 40,173 1

Sort (cost=43,224.91..43,312.94 rows=35,210 width=80) (actual time=12,664.798..12,674.413 rows=40,173 loops=1)

  • Sort Key: transactions_18.loan_id, ae_10.transaction_id, transactions_18.id DESC
  • Sort Method: quicksort Memory: 3420kB
192. 21.736 12,636.071 ↓ 1.1 40,173 1

Merge Join (cost=38,709.92..40,565.91 rows=35,210 width=80) (actual time=12,604.230..12,636.071 rows=40,173 loops=1)

  • Merge Cond: (irad_5.transaction_id = ae_10.transaction_id)
  • Join Filter: (transactions_18.date <= irad_5.interest_to_date)
  • Rows Removed by Join Filter: 5156
193. 0.249 11,744.430 ↑ 3.2 471 1

Sort (cost=108.73..112.47 rows=1,495 width=8) (actual time=11,744.318..11,744.430 rows=471 loops=1)

  • Sort Key: irad_5.transaction_id
  • Sort Method: quicksort Memory: 47kB
194. 11,744.181 11,744.181 ↑ 3.2 471 1

CTE Scan on interest_rates_and_dates irad_5 (cost=0.00..29.90 rows=1,495 width=8) (actual time=11,743.030..11,744.181 rows=471 loops=1)

195. 20.572 869.905 ↓ 3.2 45,328 1

Sort (cost=38,601.19..38,636.52 rows=14,131 width=16) (actual time=859.899..869.905 rows=45,328 loops=1)

  • Sort Key: ae_10.transaction_id
  • Sort Method: quicksort Memory: 1887kB
196. 21.343 849.333 ↓ 1.7 23,869 1

Hash Semi Join (cost=18,111.33..37,627.10 rows=14,131 width=16) (actual time=257.766..849.333 rows=23,869 loops=1)

  • Hash Cond: (transactions_18.id = x_9.id)
197. 546.468 573.157 ↓ 1.7 24,363 1

Hash Join (cost=188.84..19,509.69 rows=14,369 width=16) (actual time=2.407..573.157 rows=24,363 loops=1)

  • Hash Cond: (transactions_18.loan_id = l_2.id)
  • Join Filter: (round((((fn_max_delinquency(transactions_18.delinquency))::numeric * ('365'::numeric * pf_1.monthly_multiplier)) / '12'::numeric), 2) < '31'::numeric)
  • Rows Removed by Join Filter: 862
198. 24.426 24.426 ↓ 1.0 82,216 1

Seq Scan on transactions transactions_18 (cost=0.00..7,329.64 rows=82,164 width=29) (actual time=0.006..24.426 rows=82,216 loops=1)

199. 0.155 2.263 ↑ 2.1 471 1

Hash (cost=176.34..176.34 rows=1,000 width=44) (actual time=2.263..2.263 rows=471 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
200. 0.245 2.108 ↑ 2.1 471 1

Hash Join (cost=128.84..176.34 rows=1,000 width=44) (actual time=1.484..2.108 rows=471 loops=1)

  • Hash Cond: (l_2.payment_frequency_id = pf_1.id)
201. 0.263 1.851 ↑ 2.1 471 1

Hash Join (cost=110.52..144.26 rows=1,000 width=16) (actual time=1.466..1.851 rows=471 loops=1)

  • Hash Cond: (ae_10.loan_id = l_2.id)
202. 0.139 0.139 ↑ 2.1 471 1

CTE Scan on adjustment_events ae_10 (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.001..0.139 rows=471 loops=1)

203. 0.637 1.449 ↑ 1.0 1,934 1

Hash (cost=86.34..86.34 rows=1,934 width=8) (actual time=1.449..1.449 rows=1,934 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 92kB
204. 0.812 0.812 ↑ 1.0 1,934 1

Seq Scan on loans l_2 (cost=0.00..86.34 rows=1,934 width=8) (actual time=0.005..0.812 rows=1,934 loops=1)

205. 0.004 0.012 ↑ 52.9 7 1

Hash (cost=13.70..13.70 rows=370 width=36) (actual time=0.012..0.012 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
206. 0.008 0.008 ↑ 52.9 7 1

Seq Scan on payment_frequencies pf_1 (cost=0.00..13.70 rows=370 width=36) (actual time=0.004..0.008 rows=7 loops=1)

207. 28.118 254.833 ↑ 1.0 80,131 1

Hash (cost=16,912.52..16,912.52 rows=80,798 width=4) (actual time=254.833..254.833 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3842kB
208. 41.710 226.715 ↑ 1.0 80,131 1

Subquery Scan on x_9 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=99.329..226.715 rows=80,131 loops=1)

  • Filter: (((x_9.code)::text !~~ 'RV%'::text) AND ((COALESCE(x_9.next_code, ''::character varying))::text !~~ 'RV%'::text))
  • Rows Removed by Filter: 2085
209. 63.343 185.005 ↓ 1.0 82,216 1

WindowAgg (cost=14,036.78..15,680.06 rows=82,164 width=43) (actual time=99.318..185.005 rows=82,216 loops=1)

210. 87.668 121.662 ↓ 1.0 82,216 1

Sort (cost=14,036.78..14,242.19 rows=82,164 width=11) (actual time=99.303..121.662 rows=82,216 loops=1)

  • Sort Key: transactions_19.loan_id, transactions_19.id
  • Sort Method: external sort Disk: 1696kB
211. 33.994 33.994 ↓ 1.0 82,216 1

Seq Scan on transactions transactions_19 (cost=0.00..7,329.64 rows=82,164 width=11) (actual time=0.005..33.994 rows=82,216 loops=1)

212. 26.136 26.136 ↓ 0.0 0 264

Function Scan on fn_next_transaction nt (cost=0.25..15.25 rows=2 width=8) (actual time=0.099..0.099 rows=0 loops=264)

  • Filter: ((date <= irad_5.interest_to_date) AND (transactions_18.loan_id = loan_id))
  • Rows Removed by Filter: 1
213.          

CTE calendar_basis_points

214. 1.178 7.540 ↑ 239.8 233 1

Nested Loop (cost=0.15..12,308.87 rows=55,871 width=36) (actual time=0.034..7.540 rows=233 loops=1)

215. 0.560 0.560 ↑ 1.0 1,934 1

Seq Scan on loans l_3 (cost=0.00..86.34 rows=1,934 width=16) (actual time=0.002..0.560 rows=1,934 loops=1)

216. 5.802 5.802 ↓ 0.0 0 1,934

Index Scan using index_community_advantage_fee_basis_points_on_balance_threshold on community_advantage_fee_basis_points cafbp (cost=0.15..6.03 rows=29 width=72) (actual time=0.003..0.003 rows=0 loops=1,934)

  • Index Cond: (((l_3.amount_approved_cents / 100))::numeric >= balance_threshold)
  • Filter: ((l_3.community_advantage_approved_date >= starts_on) AND (l_3.community_advantage_approved_date <= ends_on))
  • Rows Removed by Filter: 11
217.          

CTE closing_balances

218. 0.777 1.095 ↑ 24.0 471 1

Hash Full Join (cost=6.50..768.17 rows=11,284 width=68) (actual time=0.204..1.095 rows=471 loops=1)

  • Hash Cond: (lpbad_1.transaction_id = ltbad_2.transaction_id)
219. 0.132 0.132 ↑ 24.0 471 1

CTE Scan on last_payment_before_adjustment_date lpbad_1 (cost=0.00..225.68 rows=11,284 width=68) (actual time=0.001..0.132 rows=471 loops=1)

220. 0.081 0.186 ↓ 1.3 264 1

Hash (cost=4.00..4.00 rows=200 width=68) (actual time=0.186..0.186 rows=264 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
221. 0.105 0.105 ↓ 1.3 264 1

CTE Scan on last_transaction_before_adjustment_date ltbad_2 (cost=0.00..4.00 rows=200 width=68) (actual time=0.002..0.105 rows=264 loops=1)

222. 12.881 105,664.909 ↑ 560,822.4 471 1

Hash Left Join (cost=34,938.24..103,359,665.12 rows=264,147,345 width=659) (actual time=105,647.517..105,664.909 rows=471 loops=1)

  • Hash Cond: (l.id = lrpi.loan_id)
223. 0.414 105,651.844 ↑ 560,822.4 471 1

Merge Right Join (cost=34,928.52..7,858,234.49 rows=264,147,345 width=648) (actual time=105,647.238..105,651.844 rows=471 loops=1)

  • Merge Cond: (dwds.transaction_id = ae.transaction_id)
224. 0.012 12,710.850 ↑ 16.7 12 1

Sort (cost=11.64..12.14 rows=200 width=8) (actual time=12,710.846..12,710.850 rows=12 loops=1)

  • Sort Key: dwds.transaction_id
  • Sort Method: quicksort Memory: 25kB
225. 12,710.838 12,710.838 ↑ 16.7 12 1

CTE Scan on dates_when_deliquency_started dwds (cost=0.00..4.00 rows=200 width=8) (actual time=12,710.809..12,710.838 rows=12 loops=1)

226. 0.530 92,940.580 ↑ 560,822.4 471 1

Materialize (cost=34,916.88..4,556,380.04 rows=264,147,345 width=648) (actual time=92,936.387..92,940.580 rows=471 loops=1)

227. 0.671 92,940.050 ↑ 560,822.4 471 1

Merge Left Join (cost=34,916.88..3,896,011.67 rows=264,147,345 width=648) (actual time=92,936.378..92,940.050 rows=471 loops=1)

  • Merge Cond: ((ae.transaction_id = cb.transaction_id) AND (ae.adjustment_type = cb.adjustment_type))
228. 0.636 92,937.596 ↑ 560,822.4 471 1

Merge Left Join (cost=33,931.67..1,270,260.34 rows=264,147,345 width=616) (actual time=92,934.748..92,937.596 rows=471 loops=1)

  • Merge Cond: ((ae.transaction_id = irad.transaction_id) AND (ae.adjustment_type = irad.adjustment_type))
229. 0.417 18,603.147 ↑ 112,164.5 471 1

Merge Left Join (cost=14,658.34..814,070.78 rows=52,829,469 width=503) (actual time=18,601.098..18,603.147 rows=471 loops=1)

  • Merge Cond: (ae.transaction_id = tptp.transaction_id)
230. 0.469 18,243.789 ↑ 797.9 471 1

Merge Left Join (cost=12,018.37..18,050.30 rows=375,796 width=439) (actual time=18,242.293..18,243.789 rows=471 loops=1)

  • Merge Cond: (ae.transaction_id = cplp.transaction_id)
231. 0.572 17,919.812 ↑ 83.7 471 1

Merge Left Join (cost=11,876.41..12,172.81 rows=39,433 width=438) (actual time=17,918.928..17,919.812 rows=471 loops=1)

  • Merge Cond: ((ae.transaction_id = gtptp.transaction_id) AND (ae.adjustment_type = gtptp.adjustment_type))
232. 0.667 1,017.583 ↑ 83.7 471 1

Sort (cost=11,874.65..11,973.24 rows=39,433 width=374) (actual time=1,017.351..1,017.583 rows=471 loops=1)

  • Sort Key: ae.transaction_id, ae.adjustment_type
  • Sort Method: quicksort Memory: 150kB
233. 0.317 1,016.916 ↑ 83.7 471 1

Hash Right Join (cost=402.25..2,123.51 rows=39,433 width=374) (actual time=1,008.975..1,016.916 rows=471 loops=1)

  • Hash Cond: (cbp.loan_id = l.id)
234. 7.679 7.679 ↑ 239.8 233 1

CTE Scan on calendar_basis_points cbp (cost=0.00..1,117.42 rows=55,871 width=36) (actual time=0.036..7.679 rows=233 loops=1)

235. 0.266 1,008.920 ↑ 2.9 471 1

Hash (cost=385.18..385.18 rows=1,365 width=342) (actual time=1,008.920..1,008.920 rows=471 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 99kB
236. 0.287 1,008.654 ↑ 2.9 471 1

Hash Left Join (cost=312.79..385.18 rows=1,365 width=342) (actual time=1,006.359..1,008.654 rows=471 loops=1)

  • Hash Cond: (l.id = lsm.loan_id)
237. 0.383 1,008.321 ↑ 2.9 471 1

Merge Left Join (cost=311.00..378.03 rows=1,365 width=296) (actual time=1,006.306..1,008.321 rows=471 loops=1)

  • Merge Cond: (ae.transaction_id = gsl.transaction_id)
238. 0.408 1,003.055 ↑ 2.1 471 1

Merge Left Join (cost=294.50..338.55 rows=1,000 width=264) (actual time=1,001.532..1,003.055 rows=471 loops=1)

  • Merge Cond: (ae.transaction_id = ltbad.transaction_id)
239. 0.216 565.326 ↑ 2.1 471 1

Merge Left Join (cost=282.85..309.40 rows=1,000 width=200) (actual time=564.326..565.326 rows=471 loops=1)

  • Merge Cond: (ae.transaction_id = tdtp.transaction_id)
240. 0.524 289.446 ↑ 2.1 471 1

Merge Left Join (cost=271.21..280.26 rows=1,000 width=192) (actual time=288.657..289.446 rows=471 loops=1)

  • Merge Cond: ((ae.transaction_id = tda.transaction_id) AND (ae.adjustment_type = tda.adjustment_type))
241. 0.488 3.467 ↑ 2.1 471 1

Sort (cost=259.56..262.06 rows=1,000 width=184) (actual time=3.332..3.467 rows=471 loops=1)

  • Sort Key: ae.transaction_id, ae.adjustment_type
  • Sort Method: quicksort Memory: 91kB
242. 0.256 2.979 ↑ 2.1 471 1

Hash Join (cost=148.52..209.73 rows=1,000 width=184) (actual time=2.051..2.979 rows=471 loops=1)

  • Hash Cond: (l.calculation_method_id = cm.id)
243. 0.255 2.709 ↑ 2.1 471 1

Hash Join (cost=128.84..176.34 rows=1,000 width=184) (actual time=2.031..2.709 rows=471 loops=1)

  • Hash Cond: (l.payment_frequency_id = pf.id)
244. 0.317 2.442 ↑ 2.1 471 1

Hash Join (cost=110.52..144.26 rows=1,000 width=124) (actual time=2.014..2.442 rows=471 loops=1)

  • Hash Cond: (ae.loan_id = l.id)
245. 0.133 0.133 ↑ 2.1 471 1

CTE Scan on adjustment_events ae (cost=0.00..20.00 rows=1,000 width=76) (actual time=0.002..0.133 rows=471 loops=1)

246. 0.703 1.992 ↑ 1.0 1,934 1

Hash (cost=86.34..86.34 rows=1,934 width=48) (actual time=1.992..1.992 rows=1,934 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 159kB
247. 1.289 1.289 ↑ 1.0 1,934 1

Seq Scan on loans l (cost=0.00..86.34 rows=1,934 width=48) (actual time=0.005..1.289 rows=1,934 loops=1)

248. 0.006 0.012 ↑ 52.9 7 1

Hash (cost=13.70..13.70 rows=370 width=68) (actual time=0.012..0.012 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
249. 0.006 0.006 ↑ 52.9 7 1

Seq Scan on payment_frequencies pf (cost=0.00..13.70 rows=370 width=68) (actual time=0.005..0.006 rows=7 loops=1)

250. 0.004 0.014 ↑ 107.5 4 1

Hash (cost=14.30..14.30 rows=430 width=8) (actual time=0.014..0.014 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
251. 0.010 0.010 ↑ 107.5 4 1

Seq Scan on calculation_methods cm (cost=0.00..14.30 rows=430 width=8) (actual time=0.006..0.010 rows=4 loops=1)

252. 0.287 285.455 ↓ 2.4 471 1

Sort (cost=11.64..12.14 rows=200 width=44) (actual time=285.318..285.455 rows=471 loops=1)

  • Sort Key: tda.transaction_id, tda.adjustment_type
  • Sort Method: quicksort Memory: 61kB
253. 285.168 285.168 ↓ 2.4 471 1

CTE Scan on total_disbursed_amount tda (cost=0.00..4.00 rows=200 width=44) (actual time=283.732..285.168 rows=471 loops=1)

254. 0.010 275.664 ↑ 200.0 1 1

Sort (cost=11.64..12.14 rows=200 width=12) (actual time=275.664..275.664 rows=1 loops=1)

  • Sort Key: tdtp.transaction_id
  • Sort Method: quicksort Memory: 25kB
255. 275.654 275.654 ↑ 200.0 1 1

CTE Scan on total_disbursed_this_period tdtp (cost=0.00..4.00 rows=200 width=12) (actual time=275.653..275.654 rows=1 loops=1)

256. 0.234 437.321 ↓ 2.4 470 1

Sort (cost=11.64..12.14 rows=200 width=68) (actual time=437.201..437.321 rows=470 loops=1)

  • Sort Key: ltbad.transaction_id
  • Sort Method: quicksort Memory: 45kB
257. 437.087 437.087 ↓ 1.3 264 1

CTE Scan on last_transaction_before_adjustment_date ltbad (cost=0.00..4.00 rows=200 width=68) (actual time=423.663..437.087 rows=264 loops=1)

258. 0.206 4.883 ↓ 1.7 470 1

Sort (cost=16.51..17.19 rows=273 width=36) (actual time=4.771..4.883 rows=470 loops=1)

  • Sort Key: gsl.transaction_id
  • Sort Method: quicksort Memory: 37kB
259. 4.677 4.677 ↑ 1.0 264 1

CTE Scan on guaranteed_sub_loans gsl (cost=0.00..5.46 rows=273 width=36) (actual time=4.257..4.677 rows=264 loops=1)

260. 0.023 0.046 ↓ 1.6 56 1

Hash (cost=1.35..1.35 rows=35 width=50) (actual time=0.046..0.046 rows=56 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
261. 0.023 0.023 ↓ 1.6 56 1

Seq Scan on loan_state_machines lsm (cost=0.00..1.35 rows=35 width=50) (actual time=0.007..0.023 rows=56 loops=1)

262. 0.181 16,901.657 ↓ 6.2 234 1

Sort (cost=1.76..1.85 rows=38 width=100) (actual time=16,901.572..16,901.657 rows=234 loops=1)

  • Sort Key: gtptp.transaction_id, gtptp.adjustment_type
  • Sort Method: quicksort Memory: 43kB
263. 16,901.476 16,901.476 ↓ 6.2 234 1

CTE Scan on guaranteed_total_paid_this_period gtptp (cost=0.00..0.76 rows=38 width=100) (actual time=16,901.036..16,901.476 rows=234 loops=1)

264. 0.220 323.508 ↑ 4.6 418 1

Sort (cost=141.96..146.73 rows=1,906 width=5) (actual time=323.361..323.508 rows=418 loops=1)

  • Sort Key: cplp.transaction_id
  • Sort Method: quicksort Memory: 35kB
265. 323.288 323.288 ↑ 8.6 222 1

CTE Scan on current_period_last_payment cplp (cost=0.00..38.12 rows=1,906 width=5) (actual time=322.932..323.288 rows=222 loops=1)

266. 0.233 358.941 ↑ 65.5 429 1

Sort (cost=2,639.97..2,710.26 rows=28,116 width=68) (actual time=358.800..358.941 rows=429 loops=1)

  • Sort Key: tptp.transaction_id
  • Sort Method: quicksort Memory: 42kB
267. 358.708 358.708 ↑ 120.7 233 1

CTE Scan on total_paid_this_period tptp (cost=0.00..562.32 rows=28,116 width=68) (actual time=357.384..358.708 rows=233 loops=1)

268. 1.264 74,333.813 ↑ 15.9 471 1

Sort (cost=19,273.34..19,292.02 rows=7,475 width=149) (actual time=74,333.643..74,333.813 rows=471 loops=1)

  • Sort Key: irad.transaction_id, irad.adjustment_type
  • Sort Method: quicksort Memory: 91kB
269. 6.246 74,332.549 ↑ 15.9 471 1

Nested Loop Left Join (cost=0.25..18,792.40 rows=7,475 width=149) (actual time=160.205..74,332.549 rows=471 loops=1)

270. 0.619 0.619 ↑ 3.2 471 1

CTE Scan on interest_rates_and_dates irad (cost=0.00..29.90 rows=1,495 width=85) (actual time=0.001..0.619 rows=471 loops=1)

271. 74,325.684 74,325.684 ↑ 5.0 1 471

Function Scan on fn_proxy_loans_status ls (cost=0.25..12.75 rows=5 width=72) (actual time=157.753..157.804 rows=1 loops=471)

  • Filter: (loan_id = irad.loan_id)
  • Rows Removed by Filter: 1255
272. 0.363 1.783 ↑ 24.0 471 1

Sort (cost=985.21..1,013.42 rows=11,284 width=68) (actual time=1.626..1.783 rows=471 loops=1)

  • Sort Key: cb.transaction_id, cb.adjustment_type
  • Sort Method: quicksort Memory: 61kB
273. 1.420 1.420 ↑ 24.0 471 1

CTE Scan on closing_balances cb (cost=0.00..225.68 rows=11,284 width=68) (actual time=0.207..1.420 rows=471 loops=1)

274. 0.093 0.184 ↓ 1.0 308 1

Hash (cost=5.98..5.98 rows=299 width=36) (actual time=0.184..0.184 rows=308 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
275. 0.091 0.091 ↓ 1.0 308 1

CTE Scan on last_reporting_period_info lrpi (cost=0.00..5.98 rows=299 width=36) (actual time=0.002..0.091 rows=308 loops=1)