explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xpip : Optimization for: plan #OE73

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.429 2,197.212 ↑ 1.6 368 1

Unique (cost=84,836.09..84,881.74 rows=589 width=2,031) (actual time=2,196.669..2,197.212 rows=368 loops=1)

2.          

CTE transition_events

3. 7.875 39.649 ↑ 1.1 5,579 1

Finalize GroupAggregate (cost=1,639.54..1,752.26 rows=5,893 width=20) (actual time=29.795..39.649 rows=5,579 loops=1)

  • Group Key: loan_status_transition_events.loan_application_id, loan_status_transition_events.to_status
4. 5.590 31.774 ↓ 1.5 5,840 1

Sort (cost=1,639.54..1,649.30 rows=3,906 width=20) (actual time=29.718..31.774 rows=5,840 loops=1)

  • Sort Key: loan_status_transition_events.loan_application_id, loan_status_transition_events.to_status
  • Sort Method: quicksort Memory: 649kB
5. 8.862 26.184 ↓ 1.5 5,840 1

Gather (cost=976.85..1,406.51 rows=3,906 width=20) (actual time=22.774..26.184 rows=5,840 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
6. 3.151 17.322 ↑ 1.3 2,920 2 / 2

Partial HashAggregate (cost=966.85..1,005.91 rows=3,906 width=20) (actual time=16.300..17.322 rows=2,920 loops=2)

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

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

CTE team_mbrs

9. 0.070 0.070 ↑ 1.5 153 1

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

10.          

CTE peoples

11. 0.073 0.073 ↓ 1.0 121 1

Index Scan using persons_pkey on persons (cost=0.14..2.40 rows=117 width=313) (actual time=0.012..0.073 rows=121 loops=1)

12.          

CTE team

13. 21.543 220.700 ↑ 1.0 22,012 1

Sort (cost=8,833.52..8,888.55 rows=22,013 width=232) (actual time=215.411..220.700 rows=22,012 loops=1)

  • Sort Key: la_1.id
  • Sort Method: quicksort Memory: 2,590kB
14. 37.556 199.157 ↑ 1.0 22,012 1

Hash Left Join (cost=90.49..7,245.71 rows=22,013 width=232) (actual time=2.419..199.157 rows=22,012 loops=1)

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

Hash Left Join (cost=86.69..6,260.13 rows=22,013 width=400) (actual time=2.285..161.508 rows=22,012 loops=1)

  • Hash Cond: (lat.customer_care_member_id = tm_ma.id)
  • Join Filter: (NOT lat.is_removed)
16. 15.077 149.650 ↑ 1.0 22,012 1

Hash Left Join (cost=79.48..5,424.74 rows=22,013 width=401) (actual time=2.168..149.650 rows=22,012 loops=1)

  • Hash Cond: (lat.received_member_id = tm_re.id)
  • Join Filter: (NOT lat.is_removed)
17. 10.088 134.295 ↑ 1.0 22,012 1

Hash Left Join (cost=66.33..4,588.65 rows=22,013 width=345) (actual time=1.871..134.295 rows=22,012 loops=1)

  • Hash Cond: (lat.closer_member_id = tm_cl.id)
  • Join Filter: (NOT lat.is_removed)
18. 9.862 123.930 ↑ 1.0 22,012 1

Hash Left Join (cost=53.17..3,887.98 rows=22,013 width=289) (actual time=1.573..123.930 rows=22,012 loops=1)

  • Hash Cond: (lat.secondary_member_id = tm_s.id)
  • Join Filter: (NOT lat.is_removed)
19. 9.058 113.768 ↑ 1.0 22,012 1

Hash Left Join (cost=40.02..3,194.47 rows=22,013 width=233) (actual time=1.250..113.768 rows=22,012 loops=1)

  • Hash Cond: (lat.processor_member_id = tm_pr.id)
  • Join Filter: (NOT lat.is_removed)
20. 10.036 104.417 ↑ 1.0 22,012 1

Hash Left Join (cost=26.88..2,475.13 rows=22,013 width=177) (actual time=0.941..104.417 rows=22,012 loops=1)

  • Hash Cond: (lat.underwriter_member_id = tm_u.id)
  • Join Filter: (NOT lat.is_removed)
21. 9.659 94.111 ↑ 1.0 22,012 1

Hash Left Join (cost=13.72..1,752.52 rows=22,013 width=121) (actual time=0.637..94.111 rows=22,012 loops=1)

  • Hash Cond: (lat.quality_review_id = tm_q.id)
  • Join Filter: (NOT lat.is_removed)
22. 29.712 83.894 ↑ 1.0 22,012 1

Merge Left Join (cost=0.57..1,062.66 rows=22,013 width=65) (actual time=0.057..83.894 rows=22,012 loops=1)

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

  • Heap Fetches: 6,330
24. 39.242 39.242 ↑ 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=65) (actual time=0.013..39.242 rows=22,004 loops=1)

