explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UtOE

Settings
# exclusive inclusive rows x rows loops node
1. 0.049 1,682.594 ↓ 5.0 5 1

GroupAggregate (cost=16.88..16.93 rows=1 width=88) (actual time=1,682.566..1,682.594 rows=5 loops=1)

  • Group Key: orr.month_num, orr.year_num, orr.cid, orr.unit_type_id
2.          

CTE optimal_rent

3. 0.025 1,400.789 ↓ 5.0 5 1

GroupAggregate (cost=8.38..8.41 rows=1 width=60) (actual time=1,400.774..1,400.789 rows=5 loops=1)

  • Group Key: optimal_units_data.cid, optimal_units_data.property_id, optimal_units_data.unit_type_id, optimal_units_data.month_num, optimal_units_data.year_num
4. 0.036 1,400.764 ↓ 17.0 17 1

Sort (cost=8.38..8.38 rows=1 width=60) (actual time=1,400.762..1,400.764 rows=17 loops=1)

  • Sort Key: optimal_units_data.cid, optimal_units_data.property_id, optimal_units_data.unit_type_id, optimal_units_data.month_num, optimal_units_data.year_num
  • Sort Method: quicksort Memory: 26kB
5. 0.008 1,400.728 ↓ 17.0 17 1

Subquery Scan on optimal_units_data (cost=8.30..8.37 rows=1 width=60) (actual time=1,400.697..1,400.728 rows=17 loops=1)

  • Filter: (optimal_units_data.row_num = 1)
  • Rows Removed by Filter: 11
6. 0.033 1,400.720 ↓ 28.0 28 1

WindowAgg (cost=8.30..8.35 rows=1 width=100) (actual time=1,400.694..1,400.720 rows=28 loops=1)

7. 0.045 1,400.687 ↓ 28.0 28 1

Sort (cost=8.30..8.30 rows=1 width=92) (actual time=1,400.684..1,400.687 rows=28 loops=1)

  • Sort Key: rl.unit_space_id, (date_part('MONTH'::text, (rl.effective_date)::timestamp without time zone)), (date_part('YEAR'::text, (rl.effective_date)::timestamp without time zone)), (date_part('DAY'::text, (rl.effective_date)::timestamp without time zone)) DESC
  • Sort Method: quicksort Memory: 28kB
8. 0.631 1,400.642 ↓ 28.0 28 1

GroupAggregate (cost=8.22..8.29 rows=1 width=92) (actual time=1,399.978..1,400.642 rows=28 loops=1)

  • Group Key: rl.effective_date, rl.cid, rl.property_id, rl.unit_type_id, rl.unit_space_id, (date_part('month'::text, (rl.effective_date)::timestamp without time zone)), (date_part('year'::text, (rl.effective_date)::timestamp without time zone))
9. 1.150 1,400.011 ↓ 1,400.0 1,400 1

Sort (cost=8.22..8.23 rows=1 width=41) (actual time=1,399.933..1,400.011 rows=1,400 loops=1)

  • Sort Key: rl.effective_date, rl.unit_space_id, (date_part('month'::text, (rl.effective_date)::timestamp without time zone)), (date_part('year'::text, (rl.effective_date)::timestamp without time zone))
  • Sort Method: quicksort Memory: 158kB
10. 1.206 1,398.861 ↓ 1,400.0 1,400 1

Nested Loop (cost=1.00..8.21 rows=1 width=41) (actual time=39.831..1,398.861 rows=1,400 loops=1)

11. 0.219 0.219 ↓ 12.0 12 1

Index Scan using idx_unit_spaces_company_property_id on unit_spaces us (cost=0.42..2.45 rows=1 width=16) (actual time=0.097..0.219 rows=12 loops=1)

  • Index Cond: ((cid = 2,695) AND (property_id = 33,477))
  • Filter: (unit_type_id = 148,951)
  • Rows Removed by Filter: 42
12. 1,397.436 1,397.436 ↓ 117.0 117 12

Index Scan using idx_rate_logs_partial on rate_logs rl (cost=0.57..5.75 rows=1 width=29) (actual time=60.696..116.453 rows=117 loops=12)

  • Index Cond: ((cid = 2,695) AND (property_id = 33,477) AND (ar_cascade_id = 4) AND (ar_cascade_reference_id = us.id) AND (effective_date >= (CURRENT_DATE - '5 mons'::interval)) AND (effective_date <= CURRENT_DATE))
  • Filter: ((unit_space_id IS NOT NULL) AND (NOT is_renewal) AND (rate_amount <> '0'::numeric) AND (effective_through_date > effective_date) AND (unit_space_id = ar_cascade_reference_id) AND (unit_type_id = 148,951) AND (lease_term_months = 12) AND (ar_phase_id = 1) AND (updated_by = 65))
  • Rows Removed by Filter: 49,383
13.          

CTE override_rent

14. 0.025 281.700 ↓ 5.0 5 1

GroupAggregate (cost=8.36..8.40 rows=1 width=60) (actual time=281.690..281.700 rows=5 loops=1)

  • Group Key: optimal_units_data_1.cid, optimal_units_data_1.property_id, optimal_units_data_1.unit_type_id, optimal_units_data_1.month_num, optimal_units_data_1.year_num
15. 0.027 281.675 ↓ 13.0 13 1

