explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yc4E

Settings
# exclusive inclusive rows x rows loops node
1. 5.920 20,747.131 ↑ 16.7 24 1

GroupAggregate (cost=8,914,219.95..8,914,313.95 rows=400 width=142) (actual time=20,738.544..20,747.131 rows=24 loops=1)

  • 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.354 0.427 ↑ 83.3 12 1

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

4. 0.022 0.073 ↑ 83.3 12 1

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

  • Sort Key: (row_number() OVER (?)) DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.030 0.051 ↑ 83.3 12 1

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

6. 0.021 0.021 ↑ 83.3 12 1

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

7. 15.432 20,741.211 ↓ 3.9 7,884 1

Sort (cost=8,913,860.11..8,913,865.11 rows=2,000 width=126) (actual time=20,738.255..20,741.211 rows=7,884 loops=1)

  • Sort Key: p.property_name, mgp.row_number DESC, p.property_id, mgp.start_date, mgp.end_date
  • Sort Method: quicksort Memory: 1301kB
8. 7.555 20,725.779 ↓ 3.9 7,884 1

Nested Loop (cost=4,321.34..8,913,750.45 rows=2,000 width=126) (actual time=55.169..20,725.779 rows=7,884 loops=1)

9. 0.008 0.008 ↑ 1.0 2 1

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

  • Filter: (cid = 3156)
10. 7.440 20,718.216 ↓ 3.9 3,942 2

Nested Loop (cost=4,321.34..4,456,864.71 rows=1,000 width=96) (actual time=56.651..10,359.108 rows=3,942 loops=2)

11. 0.480 0.480 ↑ 83.3 12 2

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

12. 2,068.200 20,710.296 ↓ 328.0 328 24

Nested Loop Left Join (cost=4,321.34..4,456.82 rows=1 width=100) (actual time=69.532..862.929 rows=328 loops=24)

  • Join Filter: (property_unit_details.property_unit_id = pu.id)
  • Rows Removed by Join Filter: 215496
13. 1,678.980 16,387.272 ↓ 328.0 328 24

Nested Loop Left Join (cost=4,321.34..4,436.37 rows=1 width=89) (actual time=69.343..682.803 rows=328 loops=24)

  • Join Filter: ((sc.cid = cusl.cid) AND (cll.unit_space_id = cusl.unit_space_id))
  • Rows Removed by Join Filter: 174387
14. 13.368 304.224 ↓ 328.0 328 24

Nested Loop (cost=255.24..370.19 rows=1 width=29) (actual time=1.169..12.676 rows=328 loops=24)

15. 12.420 133.176 ↓ 328.0 328 24

Nested Loop (cost=254.68..361.62 rows=1 width=28) (actual time=1.145..5.549 rows=328 loops=24)

16. 12.564 81.336 ↓ 328.0 328 24

Nested Loop (cost=254.26..353.17 rows=1 width=20) (actual time=1.138..3.389 rows=328 loops=24)

17. 18.648 45.120 ↓ 65.6 328 24

Bitmap Heap Scan on property_units pu (cost=253.97..311.61 rows=5 width=16) (actual time=1.127..1.880 rows=328 loops=24)

  • 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)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=4824
18. 0.312 26.472 ↓ 0.0 0 24

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

19. 0.840 0.840 ↓ 1.2 329 24

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

  • Index Cond: (property_id = p.property_id)
20. 25.320 25.320 ↓ 1.3 16,743 24

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

  • Index Cond: (cid = p.cid)
21. 23.652 23.652 ↑ 1.0 1 7,884

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

  • Index Cond: (id = pu.unit_type_id)
  • Filter: (cid = p.cid)
22. 39.420 39.420 ↑ 1.0 1 7,884

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 time=0.004..0.005 rows=1 loops=7,884)

  • 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. 157.680 157.680 ↑ 1.0 1 7,884

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.56 rows=1 width=17) (actual time=0.019..0.020 rows=1 loops=7,884)

  • 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. 9,665.784 14,404.068 ↓ 532.0 532 7,884

GroupAggregate (cost=4,066.10..4,066.15 rows=1 width=130) (actual time=0.209..1.827 rows=532 loops=7,884)

  • 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. 3,148.884 4,738.284 ↓ 1,036.0 1,036 7,884

Sort (cost=4,066.10..4,066.11 rows=1 width=66) (actual time=0.205..0.601 rows=1,036 loops=7,884)

  • Sort Key: sc.property_id, cll.unit_space_id, pp.value, pcd.value
  • Sort Method: quicksort Memory: 272kB
26. 21.168 1,589.400 ↓ 1,036.0 1,036 24

Result (cost=1.85..4,066.09 rows=1 width=66) (actual time=0.141..66.225 rows=1,036 loops=24)

  • One-Time Filter: (p.cid = 3156)
27. 29.624 1,568.232 ↓ 1,036.0 1,036 24

Nested Loop Left Join (cost=1.85..4,066.09 rows=1 width=66) (actual time=0.138..65.343 rows=1,036 loops=24)

28. 61.700 1,439.136 ↓ 1,036.0 1,036 24

Nested Loop Left Join (cost=1.42..4,057.63 rows=1 width=29) (actual time=0.120..59.964 rows=1,036 loops=24)

  • Join Filter: ((pcd.cid = load_prop.cid) AND (pcd.property_id = load_prop.property_id))
  • Rows Removed by Join Filter: 1036
29. 57.552 1,352.568 ↓ 1,036.0 1,036 24

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

30. 57.024 911.616 ↓ 1,065.0 1,065 24

Nested Loop (cost=0.86..4,048.66 rows=1 width=44) (actual time=0.074..37.984 rows=1,065 loops=24)

  • Join Filter: (load_prop.property_id = sc.property_id)
31. 22.224 264.912 ↓ 7.9 1,170 24

Nested Loop (cost=0.43..2,358.02 rows=148 width=20) (actual time=0.022..11.038 rows=1,170 loops=24)

32. 0.096 0.096 ↑ 1.0 2 24

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

33. 242.592 242.592 ↓ 7.9 585 48

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

  • Index Cond: ((cid = 3156) AND (property_id = load_prop.property_id))
  • Filter: (lease_status_type_id <> ALL ('{1,2}'::integer[]))
  • Rows Removed by Filter: 4322
34. 589.680 589.680 ↑ 1.0 1 28,080

Index Scan using idx_scheduled_charges_lease_interval_id on scheduled_charges sc (cost=0.43..11.41 rows=1 width=40) (actual time=0.019..0.021 rows=1 loops=28,080)

  • 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))))
  • Rows Removed by Filter: 18
35. 383.400 383.400 ↑ 1.0 1 25,560

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 time=0.013..0.015 rows=1 loops=25,560)

  • 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))))
  • Rows Removed by Filter: 12
36. 24.868 24.868 ↑ 1.0 1 24,868

Seq Scan on property_custom_data_key_values pcd (cost=0.00..1.01 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=24,868)

  • Filter: ((key)::text = 'SCHEDULED_RENT'::text)
37. 99.472 99.472 ↑ 1.0 1 24,868

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

  • Index Cond: ((cid = load_prop.cid) AND (property_id = load_prop.property_id) AND ((key)::text = 'MIGRATION_THRESHOLD_DATE'::text))
38. 2,254.824 2,254.824 ↑ 1.0 657 7,884

Seq Scan on property_unit_details (cost=0.00..12.21 rows=657 width=12) (actual time=0.004..0.286 rows=657 loops=7,884)

  • Filter: (cid = 3156)
Planning time : 70.226 ms