explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4tnC

Settings
# exclusive inclusive rows x rows loops node
1. 9,726.075 9,726.075 ↓ 46.2 878 1

CTE Scan on stg (cost=370,358.81..370,445.39 rows=19 width=2,136) (actual time=9,718.356..9,726.075 rows=878 loops=1)

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

CTE transition_events

3. 208.288 237.093 ↓ 4.2 91,075 1

HashAggregate (cost=1,938.83..2,210.65 rows=21,746 width=20) (actual time=119.641..237.093 rows=91,075 loops=1)

  • Group Key: loan_status_transition_events.loan_application_id, loan_status_transition_events.to_status
4. 28.805 28.805 ↓ 1.0 100,006 1

Seq Scan on loan_status_transition_events (cost=0.00..1,198.76 rows=98,676 width=20) (actual time=0.029..28.805 rows=100,006 loops=1)

5.          

CTE excl_addr

6. 0.022 24.306 ↓ 11.0 11 1

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

7. 0.882 24.064 ↓ 11.0 11 1

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

8. 0.964 14.694 ↓ 1.1 1,061 1

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

9. 8.425 8.425 ↓ 1.0 1,061 1

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

  • Filter: (loan_status_id = 23)
  • Rows Removed by Filter: 20,951
10. 5.305 5.305 ↑ 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.005..0.005 rows=1 loops=1,061)

  • Index Cond: (loan_application_id = la.id)
  • Filter: ((NOT is_removed) AND (NOT is_removed))
11. 8.488 8.488 ↓ 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.008..0.008 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
12. 0.220 0.220 ↑ 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.019..0.020 rows=1 loops=11)

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

CTE team

14. 35.866 180.692 ↑ 1.0 22,012 1

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

  • Hash Cond: (tm_ma.person_id = p_ma.id)
15. 10.897 144.717 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.customer_care_member_id = tm_ma.id)
16. 12.344 133.713 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.received_member_id = tm_re.id)
17. 8.587 121.080 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.closer_member_id = tm_cl.id)
18. 7.815 112.194 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.secondary_member_id = tm_s.id)
19. 8.430 104.079 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.processor_member_id = tm_pr.id)
20. 8.593 95.362 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.underwriter_member_id = tm_u.id)
21. 9.320 86.482 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.quality_review_id = tm_q.id)
22. 26.372 76.784 ↑ 1.0 22,012 1

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

  • Merge Cond: (la_1.id = lat.loan_application_id)
23. 13.631 13.631 ↓ 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.092..13.631 rows=22,012 loops=1)

  • Heap Fetches: 6,239
24. 36.781 36.781 ↑ 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.019..36.781 rows=22,004 loops=1)

25. 0.063 0.378 ↑ 1.5 153 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
26. 0.112 0.315 ↑ 1.5 153 1

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

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

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

28. 0.052 0.137 ↓ 1.0 121 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
29. 0.085 0.085 ↓ 1.0 121 1

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

30. 0.066 0.287 ↑ 1.5 153 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
31. 0.108 0.221 ↑ 1.5 153 1

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

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

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

33. 0.046 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
34. 0.042 0.042 ↓ 1.0 121 1

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

35. 0.054 0.287 ↑ 1.5 153 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
36. 0.111 0.233 ↑ 1.5 153 1

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

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

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

38. 0.053 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
39. 0.036 0.036 ↓ 1.0 121 1

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

40. 0.061 0.300 ↑ 1.5 153 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
41. 0.100 0.239 ↑ 1.5 153 1

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

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

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

43. 0.048 0.107 ↓ 1.0 121 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
44. 0.059 0.059 ↓ 1.0 121 1

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

45. 0.060 0.299 ↑ 1.5 153 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
46. 0.103 0.239 ↑ 1.5 153 1

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

  • Hash Cond: (tm_cl.person_id = p_cl.id)
47. 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.007..0.039 rows=153 loops=1)

48. 0.054 0.097 ↓ 1.0 121 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
49. 0.043 0.043 ↓ 1.0 121 1

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

50. 0.052 0.289 ↑ 1.5 153 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
51. 0.110 0.237 ↑ 1.5 153 1

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

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

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

