explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ec5L

Settings
# exclusive inclusive rows x rows loops node
1. 0.062 166.319 ↑ 1.0 10 1

Limit (cost=11,522.02..11,522.04 rows=10 width=12) (actual time=166.256..166.319 rows=10 loops=1)

  • Buffers: shared hit=59 read=2198
2. 10.513 166.257 ↑ 10,000.0 10 1

Sort (cost=11,522.02..11,772.02 rows=100,000 width=12) (actual time=166.256..166.257 rows=10 loops=1)

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

GroupAggregate (cost=4,353.79..9,361.05 rows=100,000 width=12) (actual time=92.580..155.744 rows=100,000 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=56 read=2198
4. 12.734 130.926 ↑ 1.0 100,000 1

Merge Left Join (cost=4,353.79..7,861.05 rows=100,000 width=8) (actual time=92.566..130.926 rows=100,000 loops=1)

  • Merge Cond: (video.video_id = likes.video_id)
  • Buffers: shared hit=56 read=2198
5. 25.671 25.671 ↑ 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.036..25.671 rows=100,000 loops=1)

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

Sort (cost=4,353.50..4,353.51 rows=5 width=4) (actual time=92.521..92.521 rows=1 loops=1)

  • Sort Key: likes.video_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=47 read=1278
7. 5.435 92.555 ↑ 5.0 1 1

Gather (cost=1,020.09..4,353.44 rows=5 width=4) (actual time=40.604..92.555 rows=1 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=47 read=1278
8. 12.117 87.120 ↓ 0.0 0 2

Hash Join (cost=20.09..3,352.94 rows=3 width=4) (actual time=61.178..87.120 rows=0 loops=2)

  • Hash Cond: (likes.user_id = friend.friend_id)
  • Buffers: shared hit=47 read=1278
9. 74.636 74.636 ↑ 1.2 100,000 2

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

  • Buffers: shared read=1274
10. 0.013 0.367 ↑ 4.0 1 2

Hash (cost=20.04..20.04 rows=4 width=4) (actual time=0.367..0.367 rows=1 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=5 read=4
11. 0.072 0.354 ↑ 4.0 1 2

Bitmap Heap Scan on friend (cost=4.46..20.04 rows=4 width=4) (actual time=0.352..0.354 rows=1 loops=2)

  • Recheck Cond: (user_id = 1)
  • Heap Blocks: exact=1
  • Buffers: shared hit=5 read=4
12. 0.282 0.282 ↑ 4.0 1 2

Bitmap Index Scan on friend_index (cost=0.00..4.46 rows=4 width=0) (actual time=0.282..0.282 rows=1 loops=2)

  • Index Cond: (user_id = 1)
  • Buffers: shared hit=4 read=3