explain.depesz.com

PostgreSQL's explain analyze made readable

Result: U7z4

Settings
# exclusive inclusive rows x rows loops node
1. 2,157.588 2,157.588 ↑ 1.6 368 1

CTE Scan on stg (cost=79,253.63..79,265.41 rows=589 width=2,101) (actual time=2,156.823..2,157.588 rows=368 loops=1)

2.          

CTE transition_events

3. 7.865 37.760 ↑ 1.1 5,578 1

Finalize GroupAggregate (cost=1,639.54..1,752.26 rows=5,893 width=20) (actual time=28.073..37.760 rows=5,578 loops=1)

  • Group Key: loan_status_transition_events.loan_application_id, loan_status_transition_events.to_status
4. 4.855 29.895 ↓ 1.5 5,856 1

Sort (cost=1,639.54..1,649.30 rows=3,906 width=20) (actual time=28.040..29.895 rows=5,856 loops=1)

  • Sort Key: loan_status_transition_events.loan_application_id, loan_status_transition_events.to_status
  • Sort Method: quicksort Memory: 650kB
5. 8.520 25.040 ↓ 1.5 5,856 1

Gather (cost=976.85..1,406.51 rows=3,906 width=20) (actual time=21.658..25.040 rows=5,856 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
6. 2.913 16.520 ↑ 1.3 2,928 2 / 2

Partial HashAggregate (cost=966.85..1,005.91 rows=3,906 width=20) (actual time=15.506..16.520 rows=2,928 loops=2)

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

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

CTE team

9. 33.849 165.759 ↑ 1.0 22,012 1

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

  • Hash Cond: (tm_ma.person_id = p_ma.id)
10. 10.806 131.806 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.customer_care_member_id = tm_ma.id)
11. 12.146 120.901 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.received_member_id = tm_re.id)
12. 7.976 108.467 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.closer_member_id = tm_cl.id)
13. 7.485 100.179 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.secondary_member_id = tm_s.id)
14. 8.572 92.408 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.processor_member_id = tm_pr.id)
15. 8.550 83.545 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.underwriter_member_id = tm_u.id)
16. 8.884 74.663 ↑ 1.0 22,012 1

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

  • Hash Cond: (lat.quality_review_id = tm_q.id)
17. 23.652 65.452 ↑ 1.0 22,012 1

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

  • Merge Cond: (la.id = lat.loan_application_id)
18. 12.023 12.023 ↓ 1.0 22,012 1

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

  • Heap Fetches: 6,280
19. 29.777 29.777 ↑ 1.0 22,004 1

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

20. 0.060 0.327 ↑ 1.5 153 1

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

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

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

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

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

23. 0.056 0.110 ↓ 1.0 121 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
24. 0.054 0.054 ↓ 1.0 121 1

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

25. 0.060 0.332 ↑ 1.5 153 1

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

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

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

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

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

28. 0.057 0.100 ↓ 1.0 121 1

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

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

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

30. 0.051 0.291 ↑ 1.5 153 1

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

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

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

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

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

33. 0.054 0.094 ↓ 1.0 121 1

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

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

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

35. 0.053 0.286 ↑ 1.5 153 1

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

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

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

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

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

38. 0.053 0.096 ↓ 1.0 121 1

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

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

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

40. 0.061 0.312 ↑ 1.5 153 1

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

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

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

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

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

43. 0.049 0.089 ↓ 1.0 121 1

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

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

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

45. 0.049 0.288 ↑ 1.5 153 1

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

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

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

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

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

48. 0.045 0.091 ↓ 1.0 121 1

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

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

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

50. 0.056 0.099 ↑ 1.5 153 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
51. 0.043 0.043 ↑ 1.5 153 1

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

52. 0.056 0.104 ↓ 1.0 121 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
53. 0.048 0.048 ↓ 1.0 121 1

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

54.          

CTE ltv_dti

55. 58.436 1,655.971 ↓ 35.8 42,999 1

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

  • Filter: (a.rn = 1)
  • Rows Removed by Filter: 199,995
56. 212.799 1,597.535 ↓ 1.0 242,994 1

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

57. 409.091 1,384.736 ↓ 1.0 242,994 1

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

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

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

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

CTE mi

60. 3.111 32.920 ↓ 184.4 10,513 1

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

  • Filter: (a_1.rn = 1)
  • Rows Removed by Filter: 968
61. 16.676 29.809 ↓ 1.0 11,481 1

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

62. 8.757 13.133 ↓ 1.0 11,481 1

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

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

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

64.          

CTE deal_id

65. 66.520 96.964 ↓ 1.0 21,992 1

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

  • Group Key: affiliated_business_details.loan_application_id
66. 30.444 30.444 ↓ 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.013..30.444 rows=43,983 loops=1)

67.          

CTE stg

68. 0.454 2,157.395 ↑ 1.6 368 1

Unique (cost=3,035.64..3,081.29 rows=589 width=2,031) (actual time=2,156.821..2,157.395 rows=368 loops=1)

