explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kz3c

Settings
# exclusive inclusive rows x rows loops node
1. 9.762 92,121.019 ↑ 4.7 4,243 1

Sort (cost=2,993,657.09..2,993,706.84 rows=19,900 width=32) (actual time=92,120.825..92,121.019 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=8852941 read=361060 dirtied=71642, temp read=126549 written=126549
  • I/O Timings: read=67747.417
2.          

CTE all_games

3. 353.069 91,922.810 ↑ 3.9 87,454 1

GroupAggregate (cost=2,903,796.86..2,982,671.18 rows=338,181 width=57) (actual time=91,455.536..91,922.810 rows=87,454 loops=1)

  • Output: v.channel_id, array_agg(g.title)
  • Group Key: v.channel_id
  • Buffers: shared hit=8852941 read=361060 dirtied=71642, temp read=126549 written=126549
  • I/O Timings: read=67747.417
4. 3,991.015 91,569.741 ↑ 5.2 1,901,123 1

Sort (cost=2,903,796.86..2,928,679.21 rows=9,952,941 width=50) (actual time=91,455.509..91,569.741 rows=1,901,123 loops=1)

  • Output: v.channel_id, g.title
  • Sort Key: v.channel_id
  • Sort Method: quicksort Memory: 224537kB
  • Buffers: shared hit=8852941 read=361060 dirtied=71642, temp read=126549 written=126549
  • I/O Timings: read=67747.417
5. 443.804 87,578.726 ↑ 5.2 1,901,123 1

Hash Join (cost=622,972.14..1,547,678.27 rows=9,952,941 width=50) (actual time=5,421.041..87,578.726 rows=1,901,123 loops=1)

  • Output: v.channel_id, g.title
  • Inner Unique: true
  • Hash Cond: (vg.game_id = g.id)
  • Buffers: shared hit=8852941 read=361060 dirtied=71642, temp read=126549 written=126549
  • I/O Timings: read=67747.417
6. 7,111.481 87,102.115 ↑ 5.2 1,901,123 1

Hash Join (cost=601,687.14..1,500,264.66 rows=9,952,941 width=29) (actual time=5,388.175..87,102.115 rows=1,901,123 loops=1)

  • Output: v.channel_id, vg.game_id
  • Inner Unique: true
  • Hash Cond: (v.id = vg.youtube_video_id)
  • Buffers: shared hit=8832821 read=361060 dirtied=71642, temp read=126549 written=126549
  • I/O Timings: read=67747.417
7. 907.958 74,615.549 ↑ 1.0 9,896,646 1

Append (cost=0.56..632,761.59 rows=9,952,941 width=37) (actual time=0.045..74,615.549 rows=9,896,646 loops=1)

  • Buffers: shared hit=8704618 read=359620 dirtied=71642
  • I/O Timings: read=67457.559
8. 1,669.477 1,669.477 ↓ 1.0 1,697,667 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..158,454.83 rows=1,628,654 width=37) (actual time=0.044..1,669.477 rows=1,697,667 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: 7467
  • Buffers: shared hit=1293629 read=81652 dirtied=3021
  • I/O Timings: read=688.749
9. 10,953.399 10,953.399 ↑ 1.0 4,915,374 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..204,936.76 rows=4,920,883 width=37) (actual time=0.833..10,953.399 rows=4,915,374 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: 36872
  • Buffers: shared hit=4259077 read=92168 dirtied=14551
  • I/O Timings: read=8353.677
10. 61,084.707 61,084.707 ↑ 1.0 3,283,605 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,604.05 rows=3,403,403 width=37) (actual time=0.555..61,084.707 rows=3,283,605 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: 207378
  • Buffers: shared hit=3151911 read=185800 dirtied=54070
  • I/O Timings: read=58415.134
11. 0.008 0.008 ↓ 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.008..0.008 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. 2,965.614 5,375.085 ↑ 1.0 17,238,647 1

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

  • Output: vg.youtube_video_id, vg.game_id
  • Buckets: 8388608 Batches: 8 Memory Usage: 166550kB
  • Buffers: shared hit=128203 read=1440, temp written=66282
  • I/O Timings: read=289.857
13. 2,409.471 2,409.471 ↑ 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.015..2,409.471 rows=17,238,647 loops=1)

  • Output: vg.youtube_video_id, vg.game_id
  • Buffers: shared hit=128203 read=1440
  • I/O Timings: read=289.857
14. 9.063 32.807 ↑ 1.0 50,922 1

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

  • Output: g.title, g.id
  • Buckets: 65536 Batches: 1 Memory Usage: 3654kB
  • Buffers: shared hit=20120
15. 23.744 23.744 ↑ 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..23.744 rows=50,922 loops=1)

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

CTE games_from_channels_played

17. 67.651 92,109.949 ↑ 4.7 4,244 1

HashAggregate (cost=8,890.00..9,140.00 rows=20,000 width=32) (actual time=92,109.114..92,109.949 rows=4,244 loops=1)

  • Output: (unnest(all_games.games_arr))
  • Group Key: unnest(all_games.games_arr)
  • Buffers: shared hit=8852941 read=361060 dirtied=71642, temp read=126549 written=126549
  • I/O Timings: read=67747.417
18. 33.039 92,042.298 ↓ 2.5 428,173 1

ProjectSet (cost=0.00..8,467.25 rows=169,100 width=32) (actual time=91,455.547..92,042.298 rows=428,173 loops=1)

  • Output: unnest(all_games.games_arr)
  • Buffers: shared hit=8852941 read=361060 dirtied=71642, temp read=126549 written=126549
  • I/O Timings: read=67747.417
19. 92,009.259 92,009.259 ↓ 9.2 15,507 1

CTE Scan on all_games (cost=0.00..7,609.07 rows=1,691 width=32) (actual time=91,455.543..92,009.259 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: 71947
  • Buffers: shared hit=8852941 read=361060 dirtied=71642, temp read=126549 written=126549
  • I/O Timings: read=67747.417
20. 92,111.257 92,111.257 ↑ 4.7 4,243 1

CTE Scan on games_from_channels_played (cost=0.00..425.00 rows=19,900 width=32) (actual time=92,109.118..92,111.257 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=8852941 read=361060 dirtied=71642, temp read=126549 written=126549
  • I/O Timings: read=67747.417
Planning time : 3.879 ms