explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LXHP

Settings
# exclusive inclusive rows x rows loops node
1. 18.268 5,936.801 ↑ 7.3 6,661 1

HashAggregate (cost=291,078.53..291,566.33 rows=48,780 width=25) (actual time=5,935.073..5,936.801 rows=6,661 loops=1)

  • Output: v.channel_id
  • Group Key: v.channel_id
  • Buffers: shared hit=3190253 read=73216
  • I/O Timings: read=21883.389
2. 1.598 5,918.533 ↑ 1.4 35,493 1

Gather (cost=17,553.58..290,956.58 rows=48,780 width=25) (actual time=130.267..5,918.533 rows=35,493 loops=1)

  • Output: v.channel_id
  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=3190253 read=73216
  • I/O Timings: read=21883.389
3. 5,718.145 5,916.935 ↑ 1.7 7,099 5

Nested Loop (cost=16,553.58..285,078.58 rows=12,195 width=25) (actual time=127.908..5,916.935 rows=7,099 loops=5)

  • Output: v.channel_id
  • Buffers: shared hit=3190253 read=73216
  • I/O Timings: read=21883.389
  • Worker 0: actual time=148.068..5920.122 rows=7957 loops=1
  • Buffers: shared hit=711915 read=15321
  • I/O Timings: read=4377.101
  • Worker 1: actual time=103.143..5920.258 rows=7788 loops=1
  • Buffers: shared hit=708274 read=14844
  • I/O Timings: read=4388.423
  • Worker 2: actual time=109.718..5920.416 rows=6046 loops=1
  • Buffers: shared hit=536529 read=14195
  • I/O Timings: read=4365.821
  • Worker 3: actual time=148.741..5920.165 rows=7891 loops=1
  • Buffers: shared hit=710552 read=14964
  • I/O Timings: read=4386.833
4. 117.002 198.753 ↑ 1.3 154,012 5

Parallel Bitmap Heap Scan on matching.youtube_video_to_game_2 vg (cost=16,553.02..148,651.81 rows=196,463 width=12) (actual time=104.128..198.753 rows=154,012 loops=5)

  • Output: vg.youtube_video_id, vg.game_id, vg.matching_score
  • Recheck Cond: (vg.game_id = 15033)
  • Heap Blocks: exact=20591
  • Buffers: shared hit=136273
  • Worker 0: actual time=102.236..200.651 rows=171987 loops=1
  • Buffers: shared hit=28741
  • Worker 1: actual time=102.252..200.924 rows=171052 loops=1
  • Buffers: shared hit=28654
  • Worker 2: actual time=102.133..192.937 rows=130296 loops=1
  • Buffers: shared hit=21507
  • Worker 3: actual time=102.185..200.445 rows=171595 loops=1
  • Buffers: shared hit=28710
5. 81.751 81.751 ↑ 1.0 770,062 1

Bitmap Index Scan on youtube_video_to_game_2_game_id_idx (cost=0.00..16,356.56 rows=785,853 width=0) (actual time=81.751..81.751 rows=770,062 loops=1)

  • Index Cond: (vg.game_id = 15033)
  • Buffers: shared hit=8070
6. 0.037 0.037 ↓ 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..0.68 rows=1 width=37) (actual time=0.037..0.037 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: 58
  • Buffers: shared hit=3053980 read=73216
  • I/O Timings: read=21883.389
  • Worker 0: actual time=0.033..0.033 rows=0 loops=171987
  • Buffers: shared hit=683174 read=15321
  • I/O Timings: read=4377.101
  • Worker 1: actual time=0.033..0.033 rows=0 loops=171052
  • Buffers: shared hit=679620 read=14844
  • I/O Timings: read=4388.423
  • Worker 2: actual time=0.044..0.044 rows=0 loops=130296
  • Buffers: shared hit=515022 read=14195
  • I/O Timings: read=4365.821
  • Worker 3: actual time=0.033..0.033 rows=0 loops=171595
  • Buffers: shared hit=681842 read=14964
  • I/O Timings: read=4386.833
Planning time : 0.512 ms