explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w6w5 : Optimization for: plan #4tGb

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 247.999 2,208.913 ↓ 28.9 28,888 1

Merge Left Join (cost=156,113.59..181,288.69 rows=1,000 width=88) (actual time=553.978..2,208.913 rows=28,888 loops=1)

  • Merge Cond: (us.id = ocus_1.unit_space_id)
  • Join Filter: ((oc.cid = us.cid) 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: 14,158
2. 18.250 1,932.086 ↓ 28.9 28,888 1

Merge Left Join (cost=156,053.22..181,155.80 rows=1,000 width=245) (actual time=526.409..1,932.086 rows=28,888 loops=1)

  • Merge Cond: (us.id = cl.unit_space_id)
  • Join Filter: ((cl.cid = us.cid) AND (cl.property_id = us.property_id) AND ((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. 8.385 1,907.625 ↓ 28.9 28,888 1

Merge Left Join (cost=155,888.83..180,988.85 rows=1,000 width=193) (actual time=521.364..1,907.625 rows=28,888 loops=1)

  • Merge Cond: (us.id = mr.unit_space_id)
  • Join Filter: ((mr.cid = us.cid) AND (mr.property_id = us.property_id) AND ((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. 9.430 1,898.996 ↓ 28.9 28,888 1

Merge Left Join (cost=154,388.10..179,485.59 rows=1,000 width=161) (actual time=521.189..1,898.996 rows=28,888 loops=1)

  • Merge Cond: (us.id = st.unit_space_id)
  • Join Filter: ((st.cid = us.cid) AND (st.property_id = us.property_id) AND ((generate_series.generate_series)::date >= st.scheduled_start_date) AND ((generate_series.generate_series)::date <= st.scheduled_end_date))
5. 6.053 1,549.886 ↓ 28.9 28,888 1

Nested Loop Left Join (cost=1,080.56..26,175.51 rows=1,000 width=129) (actual time=181.519..1,549.886 rows=28,888 loops=1)

6. 24.192 214.985 ↓ 28.9 28,888 1

Merge Left Join (cost=1,055.48..1,065.51 rows=1,000 width=65) (actual time=181.345..214.985 rows=28,888 loops=1)

  • Merge Cond: ((us.id = laus.unit_space_id) AND (((generate_series.generate_series)::date) = laus.move_in_date))
  • Join Filter: (us.cid = laus.cid)
7. 38.668 49.957 ↓ 28.9 28,888 1

Sort (cost=1,054.60..1,057.10 rows=1,000 width=53) (actual time=46.829..49.957 rows=28,888 loops=1)

  • Sort Key: us.id, ((generate_series.generate_series)::date)
  • Sort Method: quicksort Memory: 4,831kB
8. 6.970 11.289 ↓ 28.9 28,888 1

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

9. 0.278 2.749 ↓ 314.0 314 1

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

10. 0.111 1.843 ↓ 13.7 314 1

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

  • Hash Cond: (us.property_floorplan_id = pf.id)
11. 0.593 0.631 ↓ 3.8 314 1

Bitmap Heap Scan on unit_spaces us (cost=5.71..446.28 rows=83 width=41) (actual time=0.067..0.631 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
12. 0.038 0.038 ↓ 3.0 377 1

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

  • Index Cond: ((cid = 235) AND (property_id = 719,642))
13. 0.292 1.101 ↑ 1.0 2,322 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 123kB
14. 0.683 0.809 ↑ 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.144..0.809 rows=2,322 loops=1)

  • Recheck Cond: (cid = 235)
  • Heap Blocks: exact=164
15. 0.126 0.126 ↑ 1.0 2,325 1

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

  • Index Cond: (cid = 235)
16. 0.628 0.628 ↑ 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.002..0.002 rows=1 loops=314)

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

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

18. 21.047 140.836 ↓ 27,048.0 27,048 1

Sort (cost=0.89..0.89 rows=1 width=24) (actual time=134.502..140.836 rows=27,048 loops=1)

  • Sort Key: laus.unit_space_id, laus.move_in_date
  • Sort Method: quicksort Memory: 2,882kB
19. 119.789 119.789 ↓ 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=115.381..119.789 rows=27,048 loops=1)

  • Filter: (cid = 235)
20. 317.768 1,328.848 ↑ 1.0 1 28,888

Aggregate (cost=25.07..25.09 rows=1 width=64) (actual time=0.046..0.046 rows=1 loops=28,888)

21. 202.216 1,011.080 ↓ 2.8 14 28,888

Nested Loop Left Join (cost=0.86..25.05 rows=5 width=8) (actual time=0.015..0.035 rows=14 loops=28,888)

  • Join Filter: ((lt_1.lease_term_type_id = 2) AND (lt_1.cid = lsw_2.cid) AND (lsw_2.property_id = pcs.property_id) AND (lt_1.id = lsw_2.lease_term_id))
  • Rows Removed by Join Filter: 41
22. 86.664 433.320 ↓ 2.6 13 28,888

Nested Loop (cost=0.57..16.62 rows=5 width=16) (actual time=0.005..0.015 rows=13 loops=28,888)

23. 57.776 57.776 ↑ 1.0 1 28,888

Index Scan using idx_property_charge_settings_property_id on property_charge_settings pcs (cost=0.28..8.30 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=28,888)

  • Index Cond: (property_id = us.property_id)
  • Filter: (cid = us.cid)
24. 288.880 288.880 ↓ 13.0 13 28,888

Index Scan using idx_lease_terms_master on lease_terms lt_1 (cost=0.29..8.30 rows=1 width=16) (actual time=0.002..0.010 rows=13 loops=28,888)

  • Index Cond: ((cid = us.cid) AND (lease_term_structure_id = pcs.lease_term_structure_id))
  • Filter: (deleted_on IS NULL)
  • Rows Removed by Filter: 2
25. 173.328 375.544 ↓ 3.0 3 375,544

Materialize (cost=0.29..8.32 rows=1 width=16) (actual time=0.001..0.001 rows=3 loops=375,544)

26. 202.216 202.216 ↓ 3.0 3 28,888

Index Scan using ca_lease_start_windows_idx on lease_start_windows lsw_2 (cost=0.29..8.31 rows=1 width=16) (actual time=0.005..0.007 rows=3 loops=28,888)

  • Index Cond: ((cid = us.cid) AND (property_id = us.property_id) AND (end_date >= (generate_series.generate_series)::date))
  • Filter: ((deleted_on IS NULL) AND (is_active IS TRUE))
27. 0.019 339.680 ↓ 93.0 93 1

Sort (cost=153,307.55..153,307.55 rows=1 width=52) (actual time=339.665..339.680 rows=93 loops=1)

  • Sort Key: st.unit_space_id
  • Sort Method: quicksort Memory: 25kB
28. 0.016 339.661 ↓ 2.0 2 1

Subquery Scan on st (cost=153,307.46..153,307.54 rows=1 width=52) (actual time=339.645..339.661 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
29. 0.044 339.645 ↓ 12.0 12 1

WindowAgg (cost=153,307.46..153,307.52 rows=1 width=64) (actual time=339.624..339.645 rows=12 loops=1)

30. 0.063 339.601 ↓ 12.0 12 1

Sort (cost=153,307.46..153,307.47 rows=1 width=622) (actual time=339.600..339.601 rows=12 loops=1)

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

Bitmap Heap Scan on scheduled_tasks st_1 (cost=7,074.72..153,307.45 rows=1 width=622) (actual time=59.405..339.538 rows=12 loops=1)

  • Recheck Cond: (cid = 235)
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL) AND (property_id = ANY ('{719642}'::integer[])) AND (scheduled_task_type_id = 11) AND (property_id = 719,642))
  • Rows Removed by Filter: 356,262
  • Heap Blocks: exact=88,485
32. 38.402 38.402 ↓ 1.0 357,695 1

Bitmap Index Scan on pk_scheduled_tasks (cost=0.00..7,074.72 rows=352,839 width=0) (actual time=38.402..38.402 rows=357,695 loops=1)

  • Index Cond: (cid = 235)
33. 0.082 0.244 ↓ 368.5 737 1

Sort (cost=1,500.72..1,500.73 rows=2 width=60) (actual time=0.171..0.244 rows=737 loops=1)

  • Sort Key: mr.unit_space_id
  • Sort Method: quicksort Memory: 27kB
34. 0.028 0.162 ↓ 4.5 9 1

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

35. 0.017 0.080 ↓ 4.5 9 1

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

  • Filter: ((COALESCE(ms.maintenance_status_type_id, 0) <> 2) AND (COALESCE(ms.maintenance_status_type_id, 0) <> 4))
36. 0.045 0.045 ↓ 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.026..0.045 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
37. 0.018 0.018 ↑ 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.002..0.002 rows=1 loops=9)

  • Index Cond: ((cid = mr.cid) AND (cid = 235) AND (id = mr.maintenance_status_id))
38. 0.054 0.054 ↑ 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.006..0.006 rows=1 loops=9)

  • Index Cond: (maintenance_request_id = mr.id)
39. 1.283 6.211 ↓ 2,238.8 13,433 1

Sort (cost=164.40..164.41 rows=6 width=72) (actual time=5.040..6.211 rows=13,433 loops=1)

  • Sort Key: cl.unit_space_id
  • Sort Method: quicksort Memory: 174kB
40. 1.083 4.928 ↓ 24.5 147 1

Nested Loop Left Join (cost=2.95..164.32 rows=6 width=72) (actual time=0.160..4.928 rows=147 loops=1)

41. 0.060 3.551 ↓ 24.5 147 1

Nested Loop Left Join (cost=2.67..114.40 rows=6 width=100) (actual time=0.131..3.551 rows=147 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
42. 0.092 3.197 ↓ 24.5 147 1

Nested Loop (cost=2.38..112.34 rows=6 width=96) (actual time=0.113..3.197 rows=147 loops=1)

43. 0.134 2.811 ↓ 147.0 147 1

Nested Loop Left Join (cost=2.10..112.01 rows=1 width=88) (actual time=0.098..2.811 rows=147 loops=1)

44. 0.082 2.236 ↓ 147.0 147 1

Nested Loop Left Join (cost=1.68..103.56 rows=1 width=86) (actual time=0.082..2.236 rows=147 loops=1)

45. 0.029 1.713 ↓ 147.0 147 1

Nested Loop (cost=1.26..95.11 rows=1 width=82) (actual time=0.066..1.713 rows=147 loops=1)

  • Join Filter: ((cl.id = li.lease_id) AND (cl.active_lease_interval_id = li.id))
46. 0.118 1.096 ↓ 147.0 147 1

Nested Loop (cost=0.84..94.39 rows=1 width=70) (actual time=0.050..1.096 rows=147 loops=1)

47. 0.354 0.354 ↓ 31.2 156 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.033..0.354 rows=156 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
48. 0.624 0.624 ↑ 1.0 1 156

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.004..0.004 rows=1 loops=156)

  • 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
49. 0.588 0.588 ↑ 1.0 1 147

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

  • Index Cond: (id = ca.lease_interval_id)
  • Filter: ((cid = 235) AND (ca.lease_id = lease_id))
50. 0.441 0.441 ↑ 1.0 1 147

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.003..0.003 rows=1 loops=147)

  • Index Cond: (lease_id = cl.id)
  • Filter: ((cid = 235) AND (cl.cid = cid) AND (unit_space_id = cl.unit_space_id))
51. 0.441 0.441 ↑ 1.0 1 147

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

  • Index Cond: ((cid = cl.cid) AND (cid = 235) AND (id = cl.primary_customer_id))
52. 0.294 0.294 ↑ 1.0 1 147

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

  • Index Cond: ((cid = 235) AND (id = li.lease_term_id))
53. 0.294 0.294 ↑ 1.0 1 147

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=147)

  • 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))
