explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7tzQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.380 7.269 ↑ 138.9 12 1

Subquery Scan on t (cost=633.61..783.61 rows=1,667 width=12) (actual time=3.646..7.269 rows=12 loops=1)

  • Filter: (t.rk <= 10)
  • Rows Removed by Filter: 4988
2. 3.102 6.889 ↑ 1.0 5,000 1

WindowAgg (cost=633.61..721.11 rows=5,000 width=20) (actual time=3.645..6.889 rows=5,000 loops=1)

3. 1.120 3.787 ↑ 1.0 5,000 1

Sort (cost=633.61..646.11 rows=5,000 width=12) (actual time=3.619..3.787 rows=5,000 loops=1)

  • Sort Key: (COALESCE(friend_video.rank, '0'::bigint)) DESC
  • Sort Method: quicksort Memory: 427kB
4. 0.997 2.667 ↑ 1.0 5,000 1

Hash Left Join (cost=230.28..326.42 rows=5,000 width=12) (actual time=1.114..2.667 rows=5,000 loops=1)

  • Hash Cond: (v.video_id = friend_video.vid)
5. 0.600 0.600 ↑ 1.0 5,000 1

Seq Scan on video v (cost=0.00..83.00 rows=5,000 width=4) (actual time=0.036..0.600 rows=5,000 loops=1)

6. 0.012 1.070 ↑ 1.8 12 1

Hash (cost=230.01..230.01 rows=22 width=12) (actual time=1.070..1.070 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.002 1.058 ↑ 1.8 12 1

Subquery Scan on friend_video (cost=219.16..230.01 rows=22 width=12) (actual time=1.029..1.058 rows=12 loops=1)

8. 0.015 1.056 ↑ 1.8 12 1

GroupAggregate (cost=219.16..229.79 rows=22 width=12) (actual time=1.029..1.056 rows=12 loops=1)

  • Group Key: l.video_id
9. 0.003 1.041 ↑ 1.8 12 1

Nested Loop Anti Join (cost=219.16..229.46 rows=22 width=4) (actual time=1.017..1.041 rows=12 loops=1)

10. 0.004 0.942 ↑ 1.8 12 1

Merge Anti Join (cost=218.74..218.86 rows=22 width=4) (actual time=0.940..0.942 rows=12 loops=1)

  • Merge Cond: (l.video_id = w.video_id)
11. 0.077 0.856 ↑ 1.8 12 1

Sort (cost=210.43..210.48 rows=22 width=4) (actual time=0.855..0.856 rows=12 loops=1)

  • Sort Key: l.video_id
  • Sort Method: quicksort Memory: 25kB
12. 0.022 0.779 ↑ 1.8 12 1

Nested Loop (cost=103.03..209.94 rows=22 width=4) (actual time=0.681..0.779 rows=12 loops=1)

13. 0.005 0.535 ↑ 2.0 2 1

HashAggregate (cost=98.56..98.60 rows=4 width=4) (actual time=0.534..0.535 rows=2 loops=1)

  • Group Key: f.friend_id
14. 0.002 0.530 ↑ 2.0 2 1

Append (cost=4.31..98.55 rows=4 width=4) (actual time=0.011..0.530 rows=2 loops=1)

15. 0.004 0.011 ↑ 2.0 1 1

Bitmap Heap Scan on friend f (cost=4.31..11.93 rows=2 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Recheck Cond: (user_id = 1)
  • Heap Blocks: exact=1
16. 0.007 0.007 ↑ 2.0 1 1

Bitmap Index Scan on friend_index (cost=0.00..4.31 rows=2 width=0) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (user_id = 1)
17. 0.001 0.517 ↑ 2.0 1 1

Nested Loop (cost=21.24..86.56 rows=2 width=4) (actual time=0.517..0.517 rows=1 loops=1)

18. 0.012 0.078 ↑ 4.0 1 1

Nested Loop (cost=8.62..35.83 rows=4 width=4) (actual time=0.077..0.078 rows=1 loops=1)

19. 0.000 0.002 ↑ 2.0 1 1

Bitmap Heap Scan on friend ff (cost=4.31..11.93 rows=2 width=4) (actual time=0.002..0.002 rows=1 loops=1)

  • Recheck Cond: (user_id = 1)
  • Heap Blocks: exact=1
20. 0.002 0.002 ↑ 2.0 1 1

Bitmap Index Scan on friend_index (cost=0.00..4.31 rows=2 width=0) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (user_id = 1)
21. 0.003 0.064 ↑ 2.0 1 1

Bitmap Heap Scan on friend f_1 (cost=4.31..11.93 rows=2 width=8) (actual time=0.064..0.064 rows=1 loops=1)

  • Recheck Cond: (user_id = ff.friend_id)
  • Heap Blocks: exact=1
22. 0.061 0.061 ↑ 2.0 1 1

Bitmap Index Scan on friend_index (cost=0.00..4.31 rows=2 width=0) (actual time=0.061..0.061 rows=1 loops=1)

  • Index Cond: (user_id = ff.friend_id)
23. 0.427 0.438 ↑ 1.0 1 1

Index Only Scan using users_pkey on users u (cost=12.62..12.68 rows=1 width=4) (actual time=0.438..0.438 rows=1 loops=1)

  • Index Cond: (user_id = f_1.friend_id)
  • Filter: ((user_id <> 1) AND (NOT (hashed SubPlan 1)))
  • Heap Fetches: 1
24.          

SubPlan (forIndex Only Scan)

25. 0.011 0.011 ↑ 2.0 1 1

Index Scan using friend_index on friend directfriend (cost=0.29..12.33 rows=2 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (user_id = 1)
26. 0.022 0.222 ↑ 1.0 6 2

Bitmap Heap Scan on likes l (cost=4.47..27.76 rows=6 width=8) (actual time=0.104..0.111 rows=6 loops=2)

  • Recheck Cond: (user_id = f.friend_id)
  • Heap Blocks: exact=12
27. 0.200 0.200 ↑ 1.0 6 2

Bitmap Index Scan on likes_index (cost=0.00..4.47 rows=6 width=0) (actual time=0.100..0.100 rows=6 loops=2)

  • Index Cond: (user_id = f.friend_id)
28. 0.008 0.082 ↓ 0.0 0 1

Sort (cost=8.31..8.32 rows=1 width=4) (actual time=0.082..0.082 rows=0 loops=1)

  • Sort Key: w.video_id
  • Sort Method: quicksort Memory: 25kB
29. 0.074 0.074 ↓ 0.0 0 1

Index Only Scan using watch_index on watch w (cost=0.28..8.30 rows=1 width=4) (actual time=0.074..0.074 rows=0 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
30. 0.096 0.096 ↓ 0.0 0 12

Index Only Scan using likes_index on likes l_user (cost=0.42..0.48 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=12)

  • Index Cond: ((user_id = 1) AND (video_id = l.video_id))
  • Heap Fetches: 0
Planning time : 6.614 ms
Execution time : 7.845 ms