25. 0.059 0.558 ↑ 1.5 153 1

Hash (cost=10.38..10.38 rows=222 width=72) (actual time=0.558..0.558 rows=153 loops=1)

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

Hash Left Join (cost=3.80..10.38 rows=222 width=72) (actual time=0.288..0.499 rows=153 loops=1)

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

CTE Scan on team_mbrs tm_q (cost=0.00..4.44 rows=222 width=16) (actual time=0.035..0.162 rows=153 loops=1)

28. 0.046 0.218 ↓ 1.0 121 1

Hash (cost=2.34..2.34 rows=117 width=72) (actual time=0.218..0.218 rows=121 loops=1)

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

CTE Scan on peoples p_q (cost=0.00..2.34 rows=117 width=72) (actual time=0.015..0.172 rows=121 loops=1)

30. 0.061 0.270 ↑ 1.5 153 1

Hash (cost=10.38..10.38 rows=222 width=72) (actual time=0.270..0.270 rows=153 loops=1)

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

Hash Left Join (cost=3.80..10.38 rows=222 width=72) (actual time=0.112..0.209 rows=153 loops=1)

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

CTE Scan on team_mbrs tm_u (cost=0.00..4.44 rows=222 width=16) (actual time=0.001..0.028 rows=153 loops=1)

33. 0.047 0.089 ↓ 1.0 121 1

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

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

CTE Scan on peoples p_u (cost=0.00..2.34 rows=117 width=72) (actual time=0.001..0.042 rows=121 loops=1)

35. 0.052 0.293 ↑ 1.5 153 1

Hash (cost=10.38..10.38 rows=222 width=72) (actual time=0.292..0.293 rows=153 loops=1)

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

Hash Left Join (cost=3.80..10.38 rows=222 width=72) (actual time=0.121..0.241 rows=153 loops=1)

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

CTE Scan on team_mbrs tm_pr (cost=0.00..4.44 rows=222 width=16) (actual time=0.001..0.027 rows=153 loops=1)

38. 0.045 0.084 ↓ 1.0 121 1

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

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

CTE Scan on peoples p_pr (cost=0.00..2.34 rows=117 width=72) (actual time=0.002..0.039 rows=121 loops=1)

40. 0.060 0.300 ↑ 1.5 153 1

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

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

Hash Left Join (cost=3.80..10.38 rows=222 width=72) (actual time=0.119..0.240 rows=153 loops=1)

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

CTE Scan on team_mbrs tm_s (cost=0.00..4.44 rows=222 width=16) (actual time=0.001..0.023 rows=153 loops=1)

43. 0.050 0.090 ↓ 1.0 121 1

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

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

CTE Scan on peoples p_s (cost=0.00..2.34 rows=117 width=72) (actual time=0.002..0.040 rows=121 loops=1)

45. 0.072 0.277 ↑ 1.5 153 1

Hash (cost=10.38..10.38 rows=222 width=72) (actual time=0.277..0.277 rows=153 loops=1)

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

Hash Left Join (cost=3.80..10.38 rows=222 width=72) (actual time=0.104..0.205 rows=153 loops=1)

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

CTE Scan on team_mbrs tm_cl (cost=0.00..4.44 rows=222 width=16) (actual time=0.001..0.029 rows=153 loops=1)

48. 0.044 0.082 ↓ 1.0 121 1

Hash (cost=2.34..2.34 rows=117 width=72) (actual time=0.082..0.082 rows=121 loops=1)

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

CTE Scan on peoples p_cl (cost=0.00..2.34 rows=117 width=72) (actual time=0.001..0.038 rows=121 loops=1)

50. 0.061 0.278 ↑ 1.5 153 1

Hash (cost=10.38..10.38 rows=222 width=72) (actual time=0.278..0.278 rows=153 loops=1)

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

