explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PKuW

Settings
# exclusive inclusive rows x rows loops node
1. 40.353 3,813.719 ↑ 3,950,738.1 11,580 1

Unique (cost=59,731,523,651.90..75,858,238,891.14 rows=45,749,546,778 width=1,599) (actual time=3,770.987..3,813.719 rows=11,580 loops=1)

2.          

CTE max_transition_events

3. 75.733 276.174 ↓ 4.0 84,716 1

Sort (cost=3,653.20..3,705.86 rows=21,063 width=20) (actual time=245.457..276.174 rows=84,716 loops=1)

  • Sort Key: loan_status_transition_events.loan_application_id
  • Sort Method: quicksort Memory: 9691kB
4. 181.635 200.441 ↓ 4.0 84,716 1

HashAggregate (cost=1,877.34..2,140.62 rows=21,063 width=20) (actual time=85.853..200.441 rows=84,716 loops=1)

  • Group Key: loan_status_transition_events.loan_application_id, loan_status_transition_events.to_status
5. 18.806 18.806 ↑ 1.0 92,742 1

Seq Scan on loan_status_transition_events (cost=0.00..1,162.85 rows=95,265 width=20) (actual time=0.057..18.806 rows=92,742 loops=1)

6.          

CTE min_transition_events

7. 69.157 279.256 ↓ 4.0 84,716 1

Sort (cost=3,653.20..3,705.86 rows=21,063 width=20) (actual time=249.302..279.256 rows=84,716 loops=1)

  • Sort Key: loan_status_transition_events_1.loan_application_id
  • Sort Method: quicksort Memory: 9691kB
8. 190.102 210.099 ↓ 4.0 84,716 1

HashAggregate (cost=1,877.34..2,140.62 rows=21,063 width=20) (actual time=89.671..210.099 rows=84,716 loops=1)

  • Group Key: loan_status_transition_events_1.loan_application_id, loan_status_transition_events_1.to_status
9. 19.997 19.997 ↑ 1.0 92,742 1

Seq Scan on loan_status_transition_events loan_status_transition_events_1 (cost=0.00..1,162.85 rows=95,265 width=20) (actual time=0.034..19.997 rows=92,742 loops=1)

10.          

CTE misc_section

11. 48.036 89.427 ↓ 1.0 48,843 1

Sort (cost=5,083.59..5,205.60 rows=48,804 width=76) (actual time=72.316..89.427 rows=48,843 loops=1)

  • Sort Key: fv.loan_application_id
  • Sort Method: quicksort Memory: 8405kB
12. 25.161 41.391 ↓ 1.0 48,843 1

Hash Join (cost=3.73..1,283.05 rows=48,804 width=76) (actual time=0.274..41.391 rows=48,843 loops=1)

  • Hash Cond: (fv.loan_extra_dates_field_definition_id = sf.loan_extra_dates_field_definition_id)
13. 16.010 16.010 ↓ 1.0 48,843 1

Seq Scan on loan_extra_dates_field_values fv (cost=0.00..608.26 rows=48,804 width=20) (actual time=0.033..16.010 rows=48,843 loops=1)

  • Filter: (NOT is_removed)
  • Rows Removed by Filter: 70
14. 0.031 0.220 ↓ 1.1 51 1

