explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QfEA : Optimization for: Optimization for: Optimization for: NoRateAssociation; plan #Ryyp - Index on ocus; plan #DkNM - update lsw; plan #4fdl -revert lsw

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 327.329 17,185.524 ↓ 28.9 28,888 1

Hash Left Join (cost=417.04..35,118.90 rows=1,000 width=88) (actual time=176.788..17,185.524 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. 511.986 16,854.905 ↓ 28.9 28,888 1

Nested Loop Left Join (cost=408.48..35,029.08 rows=1,000 width=245) (actual time=173.458..16,854.905 rows=28,888 loops=1)

  • Join Filter: ((cl.cid = us.cid) AND (cl.property_id = us.property_id) AND (cl.unit_space_id = us.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: 4,243,455
3. 34.099 16,082.927 ↓ 28.9 28,888 1

Nested Loop Left Join (cost=405.53..34,654.74 rows=1,000 width=193) (actual time=163.031..16,082.927 rows=28,888 loops=1)

  • Join Filter: ((mr.cid = us.cid) AND (mr.property_id = us.property_id) AND (mr.unit_space_id = us.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: 259,992
4. 7,087.404 16,019.940 ↓ 28.9 28,888 1

Nested Loop Left Join (cost=404.40..33,074.02 rows=1,000 width=161) (actual time=162.697..16,019.940 rows=28,888 loops=1)

  • Join Filter: ((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: 82,647,546
5. 34.005 4,281.568 ↓ 28.9 28,888 1

Nested Loop Left Join (cost=399.05..32,986.17 rows=1,000 width=129) (actual time=134.716..4,281.568 rows=28,888 loops=1)

6. 44.053 203.243 ↓ 28.9 28,888 1

Hash Left Join (cost=367.12..1,018.17 rows=1,000 width=65) (actual time=134.438..203.243 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))
7. 10.669 34.604 ↓ 28.9 28,888 1

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

8. 1.205 17.655 ↓ 314.0 314 1

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

9. 0.821 12.368 ↓ 13.7 314 1

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

  • Hash Cond: (us.property_floorplan_id = pf.id)
10. 1.825 1.859 ↓ 3.8 314 1

Bitmap Heap Scan on unit_spaces us (cost=5.71..446.28 rows=83 width=41) (actual time=0.058..1.859 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
11. 0.034 0.034 ↓ 2.9 362 1

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

  • Index Cond: ((cid = 235) AND (property_id = 719,642))
12. 0.424 9.688 ↑ 1.0 2,322 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 123kB
13. 8.034 9.264 ↑ 1.0 2,322 1

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

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

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

  • Index Cond: (cid = 235)
15. 4.082 4.082 ↑ 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.013..0.013 rows=1 loops=314)

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

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

17. 5.403 124.586 ↓ 27,048.0 27,048 1

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

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

  • Filter: (cid = 235)
19. 375.544 4,044.320 ↑ 1.0 1 28,888

Aggregate (cost=31.93..31.95 rows=1 width=64) (actual time=0.140..0.140 rows=1 loops=28,888)

20. 115.552 3,668.776 ↓ 2.8 14 28,888

Nested Loop Left Join (cost=11.72..31.90 rows=5 width=8) (actual time=0.106..0.127 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
21. 86.664 548.872 ↓ 2.6 13 28,888

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

22. 115.552 115.552 ↑ 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.003..0.004 rows=1 loops=28,888)

  • Index Cond: (property_id = us.property_id)
  • Filter: (cid = us.cid)
23. 346.656 346.656 ↓ 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.003..0.012 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
24. 288.880 3,004.352 ↓ 3.0 3 375,544

Materialize (cost=11.15..15.18 rows=1 width=16) (actual time=0.006..0.008 rows=3 loops=375,544)

25. 808.864 2,715.472 ↓ 3.0 3 28,888

Bitmap Heap Scan on lease_start_windows lsw_2 (cost=11.15..15.17 rows=1 width=16) (actual time=0.070..0.094 rows=3 loops=28,888)

  • Recheck Cond: ((property_id = us.property_id) AND (cid = us.cid))
  • Filter: ((deleted_on IS NULL) AND (is_active IS TRUE) AND (end_date >= (generate_series.generate_series)::date))
  • Rows Removed by Filter: 85
  • Heap Blocks: exact=144,440
26. 57.776 1,906.608 ↓ 0.0 0 28,888

BitmapAnd (cost=11.15..11.15 rows=1 width=0) (actual time=0.066..0.066 rows=0 loops=28,888)

27. 144.440 144.440 ↓ 11.0 88 28,888

Bitmap Index Scan on idx_lease_start_windows_property_id (cost=0.00..4.35 rows=8 width=0) (actual time=0.005..0.005 rows=88 loops=28,888)

  • Index Cond: (property_id = us.property_id)
28. 1,704.392 1,704.392 ↓ 5.4 1,620 28,888

Bitmap Index Scan on pk_lease_start_windows (cost=0.00..6.55 rows=302 width=0) (actual time=0.059..0.059 rows=1,620 loops=28,888)

  • Index Cond: (cid = us.cid)
29. 4,623.659 4,650.968 ↓ 2,861.0 2,861 28,888

Materialize (cost=5.35..60.36 rows=1 width=48) (actual time=0.000..0.161 rows=2,861 loops=28,888)

30. 7.632 27.309 ↓ 2,861.0 2,861 1

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

31. 0.427 13.955 ↓ 2,861.0 2,861 1

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

32. 0.767 7.806 ↓ 2,861.0 2,861 1

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

33. 0.162 3.167 ↓ 352.0 352 1

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

34. 0.457 0.457 ↓ 196.0 196 1

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

  • Filter: (cid = 235)
  • Rows Removed by Filter: 68
35. 2.548 2.548 ↓ 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.009..0.013 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
36. 2.464 3.872 ↓ 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.008..0.011 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
37. 1.408 1.408 ↓ 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.004..0.004 rows=9 loops=352)

  • Index Cond: ((cid = 235) AND (organization_contract_id = oc.id))
38. 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))
39. 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))
40. 28.570 28.888 ↓ 4.5 9 28,888

Materialize (cost=1.14..1,500.72 rows=2 width=60) (actual time=0.000..0.001 rows=9 loops=28,888)

41. 0.025 0.318 ↓ 4.5 9 1

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

42. 0.014 0.104 ↓ 4.5 9 1

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

  • Filter: ((COALESCE(ms.maintenance_status_type_id, 0) <> 2) AND (COALESCE(ms.maintenance_status_type_id, 0) <> 4))
43. 0.072 0.072 ↓ 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.052..0.072 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
44. 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))
45. 0.189 0.189 ↑ 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.021..0.021 rows=1 loops=9)

  • Index Cond: (maintenance_request_id = mr.id)
