explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6sQQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 5,110.539 ↑ 127.1 74,774 1

Finalize GroupAggregate (cost=914,385.01..2,343,393.41 rows=9,503,936 width=65) (actual time=4,807.093..5,110.539 rows=74,774 loops=1)

  • Group Key: g.title, (date_trunc('week'::text, v.published))
2. 0.000 5,170.629 ↑ 52.1 182,267 1

Gather Merge (cost=914,385.01..2,105,795.01 rows=9,503,936 width=65) (actual time=4,807.079..5,170.629 rows=182,267 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
3. 436.555 24,401.005 ↑ 65.2 36,453 5

Partial GroupAggregate (cost=913,384.95..972,784.55 rows=2,375,984 width=65) (actual time=4,738.315..4,880.201 rows=36,453 loops=5)

  • Group Key: g.title, (date_trunc('week'::text, v.published))
4. 4,013.070 23,964.450 ↑ 6.4 368,592 5

Sort (cost=913,384.95..919,324.91 rows=2,375,984 width=41) (actual time=4,738.304..4,792.890 rows=368,592 loops=5)

  • Sort Key: g.title, (date_trunc('week'::text, v.published))
  • Sort Method: quicksort Memory: 44111kB
  • Worker 0: Sort Method: quicksort Memory: 43517kB
  • Worker 1: Sort Method: quicksort Memory: 44780kB
  • Worker 2: Sort Method: quicksort Memory: 39887kB
  • Worker 3: Sort Method: quicksort Memory: 41511kB
5. 944.105 19,951.380 ↑ 6.4 368,592 5

Parallel Hash Join (cost=449,927.61..661,767.13 rows=2,375,984 width=41) (actual time=2,173.003..3,990.276 rows=368,592 loops=5)

  • Hash Cond: (vg.game_id = g.id)
6. 6,902.050 18,963.685 ↑ 6.4 368,592 5

Parallel Hash Join (cost=429,433.85..629,095.94 rows=2,375,984 width=20) (actual time=2,164.161..3,792.737 rows=368,592 loops=5)

  • Hash Cond: (vg.youtube_video_id = v.id)
7. 1,319.475 1,319.475 ↑ 1.2 3,424,274 5

Parallel Seq Scan on youtube_video_to_game_2 vg (cost=0.00..172,320.42 rows=4,280,342 width=16) (actual time=0.005..263.895 rows=3,424,274 loops=5)

8. 3,971.150 10,742.160 ↑ 1.2 1,920,674 5

Parallel Hash (cost=399,734.05..399,734.05 rows=2,375,984 width=28) (actual time=2,148.431..2,148.432 rows=1,920,674 loops=5)

  • Buckets: 16777216 Batches: 1 Memory Usage: 732544kB
9. 780.173 6,771.010 ↑ 1.2 1,920,674 5

Parallel Append (cost=0.55..399,734.05 rows=2,375,984 width=28) (actual time=0.036..1,354.202 rows=1,920,674 loops=5)

10. 1,115.020 1,115.020 ↓ 2.1 815,842 2

Parallel Index Only Scan using videos_y2019_q1_channel_id_published_id_view_count_comment__idx on videos_y2019_q1 v (cost=0.56..131,597.32 rows=383,588 width=28) (actual time=0.028..557.510 rows=815,842 loops=2)

  • Index Cond: ((published >= '2019-03-02 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-30 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
11. 2,073.255 2,073.255 ↑ 1.2 613,404 5

Parallel Index Only Scan using videos_y2019_q3_channel_id_published_id_view_count_comment__idx on videos_y2019_q3 v_4 (cost=0.56..88,979.31 rows=764,618 width=28) (actual time=0.030..414.651 rows=613,404 loops=5)

  • Index Cond: ((published >= '2019-03-02 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-30 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
12. 884.208 884.208 ↓ 4.0 1,675,510 1

Parallel Index Only Scan using videos_y2019_m04_channel_id_published_id_view_count_comment_idx on videos_y2019_m04 v_2 (cost=0.55..57,344.45 rows=420,740 width=28) (actual time=0.034..884.208 rows=1,675,510 loops=1)

  • Index Cond: ((published >= '2019-03-02 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-30 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
13. 1,088.916 1,088.916 ↓ 2.0 819,063 2

Parallel Index Only Scan using videos_y2019_m03_channel_id_published_id_view_count_comment_idx on videos_y2019_m03 v_1 (cost=0.55..55,422.24 rows=405,785 width=28) (actual time=0.021..544.458 rows=819,063 loops=2)

  • Index Cond: ((published >= '2019-03-02 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-30 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
14. 829.430 829.430 ↓ 4.0 1,591,029 1

Parallel Index Only Scan using videos_y2019_m05_channel_id_published_id_view_count_comment_idx on videos_y2019_m05 v_3 (cost=0.55..54,509.58 rows=401,253 width=28) (actual time=0.020..829.430 rows=1,591,029 loops=1)

  • Index Cond: ((published >= '2019-03-02 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-30 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
15. 0.008 0.008 ↓ 0.0 0 1

Parallel Index Only Scan using videos_default_channel_id_published_id_view_count_comment_c_idx on videos_default v_5 (cost=0.12..1.24 rows=1 width=28) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((published >= '2019-03-02 00:00:00+00'::timestamp with time zone) AND (published <= '2019-08-30 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
16. 15.785 43.590 ↑ 1.6 10,131 5

Parallel Hash (cost=20,286.12..20,286.12 rows=16,612 width=29) (actual time=8.717..8.718 rows=10,131 loops=5)

  • Buckets: 65536 Batches: 1 Memory Usage: 3808kB
17. 27.805 27.805 ↑ 1.6 10,131 5

Parallel Seq Scan on games g (cost=0.00..20,286.12 rows=16,612 width=29) (actual time=0.005..5.561 rows=10,131 loops=5)

Planning time : 2.414 ms