53. 0.049 0.093 ↓ 1.0 121 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
54. 0.044 0.044 ↓ 1.0 121 1

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

55. 0.057 0.107 ↑ 1.5 153 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
56. 0.050 0.050 ↑ 1.5 153 1

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

57. 0.065 0.109 ↓ 1.0 121 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
58. 0.044 0.044 ↓ 1.0 121 1

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

59.          

CTE ltv_dti

60. 568.298 8,169.427 ↓ 37.5 424,423 1

Subquery Scan on a (cost=274,029.43..353,267.86 rows=11,320 width=28) (actual time=3,952.198..8,169.427 rows=424,423 loops=1)

  • Filter: (a.rn = 1)
  • Rows Removed by Filter: 1,870,654
61. 2,023.184 7,601.129 ↓ 1.0 2,295,077 1

WindowAgg (cost=274,029.43..324,968.42 rows=2,263,955 width=36) (actual time=3,952.157..7,601.129 rows=2,295,077 loops=1)

62. 4,430.060 5,577.945 ↓ 1.0 2,295,077 1

Sort (cost=274,029.43..279,689.32 rows=2,263,955 width=28) (actual time=3,952.111..5,577.945 rows=2,295,077 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: 91,240kB
63. 1,147.885 1,147.885 ↓ 1.0 2,295,077 1

Seq Scan on loan_tolerance_snapshot_items (cost=0.00..29,647.35 rows=2,263,955 width=28) (actual time=0.030..1,147.885 rows=2,295,077 loops=1)

64.          

CTE mi

65. 3.445 35.958 ↓ 184.4 10,513 1

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

  • Filter: (a_1.rn = 1)
  • Rows Removed by Filter: 968
66. 16.938 32.513 ↓ 1.0 11,481 1

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

67. 10.159 15.575 ↓ 1.0 11,481 1

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

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

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

69.          

CTE pro_detail

70. 18.180 68.942 ↑ 1.0 19,773 1

Merge Join (cost=0.57..985.10 rows=19,813 width=45) (actual time=0.066..68.942 rows=19,773 loops=1)

  • Merge Cond: (la_2.id = lt.loan_application_id)
71. 12.695 12.695 ↓ 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.041..12.695 rows=22,012 loops=1)

  • Heap Fetches: 6,239
72. 38.067 38.067 ↑ 1.0 19,773 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.018..38.067 rows=19,773 loops=1)

73.          

CTE stg

74. 3.754 9,723.448 ↑ 1.3 3,035 1

Unique (cost=7,879.72..8,187.56 rows=3,848 width=2,066) (actual time=9,718.261..9,723.448 rows=3,035 loops=1)

75. 11.846 9,719.694 ↑ 1.3 3,035 1

Sort (cost=7,879.72..7,889.34 rows=3,848 width=2,066) (actual time=9,718.258..9,719.694 rows=3,035 loops=1)

  • Sort Key: addr_1.state_code, la_3.id, b.last_name, (((b.last_name || ', '::text) || b.first_name)), lad.submitted_1003_local_date, ((LEAST((lad.preapproval_local_date)::timestamp without time zone, (lad.fast_preapproval_local_date)::timestamp without time zone, ca.created_instant))::date), (CASE WHEN (ex_addr.id IS NULL) THEN lad.application_received_local_date ELSE NULL::date END), (CASE WHEN (lad.rate_lock_cancelled_local_date IS NULL) THEN lad.rate_lock_local_date ELSE NULL::date END), lad.closing_local_date, w.funding_local_date, (CASE WHEN (cl.created_instant IS NOT NULL) THEN 1 ELSE 0 END), (CASE WHEN ((lad.closing_local_date IS NOT NULL) AND (lad.application_received_local_date IS NOT NULL) AND (lo.id <> ALL ('{2,31,41,13,33,12,30,23,1,999,42,14,21,22,19,18,20,5,43,3,27,29,28}'::bigint[]))) THEN 1 ELSE 0 END), ltv.value_on_creation, top_dti.value_on_creation, bottom_dti.value_on_creation, lt_1.amount, lt_1.interest_rate, la_3.deal_id, lo.name, t.processor, t.underwriter, t.quality_reviewer, t.received, t.closer, t.secondary, t.mortgage_adviser, pd.uuid, (CASE WHEN (pri_1.project_type = 2) THEN 'Condo'::text WHEN (pri_1.num_units > 1) THEN 'Multi-Fam'::text WHEN (pri_1.num_units = 1) THEN 'SFR'::text ELSE 'Other'::text END), (CASE lt_1.product_type_id WHEN 1 THEN '30 Yr Fixed'::text WHEN 2 THEN '15 Yr Fixed'::text WHEN 3 THEN '5/1 ARM'::text WHEN 4 THEN '7/1 ARM'::text WHEN 5 THEN '10/1 ARM'::text WHEN 6 THEN '20 Yr Fixed'::text WHEN 7 THEN '25 Yr Fixed'::text WHEN 8 THEN '10 Yr Fixed'::text WHEN 9 THEN '3/1 ARM'::text ELSE NULL::text END), mi.mi_company, (CASE WHEN (la_3.deal_id IS NOT NULL) THEN 'Y'::text ELSE 'N'::text END)
  • Sort Method: quicksort Memory: 1,114kB
