explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ydLx

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 80.773 ↑ 1.0 10 1

Limit (cost=7,238.04..7,238.06 rows=10 width=12) (actual time=80.769..80.773 rows=10 loops=1)

  • Buffers: shared hit=14 read=926
2. 13.760 80.768 ↑ 10,000.0 10 1

Sort (cost=7,238.04..7,488.04 rows=100,000 width=12) (actual time=80.768..80.768 rows=10 loops=1)

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

GroupAggregate (cost=69.82..5,077.08 rows=100,000 width=12) (actual time=1.688..67.008 rows=100,000 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=11 read=926
4. 12.971 41.495 ↑ 1.0 100,000 1

Merge Left Join (cost=69.82..3,577.08 rows=100,000 width=8) (actual time=1.680..41.495 rows=100,000 loops=1)

  • Merge Cond: (video.video_id = likes.video_id)
  • Buffers: shared hit=11 read=926
5. 26.902 26.902 ↑ 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.051..26.902 rows=100,000 loops=1)

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

Sort (cost=69.52..69.54 rows=5 width=4) (actual time=1.621..1.622 rows=1 loops=1)

  • Sort Key: likes.video_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2 read=6
7. 0.012 1.605 ↑ 5.0 1 1

Nested Loop (cost=4.86..69.47 rows=5 width=4) (actual time=1.603..1.605 rows=1 loops=1)

  • Buffers: shared hit=2 read=6
8. 1.147 1.147 ↑ 4.0 1 1

Index Scan using friend_index on friend (cost=0.42..20.49 rows=4 width=4) (actual time=1.146..1.147 rows=1 loops=1)

  • Index Cond: (user_id = 1)
  • Buffers: shared read=4
9. 0.167 0.446 ↑ 2.0 1 1

Bitmap Heap Scan on likes (cost=4.44..12.22 rows=2 width=8) (actual time=0.446..0.446 rows=1 loops=1)

  • Recheck Cond: (user_id = friend.friend_id)
  • Heap Blocks: exact=1
  • Buffers: shared hit=2 read=2
10. 0.279 0.279 ↑ 2.0 1 1

Bitmap Index Scan on user_index (cost=0.00..4.43 rows=2 width=0) (actual time=0.279..0.279 rows=1 loops=1)

  • Index Cond: (user_id = friend.friend_id)
  • Buffers: shared hit=2 read=1