explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DVbp

Settings
# exclusive inclusive rows x rows loops node
1. 0.053 5.781 ↓ 3.5 7 1

WindowAgg (cost=275.22..275.26 rows=2 width=28) (actual time=5.773..5.781 rows=7 loops=1)

2.          

CTE sanitized_activity

3. 0.290 0.290 ↓ 7.0 7 1

Seq Scan on activity a (cost=0.00..1.10 rows=1 width=125) (actual time=0.285..0.290 rows=7 loops=1)

  • Filter: ((is_fixed IS NOT TRUE) AND (activity_date >= '2019-07-01'::date) AND (class_id = 1))
4.          

CTE available_date

5. 0.471 0.751 ↑ 20.6 24 1

WindowAgg (cost=84.37..116.79 rows=495 width=12) (actual time=0.707..0.751 rows=24 loops=1)

6. 0.089 0.280 ↑ 20.6 24 1

Subquery Scan on future_dates (cost=84.37..109.37 rows=495 width=8) (actual time=0.253..0.280 rows=24 loops=1)

  • Filter: ((date_part('dow'::text, future_dates.d) <> ALL ('{0,6}'::double precision[])) AND (NOT (hashed SubPlan 2)))
  • Rows Removed by Filter: 8
7. 0.000 0.185 ↑ 31.2 32 1

Sort (cost=55.95..58.45 rows=1,000 width=8) (actual time=0.182..0.185 rows=32 loops=1)

  • Sort Key: (generate_series('2019-07-02 00:00:00'::timestamp without time zone, $3, '1 day'::interval))
  • Sort Method: quicksort Memory: 26kB
8.          

Initplan (forSort)

9. 0.062 0.069 ↑ 1.0 1 1

Aggregate (cost=1.09..1.10 rows=1 width=8) (actual time=0.069..0.069 rows=1 loops=1)

10. 0.007 0.007 ↑ 1.0 7 1

Seq Scan on activity (cost=0.00..1.07 rows=7 width=4) (actual time=0.006..0.007 rows=7 loops=1)

11. 0.124 0.125 ↑ 31.2 32 1

ProjectSet (cost=0.00..5.02 rows=1,000 width=8) (actual time=0.105..0.125 rows=32 loops=1)

12. 0.001 0.001 ↑ 1.0 1 1

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

13.          

SubPlan (forSubquery Scan)

14. 0.001 0.006 ↓ 0.0 0 1

ProjectSet (cost=0.00..26.92 rows=600 width=2) (actual time=0.006..0.006 rows=0 loops=1)

15. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on template (cost=0.00..23.88 rows=6 width=32) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: (class_id = 1)
  • Rows Removed by Filter: 1
16.          

CTE curr_date

17. 0.007 0.525 ↓ 2.0 2 1

WindowAgg (cost=0.03..0.06 rows=1 width=12) (actual time=0.519..0.525 rows=2 loops=1)

18. 0.003 0.518 ↓ 2.0 2 1

Unique (cost=0.03..0.04 rows=1 width=4) (actual time=0.515..0.518 rows=2 loops=1)

19. 0.511 0.515 ↓ 7.0 7 1

Sort (cost=0.03..0.04 rows=1 width=4) (actual time=0.514..0.515 rows=7 loops=1)

  • Sort Key: sanitized_activity_1.activity_date
  • Sort Method: quicksort Memory: 25kB
20. 0.004 0.004 ↓ 7.0 7 1

CTE Scan on sanitized_activity sanitized_activity_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.004 rows=7 loops=1)

21. 0.531 5.728 ↓ 3.5 7 1

Sort (cost=157.27..157.27 rows=2 width=20) (actual time=5.727..5.728 rows=7 loops=1)

  • Sort Key: sanitized_activity.activity_date, i.order_num
  • Sort Method: quicksort Memory: 25kB
22. 0.008 5.197 ↓ 3.5 7 1

Nested Loop (cost=106.11..157.26 rows=2 width=20) (actual time=5.015..5.197 rows=7 loops=1)

  • Join Filter: (sanitized_activity.activity_date = curr_d.d)
  • Rows Removed by Join Filter: 7
23. 0.022 3.264 ↓ 7.0 7 1

Merge Left Join (cost=106.08..145.42 rows=1 width=16) (actual time=3.203..3.264 rows=7 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: 16
24. 0.670 1.035 ↓ 7.0 7 1

WindowAgg (cost=0.03..0.06 rows=1 width=76) (actual time=1.012..1.035 rows=7 loops=1)

25. 0.065 0.365 ↓ 7.0 7 1

Sort (cost=0.03..0.04 rows=1 width=20) (actual time=0.363..0.365 rows=7 loops=1)

  • Sort Key: sanitized_activity.timeofday_id, sanitized_activity.activity_date, sanitized_activity.order_num
  • Sort Method: quicksort Memory: 25kB
26. 0.300 0.300 ↓ 7.0 7 1

CTE Scan on sanitized_activity (cost=0.00..0.02 rows=1 width=20) (actual time=0.292..0.300 rows=7 loops=1)

27. 0.026 2.207 ↑ 44.5 22 1

Materialize (cost=106.05..142.80 rows=980 width=22) (actual time=2.149..2.207 rows=22 loops=1)

28. 0.069 2.181 ↑ 98.0 10 1

WindowAgg (cost=106.05..130.55 rows=980 width=58) (actual time=2.129..2.181 rows=10 loops=1)

29. 0.562 2.112 ↑ 98.0 10 1

Sort (cost=106.05..108.50 rows=980 width=14) (actual time=2.110..2.112 rows=10 loops=1)

  • Sort Key: i.timeofday_id, i.day_num, i.order_num
  • Sort Method: quicksort Memory: 25kB
30. 1.483 1.550 ↑ 98.0 10 1

Hash Join (cost=34.98..57.36 rows=980 width=14) (actual time=1.544..1.550 rows=10 loops=1)

  • Hash Cond: (i.template_id = t.template_id)
31. 0.031 0.031 ↑ 98.0 10 1

Seq Scan on template_item i (cost=0.00..19.80 rows=980 width=18) (actual time=0.029..0.031 rows=10 loops=1)

32. 0.016 0.036 ↑ 1,110.0 1 1

Hash (cost=21.10..21.10 rows=1,110 width=4) (actual time=0.036..0.036 rows=1 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
33. 0.020 0.020 ↑ 1,110.0 1 1

Seq Scan on template t (cost=0.00..21.10 rows=1,110 width=4) (actual time=0.019..0.020 rows=1 loops=1)

34. 0.600 1.925 ↑ 1.0 2 7

Hash Join (cost=0.03..11.81 rows=2 width=8) (actual time=0.259..0.275 rows=2 loops=7)

  • Hash Cond: (avail_d.d_order = curr_d.d_order)
35. 0.784 0.784 ↑ 20.6 24 7

CTE Scan on available_date avail_d (cost=0.00..9.90 rows=495 width=12) (actual time=0.101..0.112 rows=24 loops=7)

36. 0.013 0.541 ↓ 2.0 2 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=0.541..0.541 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.528 0.528 ↓ 2.0 2 1

CTE Scan on curr_date curr_d (cost=0.00..0.02 rows=1 width=12) (actual time=0.521..0.528 rows=2 loops=1)