76. 5.857 9,707.848 ↑ 1.3 3,035 1

Hash Left Join (cost=6,980.07..7,650.57 rows=3,848 width=2,066) (actual time=9,684.328..9,707.848 rows=3,035 loops=1)

  • Hash Cond: (la_3.id = ex_addr.id)
77. 7.749 9,677.651 ↑ 1.3 3,035 1

Hash Right Join (cost=6,980.04..7,361.93 rows=3,848 width=1,978) (actual time=9,659.880..9,677.651 rows=3,035 loops=1)

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

Seq Scan on loan_terms lt_1 (cost=0.00..272.93 rows=19,813 width=24) (actual time=0.025..10.282 rows=19,773 loops=1)

79. 5.061 9,659.620 ↑ 1.3 3,035 1

Hash (cost=6,931.94..6,931.94 rows=3,848 width=1,962) (actual time=9,659.620..9,659.620 rows=3,035 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 695kB
80. 3.130 9,654.559 ↑ 1.3 3,035 1

Hash Left Join (cost=5,026.72..6,931.94 rows=3,848 width=1,962) (actual time=9,524.715..9,654.559 rows=3,035 loops=1)

  • Hash Cond: (la_3.id = mi.loan_application_id)
81. 3.284 9,604.642 ↑ 1.3 3,035 1

Hash Left Join (cost=5,024.87..6,915.56 rows=3,848 width=1,930) (actual time=9,477.900..9,604.642 rows=3,035 loops=1)

  • Hash Cond: (la_3.id = bottom_dti.loan_application_id)
82. 3.281 9,466.182 ↑ 1.3 3,035 1

Hash Left Join (cost=4,769.46..6,621.57 rows=3,848 width=1,414) (actual time=9,342.658..9,466.182 rows=3,035 loops=1)

  • Hash Cond: (la_3.id = top_dti.loan_application_id)
83. 3.668 9,342.878 ↑ 1.3 3,035 1

Hash Left Join (cost=4,514.05..6,327.57 rows=3,848 width=898) (actual time=9,222.539..9,342.878 rows=3,035 loops=1)

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

Nested Loop Left Join (cost=4,258.63..6,033.58 rows=3,848 width=382) (actual time=676.280..793.021 rows=3,035 loops=1)

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

Hash Right Join (cost=4,258.34..4,763.56 rows=3,848 width=396) (actual time=676.226..766.995 rows=3,035 loops=1)

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

CTE Scan on pro_detail pd (cost=0.00..396.26 rows=19,813 width=40) (actual time=0.071..80.417 rows=19,773 loops=1)

87. 3.078 673.822 ↑ 1.3 3,035 1

Hash (cost=4,210.24..4,210.24 rows=3,848 width=364) (actual time=673.822..673.822 rows=3,035 loops=1)

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

Hash Right Join (cost=3,808.01..4,210.24 rows=3,848 width=364) (actual time=651.762..670.744 rows=3,035 loops=1)

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

Seq Scan on property_information pri_1 (cost=0.00..294.60 rows=19,571 width=23) (actual time=0.037..12.565 rows=19,361 loops=1)

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

Hash (cost=3,759.91..3,759.91 rows=3,848 width=341) (actual time=651.618..651.618 rows=3,035 loops=1)

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

Hash Left Join (cost=2,932.92..3,759.91 rows=3,848 width=341) (actual time=616.896..648.743 rows=3,035 loops=1)

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

Hash Left Join (cost=2,442.27..3,206.54 rows=3,848 width=333) (actual time=595.882..625.994 rows=3,035 loops=1)

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

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

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

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

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

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

96. 4.187 297.217 ↓ 1.0 4,231 1

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

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

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

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

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

99. 2.327 73.266 ↓ 1.0 4,231 1

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

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

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

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

Seq Scan on warehouses w (cost=0.00..258.77 rows=19,677 width=12) (actual time=0.024..11.154 rows=19,710 loops=1)

102. 2.362 53.403 ↓ 1.0 4,231 1

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

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

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

  • Hash Cond: (la_3.id = lad.loan_application_id)
104. 18.844 18.844 ↓ 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.026..18.844 rows=21,574 loops=1)

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

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 295kB
106. 23.316 23.316 ↓ 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.194..23.316 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.030 0.455 ↑ 1.2 35 1

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

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

