explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jCid

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 294.501 17,479.116 ↓ 28.4 28,428 1

Hash Left Join (cost=153,946.81..188,673.31 rows=1,000 width=88) (actual time=818.357..17,479.116 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. 475.211 16,770.871 ↓ 28.4 28,428 1

Nested Loop Left Join (cost=639.25..35,284.49 rows=1,000 width=245) (actual time=404.575..16,770.871 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. 37.152 16,039.808 ↓ 28.4 28,428 1

Nested Loop Left Join (cost=411.11..34,734.25 rows=1,000 width=193) (actual time=396.240..16,039.808 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. 6,971.586 15,974.228 ↓ 28.4 28,428 1

Nested Loop Left Join (cost=409.98..33,153.53 rows=1,000 width=161) (actual time=390.194..15,974.228 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,274,630
5. 19.989 4,226.738 ↓ 28.4 28,428 1

Nested Loop Left Join (cost=397.62..32,916.14 rows=1,000 width=129) (actual time=130.073..4,226.738 rows=28,428 loops=1)

6. 42.657 198.401 ↓ 28.4 28,428 1

Hash Left Join (cost=365.70..963.14 rows=1,000 width=65) (actual time=129.781..198.401 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. 10.227 28.248 ↓ 28.4 28,428 1

Nested Loop (cost=364.81..949.74 rows=1,000 width=53) (actual time=2.250..28.248 rows=28,428 loops=1)

8. 1.194 10.296 ↓ 309.0 309 1

Nested Loop (cost=364.80..929.73 rows=1 width=45) (actual time=2.219..10.296 rows=309 loops=1)

9. 0.766 4.776 ↓ 14.7 309 1

Hash Join (cost=364.51..766.22 rows=21 width=41) (actual time=2.200..4.776 rows=309 loops=1)

  • Hash Cond: (us.property_floorplan_id = pf.id)
10. 1.850 1.903 ↓ 4.1 309 1

Bitmap Heap Scan on unit_spaces us (cost=5.59..407.10 rows=75 width=41) (actual time=0.081..1.903 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.053 0.053 ↓ 3.1 357 1

Bitmap Index Scan on idx_unit_spaces_company_property_id (cost=0.00..5.57 rows=115 width=0) (actual time=0.053..0.053 rows=357 loops=1)

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

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

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

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

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

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

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

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

17. 5.310 127.496 ↓ 26,588.0 26,588 1

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

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,711kB
18. 122.186 122.186 ↓ 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=117.985..122.186 rows=26,588 loops=1)

  • Filter: (cid = 235)
19. 369.564 4,008.348 ↑ 1.0 1 28,428

Aggregate (cost=31.92..31.93 rows=1 width=64) (actual time=0.141..0.141 rows=1 loops=28,428)

20. 170.568 3,638.784 ↓ 2.8 14 28,428

Nested Loop Left Join (cost=11.70..31.89 rows=5 width=8) (actual time=0.109..0.128 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. 113.712 511.704 ↓ 2.6 13 28,428

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

22. 85.284 85.284 ↑ 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.003..0.003 rows=1 loops=28,428)

  • Index Cond: (property_id = us.property_id)
  • Filter: (cid = us.cid)
23. 312.708 312.708 ↓ 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.003..0.011 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. 170.568 2,956.512 ↓ 3.0 3 369,564

Materialize (cost=11.14..15.16 rows=1 width=16) (actual time=0.006..0.008 rows=3 loops=369,564)

25. 767.556 2,785.944 ↓ 3.0 3 28,428

Bitmap Heap Scan on lease_start_windows lsw_2 (cost=11.14..15.16 rows=1 width=16) (actual time=0.074..0.098 rows=3 loops=28,428)

  • 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=142,140
26. 56.856 2,018.388 ↓ 0.0 0 28,428

BitmapAnd (cost=11.14..11.14 rows=1 width=0) (actual time=0.071..0.071 rows=0 loops=28,428)

27. 142.140 142.140 ↓ 11.0 88 28,428

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,428)

  • Index Cond: (property_id = us.property_id)
28. 1,819.392 1,819.392 ↓ 5.6 1,672 28,428

Bitmap Index Scan on pk_lease_start_windows (cost=0.00..6.54 rows=300 width=0) (actual time=0.064..0.064 rows=1,672 loops=28,428)

  • Index Cond: (cid = us.cid)
29. 4,472.193 4,775.904 ↓ 2,859.0 2,859 28,428

Materialize (cost=12.36..209.89 rows=1 width=48) (actual time=0.000..0.168 rows=2,859 loops=28,428)

30. 8.265 303.711 ↓ 2,859.0 2,859 1

Nested Loop (cost=12.36..209.89 rows=1 width=48) (actual time=0.994..303.711 rows=2,859 loops=1)

31. 0.915 289.728 ↓ 2,859.0 2,859 1

Nested Loop (cost=11.94..201.44 rows=1 width=36) (actual time=0.966..289.728 rows=2,859 loops=1)

32. 30.210 283.095 ↓ 2,859.0 2,859 1

Nested Loop (cost=11.66..193.13 rows=1 width=32) (actual time=0.954..283.095 rows=2,859 loops=1)

  • Join Filter: (oc.organization_id = o.id)
  • Rows Removed by Join Filter: 557,505
33. 0.437 0.437 ↓ 196.0 196 1

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

  • Filter: (cid = 235)
  • Rows Removed by Filter: 68
34. 108.955 252.448 ↓ 357.4 2,859 196

Hash Join (cost=11.66..186.73 rows=8 width=24) (actual time=0.014..1.288 rows=2,859 loops=196)

  • Hash Cond: (ocus_1.organization_contract_id = oc.id)
35. 143.276 143.276 ↑ 1.1 2,899 196

Seq Scan on organization_contract_unit_spaces ocus_1 (cost=0.00..166.91 rows=3,074 width=12) (actual time=0.012..0.731 rows=2,899 loops=196)

  • Filter: ((deleted_on IS NULL) AND (cid = 235))
  • Rows Removed by Filter: 4,060
36. 0.058 0.217 ↓ 351.0 351 1

Hash (cost=11.64..11.64 rows=1 width=20) (actual time=0.217..0.217 rows=351 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
37. 0.130 0.159 ↓ 351.0 351 1

Bitmap Heap Scan on organization_contracts oc (cost=4.30..11.64 rows=1 width=20) (actual time=0.039..0.159 rows=351 loops=1)

  • Recheck Cond: (cid = 235)
  • Filter: (organization_contract_status_type_id = ANY ('{1,2}'::integer[]))
  • Rows Removed by Filter: 48
  • Heap Blocks: exact=11
38. 0.029 0.029 ↓ 133.0 399 1

Bitmap Index Scan on ukey_organization_contracts_reservation_code (cost=0.00..4.30 rows=3 width=0) (actual time=0.029..0.029 rows=399 loops=1)

  • Index Cond: (cid = 235)
39. 5.718 5.718 ↑ 1.0 1 2,859

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,859)

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

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,859)

  • Index Cond: ((cid = 235) AND (id = o.customer_id))
41. 22.400 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)

42. 0.055 6.028 ↓ 4.5 9 1

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

43. 0.010 0.204 ↓ 4.5 9 1

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

  • Filter: ((COALESCE(ms.maintenance_status_type_id, 0) <> 2) AND (COALESCE(ms.maintenance_status_type_id, 0) <> 4))
44. 0.158 0.158 ↓ 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.069..0.158 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
45. 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))
46. 5.769 5.769 ↑ 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.641..0.641 rows=1 loops=9)

  • Index Cond: (maintenance_request_id = mr.id)
