explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pvnq

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 417.133 ↑ 1.0 10 1

Limit (cost=141,821.25..141,821.28 rows=10 width=12) (actual time=417.130..417.133 rows=10 loops=1)

  • Buffers: shared hit=40193
2. 1.158 417.130 ↑ 140.4 10 1

Sort (cost=141,821.25..141,824.76 rows=1,404 width=12) (actual time=417.130..417.130 rows=10 loops=1)

  • Sort Key: ((SubPlan 1)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=40193
3. 2.523 415.972 ↓ 2.8 4,000 1

Group (cost=0.28..141,790.91 rows=1,404 width=12) (actual time=0.321..415.972 rows=4,000 loops=1)

  • Group Key: v.video_id
  • Buffers: shared hit=40193
4. 1.449 1.449 ↓ 2.8 4,000 1

Index Only Scan using video_pkey on video v (cost=0.28..177.34 rows=1,404 width=4) (actual time=0.016..1.449 rows=4,000 loops=1)

  • Heap Fetches: 4000
  • Buffers: shared hit=38
5.          

SubPlan (forGroup)

6. 12.000 412.000 ↑ 1.0 1 4,000

Aggregate (cost=100.85..100.86 rows=1 width=8) (actual time=0.103..0.103 rows=1 loops=4,000)

  • Buffers: shared hit=40155
7. 6.564 400.000 ↓ 0.0 0 4,000

Hash Join (cost=15.25..100.85 rows=1 width=4) (actual time=0.100..0.100 rows=0 loops=4,000)

  • Hash Cond: (f.friend_id = l.user_id)
  • Buffers: shared hit=40155
8. 377.436 377.436 ↑ 2.1 12 1,329

Seq Scan on friend f (cost=0.00..85.50 rows=25 width=4) (actual time=0.112..0.284 rows=12 loops=1,329)

  • Filter: (user_id = $1)
  • Rows Removed by Filter: 4988
  • Buffers: shared hit=30567
9. 8.000 16.000 ↓ 0.0 0 4,000

Hash (cost=15.15..15.15 rows=8 width=8) (actual time=0.004..0.004 rows=0 loops=4,000)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=9588
10. 4.000 8.000 ↓ 0.0 0 4,000

Bitmap Heap Scan on likes l (cost=4.34..15.15 rows=8 width=8) (actual time=0.002..0.002 rows=0 loops=4,000)

  • Recheck Cond: (video_id = v.video_id)
  • Heap Blocks: exact=1576
  • Buffers: shared hit=9588
11. 4.000 4.000 ↓ 0.0 0 4,000

Bitmap Index Scan on likes_video_id (cost=0.00..4.34 rows=8 width=0) (actual time=0.001..0.001 rows=0 loops=4,000)

  • Index Cond: (video_id = v.video_id)
  • Buffers: shared hit=8012