explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qyJU : no lease windows

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 147.708 1,180.769 ↓ 28.9 28,888 1

Hash Left Join (cost=1,215.13..2,911.11 rows=1,000 width=88) (actual time=291.101..1,180.769 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. 15.247 1,029.675 ↓ 28.9 28,888 1

Hash Left Join (cost=1,206.57..2,821.28 rows=1,000 width=181) (actual time=287.686..1,029.675 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: 10,443
3. 477.532 961.061 ↓ 28.9 28,888 1

Nested Loop Left Join (cost=1,042.15..2,626.83 rows=1,000 width=129) (actual time=234.300..961.061 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
4. 9.636 223.537 ↓ 28.9 28,888 1

Hash Right Join (cost=1,036.80..2,536.52 rows=1,000 width=97) (actual time=214.357..223.537 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
5. 0.074 6.103 ↓ 4.5 9 1

Nested Loop (cost=1.14..1,500.71 rows=2 width=60) (actual time=2.107..6.103 rows=9 loops=1)

6. 0.019 2.249 ↓ 4.5 9 1

Nested Loop Left Join (cost=0.71..1,483.80 rows=2 width=16) (actual time=1.510..2.249 rows=9 loops=1)

  • Filter: ((COALESCE(ms.maintenance_status_type_id, 0) <> 2) AND (COALESCE(ms.maintenance_status_type_id, 0) <> 4))
7. 1.969 1.969 ↓ 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=1.268..1.969 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
8. 0.261 0.261 ↑ 1.0 1 9

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

  • Index Cond: ((cid = mr.cid) AND (cid = 235) AND (id = mr.maintenance_status_id))
9. 3.780 3.780 ↑ 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.420..0.420 rows=1 loops=9)

  • Index Cond: (maintenance_request_id = mr.id)
10. 10.107 207.798 ↓ 28.9 28,888 1

Hash (cost=1,018.17..1,018.17 rows=1,000 width=65) (actual time=207.798..207.798 rows=28,888 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3,282kB
11. 16.882 197.691 ↓ 28.9 28,888 1

Hash Left Join (cost=367.12..1,018.17 rows=1,000 width=65) (actual time=162.058..197.691 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. 3.552 27.185 ↓ 28.9 28,888 1

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

13. 0.060 21.749 ↓ 314.0 314 1

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

14. 0.128 8.815 ↓ 13.7 314 1

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

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

Bitmap Heap Scan on unit_spaces us (cost=5.71..446.28 rows=83 width=41) (actual time=0.098..0.712 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.060 0.060 ↓ 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.060..0.060 rows=363 loops=1)

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

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

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

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

  • Recheck Cond: (cid = 235)
  • Heap Blocks: exact=164
19. 0.826 0.826 ↑ 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.826..0.826 rows=2,324 loops=1)

  • Index Cond: (cid = 235)
20. 12.874 12.874 ↑ 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.041..0.041 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. 5.634 153.624 ↓ 27,048.0 27,048 1

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

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,736kB
23. 147.990 147.990 ↓ 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=143.722..147.990 rows=27,048 loops=1)

  • Filter: (cid = 235)
24. 240.214 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)

25. 0.443 19.778 ↓ 165.0 165 1

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

26. 0.105 16.530 ↓ 165.0 165 1

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

27. 0.224 14.940 ↓ 165.0 165 1

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

28. 0.142 5.212 ↓ 352.0 352 1

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

29. 0.366 0.366 ↓ 196.0 196 1

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

  • Filter: (cid = 235)
  • Rows Removed by Filter: 68
30. 4.704 4.704 ↓ 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.024 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
31. 7.744 9.504 ↓ 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.027..0.027 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
32. 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))
33. 1.485 1.485 ↑ 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.009..0.009 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))
34. 2.805 2.805 ↑ 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.017..0.017 rows=1 loops=165)

  • Index Cond: ((cid = 235) AND (id = o.customer_id))
35. 0.247 53.367 ↓ 24.7 148 1

