explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eQFk

Settings
# exclusive inclusive rows x rows loops node
1. 30.366 6,922.871 ↓ 81.2 15,507 1

Unique (cost=5,411.00..5,411.95 rows=191 width=25) (actual time=6,863.499..6,922.871 rows=15,507 loops=1)

  • Output: v.channel_id
  • Buffers: shared hit=4164465 read=8231 dirtied=3
  • I/O Timings: read=1046.236
2. 520.578 6,892.505 ↓ 1,081.8 206,618 1

Sort (cost=5,411.00..5,411.47 rows=191 width=25) (actual time=6,863.497..6,892.505 rows=206,618 loops=1)

  • Output: v.channel_id
  • Sort Key: v.channel_id
  • Sort Method: quicksort Memory: 22287kB
  • Buffers: shared hit=4164465 read=8231 dirtied=3
  • I/O Timings: read=1046.236
3. 393.482 6,371.927 ↓ 1,081.8 206,618 1

Nested Loop (cost=56.63..5,403.76 rows=191 width=25) (actual time=122.705..6,371.927 rows=206,618 loops=1)

  • Output: v.channel_id
  • Buffers: shared hit=4164465 read=8231 dirtied=3
  • I/O Timings: read=1046.236
4. 93.988 588.011 ↓ 2,312.5 770,062 1

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

  • Output: vg.youtube_video_id
  • Buffers: shared hit=130677 read=5600
  • I/O Timings: read=19.706
5. 0.026 0.026 ↑ 1.0 1 1

Index Scan using idx_topic_games_title on topic.games g (cost=0.41..2.63 rows=1 width=4) (actual time=0.024..0.026 rows=1 loops=1)

  • Output: g.id, g.title, g.alternative_titles, g.platforms, g.store_ids, g.categories, g.logo_url, g.description
  • Index Cond: (g.title = 'Fortnite'::text)
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=0.010
6. 397.429 493.997 ↓ 291.8 770,062 1

Bitmap Heap Scan on matching.youtube_video_to_game_2 vg (cost=56.21..2,926.41 rows=2,639 width=16) (actual time=122.634..493.997 rows=770,062 loops=1)

  • Output: vg.youtube_video_id, vg.game_id, vg.matching_score
  • Recheck Cond: (vg.game_id = g.id)
  • Heap Blocks: exact=128203
  • Buffers: shared hit=130675 read=5598
  • I/O Timings: read=19.696
7. 96.568 96.568 ↓ 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=96.568..96.568 rows=770,062 loops=1)

  • Index Cond: (vg.game_id = g.id)
  • Buffers: shared hit=2472 read=5598
  • I/O Timings: read=19.696
8. 770.062 5,390.434 ↓ 0.0 0 770,062

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

  • Buffers: shared hit=4033788 read=2631 dirtied=3
  • I/O Timings: read=1026.529
9. 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 (cost=0.00..2.02 rows=1 width=37) (actual time=0.002..0.002 rows=0 loops=770,062)

  • Output: v.channel_id, v.id
  • Index Cond: (v.id = vg.youtube_video_id)
  • Rows Removed by Index Recheck: 0
  • Filter: ((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) AND (v.view_count > 1000))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1213625 read=1103
  • I/O Timings: read=448.149
10. 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_1 (cost=0.00..2.02 rows=1 width=37) (actual time=0.002..0.002 rows=0 loops=770,062)

  • Output: v_1.channel_id, v_1.id
  • Index Cond: (v_1.id = vg.youtube_video_id)
  • Rows Removed by Index Recheck: 0
  • Filter: ((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) AND (v_1.view_count > 1000))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1208421 read=779
  • I/O Timings: read=323.886
11. 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_2 (cost=0.00..2.01 rows=1 width=37) (actual time=0.002..0.002 rows=0 loops=770,062)

  • Output: v_2.channel_id, v_2.id
  • Index Cond: (v_2.id = vg.youtube_video_id)
  • Rows Removed by Index Recheck: 0
  • Filter: ((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) AND (v_2.view_count > 1000))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=841680 read=749 dirtied=3
  • I/O Timings: read=254.495
12. 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_3 (cost=0.12..1.25 rows=1 width=44) (actual time=0.000..0.000 rows=0 loops=770,062)

  • Output: v_3.channel_id, v_3.id
  • Index Cond: ((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))
  • Filter: (vg.youtube_video_id = v_3.id)
  • Heap Fetches: 0
  • Buffers: shared hit=770062
Planning time : 2.880 ms