Hash (cost=3.15..3.15 rows=47 width=80) (actual time=0.220..0.220 rows=51 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
15. 0.052 0.189 ↓ 1.1 51 1

Hash Join (cost=1.59..3.15 rows=47 width=80) (actual time=0.137..0.189 rows=51 loops=1)

  • Hash Cond: (sf.loan_extra_dates_field_definition_id = fd.id)
16. 0.050 0.081 ↓ 1.1 51 1

Hash Join (cost=0.34..1.24 rows=47 width=40) (actual time=0.051..0.081 rows=51 loops=1)

  • Hash Cond: (sf.loan_extra_dates_section_id = s.id)
17. 0.016 0.016 ↓ 1.1 51 1

Seq Scan on loan_extra_dates_section_fields sf (cost=0.00..0.67 rows=47 width=16) (actual time=0.009..0.016 rows=51 loops=1)

18. 0.007 0.015 ↑ 1.0 6 1

Hash (cost=0.26..0.26 rows=6 width=40) (actual time=0.015..0.015 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.008 0.008 ↑ 1.0 6 1

Seq Scan on loan_extra_dates_sections s (cost=0.00..0.26 rows=6 width=40) (actual time=0.006..0.008 rows=6 loops=1)

20. 0.034 0.056 ↓ 1.1 51 1

Hash (cost=0.67..0.67 rows=47 width=40) (actual time=0.056..0.056 rows=51 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
21. 0.022 0.022 ↓ 1.1 51 1

Seq Scan on loan_extra_dates_field_definitions fd (cost=0.00..0.67 rows=47 width=40) (actual time=0.009..0.022 rows=51 loops=1)

22.          

CTE reo_info

23. 6.356 9.411 ↓ 1.1 5,023 1

HashAggregate (cost=137.80..185.38 rows=4,758 width=16) (actual time=7.105..9.411 rows=5,023 loops=1)

  • Group Key: owned_properties.loan_application_id
24. 3.055 3.055 ↑ 1.0 6,473 1

Seq Scan on owned_properties (cost=0.00..105.32 rows=6,496 width=8) (actual time=0.020..3.055 rows=6,473 loops=1)

  • Filter: (NOT is_removed)
  • Rows Removed by Filter: 1482
25.          

CTE form_free

26. 0.047 0.126 ↑ 11.8 17 1

Unique (cost=0.15..5.74 rows=200 width=40) (actual time=0.026..0.126 rows=17 loops=1)

27. 0.079 0.079 ↑ 1.0 308 1

Index Only Scan using loan_form_free_callbacks_loan_application_idx on loan_form_free_callbacks (cost=0.15..4.97 rows=308 width=40) (actual time=0.025..0.079 rows=308 loops=1)

  • Heap Fetches: 0
28.          

CTE quality_bypass_info

29. 33.020 156.384 ↑ 1.4 2,376 1

HashAggregate (cost=4,769.76..4,820.54 rows=3,385 width=232) (actual time=153.006..156.384 rows=2,376 loops=1)

  • Group Key: l_1.id, ((b_1.last_name || ', '::text) || b_1.first_name), ls.id
30. 5.310 123.364 ↓ 1.6 5,350 1

Hash Left Join (cost=4,066.09..4,295.86 rows=3,385 width=122) (actual time=106.927..123.364 rows=5,350 loops=1)

  • Hash Cond: (l_1.id = qs.loan_application_id)
  • Filter: ((NOT qs.is_removed) OR (qs.is_removed IS NULL))
31. 2.248 115.664 ↓ 1.0 3,441 1

Hash Left Join (cost=3,979.34..4,169.64 rows=3,385 width=90) (actual time=104.487..115.664 rows=3,441 loops=1)

  • Hash Cond: (l_1.id = qr.loan_application_id)
32. 1.317 109.602 ↑ 1.4 2,376 1

Hash Join (cost=3,868.66..4,024.91 rows=3,385 width=37) (actual time=100.627..109.602 rows=2,376 loops=1)

  • Hash Cond: (latm.customer_care_member_id = tm1.id)
33. 2.827 108.017 ↑ 1.4 2,376 1

Hash Right Join (cost=3,859.98..3,980.80 rows=3,385 width=45) (actual time=100.340..108.017 rows=2,376 loops=1)

  • Hash Cond: (reo.loan_application_id = l_1.id)
34. 11.990 11.990 ↓ 1.1 5,023 1

CTE Scan on reo_info reo (cost=0.00..95.16 rows=4,758 width=16) (actual time=7.109..11.990 rows=5,023 loops=1)

35. 1.628 93.200 ↑ 1.4 2,376 1

Hash (cost=3,817.66..3,817.66 rows=3,385 width=37) (actual time=93.199..93.200 rows=2,376 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 204kB
36. 2.089 91.572 ↑ 1.4 2,376 1

Nested Loop (cost=2,120.64..3,817.66 rows=3,385 width=37) (actual time=61.985..91.572 rows=2,376 loops=1)

37. 6.755 79.979 ↑ 1.4 2,376 1

Hash Join (cost=2,120.35..2,717.31 rows=3,385 width=45) (actual time=61.955..79.979 rows=2,376 loops=1)

  • Hash Cond: (b_1.id = l_1.primary_borrower_id)
38. 11.315 11.315 ↓ 1.0 26,780 1

Seq Scan on borrowers b_1 (cost=0.00..463.38 rows=26,598 width=21) (actual time=0.013..11.315 rows=26,780 loops=1)

39. 1.094 61.909 ↑ 1.4 2,376 1

Hash (cost=2,078.03..2,078.03 rows=3,385 width=40) (actual time=61.909..61.909 rows=2,376 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 200kB
40. 6.221 60.815 ↑ 1.4 2,376 1

Hash Join (cost=1,700.49..2,078.03 rows=3,385 width=40) (actual time=49.692..60.815 rows=2,376 loops=1)

  • Hash Cond: (latm.loan_application_id = lad_1.loan_application_id)
41. 4.965 4.965 ↓ 1.0 20,596 1

Seq Scan on loan_application_team_members latm (cost=0.00..267.31 rows=20,191 width=32) (actual time=0.013..4.965 rows=20,596 loops=1)

42. 1.306 49.629 ↑ 1.5 2,376 1

Hash (cost=1,657.34..1,657.34 rows=3,452 width=56) (actual time=49.629..49.629 rows=2,376 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 237kB
43. 5.678 48.323 ↑ 1.5 2,376 1

Hash Join (cost=1,277.79..1,657.34 rows=3,452 width=56) (actual time=36.288..48.323 rows=2,376 loops=1)

  • Hash Cond: (pi.loan_application_id = lad_1.loan_application_id)
44. 6.448 6.448 ↑ 1.0 18,103 1

Seq Scan on property_information pi (cost=0.00..272.33 rows=18,133 width=16) (actual time=0.020..6.448 rows=18,103 loops=1)

45. 1.179 36.197 ↑ 1.6 2,376 1

Hash (cost=1,228.79..1,228.79 rows=3,920 width=40) (actual time=36.197..36.197 rows=2,376 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 200kB
46. 4.901 35.018 ↑ 1.6 2,376 1

Hash Join (cost=861.43..1,228.79 rows=3,920 width=40) (actual time=23.591..35.018 rows=2,376 loops=1)

  • Hash Cond: (lt_1.loan_application_id = lad_1.loan_application_id)
47. 6.609 6.609 ↑ 1.0 18,492 1

Seq Scan on loan_terms lt_1 (cost=0.00..254.36 rows=18,496 width=8) (actual time=0.014..6.609 rows=18,492 loops=1)

48. 1.109 23.508 ↑ 1.8 2,376 1

Hash (cost=806.88..806.88 rows=4,364 width=32) (actual time=23.508..23.508 rows=2,376 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 213kB
49. 3.616 22.399 ↑ 1.8 2,376 1

Hash Join (cost=548.52..806.88 rows=4,364 width=32) (actual time=14.235..22.399 rows=2,376 loops=1)

  • Hash Cond: (lad_1.loan_application_id = l_1.id)
50. 4.640 4.640 ↑ 1.0 12,545 1

Seq Scan on loan_application_dates lad_1 (cost=0.00..167.67 rows=12,547 width=8) (actual time=0.011..4.640 rows=12,545 loops=1)

51. 1.005 14.143 ↑ 3.0 2,376 1

Hash (cost=458.99..458.99 rows=7,162 width=24) (actual time=14.143..14.143 rows=2,376 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 194kB
52. 4.614 13.138 ↑ 3.0 2,376 1

Hash Join (cost=1.03..458.99 rows=7,162 width=24) (actual time=0.095..13.138 rows=2,376 loops=1)

  • Hash Cond: (l_1.loan_status_id = ls.id)
53. 8.474 8.474 ↓ 1.0 20,604 1

Seq Scan on loan_applications l_1 (cost=0.00..309.12 rows=20,592 width=24) (actual time=0.013..8.474 rows=20,604 loops=1)

54. 0.013 0.050 ↓ 1.6 26 1

Hash (cost=0.83..0.83 rows=16 width=8) (actual time=0.050..0.050 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
55. 0.037 0.037 ↓ 1.6 26 1

Seq Scan on loan_status ls (cost=0.00..0.83 rows=16 width=8) (actual time=0.022..0.037 rows=26 loops=1)

  • Filter: (application_received AND (is_active OR is_closed_deal) AND (id <> ALL ('{6,43,39}'::bigint[])))
  • Rows Removed by Filter: 27
56. 9.504 9.504 ↑ 1.0 1 2,376

Index Only Scan using addresses_pkey on addresses a (cost=0.29..0.32 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=2,376)

  • Index Cond: (address_id = pi.address_id)
  • Heap Fetches: 1475
57. 0.042 0.268 ↓ 1.0 149 1

Hash (cost=6.84..6.84 rows=148 width=8) (actual time=0.268..0.268 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
58. 0.079 0.226 ↓ 1.0 149 1

Hash Join (cost=3.23..6.84 rows=148 width=8) (actual time=0.131..0.226 rows=149 loops=1)

  • Hash Cond: (tm1.person_id = prs1.id)
59. 0.057 0.057 ↓ 1.0 149 1

Seq Scan on team_members tm1 (cost=0.00..1.88 rows=148 width=16) (actual time=0.028..0.057 rows=149 loops=1)

60. 0.034 0.090 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
61. 0.056 0.056 ↑ 1.0 117 1

Seq Scan on persons prs1 (cost=0.00..1.77 rows=117 width=8) (actual time=0.023..0.056 rows=117 loops=1)

62. 1.094 3.814 ↑ 1.0 2,676 1

Hash (cost=76.93..76.93 rows=2,699 width=61) (actual time=3.814..3.814 rows=2,676 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 204kB
63. 1.279 2.720 ↑ 1.0 2,676 1

Hash Left Join (cost=3.23..76.93 rows=2,699 width=61) (actual time=0.150..2.720 rows=2,676 loops=1)

  • Hash Cond: (qr.reviewer_login_id = prs.login_id)
64. 1.344 1.344 ↑ 1.0 2,676 1

Seq Scan on quality_reviews qr (cost=0.00..36.59 rows=2,699 width=37) (actual time=0.027..1.344 rows=2,676 loops=1)

65. 0.045 0.097 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
66. 0.052 0.052 ↑ 1.0 117 1

Seq Scan on persons prs (cost=0.00..1.77 rows=117 width=40) (actual time=0.012..0.052 rows=117 loops=1)

67. 1.161 2.390 ↓ 1.0 3,448 1

Hash (cost=44.00..44.00 rows=3,420 width=21) (actual time=2.390..2.390 rows=3,448 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 221kB
68. 1.229 1.229 ↓ 1.0 3,448 1

Seq Scan on quality_schedules qs (cost=0.00..44.00 rows=3,420 width=21) (actual time=0.012..1.229 rows=3,448 loops=1)

69.          

CTE fico_scores

70. 19.832 42.736 ↓ 1.0 11,093 1

GroupAggregate (cost=0.29..853.54 rows=11,080 width=12) (actual time=0.053..42.736 rows=11,093 loops=1)

  • Group Key: credit_scores.loan_application_id
71. 22.904 22.904 ↓ 1.0 15,822 1

Index Scan using credit_scores_loan_application_idx on credit_scores (cost=0.29..466.15 rows=15,805 width=20) (actual time=0.028..22.904 rows=15,822 loops=1)

72.          

CTE all_appraisals

73. 3.293 12.785 ↓ 1.0 2,801 1

WindowAgg (cost=422.18..478.16 rows=2,799 width=41) (actual time=8.693..12.785 rows=2,801 loops=1)

74. 2.091 9.492 ↓ 1.0 2,801 1

Sort (cost=422.18..429.18 rows=2,799 width=33) (actual time=8.688..9.492 rows=2,801 loops=1)

  • Sort Key: aa.loan_application_id, aa.ordered_local_date DESC
  • Sort Method: quicksort Memory: 315kB
75. 3.267 7.401 ↓ 1.0 2,801 1

WindowAgg (cost=205.95..261.93 rows=2,799 width=33) (actual time=3.533..7.401 rows=2,801 loops=1)

76. 1.987 4.134 ↓ 1.0 2,801 1

Sort (cost=205.95..212.95 rows=2,799 width=25) (actual time=3.527..4.134 rows=2,801 loops=1)

  • Sort Key: aa.loan_application_id, aa.ordered_local_date
  • Sort Method: quicksort Memory: 299kB
77. 2.147 2.147 ↓ 1.0 2,801 1

Seq Scan on appraisals aa (cost=0.00..45.70 rows=2,799 width=25) (actual time=0.028..2.147 rows=2,801 loops=1)

  • Filter: (NOT is_removed)
  • Rows Removed by Filter: 11
78.          

CTE team

79. 55.250 185.194 ↓ 1.0 20,604 1

Hash Left Join (cost=59.66..3,706.60 rows=20,592 width=232) (actual time=2.102..185.194 rows=20,604 loops=1)

  • Hash Cond: (tm_ma.person_id = p_ma.id)
80. 9.910 129.850 ↓ 1.0 20,604 1

Hash Left Join (cost=56.43..2,021.92 rows=20,592 width=400) (actual time=1.966..129.850 rows=20,604 loops=1)

  • Hash Cond: (lat.customer_care_member_id = tm_ma.id)
81. 11.451 119.855 ↓ 1.0 20,604 1

Hash Left Join (cost=52.70..1,802.68 rows=20,592 width=400) (actual time=1.873..119.855 rows=20,604 loops=1)

  • Hash Cond: (lat.received_member_id = tm_re.id)
82. 7.193 108.135 ↓ 1.0 20,604 1

Hash Left Join (cost=44.01..1,580.65 rows=20,592 width=344) (actual time=1.595..108.135 rows=20,604 loops=1)

  • Hash Cond: (lat.closer_member_id = tm_cl.id)
83. 7.271 100.700 ↓ 1.0 20,604 1

Hash Left Join (cost=35.33..1,471.96 rows=20,592 width=288) (actual time=1.346..100.700 rows=20,604 loops=1)

  • Hash Cond: (lat.secondary_member_id = tm_s.id)
84. 7.963 93.177 ↓ 1.0 20,604 1

Hash Left Join (cost=26.64..1,369.56 rows=20,592 width=232) (actual time=1.085..93.177 rows=20,604 loops=1)

  • Hash Cond: (lat.processor_member_id = tm_pr.id)
85. 7.994 84.959 ↓ 1.0 20,604 1

Hash Left Join (cost=17.95..1,245.42 rows=20,592 width=176) (actual time=0.814..84.959 rows=20,604 loops=1)

  • Hash Cond: (lat.underwriter_member_id = tm_u.id)
86. 8.819 76.531 ↓ 1.0 20,604 1

Hash Left Join (cost=9.26..1,118.68 rows=20,592 width=120) (actual time=0.373..76.531 rows=20,604 loops=1)

  • Hash Cond: (lat.quality_review_id = tm_q.id)
87. 23.646 67.424 ↓ 1.0 20,604 1

Merge Left Join (cost=0.57..1,018.57 rows=20,592 width=64) (actual time=0.068..67.424 rows=20,604 loops=1)

  • Merge Cond: (la_1.id = lat.loan_application_id)
88. 14.498 14.498 ↓ 1.0 20,604 1

Index Only Scan using loan_applications_pkey on loan_applications la_1 (cost=0.29..349.35 rows=20,592 width=8) (actual time=0.039..14.498 rows=20,604 loops=1)

  • Heap Fetches: 9149
89. 29.280 29.280 ↓ 1.0 20,596 1

Index Scan using loan_application_team_members_unique_per_loan on loan_application_team_members lat (cost=0.29..365.35 rows=20,191 width=64) (actual time=0.023..29.280 rows=20,596 loops=1)

90. 0.046 0.288 ↓ 1.0 149 1

Hash (cost=6.84..6.84 rows=148 width=72) (actual time=0.288..0.288 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
91. 0.095 0.242 ↓ 1.0 149 1

Hash Left Join (cost=3.23..6.84 rows=148 width=72) (actual time=0.138..0.242 rows=149 loops=1)

  • Hash Cond: (tm_q.person_id = p_q.id)
92. 0.047 0.047 ↓ 1.0 149 1

Seq Scan on team_members tm_q (cost=0.00..1.88 rows=148 width=16) (actual time=0.023..0.047 rows=149 loops=1)

93. 0.037 0.100 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
94. 0.063 0.063 ↑ 1.0 117 1

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

95. 0.062 0.434 ↓ 1.0 149 1

Hash (cost=6.84..6.84 rows=148 width=72) (actual time=0.434..0.434 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
96. 0.245 0.372 ↓ 1.0 149 1

Hash Left Join (cost=3.23..6.84 rows=148 width=72) (actual time=0.269..0.372 rows=149 loops=1)

  • Hash Cond: (tm_u.person_id = p_u.id)
97. 0.042 0.042 ↓ 1.0 149 1

Seq Scan on team_members tm_u (cost=0.00..1.88 rows=148 width=16) (actual time=0.007..0.042 rows=149 loops=1)

98. 0.036 0.085 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
99. 0.049 0.049 ↑ 1.0 117 1

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

100. 0.055 0.255 ↓ 1.0 149 1

Hash (cost=6.84..6.84 rows=148 width=72) (actual time=0.255..0.255 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
101. 0.095 0.200 ↓ 1.0 149 1

Hash Left Join (cost=3.23..6.84 rows=148 width=72) (actual time=0.099..0.200 rows=149 loops=1)

  • Hash Cond: (tm_pr.person_id = p_pr.id)
102. 0.026 0.026 ↓ 1.0 149 1

Seq Scan on team_members tm_pr (cost=0.00..1.88 rows=148 width=16) (actual time=0.008..0.026 rows=149 loops=1)

103. 0.041 0.079 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
104. 0.038 0.038 ↑ 1.0 117 1

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

105. 0.046 0.252 ↓ 1.0 149 1

Hash (cost=6.84..6.84 rows=148 width=72) (actual time=0.252..0.252 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
106. 0.097 0.206 ↓ 1.0 149 1

Hash Left Join (cost=3.23..6.84 rows=148 width=72) (actual time=0.104..0.206 rows=149 loops=1)

  • Hash Cond: (tm_s.person_id = p_s.id)
107. 0.031 0.031 ↓ 1.0 149 1

Seq Scan on team_members tm_s (cost=0.00..1.88 rows=148 width=16) (actual time=0.007..0.031 rows=149 loops=1)

108. 0.036 0.078 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
109. 0.042 0.042 ↑ 1.0 117 1

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

110. 0.047 0.242 ↓ 1.0 149 1

Hash (cost=6.84..6.84 rows=148 width=72) (actual time=0.242..0.242 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
111. 0.083 0.195 ↓ 1.0 149 1

Hash Left Join (cost=3.23..6.84 rows=148 width=72) (actual time=0.091..0.195 rows=149 loops=1)

  • Hash Cond: (tm_cl.person_id = p_cl.id)
112. 0.037 0.037 ↓ 1.0 149 1

Seq Scan on team_members tm_cl (cost=0.00..1.88 rows=148 width=16) (actual time=0.006..0.037 rows=149 loops=1)

113. 0.035 0.075 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
114. 0.040 0.040 ↑ 1.0 117 1

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

115. 0.047 0.269 ↓ 1.0 149 1

Hash (cost=6.84..6.84 rows=148 width=72) (actual time=0.269..0.269 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
116. 0.099 0.222 ↓ 1.0 149 1

Hash Left Join (cost=3.23..6.84 rows=148 width=72) (actual time=0.103..0.222 rows=149 loops=1)

  • Hash Cond: (tm_re.person_id = p_re.id)
117. 0.044 0.044 ↓ 1.0 149 1

Seq Scan on team_members tm_re (cost=0.00..1.88 rows=148 width=16) (actual time=0.015..0.044 rows=149 loops=1)

118. 0.037 0.079 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
119. 0.042 0.042 ↑ 1.0 117 1

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

120. 0.045 0.085 ↓ 1.0 149 1

Hash (cost=1.88..1.88 rows=148 width=16) (actual time=0.085..0.085 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
121. 0.040 0.040 ↓ 1.0 149 1

Seq Scan on team_members tm_ma (cost=0.00..1.88 rows=148 width=16) (actual time=0.008..0.040 rows=149 loops=1)

122. 0.051 0.094 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
123. 0.043 0.043 ↑ 1.0 117 1

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

124.          

CTE ltv_dti

125. 16.174 649.733 ↓ 35.4 13,314 1

Subquery Scan on a_1 (cost=31,096.38..33,726.17 rows=376 width=28) (actual time=547.332..649.733 rows=13,314 loops=1)

  • Filter: (a_1.rn = 1)
  • Rows Removed by Filter: 61268
126. 60.954 633.559 ↑ 1.0 74,582 1

WindowAgg (cost=31,096.38..32,786.96 rows=75,137 width=36) (actual time=547.315..633.559 rows=74,582 loops=1)

127. 100.591 572.605 ↑ 1.0 74,582 1

Sort (cost=31,096.38..31,284.22 rows=75,137 width=28) (actual time=547.282..572.605 rows=74,582 loops=1)

  • Sort Key: loan_tolerance_snapshot_items.loan_application_id, loan_tolerance_snapshot_items.last_updated_date DESC
  • Sort Method: quicksort Memory: 8899kB
128. 26.518 472.014 ↑ 1.0 74,582 1

Gather (cost=10.00..25,011.32 rows=75,137 width=28) (actual time=9.755..472.014 rows=74,582 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
129. 445.496 445.496 ↑ 1.3 24,861 3 / 3

Parallel Seq Scan on loan_tolerance_snapshot_items (cost=0.00..17,487.62 rows=31,307 width=28) (actual time=0.099..445.496 rows=24,861 loops=3)

  • Filter: (tolerance_property_id = 4)
  • Rows Removed by Filter: 678396
130.          

CTE excl_addr

131. 0.011 18.979 ↓ 11.0 11 1

Nested Loop (cost=0.72..1,017.50 rows=1 width=29) (actual time=2.365..18.979 rows=11 loops=1)

132. 0.708 18.913 ↓ 11.0 11 1

Nested Loop (cost=0.58..1,017.24 rows=1 width=37) (actual time=2.350..18.913 rows=11 loops=1)

133. 1.443 12.247 ↓ 1.1 993 1

Nested Loop (cost=0.29..724.59 rows=874 width=32) (actual time=0.057..12.247 rows=993 loops=1)

134. 6.832 6.832 ↑ 1.0 993 1

Seq Scan on loan_applications la_2 (cost=0.00..360.60 rows=993 width=16) (actual time=0.030..6.832 rows=993 loops=1)

  • Filter: (loan_status_id = 23)
  • Rows Removed by Filter: 19611
135. 3.972 3.972 ↑ 1.0 1 993

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

  • Index Cond: (loan_application_id = la_2.id)
  • Filter: ((NOT is_removed) AND (NOT is_removed))
136. 5.958 5.958 ↓ 0.0 0 993

Index Scan using addresses_pkey on addresses addr_1 (cost=0.29..0.32 rows=1 width=37) (actual time=0.006..0.006 rows=0 loops=993)

  • Index Cond: (address_id = pri_1.address_id)
  • Filter: ((pri_1.loan_application_id = loan_application_id) AND (lower(street_address) ~~ '%tbd%'::text))
  • Rows Removed by Filter: 1
137. 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
138.          

CTE mi

139. 3.058 32.960 ↓ 181.7 9,992 1

Subquery Scan on a_2 (cost=884.29..1,402.23 rows=55 width=48) (actual time=10.780..32.960 rows=9,992 loops=1)

  • Filter: (a_2.rn = 1)
  • Rows Removed by Filter: 916
140. 15.760 29.902 ↓ 1.0 10,908 1

WindowAgg (cost=884.29..1,265.93 rows=10,904 width=56) (actual time=10.775..29.902 rows=10,908 loops=1)

141. 9.753 14.142 ↓ 1.0 10,908 1

Sort (cost=884.29..911.55 rows=10,904 width=20) (actual time=10.742..14.142 rows=10,908 loops=1)

  • Sort Key: mortgage_insurances.loan_application_id, mortgage_insurances.last_updated_date DESC
  • Sort Method: quicksort Memory: 1046kB
142. 4.389 4.389 ↓ 1.0 10,908 1

Seq Scan on mortgage_insurances (cost=0.00..153.04 rows=10,904 width=20) (actual time=0.020..4.389 rows=10,908 loops=1)

143.          

CTE last_pro

144. 1.205 47.365 ↓ 176.8 3,890 1

Subquery Scan on a_3 (cost=1,747.68..1,934.38 rows=22 width=88) (actual time=37.271..47.365 rows=3,890 loops=1)

  • Filter: (a_3.rn = 1)
  • Rows Removed by Filter: 601
145. 7.566 46.160 ↓ 1.0 4,491 1

WindowAgg (cost=1,747.68..1,879.47 rows=4,393 width=88) (actual time=37.266..46.160 rows=4,491 loops=1)

146. 4.855 38.594 ↓ 1.0 4,491 1

Sort (cost=1,747.68..1,758.66 rows=4,393 width=144) (actual time=37.239..38.594 rows=4,491 loops=1)

  • Sort Key: ltm.loan_application_id, ltmu.last_updated_date DESC
  • Sort Method: quicksort Memory: 549kB
147. 2.204 33.739 ↓ 1.0 4,491 1

Hash Left Join (cost=537.07..1,481.88 rows=4,393 width=144) (actual time=14.183..33.739 rows=4,491 loops=1)

  • Hash Cond: (ltmu.processor_member_id_new = tm_pr_1.id)
148. 1.375 31.226 ↓ 1.0 4,491 1

Hash Left Join (cost=528.38..1,453.58 rows=4,393 width=88) (actual time=13.851..31.226 rows=4,491 loops=1)

  • Hash Cond: (ltmu.processor_member_id_old = tm_pr_old.id)
149. 3.167 29.553 ↓ 1.0 4,491 1

Hash Join (cost=519.70..1,427.96 rows=4,393 width=32) (actual time=13.533..29.553 rows=4,491 loops=1)

  • Hash Cond: (ltmu.updated_id = ltm.id)
150. 13.094 13.094 ↓ 1.0 4,491 1

Seq Scan on loan_application_team_members_updates ltmu (cost=0.00..847.79 rows=4,411 width=32) (actual time=0.037..13.094 rows=4,491 loops=1)

  • Filter: (processor_member_id_new IS NOT NULL)
  • Rows Removed by Filter: 58420
151. 6.231 13.292 ↓ 1.0 20,596 1

Hash (cost=267.31..267.31 rows=20,191 width=16) (actual time=13.292..13.292 rows=20,596 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1222kB
152. 7.061 7.061 ↓ 1.0 20,596 1

Seq Scan on loan_application_team_members ltm (cost=0.00..267.31 rows=20,191 width=16) (actual time=0.013..7.061 rows=20,596 loops=1)

153. 0.066 0.298 ↓ 1.0 149 1

Hash (cost=6.84..6.84 rows=148 width=72) (actual time=0.298..0.298 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
154. 0.098 0.232 ↓ 1.0 149 1

Hash Left Join (cost=3.23..6.84 rows=148 width=72) (actual time=0.136..0.232 rows=149 loops=1)

  • Hash Cond: (tm_pr_old.person_id = p_pr_old.id)
155. 0.035 0.035 ↓ 1.0 149 1

Seq Scan on team_members tm_pr_old (cost=0.00..1.88 rows=148 width=16) (actual time=0.016..0.035 rows=149 loops=1)

156. 0.047 0.099 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
157. 0.052 0.052 ↑ 1.0 117 1

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

158. 0.068 0.309 ↓ 1.0 149 1

Hash (cost=6.84..6.84 rows=148 width=72) (actual time=0.309..0.309 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
159. 0.097 0.241 ↓ 1.0 149 1

Hash Left Join (cost=3.23..6.84 rows=148 width=72) (actual time=0.131..0.241 rows=149 loops=1)

  • Hash Cond: (tm_pr_1.person_id = p_pr_1.id)
160. 0.052 0.052 ↓ 1.0 149 1

Seq Scan on team_members tm_pr_1 (cost=0.00..1.88 rows=148 width=16) (actual time=0.010..0.052 rows=149 loops=1)

161. 0.051 0.092 ↑ 1.0 117 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
162. 0.041 0.041 ↑ 1.0 117 1

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

163.          

CTE tf_flag

164. 15.888 15.888 ↑ 1.0 20,594 1

Index Scan using affiliated_business_details_loan_and_business_idx on affiliated_business_details (cost=0.29..595.00 rows=20,698 width=9) (actual time=0.018..15.888 rows=20,594 loops=1)

  • Index Cond: (business_type = 2)
165.          

CTE qc_details

166. 1.575 191.062 ↑ 1.0 1,519 1

Unique (cost=7,351.65..7,436.74 rows=1,547 width=376) (actual time=187.529..191.062 rows=1,519 loops=1)

167. 8.330 189.487 ↑ 1.0 1,519 1

Sort (cost=7,351.65..7,355.52 rows=1,547 width=376) (actual time=187.528..189.487 rows=1,519 loops=1)

  • Sort Key: loan_deficiencies.loan_application_id, (max(loan_deficiencies.response_submitted_date)), (max(CASE WHEN (loan_deficiencies.severity_type = ANY ('{2,3,4,5}'::integer[])) THEN loan_deficiencies.severity_type ELSE NULL::integer END)), (count(CASE WHEN (loan_deficiencies.severity_type = 2) THEN 1 ELSE NULL::integer END)), (count(CASE WHEN (loan_deficiencies.severity_type = 3) THEN 1 ELSE NULL::integer END)), (count(CASE WHEN (loan_deficiencies.severity_type = ANY ('{4,5}'::integer[])) THEN 1 ELSE NULL::integer END)), (string_agg((COALESCE((loan_deficiencies.severity_type)::character varying, '-'::character varying))::text, 'newline'::text)), (string_agg(COALESCE(loan_deficiencies.description, '-'::text), 'newline'::text)), (string_agg((COALESCE((loan_deficiencies.deficiency_category_type)::character varying, '-'::character varying))::text, 'newline'::text)), (string_agg((COALESCE((loan_deficiencies.deficiency_subcategory_type)::character varying, '-'::character varying))::text, 'newline'::text)), (string_agg((COALESCE((loan_deficiencies.area_of_responsibility_type)::character varying, '-'::character varying))::text, 'newline'::text)), (string_agg((COALESCE((prs1_1.last_name)::character varying, '-'::character varying))::text, 'newline'::text)), (string_agg((COALESCE((loan_deficiencies.responsible_party_comments)::character varying, '-'::character varying))::text, 'newline'::text)), (string_agg((COALESCE((loan_deficiencies.initial_reviewer_comments)::character varying, '-'::character varying))::text, 'newline'::text)), (string_agg((COALESCE((loan_deficiencies.reporting_result_type)::character varying, '-'::character varying))::text, 'newline'::text)), (count(CASE WHEN (loan_deficiencies.severity_type = ANY ('{2,3,4,5}'::integer[])) THEN 1 ELSE NULL::integer END)), (count(CASE WHEN (loan_deficiencies.severity_type = 6) THEN 1 ELSE NULL::integer END)), (count(CASE WHEN (loan_deficiencies.severity_type = 7) THEN 1 ELSE NULL::integer END)), (count(CASE WHEN (loan_deficiencies.severity_type = 8) THEN 1 ELSE NULL::integer END)), (count(CASE WHEN (loan_deficiencies.severity_type = ANY ('{6,7,8}'::integer[])) THEN 1 ELSE NULL::integer END)), (max(CASE WHEN (loan_deficiencies.severity_type = ANY ('{6,7,8}'::integer[])) THEN loan_deficiencies.severity_type ELSE NULL::integer END))
  • Sort Method: quicksort Memory: 4326kB
168. 64.808 181.157 ↑ 1.0 1,519 1

GroupAggregate (cost=5,750.96..7,269.70 rows=1,547 width=376) (actual time=110.927..181.157 rows=1,519 loops=1)

  • Group Key: loan_deficiencies.loan_application_id
169. 18.423 116.349 ↑ 1.1 12,423 1

Sort (cost=5,750.96..5,783.59 rows=13,053 width=508) (actual time=110.831..116.349 rows=12,423 loops=1)

  • Sort Key: loan_deficiencies.loan_application_id
  • Sort Method: quicksort Memory: 4372kB
170. 49.194 97.926 ↑ 1.1 12,423 1

Gather (cost=13.23..4,858.65 rows=13,053 width=508) (actual time=8.861..97.926 rows=12,423 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
171. 3.142 48.732 ↑ 1.3 4,141 3 / 3

Hash Join (cost=3.23..3,543.35 rows=5,439 width=508) (actual time=0.385..48.732 rows=4,141 loops=3)

  • Hash Cond: (loan_deficiencies.responsible_party_login_id = prs1_1.login_id)
172. 45.456 45.456 ↑ 1.2 4,400 3 / 3

Parallel Seq Scan on loan_deficiencies (cost=0.00..3,465.11 rows=5,498 width=484) (actual time=0.028..45.456 rows=4,400 loops=3)

  • Filter: (severity_type IS NOT NULL)
  • Rows Removed by Filter: 119477
173. 0.050 0.134 ↑ 1.0 117 3 / 3

Hash (cost=1.77..1.77 rows=117 width=40) (actual time=0.134..0.134 rows=117 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
174. 0.084 0.084 ↑ 1.0 117 3 / 3

Seq Scan on persons prs1_1 (cost=0.00..1.77 rows=117 width=40) (actual time=0.040..0.084 rows=117 loops=3)

175.          

CTE loan_apps

176. 17.929 40.393 ↓ 1.1 11,594 1

Sort (cost=1,176.47..1,202.21 rows=10,296 width=220) (actual time=33.005..40.393 rows=11,594 loops=1)

  • Sort Key: la_3.id
  • Sort Method: quicksort Memory: 3464kB
177. 7.865 22.464 ↓ 1.1 11,594 1

Hash Join (cost=0.95..490.25 rows=10,296 width=220) (actual time=0.100..22.464 rows=11,594 loops=1)

  • Hash Cond: (la_3.loan_status_id = ls_1.id)
178. 14.554 14.554 ↓ 1.0 20,604 1

Seq Scan on loan_applications la_3 (cost=0.00..309.12 rows=20,592 width=220) (actual time=0.029..14.554 rows=20,604 loops=1)

179. 0.008 0.045 ↓ 1.5 34 1

Hash (cost=0.66..0.66 rows=23 width=8) (actual time=0.045..0.045 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
180. 0.037 0.037 ↓ 1.5 34 1

Seq Scan on loan_status ls_1 (cost=0.00..0.66 rows=23 width=8) (actual time=0.021..0.037 rows=34 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 19
181. 28.125 3,773.366 ↑ 3,950,738.1 11,580 1

Sort (cost=59,731,453,670.39..59,845,827,537.33 rows=45,749,546,778 width=1,599) (actual time=3,770.985..3,773.366 rows=11,580 loops=1)

  • Sort Key: la.id, (CASE WHEN (la.loan_type_id = ANY ('{4,5,6}'::bigint[])) THEN 'Refi'::text ELSE 'Purchase'::text END), lt.home_price, lt.amount, (COALESCE(la.loan_status_last_updated, (curr.created_instant)::timestamp with time zone)), cs.loan_fico, i.investor_loan_id, lt.interest_rate, w.advance_amount, w.participation_amount, (((b.last_name || ', '::text) || b.first_name)), l.name, addr.state_code, addr.county, (((((((((COALESCE(addr.street_address, ''::text) || ', '::text) || COALESCE(addr.unit, ''::text)) || ', '::text) || COALESCE(addr.city, ''::text)) || ', '::text) || COALESCE(addr.state_code, ''::text)) || ' '::text) || COALESCE(addr.zip_code, ''::text))), (CASE WHEN (lad.application_received_local_date IS NULL) THEN NULL::text WHEN ((ca.created_instant)::date < ('now'::cstring)::date) THEN 'Met'::text WHEN ((ex_addr.id IS NULL) AND (lad.application_received_local_date IS NOT NULL)) THEN (((ca.created_instant)::date + '30 days'::interval))::text ELSE NULL::text END), wi.escrow_file_id, (CASE WHEN (pri.project_type = 2) THEN 'Condo'::text WHEN (pri.num_units > 1) THEN 'Multi-Fam'::text WHEN (pri.num_units = 1) THEN 'SFR'::text ELSE 'Other'::text END), (CASE lt.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), (CASE lt.occupancy_type_id WHEN 1 THEN 'primary'::text WHEN 2 THEN 'secondary'::text WHEN 3 THEN 'investment'::text ELSE NULL::text END), (((ltv.value_on_creation)::text || '%'::text)), imd.display_name, mi.mi_company, wm.display_name, i.commitment_number, la.mers_min, w.release_number, t.mortgage_adviser, t.processor, t.underwriter, t.closer, t.quality_reviewer, t.secondary, (CASE WHEN (ex_addr.id IS NULL) THEN lad.application_received_local_date ELSE NULL::date END), lad.closing_local_date, w.funding_local_date, (CASE WHEN (lad.rate_lock_cancelled_local_date IS NULL) THEN lad.rate_lock_local_date ELSE NULL::date END), lad.closing_disclosure_issued_local_date, lad.closing_disclosure_received_local_date, lad.cd_ordered_date, lad.loan_estimate_issued_local_date, lad.le_received_local_date, lad.appraisal_contingency_local_date, lad.financing_contingency_local_date, ip.received_date, lad.rate_lock_expiration_local_date, (COALESCE(ms_app1.field_value, faa.ordered_local_date)), faa.delivered_local_date, laa.delivered_local_date, ms_pr_ti.field_value, ms_icr.field_value, ms_pre.field_value, ms_cd.field_value, faa.sent_to_underwriter_local_date, laa.sent_to_underwriter_local_date, faa.reviewed_date_local_date, laa.reviewed_date_local_date, ms_pc_reg.field_value, ms_pc_tr.field_value, ms_pc_sp.field_value, ms_pc_co.field_value, ms_pc_coi.field_value, ms_pc_s.field_value, ms_pc_sc.field_value, ms_pc_stm.field_value, ms_pc_rmr.field_value, ms_pc_tpr.field_value, ((ctc_docs.created_instant)::date), ((uw_pre.created_instant)::date), ((uw_pred.created_instant)::date), ((uw_f.created_instant)::date), ((dis.created_instant)::date), w.funds_ordered_local_date, lad.pa_conditions_requested_date, ((min_qr.created_instant)::date), ((min_qr2.created_instant)::date), ((uw_f_min.created_instant)::date), ((ctc_docs_h.created_instant)::date), ((ctc_docs_f.created_instant)::date), ((cl.created_instant)::date), i.ship_date, i.purchase_date, i.commitment_expiration_date, i.commitment_date, i.first_payment_due, i.service_release_date, ((ca.created_instant)::date), ((reg.created_instant)::date), ((last_pro.last_updated_date)::date), (CASE WHEN (l.is_1003_submitted AND (NOT l.is_shipped)) THEN true ELSE false END), lt.eclosing, l.is_active, (COALESCE(tf.is_title_forward, la.is_title_forward)), (CASE WHEN (la.loan_status_id = 36) THEN true ELSE false END), (CASE WHEN ((ex_addr.id IS NULL) AND (lad.application_received_local_date IS NOT NULL)) THEN true ELSE false END), faa.appraisal_waiver, laa.appraisal_waiver, qbi.id, qbi.""Borrower_Name"", qbi.""QA1_Status"", qbi.""QA1_Started_Date"", qbi.""QA1_Reviewer"", qbi.""QA1_Schedule"", qbi.""QA1_Reason"", qbi.""QA2_Status"", qbi.""QA2_Started_Date"", qbi.""QA2_Reviewer"", qbi.""QA2_Schedule"", qbi.""QA2_Reason"", qbi.""QC_Status"", qbi.""QC_Started_Date"", qbi.""QC_Reviewer"", qbi.""RQC_Status"", qbi.""RQC_Started_Date"", qbi.""RQC_Reviewer"", qbi.""REOS"", qcd.loan_application_id, qcd.last_response_date, qcd.highest_qc_severity, qcd.minor_total, qcd.moderate_total, qcd.major_total, qcd.severity, qcd.reason, qcd.category, qcd.subcategory, qcd.aor, qcd.responsible_party, qcd.responsible_party_comments, qcd.initial_reviewer_comments, qcd.reporting_results, qcd.qc_severity_total, qcd.low_total, qcd.medium_total, qcd.high_total, qcd.rqc_severity_total, qcd.highest_rqc_severity, ff.loan_application_id, ff.form_free, ((date_part('epoch'::text, (now() - pg_last_xact_replay_timestamp())))::integer)
  • Sort Method: quicksort Memory: 3503kB
182. 76.190 3,745.241 ↑ 3,950,738.1 11,580 1

Merge Right Join (cost=26,965,888,261.32..38,933,515,062.79 rows=45,749,546,778 width=1,599) (actual time=3,491.098..3,745.241 rows=11,580 loops=1)

  • Merge Cond: (ctc_docs_f.loan_application_id = la.id)
183. 1.311 310.554 ↓ 17.9 1,876 1

Sort (cost=477.44..477.70 rows=105 width=16) (actual time=309.903..310.554 rows=1,876 loops=1)

  • Sort Key: ctc_docs_f.loan_application_id
  • Sort Method: quicksort Memory: 136kB
184. 309.243 309.243 ↓ 17.9 1,876 1

CTE Scan on min_transition_events ctc_docs_f (cost=0.00..473.92 rows=105 width=16) (actual time=251.281..309.243 rows=1,876 loops=1)

  • Filter: (to_status = 50)
  • Rows Removed by Filter: 82840
185. 21.330 3,358.497 ↑ 3,950,738.1 11,580 1

Materialize (cost=26,965,887,783.88..32,116,832,114.91 rows=45,749,546,778 width=2,009) (actual time=3,181.024..3,358.497 rows=11,580 loops=1)

186. 10.206 3,337.167 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=26,965,887,783.88..32,002,458,247.96 rows=45,749,546,778 width=2,009) (actual time=3,181.010..3,337.167 rows=11,580 loops=1)

  • Merge Cond: (la.id = ctc_docs_h.loan_application_id)
187. 9.753 3,310.710 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=26,965,887,306.44..31,527,806,222.71 rows=45,749,546,778 width=2,001) (actual time=3,165.340..3,310.710 rows=11,580 loops=1)

  • Merge Cond: (la.id = uw_f_min.loan_application_id)
188. 9.996 3,284.663 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=26,965,886,829.00..31,053,154,197.45 rows=45,749,546,778 width=1,993) (actual time=3,149.669..3,284.663 rows=11,580 loops=1)

  • Merge Cond: (la.id = min_qr2.loan_application_id)
189. 9.770 3,258.608 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=26,965,886,351.55..30,578,502,172.19 rows=45,749,546,778 width=1,985) (actual time=3,134.018..3,258.608 rows=11,580 loops=1)

  • Merge Cond: (la.id = min_qr.loan_application_id)
190. 9.455 3,230.521 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=26,965,885,874.11..30,103,850,146.93 rows=45,749,546,778 width=1,977) (actual time=3,116.143..3,230.521 rows=11,580 loops=1)

  • Merge Cond: (la.id = reg.loan_application_id)
191. 10.196 3,205.660 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=26,965,885,396.67..29,629,198,121.68 rows=45,749,546,778 width=1,969) (actual time=3,100.906..3,205.660 rows=11,580 loops=1)

  • Merge Cond: (la.id = cl.loan_application_id)
192. 9.495 3,179.387 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=26,965,884,919.23..29,154,546,096.42 rows=45,749,546,778 width=1,961) (actual time=3,085.321..3,179.387 rows=11,580 loops=1)

  • Merge Cond: (la.id = ctc_docs.loan_application_id)
193. 9.227 3,153.789 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=26,965,884,441.78..28,679,894,071.16 rows=45,749,546,778 width=1,953) (actual time=3,069.794..3,153.789 rows=11,580 loops=1)

  • Merge Cond: (la.id = dis.loan_application_id)
194. 10.076 3,128.817 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=26,965,883,964.34..28,205,242,045.90 rows=45,749,546,778 width=1,945) (actual time=3,054.479..3,128.817 rows=11,580 loops=1)

  • Merge Cond: (la.id = uw_f.loan_application_id)
195. 41.254 3,102.326 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=26,965,883,486.90..27,730,590,020.65 rows=45,749,546,778 width=1,937) (actual time=3,038.712..3,102.326 rows=11,580 loops=1)

  • Merge Cond: ((la.id = curr.loan_application_id) AND (la.loan_status_id = curr.to_status))
196. 31.209 2,968.690 ↑ 3,950,738.1 11,580 1

Sort (cost=26,965,881,553.06..27,080,255,420.00 rows=45,749,546,778 width=1,929) (actual time=2,964.379..2,968.690 rows=11,580 loops=1)

  • Sort Key: la.id, la.loan_status_id
  • Sort Method: quicksort Memory: 3465kB
197. 11.336 2,937.481 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=29,799.32..3,540,918,189.86 rows=45,749,546,778 width=1,929) (actual time=2,505.361..2,937.481 rows=11,580 loops=1)

  • Merge Cond: (la.id = ca.loan_application_id)
198. 9.863 2,908.955 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=29,321.88..3,066,266,164.61 rows=45,749,546,778 width=1,921) (actual time=2,489.233..2,908.955 rows=11,580 loops=1)

  • Merge Cond: (la.id = uw_pred.loan_application_id)
199. 10.237 2,883.359 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=28,844.44..2,591,614,139.35 rows=45,749,546,778 width=1,913) (actual time=2,473.875..2,883.359 rows=11,580 loops=1)

  • Merge Cond: (la.id = uw_pre.loan_application_id)
200. 10.137 2,565.700 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=28,366.99..2,116,962,114.09 rows=45,749,546,778 width=1,905) (actual time=2,166.975..2,565.700 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_pc_tpr.loan_application_id)
201. 9.719 2,543.863 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=27,146.88..1,999,155,811.02 rows=45,749,546,778 width=1,901) (actual time=2,155.320..2,543.863 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_pc_rmr.loan_application_id)
202. 11.142 2,522.418 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=25,926.77..1,881,349,507.95 rows=45,749,546,778 width=1,897) (actual time=2,143.643..2,522.418 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_pc_stm.loan_application_id)
203. 9.486 2,497.461 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=24,706.66..1,763,543,204.88 rows=45,749,546,778 width=1,893) (actual time=2,130.910..2,497.461 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_pc_sc.loan_application_id)
204. 9.950 2,475.830 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=23,486.55..1,645,736,901.81 rows=45,749,546,778 width=1,889) (actual time=2,119.096..2,475.830 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_pc_s.loan_application_id)
205. 10.760 2,453.587 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=22,266.44..1,527,930,598.74 rows=45,749,546,778 width=1,885) (actual time=2,107.405..2,453.587 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_pc_coi.loan_application_id)
206. 10.103 2,429.200 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=21,046.33..1,410,124,295.67 rows=45,749,546,778 width=1,881) (actual time=2,094.916..2,429.200 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_pc_co.loan_application_id)
207. 9.961 2,405.155 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=19,826.22..1,292,317,992.60 rows=45,749,546,778 width=1,877) (actual time=2,082.181..2,405.155 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_pc_sp.loan_application_id)
208. 9.649 2,380.178 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=18,606.11..1,174,511,689.53 rows=45,749,546,778 width=1,873) (actual time=2,068.386..2,380.178 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_pc_tr.loan_application_id)
209. 10.710 2,350.961 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=17,386.00..1,056,705,386.46 rows=45,749,546,778 width=1,869) (actual time=2,049.694..2,350.961 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_pc_reg.loan_application_id)
210. 9.852 2,326.723 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=16,165.89..938,899,083.39 rows=45,749,546,778 width=1,865) (actual time=2,037.354..2,326.723 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_cd.loan_application_id)
211. 10.304 2,303.635 ↑ 3,950,738.1 11,580 1

Merge Left Join (cost=14,945.78..821,092,780.32 rows=45,749,546,778 width=1,861) (actual time=2,025.359..2,303.635 rows=11,580 loops=1)

  • Merge Cond: (la.id = qbi.id)
212. 9.306 2,129.351 ↑ 233,426.2 11,580 1

Merge Left Join (cost=14,679.64..128,091,624.65 rows=2,703,075,142 width=1,637) (actual time=1,862.868..2,129.351 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_pre.loan_application_id)
213. 8.828 2,107.151 ↑ 233,426.2 11,580 1

Merge Left Join (cost=13,459.53..121,129,986.04 rows=2,703,075,142 width=1,633) (actual time=1,851.232..2,107.151 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_icr.loan_application_id)
214. 9.519 2,085.345 ↑ 233,426.2 11,580 1

Merge Left Join (cost=12,239.42..114,168,347.43 rows=2,703,075,142 width=1,629) (actual time=1,839.577..2,085.345 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_pr_ti.loan_application_id)
215. 8.573 2,060.631 ↑ 233,426.2 11,580 1

Merge Left Join (cost=11,019.31..107,206,708.82 rows=2,703,075,142 width=1,625) (actual time=1,825.973..2,060.631 rows=11,580 loops=1)

  • Merge Cond: (la.id = ms_app1.loan_application_id)
216. 9.112 1,937.177 ↑ 233,426.2 11,580 1

Merge Left Join (cost=9,799.20..100,245,070.21 rows=2,703,075,142 width=1,621) (actual time=1,711.414..1,937.177 rows=11,580 loops=1)

  • Merge Cond: (la.id = last_pro.loan_application_id)
217. 7.397 1,875.323 ↑ 233,426.2 11,580 1

Merge Left Join (cost=9,798.27..89,027,307.44 rows=2,703,075,142 width=1,613) (actual time=1,660.137..1,875.323 rows=11,580 loops=1)

  • Merge Cond: (la.id = ff.loan_application_id)
218. 12.216 1,867.754 ↑ 233,426.2 11,580 1

Merge Left Join (cost=9,786.62..41,723,480.81 rows=2,703,075,142 width=1,573) (actual time=1,659.972..1,867.754 rows=11,580 loops=1)

  • Merge Cond: (la.id = cs.loan_application_id)
219. 9.045 1,798.943 ↑ 4,213.5 11,580 1

Merge Left Join (cost=8,820.69..1,054,407.88 rows=48,791,970 width=1,569) (actual time=1,606.860..1,798.943 rows=11,580 loops=1)

  • Merge Cond: (la.id = qcd.loan_application_id)
220. 8.099 1,587.836 ↑ 544.7 11,580 1

Merge Left Join (cost=8,707.79..306,645.57 rows=6,307,947 width=1,193) (actual time=1,408.896..1,587.836 rows=11,580 loops=1)

  • Merge Cond: (la.id = laa.loan_application_id)
221. 7.692 1,576.653 ↑ 544.7 11,580 1

Merge Left Join (cost=8,644.55..284,189.11 rows=6,307,947 width=1,180) (actual time=1,406.908..1,576.653 rows=11,580 loops=1)

  • Merge Cond: (la.id = faa.loan_application_id)
222. 10.064 1,551.438 ↑ 544.7 11,580 1

Merge Left Join (cost=8,581.31..261,732.66 rows=6,307,947 width=1,163) (actual time=1,390.562..1,551.438 rows=11,580 loops=1)

  • Merge Cond: (la.id = mi.loan_application_id)
223. 8.768 1,495.447 ↑ 544.7 11,580 1

Merge Left Join (cost=8,578.62..219,939.83 rows=6,307,947 width=1,131) (actual time=1,348.050..1,495.447 rows=11,580 loops=1)

  • Merge Cond: (la.id = i.loan_application_id)
224. 15.049 1,477.886 ↑ 544.7 11,580 1

Merge Left Join (cost=8,283.20..109,255.34 rows=6,307,947 width=1,056) (actual time=1,341.312..1,477.886 rows=11,580 loops=1)

  • Merge Cond: (la.id = t.loan_id)
225. 9.934 1,236.044 ↑ 5.3 11,580 1

Merge Left Join (cost=6,395.96..12,595.73 rows=61,266 width=864) (actual time=1,121.488..1,236.044 rows=11,580 loops=1)

  • Merge Cond: (la.id = ltv.loan_application_id)
226. 7.962 557.407 ↑ 5.3 11,580 1

Merge Left Join (cost=6,387.49..12,424.90 rows=61,266 width=348) (actual time=457.178..557.407 rows=11,580 loops=1)

  • Merge Cond: (la.id = wi.loan_application_id)
227. 5.707 536.005 ↑ 5.3 11,580 1

Merge Left Join (cost=6,387.21..11,296.29 rows=61,266 width=337) (actual time=457.139..536.005 rows=11,580 loops=1)

  • Merge Cond: (la.id = ex_addr.id)
228. 21.737 511.257 ↑ 5.3 11,580 1

Nested Loop Left Join (cost=6,387.18..11,138.50 rows=61,266 width=329) (actual time=438.101..511.257 rows=11,580 loops=1)

  • Join Filter: (wm.id = w.warehouse_metadata_id)
  • Rows Removed by Join Filter: 34477
229. 13.767 489.520 ↑ 5.3 11,580 1

Merge Left Join (cost=6,387.18..8,381.29 rows=61,266 width=305) (actual time=438.043..489.520 rows=11,580 loops=1)

  • Merge Cond: (la.id = pri.loan_application_id)
230. 17.881 377.748 ↑ 5.3 11,580 1

Merge Left Join (cost=3,024.31..3,946.26 rows=61,266 width=249) (actual time=348.436..377.748 rows=11,580 loops=1)

  • Merge Cond: (la.id = tf.loan_application_id)
231. 22.809 321.369 ↓ 19.6 11,580 1

Sort (cost=1,126.60..1,128.08 rows=592 width=248) (actual time=315.804..321.369 rows=11,580 loops=1)

  • Sort Key: la.id
  • Sort Method: quicksort Memory: 2408kB
232. 7.603 298.560 ↓ 19.6 11,580 1

Nested Loop Left Join (cost=76.29..1,099.34 rows=592 width=248) (actual time=35.003..298.560 rows=11,580 loops=1)

233. 10.288 244.637 ↓ 19.6 11,580 1

Hash Left Join (cost=76.01..904.85 rows=592 width=196) (actual time=34.985..244.637 rows=11,580 loops=1)

  • Hash Cond: (la.id = ip.loan_application_id)
234. 5.717 232.524 ↓ 19.6 11,580 1

Nested Loop Left Join (cost=2.10..822.80 rows=592 width=192) (actual time=33.148..232.524 rows=11,580 loops=1)

235. 9.867 180.487 ↓ 19.6 11,580 1

Nested Loop Left Join (cost=1.81..626.41 rows=592 width=165) (actual time=33.135..180.487 rows=11,580 loops=1)

236. 9.762 135.880 ↓ 19.6 11,580 1

Nested Loop Left Join (cost=1.52..435.40 rows=592 width=113) (actual time=33.125..135.880 rows=11,580 loops=1)

237. 10.688 68.218 ↓ 19.6 11,580 1

Hash Join (cost=1.24..222.73 rows=592 width=108) (actual time=33.101..68.218 rows=11,580 loops=1)

  • Hash Cond: (la.loan_status_id = l.id)
238. 57.471 57.471 ↓ 4.5 11,580 1

CTE Scan on loan_apps la (cost=0.00..205.92 rows=2,574 width=73) (actual time=33.011..57.471 rows=11,580 loops=1)

  • Filter: ((NOT is_removed) AND (NOT is_test))
  • Rows Removed by Filter: 14
239. 0.025 0.059 ↓ 1.2 53 1

Hash (cost=0.66..0.66 rows=46 width=43) (actual time=0.059..0.059 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
240. 0.034 0.034 ↓ 1.2 53 1

Seq Scan on loan_status l (cost=0.00..0.66 rows=46 width=43) (actual time=0.021..0.034 rows=53 loops=1)

241. 57.900 57.900 ↑ 1.0 1 11,580

Index Scan using borrowers_pkey on borrowers b (cost=0.29..0.35 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=11,580)

  • Index Cond: (la.primary_borrower_id = id)
242. 34.740 34.740 ↓ 0.0 0 11,580

Index Scan using loan_application_dates_unique_per_loan on loan_application_dates lad (cost=0.29..0.31 rows=1 width=60) (actual time=0.003..0.003 rows=0 loops=11,580)

  • Index Cond: (la.id = loan_application_id)
243. 46.320 46.320 ↑ 1.0 1 11,580

Index Scan using loan_terms_unique_per_loan on loan_terms lt (cost=0.29..0.32 rows=1 width=35) (actual time=0.003..0.004 rows=1 loops=11,580)

  • Index Cond: (la.id = loan_application_id)
244. 0.788 1.825 ↓ 1.0 2,902 1

Hash (cost=37.85..37.85 rows=2,885 width=12) (actual time=1.825..1.825 rows=2,902 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 169kB
245. 1.037 1.037 ↓ 1.0 2,902 1

Seq Scan on intent_to_proceed ip (cost=0.00..37.85 rows=2,885 width=12) (actual time=0.012..1.037 rows=2,902 loops=1)

246. 46.320 46.320 ↑ 1.0 1 11,580

Index Scan using warehouses_loan_application_idx on warehouses w (cost=0.29..0.32 rows=1 width=60) (actual time=0.003..0.004 rows=1 loops=11,580)

  • Index Cond: (loan_application_id = la.id)
247. 12.130 38.498 ↑ 1.0 20,593 1

Sort (cost=1,897.72..1,949.46 rows=20,698 width=9) (actual time=32.607..38.498 rows=20,593 loops=1)

  • Sort Key: tf.loan_application_id
  • Sort Method: quicksort Memory: 1734kB
248. 26.368 26.368 ↑ 1.0 20,594 1

CTE Scan on tf_flag tf (cost=0.00..413.96 rows=20,698 width=9) (actual time=0.022..26.368 rows=20,594 loops=1)

249. 22.600 98.005 ↑ 1.0 18,093 1

Sort (cost=3,362.87..3,408.18 rows=18,124 width=64) (actual time=89.584..98.005 rows=18,093 loops=1)

  • Sort Key: pri.loan_application_id
  • Sort Method: quicksort Memory: 2537kB
250. 42.011 75.405 ↑ 1.0 18,094 1

Hash Right Join (cost=544.19..2,080.99 rows=18,124 width=64) (actual time=16.157..75.405 rows=18,094 loops=1)

  • Hash Cond: ((addr.loan_application_id = pri.loan_application_id) AND (addr.address_id = pri.address_id))
  • Join Filter: (NOT pri.is_removed)
251. 17.314 17.314 ↓ 1.0 77,915 1

Seq Scan on addresses addr (cost=0.00..966.54 rows=76,034 width=66) (actual time=0.018..17.314 rows=77,915 loops=1)

252. 7.173 16.080 ↑ 1.0 18,094 1

Hash (cost=272.33..272.33 rows=18,124 width=23) (actual time=16.080..16.080 rows=18,094 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1206kB
253. 8.907 8.907 ↑ 1.0 18,094 1

Seq Scan on property_information pri (cost=0.00..272.33 rows=18,124 width=23) (actual time=0.022..8.907 rows=18,094 loops=1)

  • Filter: (NOT is_removed)
  • Rows Removed by Filter: 9
254. 0.000 0.000 ↑ 1.0 3 11,580

Materialize (cost=0.00..0.24 rows=3 width=40) (actual time=0.000..0.000 rows=3 loops=11,580)

255. 0.028 0.028 ↑ 1.0 3 1

Seq Scan on warehouse_metadata wm (cost=0.00..0.23 rows=3 width=40) (actual time=0.026..0.028 rows=3 loops=1)

256. 0.009 19.041 ↓ 11.0 11 1

Materialize (cost=0.03..0.04 rows=1 width=8) (actual time=19.031..19.041 rows=11 loops=1)

257. 0.031 19.032 ↓ 11.0 11 1

Sort (cost=0.03..0.04 rows=1 width=8) (actual time=19.026..19.032 rows=11 loops=1)

  • Sort Key: ex_addr.id
  • Sort Method: quicksort Memory: 25kB
258. 19.001 19.001 ↓ 11.0 11 1

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

259. 1.501 13.440 ↓ 1.1 3,056 1

Materialize (cost=0.28..69.01 rows=2,786 width=19) (actual time=0.021..13.440 rows=3,056 loops=1)

260. 11.939 11.939 ↓ 1.1 3,056 1

Index Scan using wire_information_loan_application_idx on wire_information wi (cost=0.28..62.04 rows=2,786 width=19) (actual time=0.012..11.939 rows=3,056 loops=1)

  • Filter: (NOT is_removed)
  • Rows Removed by Filter: 157
261. 9.134 668.703 ↓ 6,656.5 13,313 1

Sort (cost=8.47..8.47 rows=2 width=524) (actual time=664.301..668.703 rows=13,313 loops=1)

  • Sort Key: ltv.loan_application_id
  • Sort Method: quicksort Memory: 1009kB
262. 659.569 659.569 ↓ 6,657.0 13,314 1

CTE Scan on ltv_dti ltv (cost=0.00..8.46 rows=2 width=524) (actual time=547.343..659.569 rows=13,314 loops=1)

  • Filter: (tolerance_property_id = 4)
263. 19.728 226.793 ↓ 1.0 20,603 1

Sort (cost=1,887.24..1,938.72 rows=20,592 width=200) (actual time=219.801..226.793 rows=20,603 loops=1)

  • Sort Key: t.loan_id
  • Sort Method: quicksort Memory: 2286kB
264. 207.065 207.065 ↓ 1.0 20,604 1

CTE Scan on team t (cost=0.00..411.84 rows=20,592 width=200) (actual time=2.106..207.065 rows=20,604 loops=1)

265. 4.250 8.793 ↓ 1.0 3,455 1

Sort (cost=295.42..303.96 rows=3,416 width=83) (actual time=6.723..8.793 rows=3,455 loops=1)

  • Sort Key: i.loan_application_id
  • Sort Method: quicksort Memory: 543kB
266. 1.594 4.543 ↓ 1.0 3,455 1

Hash Left Join (cost=0.38..94.93 rows=3,416 width=83) (actual time=0.062..4.543 rows=3,455 loops=1)

  • Hash Cond: (i.investor_metadata_id = imd.id)
267. 2.925 2.925 ↓ 1.0 3,455 1

Seq Scan on investors i (cost=0.00..58.96 rows=3,416 width=59) (actual time=0.023..2.925 rows=3,455 loops=1)

268. 0.006 0.024 ↓ 1.4 11 1

Hash (cost=0.28..0.28 rows=8 width=40) (actual time=0.024..0.024 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
269. 0.018 0.018 ↓ 1.4 11 1

Seq Scan on investor_metadata imd (cost=0.00..0.28 rows=8 width=40) (actual time=0.014..0.018 rows=11 loops=1)

270. 7.004 45.927 ↓ 181.7 9,992 1

Sort (cost=2.69..2.83 rows=55 width=40) (actual time=42.501..45.927 rows=9,992 loops=1)

  • Sort Key: mi.loan_application_id
  • Sort Method: quicksort Memory: 920kB
271. 38.923 38.923 ↓ 181.7 9,992 1

CTE Scan on mi (cost=0.00..1.10 rows=55 width=40) (actual time=10.785..38.923 rows=9,992 loops=1)

272. 2.341 17.523 ↓ 198.9 2,784 1

Sort (cost=63.24..63.28 rows=14 width=25) (actual time=16.331..17.523 rows=2,784 loops=1)

  • Sort Key: faa.loan_application_id
  • Sort Method: quicksort Memory: 298kB
273. 15.182 15.182 ↓ 198.9 2,784 1

CTE Scan on all_appraisals faa (cost=0.00..62.98 rows=14 width=25) (actual time=8.706..15.182 rows=2,784 loops=1)

  • Filter: (asc_app_rn = 1)
  • Rows Removed by Filter: 17
274. 2.008 3.084 ↓ 198.9 2,784 1

Sort (cost=63.24..63.28 rows=14 width=21) (actual time=1.981..3.084 rows=2,784 loops=1)

  • Sort Key: laa.loan_application_id
  • Sort Method: quicksort Memory: 288kB
275. 1.076 1.076 ↓ 198.9 2,784 1

CTE Scan on all_appraisals laa (cost=0.00..62.98 rows=14 width=21) (actual time=0.006..1.076 rows=2,784 loops=1)

  • Filter: (desc_app_rn = 1)
  • Rows Removed by Filter: 17
276. 7.523 202.062 ↑ 1.0 1,519 1

Sort (cost=112.89..116.76 rows=1,547 width=376) (actual time=197.958..202.062 rows=1,519 loops=1)

  • Sort Key: qcd.loan_application_id
  • Sort Method: quicksort Memory: 4326kB
277. 194.539 194.539 ↑ 1.0 1,519 1

CTE Scan on qc_details qcd (cost=0.00..30.94 rows=1,547 width=376) (actual time=187.533..194.539 rows=1,519 loops=1)

278. 7.557 56.595 ↓ 1.0 11,092 1

Sort (cost=965.94..993.64 rows=11,080 width=12) (actual time=53.098..56.595 rows=11,092 loops=1)

  • Sort Key: cs.loan_application_id
  • Sort Method: quicksort Memory: 904kB
279. 49.038 49.038 ↓ 1.0 11,093 1

CTE Scan on fico_scores cs (cost=0.00..221.60 rows=11,080 width=12) (actual time=0.057..49.038 rows=11,093 loops=1)

280. 0.031 0.172 ↑ 11.8 17 1

Sort (cost=11.64..12.14 rows=200 width=40) (actual time=0.160..0.172 rows=17 loops=1)

  • Sort Key: ff.loan_application_id
  • Sort Method: quicksort Memory: 25kB
281. 0.141 0.141 ↑ 11.8 17 1

CTE Scan on form_free ff (cost=0.00..4.00 rows=200 width=40) (actual time=0.029..0.141 rows=17 loops=1)

282. 2.693 52.742 ↓ 176.8 3,890 1

Sort (cost=0.93..0.99 rows=22 width=16) (actual time=51.269..52.742 rows=3,890 loops=1)

  • Sort Key: last_pro.loan_application_id
  • Sort Method: quicksort Memory: 279kB
283. 50.049 50.049 ↓ 176.8 3,890 1

CTE Scan on last_pro (cost=0.00..0.44 rows=22 width=16) (actual time=37.275..50.049 rows=3,890 loops=1)

284. 0.150 114.881 ↓ 474.0 474 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=114.552..114.881 rows=474 loops=1)

285. 0.389 114.731 ↓ 474.0 474 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=114.550..114.731 rows=474 loops=1)

  • Sort Key: ms_app1.loan_application_id
  • Sort Method: quicksort Memory: 47kB
286. 114.342 114.342 ↓ 474.0 474 1

CTE Scan on misc_section ms_app1 (cost=0.00..1,220.10 rows=1 width=12) (actual time=72.437..114.342 rows=474 loops=1)

  • Filter: ((section_name = 'ProcessingServicesOrderedDatesSection'::text) AND (field_name = 'serviceAppraisal1Ordered'::text))
  • Rows Removed by Filter: 48369
287. 0.633 15.195 ↓ 2,424.0 2,424 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=13.589..15.195 rows=2,424 loops=1)

288. 1.533 14.562 ↓ 2,424.0 2,424 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=13.584..14.562 rows=2,424 loops=1)

  • Sort Key: ms_pr_ti.loan_application_id
  • Sort Method: quicksort Memory: 210kB
289. 13.029 13.029 ↓ 2,424.0 2,424 1

CTE Scan on misc_section ms_pr_ti (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.032..13.029 rows=2,424 loops=1)

  • Filter: ((section_name = 'ProcessingServicesOrderedDatesSection'::text) AND (field_name = 'serviceTitleDocumentsRequested'::text))
  • Rows Removed by Filter: 46419
290. 0.483 12.978 ↓ 2,015.0 2,015 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.647..12.978 rows=2,015 loops=1)

291. 1.367 12.495 ↓ 2,015.0 2,015 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.643..12.495 rows=2,015 loops=1)

  • Sort Key: ms_icr.loan_application_id
  • Sort Method: quicksort Memory: 143kB
292. 11.128 11.128 ↓ 2,015.0 2,015 1

CTE Scan on misc_section ms_icr (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.027..11.128 rows=2,015 loops=1)

  • Filter: ((section_name = 'ClosingMiscFactsDatesSection'::text) AND (field_name = 'closingInitialCloserReview'::text))
  • Rows Removed by Filter: 46828
293. 0.490 12.894 ↓ 2,019.0 2,019 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.629..12.894 rows=2,019 loops=1)

294. 1.296 12.404 ↓ 2,019.0 2,019 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.625..12.404 rows=2,019 loops=1)

  • Sort Key: ms_pre.loan_application_id
  • Sort Method: quicksort Memory: 143kB
