explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VTS8

Settings
# exclusive inclusive rows x rows loops node
1. 0.232 4,486.867 ↑ 35.4 47 1

Subquery Scan on t (cost=74,762.87..74,912.81 rows=1,666 width=36) (actual time=4,483.254..4,486.867 rows=47 loops=1)

  • Filter: (t.rk <= 10)
  • Rows Removed by Filter: 4949
2.          

CTE sm_tb

3. 2,888.245 3,054.843 ↑ 1.0 100,000 1

Merge Right Join (cost=13,110.65..17,437.59 rows=100,038 width=36) (actual time=132.972..3,054.843 rows=100,000 loops=1)

  • Merge Cond: (user2_like.user_id = u_1.user_id)
4. 0.523 134.533 ↑ 1.5 410 1

Finalize GroupAggregate (cost=13,110.36..13,183.69 rows=607 width=16) (actual time=132.892..134.533 rows=410 loops=1)

  • Group Key: user1_like.user_id, user2_like.user_id
5. 0.000 134.010 ↑ 1.2 411 1

Gather Merge (cost=13,110.36..13,173.83 rows=506 width=16) (actual time=132.888..134.010 rows=411 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 0.408 222.228 ↑ 1.8 137 3

Partial GroupAggregate (cost=12,110.34..12,115.40 rows=253 width=16) (actual time=73.903..74.076 rows=137 loops=3)

  • Group Key: user1_like.user_id, user2_like.user_id
7. 0.567 221.820 ↑ 1.8 137 3

Sort (cost=12,110.34..12,110.97 rows=253 width=8) (actual time=73.891..73.940 rows=137 loops=3)

  • Sort Key: user2_like.user_id
  • Sort Method: quicksort Memory: 32kB
  • Worker 0: Sort Method: quicksort Memory: 29kB
  • Worker 1: Sort Method: quicksort Memory: 31kB
8. 72.090 221.253 ↑ 1.8 137 3

Parallel Hash Join (cost=5,789.19..12,100.24 rows=253 width=8) (actual time=31.799..73.751 rows=137 loops=3)

  • Hash Cond: (user2_like.video_id = user1_like.video_id)
  • Join Filter: (user1_like.user_id <> user2_like.user_id)
  • Rows Removed by Join Filter: 1
9. 55.908 55.908 ↑ 1.2 166,667 3

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

10. 0.249 93.255 ↑ 2.0 1 3

Parallel Hash (cost=5,789.17..5,789.17 rows=2 width=8) (actual time=31.085..31.085 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
11. 93.006 93.006 ↑ 1.0 2 2

Parallel Seq Scan on likes user1_like (cost=0.00..5,789.17 rows=2 width=8) (actual time=38.282..46.503 rows=2 loops=2)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 249998
12. 32.065 32.065 ↑ 1.0 100,000 1

Index Only Scan using users_pkey on users u_1 (cost=0.29..3,239.86 rows=100,038 width=4) (actual time=0.030..32.065 rows=100,000 loops=1)

  • Heap Fetches: 100000
13. 3.227 4,486.635 ↑ 1.0 4,996 1

WindowAgg (cost=57,325.27..57,412.74 rows=4,998 width=44) (actual time=4,483.252..4,486.635 rows=4,996 loops=1)

14. 1.588 4,483.408 ↑ 1.0 4,996 1

Sort (cost=57,325.27..57,337.77 rows=4,998 width=36) (actual time=4,483.237..4,483.408 rows=4,996 loops=1)

  • Sort Key: (sum(sm_tb.similiarity)) DESC
  • Sort Method: quicksort Memory: 427kB
15. 203.753 4,481.820 ↑ 1.0 4,996 1

HashAggregate (cost=56,955.74..57,018.22 rows=4,998 width=36) (actual time=4,479.426..4,481.820 rows=4,996 loops=1)

  • Group Key: l.video_id
16. 573.530 4,278.067 ↑ 1.1 499,585 1

Hash Right Join (cost=42,142.73..54,188.79 rows=553,391 width=36) (actual time=757.016..4,278.067 rows=499,585 loops=1)

  • Hash Cond: (sm_tb.user_id = l.user_id)
17. 3,113.593 3,113.593 ↑ 1.0 100,000 1

CTE Scan on sm_tb (cost=0.00..2,000.76 rows=100,038 width=36) (actual time=132.978..3,113.593 rows=100,000 loops=1)

18. 211.549 590.944 ↓ 1.0 499,585 1

Hash (cost=33,967.45..33,967.45 rows=498,262 width=8) (actual time=590.944..590.944 rows=499,585 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3449kB
19. 71.747 379.395 ↓ 1.0 499,585 1

Hash Anti Join (cost=6,892.68..33,967.45 rows=498,262 width=8) (actual time=118.634..379.395 rows=499,585 loops=1)

  • Hash Cond: (l.user_id = u.user_id)
20. 63.886 307.194 ↓ 1.0 499,585 1

Hash Anti Join (cost=6,884.35..27,668.56 rows=498,267 width=8) (actual time=118.144..307.194 rows=499,585 loops=1)

  • Hash Cond: (l.video_id = w.video_id)
21. 79.111 242.156 ↓ 1.0 499,585 1

Hash Anti Join (cost=6,789.84..21,283.06 rows=498,367 width=8) (actual time=116.966..242.156 rows=499,585 loops=1)

  • Hash Cond: (l.video_id = l_user.video_id)
22. 46.128 46.128 ↑ 1.0 500,000 1

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

23. 0.000 116.917 ↑ 1.5 4 1

Hash (cost=6,789.77..6,789.77 rows=6 width=4) (actual time=116.917..116.917 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 75.450 116.938 ↑ 1.5 4 1

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

  • Workers Planned: 2
  • Workers Launched: 2
25. 41.488 41.488 ↑ 2.0 1 3

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

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 166665
26. 0.002 1.152 ↓ 0.0 0 1

Hash (cost=94.50..94.50 rows=1 width=4) (actual time=1.152..1.152 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
27. 1.150 1.150 ↓ 0.0 0 1

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

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 5000
28. 0.016 0.454 ↑ 1.0 1 1

Hash (cost=8.31..8.31 rows=1 width=4) (actual time=0.454..0.454 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.438 0.438 ↑ 1.0 1 1

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

  • Index Cond: (user_id = 1)
  • Heap Fetches: 1