explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GYRK

Settings
# exclusive inclusive rows x rows loops node
1. 0.706 2,622.478 ↑ 3.3 1,221 1

Nested Loop Left Join (cost=56,541.94..62,425.09 rows=4,018 width=41) (actual time=2,594.581..2,622.478 rows=1,221 loops=1)

  • Output: g.title, s.time_agg, s.video_count
  • Inner Unique: true
  • Buffers: shared hit=3765680 read=1520
  • I/O Timings: read=13.241
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.015..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. 5.968 1,742.477 ↓ 4.1 6,661 1

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

  • Output: v.channel_id
  • Buffers: shared hit=3258745
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.016..0.017 rows=1 loops=1)

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

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

  • Output: v.channel_id
  • Sort Key: v.channel_id
  • Sort Method: quicksort Memory: 4309kB
  • Buffers: shared hit=3258745
9. 8.935 1,659.642 ↓ 21.8 35,493 1

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

  • Output: v.channel_id
  • Workers Planned: 3
  • Params Evaluated: $1
  • Workers Launched: 3
  • Buffers: shared hit=3258745
10. 1,437.540 1,650.707 ↓ 16.9 8,873 4

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

  • Output: v.channel_id
  • Buffers: shared hit=3258741
  • Worker 0: actual time=108.556..1652.195 rows=8775 loops=1
  • Buffers: shared hit=808180
  • Worker 1: actual time=107.708..1652.176 rows=9006 loops=1
  • Buffers: shared hit=827019
  • Worker 2: actual time=110.341..1652.575 rows=9058 loops=1
  • Buffers: shared hit=822388
11. 130.047 213.160 ↓ 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.860..213.160 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=31494
  • Buffers: shared hit=131158
  • Worker 0: actual time=107.619..211.852 rows=191151 loops=1
  • Buffers: shared hit=31826
  • Worker 1: actual time=107.617..212.587 rows=195632 loops=1
  • Buffers: shared hit=32543
  • Worker 2: actual time=108.100..212.575 rows=194504 loops=1
  • Buffers: shared hit=32340
12. 83.113 83.113 ↓ 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=83.113..83.113 rows=770,062 loops=1)

  • Index Cond: (vg.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 (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: 450
  • Buffers: shared hit=3127583
  • Worker 0: actual time=0.007..0.007 rows=0 loops=191151
  • Buffers: shared hit=776354
  • Worker 1: actual time=0.007..0.007 rows=0 loops=195632
  • Buffers: shared hit=794476
  • Worker 2: actual time=0.007..0.007 rows=0 loops=194504
  • Buffers: shared hit=790048
14.          

CTE stats

15. 6.191 2,604.096 ↑ 6.6 1,222 1

GroupAggregate (cost=16,489.71..16,670.52 rows=8,036 width=20) (actual time=2,594.101..2,604.096 rows=1,222 loops=1)

  • Output: vg_1.game_id, (date_trunc('month'::text, v_1.published)), count(v_1.id)
  • Group Key: (date_trunc('month'::text, v_1.published)), vg_1.game_id
  • Buffers: shared hit=3763537
16. 16.975 2,597.905 ↓ 6.3 50,715 1

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

  • Output: vg_1.game_id, (date_trunc('month'::text, v_1.published)), v_1.id
  • Sort Key: (date_trunc('month'::text, v_1.published)), vg_1.game_id
  • Sort Method: quicksort Memory: 5499kB
  • Buffers: shared hit=3763537
17. 0.000 2,580.930 ↓ 6.3 50,715 1

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

  • Output: vg_1.game_id, date_trunc('month'::text, v_1.published), v_1.id
  • Inner Unique: true
  • Buffers: shared hit=3763537
18. 13.781 1,871.216 ↓ 11.1 88,980 1

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

  • Output: v_1.published, v_1.id
  • Buffers: shared hit=3373898
19. 1,744.198 1,744.198 ↓ 4.1 6,661 1

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

  • Output: c.channel_id
  • Buffers: shared hit=3258745
20. 13.322 113.237 ↓ 2.2 13 6,661

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

  • Buffers: shared hit=115153
21. 99.915 99.915 ↓ 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_1 (cost=0.56..1.77 rows=5 width=45) (actual time=0.009..0.015 rows=13 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: 1189
  • Buffers: shared hit=108492
22. 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_2 (cost=0.12..0.15 rows=1 width=52) (actual time=0.000..0.000 rows=0 loops=6,661)

  • Output: v_2.published, v_2.id, v_2.channel_id
  • Index Cond: ((v_2.channel_id = c.channel_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: 0
  • Buffers: shared hit=6661
23. 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_1 (cost=0.56..1.57 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=88,980)

  • Output: vg_1.youtube_video_id, vg_1.game_id
  • Index Cond: (vg_1.youtube_video_id = v_1.id)
  • Heap Fetches: 0
  • Buffers: shared hit=389639
24. 2,604.677 2,604.678 ↑ 3.3 1,221 1

CTE Scan on stats s (cost=0.02..180.83 rows=4,018 width=20) (actual time=2,594.125..2,604.678 rows=1,221 loops=1)

  • Output: s.game_id, s.time_agg, s.video_count
  • Filter: (NOT (hashed SubPlan 5))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=3763537
25.          

SubPlan (forCTE Scan)

26. 0.001 0.001 ↑ 1.0 1 1

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

  • Output: game_ids_1.id
27. 17.094 17.094 ↑ 1.0 1 1,221

Index Scan using games_pkey on topic.games g (cost=0.29..1.42 rows=1 width=29) (actual time=0.014..0.014 rows=1 loops=1,221)

  • Output: g.id, g.title, g.alternative_titles, g.platforms, g.store_ids, g.categories, g.logo_url, g.description
  • Index Cond: (s.game_id = g.id)
  • Buffers: shared hit=2143 read=1520
  • I/O Timings: read=13.241
Planning time : 3.219 ms