explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kEns

Settings
# exclusive inclusive rows x rows loops node
1. 6.324 28,091.515 ↑ 6.1 313 1

WindowAgg (cost=42,900.62..42,938.54 rows=1,896 width=28) (actual time=28,082.576..28,091.515 rows=313 loops=1)

2.          

CTE sanitized_activity

3. 76.779 76.779 ↑ 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.031..76.779 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.972 90.342 ↓ 2.2 1,107 1

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

6. 25.448 69.370 ↓ 2.2 1,107 1

Subquery Scan on future_dates (cost=64.47..89.47 rows=495 width=8) (actual time=30.279..69.370 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.741 43.882 ↓ 1.6 1,551 1

Sort (cost=55.41..57.91 rows=1,000 width=8) (actual time=30.211..43.882 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.062 0.666 ↑ 1.0 1 1

Result (cost=0.56..0.57 rows=1 width=8) (actual time=0.649..0.666 rows=1 loops=1)

10.          

Initplan (forResult)

11. 0.060 0.604 ↑ 1.0 1 1

Limit (cost=0.43..0.56 rows=1 width=4) (actual time=0.564..0.604 rows=1 loops=1)

12. 0.544 0.544 ↑ 1,288,419.0 1 1

Index Only Scan Backward using activity_date_idx on activity (cost=0.43..164,391.20 rows=1,288,419 width=4) (actual time=0.527..0.544 rows=1 loops=1)

  • Heap Fetches: 1
13. 15.453 15.475 ↓ 1.6 1,551 1

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

14. 0.022 0.022 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.008..0.022 rows=1 loops=1)

15.          

SubPlan (forSubquery Scan)

16. 0.012 0.040 ↓ 0.0 0 1

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

17. 0.028 0.028 ↓ 0.0 0 1

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

  • Index Cond: (class_id = 1)
18.          

CTE curr_date

19. 0.766 86.639 ↑ 5.4 37 1

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

20. 3.422 85.873 ↑ 5.4 37 1

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

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

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

22. 7.128 28,085.191 ↑ 6.1 313 1

Sort (cost=11,672.43..11,677.17 rows=1,896 width=20) (actual time=28,082.360..28,085.191 rows=313 loops=1)

  • Sort Key: sanitized_activity.activity_date, i.order_num
  • Sort Method: quicksort Memory: 47kB
23. 6.759 28,078.063 ↑ 6.1 313 1

Hash Join (cost=5,033.94..11,569.20 rows=1,896 width=20) (actual time=2,088.153..28,078.063 rows=313 loops=1)

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

Merge Left Join (cost=4,992.79..11,459.30 rows=766 width=16) (actual time=1,878.791..27,861.975 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. 10.599 19.514 ↑ 2.4 313 1

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

26. 6.111 8.915 ↑ 2.4 313 1

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

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

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

28. 12,209.610 15,247.084 ↓ 28.3 1,392,427 1

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

29. 863.110 3,037.474 ↑ 1.1 44,872 1

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

30. 857.069 2,174.364 ↑ 1.1 44,875 1

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

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

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

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

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

33. 10.683 21.124 ↑ 1.0 1,116 1

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

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

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

35. 0.354 209.329 ↑ 13.4 37 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 10.722 208.975 ↑ 13.4 37 1

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

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

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

38. 0.396 87.766 ↑ 5.4 37 1

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

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

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

Planning time : 1.268 ms