explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eTuE

Settings
# exclusive inclusive rows x rows loops node
1. 6.239 5,469.432 ↑ 6.2 313 1

WindowAgg (cost=40,318.11..40,357.17 rows=1,953 width=28) (actual time=5,460.613..5,469.432 rows=313 loops=1)

2.          

CTE sanitized_activity

3. 2.973 63.829 ↑ 2.5 313 1

Bitmap Heap Scan on activity a_1 (cost=25,818.25..28,598.85 rows=789 width=195) (actual time=60.888..63.829 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. 60.856 60.856 ↑ 2.5 313 1

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

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

CTE available_date

6. 20.527 88.264 ↓ 2.2 1,107 1

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

7. 25.105 67.737 ↓ 2.2 1,107 1

Subquery Scan on future_dates (cost=64.47..89.47 rows=495 width=8) (actual time=29.468..67.737 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. 27.772 42.555 ↓ 1.6 1,551 1

Sort (cost=55.42..57.92 rows=1,000 width=8) (actual time=29.351..42.555 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.038 0.113 ↑ 1.0 1 1

Result (cost=0.56..0.57 rows=1 width=8) (actual time=0.096..0.113 rows=1 loops=1)

11.          

Initplan (forResult)

12. 0.034 0.075 ↑ 1.0 1 1

Limit (cost=0.43..0.56 rows=1 width=4) (actual time=0.042..0.075 rows=1 loops=1)

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

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

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

15. 0.029 0.029 ↑ 1.0 1 1

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

16.          

SubPlan (forSubquery Scan)

17. 0.016 0.077 ↓ 0.0 0 1

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

18. 0.061 0.061 ↓ 0.0 0 1

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

  • Index Cond: (class_id = 1)
19.          

CTE curr_date

20. 0.714 74.242 ↑ 5.4 37 1

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

21. 0.656 73.528 ↑ 5.4 37 1

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

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

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

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

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

24. 5.615 5,463.193 ↑ 6.2 313 1

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

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

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

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

Merge Left Join (cost=10,621.26..11,371.26 rows=789 width=16) (actual time=4,490.528..5,258.235 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.630 26.791 ↑ 2.5 313 1

Sort (cost=123.27..125.24 rows=789 width=28) (actual time=24.155..26.791 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.530 21.161 ↑ 2.5 313 1

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

29. 6.550 15.631 ↑ 2.5 313 1

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

30. 5.723 9.081 ↑ 2.5 313 1

Sort (cost=53.75..55.72 rows=789 width=20) (actual time=6.316..9.081 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.358 3.358 ↑ 2.5 313 1

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

32. 843.124 4,839.121 ↑ 1.1 45,079 1

Sort (cost=10,497.99..10,621.01 rows=49,208 width=22) (actual time=4,450.409..4,839.121 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. 835.415 3,995.997 ↑ 1.0 49,208 1

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

34. 950.670 3,160.582 ↑ 1.0 49,208 1

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

35. 892.863 2,209.912 ↑ 1.0 49,208 1

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

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

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

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

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

38. 10.273 20.259 ↑ 1.0 1,116 1

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

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

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

40. 0.329 193.709 ↑ 13.4 37 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
41. 10.270 193.380 ↑ 13.4 37 1

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

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

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

43. 0.346 75.348 ↑ 5.4 37 1

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

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

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

Planning time : 1.249 ms