explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bWyW

Settings
# exclusive inclusive rows x rows loops node
1. 0.392 1,296.770 ↑ 2.5 2 1

Hash Right Join (cost=126,092.24..126,212.59 rows=5 width=580) (actual time=1,296.717..1,296.770 rows=2 loops=1)

  • Hash Cond: (cb.loan_id = l.id)
2.          

CTE last_confirmed_community_advantage_loan

3. 0.097 1.910 ↑ 1.0 299 1

Unique (cost=195.54..197.16 rows=299 width=48) (actual time=1.759..1.910 rows=299 loops=1)

4. 0.329 1.813 ↓ 3.1 999 1

Sort (cost=195.54..196.35 rows=324 width=48) (actual time=1.758..1.813 rows=999 loops=1)

  • Sort Key: carl.loan_id, carr.report_date DESC, carl.id DESC
  • Sort Method: quicksort Memory: 103kB
5. 1.235 1.484 ↓ 3.1 999 1

Hash Join (cost=1.06..182.03 rows=324 width=48) (actual time=0.037..1.484 rows=999 loops=1)

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

7. 0.005 0.012 ↓ 3.0 3 1

Hash (cost=1.05..1.05 rows=1 width=8) (actual time=0.012..0.012 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
8. 0.007 0.007 ↓ 3.0 3 1

Seq Scan on community_advantage_report_requests carr (cost=0.00..1.05 rows=1 width=8) (actual time=0.005..0.007 rows=3 loops=1)

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

CTE authorized_sales_percents

10. 6.900 6.900 ↑ 1.0 1,934 1

Seq Scan on loans l_1 (cost=0.00..569.84 rows=1,934 width=36) (actual time=0.049..6.900 rows=1,934 loops=1)

11.          

CTE last_reporting_period_info

12. 0.309 164.795 ↑ 1.0 1,573 1

Merge Full Join (cost=23,438.46..23,480.04 rows=1,597 width=40) (actual time=163.874..164.795 rows=1,573 loops=1)

  • Merge Cond: (lccal.loan_id = transactions.loan_id)
13. 0.063 2.046 ↑ 1.0 299 1

Sort (cost=18.27..19.02 rows=299 width=40) (actual time=2.026..2.046 rows=299 loops=1)

  • Sort Key: lccal.loan_id
  • Sort Method: quicksort Memory: 46kB
14. 1.983 1.983 ↑ 1.0 299 1

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

15. 0.392 162.440 ↑ 1.0 1,573 1

Unique (cost=23,420.18..23,437.32 rows=1,597 width=44) (actual time=161.845..162.440 rows=1,573 loops=1)

16. 1.353 162.048 ↓ 1.1 3,704 1

Sort (cost=23,420.18..23,428.75 rows=3,427 width=44) (actual time=161.844..162.048 rows=3,704 loops=1)

  • Sort Key: transactions.loan_id, transactions.id
  • Sort Method: quicksort Memory: 304kB
17. 13.519 160.695 ↓ 1.1 3,704 1

Hash Semi Join (cost=22,626.81..23,218.97 rows=3,427 width=44) (actual time=145.782..160.695 rows=3,704 loops=1)

  • Hash Cond: (transactions.id = x.id)
18. 1.052 15.615 ↓ 1.1 3,704 1

Merge Join (cost=6,234.50..6,296.54 rows=3,491 width=118) (actual time=14.057..15.615 rows=3,704 loops=1)

  • Merge Cond: (asp_1.loan_id = transactions.loan_id)
19. 0.566 0.822 ↑ 1.0 1,921 1

Sort (cost=144.25..149.09 rows=1,934 width=36) (actual time=0.630..0.822 rows=1,921 loops=1)

  • Sort Key: asp_1.loan_id
  • Sort Method: quicksort Memory: 139kB
20. 0.256 0.256 ↑ 1.0 1,934 1

CTE Scan on authorized_sales_percents asp_1 (cost=0.00..38.68 rows=1,934 width=36) (actual time=0.001..0.256 rows=1,934 loops=1)

21. 1.020 13.741 ↓ 1.1 3,704 1

Sort (cost=6,090.25..6,098.81 rows=3,424 width=86) (actual time=13.423..13.741 rows=3,704 loops=1)

  • Sort Key: transactions.loan_id
  • Sort Method: quicksort Memory: 617kB
22. 12.721 12.721 ↓ 1.1 3,704 1

Seq Scan on transactions (cost=0.00..5,889.24 rows=3,424 width=86) (actual time=0.013..12.721 rows=3,704 loops=1)

  • Filter: ((date <= '2018-07-31'::date) AND ((code)::text = 'LD'::text))
  • Rows Removed by Filter: 78512
23. 15.094 131.561 ↑ 1.0 80,131 1

Hash (cost=15,067.48..15,067.48 rows=80,706 width=4) (actual time=131.561..131.561 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2434kB
24. 13.976 116.467 ↑ 1.0 80,131 1

Subquery Scan on x (cost=12,189.92..15,067.48 rows=80,706 width=4) (actual time=60.337..116.467 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
25. 33.709 102.491 ↑ 1.0 82,216 1

WindowAgg (cost=12,189.92..13,834.24 rows=82,216 width=43) (actual time=60.334..102.491 rows=82,216 loops=1)

26. 52.009 68.782 ↑ 1.0 82,216 1

Sort (cost=12,189.92..12,395.46 rows=82,216 width=11) (actual time=60.325..68.782 rows=82,216 loops=1)

  • Sort Key: transactions_1.loan_id, transactions_1.id
  • Sort Method: external merge Disk: 1704kB
27. 16.773 16.773 ↑ 1.0 82,216 1

Seq Scan on transactions transactions_1 (cost=0.00..5,478.16 rows=82,216 width=11) (actual time=0.014..16.773 rows=82,216 loops=1)

28.          

CTE total_disbursed_amount

29. 0.002 14.452 ↓ 0.0 0 1

GroupAggregate (cost=22,805.15..22,805.21 rows=3 width=12) (actual time=14.452..14.452 rows=0 loops=1)

  • Group Key: transactions_2.loan_id
30. 0.009 14.450 ↓ 0.0 0 1

Sort (cost=22,805.15..22,805.15 rows=3 width=12) (actual time=14.450..14.450 rows=0 loops=1)

  • Sort Key: transactions_2.loan_id
  • Sort Method: quicksort Memory: 25kB
31. 0.000 14.441 ↓ 0.0 0 1

Hash Semi Join (cost=16,392.30..22,805.12 rows=3 width=12) (actual time=14.441..14.441 rows=0 loops=1)

  • Hash Cond: (transactions_2.id = x_1.id)
32. 14.441 14.441 ↓ 0.0 0 1

Seq Scan on transactions transactions_2 (cost=0.00..6,094.78 rows=3 width=16) (actual time=14.441..14.441 rows=0 loops=1)

  • Filter: ((loan_id = ANY ('{1931,1933}'::integer[])) AND (date <= '2018-07-31'::date) AND ((code)::text = 'LD'::text))
  • Rows Removed by Filter: 82216
33. 0.000 0.000 ↓ 0.0 0

Hash (cost=15,067.48..15,067.48 rows=80,706 width=4) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Subquery Scan on x_1 (cost=12,189.92..15,067.48 rows=80,706 width=4) (never executed)

  • Filter: (((x_1.code)::text !~~ 'RV%'::text) AND ((COALESCE(x_1.next_code, ''::character varying))::text !~~ 'RV%'::text))
35. 0.000 0.000 ↓ 0.0 0

WindowAgg (cost=12,189.92..13,834.24 rows=82,216 width=43) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Sort (cost=12,189.92..12,395.46 rows=82,216 width=11) (never executed)

  • Sort Key: transactions_3.loan_id, transactions_3.id
37. 0.000 0.000 ↓ 0.0 0

Seq Scan on transactions transactions_3 (cost=0.00..5,478.16 rows=82,216 width=11) (never executed)

38.          

CTE total_disbursed_this_period

39. 0.019 153.191 ↑ 6.1 18 1

GroupAggregate (cost=22,810.38..22,812.87 rows=110 width=12) (actual time=153.177..153.191 rows=18 loops=1)

  • Group Key: transactions_4.loan_id
40. 0.023 153.172 ↑ 2.7 41 1

Sort (cost=22,810.38..22,810.66 rows=111 width=12) (actual time=153.168..153.172 rows=41 loops=1)

  • Sort Key: transactions_4.loan_id
  • Sort Method: quicksort Memory: 26kB
41. 6.346 153.149 ↑ 2.7 41 1

Hash Semi Join (cost=16,392.30..22,806.61 rows=111 width=12) (actual time=135.350..153.149 rows=41 loops=1)

  • Hash Cond: (transactions_4.id = x_2.id)
42. 11.768 11.768 ↑ 2.8 41 1

Seq Scan on transactions transactions_4 (cost=0.00..6,094.78 rows=113 width=16) (actual time=0.151..11.768 rows=41 loops=1)

  • Filter: ((date >= '2018-07-01'::date) AND (date <= '2018-07-31'::date) AND ((code)::text = 'LD'::text))
  • Rows Removed by Filter: 82175
43. 16.116 135.035 ↑ 1.0 80,131 1

Hash (cost=15,067.48..15,067.48 rows=80,706 width=4) (actual time=135.035..135.035 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2434kB
44. 14.839 118.919 ↑ 1.0 80,131 1

Subquery Scan on x_2 (cost=12,189.92..15,067.48 rows=80,706 width=4) (actual time=57.608..118.919 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
45. 37.299 104.080 ↑ 1.0 82,216 1

WindowAgg (cost=12,189.92..13,834.24 rows=82,216 width=43) (actual time=57.605..104.080 rows=82,216 loops=1)

46. 50.526 66.781 ↑ 1.0 82,216 1

Sort (cost=12,189.92..12,395.46 rows=82,216 width=11) (actual time=57.589..66.781 rows=82,216 loops=1)

  • Sort Key: transactions_5.loan_id, transactions_5.id
  • Sort Method: external merge Disk: 1704kB
47. 16.255 16.255 ↑ 1.0 82,216 1

Seq Scan on transactions transactions_5 (cost=0.00..5,478.16 rows=82,216 width=11) (actual time=0.003..16.255 rows=82,216 loops=1)

48.          

CTE current_period_last_payment

49. 0.176 144.332 ↑ 1.5 843 1

Unique (cost=24,291.12..24,300.78 rows=1,233 width=13) (actual time=144.080..144.332 rows=843 loops=1)

50. 0.404 144.156 ↑ 1.5 1,304 1

Sort (cost=24,291.12..24,295.95 rows=1,931 width=13) (actual time=144.079..144.156 rows=1,304 loops=1)

  • Sort Key: transactions_6.loan_id, transactions_6.id DESC
  • Sort Method: quicksort Memory: 110kB
51. 7.963 143.752 ↑ 1.5 1,304 1

Hash Semi Join (cost=16,392.30..24,185.74 rows=1,931 width=13) (actual time=125.028..143.752 rows=1,304 loops=1)

  • Hash Cond: (transactions_6.id = x_3.id)
52. 10.826 10.826 ↑ 1.5 1,343 1

Seq Scan on transactions transactions_6 (cost=0.00..7,430.79 rows=1,967 width=12) (actual time=0.015..10.826 rows=1,343 loops=1)

  • Filter: ((date >= '2018-07-01'::date) AND (date <= '2018-07-31'::date) AND ((code)::text = ANY ('{PM,PMI,PMP,PMM,PRE,LP,LPI,LPP,LPM,AT,ATI,ATP,ATM,SK,RC}'::text[])))
  • Rows Removed by Filter: 80873
53. 14.253 124.963 ↑ 1.0 80,131 1

Hash (cost=15,067.48..15,067.48 rows=80,706 width=4) (actual time=124.963..124.963 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2434kB
54. 14.007 110.710 ↑ 1.0 80,131 1

Subquery Scan on x_3 (cost=12,189.92..15,067.48 rows=80,706 width=4) (actual time=54.731..110.710 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
55. 33.371 96.703 ↑ 1.0 82,216 1

WindowAgg (cost=12,189.92..13,834.24 rows=82,216 width=43) (actual time=54.728..96.703 rows=82,216 loops=1)

56. 46.778 63.332 ↑ 1.0 82,216 1

Sort (cost=12,189.92..12,395.46 rows=82,216 width=11) (actual time=54.720..63.332 rows=82,216 loops=1)

  • Sort Key: transactions_7.loan_id, transactions_7.id
  • Sort Method: external merge Disk: 1704kB
57. 16.554 16.554 ↑ 1.0 82,216 1

Seq Scan on transactions transactions_7 (cost=0.00..5,478.16 rows=82,216 width=11) (actual time=0.003..16.554 rows=82,216 loops=1)

58.          

CTE total_paid_this_period

59. 0.186 151.351 ↑ 1.5 843 1

Subquery Scan on x_4 (cost=24,357.12..24,439.31 rows=1,233 width=100) (actual time=148.686..151.351 rows=843 loops=1)

60. 2.410 151.165 ↑ 1.5 843 1

GroupAggregate (cost=24,357.12..24,423.89 rows=1,233 width=68) (actual time=148.684..151.165 rows=843 loops=1)

  • Group Key: transactions_8.loan_id
61. 0.513 148.755 ↑ 1.5 1,304 1

Sort (cost=24,357.12..24,361.95 rows=1,931 width=145) (actual time=148.667..148.755 rows=1,304 loops=1)

  • Sort Key: transactions_8.loan_id
  • Sort Method: quicksort Memory: 395kB
62. 7.311 148.242 ↑ 1.5 1,304 1

Hash Semi Join (cost=16,392.30..24,251.74 rows=1,931 width=145) (actual time=128.432..148.242 rows=1,304 loops=1)

  • Hash Cond: (transactions_8.id = x_5.id)
63. 12.579 12.579 ↑ 1.5 1,343 1

Seq Scan on transactions transactions_8 (cost=0.00..7,430.79 rows=1,967 width=149) (actual time=0.024..12.579 rows=1,343 loops=1)

  • Filter: ((date >= '2018-07-01'::date) AND (date <= '2018-07-31'::date) AND ((code)::text = ANY ('{PM,PMI,PMP,PMM,PRE,LP,LPI,LPP,LPM,AT,ATI,ATP,ATM,SK,RC}'::text[])))
  • Rows Removed by Filter: 80873
64. 14.805 128.352 ↑ 1.0 80,131 1

Hash (cost=15,067.48..15,067.48 rows=80,706 width=4) (actual time=128.352..128.352 rows=80,131 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2434kB
65. 14.098 113.547 ↑ 1.0 80,131 1

Subquery Scan on x_5 (cost=12,189.92..15,067.48 rows=80,706 width=4) (actual time=56.941..113.547 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
66. 33.962 99.449 ↑ 1.0 82,216 1

WindowAgg (cost=12,189.92..13,834.24 rows=82,216 width=43) (actual time=56.938..99.449 rows=82,216 loops=1)

67. 49.493 65.487 ↑ 1.0 82,216 1

Sort (cost=12,189.92..12,395.46 rows=82,216 width=11) (actual time=56.930..65.487 rows=82,216 loops=1)

  • Sort Key: transactions_9.loan_id, transactions_9.id
  • Sort Method: external merge Disk: 1704kB
68. 15.994 15.994 ↑ 1.0 82,216 1

Seq Scan on transactions transactions_9 (cost=0.00..5,478.16 rows=82,216 width=11) (actual time=0.003..15.994 rows=82,216 loops=1)

69.          

CTE dates_when_deliquency_started

70. 0.000 22.084 ↓ 0.0 0 1

Unique (cost=6,399.48..6,399.51 rows=5 width=12) (actual time=22.084..22.084 rows=0 loops=1)

71. 0.005 22.084 ↓ 0.0 0 1

Sort (cost=6,399.48..6,399.50 rows=5 width=12) (actual time=22.084..22.084 rows=0 loops=1)

  • Sort Key: t.loan_id, t.id DESC
  • Sort Method: quicksort Memory: 25kB
72. 0.001 22.079 ↓ 0.0 0 1

Nested Loop (cost=17.12..6,399.43 rows=5 width=12) (actual time=22.079..22.079 rows=0 loops=1)

73. 6.052 22.078 ↓ 0.0 0 1

Hash Join (cost=16.87..6,008.66 rows=26 width=12) (actual time=22.078..22.078 rows=0 loops=1)

  • Hash Cond: (t.loan_id = l_2.id)
  • Join Filter: (round((((fn_max_delinquency(t.delinquency))::numeric * ('365'::numeric * pf_1.monthly_multiplier)) / '12'::numeric), 2) < '31'::numeric)
74. 15.995 15.995 ↓ 1.0 76,659 1

Seq Scan on transactions t (cost=0.00..5,683.70 rows=76,363 width=25) (actual time=0.012..15.995 rows=76,659 loops=1)

  • Filter: (date <= '2018-07-31'::date)
  • Rows Removed by Filter: 5557
75. 0.002 0.031 ↑ 1.0 2 1

Hash (cost=16.84..16.84 rows=2 width=36) (actual time=0.031..0.031 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
76. 0.003 0.029 ↑ 1.0 2 1

Nested Loop (cost=8.57..16.84 rows=2 width=36) (actual time=0.027..0.029 rows=2 loops=1)

  • Join Filter: (l_2.payment_frequency_id = pf_1.id)
  • Rows Removed by Join Filter: 12
77. 0.005 0.005 ↑ 1.0 7 1

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

78. 0.006 0.021 ↑ 1.0 2 7

Materialize (cost=8.57..15.57 rows=2 width=8) (actual time=0.002..0.003 rows=2 loops=7)

79. 0.005 0.015 ↑ 1.0 2 1

Bitmap Heap Scan on loans l_2 (cost=8.57..15.56 rows=2 width=8) (actual time=0.013..0.015 rows=2 loops=1)

  • Recheck Cond: (id = ANY ('{1931,1933}'::integer[]))
  • Heap Blocks: exact=2
80. 0.010 0.010 ↑ 1.0 2 1

Bitmap Index Scan on loans_pkey (cost=0.00..8.57 rows=2 width=0) (actual time=0.010..0.010 rows=2 loops=1)

  • Index Cond: (id = ANY ('{1931,1933}'::integer[]))
81. 0.000 0.000 ↓ 0.0 0

Function Scan on fn_next_transaction nt (cost=0.25..15.25 rows=2 width=8) (never executed)

  • Filter: ((date <= '2018-07-31'::date) AND (t.loan_id = loan_id))
82.          

CTE calendar_basis_points

83. 1.086 5.188 ↑ 5.1 196 1

Nested Loop (cost=0.14..606.02 rows=1,003 width=36) (actual time=0.028..5.188 rows=196 loops=1)

84. 0.234 0.234 ↑ 1.0 1,934 1

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

85. 3.868 3.868 ↓ 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.14..0.26 rows=1 width=72) (actual time=0.002..0.002 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
86.          

CTE closing_balances

87. 2.306 257.568 ↑ 2.7 1,882 1

Merge Full Join (cost=120.16..250.16 rows=5,000 width=36) (actual time=255.007..257.568 rows=1,882 loops=1)

  • Merge Cond: (lp_1.loan_id = lt_1.loan_id)
88. 0.395 196.837 ↓ 1.9 1,855 1

Sort (cost=60.08..62.58 rows=1,000 width=36) (actual time=196.697..196.837 rows=1,855 loops=1)

  • Sort Key: lp_1.loan_id
  • Sort Method: quicksort Memory: 309kB
89. 196.442 196.442 ↓ 1.9 1,855 1

Function Scan on fn_last_payments lp_1 (cost=0.25..10.25 rows=1,000 width=36) (actual time=196.025..196.442 rows=1,855 loops=1)

90. 0.369 58.425 ↓ 1.9 1,882 1

Sort (cost=60.08..62.58 rows=1,000 width=36) (actual time=58.301..58.425 rows=1,882 loops=1)

  • Sort Key: lt_1.loan_id
  • Sort Method: quicksort Memory: 313kB
91. 58.056 58.056 ↓ 1.9 1,882 1

Function Scan on fn_last_transactions lt_1 (cost=0.25..10.25 rows=1,000 width=36) (actual time=57.688..58.056 rows=1,882 loops=1)

92. 258.118 258.118 ↑ 2.7 1,882 1

CTE Scan on closing_balances cb (cost=0.00..100.00 rows=5,000 width=36) (actual time=255.009..258.118 rows=1,882 loops=1)

93. 0.006 1,038.260 ↑ 1.0 2 1

Hash (cost=231.32..231.32 rows=2 width=505) (actual time=1,038.260..1,038.260 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
94. 0.013 1,038.254 ↑ 1.0 2 1

Hash Left Join (cost=193.35..231.32 rows=2 width=505) (actual time=1,038.249..1,038.254 rows=2 loops=1)

  • Hash Cond: (l.id = dwds.loan_id)
95. 0.159 1,016.155 ↑ 1.0 2 1

Hash Right Join (cost=193.19..231.14 rows=2 width=501) (actual time=1,016.152..1,016.155 rows=2 loops=1)

  • Hash Cond: (lrpi.loan_id = l.id)
96. 165.214 165.214 ↑ 1.0 1,573 1

CTE Scan on last_reporting_period_info lrpi (cost=0.00..31.94 rows=1,597 width=40) (actual time=163.876..165.214 rows=1,573 loops=1)

97. 0.004 850.782 ↑ 1.0 2 1

Hash (cost=193.17..193.17 rows=2 width=465) (actual time=850.782..850.782 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
98. 0.088 850.778 ↑ 1.0 2 1

Hash Right Join (cost=163.87..193.17 rows=2 width=465) (actual time=850.776..850.778 rows=2 loops=1)

  • Hash Cond: (cplp.loan_id = l.id)
99. 144.533 144.533 ↑ 1.5 843 1

CTE Scan on current_period_last_payment cplp (cost=0.00..24.66 rows=1,233 width=5) (actual time=144.082..144.533 rows=843 loops=1)

100. 0.002 706.157 ↑ 1.0 2 1

Hash (cost=163.85..163.85 rows=2 width=464) (actual time=706.157..706.157 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
101. 0.040 706.155 ↑ 1.0 2 1

Hash Right Join (cost=140.02..163.85 rows=2 width=464) (actual time=700.953..706.155 rows=2 loops=1)

  • Hash Cond: (cbp.loan_id = l.id)
102. 5.242 5.242 ↑ 5.1 196 1

CTE Scan on calendar_basis_points cbp (cost=0.00..20.06 rows=1,003 width=36) (actual time=0.030..5.242 rows=196 loops=1)

103. 0.004 700.873 ↑ 1.0 2 1

Hash (cost=139.99..139.99 rows=2 width=432) (actual time=700.873..700.873 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
104. 0.198 700.869 ↑ 1.0 2 1

Hash Right Join (cost=126.23..139.99 rows=2 width=432) (actual time=700.867..700.869 rows=2 loops=1)

  • Hash Cond: (lp.loan_id = l.id)
105. 183.741 183.741 ↓ 1.9 1,855 1

Function Scan on fn_last_payments lp (cost=0.25..10.25 rows=1,000 width=8) (actual time=183.588..183.741 rows=1,855 loops=1)

106. 0.005 516.930 ↑ 1.0 2 1

Hash (cost=125.96..125.96 rows=2 width=428) (actual time=516.930..516.930 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
107. 0.252 516.925 ↑ 1.0 2 1

Hash Right Join (cost=112.20..125.96 rows=2 width=428) (actual time=516.922..516.925 rows=2 loops=1)

  • Hash Cond: (lt.loan_id = l.id)
108. 57.142 57.142 ↓ 1.9 1,882 1

Function Scan on fn_last_transactions lt (cost=0.25..10.25 rows=1,000 width=100) (actual time=56.860..57.142 rows=1,882 loops=1)

109. 0.003 459.531 ↑ 1.0 2 1

Hash (cost=111.92..111.92 rows=2 width=332) (actual time=459.531..459.531 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
110. 0.205 459.528 ↑ 1.0 2 1

Hash Right Join (cost=98.16..111.92 rows=2 width=332) (actual time=459.525..459.528 rows=2 loops=1)

  • Hash Cond: (ls.loan_id = l.id)
111. 132.162 132.162 ↓ 1.3 1,265 1

Function Scan on fn_proxy_loans_status ls (cost=0.25..10.25 rows=1,000 width=72) (actual time=132.008..132.162 rows=1,265 loops=1)

112. 0.003 327.161 ↑ 1.0 2 1

Hash (cost=97.89..97.89 rows=2 width=264) (actual time=327.161..327.161 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
113. 0.004 327.158 ↑ 1.0 2 1

Nested Loop (cost=49.48..97.89 rows=2 width=264) (actual time=327.151..327.158 rows=2 loops=1)

  • Join Filter: (l.calculation_method_id = cm.id)
  • Rows Removed by Join Filter: 6
114. 0.006 0.006 ↑ 1.0 4 1

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

115. 0.006 327.148 ↑ 1.0 2 4

Materialize (cost=49.48..96.73 rows=2 width=264) (actual time=81.786..81.787 rows=2 loops=4)

116. 0.007 327.142 ↑ 1.0 2 1

Nested Loop (cost=49.48..96.72 rows=2 width=264) (actual time=327.139..327.142 rows=2 loops=1)

  • Join Filter: (l.payment_frequency_id = pf.id)
  • Rows Removed by Join Filter: 12
117. 0.004 0.004 ↑ 1.0 7 1

Seq Scan on payment_frequencies pf (cost=0.00..1.07 rows=7 width=68) (actual time=0.003..0.004 rows=7 loops=1)

118. 0.005 327.131 ↑ 1.0 2 7

Materialize (cost=49.48..95.45 rows=2 width=204) (actual time=45.661..46.733 rows=2 loops=7)

119. 0.220 327.126 ↑ 1.0 2 1

Hash Join (cost=49.48..95.44 rows=2 width=204) (actual time=319.621..327.126 rows=2 loops=1)

  • Hash Cond: (asp.loan_id = l.id)
120. 7.423 7.423 ↑ 1.0 1,934 1

CTE Scan on authorized_sales_percents asp (cost=0.00..38.68 rows=1,934 width=36) (actual time=0.050..7.423 rows=1,934 loops=1)

121. 0.003 319.483 ↑ 1.0 2 1

Hash (cost=49.46..49.46 rows=2 width=172) (actual time=319.483..319.483 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
122. 0.105 319.480 ↑ 1.0 2 1

Hash Right Join (cost=20.17..49.46 rows=2 width=172) (actual time=319.478..319.480 rows=2 loops=1)

  • Hash Cond: (tptp.loan_id = l.id)
123. 151.613 151.613 ↑ 1.5 843 1

CTE Scan on total_paid_this_period tptp (cost=0.00..24.66 rows=1,233 width=68) (actual time=148.688..151.613 rows=843 loops=1)

124. 0.004 167.762 ↑ 1.0 2 1

Hash (cost=20.14..20.14 rows=2 width=108) (actual time=167.762..167.762 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
125. 0.019 167.758 ↑ 1.0 2 1

Hash Right Join (cost=17.52..20.14 rows=2 width=108) (actual time=167.756..167.758 rows=2 loops=1)

  • Hash Cond: (tdtp.loan_id = l.id)
126. 153.198 153.198 ↑ 6.1 18 1

CTE Scan on total_disbursed_this_period tdtp (cost=0.00..2.20 rows=110 width=12) (actual time=153.179..153.198 rows=18 loops=1)

127. 0.002 14.541 ↑ 1.0 2 1

Hash (cost=17.49..17.49 rows=2 width=100) (actual time=14.541..14.541 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
128. 0.027 14.539 ↑ 1.0 2 1

Hash Right Join (cost=15.71..17.49 rows=2 width=100) (actual time=14.528..14.539 rows=2 loops=1)

  • Hash Cond: (lsm.loan_id = l.id)
129. 0.015 0.015 ↓ 1.0 58 1

Seq Scan on loan_state_machines lsm (cost=0.00..1.56 rows=56 width=48) (actual time=0.012..0.015 rows=58 loops=1)

130. 0.005 14.497 ↑ 1.0 2 1

Hash (cost=15.69..15.69 rows=2 width=56) (actual time=14.497..14.497 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
131. 0.003 14.492 ↑ 1.0 2 1

Merge Left Join (cost=15.65..15.69 rows=2 width=56) (actual time=14.490..14.492 rows=2 loops=1)

  • Merge Cond: (l.id = tda.loan_id)
132. 0.010 0.030 ↑ 1.0 2 1

Sort (cost=15.57..15.57 rows=2 width=48) (actual time=0.029..0.030 rows=2 loops=1)

  • Sort Key: l.id
  • Sort Method: quicksort Memory: 25kB
133. 0.005 0.020 ↑ 1.0 2 1

Bitmap Heap Scan on loans l (cost=8.57..15.56 rows=2 width=48) (actual time=0.018..0.020 rows=2 loops=1)

  • Recheck Cond: (id = ANY ('{1931,1933}'::integer[]))
  • Heap Blocks: exact=2
134. 0.015 0.015 ↑ 1.0 2 1

Bitmap Index Scan on loans_pkey (cost=0.00..8.57 rows=2 width=0) (actual time=0.015..0.015 rows=2 loops=1)

  • Index Cond: (id = ANY ('{1931,1933}'::integer[]))
135. 0.007 14.459 ↓ 0.0 0 1

Sort (cost=0.08..0.09 rows=3 width=12) (actual time=14.459..14.459 rows=0 loops=1)

  • Sort Key: tda.loan_id
  • Sort Method: quicksort Memory: 25kB
136. 14.452 14.452 ↓ 0.0 0 1

CTE Scan on total_disbursed_amount tda (cost=0.00..0.06 rows=3 width=12) (actual time=14.452..14.452 rows=0 loops=1)

137. 0.001 22.086 ↓ 0.0 0 1

Hash (cost=0.10..0.10 rows=5 width=8) (actual time=22.086..22.086 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
138. 22.085 22.085 ↓ 0.0 0 1

CTE Scan on dates_when_deliquency_started dwds (cost=0.00..0.10 rows=5 width=8) (actual time=22.085..22.085 rows=0 loops=1)