explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AHlg

Settings
# exclusive inclusive rows x rows loops node
1. 30.318 170,085.045 ↓ 80.8 15,507 1

Unique (cost=5,411.04..5,412.00 rows=192 width=25) (actual time=170,026.003..170,085.045 rows=15,507 loops=1)

  • Output: v.channel_id
  • Buffers: shared hit=3612757 read=439141 dirtied=52
  • I/O Timings: read=162488.275
2. 572.907 170,054.727 ↓ 1,076.2 206,621 1

Sort (cost=5,411.04..5,411.52 rows=192 width=25) (actual time=170,026.001..170,054.727 rows=206,621 loops=1)

  • Output: v.channel_id
  • Sort Key: v.channel_id
  • Sort Method: quicksort Memory: 22287kB
  • Buffers: shared hit=3612757 read=439141 dirtied=52
  • I/O Timings: read=162488.275
3. 0.000 169,481.820 ↓ 1,076.2 206,621 1

Nested Loop (cost=56.63..5,403.76 rows=192 width=25) (actual time=4,238.148..169,481.820 rows=206,621 loops=1)

  • Output: v.channel_id
  • Buffers: shared hit=3612757 read=439141 dirtied=52
  • I/O Timings: read=162488.275
4. 114.228 4,785.649 ↓ 2,312.5 770,062 1

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

  • Output: vg.youtube_video_id
  • Buffers: shared hit=128206 read=8071
  • I/O Timings: read=4110.611
5. 1.127 1.127 ↑ 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=1.125..1.127 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 = ANY ('{Fortnite}'::text[]))
  • Buffers: shared hit=1 read=3
  • I/O Timings: read=1.104
6. 458.598 4,670.294 ↓ 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=4,236.544..4,670.294 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=128205 read=8068
  • I/O Timings: read=4109.506
7. 4,211.696 4,211.696 ↓ 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=4,211.696..4,211.696 rows=770,062 loops=1)

  • Index Cond: (vg.game_id = g.id)
  • Buffers: shared hit=2 read=8068
  • I/O Timings: read=4109.506
8. 1,540.124 164,793.268 ↓ 0.0 0 770,062

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

  • Buffers: shared hit=3484551 read=431070 dirtied=52
  • I/O Timings: read=158377.664
9. 47,743.844 47,743.844 ↓ 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.062..0.062 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=1032164 read=172810 dirtied=15
  • I/O Timings: read=45966.981
10. 75,466.076 75,466.076 ↓ 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.097..0.098 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=931069 read=167021
  • I/O Timings: read=74042.971
11. 40,043.224 40,043.224 ↓ 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.051..0.052 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=751256 read=91239 dirtied=37
  • I/O Timings: read=38367.712
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.980 ms