explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N4QF

Settings
# exclusive inclusive rows x rows loops node
1. 9.964 982,896.774 ↑ 4.7 4,243 1

Sort (cost=5,432,087.85..5,432,137.60 rows=19,900 width=32) (actual time=982,896.563..982,896.774 rows=4,243 loops=1)

  • Output: games_from_channels_played.dist_games
  • Sort Key: games_from_channels_played.dist_games
  • Sort Method: quicksort Memory: 461kB
  • Buffers: shared hit=10276709 read=2823587 dirtied=234421, temp read=126546 written=126546
  • I/O Timings: read=943475.291
2.          

CTE all_games

3. 388.944 982,666.034 ↑ 3.9 87,451 1

GroupAggregate (cost=5,342,435.42..5,421,101.94 rows=338,181 width=57) (actual time=982,142.546..982,666.034 rows=87,451 loops=1)

  • Output: v.channel_id, array_agg(g.title)
  • Group Key: v.channel_id
  • Buffers: shared hit=10276709 read=2823587 dirtied=234421, temp read=126546 written=126546
  • I/O Timings: read=943475.291
4. 4,235.812 982,277.090 ↑ 5.2 1,901,118 1

Sort (cost=5,342,435.42..5,367,248.50 rows=9,925,234 width=50) (actual time=982,142.517..982,277.090 rows=1,901,118 loops=1)

  • Output: v.channel_id, g.title
  • Sort Key: v.channel_id
  • Sort Method: quicksort Memory: 224537kB
  • Buffers: shared hit=10276709 read=2823587 dirtied=234421, temp read=126546 written=126546
  • I/O Timings: read=943475.291
5. 529.207 978,041.278 ↑ 5.2 1,901,118 1

Hash Join (cost=622,972.14..3,990,290.39 rows=9,925,234 width=50) (actual time=5,218.672..978,041.278 rows=1,901,118 loops=1)

  • Output: v.channel_id, g.title
  • Inner Unique: true
  • Hash Cond: (vg.game_id = g.id)
  • Buffers: shared hit=10276709 read=2823587 dirtied=234421, temp read=126546 written=126546
  • I/O Timings: read=943475.291
6. 9,344.695 977,480.645 ↑ 5.2 1,901,118 1

Hash Join (cost=601,687.14..3,942,949.51 rows=9,925,234 width=29) (actual time=5,187.186..977,480.645 rows=1,901,118 loops=1)

  • Output: v.channel_id, vg.game_id
  • Inner Unique: true
  • Hash Cond: (v.id = vg.youtube_video_id)
  • Buffers: shared hit=10256589 read=2823587 dirtied=234421, temp read=126546 written=126546
  • I/O Timings: read=943475.291
7. 1,318.417 962,970.518 ↑ 1.0 9,896,363 1

Append (cost=0.56..3,075,953.18 rows=9,925,234 width=37) (actual time=0.864..962,970.518 rows=9,896,363 loops=1)

  • Buffers: shared hit=10126946 read=2823587 dirtied=234421
  • I/O Timings: read=943475.291
8. 325,588.223 325,588.223 ↓ 1.0 1,697,620 1

