explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KzWt

Settings
# exclusive inclusive rows x rows loops node
1. 0.059 180.957 ↑ 1.0 10 1

Limit (cost=21,754.60..21,754.62 rows=10 width=12) (actual time=180.899..180.957 rows=10 loops=1)

  • Buffers: shared hit=957 read=2731
2. 22.003 180.898 ↑ 20,000.0 10 1

Sort (cost=21,754.60..22,254.60 rows=200,000 width=12) (actual time=180.898..180.898 rows=10 loops=1)

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

GroupAggregate (cost=7,424.61..17,432.67 rows=200,000 width=12) (actual time=34.423..158.895 rows=200,000 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=954 read=2731
4. 22.430 108.319 ↑ 1.0 200,000 1

Merge Left Join (cost=7,424.61..14,432.67 rows=200,000 width=8) (actual time=34.404..108.319 rows=200,000 loops=1)

  • Merge Cond: (video.video_id = likes.video_id)
  • Buffers: shared hit=954 read=2731
5. 51.525 51.525 ↑ 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.033..51.525 rows=200,000 loops=1)

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

Sort (cost=7,424.19..7,424.20 rows=4 width=4) (actual time=34.364..34.364 rows=0 loops=1)

  • Sort Key: likes.video_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=947 read=887
7. 7.802 34.405 ↓ 0.0 0 1

Gather (cost=4,385.02..7,424.15 rows=4 width=4) (actual time=34.351..34.405 rows=0 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=947 read=887
8. 0.127 26.603 ↓ 0.0 0 2

Hash Join (cost=3,385.03..6,423.75 rows=2 width=4) (actual time=26.603..26.603 rows=0 loops=2)

  • Hash Cond: (likes.user_id = friend.friend_id)
  • Buffers: shared hit=947 read=887
9. 0.261 0.261 ↑ 117,647.0 1 2

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

  • Buffers: shared read=2
10. 0.003 26.215 ↓ 0.0 0 2

Hash (cost=3,385.00..3,385.00 rows=2 width=4) (actual time=26.213..26.215 rows=0 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=885 read=885
11. 26.212 26.212 ↓ 0.0 0 2

Seq Scan on friend (cost=0.00..3,385.00 rows=2 width=4) (actual time=26.212..26.212 rows=0 loops=2)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 200000
  • Buffers: shared hit=885 read=885