explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O6t

Settings
# exclusive inclusive rows x rows loops node
1. 204.291 4,952.584 ↓ 794.3 120,735 1

Nested Loop Left Join (cost=8,561.78..312,785.75 rows=152 width=88) (actual time=3,442.775..4,952.584 rows=120,735 loops=1)

2.          

CTE load_prop

3. 0.005 5.032 ↑ 1.0 1 1

Nested Loop (cost=0.54..9.31 rows=1 width=33) (actual time=5.029..5.032 rows=1 loops=1)

4. 5.021 5.021 ↑ 1.0 1 1

Function Scan on load_properties lp_set (cost=0.25..1.00 rows=1 width=8) (actual time=5.020..5.021 rows=1 loops=1)

  • Filter: ((is_disabled = 0) AND (is_test = 0))
5. 0.006 0.006 ↑ 1.0 1 1

Index Scan using idx_properties_id on properties p_1 (cost=0.29..8.30 rows=1 width=33) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (id = lp_set.property_id)
  • Filter: (lp_set.cid = cid)
6.          

CTE cte_date_range

7. 0.017 0.039 ↑ 111.1 9 1

WindowAgg (cost=0.00..30.00 rows=1,000 width=16) (actual time=0.025..0.039 rows=9 loops=1)

8. 0.022 0.022 ↑ 111.1 9 1

Function Scan on generate_series week_start (cost=0.00..10.00 rows=1,000 width=8) (actual time=0.018..0.022 rows=9 loops=1)

9. 256.153 4,386.088 ↓ 794.3 120,735 1

Nested Loop Left Join (cost=8,522.04..311,814.04 rows=152 width=370) (actual time=3,442.758..4,386.088 rows=120,735 loops=1)

10. 54.749 4,012.629 ↓ 110.2 16,758 1

Nested Loop Left Join (cost=8,521.61..309,841.46 rows=152 width=95) (actual time=3,442.740..4,012.629 rows=16,758 loops=1)

  • Join Filter: ((cdr.end_date >= cal.reporting_post_date) AND (cdr.end_date <= cal.apply_through_post_date))
  • Rows Removed by Join Filter: 29997
11. 119.616 3,901.225 ↓ 74.5 11,331 1

Hash Right Join (cost=8,521.17..305,314.62 rows=152 width=91) (actual time=3,442.689..3,901.225 rows=11,331 loops=1)

  • Hash Cond: ((cll.cid = usl.cid) AND (cll.unit_space_id = usl.unit_space_id))
  • Join Filter: ((cdr.end_date >= cll.reporting_post_date) AND (cdr.end_date <= cll.apply_through_post_date))
  • Rows Removed by Join Filter: 310212
12. 359.585 388.164 ↑ 1.3 153,160 1

Bitmap Heap Scan on cached_lease_logs cll (cost=7,676.69..288,935.64 rows=193,926 width=40) (actual time=41.267..388.164 rows=153,160 loops=1)

  • Recheck Cond: (cid = 235)
  • Filter: ((lease_status_type_id <> 2) AND (occupancy_type_id <> 4) AND (is_post_date_ignored = 0))
  • Rows Removed by Filter: 252553
  • Heap Blocks: exact=68784
13. 28.579 28.579 ↑ 1.0 405,713 1

Bitmap Index Scan on idx_cached_lease_logs_cid_effective_date (cost=0.00..7,628.21 rows=412,770 width=0) (actual time=28.579..28.579 rows=405,713 loops=1)

  • Index Cond: (cid = 235)
14. 5.797 3,393.445 ↓ 58.0 8,469 1

