explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6jzI

Settings
# exclusive inclusive rows x rows loops node
1. 1.334 3,871.288 ↑ 1.8 3,109 1

GroupAggregate (cost=31,533.92..31,659.29 rows=5,572 width=41) (actual time=3,869.585..3,871.288 rows=3,109 loops=1)

  • Output: g.title, (date_trunc('week'::text, v.published)), count(v.id)
  • Group Key: (date_trunc('week'::text, v.published)), g.title
  • Buffers: shared hit=119186 read=11571 dirtied=461 written=1054
  • I/O Timings: read=3591.746 write=10.767
2.          

CTE channels_played

3. 0.204 424.918 ↑ 2.7 70 1

Unique (cost=5,411.00..5,411.95 rows=191 width=25) (actual time=424.560..424.918 rows=70 loops=1)

  • Output: v_4.channel_id
  • Buffers: shared hit=24580 read=901 dirtied=89 written=132
  • I/O Timings: read=383.353 write=1.379
4. 0.809 424.714 ↓ 5.0 951 1

Sort (cost=5,411.00..5,411.47 rows=191 width=25) (actual time=424.559..424.714 rows=951 loops=1)

  • Output: v_4.channel_id
  • Sort Key: v_4.channel_id
  • Sort Method: quicksort Memory: 99kB
  • Buffers: shared hit=24580 read=901 dirtied=89 written=132
  • I/O Timings: read=383.353 write=1.379
5. 3.024 423.905 ↓ 5.0 951 1

Nested Loop (cost=56.63..5,403.76 rows=191 width=25) (actual time=2.318..423.905 rows=951 loops=1)

  • Output: v_4.channel_id
  • Buffers: shared hit=24580 read=901 dirtied=89 written=132
  • I/O Timings: read=383.353 write=1.379
6. 0.613 7.754 ↓ 12.5 4,173 1

Nested Loop (cost=56.63..2,955.43 rows=333 width=12) (actual time=1.048..7.754 rows=4,173 loops=1)

  • Output: vg_1.youtube_video_id
  • Buffers: shared hit=3946
7. 0.014 0.014 ↑ 1.0 1 1

Index Scan using idx_topic_games_title on topic.games g_1 (cost=0.41..2.63 rows=1 width=4) (actual time=0.012..0.014 rows=1 loops=1)

  • Output: g_1.id, g_1.title, g_1.alternative_titles, g_1.platforms, g_1.store_ids, g_1.categories, g_1.logo_url, g_1.description
  • Index Cond: (g_1.title = 'Brawlhalla'::text)
  • Buffers: shared hit=4
8. 6.534 7.127 ↓ 1.6 4,173 1

Bitmap Heap Scan on matching.youtube_video_to_game_2 vg_1 (cost=56.21..2,926.41 rows=2,639 width=16) (actual time=1.034..7.127 rows=4,173 loops=1)

  • Output: vg_1.youtube_video_id, vg_1.game_id, vg_1.matching_score
  • Recheck Cond: (vg_1.game_id = g_1.id)
  • Heap Blocks: exact=3882
  • Buffers: shared hit=3942
9. 0.593 0.593 ↓ 1.6 4,173 1

Bitmap Index Scan on youtube_video_to_game_2_game_id_idx (cost=0.00..55.56 rows=2,639 width=0) (actual time=0.593..0.593 rows=4,173 loops=1)

  • Index Cond: (vg_1.game_id = g_1.id)
  • Buffers: shared hit=60
10. 4.173 413.127 ↓ 0.0 0 4,173

Append (cost=0.00..7.31 rows=4 width=37) (actual time=0.096..0.099 rows=0 loops=4,173)

  • Buffers: shared hit=20634 read=901 dirtied=89 written=132
  • I/O Timings: read=383.353 write=1.379
11. 150.228 150.228 ↓ 0.0 0 4,173

Index Scan using videos_y2019_q1_id_idx on youtube.videos_y2019_q1 v_4 (cost=0.00..2.02 rows=1 width=37) (actual time=0.036..0.036 rows=0 loops=4,173)

  • Output: v_4.channel_id, v_4.id
  • Index Cond: (v_4.id = vg_1.youtube_video_id)
  • Rows Removed by Index Recheck: 0
  • Filter: ((v_4.published > '2019-03-01 00:00:00+00'::timestamp with time zone) AND (v_4.published < '2019-09-01 00:00:00+00'::timestamp with time zone) AND (v_4.view_count > 1000))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=5979 read=346 dirtied=38 written=52
  • I/O Timings: read=140.756 write=0.558
12. 254.553 254.553 ↓ 0.0 0 4,173