Hash (cost=164.32..164.32 rows=6 width=72) (actual time=53.367..53.367 rows=148 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 112kB
36. 1.694 53.120 ↓ 24.7 148 1

Nested Loop Left Join (cost=2.95..164.32 rows=6 width=72) (actual time=2.159..53.120 rows=148 loops=1)

  • Join Filter: (ocus.organization_contract_id = lsw.organization_contract_id)
  • Rows Removed by Join Filter: 47
37. 0.189 49.798 ↓ 24.7 148 1

Nested Loop Left Join (cost=2.67..114.40 rows=6 width=100) (actual time=1.686..49.798 rows=148 loops=1)

  • Join Filter: ((lt.cid = lsw.cid) AND (lsw.property_id = cl.property_id) AND (lt.id = lsw.lease_term_id))
  • Rows Removed by Join Filter: 1
38. 0.234 49.313 ↓ 24.7 148 1

Nested Loop (cost=2.38..112.34 rows=6 width=96) (actual time=1.673..49.313 rows=148 loops=1)

39. 0.117 47.303 ↓ 148.0 148 1

Nested Loop Left Join (cost=2.10..112.01 rows=1 width=88) (actual time=1.253..47.303 rows=148 loops=1)

40. 0.141 45.558 ↓ 148.0 148 1

Nested Loop Left Join (cost=1.68..103.56 rows=1 width=86) (actual time=1.237..45.558 rows=148 loops=1)

41. 0.161 35.649 ↓ 148.0 148 1

Nested Loop (cost=1.26..95.11 rows=1 width=82) (actual time=0.818..35.649 rows=148 loops=1)

  • Join Filter: ((cl.id = li.lease_id) AND (cl.active_lease_interval_id = li.id))
42. 0.157 19.356 ↓ 148.0 148 1

Nested Loop (cost=0.84..94.39 rows=1 width=70) (actual time=0.360..19.356 rows=148 loops=1)

43. 1.615 1.615 ↓ 31.4 157 1

Index Scan using idx_cached_leases_cid_property_id on cached_leases cl (cost=0.42..52.11 rows=5 width=54) (actual time=0.075..1.615 rows=157 loops=1)

  • Index Cond: ((cid = 235) AND (property_id = 719,642))
  • Filter: (lease_status_type_id = ANY ('{3,4,5}'::integer[]))
  • Rows Removed by Filter: 44
44. 17.584 17.584 ↑ 1.0 1 157

Index Scan using idx_cached_applications_lease_interval_id on cached_applications ca (cost=0.42..8.45 rows=1 width=24) (actual time=0.112..0.112 rows=1 loops=157)

  • Index Cond: (lease_interval_id = cl.active_lease_interval_id)
  • Filter: ((cid = 235) AND (cl.id = lease_id) AND (cl.unit_space_id = unit_space_id))
  • Rows Removed by Filter: 0
45. 16.132 16.132 ↑ 1.0 1 148

Index Scan using idx_lease_intervals on lease_intervals li (cost=0.42..0.71 rows=1 width=28) (actual time=0.109..0.109 rows=1 loops=148)

  • Index Cond: (id = ca.lease_interval_id)
  • Filter: ((cid = 235) AND (ca.lease_id = lease_id))
46. 9.768 9.768 ↑ 1.0 1 148

Index Scan using idx_lease_unit_spaces_lease_id on lease_unit_spaces lus (cost=0.42..8.44 rows=1 width=16) (actual time=0.066..0.066 rows=1 loops=148)

  • Index Cond: (lease_id = cl.id)
  • Filter: ((cid = 235) AND (cl.cid = cid) AND (unit_space_id = cl.unit_space_id))
47. 1.628 1.628 ↑ 1.0 1 148

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

  • Index Cond: ((cid = cl.cid) AND (cid = 235) AND (id = cl.primary_customer_id))
48. 1.776 1.776 ↑ 1.0 1 148

Index Scan using pk_lease_terms on lease_terms lt (cost=0.29..0.34 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=148)

  • Index Cond: ((cid = 235) AND (id = li.lease_term_id))
49. 0.296 0.296 ↑ 1.0 1 148

Index Scan using pk_lease_start_windows on lease_start_windows lsw (cost=0.29..0.33 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=148)

  • Index Cond: ((cid = 235) AND (id = li.lease_start_window_id))
  • Filter: ((deleted_on IS NULL) AND (is_active IS TRUE) AND (property_id = 719,642))
50. 1.628 1.628 ↑ 1.0 1 148

Index Scan using ca_organization_contract_unit_spaces_idx_1 on organization_contract_unit_spaces ocus (cost=0.28..8.30 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=148)

  • Index Cond: ((cid = cl.cid) AND (cid = 235) AND (unit_space_id = cl.unit_space_id))
51. 0.015 3.386 ↓ 2.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
52. 0.012 3.371 ↓ 2.0 2 1

Subquery Scan on st (cost=8.46..8.54 rows=1 width=52) (actual time=3.355..3.371 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
53. 0.037 3.359 ↓ 12.0 12 1

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

54. 0.058 3.322 ↓ 12.0 12 1

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

  • Sort Key: ((st_1.details ->> 'unit_space_id'::text)), st_1.start_on
  • Sort Method: quicksort Memory: 26kB
55. 3.264 3.264 ↓ 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.911..3.264 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))