Hash Left Join (cost=3.80..10.38 rows=222 width=72) (actual time=0.107..0.217 rows=153 loops=1)

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

CTE Scan on team_mbrs tm_re (cost=0.00..4.44 rows=222 width=16) (actual time=0.001..0.023 rows=153 loops=1)

53. 0.046 0.086 ↓ 1.0 121 1

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

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

CTE Scan on peoples p_re (cost=0.00..2.34 rows=117 width=72) (actual time=0.001..0.040 rows=121 loops=1)

55. 0.050 0.091 ↑ 1.5 153 1

Hash (cost=4.44..4.44 rows=222 width=16) (actual time=0.091..0.091 rows=153 loops=1)

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

CTE Scan on team_mbrs tm_ma (cost=0.00..4.44 rows=222 width=16) (actual time=0.001..0.041 rows=153 loops=1)

57. 0.053 0.093 ↓ 1.0 121 1

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

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

CTE Scan on peoples p_ma (cost=0.00..2.34 rows=117 width=72) (actual time=0.001..0.040 rows=121 loops=1)

59.          

CTE ltv_dti

60. 57.550 1,656.978 ↓ 35.8 42,999 1

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

  • Filter: (a.rn = 1)
  • Rows Removed by Filter: 200,004
61. 208.890 1,599.428 ↓ 1.0 243,003 1

WindowAgg (cost=59,631.23..65,042.15 rows=240,485 width=36) (actual time=1,309.582..1,599.428 rows=243,003 loops=1)

62. 411.824 1,390.538 ↓ 1.0 243,003 1

Sort (cost=59,631.23..60,232.45 rows=240,485 width=28) (actual time=1,309.569..1,390.538 rows=243,003 loops=1)

  • Sort Key: loan_tolerance_snapshot_items.loan_application_id, loan_tolerance_snapshot_items.tolerance_property_id, loan_tolerance_snapshot_items.last_updated_date DESC
  • Sort Method: external merge Disk: 9,440kB
63. 978.714 978.714 ↓ 1.0 243,003 1

Seq Scan on loan_tolerance_snapshot_items (cost=0.00..38,137.18 rows=240,485 width=28) (actual time=0.028..978.714 rows=243,003 loops=1)

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

CTE mi

65. 15.681 29.810 ↓ 1.0 9,621 1

WindowAgg (cost=793.60..1,129.60 rows=9,600 width=56) (actual time=10.648..29.810 rows=9,621 loops=1)

66. 8.663 14.129 ↓ 1.0 9,621 1

Sort (cost=793.60..817.60 rows=9,600 width=20) (actual time=10.618..14.129 rows=9,621 loops=1)

  • Sort Key: mortgage_insurances.loan_application_id, mortgage_insurances.last_updated_date DESC
  • Sort Method: quicksort Memory: 975kB
67. 5.466 5.466 ↓ 1.0 9,621 1

Seq Scan on mortgage_insurances (cost=0.00..158.62 rows=9,600 width=20) (actual time=0.036..5.466 rows=9,621 loops=1)

  • Filter: (NOT is_removed)
  • Rows Removed by Filter: 1,862
68.          

CTE deal_id

69. 64.219 95.214 ↓ 1.0 21,992 1

GroupAggregate (cost=0.29..1,758.93 rows=21,958 width=16) (actual time=0.102..95.214 rows=21,992 loops=1)

  • Group Key: affiliated_business_details.loan_application_id
70. 30.995 30.995 ↓ 1.0 43,983 1

Index Scan using affiliated_business_details_loan_and_business_idx on affiliated_business_details (cost=0.29..770.83 rows=43,915 width=21) (actual time=0.060..30.995 rows=43,983 loops=1)

71. 1.538 2,196.783 ↑ 1.6 368 1

