explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MmVx

Settings
# exclusive inclusive rows x rows loops node
1. 0.230 157.039 ↑ 1.0 10 1

Limit (cost=11,588.12..11,588.14 rows=10 width=12) (actual time=156.809..157.039 rows=10 loops=1)

  • Buffers: shared hit=68 read=2199
2. 13.825 156.809 ↑ 10,000.0 10 1

Sort (cost=11,588.12..11,838.12 rows=100,000 width=12) (actual time=156.809..156.809 rows=10 loops=1)

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

GroupAggregate (cost=4,419.80..9,427.15 rows=100,000 width=12) (actual time=76.809..142.984 rows=100,000 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=65 read=2199
4. 13.747 117.020 ↓ 1.0 100,001 1

Merge Left Join (cost=4,419.80..7,927.15 rows=100,000 width=8) (actual time=76.800..117.020 rows=100,001 loops=1)

  • Merge Cond: (video.video_id = likes.video_id)
  • Buffers: shared hit=65 read=2199
5. 26.512 26.512 ↑ 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.035..26.512 rows=100,000 loops=1)

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

Sort (cost=4,419.51..4,419.54 rows=11 width=4) (actual time=76.758..76.761 rows=4 loops=1)

  • Sort Key: likes.video_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=56 read=1279
7. 0.017 76.747 ↑ 2.8 4 1

Nested Loop (cost=4,350.21..4,419.32 rows=11 width=4) (actual time=76.515..76.747 rows=4 loops=1)

  • Buffers: shared hit=56 read=1279
8. 0.001 76.264 ↑ 3.0 2 1

Unique (cost=4,345.77..4,345.80 rows=6 width=4) (actual time=76.261..76.264 rows=2 loops=1)

  • Buffers: shared hit=48 read=1277
9. 0.000 76.263 ↑ 3.0 2 1

Sort (cost=4,345.77..4,345.79 rows=6 width=4) (actual time=76.261..76.263 rows=2 loops=1)

  • Sort Key: likes_1.user_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=48 read=1277
10. 4.525 76.348 ↑ 3.0 2 1

Gather (cost=1,012.25..4,345.70 rows=6 width=4) (actual time=32.681..76.348 rows=2 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=48 read=1277
11. 9.663 71.823 ↑ 4.0 1 2

Hash Join (cost=12.25..3,345.10 rows=4 width=4) (actual time=50.047..71.823 rows=1 loops=2)

  • Hash Cond: (likes_1.video_id = likes_2.video_id)
  • Buffers: shared hit=48 read=1277
12. 61.753 61.753 ↑ 1.2 100,000 2

Parallel Seq Scan on likes likes_1 (cost=0.00..2,744.59 rows=117,646 width=8) (actual time=0.133..61.753 rows=100,000 loops=2)

  • Filter: ((user_id = 1) IS NOT TRUE)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1 read=1273
13. 0.008 0.407 ↑ 2.0 1 2

Hash (cost=12.22..12.22 rows=2 width=4) (actual time=0.406..0.407 rows=1 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=5 read=4
14. 0.079 0.399 ↑ 2.0 1 2

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

  • Recheck Cond: (user_id = 1)
  • Heap Blocks: exact=1
  • Buffers: shared hit=5 read=4
15. 0.320 0.320 ↑ 2.0 1 2

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

  • Index Cond: (user_id = 1)
  • Buffers: shared hit=4 read=3
16. 0.010 0.466 ↑ 1.0 2 2

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

  • Recheck Cond: (user_id = likes_1.user_id)
  • Heap Blocks: exact=4
  • Buffers: shared hit=8 read=2
17. 0.456 0.456 ↑ 1.0 2 2

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

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