69. 1.588 2,156.941 ↑ 1.6 368 1

Sort (cost=3,035.64..3,037.12 rows=589 width=2,031) (actual time=2,156.818..2,156.941 rows=368 loops=1)

  • Sort Key: addr.state_code, la_1.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_1.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_1.deal_id) IS NOT NULL) THEN 'Y'::text ELSE 'N'::text END)
  • Sort Method: quicksort Memory: 135kB
70. 2.578 2,155.353 ↑ 1.6 368 1

Nested Loop Left Join (cost=1,670.10..3,008.54 rows=589 width=2,031) (actual time=1,987.828..2,155.353 rows=368 loops=1)

71. 0.492 2,150.199 ↑ 1.6 368 1

Hash Left Join (cost=1,669.81..2,776.84 rows=589 width=1,931) (actual time=1,987.710..2,150.199 rows=368 loops=1)

  • Hash Cond: (la_1.id = mi.loan_application_id)
72. 0.502 2,106.663 ↑ 1.6 368 1

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

  • Hash Cond: (la_1.id = bottom_dti.loan_application_id)
73. 0.550 2,091.374 ↑ 1.6 368 1

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

  • Hash Cond: (la_1.id = top_dti.loan_application_id)
74. 0.598 2,075.865 ↑ 1.6 368 1

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

  • Hash Cond: (la_1.id = ltv.loan_application_id)
75. 0.392 383.711 ↑ 1.6 368 1

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

  • Join Filter: (NOT pri.is_removed)
76. 0.643 380.743 ↑ 1.6 368 1

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

77. 0.652 377.892 ↑ 1.6 368 1

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

78. 7.711 374.296 ↑ 1.6 368 1

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

  • Hash Cond: (t.loan_id = la_1.id)
79. 185.090 185.090 ↑ 1.0 22,012 1

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

80. 0.448 181.495 ↑ 1.6 368 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 51kB
81. 0.478 181.047 ↑ 1.6 368 1

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

  • Hash Cond: (la_1.id = ca.loan_application_id)
82. 0.430 177.400 ↑ 1.6 368 1

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

  • Hash Cond: (la_1.id = cl.loan_application_id)
83. 7.867 134.893 ↑ 1.6 368 1

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

  • Hash Cond: (di.loan_application_id = la_1.id)
84. 110.582 110.582 ↓ 1.0 21,992 1

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

85. 0.323 16.444 ↑ 1.6 368 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 46kB
86. 0.612 16.121 ↑ 1.6 368 1

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

  • Hash Cond: (la_1.loan_status_id = lo.id)
87. 0.783 15.469 ↓ 1.0 1,192 1

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

88. 8.701 8.701 ↑ 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.187..8.701 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
89. 5.985 5.985 ↑ 1.0 1 1,197

Index Scan using loan_applications_pkey on loan_applications la_1 (cost=0.29..0.36 rows=1 width=32) (actual time=0.004..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
90. 0.019 0.040 ↓ 1.5 34 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
91. 0.021 0.021 ↓ 1.5 34 1

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

  • Filter: is_active
  • Rows Removed by Filter: 19
92. 0.924 42.077 ↓ 81.1 2,351 1

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

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

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

  • Filter: (to_status = 10)
  • Rows Removed by Filter: 3,227
94. 1.440 3.169 ↓ 111.3 3,227 1

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

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

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

  • Filter: (to_status = 7)
  • Rows Removed by Filter: 2,351
96. 2.944 2.944 ↑ 1.0 1 368

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

  • Index Cond: (la_1.primary_borrower_id = id)
97. 2.208 2.208 ↑ 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.006 rows=1 loops=368)

  • Index Cond: (loan_application_id = la_1.id)
  • Filter: (NOT is_removed)
98. 2.576 2.576 ↑ 1.0 1 368

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

  • Index Cond: (address_id = pri.address_id)
  • Filter: (loan_application_id = pri.loan_application_id)
99. 7.322 1,691.556 ↓ 2,388.8 14,333 1

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

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

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

  • Filter: (tolerance_property_id = 4)
  • Rows Removed by Filter: 28,666
101. 4.209 14.959 ↓ 2,388.8 14,333 1

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

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 792kB
102. 10.750 10.750 ↓ 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.750 rows=14,333 loops=1)

  • Filter: (tolerance_property_id = 17)
  • Rows Removed by Filter: 28,666
103. 4.218 14.787 ↓ 2,388.8 14,333 1

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

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 793kB
104. 10.569 10.569 ↓ 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.009..10.569 rows=14,333 loops=1)

  • Filter: (tolerance_property_id = 18)
  • Rows Removed by Filter: 28,666
105. 4.014 43.044 ↓ 184.4 10,513 1

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

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

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

107. 2.576 2.576 ↑ 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.007..0.007 rows=1 loops=368)

  • Index Cond: (la_1.id = loan_application_id)
Planning time : 18.317 ms
Execution time : 2,164.977 ms