explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zDwU : Optimization for: Optimization for: no lease windows; plan #qyJU; plan #LYlB

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 143.336 1,112.499 ↓ 28.9 28,888 1

Hash Left Join (cost=1,145.49..2,803.11 rows=1,000 width=88) (actual time=249.821..1,112.499 rows=28,888 loops=1)

  • Hash Cond: ((us.cid = st.cid) AND (us.property_id = st.property_id) AND (us.id = st.unit_space_id))
  • Join Filter: (((generate_series.generate_series)::date >= st.scheduled_start_date) AND ((generate_series.generate_series)::date <= st.scheduled_end_date))
2. 462.893 965.408 ↓ 28.9 28,888 1

Nested Loop Left Join (cost=1,136.93..2,713.28 rows=1,000 width=181) (actual time=246.033..965.408 rows=28,888 loops=1)

  • Join Filter: ((ocus_1.property_id = us.property_id) AND (oc.cid = us.cid) AND (ocus_1.unit_space_id = us.id) AND ((generate_series.generate_series)::date >= lsw_1.start_date) AND ((generate_series.generate_series)::date <= lsw_1.end_date))
  • Rows Removed by Join Filter: 4,765,498
3. 10.471 242.523 ↓ 28.9 28,888 1

Hash Right Join (cost=1,131.59..2,622.97 rows=1,000 width=149) (actual time=232.507..242.523 rows=28,888 loops=1)

  • Hash Cond: ((mr.cid = us.cid) AND (mr.property_id = us.property_id) AND (mr.unit_space_id = us.id))
  • Join Filter: (((generate_series.generate_series)::date >= COALESCE((mrd.scheduled_start_datetime)::date, (generate_series.generate_series)::date)) AND ((generate_series.generate_series)::date <= COALESCE((mrd.scheduled_end_datetime)::date, (generate_series.generate_series)::date)))
  • Rows Removed by Join Filter: 828
4. 0.071 4.479 ↓ 9.0 9 1

Nested Loop (cost=1.14..1,492.43 rows=1 width=60) (actual time=1.049..4.479 rows=9 loops=1)

5. 0.012 0.502 ↓ 9.0 9 1

Nested Loop (cost=0.71..1,483.97 rows=1 width=16) (actual time=0.412..0.502 rows=9 loops=1)

6. 0.436 0.436 ↓ 4.5 9 1

Index Scan using idx_maintenance_requests_property_id on maintenance_requests mr (cost=0.43..1,467.17 rows=2 width=20) (actual time=0.383..0.436 rows=9 loops=1)

  • Index Cond: (property_id = 719,642)
  • Filter: ((cid IS NOT NULL) AND (parent_maintenance_request_id IS NULL) AND (deleted_on IS NULL) AND (cid = 235) AND (maintenance_request_type_id = 1))
  • Rows Removed by Filter: 5
7. 0.054 0.054 ↑ 1.0 1 9

Index Scan using pk_maintenance_statuses on maintenance_statuses ms (cost=0.28..8.30 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=9)

  • Index Cond: ((cid = 235) AND (id = mr.maintenance_status_id))
  • Filter: ((COALESCE(maintenance_status_type_id, 0) <> 2) AND (COALESCE(maintenance_status_type_id, 0) <> 4))
8. 3.906 3.906 ↑ 1.0 1 9

Index Scan using idx_maintenance_request_details_maintenance_request_id on maintenance_request_details mrd (cost=0.43..8.45 rows=1 width=20) (actual time=0.434..0.434 rows=1 loops=9)

  • Index Cond: (maintenance_request_id = mr.id)
9. 12.146 227.573 ↓ 28.9 28,888 1