Index Only Scan using videos_y2019_q1_channel_id_published_id_view_count_comment__idx on youtube.videos_y2019_q1 v (cost=0.56..758,334.99 rows=1,619,851 width=37) (actual time=0.863..325,588.223 rows=1,697,620 loops=1)

  • Output: v.channel_id, v.id
  • Index Cond: ((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: 1091545
  • Buffers: shared hit=1594439 read=810503 dirtied=101896
  • I/O Timings: read=320857.555
9. 614,813.835 614,813.835 ↓ 1.0 4,915,339 1

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..2,048,415.39 rows=4,901,979 width=37) (actual time=0.398..614,813.835 rows=4,915,339 loops=1)

  • Output: v_1.channel_id, v_1.id
  • Index Cond: ((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: 2875976
  • Buffers: shared hit=5431822 read=1871245 dirtied=126818
  • I/O Timings: read=603583.978
10. 21,250.036 21,250.036 ↑ 1.0 3,283,404 1

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..219,575.38 rows=3,403,403 width=37) (actual time=0.024..21,250.036 rows=3,283,404 loops=1)

  • Output: v_2.channel_id, v_2.id
  • Index Cond: ((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: 115182
  • Buffers: shared hit=3100684 read=141839 dirtied=5707
  • I/O Timings: read=19033.757
11. 0.007 0.007 ↓ 0.0 0 1

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.007..0.007 rows=0 loops=1)

  • 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))
  • Heap Fetches: 0
  • Buffers: shared hit=1
12. 3,147.293 5,165.432 ↑ 1.0 17,238,647 1

Hash (cost=302,029.48..302,029.48 rows=17,238,648 width=16) (actual time=5,165.431..5,165.432 rows=17,238,647 loops=1)

  • Output: vg.youtube_video_id, vg.game_id
  • Buckets: 8388608 Batches: 8 Memory Usage: 166550kB
  • Buffers: shared hit=129643, temp written=66282
13. 2,018.139 2,018.139 ↑ 1.0 17,238,647 1

Seq Scan on matching.youtube_video_to_game_2 vg (cost=0.00..302,029.48 rows=17,238,648 width=16) (actual time=0.014..2,018.139 rows=17,238,647 loops=1)

  • Output: vg.youtube_video_id, vg.game_id
  • Buffers: shared hit=129643
14. 9.515 31.426 ↑ 1.0 50,922 1

Hash (cost=20,637.78..20,637.78 rows=51,778 width=29) (actual time=31.425..31.426 rows=50,922 loops=1)

  • Output: g.title, g.id
  • Buckets: 65536 Batches: 1 Memory Usage: 3654kB
  • Buffers: shared hit=20120
15. 21.911 21.911 ↑ 1.0 50,922 1

Seq Scan on topic.games g (cost=0.00..20,637.78 rows=51,778 width=29) (actual time=0.008..21.911 rows=50,922 loops=1)

  • Output: g.title, g.id
  • Buffers: shared hit=20120
16.          

CTE games_from_channels_played

17. 72.677 982,885.394 ↑ 4.7 4,244 1

HashAggregate (cost=8,890.00..9,140.00 rows=20,000 width=32) (actual time=982,884.480..982,885.394 rows=4,244 loops=1)

  • Output: (unnest(all_games.games_arr))
  • Group Key: unnest(all_games.games_arr)
  • Buffers: shared hit=10276709 read=2823587 dirtied=234421, temp read=126546 written=126546
  • I/O Timings: read=943475.291
18. 36.897 982,812.717 ↓ 2.5 428,173 1

ProjectSet (cost=0.00..8,467.25 rows=169,100 width=32) (actual time=982,142.557..982,812.717 rows=428,173 loops=1)

  • Output: unnest(all_games.games_arr)
  • Buffers: shared hit=10276709 read=2823587 dirtied=234421, temp read=126546 written=126546
  • I/O Timings: read=943475.291
19. 982,775.820 982,775.820 ↓ 9.2 15,507 1

CTE Scan on all_games (cost=0.00..7,609.07 rows=1,691 width=32) (actual time=982,142.553..982,775.820 rows=15,507 loops=1)

  • Output: all_games.channel_id, all_games.games_arr
  • Filter: (all_games.games_arr @> '{Fortnite}'::text[])
  • Rows Removed by Filter: 71944
  • Buffers: shared hit=10276709 read=2823587 dirtied=234421, temp read=126546 written=126546
  • I/O Timings: read=943475.291
20. 982,886.810 982,886.810 ↑ 4.7 4,243 1

CTE Scan on games_from_channels_played (cost=0.00..425.00 rows=19,900 width=32) (actual time=982,884.484..982,886.810 rows=4,243 loops=1)

  • Output: games_from_channels_played.dist_games
  • Filter: (games_from_channels_played.dist_games <> ALL ('{Fortnite}'::text[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=10276709 read=2823587 dirtied=234421, temp read=126546 written=126546
  • I/O Timings: read=943475.291
Planning time : 3.029 ms