explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DsFJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 146.935 ↑ 1.0 10 1

Limit (cost=14,589.72..14,589.75 rows=10 width=12) (actual time=146.930..146.935 rows=10 loops=1)

  • Buffers: shared hit=10 read=1847
2. 21.282 146.929 ↑ 20,000.0 10 1

Sort (cost=14,589.72..15,089.72 rows=200,000 width=12) (actual time=146.929..146.929 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=10 read=1847
3. 52.533 125.647 ↑ 1.0 200,000 1

GroupAggregate (cost=259.57..10,267.79 rows=200,000 width=12) (actual time=0.687..125.647 rows=200,000 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=7 read=1847
4. 21.556 73.114 ↑ 1.0 200,000 1

Merge Left Join (cost=259.57..7,267.79 rows=200,000 width=8) (actual time=0.669..73.114 rows=200,000 loops=1)

  • Merge Cond: (video.video_id = friend_likes.video_id)
  • Buffers: shared hit=7 read=1847
5. 50.932 50.932 ↑ 1.0 200,000 1

Index Only Scan using video_pkey on video (cost=0.42..6,508.42 rows=200,000 width=4) (actual time=0.038..50.932 rows=200,000 loops=1)

  • Heap Fetches: 200000
  • Buffers: shared hit=7 read=1844
6. 0.011 0.626 ↓ 0.0 0 1

Sort (cost=259.15..259.19 rows=15 width=4) (actual time=0.626..0.626 rows=0 loops=1)

  • Sort Key: friend_likes.video_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared read=3
7. 0.000 0.615 ↓ 0.0 0 1

Subquery Scan on friend_likes (cost=165.08..258.86 rows=15 width=4) (actual time=0.615..0.615 rows=0 loops=1)

  • Buffers: shared read=3
8. 0.001 0.615 ↓ 0.0 0 1

Nested Loop (cost=165.08..258.71 rows=15 width=24) (actual time=0.615..0.615 rows=0 loops=1)

  • Buffers: shared read=3
9. 0.000 0.614 ↓ 0.0 0 1

Unique (cost=160.64..160.68 rows=8 width=4) (actual time=0.614..0.614 rows=0 loops=1)

  • Buffers: shared read=3
10. 0.011 0.614 ↓ 0.0 0 1

Sort (cost=160.64..160.66 rows=8 width=4) (actual time=0.614..0.614 rows=0 loops=1)

  • Sort Key: friend.friend_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared read=3
11. 0.000 0.603 ↓ 0.0 0 1

Nested Loop (cost=63.31..160.52 rows=8 width=4) (actual time=0.603..0.603 rows=0 loops=1)

  • Buffers: shared read=3
12. 0.004 0.603 ↓ 0.0 0 1

Bitmap Heap Scan on friend friend_1 (cost=4.44..12.18 rows=2 width=4) (actual time=0.602..0.603 rows=0 loops=1)

  • Recheck Cond: (user_id = 1)
  • Buffers: shared read=3
13. 0.599 0.599 ↓ 0.0 0 1

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

  • Index Cond: (user_id = 1)
  • Buffers: shared read=3
14. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on friend (cost=58.87..74.13 rows=4 width=8) (never executed)

  • 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))
15. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=58.87..58.87 rows=4 width=0) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on friend_index (cost=0.00..4.43 rows=2 width=0) (never executed)

  • Index Cond: (user_id = friend_1.friend_id)
17. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on friend_index (cost=0.00..4.43 rows=2 width=0) (never executed)

  • Index Cond: (user_id = 1)
18. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on likes (cost=4.44..12.22 rows=2 width=8) (never executed)

  • Recheck Cond: (user_id = friend.friend_id)
19. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on user_index (cost=0.00..4.43 rows=2 width=0) (never executed)

  • Index Cond: (user_id = friend.friend_id)