explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 78PR

Settings
# exclusive inclusive rows x rows loops node
1. 7.677 27,901.440 ↑ 6.1 313 1

WindowAgg (cost=42,900.62..42,938.54 rows=1,896 width=28) (actual time=27,890.375..27,901.440 rows=313 loops=1)

2.          

CTE sanitized_activity

3. 77.302 77.302 ↑ 2.4 313 1

Index Scan using activity_date_class_idx on activity a (cost=0.43..31,107.56 rows=766 width=195) (actual time=0.030..77.302 rows=313 loops=1)

  • Index Cond: ((activity_date >= '2016-04-12'::date) AND (activity_date <= '2020-04-12'::date) AND (class_id = 1090152))
  • Filter: (is_fixed IS NOT TRUE)
4.          

CTE available_date

5. 20.689 87.917 ↓ 2.2 1,107 1

WindowAgg (cost=64.47..96.89 rows=495 width=12) (actual time=28.551..87.917 rows=1,107 loops=1)

6. 25.057 67.228 ↓ 2.2 1,107 1

Subquery Scan on future_dates (cost=64.47..89.47 rows=495 width=8) (actual time=28.533..67.228 rows=1,107 loops=1)

  • Filter: ((date_part('dow'::text, future_dates.d) <> ALL ('{0,6}'::double precision[])) AND (NOT (hashed SubPlan 2)))
  • Rows Removed by Filter: 444
7. 27.815 42.128 ↓ 1.6 1,551 1

Sort (cost=55.42..57.92 rows=1,000 width=8) (actual time=28.460..42.128 rows=1,551 loops=1)

  • Sort Key: (generate_series('2016-04-14 00:00:00'::timestamp without time zone, $3, '1 day'::interval))
  • Sort Method: quicksort Memory: 121kB
8.          

Initplan (forSort)

9. 0.035 0.126 ↑ 1.0 1 1

Result (cost=0.56..0.57 rows=1 width=8) (actual time=0.110..0.126 rows=1 loops=1)

10.          

Initplan (forResult)

11. 0.036 0.091 ↑ 1.0 1 1

Limit (cost=0.43..0.56 rows=1 width=4) (actual time=0.057..0.091 rows=1 loops=1)

12. 0.055 0.055 ↑ 1,288,419.0 1 1

Index Only Scan Backward using activity_date_class_idx on activity (cost=0.43..167,622.54 rows=1,288,419 width=4) (actual time=0.039..0.055 rows=1 loops=1)

  • Index Cond: (activity_date IS NOT NULL)
  • Heap Fetches: 1
13. 14.163 14.187 ↓ 1.6 1,551 1

ProjectSet (cost=0.00..5.02 rows=1,000 width=8) (actual time=0.165..14.187 rows=1,551 loops=1)

14. 0.024 0.024 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.009..0.024 rows=1 loops=1)

15.          

SubPlan (forSubquery Scan)

16. 0.011 0.043 ↓ 0.0 0 1

ProjectSet (cost=0.28..8.80 rows=100 width=2) (actual time=0.035..0.043 rows=0 loops=1)

17. 0.032 0.032 ↓ 0.0 0 1

Index Scan using template_class_index on template (cost=0.28..8.29 rows=1 width=13) (actual time=0.023..0.032 rows=0 loops=1)

  • Index Cond: (class_id = 1)
18.          

CTE curr_date

19. 0.678 86.965 ↑ 5.4 37 1

WindowAgg (cost=17.23..23.73 rows=200 width=12) (actual time=85.977..86.965 rows=37 loops=1)

20. 3.287 86.287 ↑ 5.4 37 1

HashAggregate (cost=17.23..19.23 rows=200 width=4) (actual time=85.953..86.287 rows=37 loops=1)

  • Group Key: sanitized_activity_1.activity_date
21. 83.000 83.000 ↑ 2.4 313 1

CTE Scan on sanitized_activity sanitized_activity_1 (cost=0.00..15.32 rows=766 width=4) (actual time=0.043..83.000 rows=313 loops=1)

22. 7.787 27,893.763 ↑ 6.1 313 1

Sort (cost=11,672.43..11,677.17 rows=1,896 width=20) (actual time=27,890.148..27,893.763 rows=313 loops=1)

  • Sort Key: sanitized_activity.activity_date, i.order_num
  • Sort Method: quicksort Memory: 47kB
