explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VQBX

Settings
# exclusive inclusive rows x rows loops node
1. 21.404 6,309.168 ↑ 46.7 6,661 1

HashAggregate (cost=36,597.21..39,710.30 rows=311,309 width=25) (actual time=6,306.543..6,309.168 rows=6,661 loops=1)

  • Output: v.channel_id
  • Group Key: v.channel_id
  • Buffers: shared hit=3706977
2.          

CTE game_video_ids

3. 140.008 140.008 ↑ 1.0 770,062 1

Index Only Scan using youtube_video_to_game_2_game_id_youtube_video_id_idx on matching.youtube_video_to_game_2 (cost=0.56..16,790.17 rows=772,406 width=12) (actual time=0.016..140.008 rows=770,062 loops=1)

  • Output: youtube_video_to_game_2.youtube_video_id
  • Index Cond: (youtube_video_to_game_2.game_id = 15033)
  • Heap Fetches: 0
  • Buffers: shared hit=579135
4. 9.219 6,287.764 ↑ 23.5 35,493 1

Nested Loop (cost=17,379.69..17,720.24 rows=834,724 width=25) (actual time=653.490..6,287.764 rows=35,493 loops=1)

  • Output: v.channel_id
  • Buffers: shared hit=3706977
5. 566.341 888.111 ↓ 3,850.3 770,062 1

HashAggregate (cost=17,379.14..17,381.14 rows=200 width=32) (actual time=653.239..888.111 rows=770,062 loops=1)

  • Output: game_video_ids.youtube_video_id
  • Group Key: game_video_ids.youtube_video_id
  • Buffers: shared hit=579135
6. 321.770 321.770 ↑ 1.0 770,062 1

CTE Scan on game_video_ids (cost=0.00..15,448.12 rows=772,406 width=32) (actual time=0.018..321.770 rows=770,062 loops=1)

  • Output: game_video_ids.youtube_video_id
  • Buffers: shared hit=579135
7. 5,390.434 5,390.434 ↓ 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.69 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 = game_video_ids.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: 386
  • Buffers: shared hit=3127842
Planning time : 0.471 ms