explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gGkE

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

CTE Scan on stg (cost=50,825.37..50,911.95 rows=19 width=1,104) (actual time=1,338.809..1,345.673 rows=878 loops=1)

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

CTE transition_events

3. 8.692 34.817 ↑ 1.1 5,579 1

Finalize HashAggregate (cost=1,435.81..1,509.47 rows=5,893 width=20) (actual time=29.353..34.817 rows=5,579 loops=1)

  • Group Key: loan_status_transition_events.loan_application_id, loan_status_transition_events.to_status
4. 8.594 26.125 ↓ 1.5 5,877 1

Gather (cost=976.85..1,406.51 rows=3,906 width=20) (actual time=23.151..26.125 rows=5,877 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 2.935 17.531 ↑ 1.3 2,938 2 / 2

Partial HashAggregate (cost=966.85..1,005.91 rows=3,906 width=20) (actual time=16.640..17.531 rows=2,938 loops=2)

  • Group Key: loan_status_transition_events.loan_application_id, loan_status_transition_events.to_status
6. 14.596 14.596 ↑ 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.049..14.596 rows=3,376 loops=2)

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

CTE excl_addr

8. 0.020 23.598 ↓ 11.0 11 1

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

9. 0.476 23.424 ↓ 11.0 11 1

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

10. 0.814 13.399 ↓ 1.1 1,061 1

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

11. 7.280 7.280 ↓ 1.0 1,061 1

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

  • Filter: (loan_status_id = 23)
  • Rows Removed by Filter: 20,951
12. 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))
13. 9.549 9.549 ↓ 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.009..0.009 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.154 0.154 ↑ 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.014..0.014 rows=1 loops=11)

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

CTE team

16. 34.131 165.783 ↑ 1.0 22,012 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • Heap Fetches: 6,369
26. 30.745 30.745 ↑ 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.013..30.745 rows=22,004 loops=1)

27. 0.058 0.389 ↑ 1.5 153 1

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

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

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

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

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

30. 0.059 0.145 ↓ 1.0 121 1

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

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

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

32. 0.061 0.285 ↑ 1.5 153 1

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

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

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

  • Hash Cond: (tm_u.person_id = p_u.id)
34. 0.032 0.032 ↑ 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.032 rows=153 loops=1)

35. 0.051 0.092 ↓ 1.0 121 1

Hash (cost=1.77..1.77 rows=117 width=72) (actual time=0.092..0.092 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.006..0.041 rows=121 loops=1)

37. 0.061 0.290 ↑ 1.5 153 1

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

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

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

  • Hash Cond: (tm_pr.person_id = p_pr.id)
39. 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)

40. 0.046 0.090 ↓ 1.0 121 1

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

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

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

42. 0.084 0.309 ↑ 1.5 153 1

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

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

Hash Left Join (cost=3.23..8.19 rows=222 width=72) (actual time=0.142..0.225 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.007..0.036 rows=153 loops=1)

45. 0.067 0.110 ↓ 1.0 121 1

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

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

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

47. 0.099 0.323 ↑ 1.5 153 1

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

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

Hash Left Join (cost=3.23..8.19 rows=222 width=72) (actual time=0.118..0.224 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.049 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
51. 0.037 0.037 ↓ 1.0 121 1

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

52. 0.056 0.281 ↑ 1.5 153 1

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

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

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

  • Hash Cond: (tm_re.person_id = p_re.id)
54. 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)

55. 0.046 0.090 ↓ 1.0 121 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
56. 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)

57. 0.055 0.112 ↑ 1.5 153 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
58. 0.057 0.057 ↑ 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.057 rows=153 loops=1)

59. 0.056 0.094 ↓ 1.0 121 1

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

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

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

61.          

CTE ltv_dti

62. 17.744 726.338 ↓ 35.8 14,333 1

Subquery Scan on a (cost=33,308.85..36,105.98 rows=400 width=28) (actual time=611.997..726.338 rows=14,333 loops=1)

  • Filter: (a.rn = 1)
  • Rows Removed by Filter: 66,670
63. 66.257 708.594 ↓ 1.0 81,003 1

WindowAgg (cost=33,308.85..35,107.00 rows=79,918 width=36) (actual time=611.978..708.594 rows=81,003 loops=1)

64. 104.415 642.337 ↓ 1.0 81,003 1

Sort (cost=33,308.85..33,508.64 rows=79,918 width=28) (actual time=611.940..642.337 rows=81,003 loops=1)

  • Sort Key: loan_tolerance_snapshot_items.loan_application_id, loan_tolerance_snapshot_items.last_updated_date DESC
  • Sort Method: quicksort Memory: 9,401kB
65. 22.453 537.922 ↓ 1.0 81,003 1

