explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7nj1 : Optimization for: Optimization for: plan #jCid; plan #98Jy

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 341.929 17,744.486 ↓ 28.4 28,428 1

Hash Left Join (cost=153,707.84..181,542.99 rows=1,000 width=88) (actual time=3,103.525..17,744.486 rows=28,428 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. 503.666 14,578.681 ↓ 28.4 28,428 1

Nested Loop Left Join (cost=400.28..28,154.17 rows=1,000 width=245) (actual time=279.604..14,578.681 rows=28,428 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,147,499
3. 43.107 13,733.879 ↓ 28.4 28,428 1

Nested Loop Left Join (cost=397.33..27,779.83 rows=1,000 width=193) (actual time=195.975..13,733.879 rows=28,428 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: 255,852
4. 7,201.145 13,662.344 ↓ 28.4 28,428 1

Nested Loop Left Join (cost=396.19..26,199.11 rows=1,000 width=161) (actual time=188.716..13,662.344 rows=28,428 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: 81,331,486
5. 19.342 1,855.863 ↓ 28.4 28,428 1

Nested Loop Left Join (cost=390.85..26,111.25 rows=1,000 width=129) (actual time=151.388..1,855.863 rows=28,428 loops=1)

6. 47.017 244.553 ↓ 28.4 28,428 1

Hash Left Join (cost=365.77..1,001.25 rows=1,000 width=65) (actual time=148.241..244.553 rows=28,428 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. 11.985 51.758 ↓ 28.4 28,428 1

Nested Loop (cost=364.88..987.85 rows=1,000 width=53) (actual time=2.419..51.758 rows=28,428 loops=1)

8. 1.115 30.503 ↓ 309.0 309 1

Nested Loop (cost=364.87..967.84 rows=1 width=45) (actual time=2.379..30.503 rows=309 loops=1)

9. 0.802 4.977 ↓ 13.4 309 1

Hash Join (cost=364.58..789.14 rows=23 width=41) (actual time=2.081..4.977 rows=309 loops=1)

  • Hash Cond: (us.property_floorplan_id = pf.id)
10. 2.162 2.237 ↓ 3.9 309 1

Bitmap Heap Scan on unit_spaces us (cost=5.66..430.01 rows=80 width=41) (actual time=0.110..2.237 rows=309 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=171
11. 0.075 0.075 ↓ 2.9 357 1

Bitmap Index Scan on idx_unit_spaces_company_property_id (cost=0.00..5.64 rows=122 width=0) (actual time=0.075..0.075 rows=357 loops=1)

  • Index Cond: ((cid = 235) AND (property_id = 719,642))
12. 0.370 1.938 ↑ 1.0 2,321 1

Hash (cost=329.67..329.67 rows=2,340 width=8) (actual time=1.938..1.938 rows=2,321 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 123kB
13. 1.380 1.568 ↑ 1.0 2,321 1

Bitmap Heap Scan on property_floorplans pf (cost=50.42..329.67 rows=2,340 width=8) (actual time=0.210..1.568 rows=2,321 loops=1)

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

Bitmap Index Scan on pk_property_floorplans (cost=0.00..49.83 rows=2,340 width=0) (actual time=0.188..0.188 rows=2,323 loops=1)

  • Index Cond: (cid = 235)
15. 24.411 24.411 ↑ 1.0 1 309

Index Scan using pk_property_units on property_units pu (cost=0.29..7.77 rows=1 width=16) (actual time=0.079..0.079 rows=1 loops=309)

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

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

17. 6.505 145.778 ↓ 26,588.0 26,588 1

Hash (cost=0.88..0.88 rows=1 width=24) (actual time=145.777..145.778 rows=26,588 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,711kB
18. 139.273 139.273 ↓ 26,588.0 26,588 1

Function Scan on load_available_unit_spaces laus (cost=0.25..0.88 rows=1 width=24) (actual time=134.438..139.273 rows=26,588 loops=1)

  • Filter: (cid = 235)
19. 397.992 1,591.968 ↑ 1.0 1 28,428

Aggregate (cost=25.07..25.09 rows=1 width=64) (actual time=0.055..0.056 rows=1 loops=28,428)

20. 255.852 1,193.976 ↓ 2.8 14 28,428

Nested Loop Left Join (cost=0.86..25.05 rows=5 width=8) (actual time=0.021..0.042 rows=14 loops=28,428)

  • 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. 85.284 568.560 ↓ 2.6 13 28,428

Nested Loop (cost=0.57..16.62 rows=5 width=16) (actual time=0.009..0.020 rows=13 loops=28,428)

22. 113.712 113.712 ↑ 1.0 1 28,428

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

  • Index Cond: (property_id = us.property_id)
  • Filter: (cid = us.cid)
23. 369.564 369.564 ↓ 13.0 13 28,428

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

  • 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. 142.140 369.564 ↓ 3.0 3 369,564

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

25. 227.424 227.424 ↓ 3.0 3 28,428

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.006..0.008 rows=3 loops=28,428)

  • 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))
26. 4,567.666 4,605.336 ↓ 2,861.0 2,861 28,428

Materialize (cost=5.35..60.37 rows=1 width=48) (actual time=0.000..0.162 rows=2,861 loops=28,428)

27. 6.814 37.670 ↓ 2,861.0 2,861 1

Nested Loop (cost=5.35..60.36 rows=1 width=48) (actual time=1.781..37.670 rows=2,861 loops=1)

28. 2.318 19.412 ↓ 2,861.0 2,861 1

Nested Loop (cost=4.93..51.92 rows=1 width=36) (actual time=1.188..19.412 rows=2,861 loops=1)

29. 0.676 5.650 ↓ 2,861.0 2,861 1

Nested Loop (cost=4.64..43.60 rows=1 width=32) (actual time=0.101..5.650 rows=2,861 loops=1)

30. 0.207 2.862 ↓ 352.0 352 1

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

31. 0.107 0.107 ↓ 196.0 196 1

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

  • Filter: (cid = 235)
  • Rows Removed by Filter: 68
32. 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
33. 1.408 2.112 ↑ 1.0 8 352

Bitmap Heap Scan on organization_contract_unit_spaces ocus_1 (cost=4.36..28.90 rows=8 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
34. 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))
35. 11.444 11.444 ↑ 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.004..0.004 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))
36. 11.444 11.444 ↑ 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.004..0.004 rows=1 loops=2,861)

  • Index Cond: ((cid = 235) AND (id = o.customer_id))
37. 21.189 28.428 ↓ 4.5 9 28,428

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

38. 0.051 7.239 ↓ 4.5 9 1

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

39. 0.022 3.399 ↓ 4.5 9 1

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

  • Filter: ((COALESCE(ms.maintenance_status_type_id, 0) <> 2) AND (COALESCE(ms.maintenance_status_type_id, 0) <> 4))
40. 3.323 3.323 ↓ 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.997..3.323 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
41. 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=12) (actual time=0.006..0.006 rows=1 loops=9)

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

  • Index Cond: (maintenance_request_id = mr.id)
