explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jym2

Settings
# exclusive inclusive rows x rows loops node
1. 6.301 5,518.069 ↑ 6.2 313 1

WindowAgg (cost=40,333.77..40,372.83 rows=1,953 width=28) (actual time=5,509.267..5,518.069 rows=313 loops=1)

2.          

CTE sanitized_activity

3. 3.244 78.725 ↑ 2.5 313 1

Bitmap Heap Scan on activity a_1 (cost=25,818.25..28,598.85 rows=789 width=195) (actual time=75.522..78.725 rows=313 loops=1)

  • Recheck Cond: ((activity_date >= '2016-04-12'::date) AND (class_id = 1090152))
  • Filter: (is_fixed IS NOT TRUE)
  • Heap Blocks: exact=20
4. 75.481 75.481 ↑ 2.5 313 1

Bitmap Index Scan on activity_date_class_idx (cost=0.00..25,818.06 rows=792 width=0) (actual time=75.473..75.481 rows=313 loops=1)

  • Index Cond: ((activity_date >= '2016-04-12'::date) AND (class_id = 1090152))
5.          

CTE available_date

6. 21.379 91.145 ↓ 2.2 1,107 1

WindowAgg (cost=80.12..112.55 rows=495 width=12) (actual time=29.160..91.145 rows=1,107 loops=1)

7. 26.473 69.766 ↓ 2.2 1,107 1

