explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rH2

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 495.685 ↑ 1.0 10 1

Limit (cost=65,466.90..65,466.92 rows=10 width=12) (actual time=495.678..495.685 rows=10 loops=1)

  • Buffers: shared hit=1285 read=1280, temp read=2015 written=2021
2. 8.965 495.679 ↑ 7,007.4 10 1

Sort (cost=65,466.90..65,642.08 rows=70,074 width=12) (actual time=495.678..495.679 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=1285 read=1280, temp read=2015 written=2021
3. 30.715 486.714 ↓ 1.2 86,591 1

GroupAggregate (cost=61,751.88..63,952.62 rows=70,074 width=12) (actual time=433.917..486.714 rows=86,591 loops=1)

  • Group Key: likes.video_id
  • Buffers: shared hit=1282 read=1280, temp read=2015 written=2021
4. 95.599 455.999 ↑ 1.0 200,000 1

Sort (cost=61,751.88..62,251.88 rows=200,000 width=12) (actual time=433.909..455.999 rows=200,000 loops=1)

  • Sort Key: likes.video_id
  • Sort Method: external merge Disk: 5104kB
  • Buffers: shared hit=1282 read=1280, temp read=2015 written=2021
5. 95.181 360.400 ↑ 1.0 200,000 1

Hash Right Join (cost=32,729.45..40,722.74 rows=200,000 width=12) (actual time=198.481..360.400 rows=200,000 loops=1)

  • Hash Cond: (likes_1.user_id = likes.user_id)
  • Buffers: shared hit=1282 read=1280, temp read=1377 written=1380
6. 19.138 181.494 ↓ 1.2 126,618 1

Merge Left Join (cost=26,173.45..29,828.00 rows=105,349 width=12) (actual time=113.948..181.494 rows=126,618 loops=1)

  • Merge Cond: (likes_1.user_id = l2.user_id)
  • Buffers: shared hit=1280 read=8, temp read=443 written=446
7. 26.942 161.479 ↓ 1.2 126,618 1

Group (cost=26,128.98..27,628.98 rows=105,349 width=8) (actual time=112.792..161.479 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. 90.103 134.537 ↑ 1.0 199,999 1

Sort (cost=26,128.98..26,628.98 rows=199,999 width=8) (actual time=112.788..134.537 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.320 44.434 ↑ 1.0 199,999 1

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

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

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

  • Buffers: shared hit=1274
12. 0.006 0.877 ↑ 3.0 2 1

Materialize (cost=44.46..44.66 rows=6 width=12) (actual time=0.873..0.877 rows=2 loops=1)

  • Buffers: shared hit=6 read=4
13. 0.010 0.871 ↑ 3.0 2 1

GroupAggregate (cost=44.46..44.58 rows=6 width=16) (actual time=0.868..0.871 rows=2 loops=1)

  • Group Key: l1.user_id, l2.user_id
  • Buffers: shared hit=6 read=4
14. 0.006 0.861 ↑ 3.0 2 1

Sort (cost=44.46..44.48 rows=6 width=12) (actual time=0.861..0.861 rows=2 loops=1)

  • Sort Key: l2.user_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=6 read=4
15. 0.010 0.855 ↑ 3.0 2 1

Nested Loop (cost=8.88..44.39 rows=6 width=12) (actual time=0.852..0.855 rows=2 loops=1)

  • Buffers: shared hit=6 read=4
16. 0.006 0.672 ↑ 2.0 1 1

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

  • Recheck Cond: (user_id = 1)
  • Heap Blocks: exact=1
  • Buffers: shared hit=1 read=3
17. 0.666 0.666 ↑ 2.0 1 1

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

  • Index Cond: (user_id = 1)
  • Buffers: shared read=3
18. 0.010 0.173 ↑ 1.5 2 1

Bitmap Heap Scan on likes l2 (cost=4.44..16.05 rows=3 width=8) (actual time=0.170..0.173 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
19. 0.163 0.163 ↑ 1.0 3 1

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

  • Index Cond: (video_id = l1.video_id)
  • Buffers: shared hit=2 read=1
20. 41.711 83.725 ↑ 1.0 200,000 1

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

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

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

  • Buffers: shared hit=2 read=1272
Planning time : 5.985 ms