explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TWjl

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=8,914,233.92..8,914,327.92 rows=400 width=142) (actual rows= loops=)

  • Group Key: p.property_name, mgp.row_number, p.cid, p.property_id, mgp.start_date, mgp.end_date
2.          

CTE monthly_generated_periods

3. 0.000 0.000 ↓ 0.0

Result (cost=82.34..359.84 rows=1,000 width=48) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=82.34..84.84 rows=1,000 width=52) (actual rows= loops=)

  • Sort Key: (row_number() OVER (?)) DESC
5. 0.000 0.000 ↓ 0.0

WindowAgg (cost=0.02..32.52 rows=1,000 width=52) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Function Scan on generate_series gs (cost=0.02..10.02 rows=1,000 width=8) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Sort (cost=8,913,874.08..8,913,879.08 rows=2,000 width=126) (actual rows= loops=)

  • Sort Key: p.property_name, mgp.row_number DESC, p.property_id, mgp.start_date, mgp.end_date
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,321.34..8,913,764.42 rows=2,000 width=126) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on load_prop p (cost=0.00..1.02 rows=2 width=30) (actual rows= loops=)

  • Filter: (cid = 3156)
10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,321.34..4,456,871.70 rows=1,000 width=96) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

CTE Scan on monthly_generated_periods mgp (cost=0.00..20.00 rows=1,000 width=16) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=4,321.34..4,456.83 rows=1 width=100) (actual rows= loops=)

  • Join Filter: (property_unit_details.property_unit_id = pu.id)
13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=4,321.34..4,436.38 rows=1 width=89) (actual rows= loops=)

  • Join Filter: ((sc.cid = cusl.cid) AND (cll.unit_space_id = cusl.unit_space_id))
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=255.24..370.20 rows=1 width=29) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=254.68..361.62 rows=1 width=28) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=254.26..353.17 rows=1 width=20) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on property_units pu (cost=253.97..311.61 rows=5 width=16) (actual rows= loops=)

  • Recheck Cond: ((property_id = p.property_id) AND (cid = p.cid))
  • Filter: (COALESCE(deleted_on, ('2099-12-31'::date)::timestamp with time zone) > mgp.end_date)
18. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=253.97..253.97 rows=15 width=0) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_property_units_property_id (cost=0.00..6.42 rows=267 width=0) (actual rows= loops=)

  • Index Cond: (property_id = p.property_id)
20. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on pk_property_units (cost=0.00..247.29 rows=13,183 width=0) (actual rows= loops=)

  • Index Cond: (cid = p.cid)
21. 0.000 0.000 ↓ 0.0

Index Scan using idx_unit_types_id on unit_types ut (cost=0.29..8.31 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = pu.unit_type_id)
  • Filter: (cid = p.cid)
22. 0.000 0.000 ↓ 0.0

Index Scan using idx_cached_unit_spaces_property_unit_id on cached_unit_spaces cus (cost=0.42..8.45 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (property_unit_id = pu.id)
  • Filter: ((cid = p.cid) AND (COALESCE(deleted_on, ('2099-12-31'::date)::timestamp with time zone) > mgp.end_date))
23. 0.000 0.000 ↓ 0.0

Index Scan using idx_cached_unit_space_logs_cid_unit_space_id_reporting_post_dat on cached_unit_space_logs cusl (cost=0.56..8.57 rows=1 width=17) (actual rows= loops=)

  • Index Cond: ((cid = p.cid) AND (unit_space_id = cus.id) AND (mgp.end_date >= reporting_post_date) AND (mgp.end_date <= apply_through_post_date) AND (is_post_date_ignored = 0))
  • Filter: ((cus.property_id = property_id) AND (COALESCE(deleted_on, ('2099-12-31'::date)::timestamp with time zone) > mgp.end_date))
24. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=4,066.10..4,066.15 rows=1 width=130) (actual rows= loops=)

  • Group Key: sc.cid, sc.property_id, cll.unit_space_id, (mgp.row_number), (mgp.start_date), (mgp.end_date), pp.value, pcd.value
