explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UOHk

Settings
# exclusive inclusive rows x rows loops node
1. 40.428 40,657.996 ↓ 4.3 24,152 1

GroupAggregate (cost=31,533.92..31,659.29 rows=5,572 width=41) (actual time=40,592.155..40,657.996 rows=24,152 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=10979180 read=481262 dirtied=9317
  • I/O Timings: read=21540.995
2.          

CTE channels_played

3. 37.074 7,769.818 ↓ 81.2 15,506 1

Unique (cost=5,411.00..5,411.95 rows=191 width=25) (actual time=7,683.033..7,769.818 rows=15,506 loops=1)

  • Output: v_4.channel_id
  • Buffers: shared hit=3849766 read=322873
  • I/O Timings: read=1344.246
4. 552.905 7,732.744 ↓ 1,081.8 206,617 1

Sort (cost=5,411.00..5,411.47 rows=191 width=25) (actual time=7,683.031..7,732.744 rows=206,617 loops=1)

  • Output: v_4.channel_id
  • Sort Key: v_4.channel_id
  • Sort Method: quicksort Memory: 22286kB
  • Buffers: shared hit=3849766 read=322873
  • I/O Timings: read=1344.246
5. 0.000 7,179.839 ↓ 1,081.8 206,617 1

Nested Loop (cost=56.63..5,403.76 rows=191 width=25) (actual time=129.631..7,179.839 rows=206,617 loops=1)

  • Output: v_4.channel_id
  • Buffers: shared hit=3849766 read=322873
  • I/O Timings: read=1344.246
6. 109.150 1,162.714 ↓ 2,312.5 770,062 1

Nested Loop (cost=56.63..2,955.43 rows=333 width=12) (actual time=129.575..1,162.714 rows=770,062 loops=1)

  • Output: vg_1.youtube_video_id
  • Buffers: shared hit=3870 read=132407
  • I/O Timings: read=404.150
7. 0.025 0.025 ↑ 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.023..0.025 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 = 'Fortnite'::text)
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=0.009
8. 948.399 1,053.539 ↓ 291.8 770,062 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=129.548..1,053.539 rows=770,062 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=128203
  • Buffers: shared hit=3868 read=132405
  • I/O Timings: read=404.141
9. 105.140 105.140 ↓ 291.8 770,062 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=105.140..105.140 rows=770,062 loops=1)

  • Index Cond: (vg_1.game_id = g_1.id)
  • Buffers: shared hit=2 read=8068
  • I/O Timings: read=25.986
10. 770.062 6,160.496 ↓ 0.0 0 770,062

Append (cost=0.00..7.31 rows=4 width=37) (actual time=0.007..0.008 rows=0 loops=770,062)

  • Buffers: shared hit=3845896 read=190466
  • I/O Timings: read=940.096
11. 2,310.186 2,310.186 ↓ 0.0 0 770,062

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.003..0.003 rows=0 loops=770,062)

  • 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=1127251 read=87473
  • I/O Timings: read=399.975
12. 1,540.124 1,540.124 ↓ 0.0 0 770,062

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.002..0.002 rows=0 loops=770,062)

  • 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))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1147882 read=61291
  • I/O Timings: read=282.868
13. 1,540.124 1,540.124 ↓ 0.0 0 770,062

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.002..0.002 rows=0 loops=770,062)

  • 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=800701 read=41702
  • I/O Timings: read=257.253
14. 0.000 0.000 ↓ 0.0 0 770,062

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=770,062)

  • 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=770062
15. 286.256 40,617.568 ↓ 39.8 221,552 1