Index Scan using videos_y2019_q2_id_idx on youtube.videos_y2019_q2 v_5 (cost=0.00..2.02 rows=1 width=37) (actual time=0.058..0.061 rows=0 loops=4,173)

  • Output: v_5.channel_id, v_5.id
  • Index Cond: (v_5.id = vg_1.youtube_video_id)
  • Rows Removed by Index Recheck: 0
  • Filter: ((v_5.published > '2019-03-01 00:00:00+00'::timestamp with time zone) AND (v_5.published < '2019-09-01 00:00:00+00'::timestamp with time zone) AND (v_5.view_count > 1000))
  • Buffers: shared hit=5952 read=555 dirtied=48 written=80
  • I/O Timings: read=242.596 write=0.821
13. 4.173 4.173 ↓ 0.0 0 4,173

Index Scan using videos_y2019_q3_id_idx on youtube.videos_y2019_q3 v_6 (cost=0.00..2.01 rows=1 width=37) (actual time=0.001..0.001 rows=0 loops=4,173)

  • Output: v_6.channel_id, v_6.id
  • Index Cond: (v_6.id = vg_1.youtube_video_id)
  • Rows Removed by Index Recheck: 0
  • Filter: ((v_6.published > '2019-03-01 00:00:00+00'::timestamp with time zone) AND (v_6.published < '2019-09-01 00:00:00+00'::timestamp with time zone) AND (v_6.view_count > 1000))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4530 dirtied=3
14. 0.000 0.000 ↓ 0.0 0 4,173

