explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O8K

Settings
# exclusive inclusive rows x rows loops node
1. 3,065.249 3,065.249 ↓ 3.1 5,290 1

CTE Scan on all_games (cost=734,095.61..741,704.68 rows=1,691 width=32) (actual time=2,926.223..3,065.249 rows=5,290 loops=1)

  • Output: all_games.channel_id
  • Filter: (all_games.games_arr @> '{Fortnite}'::text[])
  • Rows Removed by Filter: 31851
  • Buffers: shared hit=1768293 read=12
  • I/O Timings: read=0.645
2.          

CTE all_games

3. 77.644 3,045.296 ↑ 9.1 37,141 1

GroupAggregate (cost=717,514.64..734,095.61 rows=338,181 width=57) (actual time=2,926.215..3,045.296 rows=37,141 loops=1)

  • Output: v.channel_id, array_agg(g.title)
  • Group Key: v.channel_id
  • Buffers: shared hit=1768293 read=12
  • I/O Timings: read=0.645
4. 901.425 2,967.652 ↑ 5.7 288,016 1

Sort (cost=717,514.64..721,632.54 rows=1,647,160 width=50) (actual time=2,926.199..2,967.652 rows=288,016 loops=1)

  • Output: v.channel_id, g.title
  • Sort Key: v.channel_id
  • Sort Method: quicksort Memory: 38416kB
  • Buffers: shared hit=1768293 read=12
  • I/O Timings: read=0.645
5. 21.649 2,066.227 ↑ 5.7 288,016 1

Gather (cost=191,705.24..547,432.61 rows=1,647,160 width=50) (actual time=508.158..2,066.227 rows=288,016 loops=1)

  • Output: v.channel_id, g.title
  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=1768293 read=12
  • I/O Timings: read=0.645
6. 21.383 2,044.578 ↑ 7.1 57,603 5

Parallel Hash Join (cost=190,705.24..381,716.61 rows=411,790 width=50) (actual time=504.306..2,044.578 rows=57,603 loops=5)

  • Output: v.channel_id, g.title
  • Inner Unique: true
  • Hash Cond: (vg.game_id = g.id)
  • Buffers: shared hit=1768293 read=12
  • I/O Timings: read=0.645
  • Worker 0: actual time=503.442..2061.638 rows=65560 loops=1
  • Buffers: shared hit=369364 read=2
  • I/O Timings: read=0.049
  • Worker 1: actual time=503.640..2062.050 rows=58863 loops=1
  • Buffers: shared hit=404030 read=1
  • I/O Timings: read=0.071
  • Worker 2: actual time=503.603..2063.297 rows=50409 loops=1
  • Buffers: shared hit=351776 read=6
  • I/O Timings: read=0.482
  • Worker 3: actual time=503.284..2061.502 rows=64421 loops=1
  • Buffers: shared hit=324383 read=1
  • I/O Timings: read=0.029
7. 1,237.870 2,014.730 ↑ 7.1 57,603 5

Parallel Hash Join (cost=170,209.43..360,139.76 rows=411,790 width=29) (actual time=495.677..2,014.730 rows=57,603 loops=5)

  • Output: v.channel_id, vg.game_id
  • Hash Cond: (vg.youtube_video_id = v.id)
  • Buffers: shared hit=1748061 read=12
  • I/O Timings: read=0.645
  • Worker 0: actual time=495.761..2032.838 rows=65560 loops=1
  • Buffers: shared hit=365200 read=2
  • I/O Timings: read=0.049
  • Worker 1: actual time=495.667..2032.215 rows=58863 loops=1
  • Buffers: shared hit=399856 read=1
  • I/O Timings: read=0.071
  • Worker 2: actual time=495.637..2032.374 rows=50409 loops=1
  • Buffers: shared hit=347672 read=6
  • I/O Timings: read=0.482
  • Worker 3: actual time=495.676..2032.435 rows=64421 loops=1
  • Buffers: shared hit=321126 read=1
  • I/O Timings: read=0.029
8. 283.244 283.244 ↑ 1.3 3,447,729 5

Parallel Seq Scan on matching.youtube_video_to_game_2 vg (cost=0.00..172,739.62 rows=4,309,662 width=16) (actual time=0.005..283.244 rows=3,447,729 loops=5)

  • Output: vg.youtube_video_id, vg.game_id, vg.matching_score
  • Buffers: shared hit=129643
  • Worker 0: actual time=0.005..269.821 rows=3830292 loops=1
  • Buffers: shared hit=28806
  • Worker 1: actual time=0.004..289.748 rows=3402302 loops=1
  • Buffers: shared hit=25537
  • Worker 2: actual time=0.006..311.829 rows=2968225 loops=1
  • Buffers: shared hit=22330
  • Worker 3: actual time=0.004..279.293 rows=3762632 loops=1
  • Buffers: shared hit=28290
9. 141.481 493.616 ↑ 1.3 322,523 5