Sort (cost=3,253.33..3,254.80 rows=589 width=2,031) (actual time=2,196.667..2,196.783 rows=368 loops=1)

  • Sort Key: addr.state_code, la.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), lad.application_received_local_date, (CASE WHEN (lad.rate_lock_cancelled_local_date IS NULL) THEN lad.rate_lock_local_date ELSE NULL::date END), lad.closing_local_date, (CASE WHEN (cl.created_instant IS NOT NULL) THEN 1 ELSE 0 END), (CASE WHEN ((lad.closing_local_date IS NOT NULL) AND (lad.application_received_local_date IS NOT NULL) AND (lo.id <> ALL ('{2,31,41,13,33,12,30,23,1,999,42,14,21,22,19,18,20,5,43,3,27,29,28}'::bigint[]))) THEN 1 ELSE 0 END), ltv.value_on_creation, top_dti.value_on_creation, bottom_dti.value_on_creation, lt.amount, lt.interest_rate, (COALESCE((di.deal_id)::bigint, la.deal_id)), lo.name, t.processor, t.underwriter, t.quality_reviewer, t.received, t.closer, t.secondary, t.mortgage_adviser, lo.is_closed_deal, (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), mi.mi_company, (CASE WHEN (COALESCE((di.deal_id)::bigint, la.deal_id) IS NOT NULL) THEN 'Y'::text ELSE 'N'::text END)
  • Sort Method: quicksort Memory: 134kB
72. 1.971 2,195.245 ↑ 1.6 368 1

Nested Loop Left Join (cost=1,884.85..3,226.23 rows=589 width=2,031) (actual time=2,167.413..2,195.245 rows=368 loops=1)

73. 0.412 2,191.434 ↑ 1.6 368 1

Hash Left Join (cost=1,884.56..2,994.52 rows=589 width=1,931) (actual time=2,167.322..2,191.434 rows=368 loops=1)

  • Hash Cond: (la.id = mi.loan_application_id)
74. 0.465 2,149.353 ↑ 1.6 368 1

Hash Left Join (cost=1,667.96..2,772.75 rows=589 width=1,899) (actual time=2,125.629..2,149.353 rows=368 loops=1)

  • Hash Cond: (la.id = bottom_dti.loan_application_id)
75. 0.453 2,133.166 ↑ 1.6 368 1

Hash Left Join (cost=1,640.84..2,743.42 rows=589 width=1,383) (actual time=2,109.871..2,133.166 rows=368 loops=1)

  • Hash Cond: (la.id = top_dti.loan_application_id)
76. 0.525 2,117.401 ↑ 1.6 368 1

Hash Left Join (cost=1,613.72..2,714.08 rows=589 width=867) (actual time=2,094.521..2,117.401 rows=368 loops=1)

  • Hash Cond: (la.id = ltv.loan_application_id)
77. 0.257 424.501 ↑ 1.6 368 1

Nested Loop Left Join (cost=1,586.60..2,684.74 rows=589 width=351) (actual time=402.116..424.501 rows=368 loops=1)

  • Join Filter: (NOT pri.is_removed)
78. 0.513 422.036 ↑ 1.6 368 1

Nested Loop Left Join (cost=1,586.31..2,490.34 rows=589 width=365) (actual time=402.096..422.036 rows=368 loops=1)

79. 0.495 419.683 ↑ 1.6 368 1

Nested Loop Left Join (cost=1,586.02..2,303.30 rows=589 width=342) (actual time=402.080..419.683 rows=368 loops=1)

80. 5.759 416.980 ↑ 1.6 368 1

Hash Right Join (cost=1,585.73..2,114.43 rows=589 width=337) (actual time=402.053..416.980 rows=368 loops=1)

  • Hash Cond: (t.loan_id = la.id)
81. 228.973 228.973 ↑ 1.0 22,012 1

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

82. 0.413 182.248 ↑ 1.6 368 1

