explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XL2r

Settings
# exclusive inclusive rows x rows loops node
1. 6.355 12,120.214 ↑ 6.2 313 1

WindowAgg (cost=94,041.45..94,080.51 rows=1,953 width=28) (actual time=12,111.265..12,120.214 rows=313 loops=1)

2.          

CTE sanitized_activity

3. 6.755 106.273 ↑ 2.5 313 1

Gather (cost=1,000.00..41,863.61 rows=789 width=195) (actual time=11.770..106.273 rows=313 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 99.518 99.518 ↑ 3.2 104 3

Parallel Seq Scan on activity a_1 (cost=0.00..40,784.71 rows=329 width=195) (actual time=9.079..99.518 rows=104 loops=3)

  • Filter: ((is_fixed IS NOT TRUE) AND (activity_date >= '2016-04-12'::date) AND (class_id = 1090152))
  • Rows Removed by Filter: 429369
5.          

CTE available_date

6. 20.729 6,655.398 ↓ 2.2 1,107 1

WindowAgg (cost=40,523.04..40,555.47 rows=495 width=12) (actual time=6,596.395..6,655.398 rows=1,107 loops=1)

7. 24.839 6,634.669 ↓ 2.2 1,107 1

Subquery Scan on future_dates (cost=40,523.04..40,548.04 rows=495 width=8) (actual time=6,596.364..6,634.669 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. 0.000 6,609.672 ↓ 1.6 1,551 1

Sort (cost=40,498.33..40,500.83 rows=1,000 width=8) (actual time=6,596.159..6,609.672 rows=1,551 loops=1)

  • Sort Key: (generate_series('2016-04-14 00:00:00'::timestamp without time zone, $4, '1 day'::interval))
  • Sort Method: quicksort Memory: 121kB
9.          

Initplan (for Sort)

10. 0.000 6,567.712 ↑ 1.0 1 1

Finalize Aggregate (cost=40,443.48..40,443.49 rows=1 width=8) (actual time=6,567.697..6,567.712 rows=1 loops=1)

11. 3.713 6,567.734 ↓ 1.5 3 1

Gather (cost=40,443.26..40,443.47 rows=2 width=4) (actual time=6,567.351..6,567.734 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 3,262.057 6,564.021 ↑ 1.0 1 3

Partial Aggregate (cost=39,443.26..39,443.27 rows=1 width=4) (actual time=6,564.006..6,564.021 rows=1 loops=3)

13. 3,301.964 3,301.964 ↑ 1.2 429,473 3

Parallel Seq Scan on activity (cost=0.00..38,101.81 rows=536,581 width=4) (actual time=0.047..3,301.964 rows=429,473 loops=3)

14. 6,581.947 6,581.977 ↓ 1.6 1,551 1

ProjectSet (cost=0.00..5.02 rows=1,000 width=8) (actual time=6,567.755..6,581.977 rows=1,551 loops=1)

15. 0.030 0.030 ↑ 1.0 1 1

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

16.          

SubPlan (for Subquery Scan)

17. 0.020 0.158 ↓ 0.0 0 1

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

18. 0.138 0.138 ↓ 0.0 0 1

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

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

CTE curr_date

20. 0.715 116.547 ↑ 5.4 37 1

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

21. 0.869 115.832 ↑ 5.4 37 1

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

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

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

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

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

24. 5.806 12,113.859 ↑ 6.2 313 1

Sort (cost=11,589.98..11,594.86 rows=1,953 width=20) (actual time=12,111.068..12,113.859 rows=313 loops=1)

  • Sort Key: a.activity_date, t.order_num
  • Sort Method: quicksort Memory: 47kB
25. 5.809 12,108.053 ↑ 6.2 313 1

Hash Join (cost=10,662.41..11,483.23 rows=1,953 width=20) (actual time=11,305.369..12,108.053 rows=313 loops=1)

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

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

Sort (cost=123.27..125.24 rows=789 width=28) (actual time=23.357..26.166 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.498 20.496 ↑ 2.5 313 1

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

29. 6.416 14.998 ↑ 2.5 313 1

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

30. 5.604 8.582 ↑ 2.5 313 1

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

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

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

32. 861.088 4,866.445 ↑ 1.1 45,079 1

Sort (cost=10,497.99..10,621.01 rows=49,208 width=22) (actual time=4,461.976..4,866.445 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. 828.054 4,005.357 ↑ 1.0 49,208 1

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

34. 944.176 3,177.303 ↑ 1.0 49,208 1

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

35. 903.183 2,233.127 ↑ 1.0 49,208 1

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

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

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

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

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

38. 9.921 19.835 ↑ 1.0 1,116 1

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

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

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

40. 0.383 6,803.747 ↑ 13.4 37 1

Hash (cost=34.96..34.96 rows=495 width=8) (actual time=6,803.739..6,803.747 rows=37 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
41. 10.484 6,803.364 ↑ 13.4 37 1

Hash Join (cost=6.50..34.96 rows=495 width=8) (actual time=6,714.090..6,803.364 rows=37 loops=1)

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

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

43. 0.372 117.627 ↑ 5.4 37 1

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

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

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

Planning time : 0.940 ms