explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vAr

Settings
# exclusive inclusive rows x rows loops node
1. 0.292 4,288.189 ↑ 35.4 47 1

Subquery Scan on t (cost=63,197.09..63,347.03 rows=1,666 width=36) (actual time=4,283.725..4,288.189 rows=47 loops=1)

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

CTE sm_tb

3. 2,893.269 3,032.522 ↑ 1.0 100,000 1

Merge Right Join (cost=8,393.08..12,720.02 rows=100,038 width=36) (actual time=106.283..3,032.522 rows=100,000 loops=1)

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

Finalize GroupAggregate (cost=8,392.79..8,466.12 rows=607 width=16) (actual time=106.160..108.085 rows=410 loops=1)

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

Gather Merge (cost=8,392.79..8,456.25 rows=506 width=16) (actual time=106.156..107.260 rows=411 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 0.558 137.064 ↑ 1.8 137 3

Partial GroupAggregate (cost=7,392.76..7,397.82 rows=253 width=16) (actual time=45.441..45.688 rows=137 loops=3)

  • Group Key: user1_like.user_id, user2_like.user_id
7. 0.900 136.506 ↑ 1.8 137 3

Sort (cost=7,392.76..7,393.39 rows=253 width=8) (actual time=45.429..45.502 rows=137 loops=3)

  • Sort Key: user2_like.user_id
  • Sort Method: quicksort Memory: 38kB
  • Worker 0: Sort Method: quicksort Memory: 26kB
  • Worker 1: Sort Method: quicksort Memory: 29kB
8. 75.723 135.606 ↑ 1.8 137 3

Hash Join (cost=27.84..7,382.66 rows=253 width=8) (actual time=0.481..45.202 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. 59.646 59.646 ↑ 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.016..19.882 rows=166,667 loops=3)

10. 0.039 0.237 ↑ 1.5 4 3

Hash (cost=27.76..27.76 rows=6 width=8) (actual time=0.079..0.079 rows=4 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.102 0.198 ↑ 1.5 4 3

Bitmap Heap Scan on likes user1_like (cost=4.47..27.76 rows=6 width=8) (actual time=0.044..0.066 rows=4 loops=3)

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

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

  • Index Cond: (user_id = 1)
13. 31.168 31.168 ↑ 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.048..31.168 rows=100,000 loops=1)

  • Heap Fetches: 100000
14. 3.992 4,287.897 ↑ 1.0 4,996 1

WindowAgg (cost=50,477.07..50,564.54 rows=4,998 width=44) (actual time=4,283.724..4,287.897 rows=4,996 loops=1)

15. 1.704 4,283.905 ↑ 1.0 4,996 1

Sort (cost=50,477.07..50,489.57 rows=4,998 width=36) (actual time=4,283.702..4,283.905 rows=4,996 loops=1)

  • Sort Key: (sum(sm_tb.similiarity)) DESC
  • Sort Method: quicksort Memory: 427kB
16. 196.627 4,282.201 ↑ 1.0 4,996 1

HashAggregate (cost=50,107.54..50,170.01 rows=4,998 width=36) (actual time=4,279.949..4,282.201 rows=4,996 loops=1)

  • Group Key: l.video_id
17. 551.463 4,085.574 ↑ 1.1 499,585 1

Hash Right Join (cost=35,294.52..47,340.58 rows=553,391 width=36) (actual time=578.924..4,085.574 rows=499,585 loops=1)

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

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

19. 200.721 442.096 ↓ 1.0 499,585 1

Hash (cost=27,119.25..27,119.25 rows=498,262 width=8) (actual time=442.096..442.096 rows=499,585 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3449kB
20. 63.183 241.375 ↓ 1.0 499,585 1

Hash Anti Join (cost=44.47..27,119.25 rows=498,262 width=8) (actual time=0.572..241.375 rows=499,585 loops=1)

  • Hash Cond: (l.user_id = u.user_id)
21. 58.540 177.883 ↓ 1.0 499,585 1

Hash Anti Join (cost=36.15..20,820.36 rows=498,267 width=8) (actual time=0.253..177.883 rows=499,585 loops=1)

  • Hash Cond: (l.video_id = w.video_id)
22. 74.651 119.164 ↓ 1.0 499,585 1

Hash Anti Join (cost=27.84..14,521.05 rows=498,367 width=8) (actual time=0.061..119.164 rows=499,585 loops=1)

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

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

24. 0.008 0.033 ↑ 1.5 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.011 0.025 ↑ 1.5 4 1

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

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

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

  • Index Cond: (user_id = 1)
27. 0.000 0.179 ↓ 0.0 0 1

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

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

  • Index Cond: (user_id = 1)
  • Heap Fetches: 0
29. 0.010 0.309 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.299 0.299 ↑ 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.298..0.299 rows=1 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 1
Planning time : 46.967 ms
Execution time : 4,305.068 ms