explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pWwx

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 3,414.233 ↑ 1.0 10 1

Limit (cost=351,050.84..351,050.87 rows=10 width=12) (actual time=3,414.228..3,414.233 rows=10 loops=1)

  • Buffers: shared hit=101637
2. 1.526 3,414.229 ↑ 140.4 10 1

Sort (cost=351,050.84..351,054.35 rows=1,404 width=12) (actual time=3,414.228..3,414.229 rows=10 loops=1)

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

Group (cost=0.28..351,020.50 rows=1,404 width=12) (actual time=0.938..3,412.703 rows=4,000 loops=1)

  • Group Key: v.video_id
  • Buffers: shared hit=101637
4. 2.035 2.035 ↓ 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.042..2.035 rows=4,000 loops=1)

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

SubPlan (forGroup)

6. 20.000 3,408.000 ↑ 1.0 1 4,000

Aggregate (cost=249.88..249.89 rows=1 width=8) (actual time=0.852..0.852 rows=1 loops=4,000)

  • Buffers: shared hit=101599
7. 88.000 3,388.000 ↓ 0.0 0 4,000

Nested Loop (cost=33.59..249.75 rows=49 width=4) (actual time=0.834..0.847 rows=0 loops=4,000)

  • Join Filter: ((l.user_id <> f.user_id) AND ((f.friend_id = l.user_id) OR (f2.friend_id = l.user_id)))
  • Rows Removed by Join Filter: 27
  • Buffers: shared hit=101599
8. 1,723.976 3,300.000 ↑ 9.3 67 4,000

Hash Join (cost=29.26..135.07 rows=622 width=12) (actual time=0.007..0.825 rows=67 loops=4,000)

  • Hash Cond: (f2.user_id = f.friend_id)
  • Join Filter: (f.friend_id <> f2.friend_id)
  • Buffers: shared hit=92011
9. 1,576.000 1,576.000 ↑ 1.0 5,000 4,000

Seq Scan on friend f2 (cost=0.00..73.00 rows=5,000 width=8) (actual time=0.003..0.394 rows=5,000 loops=4,000)

  • Buffers: shared hit=92000
10. 0.005 0.024 ↑ 2.1 12 1

Hash (cost=28.94..28.94 rows=25 width=8) (actual time=0.024..0.024 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=11
11. 0.006 0.019 ↑ 2.1 12 1

Bitmap Heap Scan on friend f (cost=4.48..28.94 rows=25 width=8) (actual time=0.017..0.019 rows=12 loops=1)

  • Recheck Cond: (user_id = $1)
  • Heap Blocks: exact=9
  • Buffers: shared hit=11
12. 0.013 0.013 ↑ 2.1 12 1

Bitmap Index Scan on friends_user_id (cost=0.00..4.47 rows=25 width=0) (actual time=0.013..0.013 rows=12 loops=1)

  • Index Cond: (user_id = $1)
  • Buffers: shared hit=2
13. 0.000 0.000 ↓ 0.0 0 268,000

Materialize (cost=4.34..15.19 rows=8 width=8) (actual time=0.000..0.000 rows=0 loops=268,000)

  • Buffers: shared hit=9588
14. 4.000 12.000 ↓ 0.0 0 4,000

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

  • Recheck Cond: (video_id = v.video_id)
  • Heap Blocks: exact=1576
  • Buffers: shared hit=9588
15. 8.000 8.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.002..0.002 rows=0 loops=4,000)

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