explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pbQ9

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

Limit (cost=11,753.74..11,753.76 rows=10 width=12) (actual time=130.649..130.654 rows=10 loops=1)

  • Buffers: shared hit=17 read=2202
2. 13.574 130.648 ↑ 10,000.0 10 1

Sort (cost=11,753.74..12,003.74 rows=100,000 width=12) (actual time=130.648..130.648 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=17 read=2202
3. 25.447 117.074 ↑ 1.0 100,000 1

GroupAggregate (cost=4,584.67..9,592.77 rows=100,000 width=12) (actual time=51.335..117.074 rows=100,000 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=14 read=2202
4. 13.017 91.627 ↑ 1.0 100,000 1

Merge Left Join (cost=4,584.67..8,092.77 rows=100,000 width=8) (actual time=51.325..91.627 rows=100,000 loops=1)

  • Merge Cond: (video.video_id = friend_likes.video_id)
  • Buffers: shared hit=14 read=2202
5. 27.339 27.339 ↑ 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.047..27.339 rows=100,000 loops=1)

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

Sort (cost=4,584.38..4,584.53 rows=61 width=4) (actual time=51.270..51.271 rows=3 loops=1)

  • Sort Key: friend_likes.video_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=5 read=1282
7. 0.001 51.258 ↑ 20.3 3 1

Subquery Scan on friend_likes (cost=4,581.81..4,582.57 rows=61 width=4) (actual time=51.256..51.258 rows=3 loops=1)

  • Buffers: shared hit=5 read=1282
8. 0.023 51.257 ↑ 20.3 3 1

Sort (cost=4,581.81..4,581.96 rows=61 width=24) (actual time=51.256..51.257 rows=3 loops=1)

  • Sort Key: likes.user_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=5 read=1282
9. 17.434 51.234 ↑ 20.3 3 1

Hash Join (cost=780.98..4,580.00 rows=61 width=24) (actual time=26.456..51.234 rows=3 loops=1)

  • Hash Cond: (likes.user_id = friend.friend_id)
  • Buffers: shared hit=5 read=1282
10. 31.766 31.766 ↑ 1.0 200,000 1

Seq Scan on likes (cost=0.00..3,274.00 rows=200,000 width=8) (actual time=0.266..31.766 rows=200,000 loops=1)

  • Buffers: shared read=1274
11. 0.004 2.034 ↑ 10.7 3 1

Hash (cost=780.58..780.58 rows=32 width=4) (actual time=2.034..2.034 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=5 read=8
12. 0.007 2.030 ↑ 10.7 3 1

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

  • Buffers: shared hit=5 read=8
13. 0.033 2.023 ↑ 10.7 3 1

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

  • Sort Key: friend.friend_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=5 read=8
14. 0.020 1.990 ↑ 10.7 3 1

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

  • Buffers: shared hit=5 read=8
15. 0.155 0.795 ↑ 4.0 1 1

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

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

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

  • Index Cond: (user_id = 1)
  • Buffers: shared read=3
17. 0.627 1.175 ↑ 2.7 3 1

Bitmap Heap Scan on friend (cost=158.91..189.73 rows=8 width=8) (actual time=0.556..1.175 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
18. 0.002 0.548 ↓ 0.0 0 1

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

  • Buffers: shared hit=4 read=2
19. 0.542 0.542 ↑ 2.0 2 1

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

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

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

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