Hash (cost=1,112.95..1,112.95 rows=1,000 width=117) (actual time=227.573..227.573 rows=28,888 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3,764kB
10. 10.735 215.427 ↓ 28.9 28,888 1

Hash Left Join (cost=450.62..1,112.95 rows=1,000 width=117) (actual time=174.853..215.427 rows=28,888 loops=1)

  • Hash Cond: ((us.cid = cl.cid) AND (us.property_id = cl.property_id) AND (us.id = cl.unit_space_id))
  • Join Filter: (((generate_series.generate_series)::date >= COALESCE(li.lease_start_date, (generate_series.generate_series)::date)) AND ((generate_series.generate_series)::date <= COALESCE(li.lease_end_date, (generate_series.generate_series)::date)))
  • Rows Removed by Join Filter: 9,380
11. 20.615 174.704 ↓ 28.9 28,888 1

Hash Left Join (cost=367.12..1,018.17 rows=1,000 width=65) (actual time=144.845..174.704 rows=28,888 loops=1)

  • Hash Cond: ((us.cid = laus.cid) AND (us.id = laus.unit_space_id) AND ((generate_series.generate_series)::date = laus.move_in_date))
12. 4.032 11.963 ↓ 28.9 28,888 1

Nested Loop (cost=366.22..1,004.77 rows=1,000 width=53) (actual time=2.667..11.963 rows=28,888 loops=1)

13. 0.126 6.047 ↓ 314.0 314 1

Nested Loop (cost=366.22..984.76 rows=1 width=45) (actual time=2.635..6.047 rows=314 loops=1)

14. 0.150 3.409 ↓ 13.7 314 1

Hash Join (cost=365.92..806.71 rows=23 width=41) (actual time=2.581..3.409 rows=314 loops=1)

  • Hash Cond: (us.property_floorplan_id = pf.id)
15. 0.750 0.816 ↓ 3.8 314 1

Bitmap Heap Scan on unit_spaces us (cost=5.71..446.28 rows=83 width=41) (actual time=0.102..0.816 rows=314 loops=1)

  • Recheck Cond: ((cid = 235) AND (property_id = 719,642))
  • Filter: ((deleted_on IS NULL) AND (occupancy_type_id <> 2))
  • Rows Removed by Filter: 48
  • Heap Blocks: exact=174
16. 0.066 0.066 ↓ 2.9 363 1

Bitmap Index Scan on idx_unit_spaces_company_property_id (cost=0.00..5.69 rows=127 width=0) (actual time=0.065..0.066 rows=363 loops=1)

  • Index Cond: ((cid = 235) AND (property_id = 719,642))
17. 0.395 2.443 ↑ 1.0 2,322 1

Hash (cost=330.85..330.85 rows=2,349 width=8) (actual time=2.443..2.443 rows=2,322 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 123kB
18. 1.890 2.048 ↑ 1.0 2,322 1

Bitmap Heap Scan on property_floorplans pf (cost=50.49..330.85 rows=2,349 width=8) (actual time=0.196..2.048 rows=2,322 loops=1)

  • Recheck Cond: (cid = 235)
  • Heap Blocks: exact=164
19. 0.158 0.158 ↑ 1.0 2,324 1

Bitmap Index Scan on pk_property_floorplans (cost=0.00..49.90 rows=2,349 width=0) (actual time=0.158..0.158 rows=2,324 loops=1)

  • Index Cond: (cid = 235)
20. 2.512 2.512 ↑ 1.0 1 314

Index Scan using pk_property_units on property_units pu (cost=0.29..7.74 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=314)

  • Index Cond: ((cid = 235) AND (id = us.property_unit_id))
  • Filter: (property_id = 719,642)
21. 1.884 1.884 ↑ 10.9 92 314

Function Scan on generate_series (cost=0.01..10.01 rows=1,000 width=8) (actual time=0.000..0.006 rows=92 loops=314)

22. 6.094 142.126 ↓ 27,048.0 27,048 1

Hash (cost=0.88..0.88 rows=1 width=24) (actual time=142.126..142.126 rows=27,048 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,736kB
23. 136.032 136.032 ↓ 27,048.0 27,048 1

Function Scan on load_available_unit_spaces laus (cost=0.25..0.88 rows=1 width=24) (actual time=131.587..136.032 rows=27,048 loops=1)

  • Filter: (cid = 235)
24. 0.159 29.988 ↓ 110.0 110 1

Hash (cost=83.49..83.49 rows=1 width=72) (actual time=29.988..29.988 rows=110 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 86kB
25. 1.161 29.829 ↓ 110.0 110 1

Nested Loop (cost=18.80..83.49 rows=1 width=72) (actual time=2.647..29.829 rows=110 loops=1)

26. 0.089 25.038 ↓ 110.0 110 1

Nested Loop (cost=18.38..75.05 rows=1 width=98) (actual time=2.371..25.038 rows=110 loops=1)

  • Join Filter: (lsw.organization_contract_id = ocus.organization_contract_id)
  • Rows Removed by Join Filter: 36
27. 0.181 20.731 ↓ 111.0 111 1

Nested Loop (cost=18.09..74.73 rows=1 width=106) (actual time=1.743..20.731 rows=111 loops=1)

  • Join Filter: (ca.unit_space_id = cl.unit_space_id)
28. 0.047 19.310 ↓ 124.0 124 1

Nested Loop (cost=17.68..74.14 rows=1 width=88) (actual time=1.703..19.310 rows=124 loops=1)

  • Join Filter: (ca.unit_space_id = lus.unit_space_id)
29. 0.135 12.443 ↓ 124.0 124 1

Nested Loop (cost=17.26..73.65 rows=1 width=72) (actual time=1.126..12.443 rows=124 loops=1)

30. 0.152 10.388 ↓ 128.0 128 1

Nested Loop (cost=16.84..72.43 rows=1 width=48) (actual time=1.087..10.388 rows=128 loops=1)

  • Join Filter: (lt.id = li.lease_term_id)
31. 0.100 2.841 ↓ 85.0 85 1

Nested Loop (cost=0.57..52.13 rows=1 width=36) (actual time=0.110..2.841 rows=85 loops=1)

32. 1.041 1.041 ↓ 21.2 85 1

Index Scan using ca_lease_start_windows_idx on lease_start_windows lsw (cost=0.29..18.90 rows=4 width=28) (actual time=0.060..1.041 rows=85 loops=1)

  • Index Cond: ((cid = 235) AND (property_id = 719,642))
  • Filter: ((deleted_on IS NULL) AND (is_active IS TRUE))
  • Rows Removed by Filter: 3
33. 1.700 1.700 ↑ 1.0 1 85

Index Scan using pk_lease_terms on lease_terms lt (cost=0.29..8.30 rows=1 width=12) (actual time=0.020..0.020 rows=1 loops=85)

  • Index Cond: ((cid = 235) AND (id = lsw.lease_term_id))
34. 3.825 7.395 ↓ 2.0 2 85

Bitmap Heap Scan on lease_intervals li (cost=16.27..20.28 rows=1 width=28) (actual time=0.066..0.087 rows=2 loops=85)

  • Recheck Cond: ((cid = 235) AND (lease_start_window_id = lsw.id) AND (lease_term_id = lsw.lease_term_id))
  • Heap Blocks: exact=89
35. 0.065 3.570 ↓ 0.0 0 85

BitmapAnd (cost=16.27..16.27 rows=1 width=0) (actual time=0.042..0.042 rows=0 loops=85)

36. 2.210 2.210 ↑ 8.5 2 85

Bitmap Index Scan on idx_lease_intervals_cid_lease_start_window_id (cost=0.00..4.59 rows=17 width=0) (actual time=0.026..0.026 rows=2 loops=85)

  • Index Cond: ((cid = 235) AND (lease_start_window_id = lsw.id))
37. 1.295 1.295 ↑ 13.8 29 35

Bitmap Index Scan on idx_lease_intervals_lease_term_id (cost=0.00..11.42 rows=400 width=0) (actual time=0.037..0.037 rows=29 loops=35)

  • Index Cond: (lease_term_id = lsw.lease_term_id)
38. 1.920 1.920 ↑ 1.0 1 128

Index Scan using idx_cached_applications_lease_interval_id on cached_applications ca (cost=0.42..1.21 rows=1 width=24) (actual time=0.015..0.015 rows=1 loops=128)

  • Index Cond: (lease_interval_id = li.id)
  • Filter: ((cid = 235) AND (li.lease_id = lease_id))
39. 6.820 6.820 ↑ 1.0 1 124

Index Scan using idx_lease_unit_spaces_lease_id on lease_unit_spaces lus (cost=0.42..0.48 rows=1 width=16) (actual time=0.054..0.055 rows=1 loops=124)

  • Index Cond: (lease_id = li.lease_id)
  • Filter: (cid = 235)
40. 1.240 1.240 ↑ 1.0 1 124

Index Scan using pk_cached_leases on cached_leases cl (cost=0.42..0.57 rows=1 width=54) (actual time=0.010..0.010 rows=1 loops=124)

  • Index Cond: ((cid = 235) AND (id = li.lease_id))
  • Filter: ((property_id = 719,642) AND (li.id = active_lease_interval_id) AND (lease_status_type_id = ANY ('{3,4,5}'::integer[])))
  • Rows Removed by Filter: 0
41. 4.218 4.218 ↑ 1.0 1 111

Index Scan using ca_organization_contract_unit_spaces_idx on organization_contract_unit_spaces ocus (cost=0.28..0.31 rows=1 width=16) (actual time=0.035..0.038 rows=1 loops=111)

  • Index Cond: ((cid = 235) AND (unit_space_id = ca.unit_space_id))
42. 3.630 3.630 ↑ 1.0 1 110

Index Scan using pk_customers on customers c (cost=0.42..8.44 rows=1 width=10) (actual time=0.033..0.033 rows=1 loops=110)

  • Index Cond: ((cid = 235) AND (id = cl.primary_customer_id))
43. 246.621 259.992 ↓ 165.0 165 28,888

Materialize (cost=5.35..60.32 rows=1 width=52) (actual time=0.000..0.009 rows=165 loops=28,888)

44. 0.483 13.371 ↓ 165.0 165 1

Nested Loop (cost=5.35..60.31 rows=1 width=52) (actual time=7.225..13.371 rows=165 loops=1)

45. 0.155 11.733 ↓ 165.0 165 1

Nested Loop (cost=4.93..51.87 rows=1 width=40) (actual time=7.192..11.733 rows=165 loops=1)

46. 0.385 11.248 ↓ 165.0 165 1

Nested Loop (cost=4.64..43.55 rows=1 width=36) (actual time=7.147..11.248 rows=165 loops=1)

47. 0.182 4.879 ↓ 352.0 352 1

Nested Loop (cost=0.28..14.63 rows=1 width=28) (actual time=0.712..4.879 rows=352 loops=1)

48. 0.385 0.385 ↓ 196.0 196 1

Seq Scan on organizations o (cost=0.00..6.30 rows=1 width=12) (actual time=0.276..0.385 rows=196 loops=1)

  • Filter: (cid = 235)
  • Rows Removed by Filter: 68
49. 4.312 4.312 ↓ 2.0 2 196

Index Scan using ukey_organization_contracts_name on organization_contracts oc (cost=0.28..8.32 rows=1 width=20) (actual time=0.014..0.022 rows=2 loops=196)

  • Index Cond: ((cid = 235) AND (organization_id = o.id))
  • Filter: (organization_contract_status_type_id = ANY ('{1,2}'::integer[]))
  • Rows Removed by Filter: 0
50. 4.224 5.984 ↓ 0.0 0 352

Bitmap Heap Scan on organization_contract_unit_spaces ocus_1 (cost=4.36..28.91 rows=1 width=16) (actual time=0.017..0.017 rows=0 loops=352)

  • Recheck Cond: ((cid = 235) AND (organization_contract_id = oc.id))
  • Filter: ((deleted_on IS NULL) AND (property_id = 719,642))
  • Rows Removed by Filter: 9
  • Heap Blocks: exact=359
51. 1.760 1.760 ↓ 1.1 9 352

Bitmap Index Scan on ca_organization_contract_unit_spaces_idx_2 (cost=0.00..4.36 rows=8 width=0) (actual time=0.005..0.005 rows=9 loops=352)

  • Index Cond: ((cid = 235) AND (organization_contract_id = oc.id))
52. 0.330 0.330 ↑ 1.0 1 165

Index Scan using pk_lease_start_windows on lease_start_windows lsw_1 (cost=0.29..8.31 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=165)

  • Index Cond: ((cid = 235) AND (id = oc.lease_start_window_id))
  • Filter: ((deleted_on IS NULL) AND (is_active IS TRUE))
53. 1.155 1.155 ↑ 1.0 1 165

Index Scan using pk_customers on customers c_1 (cost=0.42..8.44 rows=1 width=21) (actual time=0.007..0.007 rows=1 loops=165)

  • Index Cond: ((cid = 235) AND (id = o.customer_id))
54. 0.007 3.755 ↓ 2.0 2 1

Hash (cost=8.54..8.54 rows=1 width=52) (actual time=3.755..3.755 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
55. 0.019 3.748 ↓ 2.0 2 1

Subquery Scan on st (cost=8.46..8.54 rows=1 width=52) (actual time=3.727..3.748 rows=2 loops=1)

  • Filter: ((st.scheduled_start_date <= '2020-10-29'::date) AND (st.scheduled_end_date >= '2020-07-30'::date))
  • Rows Removed by Filter: 10
56. 0.057 3.729 ↓ 12.0 12 1

WindowAgg (cost=8.46..8.52 rows=1 width=64) (actual time=3.706..3.729 rows=12 loops=1)

57. 0.114 3.672 ↓ 12.0 12 1

Sort (cost=8.46..8.47 rows=1 width=622) (actual time=3.670..3.672 rows=12 loops=1)

  • Sort Key: ((st_1.details ->> 'unit_space_id'::text)), st_1.start_on
  • Sort Method: quicksort Memory: 26kB
58. 3.558 3.558 ↓ 12.0 12 1

Index Scan using ca_scheduled_tasks_idx on scheduled_tasks st_1 (cost=0.43..8.45 rows=1 width=622) (actual time=0.971..3.558 rows=12 loops=1)

  • Index Cond: ((cid = 235) AND (scheduled_task_type_id = 11) AND (property_id = 719,642))
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL))