explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ODYW

Settings
# exclusive inclusive rows x rows loops node
1. 0.100 270.062 ↑ 1.0 10 1

Limit (cost=18,274.59..18,274.61 rows=10 width=12) (actual time=269.961..270.062 rows=10 loops=1)

  • Buffers: shared hit=74 read=4849
2. 10.902 269.962 ↑ 10,000.0 10 1

Sort (cost=18,274.59..18,524.59 rows=100,000 width=12) (actual time=269.960..269.962 rows=10 loops=1)

  • Sort Key: (COALESCE(count(likes.video_id), '0'::bigint)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=74 read=4849
3. 24.953 259.060 ↑ 1.0 100,000 1

GroupAggregate (cost=11,106.37..16,113.62 rows=100,000 width=12) (actual time=195.441..259.060 rows=100,000 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=71 read=4849
4. 12.919 234.107 ↑ 1.0 100,000 1

Merge Left Join (cost=11,106.37..14,613.62 rows=100,000 width=8) (actual time=195.422..234.107 rows=100,000 loops=1)

  • Merge Cond: (video.video_id = likes.video_id)
  • Buffers: shared hit=71 read=4849
5. 25.817 25.817 ↑ 1.0 100,000 1

Index Only Scan using video_pkey on video (cost=0.29..3,257.48 rows=100,000 width=4) (actual time=0.042..25.817 rows=100,000 loops=1)

  • Heap Fetches: 100000
  • Buffers: shared hit=9 read=920
6. 0.000 195.371 ↑ 5.0 1 1

Sort (cost=11,106.07..11,106.09 rows=5 width=4) (actual time=195.371..195.371 rows=1 loops=1)

  • Sort Key: likes.video_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=62 read=3929
7. 4.670 195.443 ↑ 5.0 1 1

Gather (cost=8,066.79..11,106.02 rows=5 width=4) (actual time=160.579..195.443 rows=1 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=62 read=3929
8. 10.284 190.773 ↓ 0.0 0 2

Parallel Hash Join (cost=7,066.79..10,105.52 rows=3 width=4) (actual time=173.419..190.773 rows=0 loops=2)

  • Hash Cond: (likes.user_id = friend.friend_id)
  • Buffers: shared hit=62 read=3929
9. 61.561 61.561 ↑ 1.2 100,000 2

Parallel Seq Scan on likes (cost=0.00..2,450.47 rows=117,647 width=8) (actual time=0.224..61.561 rows=100,000 loops=2)

  • Buffers: shared read=1274
10. 0.064 118.928 ↓ 0.0 0 2

Parallel Hash (cost=7,066.76..7,066.76 rows=2 width=4) (actual time=118.928..118.928 rows=0 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
  • Buffers: shared read=2655
11. 118.864 118.864 ↓ 0.0 0 2

Parallel Seq Scan on friend (cost=0.00..7,066.76 rows=2 width=4) (actual time=68.683..118.864 rows=0 loops=2)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 300000
  • Buffers: shared read=2655