Parallel Hash (cost=165,062.05..165,062.05 rows=411,790 width=37) (actual time=493.616..493.616 rows=322,523 loops=5)

  • Output: v.channel_id, v.id
  • Buckets: 2097152 Batches: 1 Memory Usage: 130144kB
  • Buffers: shared hit=1618362 read=12
  • I/O Timings: read=0.645
  • Worker 0: actual time=495.565..495.565 rows=335070 loops=1
  • Buffers: shared hit=336380 read=2
  • I/O Timings: read=0.049
  • Worker 1: actual time=495.566..495.567 rows=372539 loops=1
  • Buffers: shared hit=374305 read=1
  • I/O Timings: read=0.071
  • Worker 2: actual time=495.566..495.566 rows=324262 loops=1
  • Buffers: shared hit=325328 read=6
  • I/O Timings: read=0.482
  • Worker 3: actual time=495.567..495.567 rows=292071 loops=1
  • Buffers: shared hit=292822 read=1
  • I/O Timings: read=0.029
10. 34.307 352.135 ↑ 1.3 322,523 5

Parallel Append (cost=0.12..165,062.05 rows=411,790 width=37) (actual time=0.037..352.135 rows=322,523 loops=5)

  • Buffers: shared hit=1618362 read=12
  • I/O Timings: read=0.645
  • Worker 0: actual time=0.035..349.659 rows=335070 loops=1
  • Buffers: shared hit=336380 read=2
  • I/O Timings: read=0.049
  • Worker 1: actual time=0.045..346.449 rows=372539 loops=1
  • Buffers: shared hit=374305 read=1
  • I/O Timings: read=0.071
  • Worker 2: actual time=0.033..352.187 rows=324262 loops=1
  • Buffers: shared hit=325328 read=6
  • I/O Timings: read=0.482
  • Worker 3: actual time=0.046..355.942 rows=292071 loops=1
  • Buffers: shared hit=292822 read=1
  • I/O Timings: read=0.029
11. 317.820 317.820 ↑ 1.3 322,523 5

Parallel Index Only Scan using videos_y2019_q3_channel_id_published_id_view_count_comment__idx on youtube.videos_y2019_q3 v (cost=0.56..163,001.86 rows=411,790 width=37) (actual time=0.034..317.820 rows=322,523 loops=5)

  • Output: v.channel_id, v.id
  • Index Cond: ((v.published > '2019-08-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: 34238
  • Buffers: shared hit=1618361 read=12
  • I/O Timings: read=0.645
  • Worker 0: actual time=0.034..314.546 rows=335070 loops=1
  • Buffers: shared hit=336380 read=2
  • I/O Timings: read=0.049
  • Worker 1: actual time=0.044..311.581 rows=372539 loops=1
  • Buffers: shared hit=374305 read=1
  • I/O Timings: read=0.071
  • Worker 2: actual time=0.031..318.554 rows=324262 loops=1
  • Buffers: shared hit=325328 read=6
  • I/O Timings: read=0.482
  • Worker 3: actual time=0.044..321.914 rows=292071 loops=1
  • Buffers: shared hit=292822 read=1
  • I/O Timings: read=0.029
12. 0.008 0.008 ↓ 0.0 0 1

Parallel Index Only Scan using videos_default_channel_id_published_id_view_count_comment_c_idx on youtube.videos_default v_1 (cost=0.12..1.24 rows=1 width=44) (actual time=0.008..0.008 rows=0 loops=1)

  • Output: v_1.channel_id, v_1.id
  • Index Cond: ((v_1.published > '2019-08-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: 0
  • Buffers: shared hit=1
13. 3.320 8.465 ↑ 1.6 10,184 5

Parallel Hash (cost=20,287.03..20,287.03 rows=16,703 width=29) (actual time=8.464..8.465 rows=10,184 loops=5)

  • Output: g.title, g.id
  • Buckets: 65536 Batches: 1 Memory Usage: 3808kB
  • Buffers: shared hit=20120
  • Worker 0: actual time=7.588..7.588 rows=10350 loops=1
  • Buffers: shared hit=4136
  • Worker 1: actual time=7.883..7.883 rows=10467 loops=1
  • Buffers: shared hit=4146
  • Worker 2: actual time=7.876..7.876 rows=10336 loops=1
  • Buffers: shared hit=4076
  • Worker 3: actual time=7.510..7.510 rows=7890 loops=1
  • Buffers: shared hit=3229
14. 5.145 5.145 ↑ 1.6 10,184 5

Parallel Seq Scan on topic.games g (cost=0.00..20,287.03 rows=16,703 width=29) (actual time=0.003..5.145 rows=10,184 loops=5)

  • Output: g.title, g.id
  • Buffers: shared hit=20120
  • Worker 0: actual time=0.002..4.770 rows=10350 loops=1
  • Buffers: shared hit=4136
  • Worker 1: actual time=0.002..4.816 rows=10467 loops=1
  • Buffers: shared hit=4146
  • Worker 2: actual time=0.002..4.852 rows=10336 loops=1
  • Buffers: shared hit=4076
  • Worker 3: actual time=0.002..4.492 rows=7890 loops=1
  • Buffers: shared hit=3229
Planning time : 2.615 ms