Gather (cost=10.00..26,801.03 rows=79,918 width=28) (actual time=3.140..537.922 rows=81,003 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
66. 515.469 515.469 ↑ 1.2 27,001 3 / 3

Parallel Seq Scan on loan_tolerance_snapshot_items (cost=0.00..18,799.23 rows=33,299 width=28) (actual time=0.105..515.469 rows=27,001 loops=3)

  • Filter: (tolerance_property_id = 4)
  • Rows Removed by Filter: 738,090
67.          

CTE mi

68. 3.156 35.131 ↓ 184.5 10,515 1

Subquery Scan on a_1 (cost=931.42..1,475.87 rows=57 width=48) (actual time=11.928..35.131 rows=10,515 loops=1)

  • Filter: (a_1.rn = 1)
  • Rows Removed by Filter: 968
69. 16.396 31.975 ↓ 1.0 11,483 1

WindowAgg (cost=931.42..1,332.59 rows=11,462 width=56) (actual time=11.915..31.975 rows=11,483 loops=1)

70. 10.476 15.579 ↓ 1.0 11,483 1

Sort (cost=931.42..960.08 rows=11,462 width=20) (actual time=11.849..15.579 rows=11,483 loops=1)

  • Sort Key: mortgage_insurances.loan_application_id, mortgage_insurances.last_updated_date DESC
  • Sort Method: quicksort Memory: 1,086kB
71. 5.103 5.103 ↓ 1.0 11,483 1

Seq Scan on mortgage_insurances (cost=0.00..158.62 rows=11,462 width=20) (actual time=0.056..5.103 rows=11,483 loops=1)

72.          

CTE pro_detail

73. 16.748 60.691 ↑ 1.0 19,774 1

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

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

  • Heap Fetches: 6,369
75. 32.220 32.220 ↑ 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.016..32.220 rows=19,774 loops=1)

76.          

CTE stg

77. 3.551 1,343.287 ↑ 1.3 3,036 1

Unique (cost=6,228.58..6,517.18 rows=3,848 width=1,034) (actual time=1,338.740..1,343.287 rows=3,036 loops=1)

78. 12.772 1,339.736 ↑ 1.3 3,036 1

