explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wIcY

Settings
# exclusive inclusive rows x rows loops node
1. 82.608 4,178.737 ↑ 9.1 37,141 1

GroupAggregate (cost=717,514.64..734,095.61 rows=338,181 width=57) (actual time=4,054.198..4,178.737 rows=37,141 loops=1)

  • Output: v.channel_id, array_agg(g.title)
  • Group Key: v.channel_id
  • Buffers: shared hit=1668313 read=96243 dirtied=11060
  • I/O Timings: read=2018.692
2. 877.135 4,096.129 ↑ 5.7 288,016 1

Sort (cost=717,514.64..721,632.54 rows=1,647,160 width=50) (actual time=4,054.182..4,096.129 rows=288,016 loops=1)

  • Output: v.channel_id, g.title
  • Sort Key: v.channel_id
  • Sort Method: quicksort Memory: 38416kB
  • Buffers: shared hit=1668313 read=96243 dirtied=11060
  • I/O Timings: read=2018.692
3. 31.496 3,218.994 ↑ 5.7 288,016 1

Gather (cost=191,705.24..547,432.61 rows=1,647,160 width=50) (actual time=1,562.671..3,218.994 rows=288,016 loops=1)

  • Output: v.channel_id, g.title
  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=1668313 read=96243 dirtied=11060
  • I/O Timings: read=2018.692
4. 21.367 3,187.498 ↑ 7.1 57,603 5

Parallel Hash Join (cost=190,705.24..381,716.61 rows=411,790 width=50) (actual time=1,556.504..3,187.498 rows=57,603 loops=5)

  • Output: v.channel_id, g.title
  • Inner Unique: true
  • Hash Cond: (vg.game_id = g.id)
  • Buffers: shared hit=1668313 read=96243 dirtied=11060
  • I/O Timings: read=2018.692
  • Worker 0: actual time=1554.854..3204.357 rows=64864 loops=1
  • Buffers: shared hit=337339 read=19338 dirtied=2273
  • I/O Timings: read=390.431
  • Worker 1: actual time=1554.883..3204.804 rows=61693 loops=1
  • Buffers: shared hit=334323 read=19184 dirtied=2189
  • I/O Timings: read=408.452
  • Worker 2: actual time=1555.349..3207.801 rows=55568 loops=1
  • Buffers: shared hit=330397 read=19083 dirtied=2203
  • I/O Timings: read=397.389
  • Worker 3: actual time=1555.367..3205.214 rows=59524 loops=1
  • Buffers: shared hit=330004 read=19151 dirtied=2192
  • I/O Timings: read=418.345
5. 1,233.863 3,157.392 ↑ 7.1 57,603 5

Parallel Hash Join (cost=170,209.43..360,139.76 rows=411,790 width=29) (actual time=1,547.625..3,157.392 rows=57,603 loops=5)

  • Output: v.channel_id, vg.game_id
  • Hash Cond: (vg.youtube_video_id = v.id)
  • Buffers: shared hit=1648081 read=96243 dirtied=11060
  • I/O Timings: read=2018.692
  • Worker 0: actual time=1547.584..3175.607 rows=64864 loops=1
  • Buffers: shared hit=334516 read=19338 dirtied=2273
  • I/O Timings: read=390.431
  • Worker 1: actual time=1547.619..3176.208 rows=61693 loops=1
  • Buffers: shared hit=331432 read=19184 dirtied=2189
  • I/O Timings: read=408.452
  • Worker 2: actual time=1547.575..3177.857 rows=55568 loops=1
  • Buffers: shared hit=326555 read=19083 dirtied=2203
  • I/O Timings: read=397.389
  • Worker 3: actual time=1547.618..3175.564 rows=59524 loops=1
  • Buffers: shared hit=326201 read=19151 dirtied=2192
  • I/O Timings: read=418.345
6. 377.991 377.991 ↑ 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.006..377.991 rows=3,447,729 loops=5)

  • Output: vg.youtube_video_id, vg.game_id, vg.matching_score
  • Buffers: shared hit=128203 read=1440
  • I/O Timings: read=467.381
  • Worker 0: actual time=0.006..365.083 rows=3728567 loops=1
  • Buffers: shared hit=27745 read=311
  • I/O Timings: read=86.484
  • Worker 1: actual time=0.007..386.434 rows=3568489 loops=1
  • Buffers: shared hit=26561 read=267
  • I/O Timings: read=105.754
  • Worker 2: actual time=0.006..387.567 rows=3417210 loops=1
  • Buffers: shared hit=25425 read=287
  • I/O Timings: read=94.165
  • Worker 3: actual time=0.005..388.099 rows=3425110 loops=1
  • Buffers: shared hit=25400 read=287
  • I/O Timings: read=100.285
