explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uArc

Settings
# exclusive inclusive rows x rows loops node
1. 2,295.896 2,295.896 ↓ 46.2 878 1

CTE Scan on stg (cost=82,351.07..82,437.65 rows=19 width=2,136) (actual time=2,267.062..2,295.896 rows=878 loops=1)

  • Filter: (p_closing = 1)
  • Rows Removed by Filter: 2,157
2.          

CTE transition_events

3. 8.280 33.611 ↑ 1.1 5,578 1

Finalize HashAggregate (cost=1,435.81..1,509.47 rows=5,893 width=20) (actual time=28.173..33.611 rows=5,578 loops=1)

  • Group Key: loan_status_transition_events.loan_application_id, loan_status_transition_events.to_status
4. 8.823 25.331 ↓ 1.5 5,860 1

Gather (cost=976.85..1,406.51 rows=3,906 width=20) (actual time=22.272..25.331 rows=5,860 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 3.184 16.508 ↑ 1.3 2,930 2 / 2

Partial HashAggregate (cost=966.85..1,005.91 rows=3,906 width=20) (actual time=15.564..16.508 rows=2,930 loops=2)

  • Group Key: loan_status_transition_events.loan_application_id, loan_status_transition_events.to_status
6. 13.324 13.324 ↑ 1.2 3,376 2 / 2

Parallel Seq Scan on loan_status_transition_events (cost=0.00..937.56 rows=3,906 width=20) (actual time=0.040..13.324 rows=3,376 loops=2)

  • Filter: (to_status = ANY ('{7,10}'::integer[]))
  • Rows Removed by Filter: 46,628
7.          

CTE excl_addr

8. 0.011 19.191 ↓ 11.0 11 1

Nested Loop (cost=0.72..1,094.69 rows=1 width=29) (actual time=1.774..19.191 rows=11 loops=1)

9. 0.324 19.125 ↓ 11.0 11 1

Nested Loop (cost=0.58..1,094.43 rows=1 width=37) (actual time=1.760..19.125 rows=11 loops=1)

10. 1.068 12.435 ↓ 1.1 1,061 1

Nested Loop (cost=0.29..780.15 rows=938 width=32) (actual time=0.032..12.435 rows=1,061 loops=1)

11. 7.123 7.123 ↓ 1.0 1,061 1

Seq Scan on loan_applications la (cost=0.00..392.73 rows=1,055 width=16) (actual time=0.014..7.123 rows=1,061 loops=1)

  • Filter: (loan_status_id = 23)
  • Rows Removed by Filter: 20,951
12. 4.244 4.244 ↑ 1.0 1 1,061

Index Scan using property_information_loan_application_idx on property_information pri (cost=0.29..0.36 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1,061)

  • Index Cond: (loan_application_id = la.id)
  • Filter: ((NOT is_removed) AND (NOT is_removed))
13. 6.366 6.366 ↓ 0.0 0 1,061

Index Scan using addresses_pkey on addresses addr (cost=0.29..0.33 rows=1 width=37) (actual time=0.006..0.006 rows=0 loops=1,061)

  • Index Cond: (address_id = pri.address_id)
  • Filter: ((pri.loan_application_id = loan_application_id) AND (lower(street_address) ~~ '%tbd%'::text))
  • Rows Removed by Filter: 1
14. 0.055 0.055 ↑ 1.0 1 11

Index Only Scan using loan_status_pkey on loan_status lon (cost=0.14..0.26 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=11)

  • Index Cond: (id = 23)
  • Heap Fetches: 11
15.          

CTE team

16. 39.442 171.958 ↑ 1.0 22,012 1

Hash Left Join (cost=75.16..3,137.08 rows=22,013 width=232) (actual time=2.042..171.958 rows=22,012 loops=1)

  • Hash Cond: (tm_ma.person_id = p_ma.id)
17. 10.617 132.428 ↑ 1.0 22,012 1

Hash Left Join (cost=71.93..2,152.06 rows=22,013 width=400) (actual time=1.916..132.428 rows=22,012 loops=1)

  • Hash Cond: (lat.customer_care_member_id = tm_ma.id)
18. 12.126 121.721 ↑ 1.0 22,012 1

Hash Left Join (cost=66.34..1,912.76 rows=22,013 width=400) (actual time=1.813..121.721 rows=22,012 loops=1)

  • Hash Cond: (lat.received_member_id = tm_re.id)
19. 8.654 109.338 ↑ 1.0 22,012 1

Hash Left Join (cost=55.38..1,672.81 rows=22,013 width=344) (actual time=1.542..109.338 rows=22,012 loops=1)

  • Hash Cond: (lat.closer_member_id = tm_cl.id)
20. 7.467 100.422 ↑ 1.0 22,012 1

Hash Left Join (cost=44.41..1,554.86 rows=22,013 width=288) (actual time=1.265..100.422 rows=22,012 loops=1)

  • Hash Cond: (lat.secondary_member_id = tm_s.id)
21. 7.867 92.659 ↑ 1.0 22,012 1

Hash Left Join (cost=33.45..1,443.36 rows=22,013 width=232) (actual time=0.956..92.659 rows=22,012 loops=1)

  • Hash Cond: (lat.processor_member_id = tm_pr.id)
22. 8.421 84.530 ↑ 1.0 22,012 1

Hash Left Join (cost=22.49..1,308.59 rows=22,013 width=176) (actual time=0.672..84.530 rows=22,012 loops=1)

  • Hash Cond: (lat.underwriter_member_id = tm_u.id)
23. 9.009 75.852 ↑ 1.0 22,012 1

Hash Left Join (cost=11.54..1,170.87 rows=22,013 width=120) (actual time=0.391..75.852 rows=22,012 loops=1)

  • Hash Cond: (lat.quality_review_id = tm_q.id)
24. 24.107 66.524 ↑ 1.0 22,012 1

Merge Left Join (cost=0.57..1,062.66 rows=22,013 width=64) (actual time=0.053..66.524 rows=22,012 loops=1)

  • Merge Cond: (la_1.id = lat.loan_application_id)
25. 11.867 11.867 ↓ 1.0 22,012 1

Index Only Scan using loan_applications_pkey on loan_applications la_1 (cost=0.29..337.10 rows=21,994 width=8) (actual time=0.032..11.867 rows=22,012 loops=1)

  • Heap Fetches: 6,280
26. 30.550 30.550 ↑ 1.0 22,004 1

Index Scan using loan_application_team_members_unique_per_loan on loan_application_team_members lat (cost=0.29..395.61 rows=22,013 width=64) (actual time=0.014..30.550 rows=22,004 loops=1)

27. 0.041 0.319 ↑ 1.5 153 1

Hash (cost=8.19..8.19 rows=222 width=72) (actual time=0.319..0.319 rows=153 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
28. 0.101 0.278 ↑ 1.5 153 1

Hash Left Join (cost=3.23..8.19 rows=222 width=72) (actual time=0.161..0.278 rows=153 loops=1)

  • Hash Cond: (tm_q.person_id = p_q.id)
29. 0.054 0.054 ↑ 1.5 153 1

Seq Scan on team_members tm_q (cost=0.00..2.82 rows=222 width=16) (actual time=0.022..0.054 rows=153 loops=1)

30. 0.040 0.123 ↓ 1.0 121 1

Hash (cost=1.77..1.77 rows=117 width=72) (actual time=0.123..0.123 rows=121 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
31. 0.083 0.083 ↓ 1.0 121 1

Seq Scan on persons p_q (cost=0.00..1.77 rows=117 width=72) (actual time=0.010..0.083 rows=121 loops=1)

32. 0.060 0.257 ↑ 1.5 153 1

Hash (cost=8.19..8.19 rows=222 width=72) (actual time=0.257..0.257 rows=153 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
33. 0.073 0.197 ↑ 1.5 153 1

Hash Left Join (cost=3.23..8.19 rows=222 width=72) (actual time=0.104..0.197 rows=153 loops=1)

  • Hash Cond: (tm_u.person_id = p_u.id)
34. 0.041 0.041 ↑ 1.5 153 1

Seq Scan on team_members tm_u (cost=0.00..2.82 rows=222 width=16) (actual time=0.008..0.041 rows=153 loops=1)

35. 0.042 0.083 ↓ 1.0 121 1

Hash (cost=1.77..1.77 rows=117 width=72) (actual time=0.083..0.083 rows=121 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
36. 0.041 0.041 ↓ 1.0 121 1

Seq Scan on persons p_u (cost=0.00..1.77 rows=117 width=72) (actual time=0.005..0.041 rows=121 loops=1)

37. 0.067 0.262 ↑ 1.5 153 1

Hash (cost=8.19..8.19 rows=222 width=72) (actual time=0.262..0.262 rows=153 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
38. 0.084 0.195 ↑ 1.5 153 1

Hash Left Join (cost=3.23..8.19 rows=222 width=72) (actual time=0.103..0.195 rows=153 loops=1)

  • Hash Cond: (tm_pr.person_id = p_pr.id)
39. 0.028 0.028 ↑ 1.5 153 1

Seq Scan on team_members tm_pr (cost=0.00..2.82 rows=222 width=16) (actual time=0.006..0.028 rows=153 loops=1)

40. 0.041 0.083 ↓ 1.0 121 1

Hash (cost=1.77..1.77 rows=117 width=72) (actual time=0.083..0.083 rows=121 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
41. 0.042 0.042 ↓ 1.0 121 1

Seq Scan on persons p_pr (cost=0.00..1.77 rows=117 width=72) (actual time=0.005..0.042 rows=121 loops=1)

42. 0.083 0.296 ↑ 1.5 153 1

Hash (cost=8.19..8.19 rows=222 width=72) (actual time=0.296..0.296 rows=153 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
43. 0.088 0.213 ↑ 1.5 153 1

Hash Left Join (cost=3.23..8.19 rows=222 width=72) (actual time=0.111..0.213 rows=153 loops=1)

  • Hash Cond: (tm_s.person_id = p_s.id)
44. 0.036 0.036 ↑ 1.5 153 1

Seq Scan on team_members tm_s (cost=0.00..2.82 rows=222 width=16) (actual time=0.006..0.036 rows=153 loops=1)

45. 0.047 0.089 ↓ 1.0 121 1

Hash (cost=1.77..1.77 rows=117 width=72) (actual time=0.089..0.089 rows=121 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
46. 0.042 0.042 ↓ 1.0 121 1

Seq Scan on persons p_s (cost=0.00..1.77 rows=117 width=72) (actual time=0.005..0.042 rows=121 loops=1)

47. 0.052 0.262 ↑ 1.5 153 1

Hash (cost=8.19..8.19 rows=222 width=72) (actual time=0.262..0.262 rows=153 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
48. 0.087 0.210 ↑ 1.5 153 1

Hash Left Join (cost=3.23..8.19 rows=222 width=72) (actual time=0.105..0.210 rows=153 loops=1)

  • Hash Cond: (tm_cl.person_id = p_cl.id)
49. 0.039 0.039 ↑ 1.5 153 1

Seq Scan on team_members tm_cl (cost=0.00..2.82 rows=222 width=16) (actual time=0.008..0.039 rows=153 loops=1)

50. 0.045 0.084 ↓ 1.0 121 1

Hash (cost=1.77..1.77 rows=117 width=72) (actual time=0.084..0.084 rows=121 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
51. 0.039 0.039 ↓ 1.0 121 1

Seq Scan on persons p_cl (cost=0.00..1.77 rows=117 width=72) (actual time=0.005..0.039 rows=121 loops=1)

52. 0.053 0.257 ↑ 1.5 153 1

Hash (cost=8.19..8.19 rows=222 width=72) (actual time=0.257..0.257 rows=153 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
53. 0.082 0.204 ↑ 1.5 153 1

Hash Left Join (cost=3.23..8.19 rows=222 width=72) (actual time=0.104..0.204 rows=153 loops=1)

  • Hash Cond: (tm_re.person_id = p_re.id)
54. 0.036 0.036 ↑ 1.5 153 1

Seq Scan on team_members tm_re (cost=0.00..2.82 rows=222 width=16) (actual time=0.006..0.036 rows=153 loops=1)

55. 0.040 0.086 ↓ 1.0 121 1

Hash (cost=1.77..1.77 rows=117 width=72) (actual time=0.086..0.086 rows=121 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
56. 0.046 0.046 ↓ 1.0 121 1

Seq Scan on persons p_re (cost=0.00..1.77 rows=117 width=72) (actual time=0.008..0.046 rows=121 loops=1)

57. 0.056 0.090 ↑ 1.5 153 1

Hash (cost=2.82..2.82 rows=222 width=16) (actual time=0.090..0.090 rows=153 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
58. 0.034 0.034 ↑ 1.5 153 1

Seq Scan on team_members tm_ma (cost=0.00..2.82 rows=222 width=16) (actual time=0.007..0.034 rows=153 loops=1)

59. 0.049 0.088 ↓ 1.0 121 1

Hash (cost=1.77..1.77 rows=117 width=72) (actual time=0.088..0.088 rows=121 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
60. 0.039 0.039 ↓ 1.0 121 1

Seq Scan on persons p_ma (cost=0.00..1.77 rows=117 width=72) (actual time=0.005..0.039 rows=121 loops=1)

61.          

CTE ltv_dti

62. 57.553 1,654.212 ↓ 35.8 42,999 1

Subquery Scan on a (cost=59,631.23..68,048.21 rows=1,202 width=28) (actual time=1,306.069..1,654.212 rows=42,999 loops=1)

  • Filter: (a.rn = 1)
  • Rows Removed by Filter: 199,995
63. 209.905 1,596.659 ↓ 1.0 242,994 1

WindowAgg (cost=59,631.23..65,042.15 rows=240,485 width=36) (actual time=1,306.053..1,596.659 rows=242,994 loops=1)

64. 402.476 1,386.754 ↓ 1.0 242,994 1

Sort (cost=59,631.23..60,232.45 rows=240,485 width=28) (actual time=1,306.039..1,386.754 rows=242,994 loops=1)

  • Sort Key: loan_tolerance_snapshot_items.loan_application_id, loan_tolerance_snapshot_items.tolerance_property_id, loan_tolerance_snapshot_items.last_updated_date DESC
  • Sort Method: external merge Disk: 9,440kB
65. 984.278 984.278 ↓ 1.0 242,994 1

Seq Scan on loan_tolerance_snapshot_items (cost=0.00..38,137.18 rows=240,485 width=28) (actual time=0.031..984.278 rows=242,994 loops=1)

  • Filter: (tolerance_property_id = ANY ('{4,17,18}'::integer[]))
  • Rows Removed by Filter: 2,052,139
66.          

CTE mi

67. 3.135 34.008 ↓ 184.4 10,513 1

Subquery Scan on a_1 (cost=931.42..1,475.87 rows=57 width=48) (actual time=10.981..34.008 rows=10,513 loops=1)

  • Filter: (a_1.rn = 1)
  • Rows Removed by Filter: 968
68. 16.444 30.873 ↓ 1.0 11,481 1

WindowAgg (cost=931.42..1,332.59 rows=11,462 width=56) (actual time=10.976..30.873 rows=11,481 loops=1)

69. 9.715 14.429 ↓ 1.0 11,481 1

Sort (cost=931.42..960.08 rows=11,462 width=20) (actual time=10.946..14.429 rows=11,481 loops=1)

  • Sort Key: mortgage_insurances.loan_application_id, mortgage_insurances.last_updated_date DESC
  • Sort Method: quicksort Memory: 1,086kB
70. 4.714 4.714 ↓ 1.0 11,481 1

Seq Scan on mortgage_insurances (cost=0.00..158.62 rows=11,462 width=20) (actual time=0.034..4.714 rows=11,481 loops=1)

71.          

CTE pro_detail

72. 16.137 58.311 ↑ 1.0 19,774 1

Merge Join (cost=0.57..985.10 rows=19,813 width=45) (actual time=0.050..58.311 rows=19,774 loops=1)

  • Merge Cond: (la_2.id = lt.loan_application_id)
73. 11.043 11.043 ↓ 1.0 22,012 1

Index Only Scan using loan_applications_pkey on loan_applications la_2 (cost=0.29..337.10 rows=21,994 width=8) (actual time=0.031..11.043 rows=22,012 loops=1)

  • Heap Fetches: 6,280
74. 31.131 31.131 ↑ 1.0 19,774 1

Index Scan using loan_terms_unique_per_loan on loan_terms lt (cost=0.29..345.35 rows=19,813 width=45) (actual time=0.014..31.131 rows=19,774 loops=1)

75.          

CTE stg

76. 5.819 2,288.713 ↑ 1.3 3,035 1

Hash Left Join (cost=5,430.15..6,100.65 rows=3,848 width=2,066) (actual time=2,265.445..2,288.713 rows=3,035 loops=1)

  • Hash Cond: (la_3.id = ex_addr.id)
77. 7.564 2,263.639 ↑ 1.3 3,035 1

Hash Right Join (cost=5,430.12..5,812.01 rows=3,848 width=1,978) (actual time=2,246.134..2,263.639 rows=3,035 loops=1)

  • Hash Cond: (lt_1.loan_application_id = la_3.id)
78. 10.218 10.218 ↑ 1.0 19,774 1

Seq Scan on loan_terms lt_1 (cost=0.00..272.93 rows=19,813 width=24) (actual time=0.026..10.218 rows=19,774 loops=1)

79. 4.669 2,245.857 ↑ 1.3 3,035 1

Hash (cost=5,382.02..5,382.02 rows=3,848 width=1,962) (actual time=2,245.856..2,245.857 rows=3,035 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 695kB
80. 2.876 2,241.188 ↑ 1.3 3,035 1

Hash Left Join (cost=3,549.22..5,382.02 rows=3,848 width=1,962) (actual time=2,130.881..2,241.188 rows=3,035 loops=1)

  • Hash Cond: (la_3.id = mi.loan_application_id)
81. 2.862 2,194.136 ↑ 1.3 3,035 1

Hash Left Join (cost=3,547.37..5,365.64 rows=3,848 width=1,930) (actual time=2,086.683..2,194.136 rows=3,035 loops=1)

  • Hash Cond: (la_3.id = bottom_dti.loan_application_id)
82. 2.990 2,174.608 ↑ 1.3 3,035 1

Hash Left Join (cost=3,520.25..5,324.08 rows=3,848 width=1,414) (actual time=2,069.978..2,174.608 rows=3,035 loops=1)

  • Hash Cond: (la_3.id = top_dti.loan_application_id)
83. 3.267 2,153.857 ↑ 1.3 3,035 1

Hash Left Join (cost=3,493.13..5,282.52 rows=3,848 width=898) (actual time=2,052.142..2,153.857 rows=3,035 loops=1)

  • Hash Cond: (la_3.id = ltv.loan_application_id)
84. 2.686 461.682 ↑ 1.3 3,035 1

Nested Loop Left Join (cost=3,466.01..5,240.96 rows=3,848 width=382) (actual time=363.194..461.682 rows=3,035 loops=1)

  • Join Filter: (NOT pri_1.is_removed)
85. 10.680 440.786 ↑ 1.3 3,035 1

Hash Right Join (cost=3,465.72..3,970.94 rows=3,848 width=396) (actual time=363.166..440.786 rows=3,035 loops=1)

  • Hash Cond: (pd.id = la_3.id)
86. 68.884 68.884 ↑ 1.0 19,774 1

CTE Scan on pro_detail pd (cost=0.00..396.26 rows=19,813 width=40) (actual time=0.054..68.884 rows=19,774 loops=1)

87. 3.030 361.222 ↑ 1.3 3,035 1

Hash (cost=3,417.62..3,417.62 rows=3,848 width=364) (actual time=361.222..361.222 rows=3,035 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 577kB
88. 6.099 358.192 ↑ 1.3 3,035 1

Hash Right Join (cost=3,015.39..3,417.62 rows=3,848 width=364) (actual time=342.241..358.192 rows=3,035 loops=1)

  • Hash Cond: (pri_1.loan_application_id = la_3.id)
89. 9.940 9.940 ↑ 1.0 19,362 1

Seq Scan on property_information pri_1 (cost=0.00..294.60 rows=19,571 width=23) (actual time=0.019..9.940 rows=19,362 loops=1)

  • Filter: (NOT is_removed)
  • Rows Removed by Filter: 9
90. 2.738 342.153 ↑ 1.3 3,035 1

Hash (cost=2,967.29..2,967.29 rows=3,848 width=341) (actual time=342.153..342.153 rows=3,035 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 494kB
91. 1.706 339.415 ↑ 1.3 3,035 1

Hash Left Join (cost=2,217.53..2,967.29 rows=3,848 width=341) (actual time=312.875..339.415 rows=3,035 loops=1)

  • Hash Cond: (la_3.id = ca.loan_application_id)
92. 1.560 335.010 ↑ 1.3 3,035 1

Hash Left Join (cost=2,084.58..2,810.23 rows=3,848 width=333) (actual time=310.149..335.010 rows=3,035 loops=1)

  • Hash Cond: (la_3.id = cl.loan_application_id)
93. 1.872 295.000 ↑ 1.3 3,035 1

Hash Join (cost=1,951.62..2,653.18 rows=3,848 width=325) (actual time=271.666..295.000 rows=3,035 loops=1)

  • Hash Cond: (la_3.loan_status_id = lo.id)
94. 9.013 292.875 ↓ 1.0 4,231 1

Hash Right Join (cost=1,949.33..2,596.97 rows=4,117 width=293) (actual time=271.334..292.875 rows=4,231 loops=1)

  • Hash Cond: (b.id = la_3.primary_borrower_id)
95. 12.894 12.894 ↓ 1.0 28,702 1

Seq Scan on borrowers b (cost=0.00..499.00 rows=28,660 width=21) (actual time=0.022..12.894 rows=28,702 loops=1)

96. 3.825 270.968 ↓ 1.0 4,231 1

Hash (cost=1,897.87..1,897.87 rows=4,117 width=288) (actual time=270.968..270.968 rows=4,231 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 563kB
97. 12.143 267.143 ↓ 1.0 4,231 1

Hash Right Join (cost=1,333.89..1,897.87 rows=4,117 width=288) (actual time=69.687..267.143 rows=4,231 loops=1)

  • Hash Cond: (t.loan_id = la_3.id)
98. 191.568 191.568 ↑ 1.0 22,012 1

CTE Scan on team t (cost=0.00..440.26 rows=22,013 width=232) (actual time=2.045..191.568 rows=22,012 loops=1)

99. 1.931 63.432 ↓ 1.0 4,231 1

Hash (cost=1,282.48..1,282.48 rows=4,113 width=64) (actual time=63.432..63.432 rows=4,231 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 389kB
100. 5.955 61.501 ↓ 1.0 4,231 1

Hash Right Join (cost=913.12..1,282.48 rows=4,113 width=64) (actual time=46.545..61.501 rows=4,231 loops=1)

  • Hash Cond: (w.loan_application_id = la_3.id)
101. 9.153 9.153 ↓ 1.0 19,711 1

Seq Scan on warehouses w (cost=0.00..258.77 rows=19,677 width=12) (actual time=0.021..9.153 rows=19,711 loops=1)

102. 1.993 46.393 ↓ 1.0 4,231 1

Hash (cost=861.71..861.71 rows=4,113 width=60) (actual time=46.393..46.393 rows=4,231 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 385kB
103. 6.305 44.400 ↓ 1.0 4,231 1

Hash Join (cost=402.00..861.71 rows=4,113 width=60) (actual time=23.448..44.400 rows=4,231 loops=1)

  • Hash Cond: (la_3.id = lad.loan_application_id)
104. 14.688 14.688 ↓ 1.0 21,574 1

Seq Scan on loan_applications la_3 (cost=0.00..337.74 rows=21,555 width=32) (actual time=0.012..14.688 rows=21,574 loops=1)

  • Filter: (NOT is_test)
  • Rows Removed by Filter: 438
105. 1.864 23.407 ↓ 1.0 4,246 1

Hash (cost=349.54..349.54 rows=4,197 width=36) (actual time=23.407..23.407 rows=4,246 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 295kB
106. 21.543 21.543 ↓ 1.0 4,246 1

Seq Scan on loan_application_dates lad (cost=0.00..349.54 rows=4,197 width=36) (actual time=0.163..21.543 rows=4,246 loops=1)

  • Filter: (closing_local_date >= ((date_trunc('month'::text, now()))::date - '3 mons'::interval))
  • Rows Removed by Filter: 9,282
107. 0.016 0.253 ↑ 1.2 35 1

Hash (cost=1.75..1.75 rows=43 width=40) (actual time=0.253..0.253 rows=35 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
108. 0.237 0.237 ↑ 1.2 35 1

Seq Scan on loan_status lo (cost=0.00..1.75 rows=43 width=40) (actual time=0.053..0.237 rows=35 loops=1)

  • Filter: ((name <> ALL ('{Draft,"Approved, Not Accepted","Initializing - Internal Only"}'::text[])) AND (lower(name) !~~ '%cancelled%'::text) AND (lower(name) !~~ '%denied%'::text) AND (lower(name) !~~ '%withdrawn%'::text) AND (lower(name) !~~ '%expired%'::text))
  • Rows Removed by Filter: 18
109. 0.985 38.450 ↓ 81.1 2,351 1

Hash (cost=132.59..132.59 rows=29 width=16) (actual time=38.450..38.450 rows=2,351 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 143kB
110. 37.465 37.465 ↓ 81.1 2,351 1

CTE Scan on transition_events cl (cost=0.00..132.59 rows=29 width=16) (actual time=28.190..37.465 rows=2,351 loops=1)

  • Filter: (to_status = 10)
  • Rows Removed by Filter: 3,227
111. 0.975 2.699 ↓ 111.3 3,227 1

Hash (cost=132.59..132.59 rows=29 width=16) (actual time=2.699..2.699 rows=3,227 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 184kB
112. 1.724 1.724 ↓ 111.3 3,227 1

CTE Scan on transition_events ca (cost=0.00..132.59 rows=29 width=16) (actual time=0.006..1.724 rows=3,227 loops=1)

  • Filter: (to_status = 7)
  • Rows Removed by Filter: 2,351
113. 18.210 18.210 ↑ 1.0 1 3,035

Index Scan using addresses_pkey on addresses addr_1 (cost=0.29..0.32 rows=1 width=19) (actual time=0.005..0.006 rows=1 loops=3,035)

  • Index Cond: (address_id = pri_1.address_id)
  • Filter: (loan_application_id = pri_1.loan_application_id)
114. 6.835 1,688.908 ↓ 2,388.8 14,333 1

Hash (cost=27.04..27.04 rows=6 width=524) (actual time=1,688.908..1,688.908 rows=14,333 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 799kB
115. 1,682.073 1,682.073 ↓ 2,388.8 14,333 1

CTE Scan on ltv_dti ltv (cost=0.00..27.04 rows=6 width=524) (actual time=1,306.077..1,682.073 rows=14,333 loops=1)

  • Filter: (tolerance_property_id = 4)
  • Rows Removed by Filter: 28,666
116. 5.142 17.761 ↓ 2,388.8 14,333 1

Hash (cost=27.04..27.04 rows=6 width=524) (actual time=17.761..17.761 rows=14,333 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 792kB
117. 12.619 12.619 ↓ 2,388.8 14,333 1

CTE Scan on ltv_dti top_dti (cost=0.00..27.04 rows=6 width=524) (actual time=0.007..12.619 rows=14,333 loops=1)

  • Filter: (tolerance_property_id = 17)
  • Rows Removed by Filter: 28,666
118. 4.945 16.666 ↓ 2,388.8 14,333 1

Hash (cost=27.04..27.04 rows=6 width=524) (actual time=16.666..16.666 rows=14,333 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 793kB
119. 11.721 11.721 ↓ 2,388.8 14,333 1

CTE Scan on ltv_dti bottom_dti (cost=0.00..27.04 rows=6 width=524) (actual time=0.008..11.721 rows=14,333 loops=1)

  • Filter: (tolerance_property_id = 18)
  • Rows Removed by Filter: 28,666
120. 4.172 44.176 ↓ 184.4 10,513 1

Hash (cost=1.14..1.14 rows=57 width=40) (actual time=44.176..44.176 rows=10,513 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 594kB
121. 40.004 40.004 ↓ 184.4 10,513 1

CTE Scan on mi (cost=0.00..1.14 rows=57 width=40) (actual time=10.984..40.004 rows=10,513 loops=1)

122. 0.047 19.255 ↓ 11.0 11 1

Hash (cost=0.02..0.02 rows=1 width=8) (actual time=19.255..19.255 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
123. 19.208 19.208 ↓ 11.0 11 1

CTE Scan on excl_addr ex_addr (cost=0.00..0.02 rows=1 width=8) (actual time=1.778..19.208 rows=11 loops=1)

Planning time : 19.914 ms
Execution time : 2,302.469 ms