explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PUHR

Settings
# exclusive inclusive rows x rows loops node
1. 4.968 8,483.775 ↓ 4.1 6,661 1

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

  • Output: v.channel_id
  • Buffers: shared hit=3130483 read=128198
  • I/O Timings: read=464.462
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. 76.489 8,478.793 ↓ 21.8 35,493 1

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

  • Output: v.channel_id
  • Sort Key: v.channel_id
  • Sort Method: quicksort Memory: 4309kB
  • Buffers: shared hit=3130483 read=128198
  • I/O Timings: read=464.462
7. 10.081 8,402.304 ↓ 21.8 35,493 1

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

  • Output: v.channel_id
  • Workers Planned: 3
  • Params Evaluated: $1
  • Workers Launched: 3
  • Buffers: shared hit=3130483 read=128198
  • I/O Timings: read=464.462
8. 1,595.795 8,392.223 ↓ 16.9 8,873 4

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

  • Output: v.channel_id
  • Buffers: shared hit=3130479 read=128198
  • I/O Timings: read=464.462
  • Worker 0: actual time=112.314..8392.949 rows=7838 loops=1
  • Buffers: shared hit=676518 read=27986
  • I/O Timings: read=108.962
  • Worker 1: actual time=112.547..8392.338 rows=9465 loops=1
  • Buffers: shared hit=851016 read=34975
  • I/O Timings: read=123.464
  • Worker 2: actual time=109.928..8395.146 rows=9639 loops=1
  • Buffers: shared hit=844637 read=34533
  • I/O Timings: read=123.130
9. 6,714.222 6,796.420 ↓ 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=108.617..6,796.420 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=30705
  • Buffers: shared hit=2960 read=128198
  • I/O Timings: read=464.462
  • Worker 0: actual time=106.817..6902.179 rows=166542 loops=1
  • Buffers: shared read=27986
  • I/O Timings: read=108.962
  • Worker 1: actual time=106.240..6694.202 rows=209561 loops=1
  • Buffers: shared hit=1 read=34975
  • I/O Timings: read=123.464
  • Worker 2: actual time=109.756..6674.986 rows=207938 loops=1
  • Buffers: shared hit=3 read=34533
  • I/O Timings: read=123.130
10. 82.198 82.198 ↓ 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=82.198..82.198 rows=770,062 loops=1)

  • Index Cond: (vg.game_id = ANY ($1))
  • Buffers: shared hit=2955
11. 0.008 0.008 ↓ 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.008..0.008 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: 418
  • Buffers: shared hit=3127519
  • Worker 0: actual time=0.009..0.009 rows=0 loops=166542
  • Buffers: shared hit=676518
  • Worker 1: actual time=0.008..0.008 rows=0 loops=209561
  • Buffers: shared hit=851015
  • Worker 2: actual time=0.008..0.008 rows=0 loops=207938
  • Buffers: shared hit=844634
Planning time : 0.559 ms