explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PcoN

Settings
# exclusive inclusive rows x rows loops node
1. 0.106 13,516.484 ↓ 12.0 12 1

GroupAggregate (cost=30.41..30.46 rows=1 width=88) (actual time=13,516.417..13,516.484 rows=12 loops=1)

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

CTE optimal_rent

3. 0.110 12,813.851 ↓ 12.0 12 1

GroupAggregate (cost=15.67..15.70 rows=1 width=60) (actual time=12,813.752..12,813.851 rows=12 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.072 12,813.741 ↓ 41.0 41 1

Sort (cost=15.67..15.67 rows=1 width=60) (actual time=12,813.716..12,813.741 rows=41 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: 28kB
5. 0.017 12,813.669 ↓ 41.0 41 1

Subquery Scan on optimal_units_data (cost=15.59..15.66 rows=1 width=60) (actual time=12,813.595..12,813.669 rows=41 loops=1)

  • Filter: (optimal_units_data.row_num = 1)
  • Rows Removed by Filter: 24
6. 0.077 12,813.652 ↓ 65.0 65 1

WindowAgg (cost=15.59..15.65 rows=1 width=100) (actual time=12,813.589..12,813.652 rows=65 loops=1)

7. 0.072 12,813.575 ↓ 65.0 65 1

Sort (cost=15.59..15.59 rows=1 width=92) (actual time=12,813.570..12,813.575 rows=65 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: 34kB
8. 1.532 12,813.503 ↓ 65.0 65 1

GroupAggregate (cost=15.52..15.58 rows=1 width=92) (actual time=12,811.868..12,813.503 rows=65 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. 4.780 12,811.971 ↓ 3,201.0 3,201 1

Sort (cost=15.52..15.52 rows=1 width=41) (actual time=12,811.779..12,811.971 rows=3,201 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: 347kB
10. 5.735 12,807.191 ↓ 3,201.0 3,201 1

Nested Loop (cost=1.00..15.51 rows=1 width=41) (actual time=2.564..12,807.191 rows=3,201 loops=1)

11. 0.444 0.444 ↓ 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.057..0.444 rows=12 loops=1)

  • Index Cond: ((cid = 2695) AND (property_id = 33477))
  • Filter: (unit_type_id = 148951)
  • Rows Removed by Filter: 42
12. 12,801.012 12,801.012 ↓ 267.0 267 12

Index Scan using idx_rate_logs_partial on rate_logs rl (cost=0.57..13.04 rows=1 width=29) (actual time=155.140..1,066.751 rows=267 loops=12)

  • 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 (unit_type_id = 148951) AND (lease_term_months = 12) AND (ar_phase_id = 1) AND (updated_by = 65))
  • Rows Removed by Filter: 83369
13.          

CTE override_rent

14. 0.083 702.288 ↓ 12.0 12 1

GroupAggregate (cost=14.60..14.63 rows=1 width=60) (actual time=702.237..702.288 rows=12 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.126 702.205 ↓ 36.0 36 1

Sort (cost=14.60..14.60 rows=1 width=60) (actual time=702.200..702.205 rows=36 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: 27kB
16. 0.028 702.079 ↓ 36.0 36 1

Subquery Scan on optimal_units_data_1 (cost=14.52..14.59 rows=1 width=60) (actual time=701.957..702.079 rows=36 loops=1)

  • Filter: (optimal_units_data_1.row_num = 1)
  • Rows Removed by Filter: 21
17. 0.115 702.051 ↓ 57.0 57 1

WindowAgg (cost=14.52..14.58 rows=1 width=100) (actual time=701.948..702.051 rows=57 loops=1)

18. 0.115 701.936 ↓ 57.0 57 1

Sort (cost=14.52..14.52 rows=1 width=92) (actual time=701.928..701.936 rows=57 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: 33kB
19. 2.512 701.821 ↓ 57.0 57 1

GroupAggregate (cost=14.45..14.51 rows=1 width=92) (actual time=699.097..701.821 rows=57 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. 3.775 699.309 ↓ 2,801.0 2,801 1

Sort (cost=14.45..14.45 rows=1 width=41) (actual time=698.989..699.309 rows=2,801 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: 315kB
21. 3.758 695.534 ↓ 2,801.0 2,801 1

Nested Loop (cost=1.00..14.44 rows=1 width=41) (actual time=0.930..695.534 rows=2,801 loops=1)

22. 0.180 0.180 ↓ 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.057..0.180 rows=12 loops=1)

  • Index Cond: ((cid = 2695) AND (property_id = 33477))
  • Filter: (unit_type_id = 148951)
  • Rows Removed by Filter: 42
23. 691.596 691.596 ↓ 233.0 233 12

Index Scan using idx_partial_rate_logs on rate_logs rl_1 (cost=0.57..11.97 rows=1 width=29) (actual time=9.041..57.633 rows=233 loops=12)

  • 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 (unit_type_id = 148951) AND (lease_term_months = 12) AND (updated_by = 65))
  • Rows Removed by Filter: 31279
24. 0.066 13,516.378 ↓ 12.0 12 1

Sort (cost=0.07..0.08 rows=1 width=88) (actual time=13,516.376..13,516.378 rows=12 loops=1)

  • Sort Key: orr.month_num, orr.year_num DESC, orr.cid, orr.unit_type_id
  • Sort Method: quicksort Memory: 26kB
25. 0.083 13,516.312 ↓ 12.0 12 1

Nested Loop (cost=0.00..0.06 rows=1 width=88) (actual time=13,516.032..13,516.312 rows=12 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: 132
26. 12,813.881 12,813.881 ↓ 12.0 12 1

CTE Scan on optimal_rent orr (cost=0.00..0.02 rows=1 width=60) (actual time=12,813.769..12,813.881 rows=12 loops=1)

27. 702.348 702.348 ↓ 12.0 12 12

CTE Scan on override_rent pr (cost=0.00..0.02 rows=1 width=60) (actual time=58.520..58.529 rows=12 loops=12)

Planning time : 19.660 ms