Sort (cost=6,228.58..6,238.20 rows=3,848 width=1,034) (actual time=1,338.737..1,339.736 rows=3,036 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, 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,086kB
79. 8.669 1,326.964 ↑ 1.3 3,036 1

Hash Left Join (cost=3,906.91..5,999.43 rows=3,848 width=1,034) (actual time=1,207.855..1,326.964 rows=3,036 loops=1)

  • Hash Cond: (la_3.id = ex_addr.id)
80. 2.571 1,294.656 ↑ 1.3 3,036 1

Hash Left Join (cost=3,906.88..5,710.79 rows=3,848 width=946) (actual time=1,184.124..1,294.656 rows=3,036 loops=1)

  • Hash Cond: (la_3.id = mi.loan_application_id)
81. 3.316 1,246.324 ↑ 1.3 3,036 1

Hash Left Join (cost=3,905.02..5,694.41 rows=3,848 width=914) (actual time=1,138.316..1,246.324 rows=3,036 loops=1)

  • Hash Cond: (la_3.id = ltv.loan_application_id)
82. 2.528 499.622 ↑ 1.3 3,036 1

Nested Loop Left Join (cost=3,896.00..5,670.94 rows=3,848 width=398) (actual time=394.877..499.622 rows=3,036 loops=1)

  • Join Filter: (NOT pri_1.is_removed)
83. 11.541 475.842 ↑ 1.3 3,036 1

Hash Right Join (cost=3,895.71..4,400.92 rows=3,848 width=412) (actual time=394.827..475.842 rows=3,036 loops=1)

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

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

85. 3.526 392.792 ↑ 1.3 3,036 1

Hash (cost=3,847.61..3,847.61 rows=3,848 width=380) (actual time=392.792..392.792 rows=3,036 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 633kB
86. 7.032 389.266 ↑ 1.3 3,036 1

Hash Right Join (cost=3,445.37..3,847.61 rows=3,848 width=380) (actual time=370.552..389.266 rows=3,036 loops=1)

  • Hash Cond: (pri_1.loan_application_id = la_3.id)
87. 11.813 11.813 ↑ 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.027..11.813 rows=19,362 loops=1)

  • Filter: (NOT is_removed)
  • Rows Removed by Filter: 9
88. 3.171 370.421 ↑ 1.3 3,036 1

Hash (cost=3,397.27..3,397.27 rows=3,848 width=357) (actual time=370.421..370.421 rows=3,036 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 550kB
89. 7.008 367.250 ↑ 1.3 3,036 1

Hash Right Join (cost=3,015.39..3,397.27 rows=3,848 width=357) (actual time=348.482..367.250 rows=3,036 loops=1)

  • Hash Cond: (lt_1.loan_application_id = la_3.id)
90. 12.027 12.027 ↑ 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.024..12.027 rows=19,774 loops=1)

91. 3.279 348.215 ↑ 1.3 3,036 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 494kB
92. 1.912 344.936 ↑ 1.3 3,036 1

Hash Left Join (cost=2,217.53..2,967.29 rows=3,848 width=341) (actual time=313.118..344.936 rows=3,036 loops=1)

  • Hash Cond: (la_3.id = ca.loan_application_id)
93. 1.753 340.350 ↑ 1.3 3,036 1

Hash Left Join (cost=2,084.58..2,810.23 rows=3,848 width=333) (actual time=310.417..340.350 rows=3,036 loops=1)

  • Hash Cond: (la_3.id = cl.loan_application_id)
94. 1.981 299.048 ↑ 1.3 3,036 1

Hash Join (cost=1,951.62..2,653.18 rows=3,848 width=325) (actual time=270.852..299.048 rows=3,036 loops=1)

  • Hash Cond: (la_3.loan_status_id = lo.id)
95. 9.989 296.637 ↓ 1.0 4,232 1

Hash Right Join (cost=1,949.33..2,596.97 rows=4,117 width=293) (actual time=270.325..296.637 rows=4,232 loops=1)

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

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

97. 3.744 269.856 ↓ 1.0 4,232 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 563kB
98. 10.932 266.112 ↓ 1.0 4,232 1

Hash Right Join (cost=1,333.89..1,897.87 rows=4,117 width=288) (actual time=77.383..266.112 rows=4,232 loops=1)

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

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

100. 2.156 70.345 ↓ 1.0 4,232 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 389kB
101. 6.513 68.189 ↓ 1.0 4,232 1

Hash Right Join (cost=913.12..1,282.48 rows=4,113 width=64) (actual time=51.121..68.189 rows=4,232 loops=1)

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

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

103. 2.238 50.920 ↓ 1.0 4,232 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 385kB
104. 6.423 48.682 ↓ 1.0 4,232 1

Hash Join (cost=402.00..861.71 rows=4,113 width=60) (actual time=24.837..48.682 rows=4,232 loops=1)

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

  • Filter: (NOT is_test)
  • Rows Removed by Filter: 438
106. 2.081 24.750 ↓ 1.0 4,247 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 295kB
107. 22.669 22.669 ↓ 1.0 4,247 1

Seq Scan on loan_application_dates lad (cost=0.00..349.54 rows=4,197 width=36) (actual time=0.188..22.669 rows=4,247 loops=1)

  • Filter: (closing_local_date >= ((date_trunc('month'::text, now()))::date - '3 mons'::interval))
  • Rows Removed by Filter: 9,281
108. 0.031 0.430 ↑ 1.2 35 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
109. 0.399 0.399 ↑ 1.2 35 1

Seq Scan on loan_status lo (cost=0.00..1.75 rows=43 width=40) (actual time=0.199..0.399 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
110. 0.909 39.549 ↓ 81.1 2,352 1

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

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 143kB
111. 38.640 38.640 ↓ 81.1 2,352 1

CTE Scan on transition_events cl (cost=0.00..132.59 rows=29 width=16) (actual time=29.374..38.640 rows=2,352 loops=1)

  • Filter: (to_status = 10)
  • Rows Removed by Filter: 3,227
112. 0.994 2.674 ↓ 111.3 3,227 1

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

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

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

  • Filter: (to_status = 7)
  • Rows Removed by Filter: 2,352
114. 21.252 21.252 ↑ 1.0 1 3,036

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

  • Index Cond: (address_id = pri_1.address_id)
  • Filter: (loan_application_id = pri_1.loan_application_id)
115. 6.379 743.386 ↓ 7,166.5 14,333 1

Hash (cost=9.00..9.00 rows=2 width=524) (actual time=743.386..743.386 rows=14,333 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 799kB
116. 737.007 737.007 ↓ 7,166.5 14,333 1

CTE Scan on ltv_dti ltv (cost=0.00..9.00 rows=2 width=524) (actual time=612.007..737.007 rows=14,333 loops=1)

  • Filter: (tolerance_property_id = 4)
117. 4.359 45.761 ↓ 184.5 10,515 1

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

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 594kB
118. 41.402 41.402 ↓ 184.5 10,515 1

CTE Scan on mi (cost=0.00..1.14 rows=57 width=40) (actual time=11.935..41.402 rows=10,515 loops=1)

119. 0.017 23.639 ↓ 11.0 11 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
120. 23.622 23.622 ↓ 11.0 11 1

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

Planning time : 32.470 ms
Execution time : 1,352.724 ms