47. 247.672 255.852 ↓ 24.3 146 28,428

Materialize (cost=228.14..340.25 rows=6 width=72) (actual time=0.000..0.009 rows=146 loops=28,428)

48. 1.129 8.180 ↓ 24.3 146 1

Hash Left Join (cost=228.14..340.22 rows=6 width=72) (actual time=1.558..8.180 rows=146 loops=1)

  • Hash Cond: ((cl.cid = ocus.cid) AND (cl.unit_space_id = ocus.unit_space_id) AND (lsw.organization_contract_id = ocus.organization_contract_id))
49. 0.051 5.662 ↓ 24.3 146 1

Nested Loop Left Join (cost=2.67..114.39 rows=6 width=100) (actual time=0.124..5.662 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
50. 0.105 5.319 ↓ 24.3 146 1

Nested Loop (cost=2.38..112.33 rows=6 width=96) (actual time=0.110..5.319 rows=146 loops=1)

51. 0.164 4.922 ↓ 146.0 146 1

Nested Loop Left Join (cost=2.10..112.00 rows=1 width=88) (actual time=0.096..4.922 rows=146 loops=1)

52. 0.093 4.320 ↓ 146.0 146 1

Nested Loop Left Join (cost=1.68..103.55 rows=1 width=86) (actual time=0.080..4.320 rows=146 loops=1)

53. 0.130 2.475 ↓ 146.0 146 1

Nested Loop (cost=1.26..95.10 rows=1 width=82) (actual time=0.064..2.475 rows=146 loops=1)

  • Join Filter: ((cl.id = li.lease_id) AND (cl.active_lease_interval_id = li.id))
54. 0.064 1.177 ↓ 146.0 146 1

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

55. 0.338 0.338 ↓ 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.030..0.338 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
56. 0.775 0.775 ↑ 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.005..0.005 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
57. 1.168 1.168 ↑ 1.0 1 146

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

  • Index Cond: (id = ca.lease_interval_id)
  • Filter: ((cid = 235) AND (ca.lease_id = lease_id))
58. 1.752 1.752 ↑ 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.012..0.012 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))
59. 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))
60. 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))
61. 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))
62. 0.527 1.389 ↓ 1.0 3,352 1

Hash (cost=166.91..166.91 rows=3,346 width=16) (actual time=1.389..1.389 rows=3,352 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 190kB
63. 0.862 0.862 ↓ 1.0 3,352 1

Seq Scan on organization_contract_unit_spaces ocus (cost=0.00..166.91 rows=3,346 width=16) (actual time=0.025..0.862 rows=3,352 loops=1)

  • Filter: (cid = 235)
  • Rows Removed by Filter: 3,607
64. 0.005 413.744 ↓ 2.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
65. 0.017 413.739 ↓ 2.0 2 1

Subquery Scan on st (cost=153,307.46..153,307.54 rows=1 width=52) (actual time=413.725..413.739 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
66. 0.043 413.722 ↓ 12.0 12 1

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

67. 0.064 413.679 ↓ 12.0 12 1

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

  • Sort Key: ((st_1.details ->> 'unit_space_id'::text)), st_1.start_on
  • Sort Method: quicksort Memory: 26kB
68. 377.308 413.615 ↓ 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=56.265..413.615 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,472
69. 36.307 36.307 ↓ 1.0 357,690 1

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

  • Index Cond: (cid = 235)