46. 249.839 259.992 ↓ 24.5 147 28,888

Materialize (cost=2.95..164.35 rows=6 width=72) (actual time=0.000..0.009 rows=147 loops=28,888)

47. 1.430 10.153 ↓ 24.5 147 1

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

48. 0.100 8.429 ↓ 24.5 147 1

Nested Loop Left Join (cost=2.67..114.40 rows=6 width=100) (actual time=0.138..8.429 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
49. 0.184 8.035 ↓ 24.5 147 1

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

50. 0.137 7.557 ↓ 147.0 147 1

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

51. 0.032 6.538 ↓ 147.0 147 1

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

52. 0.138 3.272 ↓ 147.0 147 1

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

  • Join Filter: ((cl.id = li.lease_id) AND (cl.active_lease_interval_id = li.id))
53. 0.045 1.811 ↓ 147.0 147 1

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

54. 0.362 0.362 ↓ 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.036..0.362 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
55. 1.404 1.404 ↑ 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.008..0.009 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
56. 1.323 1.323 ↑ 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.009..0.009 rows=1 loops=147)

  • Index Cond: (id = ca.lease_interval_id)
  • Filter: ((cid = 235) AND (ca.lease_id = lease_id))
57. 3.234 3.234 ↑ 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.021..0.022 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))
58. 0.882 0.882 ↑ 1.0 1 147

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

  • Index Cond: ((cid = cl.cid) AND (cid = 235) AND (id = cl.primary_customer_id))
59. 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))
60. 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))
61. 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))
62. 0.006 3.290 ↓ 2.0 2 1

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

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

Subquery Scan on st (cost=8.46..8.54 rows=1 width=52) (actual time=3.269..3.284 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
64. 0.038 3.272 ↓ 12.0 12 1

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

65. 0.055 3.234 ↓ 12.0 12 1

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

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