295. 11.108 11.108 ↓ 2,019.0 2,019 1

CTE Scan on misc_section ms_pre (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.046..11.108 rows=2,019 loops=1)

  • Filter: ((section_name = 'ClosingMiscFactsDatesSection'::text) AND (field_name = 'closingPrelimCDSent'::text))
  • Rows Removed by Filter: 46824
296. 3.659 163.980 ↑ 1.4 2,376 1

Sort (cost=266.14..274.61 rows=3,385 width=224) (actual time=162.481..163.980 rows=2,376 loops=1)

  • Sort Key: qbi.id
  • Sort Method: quicksort Memory: 456kB
297. 160.321 160.321 ↑ 1.4 2,376 1

CTE Scan on quality_bypass_info qbi (cost=0.00..67.70 rows=3,385 width=224) (actual time=153.013..160.321 rows=2,376 loops=1)

298. 0.491 13.236 ↓ 1,973.0 1,973 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.990..13.236 rows=1,973 loops=1)

299. 1.404 12.745 ↓ 1,973.0 1,973 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.985..12.745 rows=1,973 loops=1)

  • Sort Key: ms_cd.loan_application_id
  • Sort Method: quicksort Memory: 141kB
300. 11.341 11.341 ↓ 1,973.0 1,973 1

CTE Scan on misc_section ms_cd (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.030..11.341 rows=1,973 loops=1)

  • Filter: ((section_name = 'ClosingMiscFactsDatesSection'::text) AND (field_name = 'closingCDBalanced'::text))
  • Rows Removed by Filter: 46870
