explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pfSA

Settings
# exclusive inclusive rows x rows loops node
1. 0.382 257.055 ↑ 138.9 12 1

Subquery Scan on t (cost=26,180.23..26,330.23 rows=1,667 width=12) (actual time=252.957..257.055 rows=12 loops=1)

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

WindowAgg (cost=26,180.23..26,267.73 rows=5,000 width=20) (actual time=252.956..256.673 rows=5,000 loops=1)

3. 1.227 253.162 ↑ 1.0 5,000 1

Sort (cost=26,180.23..26,192.73 rows=5,000 width=12) (actual time=252.934..253.162 rows=5,000 loops=1)

  • Sort Key: (COALESCE((count(l.video_id)), '0'::bigint)) DESC
  • Sort Method: quicksort Memory: 427kB
4. 1.058 251.935 ↑ 1.0 5,000 1

Hash Right Join (cost=20,080.54..25,873.04 rows=5,000 width=12) (actual time=250.880..251.935 rows=5,000 loops=1)

  • Hash Cond: (l.video_id = v.video_id)
5. 0.025 249.656 ↑ 1.8 12 1

GroupAggregate (cost=19,935.04..25,727.26 rows=22 width=12) (actual time=249.625..249.656 rows=12 loops=1)

  • Group Key: l.video_id
6. 0.042 249.631 ↑ 1.8 12 1

Nested Loop Anti Join (cost=19,935.04..25,726.93 rows=22 width=4) (actual time=249.600..249.631 rows=12 loops=1)

  • Join Filter: (l_user.video_id = l.video_id)
  • Rows Removed by Join Filter: 48
7. 0.007 150.589 ↑ 1.8 12 1

Merge Anti Join (cost=18,935.04..18,935.17 rows=22 width=4) (actual time=150.585..150.589 rows=12 loops=1)

  • Merge Cond: (l.video_id = w.video_id)
8. 0.030 150.115 ↑ 1.8 12 1

Sort (cost=18,840.53..18,840.59 rows=22 width=4) (actual time=150.114..150.115 rows=12 loops=1)

  • Sort Key: l.video_id
  • Sort Method: quicksort Memory: 25kB
9. 36.523 150.085 ↑ 1.8 12 1

Hash Join (cost=9,342.48..18,840.04 rows=22 width=4) (actual time=86.709..150.085 rows=12 loops=1)

  • Hash Cond: (l.user_id = f.friend_id)
10. 39.044 39.044 ↑ 1.0 500,000 1

Seq Scan on likes l (cost=0.00..8,185.00 rows=500,000 width=8) (actual time=0.026..39.044 rows=500,000 loops=1)

11. 0.015 74.518 ↑ 2.0 2 1

Hash (cost=9,342.43..9,342.43 rows=4 width=4) (actual time=74.518..74.518 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.038 74.503 ↑ 2.0 2 1

HashAggregate (cost=9,342.35..9,342.39 rows=4 width=4) (actual time=74.502..74.503 rows=2 loops=1)

  • Group Key: f.friend_id
13. 0.004 74.465 ↑ 2.0 2 1

Append (cost=0.00..9,342.34 rows=4 width=4) (actual time=13.353..74.465 rows=2 loops=1)

14. 14.499 14.499 ↑ 2.0 1 1

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

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 99999
15. 5.818 59.962 ↑ 2.0 1 1

Hash Join (cost=5,329.12..7,649.28 rows=2 width=4) (actual time=42.171..59.962 rows=1 loops=1)

  • Hash Cond: (u.user_id = f_1.friend_id)
16. 19.419 31.731 ↓ 2.0 99,998 1

Seq Scan on users u (cost=1,693.01..3,825.57 rows=50,018 width=4) (actual time=12.370..31.731 rows=99,998 loops=1)

  • Filter: ((user_id <> 1) AND (NOT (hashed SubPlan 1)))
  • Rows Removed by Filter: 2
17.          

SubPlan (forSeq Scan)

18. 12.312 12.312 ↑ 2.0 1 1

Seq Scan on friend directfriend (cost=0.00..1,693.00 rows=2 width=4) (actual time=11.434..12.312 rows=1 loops=1)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 99999
19. 0.017 22.413 ↑ 4.0 1 1

Hash (cost=3,636.07..3,636.07 rows=4 width=4) (actual time=22.413..22.413 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 7.809 22.396 ↑ 4.0 1 1

Hash Join (cost=1,693.03..3,636.07 rows=4 width=4) (actual time=12.045..22.396 rows=1 loops=1)

  • Hash Cond: (f_1.user_id = ff.friend_id)
21. 6.629 6.629 ↑ 1.0 100,000 1

Seq Scan on friend f_1 (cost=0.00..1,443.00 rows=100,000 width=8) (actual time=0.024..6.629 rows=100,000 loops=1)

22. 0.049 7.958 ↑ 2.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 7.909 7.909 ↑ 2.0 1 1

Seq Scan on friend ff (cost=0.00..1,693.00 rows=2 width=4) (actual time=6.510..7.909 rows=1 loops=1)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 99999
24. 0.008 0.467 ↓ 0.0 0 1

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

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

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

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 5000
26. 0.000 99.000 ↑ 1.5 4 12

Materialize (cost=1,000.00..6,789.80 rows=6 width=4) (actual time=4.485..8.250 rows=4 loops=12)

27. 75.747 107.709 ↑ 1.5 4 1

Gather (cost=1,000.00..6,789.77 rows=6 width=4) (actual time=53.800..107.709 rows=4 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
28. 31.962 31.962 ↑ 2.0 1 3

Parallel Seq Scan on likes l_user (cost=0.00..5,789.17 rows=2 width=4) (actual time=20.014..31.962 rows=1 loops=3)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 166665
29. 0.653 1.221 ↑ 1.0 5,000 1

Hash (cost=83.00..83.00 rows=5,000 width=4) (actual time=1.221..1.221 rows=5,000 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 240kB
30. 0.568 0.568 ↑ 1.0 5,000 1

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