explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gs3O

Settings
# exclusive inclusive rows x rows loops node
1. 0.061 526.883 ↑ 1.0 10 1

Limit (cost=69,473.98..69,474.00 rows=10 width=12) (actual time=526.823..526.883 rows=10 loops=1)

  • Buffers: shared hit=2575 read=1281, temp read=2015 written=2021
2. 8.856 526.822 ↑ 7,007.4 10 1

Sort (cost=69,473.98..69,649.16 rows=70,074 width=12) (actual time=526.822..526.822 rows=10 loops=1)

  • Sort Key: (sum(COALESCE((log(((1 + COALESCE((count(l1.video_id)), '0'::bigint)))::double precision)), '0'::double precision))) DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=2047 read=1281, temp read=2015 written=2021
3. 30.553 517.966 ↓ 1.2 86,591 1

GroupAggregate (cost=65,758.96..67,959.70 rows=70,074 width=12) (actual time=465.598..517.966 rows=86,591 loops=1)

  • Group Key: likes.video_id
  • Buffers: shared hit=2044 read=1281, temp read=2015 written=2021
4. 96.158 487.413 ↑ 1.0 200,000 1

Sort (cost=65,758.96..66,258.96 rows=200,000 width=12) (actual time=465.588..487.413 rows=200,000 loops=1)

  • Sort Key: likes.video_id
  • Sort Method: external merge Disk: 5104kB
  • Buffers: shared hit=2044 read=1281, temp read=2015 written=2021
5. 92.934 391.255 ↑ 1.0 200,000 1

Hash Right Join (cost=36,736.02..44,729.82 rows=200,000 width=12) (actual time=231.604..391.255 rows=200,000 loops=1)

  • Hash Cond: (likes_1.user_id = likes.user_id)
  • Buffers: shared hit=2044 read=1281, temp read=1377 written=1380
6. 19.695 217.269 ↓ 1.2 126,618 1

Merge Left Join (cost=30,180.02..33,835.08 rows=105,349 width=12) (actual time=149.765..217.269 rows=126,618 loops=1)

  • Merge Cond: (likes_1.user_id = l2.user_id)
  • Buffers: shared hit=2044 read=7, temp read=443 written=446
7. 26.396 169.650 ↓ 1.2 126,618 1

Group (cost=26,128.98..27,628.98 rows=105,349 width=8) (actual time=121.516..169.650 rows=126,618 loops=1)

  • Group Key: users.user_id, likes_1.user_id
  • Buffers: shared hit=1274 read=4, temp read=443 written=446
8. 97.527 143.254 ↑ 1.0 199,999 1

Sort (cost=26,128.98..26,628.98 rows=199,999 width=8) (actual time=121.511..143.254 rows=199,999 loops=1)

  • Sort Key: likes_1.user_id
  • Sort Method: external merge Disk: 3544kB
  • Buffers: shared hit=1274 read=4, temp read=443 written=446
9. 26.718 45.727 ↑ 1.0 199,999 1

Nested Loop Left Join (cost=0.42..5,782.44 rows=199,999 width=8) (actual time=1.194..45.727 rows=199,999 loops=1)

  • Join Filter: (likes_1.user_id <> users.user_id)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=1274 read=4
10. 1.174 1.174 ↑ 1.0 1 1

Index Only Scan using users_pkey on users (cost=0.42..8.44 rows=1 width=4) (actual time=1.172..1.174 rows=1 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 1
  • Buffers: shared read=4
11. 17.835 17.835 ↑ 1.0 200,000 1

Seq Scan on likes likes_1 (cost=0.00..3,274.00 rows=200,000 width=4) (actual time=0.013..17.835 rows=200,000 loops=1)

  • Buffers: shared hit=1274
12. 0.011 27.924 ↑ 3.0 2 1

Materialize (cost=4,051.04..4,051.74 rows=6 width=12) (actual time=27.920..27.924 rows=2 loops=1)

  • Buffers: shared hit=770 read=3
13. 0.000 27.913 ↑ 3.0 2 1

Finalize GroupAggregate (cost=4,051.04..4,051.66 rows=6 width=16) (actual time=27.911..27.913 rows=2 loops=1)

  • Group Key: l1.user_id, l2.user_id
  • Buffers: shared hit=770 read=3
14. 0.000 27.961 ↑ 2.0 2 1

Gather Merge (cost=4,051.04..4,051.57 rows=4 width=16) (actual time=27.906..27.961 rows=2 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=1298 read=3
15. 0.020 43.324 ↑ 4.0 1 2

Partial GroupAggregate (cost=3,051.03..3,051.11 rows=4 width=16) (actual time=21.662..21.662 rows=1 loops=2)

  • Group Key: l1.user_id, l2.user_id
  • Buffers: shared hit=1298 read=3
16. 0.050 43.304 ↑ 4.0 1 2

Sort (cost=3,051.03..3,051.04 rows=4 width=12) (actual time=21.652..21.652 rows=1 loops=2)

  • Sort Key: l2.user_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1298 read=3
17. 17.638 43.254 ↑ 4.0 1 2

Hash Join (cost=12.25..3,050.99 rows=4 width=12) (actual time=13.543..21.627 rows=1 loops=2)

  • Hash Cond: (l2.video_id = l1.video_id)
  • Join Filter: (l1.user_id <> l2.user_id)
  • Rows Removed by Join Filter: 0
  • Buffers: shared hit=1291 read=3
18. 19.652 19.652 ↑ 1.2 100,000 2

Parallel Seq Scan on likes l2 (cost=0.00..2,450.47 rows=117,647 width=8) (actual time=0.011..9.826 rows=100,000 loops=2)

  • Buffers: shared hit=1274
19. 0.016 5.964 ↑ 2.0 1 2

Hash (cost=12.22..12.22 rows=2 width=8) (actual time=2.982..2.982 rows=1 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=6 read=3
20. 0.044 5.948 ↑ 2.0 1 2

Bitmap Heap Scan on likes l1 (cost=4.44..12.22 rows=2 width=8) (actual time=2.973..2.974 rows=1 loops=2)

  • Recheck Cond: (user_id = 1)
  • Heap Blocks: exact=1
  • Buffers: shared hit=6 read=3
21. 5.904 5.904 ↑ 2.0 1 2

Bitmap Index Scan on like_index (cost=0.00..4.43 rows=2 width=0) (actual time=2.952..2.952 rows=1 loops=2)

  • Index Cond: (user_id = 1)
  • Buffers: shared hit=4 read=3
22. 42.393 81.052 ↑ 1.0 200,000 1

Hash (cost=3,274.00..3,274.00 rows=200,000 width=8) (actual time=81.052..81.052 rows=200,000 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2973kB
  • Buffers: shared read=1274, temp written=512
23. 38.659 38.659 ↑ 1.0 200,000 1

Seq Scan on likes (cost=0.00..3,274.00 rows=200,000 width=8) (actual time=0.335..38.659 rows=200,000 loops=1)

  • Buffers: shared read=1274