explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C8Pg

Settings
# exclusive inclusive rows x rows loops node
1. 5.028 1,742.006 ↓ 4.1 6,661 1

Unique (cost=39,862.96..39,871.10 rows=1,628 width=25) (actual time=1,732.864..1,742.006 rows=6,661 loops=1)

  • Output: v.channel_id
  • Buffers: shared hit=3258698
2.          

CTE game_ids

3. 0.013 0.013 ↑ 1.0 1 1

Index Scan using idx_topic_games_title on topic.games (cost=0.41..2.63 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)

  • Output: games.id
  • Index Cond: (games.title = ANY ('{Fortnite}'::text[]))
  • Buffers: shared hit=4
4.          

Initplan (forUnique)

5. 0.014 0.014 ↑ 1.0 1 1

CTE Scan on game_ids (cost=0.00..0.02 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1)

  • Output: game_ids.id
  • Buffers: shared hit=4
6. 72.979 1,736.964 ↓ 21.8 35,493 1

Sort (cost=39,860.31..39,864.38 rows=1,628 width=25) (actual time=1,732.862..1,736.964 rows=35,493 loops=1)

  • Output: v.channel_id
  • Sort Key: v.channel_id
  • Sort Method: quicksort Memory: 4309kB
  • Buffers: shared hit=3258698
7. 7.981 1,663.985 ↓ 21.8 35,493 1

Gather (cost=1,330.47..39,773.46 rows=1,628 width=25) (actual time=120.997..1,663.985 rows=35,493 loops=1)

  • Output: v.channel_id
  • Workers Planned: 3
  • Params Evaluated: $1
  • Workers Launched: 3
  • Buffers: shared hit=3258698
8. 1,432.778 1,656.004 ↓ 16.9 8,873 4

Nested Loop (cost=330.47..38,610.66 rows=525 width=25) (actual time=115.669..1,656.004 rows=8,873 loops=4)

  • Output: v.channel_id
  • Buffers: shared hit=3258694
  • Worker 0: actual time=114.803..1657.367 rows=8975 loops=1
  • Buffers: shared hit=817693
  • Worker 1: actual time=114.740..1658.312 rows=8817 loops=1
  • Buffers: shared hit=813407
  • Worker 2: actual time=112.887..1657.336 rows=8812 loops=1
  • Buffers: shared hit=810844
9. 135.611 223.219 ↓ 22.8 192,516 4

Parallel Bitmap Heap Scan on matching.youtube_video_to_game_2 vg (cost=329.91..25,703.34 rows=8,462 width=12) (actual time=114.092..223.219 rows=192,516 loops=4)

  • Output: vg.youtube_video_id, vg.game_id, vg.matching_score
  • Recheck Cond: (vg.game_id = ANY ($1))
  • Heap Blocks: exact=32030
  • Buffers: shared hit=131158
  • Worker 0: actual time=112.952..222.973 rows=193379 loops=1
  • Buffers: shared hit=32272
  • Worker 1: actual time=113.764..222.608 rows=192391 loops=1
  • Buffers: shared hit=32000
  • Worker 2: actual time=112.790..221.628 rows=191812 loops=1
  • Buffers: shared hit=31901
10. 87.608 87.608 ↓ 29.4 770,062 1

Bitmap Index Scan on youtube_video_to_game_2_game_id_youtube_video_id_idx (cost=0.00..323.35 rows=26,232 width=0) (actual time=87.608..87.608 rows=770,062 loops=1)

  • Index Cond: (vg.game_id = ANY ($1))
  • Buffers: shared hit=2955
11. 0.007 0.007 ↓ 0.0 0 770,062

Index Only Scan using videos_y2019_q2_id_published_channel_id_view_count_comment__idx on youtube.videos_y2019_q2 v (cost=0.56..1.52 rows=1 width=37) (actual time=0.007..0.007 rows=0 loops=770,062)

  • Output: v.id, v.published, v.channel_id, v.view_count, v.comment_count, v.like_count, v.dislike_count, v.favorite_count
  • Index Cond: ((v.id = vg.youtube_video_id) AND (v.published > '2019-04-01 00:00:00+00'::timestamp with time zone) AND (v.published < '2019-05-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 425
  • Buffers: shared hit=3127536
  • Worker 0: actual time=0.007..0.007 rows=0 loops=193379
  • Buffers: shared hit=785421
  • Worker 1: actual time=0.007..0.007 rows=0 loops=192391
  • Buffers: shared hit=781407
  • Worker 2: actual time=0.007..0.007 rows=0 loops=191812
  • Buffers: shared hit=778943
Planning time : 0.652 ms