explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0JRU : Optimization for: big query; plan #CLnr

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.782 103,537.612 ↑ 560,822.4 471 1

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

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

CTE adjustment_events

3. 9,139.516 9,139.516 ↑ 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,139.422..9,139.516 rows=471 loops=1)

4.          

CTE last_confirmed_community_advantage_loan

5. 0.288 3.546 ↑ 1.0 299 1

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

6. 0.778 3.258 ↓ 2.3 999 1

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

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

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

  • Hash Cond: (carl.community_advantage_report_request_id = carr.id)
8. 0.681 0.681 ↑ 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.020..0.681 rows=1,298 loops=1)

9. 0.005 0.019 ↑ 75.3 3 1

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

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

Seq Scan on community_advantage_report_requests carr (cost=0.00..18.50 rows=226 width=8) (actual time=0.011..0.014 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.191 2,180.873 ↓ 1.0 308 1

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

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

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

14. 0.137 2,176.983 ↓ 5.6 308 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
15. 0.147 2,176.846 ↓ 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,149.713..2,176.846 rows=308 loops=1)

16. 12.263 2,176.699 ↓ 5.6 308 1

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

17. 80.951 2,164.436 ↓ 1,080.7 59,438 1

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

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

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

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

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

  • Hash Cond: (transactions.loan_id = lat.loan_id)
20. 19.218 19.218 ↓ 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.218 rows=3,704 loops=1)

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

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

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

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

23. 68.943 1,667.463 ↓ 596.7 15,515 1

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

24. 13.510 1,598.520 ↓ 596.7 15,515 1

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

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

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

26. 15.492 332.473 ↓ 7.4 15,834 1

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

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

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

  • Hash Cond: (transactions_1.loan_id = l_1.id)
28. 25.451 25.451 ↓ 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.002..25.451 rows=82,216 loops=1)

29. 0.098 0.971 ↓ 6.3 319 1

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

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

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

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

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

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

Subquery Scan on x_1 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=99.157..225.965 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.961 184.290 ↓ 1.0 82,216 1

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

34. 87.152 121.329 ↓ 1.0 82,216 1

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

  • Sort Key: transactions_2.loan_id, transactions_2.id
  • Sort Method: external sort Disk: 1696kB
35. 34.177 34.177 ↓ 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.004..34.177 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.077..0.077 rows=1 loops=15,834)

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

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

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

Subquery Scan on x (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=101.228..227.236 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. 61.979 184.978 ↓ 1.0 82,216 1

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

40. 88.612 122.999 ↓ 1.0 82,216 1

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

  • Sort Key: transactions_3.loan_id, transactions_3.id
  • Sort Method: external sort Disk: 1696kB
41. 34.387 34.387 ↓ 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..34.387 rows=82,216 loops=1)

42.          

CTE last_payment_before_adjustment_date

43. 3.451 371.627 ↑ 24.0 471 1

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

44. 17.919 368.176 ↓ 1.2 13,745 1

Sort (cost=31,011.10..31,039.31 rows=11,284 width=126) (actual time=365.074..368.176 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.629 350.257 ↓ 1.2 13,745 1

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

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

Hash Join (cost=10,588.38..12,173.43 rows=11,475 width=126) (actual time=78.060..89.772 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.185 0.185 ↑ 2.1 471 1

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

48. 29.893 77.761 ↑ 1.0 64,110 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 4229kB
49. 47.868 47.868 ↑ 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.011..47.868 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. 28.064 251.856 ↑ 1.0 80,131 1

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

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

Subquery Scan on x_2 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=98.581..223.792 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.828 182.376 ↓ 1.0 82,216 1

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

53. 87.458 120.548 ↓ 1.0 82,216 1

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

  • Sort Key: transactions_5.loan_id, transactions_5.id
  • Sort Method: external sort Disk: 1696kB
54. 33.090 33.090 ↓ 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.009..33.090 rows=82,216 loops=1)

55.          

CTE last_transaction_before_adjustment_date

56. 5.683 432.019 ↓ 1.3 264 1

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

57. 47.737 426.336 ↓ 1.5 21,815 1

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

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

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

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

Hash Join (cost=9,801.69..12,042.29 rows=14,369 width=153) (actual time=93.661..113.626 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.179 0.179 ↑ 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.179 rows=471 loops=1)

61. 47.390 93.334 ↓ 1.0 82,216 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 3305kB
62. 45.944 45.944 ↓ 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.009..45.944 rows=82,216 loops=1)

