explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 202.498 1,935.472 ↓ 28.4 28,428 1

Merge Left Join (cost=156,036.30..181,208.87 rows=1,000 width=88) (actual time=512.311..1,935.472 rows=28,428 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
2. 16.570 1,728.924 ↓ 28.4 28,428 1

Merge Left Join (cost=154,535.58..179,635.61 rows=1,000 width=213) (actual time=508.299..1,728.924 rows=28,428 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.351 1,703.174 ↓ 28.4 28,428 1

Merge Left Join (cost=154,371.18..179,468.67 rows=1,000 width=161) (actual time=500.254..1,703.174 rows=28,428 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))
4. 27.693 1,376.650 ↓ 28.4 28,428 1

Nested Loop Left Join (cost=1,063.63..26,158.58 rows=1,000 width=129) (actual time=182.091..1,376.650 rows=28,428 loops=1)

5. 22.112 211.837 ↓ 28.4 28,428 1

Merge Left Join (cost=1,038.56..1,048.58 rows=1,000 width=65) (actual time=181.924..211.837 rows=28,428 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)
6. 38.350 49.966 ↓ 28.4 28,428 1

Sort (cost=1,037.67..1,040.17 rows=1,000 width=53) (actual time=47.242..49.966 rows=28,428 loops=1)

  • Sort Key: us.id, ((generate_series.generate_series)::date)
  • Sort Method: quicksort Memory: 4,766kB
7. 6.406 11.616 ↓ 28.4 28,428 1

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

8. 0.207 3.356 ↓ 309.0 309 1

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

9. 0.122 1.913 ↓ 13.4 309 1

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

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

Bitmap Heap Scan on unit_spaces us (cost=5.66..430.01 rows=80 width=41) (actual time=0.058..0.606 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.034 0.034 ↓ 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.034..0.034 rows=357 loops=1)

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

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 123kB
13. 0.737 0.892 ↑ 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.174..0.892 rows=2,321 loops=1)

  • Recheck Cond: (cid = 235)
  • Heap Blocks: exact=164
14. 0.155 0.155 ↑ 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.155..0.155 rows=2,323 loops=1)

  • Index Cond: (cid = 235)
15. 1.236 1.236 ↑ 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.004..0.004 rows=1 loops=309)

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

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

17. 19.496 139.759 ↓ 26,588.0 26,588 1

Sort (cost=0.89..0.89 rows=1 width=24) (actual time=134.665..139.759 rows=26,588 loops=1)

  • Sort Key: laus.unit_space_id, laus.move_in_date
  • Sort Method: quicksort Memory: 2,846kB
18. 120.263 120.263 ↓ 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=116.153..120.263 rows=26,588 loops=1)

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

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

20. 142.140 881.268 ↓ 2.8 14 28,428

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

  • Join Filter: ((lt_1.lease_term_type_id = 2) AND (lt_1.cid = lsw_1.cid) AND (lsw_1.property_id = pcs.property_id) AND (lt_1.id = lsw_1.lease_term_id))
  • Rows Removed by Join Filter: 41
21. 56.856 369.564 ↓ 2.6 13 28,428

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

22. 56.856 56.856 ↑ 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.002..0.002 rows=1 loops=28,428)

  • Index Cond: (property_id = us.property_id)
  • Filter: (cid = us.cid)
23. 255.852 255.852 ↓ 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.002..0.009 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. 198.996 369.564 ↓ 3.0 3 369,564

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

25. 170.568 170.568 ↓ 3.0 3 28,428

Index Scan using ca_lease_start_windows_idx on lease_start_windows lsw_1 (cost=0.29..8.31 rows=1 width=16) (actual time=0.005..0.006 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. 0.019 318.173 ↓ 93.0 93 1

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

  • Sort Key: st.unit_space_id
  • Sort Method: quicksort Memory: 25kB
27. 0.018 318.154 ↓ 2.0 2 1

Subquery Scan on st (cost=153,307.46..153,307.54 rows=1 width=52) (actual time=318.138..318.154 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
28. 0.053 318.136 ↓ 12.0 12 1

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

29. 0.079 318.083 ↓ 12.0 12 1

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

  • Sort Key: ((st_1.details ->> 'unit_space_id'::text)), st_1.start_on
  • Sort Method: quicksort Memory: 26kB
30. 279.177 318.004 ↓ 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=58.903..318.004 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
31. 38.827 38.827 ↓ 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=38.827..38.827 rows=357,694 loops=1)

  • Index Cond: (cid = 235)
32. 1.267 9.180 ↓ 2,223.5 13,341 1

Sort (cost=164.40..164.41 rows=6 width=72) (actual time=8.039..9.180 rows=13,341 loops=1)

  • Sort Key: cl.unit_space_id
  • Sort Method: quicksort Memory: 173kB
33. 1.344 7.913 ↓ 24.3 146 1

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

34. 0.078 6.277 ↓ 24.3 146 1

Nested Loop Left Join (cost=2.67..114.40 rows=6 width=100) (actual time=0.145..6.277 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
35. 0.169 5.907 ↓ 24.3 146 1

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

36. 0.195 5.446 ↓ 146.0 146 1

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

37. 0.071 4.813 ↓ 146.0 146 1

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

38. 0.119 3.282 ↓ 146.0 146 1

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

  • Join Filter: ((cl.id = li.lease_id) AND (cl.active_lease_interval_id = li.id))
39. 0.127 1.411 ↓ 146.0 146 1

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

40. 0.354 0.354 ↓ 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.031..0.354 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
41. 0.930 0.930 ↑ 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.006..0.006 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
42. 1.752 1.752 ↑ 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.012..0.012 rows=1 loops=146)

  • Index Cond: (id = ca.lease_interval_id)
  • Filter: ((cid = 235) AND (ca.lease_id = lease_id))
43. 1.460 1.460 ↑ 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.010..0.010 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))
44. 0.438 0.438 ↑ 1.0 1 146

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

  • Index Cond: ((cid = cl.cid) AND (cid = 235) AND (id = cl.primary_customer_id))
45. 0.292 0.292 ↑ 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.002..0.002 rows=1 loops=146)

  • Index Cond: ((cid = 235) AND (id = li.lease_term_id))
46. 0.292 0.292 ↑ 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.002..0.002 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))
47. 0.292 0.292 ↑ 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.002..0.002 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))
48. 0.088 4.050 ↓ 368.5 737 1

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

  • Sort Key: mr.unit_space_id
  • Sort Method: quicksort Memory: 27kB
49. 0.081 3.962 ↓ 4.5 9 1

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

50. 0.025 0.146 ↓ 4.5 9 1

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

  • Filter: ((COALESCE(ms.maintenance_status_type_id, 0) <> 2) AND (COALESCE(ms.maintenance_status_type_id, 0) <> 4))
51. 0.085 0.085 ↓ 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.053..0.085 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
52. 0.036 0.036 ↑ 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.004..0.004 rows=1 loops=9)

  • Index Cond: ((cid = mr.cid) AND (cid = 235) AND (id = mr.maintenance_status_id))
53. 3.735 3.735 ↑ 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.414..0.415 rows=1 loops=9)

  • Index Cond: (maintenance_request_id = mr.id)