Subquery Scan on future_dates (cost=80.12..105.12 rows=495 width=8) (actual time=29.136..69.766 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
8. 28.390 43.166 ↓ 1.6 1,551 1

Sort (cost=55.42..57.92 rows=1,000 width=8) (actual time=28.971..43.166 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
9.          

Initplan (forSort)

10. 0.036 0.149 ↑ 1.0 1 1

Result (cost=0.56..0.57 rows=1 width=8) (actual time=0.133..0.149 rows=1 loops=1)

11.          

Initplan (forResult)

12. 0.036 0.113 ↑ 1.0 1 1

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

13. 0.077 0.077 ↑ 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.060..0.077 rows=1 loops=1)

  • Index Cond: (activity_date IS NOT NULL)
  • Heap Fetches: 1
14. 14.601 14.627 ↓ 1.6 1,551 1

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

15. 0.026 0.026 ↑ 1.0 1 1

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

16.          

SubPlan (forSubquery Scan)

17. 0.017 0.127 ↓ 0.0 0 1

ProjectSet (cost=0.00..24.46 rows=100 width=2) (actual time=0.119..0.127 rows=0 loops=1)

18. 0.110 0.110 ↓ 0.0 0 1

Seq Scan on template (cost=0.00..23.95 rows=1 width=13) (actual time=0.102..0.110 rows=0 loops=1)

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

CTE curr_date

20. 0.721 89.285 ↑ 5.4 37 1

WindowAgg (cost=27.40..32.40 rows=200 width=12) (actual time=88.261..89.285 rows=37 loops=1)

21. 0.694 88.564 ↑ 5.4 37 1

Sort (cost=27.40..27.90 rows=200 width=4) (actual time=88.237..88.564 rows=37 loops=1)

  • Sort Key: sanitized_activity_1.activity_date
  • Sort Method: quicksort Memory: 26kB
22. 3.445 87.870 ↑ 5.4 37 1

HashAggregate (cost=17.75..19.75 rows=200 width=4) (actual time=87.508..87.870 rows=37 loops=1)

  • Group Key: sanitized_activity_1.activity_date
23. 84.425 84.425 ↑ 2.5 313 1

CTE Scan on sanitized_activity sanitized_activity_1 (cost=0.00..15.78 rows=789 width=4) (actual time=75.542..84.425 rows=313 loops=1)

24. 5.597 5,511.768 ↑ 6.2 313 1

Sort (cost=11,589.98..11,594.86 rows=1,953 width=20) (actual time=5,509.094..5,511.768 rows=313 loops=1)

  • Sort Key: a.activity_date, t.order_num
  • Sort Method: quicksort Memory: 47kB
25. 5.610 5,506.171 ↑ 6.2 313 1

Hash Join (cost=10,662.41..11,483.23 rows=1,953 width=20) (actual time=4,723.842..5,506.171 rows=313 loops=1)

  • Hash Cond: (a.activity_date = curr_d.d)
26. 398.372 5,286.965 ↑ 2.5 313 1

Merge Left Join (cost=10,621.26..11,371.26 rows=789 width=16) (actual time=4,510.216..5,286.965 rows=313 loops=1)

  • Merge Cond: ((a.timeofday_id = t.timeofday_id) AND (((a.day_num + '1'::double precision)) = ((t.day_num)::double precision)) AND (a.tod_ordered = t.template_tod_ordered))
27. 5.691 26.351 ↑ 2.5 313 1

Sort (cost=123.27..125.24 rows=789 width=28) (actual time=23.649..26.351 rows=313 loops=1)

  • Sort Key: a.timeofday_id, ((a.day_num + '1'::double precision)), a.tod_ordered
  • Sort Method: quicksort Memory: 49kB
28. 5.571 20.660 ↑ 2.5 313 1

Subquery Scan on a (cost=53.75..85.31 rows=789 width=28) (actual time=6.082..20.660 rows=313 loops=1)

29. 6.387 15.089 ↑ 2.5 313 1

WindowAgg (cost=53.75..77.42 rows=789 width=76) (actual time=6.064..15.089 rows=313 loops=1)

30. 5.639 8.702 ↑ 2.5 313 1

Sort (cost=53.75..55.72 rows=789 width=20) (actual time=6.019..8.702 rows=313 loops=1)

  • Sort Key: sanitized_activity.timeofday_id, sanitized_activity.activity_date, sanitized_activity.order_num
  • Sort Method: quicksort Memory: 49kB
31. 3.063 3.063 ↑ 2.5 313 1

CTE Scan on sanitized_activity (cost=0.00..15.78 rows=789 width=20) (actual time=0.011..3.063 rows=313 loops=1)

32. 843.461 4,862.242 ↑ 1.1 45,079 1

Sort (cost=10,497.99..10,621.01 rows=49,208 width=22) (actual time=4,470.921..4,862.242 rows=45,079 loops=1)

  • Sort Key: t.timeofday_id, ((t.day_num)::double precision), t.template_tod_ordered
  • Sort Method: external sort Disk: 2216kB
33. 836.140 4,018.781 ↑ 1.0 49,208 1

Subquery Scan on t (cost=4,940.78..6,663.06 rows=49,208 width=22) (actual time=1,796.818..4,018.781 rows=49,208 loops=1)

34. 949.681 3,182.641 ↑ 1.0 49,208 1

WindowAgg (cost=4,940.78..6,170.98 rows=49,208 width=58) (actual time=1,796.798..3,182.641 rows=49,208 loops=1)

35. 901.803 2,232.960 ↑ 1.0 49,208 1

Sort (cost=4,940.78..5,063.80 rows=49,208 width=14) (actual time=1,795.326..2,232.960 rows=49,208 loops=1)

  • Sort Key: i.timeofday_id, i.day_num, i.order_num
  • Sort Method: external merge Disk: 1360kB
36. 879.993 1,331.157 ↑ 1.0 49,208 1

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

  • Hash Cond: (i.template_id = t_1.template_id)
37. 431.071 431.071 ↑ 1.0 49,208 1

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

38. 10.232 20.093 ↑ 1.0 1,116 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 56kB
39. 9.861 9.861 ↑ 1.0 1,116 1

Seq Scan on template t_1 (cost=0.00..21.16 rows=1,116 width=4) (actual time=0.018..9.861 rows=1,116 loops=1)

40. 0.417 213.596 ↑ 13.4 37 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
41. 11.823 213.179 ↑ 13.4 37 1

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

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

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

43. 0.396 90.395 ↑ 5.4 37 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
44. 89.999 89.999 ↑ 5.4 37 1

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

Planning time : 0.851 ms