63. 27.274 251.127 ↑ 1.0 80,131 1

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

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

Subquery Scan on x_3 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=97.820..223.853 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.351 182.178 ↓ 1.0 82,216 1

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

66. 86.451 119.827 ↓ 1.0 82,216 1

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

  • Sort Key: transactions_7.loan_id, transactions_7.id
  • Sort Method: external sort Disk: 1696kB
67. 33.376 33.376 ↓ 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.006..33.376 rows=82,216 loops=1)

68.          

CTE guaranteed_sub_loans

69. 0.207 4.548 ↑ 1.0 264 1

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

70. 0.349 4.341 ↓ 1.7 471 1

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

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

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

  • Hash Cond: (children.participation_loan_id = parents.id)
72. 1.054 1.054 ↑ 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.054 rows=1,613 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 64kB
74. 0.248 1.769 ↓ 2.9 471 1

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

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

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

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

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

77. 0.106 0.956 ↓ 1.0 321 1

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

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

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

  • Filter: is_community_advantage
  • Rows Removed by Filter: 1613
79. 0.077 0.172 ↓ 1.3 264 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
80. 0.095 0.095 ↓ 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.002..0.095 rows=264 loops=1)

81.          

CTE indexed_adjustment_events_and_dates

82. 0.431 11,694.764 ↑ 3.2 471 1

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

83. 0.396 11,694.333 ↑ 3.2 471 1

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

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

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

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

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

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

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

87. 0.258 372.140 ↑ 24.0 471 1

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

  • Sort Key: lpbad.loan_id, lpbad.adjustment_date
  • Sort Method: quicksort Memory: 47kB
88. 371.882 371.882 ↑ 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=365.079..371.882 rows=471 loops=1)

89. 0.262 2,181.337 ↓ 1.8 547 1

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

  • Sort Key: lrpi_1.loan_id
  • Sort Method: quicksort Memory: 39kB
90. 2,181.075 2,181.075 ↓ 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,180.031..2,181.075 rows=308 loops=1)

91.          

CTE interest_rates_and_dates

92. 0.603 11,696.541 ↑ 3.2 471 1

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

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

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

  • Sort Key: iaead1.loan_id, iaead1.r
  • Sort Method: quicksort Memory: 61kB
94. 11,695.151 11,695.151 ↑ 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,693.135..11,695.151 rows=471 loops=1)

95. 0.290 0.461 ↑ 3.2 471 1

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

  • Sort Key: iaead2.loan_id, ((iaead2.r + 1))
  • Sort Method: quicksort Memory: 61kB
96. 0.171 0.171 ↑ 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.171 rows=471 loops=1)

97.          

CTE total_disbursed_amount

98. 0.776 274.757 ↓ 2.4 471 1

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

  • Group Key: ae_5.transaction_id, ae_5.adjustment_type
99. 1.112 273.981 ↓ 2.5 1,675 1

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

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

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

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

Hash Join (cost=7,584.25..7,848.81 rows=688 width=48) (actual time=18.549..19.454 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.152 0.152 ↑ 2.1 471 1

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

103. 1.651 18.528 ↑ 1.0 3,811 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 226kB
104. 16.877 16.877 ↑ 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.877 rows=3,811 loops=1)

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

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

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

Subquery Scan on x_4 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=98.671..224.510 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.394 182.854 ↓ 1.0 82,216 1

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

108. 86.445 120.460 ↓ 1.0 82,216 1

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

  • Sort Key: transactions_9.loan_id, transactions_9.id
  • Sort Method: external sort Disk: 1696kB
109. 34.015 34.015 ↓ 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.007..34.015 rows=82,216 loops=1)

110.          

CTE total_disbursed_this_period

111. 0.011 273.949 ↑ 200.0 1 1

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

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

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

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

Hash Join (cost=25,555.33..26,456.37 rows=1,687 width=16) (actual time=273.909..273.930 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.508 272.884 ↑ 1.2 1,681 1

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

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

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

  • Hash Cond: (ae_6.loan_id = transactions_10.loan_id)
116. 0.164 0.164 ↑ 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.164 rows=471 loops=1)