25. 0.000 0.000 ↓ 0.0

Sort (cost=4,066.10..4,066.11 rows=1 width=66) (actual rows= loops=)

  • Sort Key: sc.property_id, cll.unit_space_id, pp.value, pcd.value
26. 0.000 0.000 ↓ 0.0

Result (cost=1.85..4,066.09 rows=1 width=66) (actual rows= loops=)

  • One-Time Filter: (p.cid = 3156)
27. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.85..4,066.09 rows=1 width=66) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.42..4,057.63 rows=1 width=29) (actual rows= loops=)

  • Join Filter: ((pcd.cid = load_prop.cid) AND (pcd.property_id = load_prop.property_id))
29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.42..4,056.61 rows=1 width=24) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..4,048.66 rows=1 width=44) (actual rows= loops=)

  • Join Filter: (load_prop.property_id = sc.property_id)
31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..2,358.02 rows=148 width=20) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Seq Scan on load_prop (cost=0.00..1.02 rows=2 width=8) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Index Scan using idx_lease_intervals_cid_propid_leaseid on lease_intervals li (cost=0.43..1,177.76 rows=74 width=12) (actual rows= loops=)

  • Index Cond: ((cid = 3156) AND (property_id = load_prop.property_id))
  • Filter: (lease_status_type_id <> ALL ('{1,2}'::integer[]))
34. 0.000 0.000 ↓ 0.0

Index Scan using idx_scheduled_charges_lease_interval_id on scheduled_charges sc (cost=0.43..11.41 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (lease_interval_id = li.id)
  • Filter: ((NOT is_unselected_quote) AND (cid = 3156) AND (ar_trigger_id = 307) AND (ar_code_type_id = 2) AND (li.property_id = property_id) AND ((last_posted_on = date_trunc('MONTH'::text, (mgp.end_date)::timestamp with time zone)) OR ((charge_start_date <= mgp.end_date) AND (COALESCE(charge_end_date, '2099-12-31'::date) >= mgp.end_date) AND (COALESCE(deleted_on, ('2099-12-31'::date)::timestamp with time zone) > mgp.end_date))))
35. 0.000 0.000 ↓ 0.0

Index Scan using idx_cached_lease_logs_cid_lease_id_reporting_post_month_apply_t on cached_lease_logs cll (cost=0.56..7.94 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((cid = 3156) AND (lease_id = sc.lease_id))
  • Filter: ((mgp.end_date >= reporting_post_date) AND (mgp.end_date <= apply_through_post_date) AND (lease_status_type_id = ANY ('{4,5}'::integer[])) AND (occupancy_type_id <> 5) AND (is_post_date_ignored = 0) AND ((sc.last_posted_on = date_trunc('MONTH'::text, (mgp.end_date)::timestamp with time zone)) OR ((sc.charge_start_date <= mgp.end_date) AND (COALESCE(sc.charge_end_date, '2099-12-31'::date) >= mgp.end_date) AND (COALESCE(sc.deleted_on, ('2099-12-31'::date)::timestamp with time zone) > mgp.end_date) AND (COALESCE(move_out_date, '2099-12-31'::date) > mgp.end_date))))
36. 0.000 0.000 ↓ 0.0

Seq Scan on property_custom_data_key_values pcd (cost=0.00..1.01 rows=1 width=13) (actual rows= loops=)

  • Filter: ((key)::text = 'SCHEDULED_RENT'::text)
37. 0.000 0.000 ↓ 0.0

Index Scan using uk_property_preferences_cid_property_id_key on property_preferences pp (cost=0.42..8.44 rows=1 width=37) (actual rows= loops=)

  • Index Cond: ((cid = load_prop.cid) AND (property_id = load_prop.property_id) AND ((key)::text = 'MIGRATION_THRESHOLD_DATE'::text))
38. 0.000 0.000 ↓ 0.0

Seq Scan on property_unit_details (cost=0.00..12.21 rows=657 width=12) (actual rows= loops=)