Sort (cost=8.36..8.37 rows=1 width=60) (actual time=281.674..281.675 rows=13 loops=1)

  • Sort Key: optimal_units_data_1.cid, optimal_units_data_1.property_id, optimal_units_data_1.unit_type_id, optimal_units_data_1.month_num, optimal_units_data_1.year_num
  • Sort Method: quicksort Memory: 26kB
16. 0.007 281.648 ↓ 13.0 13 1

Subquery Scan on optimal_units_data_1 (cost=8.28..8.35 rows=1 width=60) (actual time=281.625..281.648 rows=13 loops=1)

  • Filter: (optimal_units_data_1.row_num = 1)
  • Rows Removed by Filter: 8
17. 0.033 281.641 ↓ 21.0 21 1

WindowAgg (cost=8.28..8.34 rows=1 width=100) (actual time=281.622..281.641 rows=21 loops=1)

18. 0.060 281.608 ↓ 21.0 21 1

Sort (cost=8.28..8.29 rows=1 width=92) (actual time=281.607..281.608 rows=21 loops=1)

  • Sort Key: rl_1.unit_space_id, (date_part('MONTH'::text, (rl_1.effective_date)::timestamp without time zone)), (date_part('YEAR'::text, (rl_1.effective_date)::timestamp without time zone)), (date_part('DAY'::text, (rl_1.effective_date)::timestamp without time zone)) DESC
  • Sort Method: quicksort Memory: 27kB
19. 0.504 281.548 ↓ 21.0 21 1

GroupAggregate (cost=8.21..8.27 rows=1 width=92) (actual time=281.027..281.548 rows=21 loops=1)

  • Group Key: rl_1.effective_date, rl_1.cid, rl_1.property_id, rl_1.unit_type_id, rl_1.unit_space_id, (date_part('month'::text, (rl_1.effective_date)::timestamp without time zone)), (date_part('year'::text, (rl_1.effective_date)::timestamp without time zone))
20. 0.759 281.044 ↓ 1,050.0 1,050 1

Sort (cost=8.21..8.22 rows=1 width=41) (actual time=280.983..281.044 rows=1,050 loops=1)

  • Sort Key: rl_1.effective_date, rl_1.unit_space_id, (date_part('month'::text, (rl_1.effective_date)::timestamp without time zone)), (date_part('year'::text, (rl_1.effective_date)::timestamp without time zone))
  • Sort Method: quicksort Memory: 131kB
21. 0.762 280.285 ↓ 1,050.0 1,050 1

Nested Loop (cost=1.00..8.20 rows=1 width=41) (actual time=7.829..280.285 rows=1,050 loops=1)

22. 0.103 0.103 ↓ 12.0 12 1

Index Scan using idx_unit_spaces_company_property_id on unit_spaces us_1 (cost=0.42..2.45 rows=1 width=16) (actual time=0.034..0.103 rows=12 loops=1)

  • Index Cond: ((cid = 2,695) AND (property_id = 33,477))
  • Filter: (unit_type_id = 148,951)
  • Rows Removed by Filter: 42
23. 279.420 279.420 ↓ 88.0 88 12

Index Scan using idx_partial_rate_logs on rate_logs rl_1 (cost=0.57..5.73 rows=1 width=29) (actual time=11.604..23.285 rows=88 loops=12)

  • Index Cond: ((cid = 2,695) AND (property_id = 33,477) AND (ar_cascade_id = 4) AND (ar_cascade_reference_id = us_1.id) AND (effective_date >= (CURRENT_DATE - '5 mons'::interval)) AND (effective_date <= CURRENT_DATE))
  • Filter: ((unit_space_id IS NOT NULL) AND is_allowed AND (NOT is_renewal) AND (rate_amount <> '0'::numeric) AND (effective_through_date > effective_date) AND (unit_space_id = ar_cascade_reference_id) AND (unit_type_id = 148,951) AND (lease_term_months = 12) AND (updated_by = 65))
  • Rows Removed by Filter: 18,163
24. 0.026 1,682.545 ↓ 5.0 5 1

Sort (cost=0.07..0.08 rows=1 width=88) (actual time=1,682.544..1,682.545 rows=5 loops=1)

  • Sort Key: orr.month_num, orr.year_num DESC, orr.cid, orr.unit_type_id
  • Sort Method: quicksort Memory: 25kB
25. 0.013 1,682.519 ↓ 5.0 5 1

Nested Loop (cost=0.00..0.06 rows=1 width=88) (actual time=1,682.477..1,682.519 rows=5 loops=1)

  • Join Filter: ((orr.cid = pr.cid) AND (orr.property_id = pr.property_id) AND (orr.unit_type_id = pr.unit_type_id) AND (orr.month_num = pr.month_num) AND (orr.year_num = pr.year_num))
  • Rows Removed by Join Filter: 20
26. 1,400.796 1,400.796 ↓ 5.0 5 1

CTE Scan on optimal_rent orr (cost=0.00..0.02 rows=1 width=60) (actual time=1,400.778..1,400.796 rows=5 loops=1)

27. 281.710 281.710 ↓ 5.0 5 5

CTE Scan on override_rent pr (cost=0.00..0.02 rows=1 width=60) (actual time=56.339..56.342 rows=5 loops=5)