explain.depesz.com

PostgreSQL's explain analyze made readable

Result: umaC

Settings
# exclusive inclusive rows x rows loops node
1. 0.138 159.442 ↑ 1.0 10 1

Limit (cost=18,381.77..18,381.80 rows=10 width=12) (actual time=159.303..159.442 rows=10 loops=1)

  • Buffers: shared hit=56 read=1849
2. 26.975 159.304 ↑ 20,000.0 10 1

Sort (cost=18,381.77..18,881.77 rows=200,000 width=12) (actual time=159.302..159.304 rows=10 loops=1)

  • Sort Key: (COALESCE(count(likes.video_id), '0'::bigint)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=56 read=1849
3. 50.892 132.329 ↑ 1.0 200,000 1

GroupAggregate (cost=4,051.79..14,059.85 rows=200,000 width=12) (actual time=8.766..132.329 rows=200,000 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=53 read=1849
4. 21.965 81.437 ↑ 1.0 200,000 1

Merge Left Join (cost=4,051.79..11,059.85 rows=200,000 width=8) (actual time=8.751..81.437 rows=200,000 loops=1)

  • Merge Cond: (video.video_id = likes.video_id)
  • Buffers: shared hit=53 read=1849
5. 50.761 50.761 ↑ 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.032..50.761 rows=200,000 loops=1)

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

Sort (cost=4,051.37..4,051.38 rows=4 width=4) (actual time=8.711..8.711 rows=0 loops=1)

  • Sort Key: likes.video_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=46 read=5
7. 8.181 8.828 ↓ 0.0 0 1

Gather (cost=1,012.20..4,051.33 rows=4 width=4) (actual time=8.698..8.828 rows=0 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=46 read=5
8. 0.069 0.647 ↓ 0.0 0 2

Hash Join (cost=12.20..3,050.93 rows=2 width=4) (actual time=0.646..0.647 rows=0 loops=2)

  • Hash Cond: (likes.user_id = friend.friend_id)
  • Buffers: shared hit=46 read=5
9. 0.278 0.278 ↑ 117,647.0 1 2

Parallel Seq Scan on likes (cost=0.00..2,450.47 rows=117,647 width=8) (actual time=0.278..0.278 rows=1 loops=2)

  • Buffers: shared read=2
10. 0.000 0.300 ↓ 0.0 0 2

Hash (cost=12.18..12.18 rows=2 width=4) (actual time=0.300..0.300 rows=0 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=4 read=3
11. 0.005 0.300 ↓ 0.0 0 2

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

  • Recheck Cond: (user_id = 1)
  • Buffers: shared hit=4 read=3
12. 0.295 0.295 ↓ 0.0 0 2

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

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