117. 1.526 18.058 ↑ 1.0 3,811 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 226kB
118. 16.532 16.532 ↑ 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.015..16.532 rows=3,811 loops=1)

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

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

  • Buckets: 131072 Batches: 1 Memory Usage: 3842kB
120. 41.530 225.091 ↑ 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.159..225.091 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.658 183.561 ↓ 1.0 82,216 1

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

122. 86.994 120.903 ↓ 1.0 82,216 1

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

  • Sort Key: transactions_11.loan_id, transactions_11.id
  • Sort Method: external sort Disk: 1696kB
123. 33.909 33.909 ↓ 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.909 rows=82,216 loops=1)

124. 0.155 0.311 ↑ 3.2 471 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 40kB
125. 0.156 0.156 ↑ 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.001..0.156 rows=471 loops=1)

126.          

CTE total_paid_this_period

127. 1.127 357.012 ↑ 120.7 233 1

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

  • Group Key: transactions_12.loan_id, ae_7.transaction_id
128. 0.343 355.885 ↑ 64.6 435 1

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

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

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

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

Hash Join (cost=11,342.94..18,517.06 rows=28,591 width=153) (actual time=88.892..102.818 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.612 1.445 ↑ 8.4 885 1

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

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

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

  • Sort Key: ae_7.transaction_id
  • Sort Method: quicksort Memory: 47kB
133. 0.109 0.109 ↑ 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.109 rows=471 loops=1)

134. 0.316 0.469 ↑ 1.7 884 1

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

  • Sort Key: irad_2.transaction_id
  • Sort Method: quicksort Memory: 61kB
135. 0.153 0.153 ↑ 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.001..0.153 rows=471 loops=1)

136. 40.061 88.231 ↑ 1.0 64,110 1

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

  • Buckets: 32768 Batches: 4 Memory Usage: 3114kB
137. 48.170 48.170 ↑ 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.017..48.170 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.033 251.922 ↑ 1.0 80,131 1

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

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

Subquery Scan on x_6 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=98.401..223.889 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.030 182.175 ↓ 1.0 82,216 1

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

141. 86.485 120.145 ↓ 1.0 82,216 1

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

  • Sort Key: transactions_13.loan_id, transactions_13.id
  • Sort Method: external sort Disk: 1696kB
142. 33.660 33.660 ↓ 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.008..33.660 rows=82,216 loops=1)

143.          

CTE guaranteed_total_paid_this_period

144. 0.223 16,921.476 ↓ 6.2 234 1

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

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