7. 141.631 1,545.538 ↑ 1.3 322,520 5

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

  • Output: v.channel_id, v.id
  • Buckets: 2097152 Batches: 1 Memory Usage: 130144kB
  • Buffers: shared hit=1519822 read=94803 dirtied=11060
  • I/O Timings: read=1551.311
  • Worker 0: actual time=1547.503..1547.504 rows=325736 loops=1
  • Buffers: shared hit=306757 read=19027 dirtied=2273
  • I/O Timings: read=303.948
  • Worker 1: actual time=1547.503..1547.503 rows=323290 loops=1
  • Buffers: shared hit=304857 read=18917 dirtied=2189
  • I/O Timings: read=302.698
  • Worker 2: actual time=1547.497..1547.497 rows=319359 loops=1
  • Buffers: shared hit=301116 read=18796 dirtied=2203
  • I/O Timings: read=303.223
  • Worker 3: actual time=1547.500..1547.500 rows=319850 loops=1
  • Buffers: shared hit=300787 read=18864 dirtied=2192
  • I/O Timings: read=318.060
8. 33.479 1,403.907 ↑ 1.3 322,520 5

Parallel Append (cost=0.12..165,062.05 rows=411,790 width=37) (actual time=0.086..1,403.907 rows=322,520 loops=5)

  • Buffers: shared hit=1519822 read=94803 dirtied=11060
  • I/O Timings: read=1551.311
  • Worker 0: actual time=0.087..1403.852 rows=325736 loops=1
  • Buffers: shared hit=306757 read=19027 dirtied=2273
  • I/O Timings: read=303.948
  • Worker 1: actual time=0.113..1404.499 rows=323290 loops=1
  • Buffers: shared hit=304857 read=18917 dirtied=2189
  • I/O Timings: read=302.698
  • Worker 2: actual time=0.140..1399.498 rows=319359 loops=1
  • Buffers: shared hit=301116 read=18796 dirtied=2203
  • I/O Timings: read=303.223
  • Worker 3: actual time=0.040..1405.869 rows=319850 loops=1
  • Buffers: shared hit=300787 read=18864 dirtied=2192
  • I/O Timings: read=318.060
9. 1,370.418 1,370.418 ↑ 1.3 322,520 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.079..1,370.418 rows=322,520 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: 30091
  • Buffers: shared hit=1519821 read=94803 dirtied=11060
  • I/O Timings: read=1551.311
  • Worker 0: actual time=0.079..1370.581 rows=325736 loops=1
  • Buffers: shared hit=306757 read=19027 dirtied=2273
  • I/O Timings: read=303.948
  • Worker 1: actual time=0.103..1370.929 rows=323290 loops=1
  • Buffers: shared hit=304857 read=18917 dirtied=2189
  • I/O Timings: read=302.698
  • Worker 2: actual time=0.135..1365.101 rows=319359 loops=1
  • Buffers: shared hit=301116 read=18796 dirtied=2203
  • I/O Timings: read=303.223
  • Worker 3: actual time=0.039..1372.403 rows=319850 loops=1
  • Buffers: shared hit=300787 read=18864 dirtied=2192
  • I/O Timings: read=318.060
10. 0.010 0.010 ↓ 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.010..0.010 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
11. 3.388 8.739 ↑ 1.6 10,184 5

Parallel Hash (cost=20,287.03..20,287.03 rows=16,703 width=29) (actual time=8.739..8.739 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.195..7.195 rows=6798 loops=1
  • Buffers: shared hit=2795
  • Worker 1: actual time=7.179..7.179 rows=6902 loops=1
  • Buffers: shared hit=2863
  • Worker 2: actual time=7.690..7.690 rows=9413 loops=1
  • Buffers: shared hit=3814
  • Worker 3: actual time=7.692..7.692 rows=9380 loops=1
  • Buffers: shared hit=3775
12. 5.351 5.351 ↑ 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.351 rows=10,184 loops=5)

  • Output: g.title, g.id
  • Buffers: shared hit=20120
  • Worker 0: actual time=0.003..4.282 rows=6798 loops=1
  • Buffers: shared hit=2795
  • Worker 1: actual time=0.003..4.285 rows=6902 loops=1
  • Buffers: shared hit=2863
  • Worker 2: actual time=0.002..4.701 rows=9413 loops=1
  • Buffers: shared hit=3814
  • Worker 3: actual time=0.003..4.761 rows=9380 loops=1
  • Buffers: shared hit=3775
Planning time : 2.911 ms