explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iBUP

Settings
# exclusive inclusive rows x rows loops node
1. 40.342 16,451.771 ↓ 4.3 24,152 1

GroupAggregate (cost=31,300.13..31,425.50 rows=5,572 width=41) (actual time=16,387.118..16,451.771 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=11355687 read=13
  • I/O Timings: read=0.136
2.          

CTE channels_played

3. 32.393 5,731.385 ↓ 81.2 15,506 1

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

  • Output: v_4.channel_id
  • Buffers: shared hit=4172668
4. 520.797 5,698.992 ↓ 1,081.8 206,617 1

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

  • Output: v_4.channel_id
  • Sort Key: v_4.channel_id
  • Sort Method: quicksort Memory: 22286kB
  • Buffers: shared hit=4172668
5. 1.242 5,178.195 ↓ 1,081.8 206,617 1

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

  • Output: v_4.channel_id
  • Buffers: shared hit=4172668
6. 100.551 556.581 ↓ 2,312.5 770,062 1

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

  • Output: vg_1.youtube_video_id
  • Buffers: shared hit=136277
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.013..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 = 'Fortnite'::text)
  • Buffers: shared hit=4
8. 386.591 456.016 ↓ 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=95.355..456.016 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=136273
9. 69.425 69.425 ↓ 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=69.425..69.425 rows=770,062 loops=1)

  • Index Cond: (vg_1.game_id = g_1.id)
  • Buffers: shared hit=8070
10. 770.062 4,620.372 ↓ 0.0 0 770,062

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

  • Buffers: shared hit=4036391
11. 1,540.124 1,540.124 ↓ 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.002..0.002 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=1214728
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=1209183
13. 770.062 770.062 ↓ 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.001..0.001 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=842418
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. 270.824 16,411.429 ↓ 39.8 221,552 1

Sort (cost=25,888.18..25,902.11 rows=5,572 width=45) (actual time=16,387.106..16,411.429 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=11355687 read=13
  • I/O Timings: read=0.136
16. 227.070 16,140.605 ↓ 39.8 221,552 1

Nested Loop (cost=1.41..25,541.49 rows=5,572 width=45) (actual time=5,655.454..16,140.605 rows=221,552 loops=1)

  • Output: g.title, date_trunc('week'::text, v.published), v.id
  • Inner Unique: true
  • Buffers: shared hit=11355687 read=13
  • I/O Timings: read=0.136
17. 75.705 15,485.366 ↓ 76.6 428,169 1

Nested Loop (cost=1.12..12,694.92 rows=5,589 width=24) (actual time=5,654.664..15,485.366 rows=428,169 loops=1)

  • Output: v.published, v.id, vg.game_id
  • Inner Unique: true
  • Buffers: shared hit=10071180 read=13
  • I/O Timings: read=0.136
18. 114.032 7,354.253 ↓ 180.2 1,006,926 1

Nested Loop (cost=0.56..3,895.19 rows=5,589 width=20) (actual time=5,654.241..7,354.253 rows=1,006,926 loops=1)

  • Output: v.published, v.id
  • Buffers: shared hit=5782290 read=13
  • I/O Timings: read=0.136
19. 5,736.139 5,736.139 ↓ 81.2 15,506 1

CTE Scan on channels_played cp (cost=0.00..3.82 rows=191 width=32) (actual time=5,654.206..5,736.139 rows=15,506 loops=1)

  • Output: cp.channel_id
  • Buffers: shared hit=4172668
20. 108.542 1,504.082 ↓ 2.0 65 15,506

Append (cost=0.56..20.05 rows=32 width=45) (actual time=0.012..0.097 rows=65 loops=15,506)

  • Buffers: shared hit=1609622 read=13
  • I/O Timings: read=0.136
21. 294.614 294.614 ↓ 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.83 rows=5 width=45) (actual time=0.009..0.019 rows=11 loops=15,506)

  • Output: v.published, v.id, v.channel_id
  • Index Cond: ((v.channel_id = cp.channel_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: 90611
  • Buffers: shared hit=280101 read=1
  • I/O Timings: read=0.009
22. 635.746 635.746 ↓ 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..8.73 rows=16 width=45) (actual time=0.010..0.041 rows=33 loops=15,506)

  • Output: v_1.published, v_1.id, v_1.channel_id
  • Index Cond: ((v_1.channel_id = cp.channel_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: 260517
  • Buffers: shared hit=771236 read=10
  • I/O Timings: read=0.096
23. 465.180 465.180 ↓ 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..7.17 rows=10 width=45) (actual time=0.009..0.030 rows=21 loops=15,506)

  • Output: v_2.published, v_2.id, v_2.channel_id
  • Index Cond: ((v_2.channel_id = cp.channel_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: 159828
  • Buffers: shared hit=542779 read=2
  • I/O Timings: read=0.031
24. 0.000 0.000 ↓ 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.000..0.000 rows=0 loops=15,506)

  • Output: v_3.published, v_3.id, v_3.channel_id
  • Index Cond: ((v_3.channel_id = cp.channel_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
25. 8,055.408 8,055.408 ↓ 0.0 0 1,006,926

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=0 loops=1,006,926)

  • Output: vg.youtube_video_id, vg.game_id
  • Index Cond: (vg.youtube_video_id = v.id)
  • Heap Fetches: 0
  • Buffers: shared hit=4288890
26. 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=1284507
Planning time : 6.029 ms