Sort (cost=22,720.31..22,720.40 rows=38 width=56) (actual time=16,921.191..16,921.253 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.179 16,921.116 ↓ 6.2 234 1

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

147. 0.682 16,920.937 ↓ 11.8 449 1

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

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

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

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

Merge Join (cost=470.66..4,794.08 rows=38 width=185) (actual time=54.210..16,664.471 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,842.588 16,420.774 ↓ 7.1 312,155 1

Nested Loop (cost=298.02..25,067.45 rows=43,742 width=193) (actual time=26.485..16,420.774 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.273 4.573 ↓ 78.5 471 1

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

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

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

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

Merge Join (cost=178.56..197.64 rows=37 width=52) (actual time=0.638..1.344 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.294 0.432 ↑ 2.1 471 1

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

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

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

156. 0.275 0.429 ↑ 3.2 471 1

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

  • Sort Key: irad_3.transaction_id, irad_3.adjustment_type
  • Sort Method: quicksort Memory: 61kB
157. 0.154 0.154 ↑ 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.002..0.154 rows=471 loops=1)

158. 0.597 1.349 ↓ 1.8 555 1

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

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

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

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

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

161. 49.329 49.329 ↑ 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.329 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. 80.596 81.756 ↓ 192.9 312,243 1

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

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

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

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

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

  • Buckets: 131072 Batches: 1 Memory Usage: 3842kB
165. 41.528 224.251 ↑ 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.696..224.251 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.136 182.723 ↓ 1.0 82,216 1

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

167. 86.411 120.587 ↓ 1.0 82,216 1

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

  • Sort Key: transactions_15.loan_id, transactions_15.id
  • Sort Method: external sort Disk: 1696kB
168. 34.176 34.176 ↓ 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..34.176 rows=82,216 loops=1)

169.          

CTE current_period_last_payment

170. 0.161 321.161 ↑ 8.6 222 1

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

171. 0.269 321.000 ↑ 64.6 435 1

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

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

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

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

Hash Join (cost=9,868.94..15,481.06 rows=28,591 width=16) (actual time=64.223..68.436 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.577 1.490 ↑ 8.4 885 1

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

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

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

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

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

177. 0.372 0.528 ↑ 1.7 884 1

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

  • Sort Key: irad_4.transaction_id
  • Sort Method: quicksort Memory: 61kB
178. 0.156 0.156 ↑ 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.156 rows=471 loops=1)

179. 22.027 63.134 ↑ 1.0 64,110 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 3779kB
180. 41.107 41.107 ↑ 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.029..41.107 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. 28.505 251.487 ↑ 1.0 80,131 1

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

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

Subquery Scan on x_8 (cost=14,036.78..16,912.52 rows=80,798 width=4) (actual time=97.615..222.982 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.036 181.569 ↓ 1.0 82,216 1

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

184. 86.638 119.533 ↓ 1.0 82,216 1

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

  • Sort Key: transactions_17.loan_id, transactions_17.id
  • Sort Method: external sort Disk: 1696kB
185. 32.895 32.895 ↓ 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.005..32.895 rows=82,216 loops=1)

186.          

CTE dates_when_deliquency_started

187. 0.006 12,692.611 ↑ 16.7 12 1

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

188. 0.036 12,692.605 ↑ 3,451.9 17 1

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

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

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

190. 9.754 12,665.244 ↑ 133.4 264 1

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

191. 38.325 12,655.490 ↓ 1.1 40,173 1

Sort (cost=43,224.91..43,312.94 rows=35,210 width=80) (actual time=12,646.161..12,655.490 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.456 12,617.165 ↓ 1.1 40,173 1

Merge Join (cost=38,709.92..40,565.91 rows=35,210 width=80) (actual time=12,585.718..12,617.165 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.258 11,697.150 ↑ 3.2 471 1

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

  • Sort Key: irad_5.transaction_id
  • Sort Method: quicksort Memory: 47kB
194. 11,696.892 11,696.892 ↑ 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,695.730..11,696.892 rows=471 loops=1)

195. 21.409 898.559 ↓ 3.2 45,328 1

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

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

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

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

Hash Join (cost=188.84..19,509.69 rows=14,369 width=16) (actual time=2.858..603.080 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.893 24.893 ↓ 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.893 rows=82,216 loops=1)

199. 0.150 2.200 ↑ 2.1 471 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
200. 0.241 2.050 ↑ 2.1 471 1

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

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

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

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

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

203. 0.611 1.396 ↑ 1.0 1,934 1

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

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

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

205. 0.007 0.015 ↑ 52.9 7 1

Hash (cost=13.70..13.70 rows=370 width=36) (actual time=0.015..0.015 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.006..0.008 rows=7 loops=1)

207. 27.546 252.737 ↑ 1.0 80,131 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 3842kB
208. 41.770 225.191 ↑ 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.157..225.191 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. 62.211 183.421 ↓ 1.0 82,216 1

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

210. 87.419 121.210 ↓ 1.0 82,216 1

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

  • Sort Key: transactions_19.loan_id, transactions_19.id
  • Sort Method: external sort Disk: 1696kB
211. 33.791 33.791 ↓ 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.791 rows=82,216 loops=1)

212. 26.928 26.928 ↓ 0.0 0 264

Function Scan on fn_next_transaction nt (cost=0.25..15.25 rows=2 width=8) (actual time=0.102..0.102 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.200 7.536 ↑ 239.8 233 1

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

215. 0.534 0.534 ↑ 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.534 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.831 1.152 ↑ 24.0 471 1

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

  • Hash Cond: (lpbad_1.transaction_id = ltbad_2.transaction_id)
219. 0.123 0.123 ↑ 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.123 rows=471 loops=1)

220. 0.100 0.198 ↓ 1.3 264 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
221. 0.098 0.098 ↓ 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.003..0.098 rows=264 loops=1)

222. 12.735 103,536.830 ↑ 560,822.4 471 1

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

  • Hash Cond: (l.id = lrpi.loan_id)
223. 0.442 103,523.911 ↑ 560,822.4 471 1

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

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

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

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

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

226. 0.550 90,830.830 ↑ 560,822.4 471 1

Materialize (cost=34,916.88..4,556,380.04 rows=264,147,345 width=648) (actual time=90,826.620..90,830.830 rows=471 loops=1)

227. 0.621 90,830.280 ↑ 560,822.4 471 1

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

  • Merge Cond: ((ae.transaction_id = cb.transaction_id) AND (ae.adjustment_type = cb.adjustment_type))
228. 0.640 90,827.795 ↑ 560,822.4 471 1

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

  • Merge Cond: ((ae.transaction_id = irad.transaction_id) AND (ae.adjustment_type = irad.adjustment_type))
229. 0.470 18,603.051 ↑ 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.001..18,603.051 rows=471 loops=1)

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

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

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

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

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

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

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

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

  • Hash Cond: (cbp.loan_id = l.id)
