explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FYFq

Settings
# exclusive inclusive rows x rows loops node
1. 5.984 28,173.376 ↑ 6.1 313 1

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

2.          

CTE sanitized_activity

3. 80.099 80.099 ↑ 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.041..80.099 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.863 88.650 ↓ 2.2 1,107 1

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

6. 25.116 67.787 ↓ 2.2 1,107 1

Subquery Scan on future_dates (cost=64.47..89.47 rows=495 width=8) (actual time=29.072..67.787 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. 28.204 42.623 ↓ 1.6 1,551 1

Sort (cost=55.42..57.92 rows=1,000 width=8) (actual time=28.985..42.623 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.109 ↑ 1.0 1 1

Result (cost=0.56..0.57 rows=1 width=8) (actual time=0.095..0.109 rows=1 loops=1)

10.          

Initplan (forResult)

11. 0.034 0.074 ↑ 1.0 1 1

Limit (cost=0.43..0.56 rows=1 width=4) (actual time=0.044..0.074 rows=1 loops=1)

12. 0.040 0.040 ↑ 1,288,419.0 1 1

Index Only Scan Backward using activity_date_idx on activity (cost=0.43..167,612.25 rows=1,288,419 width=4) (actual time=0.027..0.040 rows=1 loops=1)

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

ProjectSet (cost=0.00..5.02 rows=1,000 width=8) (actual time=0.150..14.310 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.017 0.048 ↓ 0.0 0 1

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

17. 0.031 0.031 ↓ 0.0 0 1

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

  • Index Cond: (class_id = 1)
18.          

CTE curr_date

19. 0.756 90.222 ↑ 5.4 37 1

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

20. 3.440 89.466 ↑ 5.4 37 1

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

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

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

22. 6.929 28,167.392 ↑ 6.1 313 1

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

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

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

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

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

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

26. 6.298 9.319 ↑ 2.4 313 1

Sort (cost=52.02..53.93 rows=766 width=20) (actual time=6.168..9.319 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.021 3.021 ↑ 2.4 313 1

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

28. 12,229.191 15,302.696 ↓ 28.3 1,392,427 1

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

29. 886.388 3,073.505 ↑ 1.1 44,872 1

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

30. 869.942 2,187.117 ↑ 1.1 44,875 1

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

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

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

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

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

33. 9.699 19.138 ↑ 1.0 1,116 1

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

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

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

35. 0.356 211.520 ↑ 13.4 37 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 11.022 211.164 ↑ 13.4 37 1

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

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

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

38. 0.386 91.333 ↑ 5.4 37 1

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

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

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

Planning time : 0.680 ms