Index Only Scan using videos_default_channel_id_published_id_view_count_comment_c_idx on youtube.videos_default v_7 (cost=0.12..1.25 rows=1 width=44) (actual time=0.000..0.000 rows=0 loops=4,173)

  • Output: v_7.channel_id, v_7.id
  • Index Cond: ((v_7.published > '2019-03-01 00:00:00+00'::timestamp with time zone) AND (v_7.published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Filter: (vg_1.youtube_video_id = v_7.id)
  • Heap Fetches: 0
  • Buffers: shared hit=4173
15. 9.750 3,869.954 ↓ 1.3 7,437 1

Sort (cost=26,121.97..26,135.90 rows=5,572 width=45) (actual time=3,869.578..3,869.954 rows=7,437 loops=1)

  • Output: g.title, (date_trunc('week'::text, v.published)), v.id
  • Sort Key: (date_trunc('week'::text, v.published)), g.title
  • Sort Method: quicksort Memory: 894kB
  • Buffers: shared hit=119186 read=11571 dirtied=461 written=1054
  • I/O Timings: read=3591.746 write=10.767
16. 11.564 3,860.204 ↓ 1.3 7,437 1

Nested Loop (cost=1.97..25,775.28 rows=5,572 width=45) (actual time=424.671..3,860.204 rows=7,437 loops=1)

  • Output: g.title, date_trunc('week'::text, v.published), v.id
  • Inner Unique: true
  • Buffers: shared hit=119186 read=11571 dirtied=461 written=1054
  • I/O Timings: read=3591.746 write=10.767
17. 5.613 3,831.864 ↓ 1.5 8,388 1

Nested Loop (cost=1.68..12,909.02 rows=5,589 width=24) (actual time=424.656..3,831.864 rows=8,388 loops=1)

  • Output: v.published, v.id, vg.game_id
  • Inner Unique: true
  • Buffers: shared hit=94178 read=11415 dirtied=461 written=1051
  • I/O Timings: read=3590.627 write=10.736
18. 3.491 2,937.831 ↓ 2.4 13,668 1

Nested Loop (cost=1.11..4,104.74 rows=5,589 width=20) (actual time=424.640..2,937.831 rows=13,668 loops=1)

  • Output: v.published, v.id
  • Join Filter: (cp.channel_id = v.channel_id)
  • Buffers: shared hit=39449 read=7205 dirtied=461 written=650
  • I/O Timings: read=2843.626 write=6.705
19. 0.201 435.410 ↑ 2.7 70 1

Nested Loop (cost=0.56..514.67 rows=191 width=57) (actual time=424.611..435.410 rows=70 loops=1)

  • Output: cp.channel_id, c.id
  • Inner Unique: true
  • Buffers: shared hit=24934 read=958 dirtied=93 written=135
  • I/O Timings: read=392.426 write=1.408
20. 424.989 424.989 ↑ 2.7 70 1

CTE Scan on channels_played cp (cost=0.00..3.82 rows=191 width=32) (actual time=424.562..424.989 rows=70 loops=1)

  • Output: cp.channel_id
  • Buffers: shared hit=24580 read=901 dirtied=89 written=132
  • I/O Timings: read=383.353 write=1.379
21. 10.220 10.220 ↑ 1.0 1 70

Index Only Scan using channels_pkey on youtube.channels c (cost=0.56..2.67 rows=1 width=25) (actual time=0.146..0.146 rows=1 loops=70)

  • Output: c.id
  • Index Cond: (c.id = cp.channel_id)
  • Heap Fetches: 67
  • Buffers: shared hit=354 read=57 dirtied=4 written=3
  • I/O Timings: read=9.073 write=0.029
22. 2.170 2,498.930 ↓ 6.1 195 70

Append (cost=0.56..18.40 rows=32 width=45) (actual time=0.733..35.699 rows=195 loops=70)

  • Buffers: shared hit=14515 read=6247 dirtied=368 written=515
  • I/O Timings: read=2451.200 write=5.297
23. 359.030 359.030 ↓ 6.0 30 70

Index Only Scan using videos_y2019_q1_channel_id_published_id_view_count_comment__idx on youtube.videos_y2019_q1 v (cost=0.56..3.53 rows=5 width=45) (actual time=0.673..5.129 rows=30 loops=70)

  • Output: v.published, v.id, v.channel_id
  • Index Cond: ((v.channel_id = c.id) AND (v.published > '2019-03-01 00:00:00+00'::timestamp with time zone) AND (v.published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 1275
  • Buffers: shared hit=1938 read=867 dirtied=84 written=89
  • I/O Timings: read=351.941 write=0.889
24. 1,519.560 1,519.560 ↓ 6.3 101 70

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..7.99 rows=16 width=45) (actual time=0.719..21.708 rows=101 loops=70)

  • Output: v_1.published, v_1.id, v_1.channel_id
  • Index Cond: ((v_1.channel_id = c.id) AND (v_1.published > '2019-03-01 00:00:00+00'::timestamp with time zone) AND (v_1.published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 4570
  • Buffers: shared hit=7720 read=3534 dirtied=174 written=284
  • I/O Timings: read=1494.639 write=3.025
25. 618.030 618.030 ↓ 6.5 65 70

Index Only Scan using videos_y2019_q3_channel_id_published_id_view_count_comment__idx on youtube.videos_y2019_q3 v_2 (cost=0.56..6.56 rows=10 width=45) (actual time=0.446..8.829 rows=65 loops=70)

  • Output: v_2.published, v_2.id, v_2.channel_id
  • Index Cond: ((v_2.channel_id = c.id) AND (v_2.published > '2019-03-01 00:00:00+00'::timestamp with time zone) AND (v_2.published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 2262
  • Buffers: shared hit=4787 read=1846 dirtied=110 written=142
  • I/O Timings: read=604.620 write=1.383
26. 0.140 0.140 ↓ 0.0 0 70

Index Only Scan using videos_default_channel_id_published_id_view_count_comment_c_idx on youtube.videos_default v_3 (cost=0.12..0.15 rows=1 width=52) (actual time=0.002..0.002 rows=0 loops=70)

  • Output: v_3.published, v_3.id, v_3.channel_id
  • Index Cond: ((v_3.channel_id = c.id) AND (v_3.published > '2019-03-01 00:00:00+00'::timestamp with time zone) AND (v_3.published < '2019-09-01 00:00:00+00'::timestamp with time zone))
  • Heap Fetches: 0
  • Buffers: shared hit=70
27. 888.420 888.420 ↑ 1.0 1 13,668

Index Only Scan using youtube_video_to_game_2_pkey on matching.youtube_video_to_game_2 vg (cost=0.56..1.58 rows=1 width=16) (actual time=0.065..0.065 rows=1 loops=13,668)

  • Output: vg.youtube_video_id, vg.game_id
  • Index Cond: (vg.youtube_video_id = v.id)
  • Heap Fetches: 0
  • Buffers: shared hit=54729 read=4210 written=401
  • I/O Timings: read=747.001 write=4.031
28. 16.776 16.776 ↑ 1.0 1 8,388

Index Scan using games_pkey on topic.games g (cost=0.29..2.30 rows=1 width=29) (actual time=0.002..0.002 rows=1 loops=8,388)

  • Output: g.id, g.title, g.alternative_titles, g.platforms, g.store_ids, g.categories, g.logo_url, g.description
  • Index Cond: (g.id = vg.game_id)
  • Filter: (g.title <> 'Brawlhalla'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=25008 read=156 written=3
  • I/O Timings: read=1.119 write=0.031
Planning time : 6.647 ms