Sort (cost=26,121.97..26,135.90 rows=5,572 width=45) (actual time=40,592.144..40,617.568 rows=221,552 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: 26944kB
  • Buffers: shared hit=10979180 read=481262 dirtied=9317
  • I/O Timings: read=21540.995
16. 345.378 40,331.312 ↓ 39.8 221,552 1

Nested Loop (cost=1.97..25,775.28 rows=5,572 width=45) (actual time=7,686.740..40,331.312 rows=221,552 loops=1)

  • Output: g.title, date_trunc('week'::text, v.published), v.id
  • Inner Unique: true
  • Buffers: shared hit=10979180 read=481262 dirtied=9317
  • I/O Timings: read=21540.995
17. 82.336 39,557.765 ↓ 76.6 428,169 1

Nested Loop (cost=1.68..12,909.02 rows=5,589 width=24) (actual time=7,683.642..39,557.765 rows=428,169 loops=1)

  • Output: v.published, v.id, vg.game_id
  • Inner Unique: true
  • Buffers: shared hit=9695433 read=480502 dirtied=9317
  • I/O Timings: read=21537.626
18. 183.134 30,413.104 ↓ 180.2 1,006,925 1

Nested Loop (cost=1.11..4,104.74 rows=5,589 width=20) (actual time=7,683.109..30,413.104 rows=1,006,925 loops=1)

  • Output: v.published, v.id
  • Join Filter: (cp.channel_id = v.channel_id)
  • Buffers: shared hit=5407154 read=479895 dirtied=9317
  • I/O Timings: read=21534.672
19. 15.370 8,521.570 ↓ 81.2 15,506 1

Nested Loop (cost=0.56..514.67 rows=191 width=57) (actual time=7,683.068..8,521.570 rows=15,506 loops=1)

  • Output: cp.channel_id, c.id
  • Inner Unique: true
  • Buffers: shared hit=3935086 read=328199 dirtied=216
  • I/O Timings: read=1896.666
20. 7,777.418 7,777.418 ↓ 81.2 15,506 1

CTE Scan on channels_played cp (cost=0.00..3.82 rows=191 width=32) (actual time=7,683.035..7,777.418 rows=15,506 loops=1)

  • Output: cp.channel_id
  • Buffers: shared hit=3849766 read=322873
  • I/O Timings: read=1344.246
21. 728.782 728.782 ↑ 1.0 1 15,506

Index Only Scan using channels_pkey on youtube.channels c (cost=0.56..2.67 rows=1 width=25) (actual time=0.047..0.047 rows=1 loops=15,506)

  • Output: c.id
  • Index Cond: (c.id = cp.channel_id)
  • Heap Fetches: 14803
  • Buffers: shared hit=85320 read=5326 dirtied=216
  • I/O Timings: read=552.421
22. 124.048 21,708.400 ↓ 2.0 65 15,506

Append (cost=0.56..18.40 rows=32 width=45) (actual time=0.108..1.400 rows=65 loops=15,506)

  • Buffers: shared hit=1472068 read=151696 dirtied=9101
  • I/O Timings: read=19638.006
23. 14,187.990 14,187.990 ↓ 2.2 11 15,506

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.104..0.915 rows=11 loops=15,506)

  • 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: 106253
  • Buffers: shared hit=245155 read=48442 dirtied=8271
  • I/O Timings: read=13691.153
24. 4,574.270 4,574.270 ↓ 2.1 33 15,506

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.034..0.295 rows=33 loops=15,506)

  • 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: 261172
  • Buffers: shared hit=712415 read=59922 dirtied=525
  • I/O Timings: read=3740.665
25. 2,806.586 2,806.586 ↓ 2.1 21 15,506

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.032..0.181 rows=21 loops=15,506)

  • 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: 159390
  • Buffers: shared hit=498992 read=43332 dirtied=305
  • I/O Timings: read=2206.188
26. 15.506 15.506 ↓ 0.0 0 15,506

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.001..0.001 rows=0 loops=15,506)

  • 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=15506
27. 9,062.325 9,062.325 ↓ 0.0 0 1,006,925

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.009..0.009 rows=0 loops=1,006,925)

  • Output: vg.youtube_video_id, vg.game_id
  • Index Cond: (vg.youtube_video_id = v.id)
  • Heap Fetches: 0
  • Buffers: shared hit=4288279 read=607
  • I/O Timings: read=2.954
28. 428.169 428.169 ↑ 1.0 1 428,169

Index Scan using games_pkey on topic.games g (cost=0.29..2.30 rows=1 width=29) (actual time=0.001..0.001 rows=1 loops=428,169)

  • 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 <> 'Fortnite'::text)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1283747 read=760
  • I/O Timings: read=3.368
Planning time : 6.576 ms