explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ps5P

Settings
# exclusive inclusive rows x rows loops node
1. 0.158 56,186.379 ↓ 56.0 56 1

GroupAggregate (cost=30.40..30.45 rows=1 width=88) (actual time=56,186.233..56,186.379 rows=56 loops=1)

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

CTE optimal_rent

3. 0.132 53,531.347 ↓ 60.0 60 1

GroupAggregate (cost=15.67..15.70 rows=1 width=60) (actual time=53,531.222..53,531.347 rows=60 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.218 53,531.215 ↓ 133.0 133 1

Sort (cost=15.67..15.67 rows=1 width=60) (actual time=53,531.199..53,531.215 rows=133 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: 35kB
5. 0.069 53,530.997 ↓ 133.0 133 1

Subquery Scan on optimal_units_data (cost=15.59..15.66 rows=1 width=60) (actual time=53,530.596..53,530.997 rows=133 loops=1)

  • Filter: (optimal_units_data.row_num = 1)
  • Rows Removed by Filter: 58
6. 0.325 53,530.928 ↓ 191.0 191 1

WindowAgg (cost=15.59..15.64 rows=1 width=100) (actual time=53,530.590..53,530.928 rows=191 loops=1)

7. 0.309 53,530.603 ↓ 191.0 191 1

Sort (cost=15.59..15.59 rows=1 width=92) (actual time=53,530.576..53,530.603 rows=191 loops=1)

  • Sort Key: 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)), (date_part('DAY'::text, (rl.effective_date)::timestamp without time zone)) DESC
  • Sort Method: quicksort Memory: 51kB
8. 8.292 53,530.294 ↓ 191.0 191 1

GroupAggregate (cost=15.51..15.58 rows=1 width=92) (actual time=53,521.019..53,530.294 rows=191 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. 15.313 53,522.002 ↓ 9,452.0 9,452 1

Sort (cost=15.51..15.52 rows=1 width=41) (actual time=53,520.912..53,522.002 rows=9,452 loops=1)

  • Sort Key: rl.effective_date, 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))
  • Sort Method: quicksort Memory: 1123kB
10. 107.259 53,506.689 ↓ 9,452.0 9,452 1

Nested Loop (cost=1.00..15.50 rows=1 width=41) (actual time=124.346..53,506.689 rows=9,452 loops=1)

11. 0.828 0.828 ↓ 54.0 54 1

Index Scan using idx_unit_spaces_company_property_id on unit_spaces us (cost=0.42..2.44 rows=1 width=16) (actual time=0.043..0.828 rows=54 loops=1)

  • Index Cond: ((cid = 2695) AND (property_id = 33477))
12. 53,398.602 53,398.602 ↓ 175.0 175 54

Index Scan using idx_rate_logs_partial on rate_logs rl (cost=0.57..13.04 rows=1 width=29) (actual time=239.816..988.863 rows=175 loops=54)

  • Index Cond: ((cid = 2695) AND (property_id = 33477) AND (ar_cascade_id = 4) AND (ar_cascade_reference_id = us.id) AND (effective_date >= (CURRENT_DATE - '1 year'::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 (lease_term_months = 12) AND (ar_phase_id = 1) AND (updated_by = 65) AND (us.unit_type_id = unit_type_id))
  • Rows Removed by Filter: 66483
13.          

CTE override_rent

14. 0.102 2,653.910 ↓ 57.0 57 1

GroupAggregate (cost=14.60..14.63 rows=1 width=60) (actual time=2,653.816..2,653.910 rows=57 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.176 2,653.808 ↓ 120.0 120 1

Sort (cost=14.60..14.60 rows=1 width=60) (actual time=2,653.800..2,653.808 rows=120 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: 34kB
16. 0.033 2,653.632 ↓ 120.0 120 1

Subquery Scan on optimal_units_data_1 (cost=14.52..14.59 rows=1 width=60) (actual time=2,653.460..2,653.632 rows=120 loops=1)

  • Filter: (optimal_units_data_1.row_num = 1)
  • Rows Removed by Filter: 48
17. 0.148 2,653.599 ↓ 168.0 168 1

WindowAgg (cost=14.52..14.57 rows=1 width=100) (actual time=2,653.454..2,653.599 rows=168 loops=1)

18. 0.144 2,653.451 ↓ 168.0 168 1

Sort (cost=14.52..14.52 rows=1 width=92) (actual time=2,653.439..2,653.451 rows=168 loops=1)

  • Sort Key: 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)), (date_part('DAY'::text, (rl_1.effective_date)::timestamp without time zone)) DESC
  • Sort Method: quicksort Memory: 48kB
19. 3.574 2,653.307 ↓ 168.0 168 1

GroupAggregate (cost=14.44..14.51 rows=1 width=92) (actual time=2,649.322..2,653.307 rows=168 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. 6.906 2,649.733 ↓ 8,351.0 8,351 1

Sort (cost=14.44..14.45 rows=1 width=41) (actual time=2,649.265..2,649.733 rows=8,351 loops=1)

  • Sort Key: rl_1.effective_date, 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))
  • Sort Method: quicksort Memory: 1037kB
21. 7.533 2,642.827 ↓ 8,351.0 8,351 1

Nested Loop (cost=1.00..14.43 rows=1 width=41) (actual time=1.776..2,642.827 rows=8,351 loops=1)

22. 0.202 0.202 ↓ 54.0 54 1

Index Scan using idx_unit_spaces_company_property_id on unit_spaces us_1 (cost=0.42..2.44 rows=1 width=16) (actual time=0.045..0.202 rows=54 loops=1)

  • Index Cond: ((cid = 2695) AND (property_id = 33477))
23. 2,635.092 2,635.092 ↓ 155.0 155 54

Index Scan using idx_partial_rate_logs on rate_logs rl_1 (cost=0.57..11.97 rows=1 width=29) (actual time=11.598..48.798 rows=155 loops=54)

  • Index Cond: ((cid = 2695) AND (property_id = 33477) AND (ar_cascade_id = 4) AND (ar_cascade_reference_id = us_1.id) AND (effective_date >= (CURRENT_DATE - '1 year'::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 (lease_term_months = 12) AND (updated_by = 65) AND (us_1.unit_type_id = unit_type_id))
  • Rows Removed by Filter: 25015
24. 0.060 56,186.221 ↓ 56.0 56 1

Sort (cost=0.07..0.08 rows=1 width=88) (actual time=56,186.216..56,186.221 rows=56 loops=1)

  • Sort Key: orr.month_num, orr.year_num DESC, orr.cid, orr.unit_type_id
  • Sort Method: quicksort Memory: 29kB
25. 0.500 56,186.161 ↓ 56.0 56 1

Nested Loop (cost=0.00..0.06 rows=1 width=88) (actual time=56,185.066..56,186.161 rows=56 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: 3364
26. 53,531.381 53,531.381 ↓ 60.0 60 1

CTE Scan on optimal_rent orr (cost=0.00..0.02 rows=1 width=60) (actual time=53,531.230..53,531.381 rows=60 loops=1)

27. 2,654.280 2,654.280 ↓ 57.0 57 60

CTE Scan on override_rent pr (cost=0.00..0.02 rows=1 width=60) (actual time=44.230..44.238 rows=57 loops=60)

Planning time : 39.813 ms