301. 0.464 13.528 ↓ 1,992.0 1,992 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=12.333..13.528 rows=1,992 loops=1)

302. 1.397 13.064 ↓ 1,992.0 1,992 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=12.331..13.064 rows=1,992 loops=1)

  • Sort Key: ms_pc_reg.loan_application_id
  • Sort Method: quicksort Memory: 142kB
303. 11.667 11.667 ↓ 1,992.0 1,992 1

CTE Scan on misc_section ms_pc_reg (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.021..11.667 rows=1,992 loops=1)

  • Filter: ((section_name = 'FundingPostClosingDatesSection'::text) AND (field_name = 'fundingPostClosingRegisteredMERS'::text))
  • Rows Removed by Filter: 46851
304. 0.328 19.568 ↓ 1,534.0 1,534 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=18.676..19.568 rows=1,534 loops=1)

305. 1.158 19.240 ↓ 1,534.0 1,534 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=18.670..19.240 rows=1,534 loops=1)

  • Sort Key: ms_pc_tr.loan_application_id
  • Sort Method: quicksort Memory: 120kB
306. 18.082 18.082 ↓ 1,534.0 1,534 1

CTE Scan on misc_section ms_pc_tr (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.019..18.082 rows=1,534 loops=1)

  • Filter: ((section_name = 'FundingPostClosingDatesSection'::text) AND (field_name = 'fundingPostClosingTransferredMERS'::text))
  • Rows Removed by Filter: 47309