Hash (cost=1,578.38..1,578.38 rows=588 width=113) (actual time=182.248..182.248 rows=368 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 51kB
83. 0.475 181.835 ↑ 1.6 368 1

Hash Left Join (cost=1,043.64..1,578.38 rows=588 width=113) (actual time=87.274..181.835 rows=368 loops=1)

  • Hash Cond: (la.id = ca.loan_application_id)
84. 0.410 178.374 ↑ 1.6 368 1

Hash Left Join (cost=910.68..1,441.74 rows=588 width=105) (actual time=84.251..178.374 rows=368 loops=1)

  • Hash Cond: (la.id = cl.loan_application_id)
85. 7.532 133.969 ↑ 1.6 368 1

Hash Right Join (cost=777.73..1,305.10 rows=588 width=97) (actual time=40.206..133.969 rows=368 loops=1)

  • Hash Cond: (di.loan_application_id = la.id)
86. 109.106 109.106 ↓ 1.0 21,992 1

CTE Scan on deal_id di (cost=0.00..439.16 rows=21,958 width=12) (actual time=0.106..109.106 rows=21,992 loops=1)

87. 0.384 17.331 ↑ 1.6 368 1

Hash (cost=770.38..770.38 rows=588 width=93) (actual time=17.331..17.331 rows=368 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 46kB
88. 0.627 16.947 ↑ 1.6 368 1

Hash Join (cost=1.24..770.38 rows=588 width=93) (actual time=0.355..16.947 rows=368 loops=1)

  • Hash Cond: (la.loan_status_id = lo.id)
89. 1.158 16.272 ↓ 1.0 1,192 1

Nested Loop (cost=0.29..759.14 rows=1,175 width=60) (actual time=0.198..16.272 rows=1,192 loops=1)

90. 9.129 9.129 ↑ 1.0 1,197 1

Seq Scan on loan_application_dates lad (cost=0.00..315.66 rows=1,199 width=36) (actual time=0.171..9.129 rows=1,197 loops=1)

  • Filter: ((application_received_local_date IS NOT NULL) AND (closing_local_date >= (('now'::cstring)::date - 90)))
  • Rows Removed by Filter: 12,331
91. 5.985 5.985 ↑ 1.0 1 1,197

Index Scan using loan_applications_pkey on loan_applications la (cost=0.29..0.36 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1,197)

  • Index Cond: (id = lad.loan_application_id)
  • Filter: ((NOT is_test) AND (NOT is_removed))
  • Rows Removed by Filter: 0
92. 0.021 0.048 ↓ 1.5 34 1

Hash (cost=0.66..0.66 rows=23 width=41) (actual time=0.048..0.048 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
93. 0.027 0.027 ↓ 1.5 34 1

Seq Scan on loan_status lo (cost=0.00..0.66 rows=23 width=41) (actual time=0.012..0.027 rows=34 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 19
94. 1.041 43.995 ↓ 81.1 2,352 1

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

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

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

  • Filter: (to_status = 10)
  • Rows Removed by Filter: 3,227
96. 1.091 2.986 ↓ 111.3 3,227 1

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

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

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

  • Filter: (to_status = 7)
  • Rows Removed by Filter: 2,352
98. 2.208 2.208 ↑ 1.0 1 368

Index Scan using borrowers_pkey on borrowers b (cost=0.29..0.31 rows=1 width=21) (actual time=0.005..0.006 rows=1 loops=368)

  • Index Cond: (la.primary_borrower_id = id)
99. 1.840 1.840 ↑ 1.0 1 368

Index Scan using property_information_loan_application_idx on property_information pri (cost=0.29..0.31 rows=1 width=23) (actual time=0.005..0.005 rows=1 loops=368)

  • Index Cond: (loan_application_id = la.id)
  • Filter: (NOT is_removed)
100. 2.208 2.208 ↑ 1.0 1 368

Index Scan using addresses_pkey on addresses addr (cost=0.29..0.32 rows=1 width=19) (actual time=0.005..0.006 rows=1 loops=368)

  • Index Cond: (address_id = pri.address_id)
  • Filter: (loan_application_id = pri.loan_application_id)
101. 7.046 1,692.375 ↓ 2,388.8 14,333 1

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

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

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

  • Filter: (tolerance_property_id = 4)
  • Rows Removed by Filter: 28,666
103. 4.598 15.312 ↓ 2,388.8 14,333 1

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

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

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

  • Filter: (tolerance_property_id = 17)
  • Rows Removed by Filter: 28,666
105. 4.624 15.722 ↓ 2,388.8 14,333 1

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

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

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

  • Filter: (tolerance_property_id = 18)
  • Rows Removed by Filter: 28,666
107. 4.289 41.669 ↓ 200.4 9,621 1

Hash (cost=216.00..216.00 rows=48 width=40) (actual time=41.669..41.669 rows=9,621 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 556kB
108. 37.380 37.380 ↓ 200.4 9,621 1

CTE Scan on mi (cost=0.00..216.00 rows=48 width=40) (actual time=10.660..37.380 rows=9,621 loops=1)

  • Filter: (rn = 1)
109. 1.840 1.840 ↑ 1.0 1 368

Index Scan using loan_terms_unique_per_loan on loan_terms lt (cost=0.29..0.31 rows=1 width=24) (actual time=0.004..0.005 rows=1 loops=368)

  • Index Cond: (la.id = loan_application_id)
Planning time : 17.761 ms
Execution time : 2,203.792 ms