explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9Qid

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

Limit (cost=150,316.34..150,316.36 rows=10 width=12) (actual time=787.467..787.472 rows=10 loops=1)

  • Buffers: shared hit=58658
2. 1.033 787.467 ↑ 140.4 10 1

Sort (cost=150,316.34..150,319.85 rows=1,404 width=12) (actual time=787.466..787.467 rows=10 loops=1)

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

Group (cost=0.28..150,286.00 rows=1,404 width=12) (actual time=0.232..786.434 rows=4,000 loops=1)

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

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

SubPlan (forGroup)

6. 16.000 780.000 ↑ 1.0 1 4,000

Aggregate (cost=106.90..106.91 rows=1 width=8) (actual time=0.195..0.195 rows=1 loops=4,000)

  • Buffers: shared hit=58620
7. 6.525 764.000 ↓ 0.0 0 4,000

Hash Join (cost=68.97..106.89 rows=4 width=4) (actual time=0.191..0.191 rows=0 loops=4,000)

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

Hash Join (cost=34.49..72.04 rows=84 width=8) (actual time=0.275..0.275 rows=0 loops=1,329)

  • Hash Cond: (l2.video_id = l.video_id)
  • Buffers: shared hit=14620
9. 171.441 171.441 ↑ 1.2 1,599 1,329

Seq Scan on likes l2 (cost=0.00..29.70 rows=1,870 width=8) (actual time=0.002..0.129 rows=1,599 loops=1,329)

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

Hash (cost=34.38..34.38 rows=9 width=8) (actual time=0.095..0.095 rows=0 loops=1)

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

Seq Scan on likes l (cost=0.00..34.38 rows=9 width=8) (actual time=0.095..0.096 rows=0 loops=1)

  • Filter: (user_id = $1)
  • Rows Removed by Filter: 1600
  • Buffers: shared hit=11
12. 8.000 392.000 ↓ 0.0 0 4,000

Hash (cost=34.38..34.38 rows=9 width=8) (actual time=0.098..0.098 rows=0 loops=4,000)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=44000
13. 384.000 384.000 ↓ 0.0 0 4,000

Seq Scan on likes l3 (cost=0.00..34.38 rows=9 width=8) (actual time=0.080..0.096 rows=0 loops=4,000)

  • Filter: (video_id = v.video_id)
  • Rows Removed by Filter: 1600
  • Buffers: shared hit=44000
Planning time : 0.246 ms
Execution time : 787.506 ms