explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DaSd

Settings
# exclusive inclusive rows x rows loops node
1. 76.799 64,301.188 ↑ 9.1 37,141 1

GroupAggregate (cost=1,476,501.99..1,492,981.44 rows=338,181 width=57) (actual time=64,183.709..64,301.188 rows=37,141 loops=1)

  • Output: v.channel_id, array_agg(g.title)
  • Group Key: v.channel_id
  • Buffers: shared hit=1926170 read=728484 dirtied=88189 written=10847
  • I/O Timings: read=297033.232 write=137.850
2. 906.744 64,224.389 ↑ 5.7 288,014 1

Sort (cost=1,476,501.99..1,480,586.05 rows=1,633,624 width=50) (actual time=64,183.692..64,224.389 rows=288,014 loops=1)

  • Output: v.channel_id, g.title
  • Sort Key: v.channel_id
  • Sort Method: quicksort Memory: 38416kB
  • Buffers: shared hit=1926170 read=728484 dirtied=88189 written=10847
  • I/O Timings: read=297033.232 write=137.850
3. 25.385 63,317.645 ↑ 5.7 288,014 1

Gather (cost=953,558.47..1,307,914.90 rows=1,633,624 width=50) (actual time=61,630.284..63,317.645 rows=288,014 loops=1)

  • Output: v.channel_id, g.title
  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=1926170 read=728484 dirtied=88189 written=10847
  • I/O Timings: read=297033.232 write=137.850
4. 22.448 63,292.260 ↑ 7.1 57,603 5

Parallel Hash Join (cost=952,558.47..1,143,552.50 rows=408,406 width=50) (actual time=61,623.987..63,292.260 rows=57,603 loops=5)

  • Output: v.channel_id, g.title
  • Inner Unique: true
  • Hash Cond: (vg.game_id = g.id)
  • Buffers: shared hit=1926170 read=728484 dirtied=88189 written=10847
  • I/O Timings: read=297033.232 write=137.850
  • Worker 0: actual time=61622.464..63309.982 rows=58170 loops=1
  • Buffers: shared hit=384989 read=145233 dirtied=17374 written=2226
  • I/O Timings: read=59478.801 write=28.837
  • Worker 1: actual time=61622.461..63310.018 rows=55617 loops=1
  • Buffers: shared hit=384306 read=144460 dirtied=17681 written=2147
  • I/O Timings: read=59337.701 write=27.740
  • Worker 2: actual time=61622.771..63309.139 rows=62570 loops=1
  • Buffers: shared hit=385742 read=145957 dirtied=17711 written=2142
  • I/O Timings: read=59339.687 write=28.499
  • Worker 3: actual time=61622.635..63310.553 rows=63429 loops=1
  • Buffers: shared hit=388420 read=146082 dirtied=17627 written=2181
  • I/O Timings: read=59335.511 write=29.961
5. 1,265.685 63,239.238 ↑ 7.1 57,603 5

Parallel Hash Join (cost=932,062.66..1,121,984.53 rows=408,406 width=29) (actual time=61,593.239..63,239.238 rows=57,603 loops=5)

  • Output: v.channel_id, vg.game_id
  • Hash Cond: (vg.youtube_video_id = v.id)
  • Buffers: shared hit=1926056 read=708366 dirtied=88189 written=10847
  • I/O Timings: read=296954.744 write=137.850
  • Worker 0: actual time=61593.180..63257.443 rows=58170 loops=1
  • Buffers: shared hit=384961 read=142105 dirtied=17374 written=2226
  • I/O Timings: read=59464.747 write=28.837
  • Worker 1: actual time=61593.208..63257.142 rows=55617 loops=1
  • Buffers: shared hit=384278 read=141326 dirtied=17681 written=2147
  • I/O Timings: read=59323.568 write=27.740
  • Worker 2: actual time=61593.357..63256.851 rows=62570 loops=1
  • Buffers: shared hit=385713 read=141775 dirtied=17711 written=2142
  • I/O Timings: read=59323.683 write=28.499
  • Worker 3: actual time=61593.223..63258.482 rows=63429 loops=1
  • Buffers: shared hit=388392 read=141811 dirtied=17627 written=2181
  • I/O Timings: read=59320.191 write=29.961
6. 383.017 383.017 ↑ 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..383.017 rows=3,447,729 loops=5)

  • Output: vg.youtube_video_id, vg.game_id, vg.matching_score
  • Buffers: shared hit=127849 read=1794 written=4
  • I/O Timings: read=431.605 write=0.061
  • Worker 0: actual time=0.004..392.804 rows=3350275 loops=1
  • Buffers: shared hit=24824 read=377
  • I/O Timings: read=82.674
  • Worker 1: actual time=0.006..391.565 rows=3343459 loops=1
  • Buffers: shared hit=24826 read=358 written=1
  • I/O Timings: read=85.168 write=0.015
  • Worker 2: actual time=0.005..375.946 rows=3614148 loops=1
  • Buffers: shared hit=26785 read=355 written=2
  • I/O Timings: read=84.983 write=0.031
  • Worker 3: actual time=0.004..377.811 rows=3677109 loops=1
  • Buffers: shared hit=27278 read=350 written=1
  • I/O Timings: read=83.525 write=0.014
7. 215.993 61,590.536 ↑ 1.3 322,511 5

