explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1LDB

Settings
# exclusive inclusive rows x rows loops node
1. 1,046.484 172,316.281 ↓ 58.2 58,240 1

Hash Left Join (cost=309,709.14..410,215.70 rows=1,000 width=88) (actual time=11,602.020..172,316.281 rows=58,240 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. 95,986.196 170,822.725 ↓ 58.2 58,240 1

Nested Loop Left Join (cost=160,389.31..260,814.60 rows=1,000 width=245) (actual time=11,154.903..170,822.725 rows=58,240 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: 865,207,861
3. 61.951 10,718.693 ↓ 58.2 58,236 1

Hash Left Join (cost=150,695.79..242,727.10 rows=1,000 width=193) (actual time=10,542.876..10,718.693 rows=58,236 loops=1)

  • Hash Cond: ((us.cid = oc.cid) AND (us.id = ocus_1.unit_space_id))
  • Join Filter: (((generate_series.generate_series)::date >= lsw_1.start_date) AND ((generate_series.generate_series)::date <= lsw_1.end_date))
  • Rows Removed by Join Filter: 3,550
4. 57.416 10,643.478 ↓ 58.2 58,236 1

Hash Right Join (cost=149,948.34..241,972.13 rows=1,000 width=161) (actual time=10,529.599..10,643.478 rows=58,236 loops=1)

  • Hash Cond: ((mr.cid = us.cid) AND (mr.property_id = us.property_id) AND (mr.unit_space_id = us.id))
  • Join Filter: (((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: 3,309
5. 1.106 3,246.962 ↑ 12.7 1,171 1

Hash Left Join (cost=116,881.58..208,237.99 rows=14,830 width=60) (actual time=1,587.863..3,246.962 rows=1,171 loops=1)

  • Hash Cond: ((mr.cid = ms.cid) AND (mr.maintenance_status_id = ms.id))
  • Filter: ((COALESCE(ms.maintenance_status_type_id, 0) <> 3) AND (COALESCE(ms.maintenance_status_type_id, 0) <> 4))
  • Rows Removed by Filter: 734
6. 462.307 3,245.168 ↑ 7.9 1,905 1

Hash Join (cost=116,804.05..208,081.44 rows=14,979 width=64) (actual time=1,587.157..3,245.168 rows=1,905 loops=1)

  • Hash Cond: (mrd.maintenance_request_id = mr.id)
7. 1,196.378 1,196.378 ↓ 1.6 4,128,419 1

Seq Scan on maintenance_request_details mrd (cost=0.00..84,514.89 rows=2,533,389 width=20) (actual time=0.435..1,196.378 rows=4,128,419 loops=1)

8. 0.962 1,586.483 ↑ 12.8 1,905 1

Hash (cost=116,498.93..116,498.93 rows=24,410 width=20) (actual time=1,586.482..1,586.483 rows=1,905 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 353kB
9. 1,585.521 1,585.521 ↑ 12.8 1,905 1

Index Scan using idx_maintenance_request_parent_maintenance_request_id on maintenance_requests mr (cost=0.43..116,498.93 rows=24,410 width=20) (actual time=1.525..1,585.521 rows=1,905 loops=1)

  • Index Cond: (parent_maintenance_request_id IS NULL)
  • Filter: ((cid IS NOT NULL) AND (deleted_on IS NULL) AND (cid = 235) AND (maintenance_request_type_id = 1))
  • Rows Removed by Filter: 2,110,229
10. 0.025 0.688 ↑ 1.0 79 1

Hash (cost=76.33..76.33 rows=80 width=12) (actual time=0.688..0.688 rows=79 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
11. 0.663 0.663 ↑ 1.0 79 1

Seq Scan on maintenance_statuses ms (cost=0.00..76.33 rows=80 width=12) (actual time=0.024..0.663 rows=79 loops=1)

  • Filter: (cid = 235)
  • Rows Removed by Filter: 589
12. 51.836 7,339.100 ↓ 58.2 58,236 1

Hash (cost=33,049.26..33,049.26 rows=1,000 width=129) (actual time=7,339.100..7,339.100 rows=58,236 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 18,116kB
13. 53.849 7,287.264 ↓ 58.2 58,236 1

Nested Loop Left Join (cost=45.96..33,049.26 rows=1,000 width=129) (actual time=372.772..7,287.264 rows=58,236 loops=1)

14. 69.495 478.039 ↓ 58.2 58,236 1

Hash Left Join (cost=13.97..1,021.26 rows=1,000 width=65) (actual time=371.872..478.039 rows=58,236 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))
15. 12.484 36.934 ↓ 58.2 58,236 1

Nested Loop (cost=1.13..995.91 rows=1,000 width=53) (actual time=0.220..36.934 rows=58,236 loops=1)

16. 1.040 17.487 ↓ 633.0 633 1

Nested Loop (cost=1.12..975.90 rows=1 width=45) (actual time=0.180..17.487 rows=633 loops=1)

17. 1.667 8.218 ↓ 23.4 633 1

Nested Loop (cost=0.71..759.22 rows=27 width=41) (actual time=0.123..8.218 rows=633 loops=1)

18. 2.120 2.120 ↓ 6.1 633 1

Index Scan using idx_unit_spaces_company_property_id on unit_spaces us (cost=0.42..459.80 rows=103 width=41) (actual time=0.069..2.120 rows=633 loops=1)

  • Index Cond: ((cid = 235) AND (property_id = ANY ('{550592}'::integer[])))
  • Filter: ((deleted_on IS NULL) AND (occupancy_type_id <> 2))
  • Rows Removed by Filter: 5
19. 4.431 4.431 ↑ 1.0 1 633

Index Only Scan using pk_property_floorplans on property_floorplans pf (cost=0.29..2.91 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=633)

  • Index Cond: ((cid = 235) AND (id = us.property_floorplan_id))
  • Heap Fetches: 633
20. 8.229 8.229 ↑ 1.0 1 633

Index Scan using pk_property_units on property_units pu (cost=0.42..8.02 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=633)

  • Index Cond: ((cid = 235) AND (id = us.property_unit_id))
  • Filter: (us.property_id = property_id)
21. 6.963 6.963 ↑ 10.9 92 633

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

22. 11.701 371.610 ↓ 10,543.2 52,716 1

Hash (cost=12.75..12.75 rows=5 width=24) (actual time=371.610..371.610 rows=52,716 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3,395kB
23. 359.909 359.909 ↓ 10,543.2 52,716 1

Function Scan on load_available_unit_spaces laus (cost=0.25..12.75 rows=5 width=24) (actual time=352.110..359.909 rows=52,716 loops=1)

  • Filter: (cid = 235)
24. 465.888 6,755.376 ↑ 1.0 1 58,236

Aggregate (cost=31.99..32.01 rows=1 width=64) (actual time=0.116..0.116 rows=1 loops=58,236)

25. 465.888 6,289.488 ↓ 2.4 12 58,236

Nested Loop Left Join (cost=11.78..31.96 rows=5 width=8) (actual time=0.084..0.108 rows=12 loops=58,236)

  • 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: 48
26. 58.236 524.124 ↓ 1.4 7 58,236

Nested Loop (cost=0.57..16.62 rows=5 width=16) (actual time=0.004..0.009 rows=7 loops=58,236)

27. 116.472 116.472 ↑ 1.0 1 58,236

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=58,236)

  • Index Cond: (property_id = us.property_id)
  • Filter: (cid = us.cid)
28. 349.416 349.416 ↓ 7.0 7 58,236

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.006 rows=7 loops=58,236)

  • Index Cond: ((cid = us.cid) AND (lease_term_structure_id = pcs.lease_term_structure_id))
  • Filter: (deleted_on IS NULL)
  • Rows Removed by Filter: 1
29. 232.944 5,299.476 ↓ 8.0 8 407,652

Materialize (cost=11.21..15.24 rows=1 width=16) (actual time=0.011..0.013 rows=8 loops=407,652)

30. 815.304 5,066.532 ↓ 8.0 8 58,236

Bitmap Heap Scan on lease_start_windows lsw_2 (cost=11.21..15.23 rows=1 width=16) (actual time=0.075..0.087 rows=8 loops=58,236)

  • 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: 37
  • Heap Blocks: exact=174,708
31. 116.472 4,251.228 ↓ 0.0 0 58,236

BitmapAnd (cost=11.21..11.21 rows=1 width=0) (actual time=0.073..0.073 rows=0 loops=58,236)

32. 174.708 174.708 ↓ 5.6 45 58,236

Bitmap Index Scan on idx_lease_start_windows_property_id (cost=0.00..4.35 rows=8 width=0) (actual time=0.003..0.003 rows=45 loops=58,236)

  • Index Cond: (property_id = us.property_id)
33. 3,960.048 3,960.048 ↓ 5.9 1,844 58,236

Bitmap Index Scan on pk_lease_start_windows (cost=0.00..6.61 rows=310 width=0) (actual time=0.068..0.068 rows=1,844 loops=58,236)

  • Index Cond: (cid = us.cid)
34. 1.150 13.264 ↓ 107.1 2,891 1

Hash (cost=747.05..747.05 rows=27 width=48) (actual time=13.264..13.264 rows=2,891 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 789kB
35. 0.653 12.114 ↓ 107.1 2,891 1

Hash Join (cost=566.46..747.05 rows=27 width=48) (actual time=8.959..12.114 rows=2,891 loops=1)

  • Hash Cond: (ocus_1.organization_contract_id = oc.id)
36. 2.556 2.556 ↑ 1.1 2,931 1

Seq Scan on organization_contract_unit_spaces ocus_1 (cost=0.00..168.62 rows=3,101 width=12) (actual time=0.040..2.556 rows=2,931 loops=1)

  • Filter: ((deleted_on IS NULL) AND (cid = 235))
  • Rows Removed by Filter: 4,102
37. 0.185 8.905 ↓ 75.6 378 1

Hash (cost=566.39..566.39 rows=5 width=48) (actual time=8.905..8.905 rows=378 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 106kB
38. 1.255 8.720 ↓ 75.6 378 1

Nested Loop (cost=58.47..566.39 rows=5 width=48) (actual time=1.262..8.720 rows=378 loops=1)

39. 0.320 3.307 ↓ 21.0 378 1

Nested Loop (cost=58.05..424.20 rows=18 width=32) (actual time=1.195..3.307 rows=378 loops=1)

40. 0.202 2.609 ↓ 15.8 378 1

Hash Join (cost=57.90..418.88 rows=24 width=24) (actual time=1.158..2.609 rows=378 loops=1)

  • Hash Cond: (lsw_1.id = oc.lease_start_window_id)
41. 1.295 1.405 ↑ 1.6 940 1

Bitmap Heap Scan on lease_start_windows lsw_1 (cost=32.78..388.00 rows=1,475 width=16) (actual time=0.137..1.405 rows=940 loops=1)

  • Recheck Cond: (cid = 235)
  • Filter: ((deleted_on IS NULL) AND (is_active IS TRUE))
  • Rows Removed by Filter: 674
  • Heap Blocks: exact=127
42. 0.110 0.110 ↓ 1.1 1,844 1

Bitmap Index Scan on pk_lease_start_windows (cost=0.00..32.41 rows=1,617 width=0) (actual time=0.110..0.110 rows=1,844 loops=1)

  • Index Cond: (cid = 235)
43. 0.077 1.002 ↓ 1.0 378 1

Hash (cost=20.49..20.49 rows=370 width=20) (actual time=1.002..1.002 rows=378 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
44. 0.925 0.925 ↓ 1.0 378 1

Seq Scan on organization_contracts oc (cost=0.00..20.49 rows=370 width=20) (actual time=0.440..0.925 rows=378 loops=1)

  • Filter: ((organization_contract_status_type_id = ANY ('{1,2}'::integer[])) AND (cid = 235))
  • Rows Removed by Filter: 203
45. 0.378 0.378 ↑ 1.0 1 378

Index Scan using pk_organizations on organizations o (cost=0.15..0.22 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=378)

  • Index Cond: ((cid = 235) AND (id = oc.organization_id))
46. 4.158 4.158 ↑ 1.0 1 378

Index Scan using pk_customers on customers c_1 (cost=0.42..7.90 rows=1 width=21) (actual time=0.011..0.011 rows=1 loops=378)

  • Index Cond: ((cid = 235) AND (id = o.customer_id))
47. 63,527.057 64,117.836 ↓ 2,971.4 14,857 58,236

Materialize (cost=9,693.52..17,912.51 rows=5 width=72) (actual time=0.001..1.101 rows=14,857 loops=58,236)

48. 111.865 590.779 ↓ 2,971.4 14,857 1

Nested Loop Left Join (cost=9,693.52..17,912.49 rows=5 width=72) (actual time=54.146..590.779 rows=14,857 loops=1)

  • Join Filter: (ocus.organization_contract_id = lsw.organization_contract_id)
  • Rows Removed by Join Filter: 835
49. 13.397 464.080 ↓ 2,966.8 14,834 1

Nested Loop Left Join (cost=9,693.24..17,910.81 rows=5 width=100) (actual time=53.770..464.080 rows=14,834 loops=1)

  • Join Filter: ((lt.cid = lsw.cid) AND (lt.id = lsw.lease_term_id) AND (lsw.property_id = cl.property_id))
  • Rows Removed by Join Filter: 10
50. 3.727 450.683 ↓ 2,966.8 14,834 1

Nested Loop (cost=9,692.95..17,909.12 rows=5 width=96) (actual time=53.764..450.683 rows=14,834 loops=1)

51. 17.092 417.288 ↓ 14,834.0 14,834 1

Nested Loop Left Join (cost=9,692.67..17,908.79 rows=1 width=88) (actual time=53.733..417.288 rows=14,834 loops=1)

52. 1.039 355.694 ↓ 14,834.0 14,834 1

Nested Loop Left Join (cost=9,692.25..17,906.65 rows=1 width=86) (actual time=53.689..355.694 rows=14,834 loops=1)

53. 14.541 295.323 ↓ 14,833.0 14,833 1

Nested Loop (cost=9,691.83..17,905.91 rows=1 width=82) (actual time=53.374..295.323 rows=14,833 loops=1)

  • Join Filter: ((cl.id = ca.lease_id) AND (cl.active_lease_interval_id = ca.lease_interval_id) AND (cl.unit_space_id = ca.unit_space_id))
  • Rows Removed by Join Filter: 3,944
54. 42.552 131.619 ↓ 21,309.0 21,309 1

Hash Join (cost=9,691.41..17,904.38 rows=1 width=82) (actual time=53.188..131.619 rows=21,309 loops=1)

  • Hash Cond: ((li.lease_id = cl.id) AND (li.id = cl.active_lease_interval_id))
55. 36.445 43.803 ↓ 1.0 136,696 1

Bitmap Heap Scan on lease_intervals li (cost=2,582.78..10,078.80 rows=136,562 width=28) (actual time=7.878..43.803 rows=136,696 loops=1)

  • Recheck Cond: (cid = 235)
  • Heap Blocks: exact=3,575
56. 7.358 7.358 ↓ 1.0 136,860 1

Bitmap Index Scan on pk_lease_intervals (cost=0.00..2,548.64 rows=136,562 width=0) (actual time=7.358..7.358 rows=136,860 loops=1)

  • Index Cond: (cid = 235)
57. 7.284 45.264 ↑ 1.2 21,309 1

Hash (cost=6,720.95..6,720.95 rows=25,845 width=54) (actual time=45.264..45.264 rows=21,309 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,087kB
58. 34.612 37.980 ↑ 1.2 21,309 1

Bitmap Heap Scan on cached_leases cl (cost=978.90..6,720.95 rows=25,845 width=54) (actual time=3.860..37.980 rows=21,309 loops=1)

  • Recheck Cond: (cid = 235)
  • Filter: (lease_status_type_id = ANY ('{3,4,5}'::integer[]))
  • Rows Removed by Filter: 29,786
  • Heap Blocks: exact=3,667
59. 3.368 3.368 ↑ 1.0 51,113 1

Bitmap Index Scan on pk_cached_leases (cost=0.00..972.44 rows=51,203 width=0) (actual time=3.367..3.368 rows=51,113 loops=1)

  • Index Cond: (cid = 235)
60. 149.163 149.163 ↑ 1.0 1 21,309

Index Scan using idx_cached_applications_lease_interval_id on cached_applications ca (cost=0.42..1.52 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=21,309)

  • Index Cond: (lease_interval_id = li.id)
  • Filter: ((cid = 235) AND (li.lease_id = lease_id))
61. 59.332 59.332 ↑ 1.0 1 14,833

Index Scan using idx_lease_unit_spaces_lease_id on lease_unit_spaces lus (cost=0.42..0.73 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=14,833)

  • Index Cond: (lease_id = cl.id)
  • Filter: ((cid = 235) AND (cl.cid = cid) AND (unit_space_id = cl.unit_space_id))
  • Rows Removed by Filter: 0
62. 44.502 44.502 ↑ 1.0 1 14,834

Index Scan using idx_customers_id on customers c (cost=0.42..2.13 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=14,834)

  • Index Cond: (id = cl.primary_customer_id)
  • Filter: ((cid = 235) AND (cl.cid = cid))
63. 29.668 29.668 ↑ 1.0 1 14,834

Index Scan using pk_lease_terms on lease_terms lt (cost=0.29..0.33 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=14,834)

  • Index Cond: ((cid = 235) AND (id = li.lease_term_id))
64. 0.000 0.000 ↓ 0.0 0 14,834

Index Scan using pk_lease_start_windows on lease_start_windows lsw (cost=0.29..0.32 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=14,834)

  • Index Cond: ((cid = 235) AND (id = li.lease_start_window_id))
  • Filter: ((deleted_on IS NULL) AND (is_active IS TRUE))
  • Rows Removed by Filter: 0
65. 14.834 14.834 ↓ 0.0 0 14,834

Index Scan using idx_organization_contract_unit_spaces_unit_space_id on organization_contract_unit_spaces ocus (cost=0.28..0.32 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=14,834)

  • Index Cond: (unit_space_id = cl.unit_space_id)
  • Filter: ((cid = 235) AND (cid = cl.cid))
66. 0.006 447.072 ↓ 2.0 2 1

Hash (cost=149,319.81..149,319.81 rows=1 width=52) (actual time=447.072..447.072 rows=2 loops=1)

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

Subquery Scan on st (cost=149,319.73..149,319.81 rows=1 width=52) (actual time=447.055..447.066 rows=2 loops=1)

  • Filter: ((st.scheduled_start_date <= '2020-12-16'::date) AND (st.scheduled_end_date >= '2020-09-16'::date))
  • Rows Removed by Filter: 4
68. 0.041 447.049 ↓ 6.0 6 1

WindowAgg (cost=149,319.73..149,319.79 rows=1 width=64) (actual time=447.036..447.049 rows=6 loops=1)

69. 0.115 447.008 ↓ 6.0 6 1

Sort (cost=149,319.73..149,319.74 rows=1 width=631) (actual time=447.007..447.008 rows=6 loops=1)

  • Sort Key: st_1.property_id, ((st_1.details ->> 'unit_space_id'::text)), st_1.start_on
  • Sort Method: quicksort Memory: 25kB
70. 406.129 446.893 ↓ 6.0 6 1

Bitmap Heap Scan on scheduled_tasks st_1 (cost=6,789.89..149,319.72 rows=1 width=631) (actual time=75.348..446.893 rows=6 loops=1)

  • Recheck Cond: (cid = 235)
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL) AND (property_id = ANY ('{550592}'::integer[])) AND (scheduled_task_type_id = 11))
  • Rows Removed by Filter: 365,128
  • Heap Blocks: exact=88,184
71. 40.764 40.764 ↑ 1.0 365,170 1

Bitmap Index Scan on pk_scheduled_tasks (cost=0.00..6,789.89 rows=367,128 width=0) (actual time=40.764..40.764 rows=365,170 loops=1)

  • Index Cond: (cid = 235)