54. 0.294 0.294 ↑ 1.0 1 147

Index Scan using ca_organization_contract_unit_spaces_idx on organization_contract_unit_spaces ocus (cost=0.28..8.31 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=147)

  • Index Cond: ((cid = cl.cid) AND (cid = 235) AND (organization_contract_id = lsw.organization_contract_id) AND (unit_space_id = cl.unit_space_id))
55. 2.567 28.828 ↓ 17,785.0 17,785 1

Sort (cost=60.36..60.37 rows=1 width=48) (actual time=27.315..28.828 rows=17,785 loops=1)

  • Sort Key: ocus_1.unit_space_id
  • Sort Method: quicksort Memory: 1,071kB
56. 7.999 26.261 ↓ 2,861.0 2,861 1

Nested Loop (cost=5.35..60.35 rows=1 width=48) (actual time=0.088..26.261 rows=2,861 loops=1)

57. 1.028 12.540 ↓ 2,861.0 2,861 1

Nested Loop (cost=4.93..51.91 rows=1 width=36) (actual time=0.071..12.540 rows=2,861 loops=1)

58. 0.571 5.790 ↓ 2,861.0 2,861 1

Nested Loop (cost=4.64..43.59 rows=1 width=32) (actual time=0.054..5.790 rows=2,861 loops=1)