Parallel Hash (cost=926,957.59..926,957.59 rows=408,406 width=37) (actual time=61,590.536..61,590.536 rows=322,511 loops=5)

  • Output: v.channel_id, v.id
  • Buckets: 2097152 Batches: 1 Memory Usage: 130112kB
  • Buffers: shared hit=1798151 read=706572 dirtied=88189 written=10843
  • I/O Timings: read=296523.139 write=137.790
  • Worker 0: actual time=61593.022..61593.022 rows=323425 loops=1
  • Buffers: shared hit=360123 read=141728 dirtied=17374 written=2226
  • I/O Timings: read=59382.073 write=28.837
  • Worker 1: actual time=61593.035..61593.035 rows=321876 loops=1
  • Buffers: shared hit=359438 read=140968 dirtied=17681 written=2146
  • I/O Timings: read=59238.400 write=27.725
  • Worker 2: actual time=61593.018..61593.018 rows=321761 loops=1
  • Buffers: shared hit=358914 read=141420 dirtied=17711 written=2140
  • I/O Timings: read=59238.700 write=28.467
  • Worker 3: actual time=61593.038..61593.038 rows=323530 loops=1
  • Buffers: shared hit=361100 read=141461 dirtied=17627 written=2180
  • I/O Timings: read=59236.666 write=29.947
8. 38.532 61,374.543 ↑ 1.3 322,511 5

Parallel Append (cost=0.12..926,957.59 rows=408,406 width=37) (actual time=0.981..61,374.543 rows=322,511 loops=5)

  • Buffers: shared hit=1798151 read=706572 dirtied=88189 written=10843
  • I/O Timings: read=296523.139 write=137.790
  • Worker 0: actual time=0.524..61374.053 rows=323425 loops=1
  • Buffers: shared hit=360123 read=141728 dirtied=17374 written=2226
  • I/O Timings: read=59382.073 write=28.837
  • Worker 1: actual time=1.484..61373.560 rows=321876 loops=1
  • Buffers: shared hit=359438 read=140968 dirtied=17681 written=2146
  • I/O Timings: read=59238.400 write=27.725
  • Worker 2: actual time=0.938..61372.471 rows=321761 loops=1
  • Buffers: shared hit=358914 read=141420 dirtied=17711 written=2140
  • I/O Timings: read=59238.700 write=28.467
  • Worker 3: actual time=1.464..61372.832 rows=323530 loops=1
  • Buffers: shared hit=361100 read=141461 dirtied=17627 written=2180
  • I/O Timings: read=59236.666 write=29.947
9. 61,336.005 61,336.005 ↑ 1.3 322,511 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..924,914.32 rows=408,406 width=37) (actual time=0.974..61,336.005 rows=322,511 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: 907467
  • Buffers: shared hit=1798150 read=706572 dirtied=88189 written=10843
  • I/O Timings: read=296523.139 write=137.790
  • Worker 0: actual time=0.523..61335.957 rows=323425 loops=1
  • Buffers: shared hit=360123 read=141728 dirtied=17374 written=2226
  • I/O Timings: read=59382.073 write=28.837
  • Worker 1: actual time=1.473..61335.041 rows=321876 loops=1
  • Buffers: shared hit=359438 read=140968 dirtied=17681 written=2146
  • I/O Timings: read=59238.400 write=27.725
  • Worker 2: actual time=0.934..61334.116 rows=321761 loops=1
  • Buffers: shared hit=358914 read=141420 dirtied=17711 written=2140
  • I/O Timings: read=59238.700 write=28.467
  • Worker 3: actual time=1.454..61334.468 rows=323530 loops=1
  • Buffers: shared hit=361100 read=141461 dirtied=17627 written=2180
  • I/O Timings: read=59236.666 write=29.947
10. 0.006 0.006 ↓ 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.006..0.006 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.964 30.574 ↑ 1.6 10,184 5

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

  • Output: g.title, g.id
  • Buckets: 65536 Batches: 1 Memory Usage: 3840kB
  • Buffers: shared hit=2 read=20118
  • I/O Timings: read=78.487
  • Worker 0: actual time=29.180..29.180 rows=7705 loops=1
  • Buffers: shared read=3128
  • I/O Timings: read=14.054
  • Worker 1: actual time=29.171..29.171 rows=7784 loops=1
  • Buffers: shared read=3134
  • I/O Timings: read=14.133
  • Worker 2: actual time=29.335..29.335 rows=10439 loops=1
  • Buffers: shared hit=1 read=4182
  • I/O Timings: read=16.004
  • Worker 3: actual time=29.336..29.336 rows=10641 loops=1
  • Buffers: shared read=4271
  • I/O Timings: read=15.320
12. 26.610 26.610 ↑ 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.014..26.610 rows=10,184 loops=5)

  • Output: g.title, g.id
  • Buffers: shared hit=2 read=20118
  • I/O Timings: read=78.487
  • Worker 0: actual time=0.016..25.319 rows=7705 loops=1
  • Buffers: shared read=3128
  • I/O Timings: read=14.054
  • Worker 1: actual time=0.016..25.348 rows=7784 loops=1
  • Buffers: shared read=3134
  • I/O Timings: read=14.133
  • Worker 2: actual time=0.012..25.714 rows=10439 loops=1
  • Buffers: shared hit=1 read=4182
  • I/O Timings: read=16.004
  • Worker 3: actual time=0.012..24.948 rows=10641 loops=1
  • Buffers: shared read=4271
  • I/O Timings: read=15.320
Planning time : 2.452 ms