307. 0.572 15.016 ↓ 2,005.0 2,005 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=13.778..15.016 rows=2,005 loops=1)

308. 1.415 14.444 ↓ 2,005.0 2,005 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=13.767..14.444 rows=2,005 loops=1)

  • Sort Key: ms_pc_sp.loan_application_id
  • Sort Method: quicksort Memory: 142kB
309. 13.029 13.029 ↓ 2,005.0 2,005 1

CTE Scan on misc_section ms_pc_sp (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.029..13.029 rows=2,005 loops=1)

  • Filter: ((section_name = 'FundingPostClosingDatesSection'::text) AND (field_name = 'fundingPostClosingSettlementPackageReceived'::text))
  • Rows Removed by Filter: 46838
310. 0.494 13.942 ↓ 1,947.0 1,947 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=12.718..13.942 rows=1,947 loops=1)

311. 1.434 13.448 ↓ 1,947.0 1,947 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=12.712..13.448 rows=1,947 loops=1)

  • Sort Key: ms_pc_co.loan_application_id
  • Sort Method: quicksort Memory: 140kB
312. 12.014 12.014 ↓ 1,947.0 1,947 1

CTE Scan on misc_section ms_pc_co (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.046..12.014 rows=1,947 loops=1)

  • Filter: ((section_name = 'FundingPostClosingDatesSection'::text) AND (field_name = 'fundingPostClosingCollateralToWarehouse'::text))
  • Rows Removed by Filter: 46896