59. 0.098 3.107 ↓ 352.0 352 1

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

60. 0.069 0.069 ↓ 196.0 196 1

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

  • Filter: (cid = 235)
  • Rows Removed by Filter: 68
61. 2.940 2.940 ↓ 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.010..0.015 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
62. 1.408 2.112 ↓ 1.1 8 352

Bitmap Heap Scan on organization_contract_unit_spaces ocus_1 (cost=4.36..28.89 rows=7 width=12) (actual time=0.004..0.006 rows=8 loops=352)

  • Recheck Cond: ((cid = 235) AND (organization_contract_id = oc.id))
  • Filter: (deleted_on IS NULL)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=359
63. 0.704 0.704 ↓ 1.1 9 352

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

  • Index Cond: ((cid = 235) AND (organization_contract_id = oc.id))
64. 5.722 5.722 ↑ 1.0 1 2,861

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=2,861)

  • Index Cond: ((cid = 235) AND (id = oc.lease_start_window_id))
  • Filter: ((deleted_on IS NULL) AND (is_active IS TRUE))
65. 5.722 5.722 ↑ 1.0 1 2,861

Index Scan using pk_customers on customers c_1 (cost=0.42..8.44 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=2,861)

  • Index Cond: ((cid = 235) AND (id = o.customer_id))