explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ye1Y

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 3,837.624 ↑ 1.0 10 1

Limit (cost=505,279.13..505,279.15 rows=10 width=12) (actual time=3,837.619..3,837.624 rows=10 loops=1)

  • Buffers: shared hit=136061
2. 1.505 3,837.618 ↑ 140.4 10 1

Sort (cost=505,279.13..505,282.64 rows=1,404 width=12) (actual time=3,837.618..3,837.618 rows=10 loops=1)

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

Group (cost=0.28..505,248.79 rows=1,404 width=12) (actual time=1.273..3,836.113 rows=4,000 loops=1)

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

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

SubPlan (forGroup)

6. 20.000 3,828.000 ↑ 1.0 1 4,000

Aggregate (cost=359.73..359.74 rows=1 width=8) (actual time=0.957..0.957 rows=1 loops=4,000)

  • Buffers: shared hit=136023
7. 0.000 3,808.000 ↓ 0.0 0 4,000

Nested Loop (cost=88.30..359.58 rows=60 width=4) (actual time=0.939..0.952 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=136023
8. 1,739.705 3,336.000 ↑ 10.0 67 4,000

Hash Join (cost=88.30..204.22 rows=672 width=12) (actual time=0.008..0.834 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. 1,596.000 1,596.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.004..0.399 rows=5,000 loops=4,000)

  • Buffers: shared hit=92000
10. 0.005 0.295 ↑ 2.2 12 1

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

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

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

  • Filter: (user_id = $1)
  • Rows Removed by Filter: 4988
  • Buffers: shared hit=23
12. 140.000 536.000 ↓ 0.0 0 268,000

Materialize (cost=0.00..34.42 rows=9 width=8) (actual time=0.001..0.002 rows=0 loops=268,000)

  • Buffers: shared hit=44000
13. 396.000 396.000 ↓ 0.0 0 4,000

Seq Scan on likes l (cost=0.00..34.38 rows=9 width=8) (actual time=0.081..0.099 rows=0 loops=4,000)

  • Filter: (video_id = v.video_id)
  • Rows Removed by Filter: 1600
  • Buffers: shared hit=44000
Planning time : 0.243 ms
Execution time : 3,837.687 ms