313. 0.491 13.627 ↓ 1,822.0 1,822 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=12.468..13.627 rows=1,822 loops=1)

314. 1.343 13.136 ↓ 1,822.0 1,822 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=12.462..13.136 rows=1,822 loops=1)

  • Sort Key: ms_pc_coi.loan_application_id
  • Sort Method: quicksort Memory: 134kB
315. 11.793 11.793 ↓ 1,822.0 1,822 1

CTE Scan on misc_section ms_pc_coi (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.022..11.793 rows=1,822 loops=1)

  • Filter: ((section_name = 'FundingPostClosingDatesSection'::text) AND (field_name = 'fundingPostClosingCollateralToInvestor'::text))
  • Rows Removed by Filter: 47021
316. 0.196 12.293 ↓ 722.0 722 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.677..12.293 rows=722 loops=1)

317. 0.697 12.097 ↓ 722.0 722 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.672..12.097 rows=722 loops=1)

  • Sort Key: ms_pc_s.loan_application_id
  • Sort Method: quicksort Memory: 58kB
318. 11.400 11.400 ↓ 722.0 722 1

CTE Scan on misc_section ms_pc_s (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.043..11.400 rows=722 loops=1)

  • Filter: ((section_name = 'FundingPostClosingDatesSection'::text) AND (field_name = 'fundingPostClosingSuspenseDate'::text))
  • Rows Removed by Filter: 48121
