explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O77C

Settings
# exclusive inclusive rows x rows loops node
1. 5.377 2,592.366 ↑ 6.6 1,222 1

GroupAggregate (cost=56,360.81..56,541.62 rows=8,036 width=20) (actual time=2,584.173..2,592.366 rows=1,222 loops=1)

  • Output: vg.game_id, (date_trunc('month'::text, v.published)), count(v.id)
  • Group Key: (date_trunc('month'::text, v.published)), vg.game_id
  • Buffers: shared hit=3763509
2.          

CTE game_ids

3. 0.015 0.015 ↑ 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.014..0.015 rows=1 loops=1)

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

CTE channel_ids

5. 6.127 1,719.253 ↓ 4.1 6,661 1

Unique (cost=39,860.33..39,868.47 rows=1,628 width=25) (actual time=1,707.324..1,719.253 rows=6,661 loops=1)

  • Output: v_2.channel_id
  • Buffers: shared hit=3258717
6.          

Initplan (forUnique)

7. 0.017 0.017 ↑ 1.0 1 1

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

  • Output: game_ids.id
  • Buffers: shared hit=4
8. 83.180 1,713.109 ↓ 21.8 35,493 1

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

  • Output: v_2.channel_id
  • Sort Key: v_2.channel_id
  • Sort Method: quicksort Memory: 4309kB
  • Buffers: shared hit=3258717
9. 7.494 1,629.929 ↓ 21.8 35,493 1

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

  • Output: v_2.channel_id
  • Workers Planned: 3
  • Params Evaluated: $1
  • Workers Launched: 3
  • Buffers: shared hit=3258717
10. 1,409.520 1,622.435 ↓ 16.9 8,873 4

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

  • Output: v_2.channel_id
  • Buffers: shared hit=3258713
  • Worker 0: actual time=110.298..1623.763 rows=9032 loops=1
  • Buffers: shared hit=814270
  • Worker 1: actual time=109.489..1624.647 rows=8997 loops=1
  • Buffers: shared hit=827703
  • Worker 2: actual time=109.016..1624.207 rows=8649 loops=1
  • Buffers: shared hit=807302
11. 131.005 212.908 ↓ 22.8 192,516 4

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

  • Output: vg_1.youtube_video_id, vg_1.game_id, vg_1.matching_score
  • Recheck Cond: (vg_1.game_id = ANY ($1))
  • Heap Blocks: exact=31705
  • Buffers: shared hit=131158
  • Worker 0: actual time=108.336..211.636 rows=192540 loops=1
  • Buffers: shared hit=32053
  • Worker 1: actual time=109.104..212.727 rows=195770 loops=1
  • Buffers: shared hit=32626
  • Worker 2: actual time=108.729..211.851 rows=190979 loops=1
  • Buffers: shared hit=31819
12. 81.903 81.903 ↓ 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=81.903..81.903 rows=770,062 loops=1)

  • Index Cond: (vg_1.game_id = ANY ($1))
  • Buffers: shared hit=2955
13. 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_2 (cost=0.56..1.52 rows=1 width=37) (actual time=0.007..0.007 rows=0 loops=770,062)

  • Output: v_2.id, v_2.published, v_2.channel_id, v_2.view_count, v_2.comment_count, v_2.like_count, v_2.dislike_count, v_2.favorite_count
  • Index Cond: ((v_2.id = vg_1.youtube_video_id) AND (v_2.published > '2019-04-01 00:00:00+00'::timestamp with time zone) AND (v_2.published < '2019-05-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 450
  • Buffers: shared hit=3127555
  • Worker 0: actual time=0.007..0.007 rows=0 loops=192540
  • Buffers: shared hit=782217
  • Worker 1: actual time=0.007..0.007 rows=0 loops=195770
  • Buffers: shared hit=795077
  • Worker 2: actual time=0.007..0.007 rows=0 loops=190979
  • Buffers: shared hit=775483
14. 15.934 2,586.989 ↓ 6.3 50,715 1

Sort (cost=16,489.71..16,509.80 rows=8,036 width=24) (actual time=2,584.164..2,586.989 rows=50,715 loops=1)

  • Output: vg.game_id, (date_trunc('month'::text, v.published)), v.id
  • Sort Key: (date_trunc('month'::text, v.published)), vg.game_id
  • Sort Method: quicksort Memory: 5499kB
  • Buffers: shared hit=3763509
15. 10.315 2,571.055 ↓ 6.3 50,715 1

Nested Loop (cost=1.12..15,968.49 rows=8,036 width=24) (actual time=1,707.527..2,571.055 rows=50,715 loops=1)

  • Output: vg.game_id, date_trunc('month'::text, v.published), v.id
  • Inner Unique: true
  • Buffers: shared hit=3763509
16. 7.891 1,848.900 ↓ 11.1 88,980 1

Nested Loop (cost=0.56..3,294.15 rows=8,036 width=20) (actual time=1,707.357..1,848.900 rows=88,980 loops=1)

  • Output: v.published, v.id
  • Buffers: shared hit=3373870
17. 1,721.111 1,721.111 ↓ 4.1 6,661 1

CTE Scan on channel_ids c (cost=0.00..32.56 rows=1,628 width=32) (actual time=1,707.326..1,721.111 rows=6,661 loops=1)

  • Output: c.channel_id
  • Buffers: shared hit=3258717
18. 13.322 119.898 ↓ 2.2 13 6,661

Append (cost=0.56..1.94 rows=6 width=45) (actual time=0.009..0.018 rows=13 loops=6,661)

  • Buffers: shared hit=115153
19. 106.576 106.576 ↓ 2.6 13 6,661

Index Only Scan using videos_y2019_q2_channel_id_published_id_view_count_comment__idx on youtube.videos_y2019_q2 v (cost=0.56..1.77 rows=5 width=45) (actual time=0.009..0.016 rows=13 loops=6,661)

  • Output: v.published, v.id, v.channel_id
  • Index Cond: ((v.channel_id = c.channel_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: 1189
  • Buffers: shared hit=108492
20. 0.000 0.000 ↓ 0.0 0 6,661

Index Only Scan using videos_default_channel_id_published_id_view_count_comment_c_idx on youtube.videos_default v_1 (cost=0.12..0.15 rows=1 width=52) (actual time=0.000..0.000 rows=0 loops=6,661)

  • Output: v_1.published, v_1.id, v_1.channel_id
  • Index Cond: ((v_1.channel_id = c.channel_id) AND (v_1.published > '2019-04-01 00:00:00+00'::timestamp with time zone) AND (v_1.published < '2019-05-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
  • Buffers: shared hit=6661
21. 711.840 711.840 ↑ 1.0 1 88,980

Index Only Scan using youtube_video_to_game_2_pkey on matching.youtube_video_to_game_2 vg (cost=0.56..1.57 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=88,980)

  • Output: vg.youtube_video_id, vg.game_id
  • Index Cond: (vg.youtube_video_id = v.id)
  • Heap Fetches: 0
  • Buffers: shared hit=389639
Planning time : 3.397 ms