explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SuLB

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

Limit (cost=1,013,987.72..1,013,987.74 rows=10 width=12) (actual time=877.926..877.931 rows=10 loops=1)

  • Buffers: shared hit=80861
2. 1.071 877.928 ↑ 400.0 10 1

Sort (cost=1,013,987.72..1,013,997.72 rows=4,000 width=12) (actual time=877.926..877.928 rows=10 loops=1)

  • Sort Key: ((SubPlan 1)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=80861
3. 3.111 876.857 ↑ 1.0 4,000 1

Group (cost=0.28..1,013,901.28 rows=4,000 width=12) (actual time=1.493..876.857 rows=4,000 loops=1)

  • Group Key: v.video_id
  • Buffers: shared hit=80861
4. 1.746 1.746 ↑ 1.0 4,000 1

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

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

SubPlan (forGroup)

6. 8.000 872.000 ↑ 1.0 1 4,000

Aggregate (cost=253.43..253.44 rows=1 width=8) (actual time=0.218..0.218 rows=1 loops=4,000)

  • Buffers: shared hit=80823
7. 14.949 864.000 ↓ 0.0 0 4,000

Hash Join (cost=135.50..253.43 rows=1 width=4) (actual time=0.210..0.216 rows=0 loops=4,000)

  • Hash Cond: (f.friend_id = f2.user_id)
  • Join Filter: ((f.friend_id <> f2.friend_id) AND ((f.friend_id = l.user_id) OR (f2.friend_id = l.user_id)))
  • Rows Removed by Join Filter: 27
  • Buffers: shared hit=80823
8. 14.400 848.000 ↑ 2.4 5 4,000

Nested Loop (cost=0.00..116.65 rows=12 width=12) (actual time=0.120..0.212 rows=5 loops=4,000)

  • Join Filter: (l.user_id <> f.user_id)
  • Buffers: shared hit=80800
9. 392.000 392.000 ↓ 0.0 0 4,000

Seq Scan on likes l (cost=0.00..31.00 rows=1 width=8) (actual time=0.080..0.098 rows=0 loops=4,000)

  • Filter: (video_id = v.video_id)
  • Rows Removed by Filter: 1600
  • Buffers: shared hit=44000
10. 441.600 441.600 ↑ 1.0 12 1,600

Seq Scan on friend f (cost=0.00..85.50 rows=12 width=8) (actual time=0.108..0.276 rows=12 loops=1,600)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 4988
  • Buffers: shared hit=36800
11. 0.613 1.051 ↑ 1.0 5,000 1

Hash (cost=73.00..73.00 rows=5,000 width=8) (actual time=1.051..1.051 rows=5,000 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 260kB
  • Buffers: shared hit=23
12. 0.438 0.438 ↑ 1.0 5,000 1

Seq Scan on friend f2 (cost=0.00..73.00 rows=5,000 width=8) (actual time=0.005..0.438 rows=5,000 loops=1)

  • Buffers: shared hit=23