explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KiA7

Settings
# exclusive inclusive rows x rows loops node
1. 0.063 215.101 ↑ 1.0 10 1

Limit (cost=15,260.32..15,260.35 rows=10 width=12) (actual time=215.036..215.101 rows=10 loops=1)

  • Buffers: shared hit=15 read=3577
2. 11.916 215.038 ↑ 10,000.0 10 1

Sort (cost=15,260.32..15,510.32 rows=100,000 width=12) (actual time=215.035..215.038 rows=10 loops=1)

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

GroupAggregate (cost=8,092.10..13,099.36 rows=100,000 width=12) (actual time=131.925..203.122 rows=100,000 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=12 read=3577
4. 13.556 174.795 ↑ 1.0 100,000 1

Merge Left Join (cost=8,092.10..11,599.36 rows=100,000 width=8) (actual time=131.900..174.795 rows=100,000 loops=1)

  • Merge Cond: (video.video_id = likes.video_id)
  • Buffers: shared hit=12 read=3577
5. 29.379 29.379 ↑ 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.032..29.379 rows=100,000 loops=1)

  • Heap Fetches: 100000
  • Buffers: shared hit=9 read=920
6. 0.000 131.860 ↑ 5.0 1 1

Sort (cost=8,091.81..8,091.82 rows=5 width=4) (actual time=131.860..131.860 rows=1 loops=1)

  • Sort Key: likes.video_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3 read=2657
7. 12.281 131.904 ↑ 5.0 1 1

Gather (cost=1,004.44..8,091.75 rows=5 width=4) (actual time=131.771..131.904 rows=1 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=3 read=2657
8. 0.000 119.623 ↓ 0.0 0 2

Nested Loop (cost=4.44..7,091.25 rows=3 width=4) (actual time=70.894..119.623 rows=0 loops=2)

  • Buffers: shared hit=3 read=2657
9. 119.454 119.454 ↓ 0.0 0 2

Parallel Seq Scan on friend (cost=0.00..7,066.76 rows=2 width=4) (actual time=70.726..119.454 rows=0 loops=2)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 300000
  • Buffers: shared read=2655
10. 0.127 0.320 ↑ 2.0 1 1

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

  • Recheck Cond: (user_id = friend.friend_id)
  • Buffers: shared hit=3 read=2
11. 0.193 0.193 ↑ 2.0 1 1

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

  • Index Cond: (user_id = friend.friend_id)
  • Buffers: shared hit=3 read=1