Seq Scan on loan_status lo (cost=0.00..1.75 rows=43 width=40) (actual time=0.232..0.425 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. 1.595 297.536 ↓ 21.6 2,350 1

Hash (cost=489.28..489.28 rows=109 width=16) (actual time=297.535..297.536 rows=2,350 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 143kB
110. 295.941 295.941 ↓ 21.6 2,350 1

CTE Scan on transition_events cl (cost=0.00..489.28 rows=109 width=16) (actual time=119.718..295.941 rows=2,350 loops=1)

  • Filter: (to_status = 10)
  • Rows Removed by Filter: 88,725
111. 1.053 20.985 ↓ 29.6 3,227 1

Hash (cost=489.28..489.28 rows=109 width=16) (actual time=20.985..20.985 rows=3,227 loops=1)

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

CTE Scan on transition_events ca (cost=0.00..489.28 rows=109 width=16) (actual time=0.016..19.932 rows=3,227 loops=1)

  • Filter: (to_status = 7)
  • Rows Removed by Filter: 87,848
113. 21.245 21.245 ↑ 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.007..0.007 rows=1 loops=3,035)

  • Index Cond: (address_id = pri_1.address_id)
  • Filter: (loan_application_id = pri_1.loan_application_id)
114. 13.727 8,546.189 ↓ 251.5 14,333 1

Hash (cost=254.70..254.70 rows=57 width=524) (actual time=8,546.189..8,546.189 rows=14,333 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 799kB
115. 8,532.462 8,532.462 ↓ 251.5 14,333 1

CTE Scan on ltv_dti ltv (cost=0.00..254.70 rows=57 width=524) (actual time=3,952.239..8,532.462 rows=14,333 loops=1)

  • Filter: (tolerance_property_id = 4)
  • Rows Removed by Filter: 410,090
116. 7.078 120.023 ↓ 251.5 14,333 1

Hash (cost=254.70..254.70 rows=57 width=524) (actual time=120.023..120.023 rows=14,333 loops=1)

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

CTE Scan on ltv_dti top_dti (cost=0.00..254.70 rows=57 width=524) (actual time=0.074..112.945 rows=14,333 loops=1)

  • Filter: (tolerance_property_id = 17)
  • Rows Removed by Filter: 410,090
118. 14.420 135.176 ↓ 251.5 14,333 1

Hash (cost=254.70..254.70 rows=57 width=524) (actual time=135.176..135.176 rows=14,333 loops=1)

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

CTE Scan on ltv_dti bottom_dti (cost=0.00..254.70 rows=57 width=524) (actual time=0.033..120.756 rows=14,333 loops=1)

  • Filter: (tolerance_property_id = 18)
  • Rows Removed by Filter: 410,090
120. 4.500 46.787 ↓ 184.4 10,513 1

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

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

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

122. 0.014 24.340 ↓ 11.0 11 1

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

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

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

Planning time : 33.966 ms
Execution time : 9,769.838 ms