23. 7.081 27,885.976 ↑ 6.1 313 1

Hash Join (cost=5,033.94..11,569.20 rows=1,896 width=20) (actual time=2,082.923..27,885.976 rows=313 loops=1)

  • Hash Cond: (sanitized_activity.activity_date = curr_d.d)
24. 12,507.094 27,672.606 ↑ 2.4 313 1

Merge Left Join (cost=4,992.79..11,459.30 rows=766 width=16) (actual time=1,876.613..27,672.606 rows=313 loops=1)

  • Merge Cond: (sanitized_activity.timeofday_id = i.timeofday_id)
  • Join Filter: (((row_number() OVER (?)) = (row_number() OVER (?))) AND ((i.day_num)::double precision = ((date_part('dow'::text, (sanitized_activity.activity_date)::timestamp without time zone)) + '1'::double precision)))
  • Rows Removed by Join Filter: 1387334
25. 11.118 20.447 ↑ 2.4 313 1

WindowAgg (cost=52.02..75.00 rows=766 width=76) (actual time=6.298..20.447 rows=313 loops=1)

26. 6.047 9.329 ↑ 2.4 313 1

Sort (cost=52.02..53.93 rows=766 width=20) (actual time=6.248..9.329 rows=313 loops=1)

  • Sort Key: sanitized_activity.timeofday_id, sanitized_activity.activity_date, sanitized_activity.order_num
  • Sort Method: quicksort Memory: 49kB
27. 3.282 3.282 ↑ 2.4 313 1

CTE Scan on sanitized_activity (cost=0.00..15.32 rows=766 width=20) (actual time=0.011..3.282 rows=313 loops=1)

28. 12,121.988 15,145.065 ↓ 28.3 1,392,427 1

Materialize (cost=4,940.78..6,786.08 rows=49,208 width=22) (actual time=1,770.682..15,145.065 rows=1,392,427 loops=1)

29. 858.734 3,023.077 ↑ 1.1 44,872 1

WindowAgg (cost=4,940.78..6,170.98 rows=49,208 width=58) (actual time=1,770.663..3,023.077 rows=44,872 loops=1)

30. 855.529 2,164.343 ↑ 1.1 44,875 1

Sort (cost=4,940.78..5,063.80 rows=49,208 width=14) (actual time=1,769.195..2,164.343 rows=44,875 loops=1)

  • Sort Key: i.timeofday_id, i.day_num, i.order_num
  • Sort Method: external merge Disk: 1360kB
31. 867.053 1,308.814 ↑ 1.0 49,208 1

Hash Join (cost=35.11..1,105.85 rows=49,208 width=14) (actual time=20.952..1,308.814 rows=49,208 loops=1)

  • Hash Cond: (i.template_id = t.template_id)
32. 420.871 420.871 ↑ 1.0 49,208 1

Seq Scan on template_item i (cost=0.00..941.08 rows=49,208 width=18) (actual time=0.034..420.871 rows=49,208 loops=1)

33. 10.382 20.890 ↑ 1.0 1,116 1

Hash (cost=21.16..21.16 rows=1,116 width=4) (actual time=20.881..20.890 rows=1,116 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 56kB
34. 10.508 10.508 ↑ 1.0 1,116 1

Seq Scan on template t (cost=0.00..21.16 rows=1,116 width=4) (actual time=0.025..10.508 rows=1,116 loops=1)

35. 0.339 206.289 ↑ 13.4 37 1

Hash (cost=34.96..34.96 rows=495 width=8) (actual time=206.281..206.289 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 10.290 205.950 ↑ 13.4 37 1

Hash Join (cost=6.50..34.96 rows=495 width=8) (actual time=116.556..205.950 rows=37 loops=1)

  • Hash Cond: (avail_d.d_order = curr_d.d_order)
37. 107.694 107.694 ↓ 2.2 1,107 1

CTE Scan on available_date avail_d (cost=0.00..9.90 rows=495 width=12) (actual time=28.567..107.694 rows=1,107 loops=1)

38. 0.343 87.966 ↑ 5.4 37 1

Hash (cost=4.00..4.00 rows=200 width=12) (actual time=87.958..87.966 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
39. 87.623 87.623 ↑ 5.4 37 1

CTE Scan on curr_date curr_d (cost=0.00..4.00 rows=200 width=12) (actual time=85.994..87.623 rows=37 loops=1)

Planning time : 0.980 ms