319. 0.143 12.145 ↓ 574.0 574 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.797..12.145 rows=574 loops=1)

320. 0.467 12.002 ↓ 574.0 574 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.789..12.002 rows=574 loops=1)

  • Sort Key: ms_pc_sc.loan_application_id
  • Sort Method: quicksort Memory: 51kB
321. 11.535 11.535 ↓ 574.0 574 1

CTE Scan on misc_section ms_pc_sc (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.042..11.535 rows=574 loops=1)

  • Filter: ((section_name = 'FundingPostClosingDatesSection'::text) AND (field_name = 'fundingPostClosingSuspenseClearedDate'::text))
  • Rows Removed by Filter: 48269
322. 0.450 13.815 ↓ 1,835.0 1,835 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=12.715..13.815 rows=1,835 loops=1)

323. 1.372 13.365 ↓ 1,835.0 1,835 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=12.705..13.365 rows=1,835 loops=1)

  • Sort Key: ms_pc_stm.loan_application_id
  • Sort Method: quicksort Memory: 135kB
324. 11.993 11.993 ↓ 1,835.0 1,835 1

CTE Scan on misc_section ms_pc_stm (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.045..11.993 rows=1,835 loops=1)

  • Filter: ((section_name = 'FundingPostClosingDatesSection'::text) AND (field_name = 'fundingPostClosingNoticeServicingTransferMailed'::text))
  • Rows Removed by Filter: 47008