234. 7.714 7.714 ↑ 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.039..7.714 rows=233 loops=1)

235. 0.279 992.206 ↑ 2.9 471 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 99kB
236. 0.280 991.927 ↑ 2.9 471 1

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

  • Hash Cond: (l.id = lsm.loan_id)
237. 0.371 991.597 ↑ 2.9 471 1

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

  • Merge Cond: (ae.transaction_id = gsl.transaction_id)
238. 0.401 986.332 ↑ 2.1 471 1

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Hash Cond: (ae.loan_id = l.id)
245. 0.129 0.129 ↑ 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.129 rows=471 loops=1)

246. 0.741 2.001 ↑ 1.0 1,934 1

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

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

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

248. 0.004 0.013 ↑ 52.9 7 1

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

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

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

250. 0.009 0.019 ↑ 107.5 4 1

Hash (cost=14.30..14.30 rows=430 width=8) (actual time=0.019..0.019 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.010..0.010 rows=4 loops=1)

252. 0.294 275.341 ↓ 2.4 471 1

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

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

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

254. 0.010 273.961 ↑ 200.0 1 1

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

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

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

256. 0.242 432.445 ↓ 2.4 470 1

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

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

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

258. 0.211 4.894 ↓ 1.7 470 1

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

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

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

260. 0.024 0.050 ↓ 1.6 57 1

Hash (cost=1.35..1.35 rows=35 width=50) (actual time=0.050..0.050 rows=57 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
261. 0.026 0.026 ↓ 1.6 57 1

Seq Scan on loan_state_machines lsm (cost=0.00..1.35 rows=35 width=50) (actual time=0.011..0.026 rows=57 loops=1)

262. 0.172 16,921.815 ↓ 6.2 234 1

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

  • Sort Key: gtptp.transaction_id, gtptp.adjustment_type
  • Sort Method: quicksort Memory: 43kB
263. 16,921.643 16,921.643 ↓ 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,921.206..16,921.643 rows=234 loops=1)

264. 0.199 321.462 ↑ 4.6 418 1

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

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

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

266. 0.235 357.418 ↑ 65.5 429 1

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

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

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

268. 1.482 72,224.104 ↑ 15.9 471 1

Sort (cost=19,273.34..19,292.02 rows=7,475 width=149) (actual time=72,223.906..72,224.104 rows=471 loops=1)

  • Sort Key: irad.transaction_id, irad.adjustment_type
  • Sort Method: quicksort Memory: 91kB
269. 10.660 72,222.622 ↑ 15.9 471 1

Nested Loop Left Join (cost=0.25..18,792.40 rows=7,475 width=149) (actual time=158.684..72,222.622 rows=471 loops=1)

270. 0.597 0.597 ↑ 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.002..0.597 rows=471 loops=1)

271. 72,211.365 72,211.365 ↑ 5.0 1 471

Function Scan on fn_singular_loans_status ls (cost=0.25..12.75 rows=5 width=72) (actual time=153.268..153.315 rows=1 loops=471)

  • Filter: (loan_id = irad.loan_id)
  • Rows Removed by Filter: 1869
272. 0.380 1.864 ↑ 24.0 471 1

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

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

CTE Scan on closing_balances cb (cost=0.00..225.68 rows=11,284 width=68) (actual time=0.220..1.484 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)