explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ka7

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 407.347 ↑ 1.0 10 1

Limit (cost=90,665.63..90,665.65 rows=10 width=12) (actual time=407.343..407.347 rows=10 loops=1)

  • Buffers: shared hit=24237
2. 1.108 407.342 ↑ 140.4 10 1

Sort (cost=90,665.63..90,669.14 rows=1,404 width=12) (actual time=407.342..407.342 rows=10 loops=1)

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

Group (cost=0.28..90,635.29 rows=1,404 width=12) (actual time=0.044..406.234 rows=4,000 loops=1)

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

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

SubPlan (forGroup)

6. 12.000 400.000 ↑ 1.0 1 4,000

Aggregate (cost=64.42..64.43 rows=1 width=8) (actual time=0.100..0.100 rows=1 loops=4,000)

  • Buffers: shared hit=24199
7. 6.525 388.000 ↓ 0.0 0 4,000

Hash Join (cost=30.49..64.41 rows=3 width=4) (actual time=0.097..0.097 rows=0 loops=4,000)

  • Hash Cond: (l2.user_id = l3.user_id)
  • Join Filter: (l.user_id <> l3.user_id)
  • Buffers: shared hit=24199
8. 191.372 365.475 ↓ 0.0 0 1,329

Hash Join (cost=15.25..48.89 rows=64 width=8) (actual time=0.275..0.275 rows=0 loops=1,329)

  • Hash Cond: (l2.video_id = l.video_id)
  • Buffers: shared hit=14611
9. 174.099 174.099 ↑ 1.0 1,599 1,329

Seq Scan on likes l2 (cost=0.00..27.00 rows=1,600 width=8) (actual time=0.002..0.131 rows=1,599 loops=1,329)

  • Buffers: shared hit=14609
10. 0.000 0.004 ↓ 0.0 0 1

Hash (cost=15.15..15.15 rows=8 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=2
11. 0.001 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on likes l (cost=4.34..15.15 rows=8 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Recheck Cond: (user_id = $1)
  • Buffers: shared hit=2
12. 0.003 0.003 ↓ 0.0 0 1

Bitmap Index Scan on likes_user_id (cost=0.00..4.34 rows=8 width=0) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (user_id = $1)
  • Buffers: shared hit=2
13. 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
14. 4.000 8.000 ↓ 0.0 0 4,000

Bitmap Heap Scan on likes l3 (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
15. 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