explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2fGX

Settings
# exclusive inclusive rows x rows loops node
1. 6.105 27,775.212 ↑ 6.1 313 1

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

2.          

CTE sanitized_activity

3. 78.847 78.847 ↑ 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.142..78.847 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. 21.353 89.888 ↓ 2.2 1,107 1

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

6. 26.608 68.535 ↓ 2.2 1,107 1

Subquery Scan on future_dates (cost=64.47..89.47 rows=495 width=8) (actual time=28.026..68.535 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.446 41.881 ↓ 1.6 1,551 1

Sort (cost=55.42..57.92 rows=1,000 width=8) (actual time=27.948..41.881 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.036 0.148 ↑ 1.0 1 1

Result (cost=0.56..0.57 rows=1 width=8) (actual time=0.131..0.148 rows=1 loops=1)

10.          

Initplan (forResult)

11. 0.038 0.112 ↑ 1.0 1 1

Limit (cost=0.43..0.56 rows=1 width=4) (actual time=0.078..0.112 rows=1 loops=1)

12. 0.074 0.074 ↑ 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.058..0.074 rows=1 loops=1)

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

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

14. 0.027 0.027 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.010..0.027 rows=1 loops=1)

15.          

SubPlan (forSubquery Scan)

16. 0.015 0.046 ↓ 0.0 0 1

ProjectSet (cost=0.28..8.80 rows=100 width=2) (actual time=0.038..0.046 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.022..0.031 rows=0 loops=1)

  • Index Cond: (class_id = 1)
18.          

CTE curr_date

19. 0.688 88.227 ↑ 5.4 37 1

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

20. 3.254 87.539 ↑ 5.4 37 1

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

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

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

22. 6.828 27,769.107 ↑ 6.1 313 1

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

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

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

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

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

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

26. 6.121 9.286 ↑ 2.4 313 1

Sort (cost=52.02..53.93 rows=766 width=20) (actual time=6.306..9.286 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.165 3.165 ↑ 2.4 313 1

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

28. 12,049.284 15,083.705 ↓ 28.3 1,392,427 1

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

29. 862.620 3,034.421 ↑ 1.1 44,872 1

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

30. 859.007 2,171.801 ↑ 1.1 44,875 1

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

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

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

  • Hash Cond: (i.template_id = t.template_id)
32. 423.558 423.558 ↑ 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..423.558 rows=49,208 loops=1)

33. 10.764 21.042 ↑ 1.0 1,116 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 56kB
34. 10.278 10.278 ↑ 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.278 rows=1,116 loops=1)

35. 0.461 210.827 ↑ 13.4 37 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 10.632 210.366 ↑ 13.4 37 1

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

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

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

38. 0.350 89.239 ↑ 5.4 37 1

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

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

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

Planning time : 1.084 ms