43. 257.924 341.136 ↓ 24.3 146 28,428

Materialize (cost=2.95..164.35 rows=6 width=72) (actual time=0.000..0.012 rows=146 loops=28,428)

44. 2.074 83.212 ↓ 24.3 146 1

Nested Loop Left Join (cost=2.95..164.32 rows=6 width=72) (actual time=2.254..83.212 rows=146 loops=1)

45. 0.202 80.554 ↓ 24.3 146 1

Nested Loop Left Join (cost=2.67..114.40 rows=6 width=100) (actual time=2.200..80.554 rows=146 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
46. 0.295 79.914 ↓ 24.3 146 1

Nested Loop (cost=2.38..112.34 rows=6 width=96) (actual time=2.159..79.914 rows=146 loops=1)

47. 0.325 77.867 ↓ 146.0 146 1

Nested Loop Left Join (cost=2.10..112.01 rows=1 width=88) (actual time=1.822..77.867 rows=146 loops=1)

48. 0.258 73.892 ↓ 146.0 146 1

Nested Loop Left Join (cost=1.68..103.56 rows=1 width=86) (actual time=1.797..73.892 rows=146 loops=1)

49. 0.232 54.946 ↓ 146.0 146 1

Nested Loop (cost=1.26..95.11 rows=1 width=82) (actual time=1.296..54.946 rows=146 loops=1)

  • Join Filter: ((cl.id = li.lease_id) AND (cl.active_lease_interval_id = li.id))
50. 0.209 23.908 ↓ 146.0 146 1

Nested Loop (cost=0.84..94.39 rows=1 width=70) (actual time=0.649..23.908 rows=146 loops=1)

51. 1.379 1.379 ↓ 31.0 155 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.064..1.379 rows=155 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
52. 22.320 22.320 ↑ 1.0 1 155

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.144..0.144 rows=1 loops=155)

  • 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
53. 30.806 30.806 ↑ 1.0 1 146

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

  • Index Cond: (id = ca.lease_interval_id)
  • Filter: ((cid = 235) AND (ca.lease_id = lease_id))
54. 18.688 18.688 ↑ 1.0 1 146

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.128..0.128 rows=1 loops=146)

  • Index Cond: (lease_id = cl.id)
  • Filter: ((cid = 235) AND (cl.cid = cid) AND (unit_space_id = cl.unit_space_id))
55. 3.650 3.650 ↑ 1.0 1 146

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

  • Index Cond: ((cid = cl.cid) AND (cid = 235) AND (id = cl.primary_customer_id))
56. 1.752 1.752 ↑ 1.0 1 146

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

  • Index Cond: ((cid = 235) AND (id = li.lease_term_id))
57. 0.438 0.438 ↑ 1.0 1 146

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

  • 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))
58. 0.584 0.584 ↑ 1.0 1 146

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.003..0.004 rows=1 loops=146)

  • Index Cond: ((cid = cl.cid) AND (cid = 235) AND (organization_contract_id = lsw.organization_contract_id) AND (unit_space_id = cl.unit_space_id))
59. 0.007 2,823.876 ↓ 2.0 2 1

Hash (cost=153,307.54..153,307.54 rows=1 width=52) (actual time=2,823.875..2,823.876 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
60. 0.023 2,823.869 ↓ 2.0 2 1

Subquery Scan on st (cost=153,307.46..153,307.54 rows=1 width=52) (actual time=2,823.850..2,823.869 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
61. 0.070 2,823.846 ↓ 12.0 12 1

WindowAgg (cost=153,307.46..153,307.52 rows=1 width=64) (actual time=2,823.809..2,823.846 rows=12 loops=1)

62. 0.086 2,823.776 ↓ 12.0 12 1

Sort (cost=153,307.46..153,307.47 rows=1 width=622) (actual time=2,823.775..2,823.776 rows=12 loops=1)

  • Sort Key: ((st_1.details ->> 'unit_space_id'::text)), st_1.start_on
  • Sort Method: quicksort Memory: 26kB
63. 2,767.911 2,823.690 ↓ 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=87.945..2,823.690 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,484
64. 55.779 55.779 ↓ 1.0 357,694 1

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

  • Index Cond: (cid = 235)