explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ttYm

Settings
# exclusive inclusive rows x rows loops node
1. 0.808 172.675 ↑ 35.5 47 1

Subquery Scan on t (cost=9,720.74..9,870.74 rows=1,667 width=12) (actual time=165.582..172.675 rows=47 loops=1)

  • Filter: (t.rk <= 10)
  • Rows Removed by Filter: 4953
2. 5.960 171.867 ↑ 1.0 5,000 1

WindowAgg (cost=9,720.74..9,808.24 rows=5,000 width=20) (actual time=165.580..171.867 rows=5,000 loops=1)

3. 1.582 165.907 ↑ 1.0 5,000 1

Sort (cost=9,720.74..9,733.24 rows=5,000 width=12) (actual time=165.550..165.907 rows=5,000 loops=1)

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

Hash Left Join (cost=9,317.41..9,413.54 rows=5,000 width=12) (actual time=161.366..164.325 rows=5,000 loops=1)

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

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

6. 0.440 161.306 ↑ 2.9 1,718 1

Hash (cost=9,254.93..9,254.93 rows=4,998 width=12) (actual time=161.306..161.306 rows=1,718 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 138kB
7. 0.309 160.866 ↑ 2.9 1,718 1

Subquery Scan on friend_video (cost=9,154.97..9,254.93 rows=4,998 width=12) (actual time=159.918..160.866 rows=1,718 loops=1)

8. 2.088 160.557 ↑ 2.9 1,718 1

HashAggregate (cost=9,154.97..9,204.95 rows=4,998 width=12) (actual time=159.917..160.557 rows=1,718 loops=1)

  • Group Key: l.video_id
9. 0.552 158.469 ↑ 4.3 2,116 1

Hash Anti Join (cost=7,575.48..9,109.81 rows=9,033 width=4) (actual time=135.873..158.469 rows=2,116 loops=1)

  • Hash Cond: (l.video_id = w.video_id)
10. 0.780 157.791 ↑ 4.3 2,116 1

Hash Anti Join (cost=7,567.17..8,987.45 rows=9,035 width=4) (actual time=135.732..157.791 rows=2,116 loops=1)

  • Hash Cond: (l.video_id = l_user_1.video_id)
11. 0.658 156.970 ↑ 3.6 2,527 1

Nested Loop (cost=7,539.33..8,845.24 rows=9,065 width=4) (actual time=135.665..156.970 rows=2,527 loops=1)

12. 0.554 135.402 ↑ 4.0 410 1

HashAggregate (cost=7,538.91..7,555.24 rows=1,633 width=4) (actual time=135.166..135.402 rows=410 loops=1)

  • Group Key: l_other_user.user_id
13. 64.026 134.848 ↑ 4.0 411 1

Gather (cost=1,027.91..7,534.82 rows=1,633 width=4) (actual time=2.181..134.848 rows=411 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 24.604 70.822 ↑ 5.0 137 3

Hash Join (cost=27.91..6,371.52 rows=680 width=4) (actual time=1.710..70.822 rows=137 loops=3)

  • Hash Cond: (l_other_user.video_id = l_user.video_id)
15. 46.117 46.117 ↑ 1.2 166,665 3

Parallel Seq Scan on likes l_other_user (cost=0.00..5,789.17 rows=208,331 width=8) (actual time=0.023..46.117 rows=166,665 loops=3)

  • Filter: (user_id <> 1)
  • Rows Removed by Filter: 1
16. 0.012 0.101 ↑ 1.5 4 3

Hash (cost=27.84..27.84 rows=6 width=4) (actual time=0.101..0.101 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.012 0.089 ↑ 1.5 4 3

HashAggregate (cost=27.78..27.84 rows=6 width=4) (actual time=0.088..0.089 rows=4 loops=3)

  • Group Key: l_user.video_id
18. 0.038 0.077 ↑ 1.5 4 3

Bitmap Heap Scan on likes l_user (cost=4.47..27.76 rows=6 width=4) (actual time=0.052..0.077 rows=4 loops=3)

  • Recheck Cond: (user_id = 1)
  • Heap Blocks: exact=4
19. 0.039 0.039 ↑ 1.5 4 3

Bitmap Index Scan on likes_index (cost=0.00..4.47 rows=6 width=0) (actual time=0.039..0.039 rows=4 loops=3)

  • Index Cond: (user_id = 1)
20. 20.910 20.910 ↑ 1.0 6 410

Index Only Scan using likes_index on likes l (cost=0.42..0.73 rows=6 width=8) (actual time=0.044..0.051 rows=6 loops=410)

  • Index Cond: (user_id = l_other_user.user_id)
  • Heap Fetches: 2527
21. 0.010 0.041 ↑ 1.5 4 1

Hash (cost=27.76..27.76 rows=6 width=4) (actual time=0.041..0.041 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.010 0.031 ↑ 1.5 4 1

Bitmap Heap Scan on likes l_user_1 (cost=4.47..27.76 rows=6 width=4) (actual time=0.025..0.031 rows=4 loops=1)

  • Recheck Cond: (user_id = 1)
  • Heap Blocks: exact=4
23. 0.021 0.021 ↑ 1.5 4 1

Bitmap Index Scan on likes_index (cost=0.00..4.47 rows=6 width=0) (actual time=0.021..0.021 rows=4 loops=1)

  • Index Cond: (user_id = 1)
24. 0.000 0.126 ↓ 0.0 0 1

Hash (cost=8.30..8.30 rows=1 width=4) (actual time=0.126..0.126 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
25. 0.126 0.126 ↓ 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.126..0.126 rows=0 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
Planning time : 4.215 ms
Execution time : 173.584 ms