explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1Lqh

Settings
# exclusive inclusive rows x rows loops node
1. 0.430 175.859 ↓ 3.0 5,000 1

Subquery Scan on t (cost=14,797.15..14,947.15 rows=1,667 width=12) (actual time=172.832..175.859 rows=5,000 loops=1)

  • Filter: (t.rk <= 10)
2. 2.459 175.429 ↑ 1.0 5,000 1

WindowAgg (cost=14,797.15..14,884.65 rows=5,000 width=20) (actual time=172.830..175.429 rows=5,000 loops=1)

3. 1.158 172.970 ↑ 1.0 5,000 1

Sort (cost=14,797.15..14,809.65 rows=5,000 width=12) (actual time=172.807..172.970 rows=5,000 loops=1)

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

Hash Left Join (cost=14,393.82..14,489.96 rows=5,000 width=12) (actual time=170.018..171.812 rows=5,000 loops=1)

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

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

6. 0.019 169.981 ↑ 1.6 7 1

Hash (cost=14,393.68..14,393.68 rows=11 width=12) (actual time=169.981..169.981 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 0.002 169.962 ↑ 1.6 7 1

Subquery Scan on friend_video (cost=14,392.14..14,393.68 rows=11 width=12) (actual time=169.955..169.962 rows=7 loops=1)

8. 0.000 169.960 ↑ 1.6 7 1

Finalize GroupAggregate (cost=14,392.14..14,393.57 rows=11 width=12) (actual time=169.954..169.960 rows=7 loops=1)

  • Group Key: l.video_id
9. 0.000 179.753 ↑ 1.4 7 1

Gather Merge (cost=14,392.14..14,393.41 rows=10 width=12) (actual time=169.950..179.753 rows=7 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 0.063 309.648 ↑ 2.5 2 3

Partial GroupAggregate (cost=13,392.12..13,392.23 rows=5 width=12) (actual time=103.214..103.216 rows=2 loops=3)

  • Group Key: l.video_id
11. 0.009 309.585 ↑ 2.5 2 3

Merge Anti Join (cost=13,392.12..13,392.16 rows=5 width=4) (actual time=103.193..103.195 rows=2 loops=3)

  • Merge Cond: (l.video_id = w.video_id)
12. 0.135 306.114 ↑ 2.5 2 3

Sort (cost=13,297.61..13,297.62 rows=5 width=4) (actual time=102.037..102.038 rows=2 loops=3)

  • Sort Key: l.video_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
13. 0.237 305.979 ↑ 2.5 2 3

Parallel Hash Anti Join (cost=7,482.22..13,297.55 rows=5 width=4) (actual time=75.525..101.993 rows=2 loops=3)

  • Hash Cond: (l.video_id = l_user.video_id)
14. 85.953 203.175 ↑ 2.5 2 3

Hash Semi Join (cost=1,693.03..7,508.29 rows=5 width=4) (actual time=41.265..67.725 rows=2 loops=3)

  • Hash Cond: (l.user_id = f.friend_id)
15. 73.230 73.230 ↑ 1.2 166,667 3

Parallel Seq Scan on likes l (cost=0.00..5,268.33 rows=208,333 width=8) (actual time=0.013..24.410 rows=166,667 loops=3)

16. 0.060 43.992 ↑ 2.0 1 3

Hash (cost=1,693.00..1,693.00 rows=2 width=4) (actual time=14.664..14.664 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 43.932 43.932 ↑ 2.0 1 3

Seq Scan on friend f (cost=0.00..1,693.00 rows=2 width=4) (actual time=12.791..14.644 rows=1 loops=3)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 99999
18. 0.163 102.567 ↑ 2.0 1 3

Parallel Hash (cost=5,789.17..5,789.17 rows=2 width=4) (actual time=34.188..34.189 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
19. 102.404 102.404 ↑ 1.0 2 2

Parallel Seq Scan on likes l_user (cost=0.00..5,789.17 rows=2 width=4) (actual time=29.467..51.202 rows=2 loops=2)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 249998
20. 0.141 3.462 ↓ 0.0 0 3

Sort (cost=94.51..94.52 rows=1 width=4) (actual time=1.154..1.154 rows=0 loops=3)

  • Sort Key: w.video_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
21. 3.321 3.321 ↓ 0.0 0 3

Seq Scan on watch w (cost=0.00..94.50 rows=1 width=4) (actual time=1.107..1.107 rows=0 loops=3)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 5000