explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Awku2

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 25.437 ↑ 1.0 10 1

Limit (cost=21,620.82..21,620.84 rows=10 width=16) (actual time=25.433..25.437 rows=10 loops=1)

  • Buffers: shared hit=9629
2. 0.777 25.431 ↑ 140.4 10 1

Sort (cost=21,620.82..21,624.33 rows=1,404 width=16) (actual time=25.431..25.431 rows=10 loops=1)

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

Group (cost=0.55..21,590.48 rows=1,404 width=16) (actual time=0.045..24.654 rows=4,000 loops=1)

  • Group Key: v.video_id, u.user_id
  • Buffers: shared hit=9629
4. 1.639 2.784 ↓ 2.8 4,000 1

Nested Loop (cost=0.55..203.18 rows=1,404 width=8) (actual time=0.024..2.784 rows=4,000 loops=1)

  • Buffers: shared hit=41
5. 1.145 1.145 ↓ 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.010..1.145 rows=4,000 loops=1)

  • Heap Fetches: 4000
  • Buffers: shared hit=38
6. 0.000 0.000 ↑ 1.0 1 4,000

Materialize (cost=0.27..8.30 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=4,000)

  • Buffers: shared hit=3
7. 0.011 0.011 ↑ 1.0 1 1

Index Only Scan using users_pkey on users u (cost=0.27..8.29 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (user_id = $1)
  • Heap Fetches: 1
  • Buffers: shared hit=3
8.          

SubPlan (forGroup)

9. 12.000 20.000 ↑ 1.0 1 4,000

Aggregate (cost=15.21..15.23 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=4,000)

  • Buffers: shared hit=9588
10. 4.000 8.000 ↓ 0.0 0 4,000

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

  • Recheck Cond: (v.video_id = video_id)
  • Filter: (user_id <> u.user_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: (v.video_id = video_id)
  • Buffers: shared hit=8012