explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ekQX

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 83.467 ↑ 1.0 10 1

Limit (cost=8,347.54..8,347.57 rows=10 width=12) (actual time=83.463..83.467 rows=10 loops=1)

  • Buffers: shared hit=20 read=937
2. 13.301 83.463 ↑ 10,000.0 10 1

Sort (cost=8,347.54..8,597.54 rows=100,000 width=12) (actual time=83.463..83.463 rows=10 loops=1)

  • Sort Key: (COALESCE(count(friend_likes.video_id), '0'::bigint)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=20 read=937
3. 25.270 70.162 ↑ 1.0 100,000 1

GroupAggregate (cost=1,178.48..6,186.58 rows=100,000 width=12) (actual time=3.994..70.162 rows=100,000 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=17 read=937
4. 13.393 44.892 ↑ 1.0 100,000 1

Merge Left Join (cost=1,178.48..4,686.58 rows=100,000 width=8) (actual time=3.974..44.892 rows=100,000 loops=1)

  • Merge Cond: (video.video_id = friend_likes.video_id)
  • Buffers: shared hit=17 read=937
5. 27.587 27.587 ↑ 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.050..27.587 rows=100,000 loops=1)

  • Heap Fetches: 100000
  • Buffers: shared hit=9 read=920
6. 0.019 3.912 ↑ 20.3 3 1

Sort (cost=1,178.18..1,178.34 rows=61 width=4) (actual time=3.910..3.912 rows=3 loops=1)

  • Sort Key: friend_likes.video_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=8 read=17
7. 0.006 3.893 ↑ 20.3 3 1

Subquery Scan on friend_likes (cost=780.52..1,176.38 rows=61 width=4) (actual time=2.596..3.893 rows=3 loops=1)

  • Buffers: shared hit=8 read=17
8. 0.013 3.887 ↑ 20.3 3 1

Nested Loop (cost=780.52..1,175.77 rows=61 width=24) (actual time=2.594..3.887 rows=3 loops=1)

  • Buffers: shared hit=8 read=17
9. 0.005 1.846 ↑ 10.7 3 1

Unique (cost=780.10..780.26 rows=32 width=4) (actual time=1.840..1.846 rows=3 loops=1)

  • Buffers: shared hit=5 read=8
10. 0.036 1.841 ↑ 10.7 3 1

Sort (cost=780.10..780.18 rows=32 width=4) (actual time=1.839..1.841 rows=3 loops=1)

  • Sort Key: friend.friend_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=5 read=8
11. 0.013 1.805 ↑ 10.7 3 1

Nested Loop (cost=163.37..779.30 rows=32 width=4) (actual time=1.354..1.805 rows=3 loops=1)

  • Buffers: shared hit=5 read=8
12. 0.284 0.903 ↑ 4.0 1 1

Bitmap Heap Scan on friend friend_1 (cost=4.46..20.04 rows=4 width=4) (actual time=0.903..0.903 rows=1 loops=1)

  • Recheck Cond: (user_id = 1)
  • Heap Blocks: exact=1
  • Buffers: shared read=4
13. 0.619 0.619 ↑ 4.0 1 1

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

  • Index Cond: (user_id = 1)
  • Buffers: shared read=3
14. 0.456 0.889 ↑ 2.7 3 1

Bitmap Heap Scan on friend (cost=158.91..189.73 rows=8 width=8) (actual time=0.439..0.889 rows=3 loops=1)

  • Recheck Cond: ((user_id = friend_1.friend_id) OR (user_id = 1))
  • Filter: (((user_id = friend_1.friend_id) AND ((friend_id = 1) IS NOT TRUE)) OR (user_id = 1))
  • Heap Blocks: exact=3
  • Buffers: shared hit=5 read=4
15. 0.007 0.433 ↓ 0.0 0 1

BitmapOr (cost=158.91..158.91 rows=8 width=0) (actual time=0.433..0.433 rows=0 loops=1)

  • Buffers: shared hit=4 read=2
16. 0.423 0.423 ↑ 2.0 2 1

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

  • Index Cond: (user_id = friend_1.friend_id)
  • Buffers: shared hit=1 read=2
17. 0.003 0.003 ↑ 4.0 1 1

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

  • Index Cond: (user_id = 1)
  • Buffers: shared hit=3
18. 2.028 2.028 ↑ 2.0 1 3

Index Scan using like_index on likes (cost=0.42..12.33 rows=2 width=8) (actual time=0.673..0.676 rows=1 loops=3)

  • Index Cond: (user_id = friend.friend_id)
  • Buffers: shared hit=3 read=9