explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pDGD

Settings
# exclusive inclusive rows x rows loops node
1. 5.607 30,727.094 ↑ 6.6 1,222 1

GroupAggregate (cost=56,360.81..56,541.62 rows=8,036 width=20) (actual time=30,718.747..30,727.094 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=3696158 read=67300 dirtied=301
  • I/O Timings: read=27734.177
2.          

CTE game_ids

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

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

CTE channel_ids

5. 8.429 1,798.479 ↓ 4.1 6,661 1

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

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

Initplan (forUnique)

7. 0.015 0.015 ↑ 1.0 1 1

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

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

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

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

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

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

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

  • Output: v_2.channel_id
  • Buffers: shared hit=3258696
  • Worker 0: actual time=109.648..1697.450 rows=9241 loops=1
  • Buffers: shared hit=854161
  • Worker 1: actual time=106.852..1693.276 rows=8324 loops=1
  • Buffers: shared hit=763563
  • Worker 2: actual time=110.336..1698.183 rows=9303 loops=1
  • Buffers: shared hit=846281
11. 133.734 216.259 ↓ 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.315..216.259 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=31191
  • Buffers: shared hit=131158
  • Worker 0: actual time=109.247..216.980 rows=202037 loops=1
  • Buffers: shared hit=33654
  • Worker 1: actual time=105.066..210.838 rows=180592 loops=1
  • Buffers: shared hit=30132
  • Worker 2: actual time=109.826..217.388 rows=200167 loops=1
  • Buffers: shared hit=33226
12. 82.525 82.525 ↓ 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.525..82.525 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: 430
  • Buffers: shared hit=3127538
  • Worker 0: actual time=0.007..0.007 rows=0 loops=202037
  • Buffers: shared hit=820507
  • Worker 1: actual time=0.008..0.008 rows=0 loops=180592
  • Buffers: shared hit=733431
  • Worker 2: actual time=0.007..0.007 rows=0 loops=200167
  • Buffers: shared hit=813055
14. 23.539 30,721.487 ↓ 6.3 50,715 1

Sort (cost=16,489.71..16,509.80 rows=8,036 width=24) (actual time=30,718.739..30,721.487 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=3696158 read=67300 dirtied=301
  • I/O Timings: read=27734.177
15. 70.960 30,697.948 ↓ 6.3 50,715 1

Nested Loop (cost=1.12..15,968.49 rows=8,036 width=24) (actual time=1,799.254..30,697.948 rows=50,715 loops=1)

  • Output: vg.game_id, date_trunc('month'::text, v.published), v.id
  • Inner Unique: true
  • Buffers: shared hit=3696155 read=67300 dirtied=301
  • I/O Timings: read=27734.177
16. 19.472 5,178.708 ↓ 11.1 88,980 1

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

  • Output: v.published, v.id
  • Buffers: shared hit=3365208 read=8608 dirtied=301
  • I/O Timings: read=3170.013
17. 1,802.092 1,802.092 ↓ 4.1 6,661 1

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

  • Output: c.channel_id
  • Buffers: shared hit=3258700
18. 13.322 3,357.144 ↓ 2.2 13 6,661

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

  • Buffers: shared hit=106508 read=8608 dirtied=301
  • I/O Timings: read=3170.013
19. 3,337.161 3,337.161 ↓ 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.379..0.501 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: 1152
  • Buffers: shared hit=99847 read=8608 dirtied=301
  • I/O Timings: read=3170.013
20. 6.661 6.661 ↓ 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.001..0.001 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. 25,448.280 25,448.280 ↑ 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.286..0.286 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=330947 read=58692
  • I/O Timings: read=24564.163
Planning time : 11.351 ms