explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Swfp

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

Limit (cost=7,286.81..7,286.83 rows=10 width=12) (actual time=85.331..85.337 rows=10 loops=1)

  • Buffers: shared hit=21 read=931
2. 14.324 85.331 ↑ 10,000.0 10 1

Sort (cost=7,286.81..7,536.81 rows=100,000 width=12) (actual time=85.330..85.331 rows=10 loops=1)

  • Sort Key: (COALESCE(count(likes.video_id), '0'::bigint)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=21 read=931
3. 26.577 71.007 ↑ 1.0 100,000 1

GroupAggregate (cost=118.49..5,125.84 rows=100,000 width=12) (actual time=3.341..71.007 rows=100,000 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=18 read=931
4. 13.856 44.430 ↓ 1.0 100,001 1

Merge Left Join (cost=118.49..3,625.84 rows=100,000 width=8) (actual time=3.333..44.430 rows=100,001 loops=1)

  • Merge Cond: (video.video_id = likes.video_id)
  • Buffers: shared hit=18 read=931
5. 27.295 27.295 ↑ 1.0 100,000 1

Index Only Scan using video_pkey on video (cost=0.29..3,257.48 rows=100,000 width=4) (actual time=0.049..27.295 rows=100,000 loops=1)

  • Heap Fetches: 100000
  • Buffers: shared hit=9 read=920
6. 0.015 3.279 ↑ 2.8 4 1

Sort (cost=118.20..118.23 rows=11 width=4) (actual time=3.277..3.279 rows=4 loops=1)

  • Sort Key: likes.video_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=9 read=11
7. 0.013 3.264 ↑ 2.8 4 1

Nested Loop (cost=48.90..118.01 rows=11 width=4) (actual time=2.798..3.264 rows=4 loops=1)

  • Buffers: shared hit=9 read=11
8. 0.007 2.321 ↑ 3.0 2 1

Unique (cost=44.46..44.49 rows=6 width=4) (actual time=2.318..2.321 rows=2 loops=1)

  • Buffers: shared hit=3 read=7
9. 0.026 2.314 ↑ 3.0 2 1

Sort (cost=44.46..44.48 rows=6 width=4) (actual time=2.313..2.314 rows=2 loops=1)

  • Sort Key: likes_1.user_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3 read=7
10. 0.015 2.288 ↑ 3.0 2 1

Nested Loop (cost=8.88..44.39 rows=6 width=4) (actual time=2.070..2.288 rows=2 loops=1)

  • Buffers: shared hit=3 read=7
11. 0.323 1.514 ↑ 2.0 1 1

Bitmap Heap Scan on likes likes_2 (cost=4.44..12.22 rows=2 width=4) (actual time=1.514..1.514 rows=1 loops=1)

  • Recheck Cond: (user_id = 1)
  • Heap Blocks: exact=1
  • Buffers: shared read=4
12. 1.191 1.191 ↑ 2.0 1 1

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

  • Index Cond: (user_id = 1)
  • Buffers: shared read=3
13. 0.454 0.759 ↑ 1.5 2 1

Bitmap Heap Scan on likes likes_1 (cost=4.44..16.05 rows=3 width=8) (actual time=0.542..0.759 rows=2 loops=1)

  • Recheck Cond: (video_id = likes_2.video_id)
  • Filter: ((user_id = 1) IS NOT TRUE)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=3
  • Buffers: shared hit=3 read=3
14. 0.305 0.305 ↑ 1.0 3 1

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

  • Index Cond: (video_id = likes_2.video_id)
  • Buffers: shared hit=2 read=1
15. 0.374 0.930 ↑ 1.0 2 2

Bitmap Heap Scan on likes (cost=4.44..12.22 rows=2 width=8) (actual time=0.368..0.465 rows=2 loops=2)

  • Recheck Cond: (user_id = likes_1.user_id)
  • Heap Blocks: exact=4
  • Buffers: shared hit=6 read=4
16. 0.556 0.556 ↑ 1.0 2 2

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

  • Index Cond: (user_id = likes_1.user_id)
  • Buffers: shared hit=4 read=2
Planning time : 8.385 ms