325. 0.034 11.726 ↓ 91.0 91 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.660..11.726 rows=91 loops=1)

326. 0.129 11.692 ↓ 91.0 91 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.651..11.692 rows=91 loops=1)

  • Sort Key: ms_pc_rmr.loan_application_id
  • Sort Method: quicksort Memory: 29kB
327. 11.563 11.563 ↓ 91.0 91 1

CTE Scan on misc_section ms_pc_rmr (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.069..11.563 rows=91 loops=1)

  • Filter: ((section_name = 'FundingPostClosingDatesSection'::text) AND (field_name = 'fundingPostClosingRecordedMortgageReceived'::text))
  • Rows Removed by Filter: 48752
328. 0.034 11.700 ↓ 89.0 89 1

Materialize (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.638..11.700 rows=89 loops=1)

329. 0.142 11.666 ↓ 89.0 89 1

Sort (cost=1,220.11..1,220.12 rows=1 width=12) (actual time=11.626..11.666 rows=89 loops=1)

  • Sort Key: ms_pc_tpr.loan_application_id
  • Sort Method: quicksort Memory: 29kB
330. 11.524 11.524 ↓ 89.0 89 1

CTE Scan on misc_section ms_pc_tpr (cost=0.00..1,220.10 rows=1 width=12) (actual time=0.061..11.524 rows=89 loops=1)

  • Filter: ((section_name = 'FundingPostClosingDatesSection'::text) AND (field_name = 'fundingPostClosingFinalTitlePolicyReceived'::text))
  • Rows Removed by Filter: 48754
331. 1.072 307.422 ↓ 14.1 1,483 1

Sort (cost=477.44..477.70 rows=105 width=16) (actual time=306.880..307.422 rows=1,483 loops=1)

  • Sort Key: uw_pre.loan_application_id
  • Sort Method: quicksort Memory: 118kB
332. 306.350 306.350 ↓ 14.1 1,483 1

CTE Scan on max_transition_events uw_pre (cost=0.00..473.92 rows=105 width=16) (actual time=245.531..306.350 rows=1,483 loops=1)

  • Filter: (to_status = 3)
  • Rows Removed by Filter: 83233
333. 0.745 15.733 ↓ 11.7 1,226 1

Sort (cost=477.44..477.70 rows=105 width=16) (actual time=15.336..15.733 rows=1,226 loops=1)

  • Sort Key: uw_pred.loan_application_id
  • Sort Method: quicksort Memory: 106kB
334. 14.988 14.988 ↓ 11.7 1,226 1

CTE Scan on max_transition_events uw_pred (cost=0.00..473.92 rows=105 width=16) (actual time=0.016..14.988 rows=1,226 loops=1)

  • Filter: (to_status = 4)
  • Rows Removed by Filter: 83490
335. 1.753 17.190 ↓ 28.0 2,945 1

Sort (cost=477.44..477.70 rows=105 width=16) (actual time=16.105..17.190 rows=2,945 loops=1)

  • Sort Key: ca.loan_application_id
  • Sort Method: quicksort Memory: 235kB
336. 15.437 15.437 ↓ 28.0 2,945 1

CTE Scan on max_transition_events ca (cost=0.00..473.92 rows=105 width=16) (actual time=0.046..15.437 rows=2,945 loops=1)

  • Filter: (to_status = 7)
  • Rows Removed by Filter: 81771
337. 72.705 92.382 ↓ 4.0 84,710 1

Sort (cost=1,933.84..1,986.50 rows=21,063 width=20) (actual time=74.250..92.382 rows=84,710 loops=1)

  • Sort Key: curr.loan_application_id, curr.to_status
  • Sort Method: quicksort Memory: 9691kB
338. 19.677 19.677 ↓ 4.0 84,716 1

CTE Scan on max_transition_events curr (cost=0.00..421.26 rows=21,063 width=20) (actual time=0.005..19.677 rows=84,716 loops=1)

339. 1.231 16.415 ↓ 21.7 2,276 1

Sort (cost=477.44..477.70 rows=105 width=16) (actual time=15.748..16.415 rows=2,276 loops=1)

  • Sort Key: uw_f.loan_application_id
  • Sort Method: quicksort Memory: 203kB
340. 15.184 15.184 ↓ 21.7 2,276 1

CTE Scan on max_transition_events uw_f (cost=0.00..473.92 rows=105 width=16) (actual time=0.030..15.184 rows=2,276 loops=1)

  • Filter: (to_status = 8)
  • Rows Removed by Filter: 82440
341. 0.865 15.745 ↓ 15.4 1,617 1

Sort (cost=477.44..477.70 rows=105 width=16) (actual time=15.294..15.745 rows=1,617 loops=1)

  • Sort Key: dis.loan_application_id
  • Sort Method: quicksort Memory: 124kB
342. 14.880 14.880 ↓ 15.4 1,617 1

CTE Scan on max_transition_events dis (cost=0.00..473.92 rows=105 width=16) (actual time=0.019..14.880 rows=1,617 loops=1)

  • Filter: (to_status = 39)
  • Rows Removed by Filter: 83099
343. 1.052 16.103 ↓ 17.9 1,876 1

Sort (cost=477.44..477.70 rows=105 width=16) (actual time=15.507..16.103 rows=1,876 loops=1)

  • Sort Key: ctc_docs.loan_application_id
  • Sort Method: quicksort Memory: 136kB
344. 15.051 15.051 ↓ 17.9 1,876 1

CTE Scan on max_transition_events ctc_docs (cost=0.00..473.92 rows=105 width=16) (actual time=0.501..15.051 rows=1,876 loops=1)

  • Filter: (to_status = 50)
  • Rows Removed by Filter: 82840
345. 1.085 16.077 ↓ 20.9 2,198 1

Sort (cost=477.44..477.70 rows=105 width=16) (actual time=15.563..16.077 rows=2,198 loops=1)

  • Sort Key: cl.loan_application_id
  • Sort Method: quicksort Memory: 200kB
346. 14.992 14.992 ↓ 20.9 2,198 1

CTE Scan on max_transition_events cl (cost=0.00..473.92 rows=105 width=16) (actual time=0.027..14.992 rows=2,198 loops=1)

  • Filter: (to_status = 10)
  • Rows Removed by Filter: 82518
347. 0.429 15.406 ↓ 6.7 701 1

Sort (cost=477.44..477.70 rows=105 width=16) (actual time=15.218..15.406 rows=701 loops=1)

  • Sort Key: reg.loan_application_id
  • Sort Method: quicksort Memory: 57kB
348. 14.977 14.977 ↓ 6.7 701 1

CTE Scan on max_transition_events reg (cost=0.00..473.92 rows=105 width=16) (actual time=0.465..14.977 rows=701 loops=1)

  • Filter: (to_status = 43)
  • Rows Removed by Filter: 84015
349. 0.880 18.317 ↓ 13.7 1,436 1

Sort (cost=477.44..477.70 rows=105 width=16) (actual time=17.859..18.317 rows=1,436 loops=1)

  • Sort Key: min_qr.loan_application_id
  • Sort Method: quicksort Memory: 116kB
350. 17.437 17.437 ↓ 13.7 1,436 1

CTE Scan on min_transition_events min_qr (cost=0.00..473.92 rows=105 width=16) (actual time=0.032..17.437 rows=1,436 loops=1)

  • Filter: (to_status = 32)
  • Rows Removed by Filter: 83280
351. 0.818 16.059 ↓ 14.4 1,516 1

Sort (cost=477.44..477.70 rows=105 width=16) (actual time=15.630..16.059 rows=1,516 loops=1)

  • Sort Key: min_qr2.loan_application_id
  • Sort Method: quicksort Memory: 120kB
352. 15.241 15.241 ↓ 14.4 1,516 1

CTE Scan on min_transition_events min_qr2 (cost=0.00..473.92 rows=105 width=16) (actual time=0.031..15.241 rows=1,516 loops=1)

  • Filter: (to_status = 35)
  • Rows Removed by Filter: 83200
353. 1.209 16.294 ↓ 21.7 2,276 1

Sort (cost=477.44..477.70 rows=105 width=16) (actual time=15.645..16.294 rows=2,276 loops=1)

  • Sort Key: uw_f_min.loan_application_id
  • Sort Method: quicksort Memory: 203kB
354. 15.085 15.085 ↓ 21.7 2,276 1

CTE Scan on min_transition_events uw_f_min (cost=0.00..473.92 rows=105 width=16) (actual time=0.028..15.085 rows=2,276 loops=1)

  • Filter: (to_status = 8)
  • Rows Removed by Filter: 82440
355. 1.058 16.251 ↓ 18.2 1,909 1

Sort (cost=477.44..477.70 rows=105 width=16) (actual time=15.647..16.251 rows=1,909 loops=1)

  • Sort Key: ctc_docs_h.loan_application_id
  • Sort Method: quicksort Memory: 138kB
356. 15.193 15.193 ↓ 18.2 1,909 1

CTE Scan on min_transition_events ctc_docs_h (cost=0.00..473.92 rows=105 width=16) (actual time=0.601..15.193 rows=1,909 loops=1)

  • Filter: (to_status = 49)
  • Rows Removed by Filter: 82807
Planning time : 236.331 ms
Execution time : 3,831.369 ms