explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1hSQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 5,441.485 ↑ 1.0 10 1

Limit (cost=459,383.24..459,383.26 rows=10 width=12) (actual time=5,441.478..5,441.485 rows=10 loops=1)

  • Buffers: shared hit=101649 read=6
2. 2.958 5,441.480 ↑ 140.4 10 1

Sort (cost=459,383.24..459,386.75 rows=1,404 width=12) (actual time=5,441.478..5,441.480 rows=10 loops=1)

  • Sort Key: ((SubPlan 1)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=101649 read=6
3. 7.994 5,438.522 ↓ 2.8 4,000 1

Group (cost=0.28..459,352.90 rows=1,404 width=12) (actual time=2.850..5,438.522 rows=4,000 loops=1)

  • Group Key: v.video_id
  • Buffers: shared hit=101646 read=6
4. 6.528 6.528 ↓ 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.110..6.528 rows=4,000 loops=1)

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

SubPlan (forGroup)

6. 40.000 5,424.000 ↑ 1.0 1 4,000

Aggregate (cost=327.04..327.05 rows=1 width=8) (actual time=1.355..1.356 rows=1 loops=4,000)

  • Buffers: shared hit=101608 read=6
7. 148.000 5,384.000 ↓ 0.0 0 4,000

Nested Loop (cost=92.64..326.90 rows=53 width=4) (actual time=1.326..1.346 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=101605 read=6
8. 2,718.886 5,236.000 ↑ 10.0 67 4,000

Hash Join (cost=88.30..204.22 rows=672 width=12) (actual time=0.012..1.309 rows=67 loops=4,000)

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

Seq Scan on friend f2 (cost=0.00..74.98 rows=5,198 width=8) (actual time=0.006..0.629 rows=5,000 loops=4,000)

  • Buffers: shared hit=92000
10. 0.012 1.114 ↑ 2.2 12 1

Hash (cost=87.98..87.98 rows=26 width=8) (actual time=1.114..1.114 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=23
11. 1.102 1.102 ↑ 2.2 12 1

Seq Scan on friend f (cost=0.00..87.98 rows=26 width=8) (actual time=0.428..1.102 rows=12 loops=1)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 4988
  • Buffers: shared hit=23
12. 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=9582 read=6
13. 8.000 20.000 ↓ 0.0 0 4,000

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

  • Recheck Cond: (video_id = v.video_id)
  • Heap Blocks: exact=1576
  • Buffers: shared hit=9582 read=6
14. 12.000 12.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.003..0.003 rows=0 loops=4,000)

  • Index Cond: (video_id = v.video_id)
  • Buffers: shared hit=8006 read=6