explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VOPQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.058 254.384 ↑ 1.0 10 1

Limit (cost=57,354.40..57,354.43 rows=10 width=12) (actual time=254.325..254.384 rows=10 loops=1)

  • Buffers: shared hit=2667 read=4849
2. 11.070 254.326 ↑ 10,000.0 10 1

Sort (cost=57,354.40..57,604.40 rows=100,000 width=12) (actual time=254.325..254.326 rows=10 loops=1)

  • Sort Key: (COALESCE(count(friend_likes.video_id), '0'::bigint)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=2667 read=4849
3. 26.797 243.256 ↑ 1.0 100,000 1

GroupAggregate (cost=50,185.34..55,193.44 rows=100,000 width=12) (actual time=176.837..243.256 rows=100,000 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=2664 read=4849
4. 13.052 216.459 ↑ 1.0 100,000 1

Merge Left Join (cost=50,185.34..53,693.44 rows=100,000 width=8) (actual time=176.824..216.459 rows=100,000 loops=1)

  • Merge Cond: (video.video_id = friend_likes.video_id)
  • Buffers: shared hit=2664 read=4849
5. 26.645 26.645 ↑ 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.055..26.645 rows=100,000 loops=1)

  • Heap Fetches: 100000
  • Buffers: shared hit=9 read=920
6. 0.012 176.762 ↑ 20.3 3 1

Sort (cost=50,185.05..50,185.20 rows=61 width=4) (actual time=176.761..176.762 rows=3 loops=1)

  • Sort Key: friend_likes.video_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2655 read=3929
7. 0.001 176.750 ↑ 20.3 3 1

Subquery Scan on friend_likes (cost=50,182.48..50,183.24 rows=61 width=4) (actual time=176.748..176.750 rows=3 loops=1)

  • Buffers: shared hit=2655 read=3929
8. 0.017 176.749 ↑ 20.3 3 1

Sort (cost=50,182.48..50,182.63 rows=61 width=24) (actual time=176.747..176.749 rows=3 loops=1)

  • Sort Key: likes.user_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2655 read=3929
9. 16.355 176.732 ↑ 20.3 3 1

Hash Join (cost=46,381.64..50,180.67 rows=61 width=24) (actual time=154.507..176.732 rows=3 loops=1)

  • Hash Cond: (likes.user_id = friend.friend_id)
  • Buffers: shared hit=2655 read=3929
10. 28.854 28.854 ↑ 1.0 200,000 1

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

  • Buffers: shared read=1274
11. 0.009 131.523 ↑ 10.7 3 1

Hash (cost=46,381.24..46,381.24 rows=32 width=4) (actual time=131.523..131.523 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2655 read=2655
12. 0.005 131.514 ↑ 10.7 3 1

Unique (cost=46,380.76..46,380.92 rows=32 width=4) (actual time=131.510..131.514 rows=3 loops=1)

  • Buffers: shared hit=2655 read=2655
13. 1.808 131.509 ↑ 10.7 3 1

Sort (cost=46,380.76..46,380.84 rows=32 width=4) (actual time=131.508..131.509 rows=3 loops=1)

  • Sort Key: friend.friend_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2655 read=2655
14. 37.271 129.701 ↑ 10.7 3 1

Gather (cost=1,000.00..46,379.96 rows=32 width=4) (actual time=51.731..129.701 rows=3 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=2655 read=2655
15. 0.000 92.430 ↑ 9.5 2 2

Nested Loop (cost=0.00..45,376.76 rows=19 width=4) (actual time=53.474..92.430 rows=2 loops=2)

  • Join Filter: (((friend.user_id = friend_1.friend_id) AND ((friend.friend_id = 1) IS NOT TRUE)) OR (friend.user_id = 1))
  • Rows Removed by Join Filter: 299998
  • Buffers: shared hit=2655 read=2655
16. 39.087 39.087 ↓ 0.0 0 2

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

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 300000
  • Buffers: shared read=2655
17. 56.146 56.146 ↑ 1.0 600,000 1

Seq Scan on friend (cost=0.00..8,655.00 rows=600,000 width=8) (actual time=0.011..56.146 rows=600,000 loops=1)

  • Buffers: shared hit=2655