explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9pil

Settings
# exclusive inclusive rows x rows loops node
1. 0.067 507.999 ↑ 1.0 10 1

Limit (cost=69,183.65..69,183.68 rows=10 width=12) (actual time=507.933..507.999 rows=10 loops=1)

  • Buffers: shared hit=2565 read=1277, temp read=2015 written=2021
2. 9.613 507.932 ↑ 7,007.4 10 1

Sort (cost=69,183.65..69,358.84 rows=70,074 width=12) (actual time=507.931..507.932 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=2498 read=1277, temp read=2015 written=2021
3. 32.812 498.319 ↓ 1.2 86,591 1

GroupAggregate (cost=65,468.64..67,669.38 rows=70,074 width=12) (actual time=441.865..498.319 rows=86,591 loops=1)

  • Group Key: likes.video_id
  • Buffers: shared hit=2495 read=1277, temp read=2015 written=2021
4. 98.164 465.507 ↑ 1.0 200,000 1

Sort (cost=65,468.64..65,968.64 rows=200,000 width=12) (actual time=441.857..465.507 rows=200,000 loops=1)

  • Sort Key: likes.video_id
  • Sort Method: external merge Disk: 5104kB
  • Buffers: shared hit=2495 read=1277, temp read=2015 written=2021
5. 93.023 367.343 ↑ 1.0 200,000 1

Hash Right Join (cost=36,445.70..44,439.50 rows=200,000 width=12) (actual time=207.545..367.343 rows=200,000 loops=1)

  • Hash Cond: (likes_1.user_id = likes.user_id)
  • Buffers: shared hit=2495 read=1277, temp read=1377 written=1380
6. 19.413 191.414 ↓ 1.2 126,618 1

Merge Left Join (cost=29,889.70..33,544.75 rows=105,349 width=12) (actual time=123.702..191.414 rows=126,618 loops=1)

  • Merge Cond: (likes_1.user_id = l2.user_id)
  • Buffers: shared hit=2493 read=5, temp read=443 written=446
7. 26.881 158.177 ↓ 1.2 126,618 1

Group (cost=26,128.98..27,628.98 rows=105,349 width=8) (actual time=109.323..158.177 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. 88.963 131.296 ↑ 1.0 199,999 1

Sort (cost=26,128.98..26,628.98 rows=199,999 width=8) (actual time=109.319..131.296 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. 25.505 42.333 ↑ 1.0 199,999 1

Nested Loop Left Join (cost=0.42..5,782.44 rows=199,999 width=8) (actual time=0.830..42.333 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. 0.815 0.815 ↑ 1.0 1 1

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

  • Index Cond: (user_id = 1)
  • Heap Fetches: 1
  • Buffers: shared read=4
11. 16.013 16.013 ↑ 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.009..16.013 rows=200,000 loops=1)

  • Buffers: shared hit=1274
12. 0.009 13.824 ↑ 3.0 2 1

Materialize (cost=3,760.72..3,761.41 rows=6 width=12) (actual time=13.820..13.824 rows=2 loops=1)

  • Buffers: shared hit=1219 read=1
13. 0.000 13.815 ↑ 3.0 2 1

Finalize GroupAggregate (cost=3,760.72..3,761.34 rows=6 width=16) (actual time=13.812..13.815 rows=2 loops=1)

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

Gather Merge (cost=3,760.72..3,761.25 rows=4 width=16) (actual time=13.798..13.860 rows=2 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=1286 read=1
15. 0.022 14.374 ↑ 4.0 1 2

Partial GroupAggregate (cost=2,760.71..2,760.79 rows=4 width=16) (actual time=7.186..7.187 rows=1 loops=2)

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

Sort (cost=2,760.71..2,760.72 rows=4 width=12) (actual time=7.176..7.176 rows=1 loops=2)

  • Sort Key: l2.user_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1286 read=1
17. 0.029 14.302 ↑ 4.0 1 2

Nested Loop (cost=4.44..2,760.67 rows=4 width=12) (actual time=5.764..7.151 rows=1 loops=2)

  • Buffers: shared hit=1279 read=1
18. 14.048 14.048 ↓ 0.0 0 2

Parallel Seq Scan on likes l1 (cost=0.00..2,744.59 rows=1 width=8) (actual time=5.640..7.024 rows=0 loops=2)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 100000
  • Buffers: shared hit=1274
19. 0.010 0.225 ↑ 1.5 2 1

Bitmap Heap Scan on likes l2 (cost=4.44..16.05 rows=3 width=8) (actual time=0.223..0.225 rows=2 loops=1)

  • Recheck Cond: (video_id = l1.video_id)
  • Filter: (l1.user_id <> user_id)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=3
  • Buffers: shared hit=5 read=1
20. 0.215 0.215 ↑ 1.0 3 1

Bitmap Index Scan on like_index (cost=0.00..4.44 rows=3 width=0) (actual time=0.214..0.215 rows=3 loops=1)

  • Index Cond: (video_id = l1.video_id)
  • Buffers: shared hit=2 read=1
21. 42.904 82.906 ↑ 1.0 200,000 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 2973kB
  • Buffers: shared hit=2 read=1272, temp written=512
22. 40.002 40.002 ↑ 1.0 200,000 1

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

  • Buffers: shared hit=2 read=1272