Hash (cost=842.29..842.29 rows=146 width=63) (actual time=3,393.445..3,393.445 rows=8,469 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 989kB
15. 297.806 3,387.648 ↓ 58.0 8,469 1

Nested Loop (cost=1.27..842.29 rows=146 width=63) (actual time=680.850..3,387.648 rows=8,469 loops=1)

  • Join Filter: (((usl.deleted_on IS NULL) OR (usl.deleted_on > cdr.end_date)) AND (((cdr.start_date >= usl.reporting_post_date) AND (cdr.start_date <= usl.apply_through_post_date)) OR ((cdr.end_date >= usl.reporting_post_date) AND (cdr.end_date <= usl.apply_through_post_date))))
  • Rows Removed by Join Filter: 457713
16. 1,551.844 2,882.650 ↓ 51,798.0 51,798 1

Nested Loop (cost=1.27..799.79 rows=1 width=55) (actual time=619.410..2,882.650 rows=51,798 loops=1)

  • Join Filter: ((pp.value)::integer = ass.id)
  • Rows Removed by Join Filter: 2952486
17. 0.112 0.112 ↑ 1.0 58 1

Seq Scan on application_stage_statuses ass (cost=0.00..1.58 rows=58 width=12) (actual time=0.008..0.112 rows=58 loops=1)

18. 1,162.075 1,330.694 ↓ 25,899.0 51,798 58

Materialize (cost=1.27..795.90 rows=2 width=58) (actual time=0.088..22.943 rows=51,798 loops=58)

19. 44.290 168.619 ↓ 25,899.0 51,798 1

Nested Loop (cost=1.27..795.89 rows=2 width=58) (actual time=5.079..168.619 rows=51,798 loops=1)

  • Join Filter: (p.id = usl.property_id)
20. 0.004 5.060 ↑ 1.0 1 1

Nested Loop (cost=0.71..14.32 rows=1 width=46) (actual time=5.053..5.060 rows=1 loops=1)

  • Join Filter: (load_prop.property_id = p.id)
21. 0.003 5.052 ↑ 1.0 1 1

Nested Loop (cost=0.42..8.47 rows=1 width=23) (actual time=5.047..5.052 rows=1 loops=1)

22. 5.036 5.036 ↑ 1.0 1 1

CTE Scan on load_prop (cost=0.00..0.02 rows=1 width=4) (actual time=5.032..5.036 rows=1 loops=1)

23. 0.013 0.013 ↑ 1.0 1 1

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp (cost=0.42..8.45 rows=1 width=19) (actual time=0.012..0.013 rows=1 loops=1)

  • Index Cond: ((cid = 235) AND (property_id = load_prop.property_id) AND ((key)::text = 'CHANGE_UNIT_STATUS_TO_RENTED'::text))
24. 0.004 0.004 ↑ 1.0 1 1

Index Scan using idx_properties_id on properties p (cost=0.29..5.83 rows=1 width=23) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (id = pp.property_id)
  • Filter: (cid = 235)
25. 119.269 119.269 ↓ 709.6 51,798 1

Index Scan using idx_unit_space_logs_cid_property_id_reporting_post_month_unit_s on unit_space_logs usl (cost=0.56..780.66 rows=73 width=32) (actual time=0.023..119.269 rows=51,798 loops=1)

  • Index Cond: ((cid = 235) AND (property_id = pp.property_id))
  • Filter: (is_post_date_ignored = 0)
  • Rows Removed by Filter: 48273
26. 207.192 207.192 ↑ 111.1 9 51,798

CTE Scan on cte_date_range cdr (cost=0.00..20.00 rows=1,000 width=16) (actual time=0.000..0.004 rows=9 loops=51,798)

27. 56.655 56.655 ↑ 2.2 4 11,331

Index Scan using idx_cached_application_logs_cid_lease_id on cached_application_logs cal (cost=0.43..29.65 rows=9 width=20) (actual time=0.002..0.005 rows=4 loops=11,331)

  • Index Cond: ((cid = cll.cid) AND (cid = 235) AND (lease_id = cll.lease_id))
28. 117.306 117.306 ↓ 7.0 7 16,758

Index Scan using idx_cached_applications_lease_id on cached_applications ca (cost=0.43..12.97 rows=1 width=283) (actual time=0.002..0.007 rows=7 loops=16,758)

  • Index Cond: (lease_id = cll.lease_id)
  • Filter: ((cid = 235) AND (cid = cll.cid))
  • Rows Removed by Filter: 0
29. 362.205 362.205 ↑ 1.0 1 120,735

Index Scan using idx_lease_processes_lease_id on lease_processes lp (cost=0.43..6.11 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=120,735)

  • Index Cond: (lease_id = ca.lease_id)
  • Filter: ((customer_id IS NULL) AND (cid = 235) AND (cid = ca.cid))
  • Rows Removed by Filter: 1