explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VKqo

Settings
# exclusive inclusive rows x rows loops node
1. 0.116 191.724 ↑ 1.0 10 1

Limit (cost=17,899.04..17,899.07 rows=10 width=12) (actual time=191.610..191.724 rows=10 loops=1)

  • Buffers: shared hit=3545 read=1271
2. 13.131 191.608 ↑ 10,000.0 10 1

Sort (cost=17,899.04..18,149.04 rows=100,000 width=12) (actual time=191.608..191.608 rows=10 loops=1)

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

GroupAggregate (cost=10,730.73..15,738.08 rows=100,000 width=12) (actual time=122.245..178.477 rows=100,000 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=3542 read=1271
4. 12.837 154.257 ↓ 1.0 100,001 1

Merge Left Join (cost=10,730.73..14,238.08 rows=100,000 width=8) (actual time=122.230..154.257 rows=100,001 loops=1)

  • Merge Cond: (video.video_id = likes.video_id)
  • Buffers: shared hit=3542 read=1271
5. 19.225 19.225 ↑ 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.029..19.225 rows=100,000 loops=1)

  • Heap Fetches: 100000
  • Buffers: shared hit=929
6. 0.016 122.195 ↑ 2.8 4 1

Sort (cost=10,730.44..10,730.46 rows=11 width=4) (actual time=122.193..122.195 rows=4 loops=1)

  • Sort Key: likes.video_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2613 read=1271
7. 15.968 122.179 ↑ 2.8 4 1

Hash Join (cost=6,931.22..10,730.25 rows=11 width=4) (actual time=99.372..122.179 rows=4 loops=1)

  • Hash Cond: (likes.user_id = likes_1.user_id)
  • Buffers: shared hit=2613 read=1271
8. 22.006 22.006 ↑ 1.0 200,000 1

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

  • Buffers: shared hit=1273 read=1
9. 0.011 84.205 ↑ 3.0 2 1

Hash (cost=6,931.15..6,931.15 rows=6 width=4) (actual time=84.205..84.205 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1340 read=1270
10. 0.003 84.194 ↑ 3.0 2 1

Unique (cost=6,931.06..6,931.09 rows=6 width=4) (actual time=84.191..84.194 rows=2 loops=1)

  • Buffers: shared hit=1340 read=1270
11. 0.000 84.191 ↑ 3.0 2 1

Sort (cost=6,931.06..6,931.07 rows=6 width=4) (actual time=84.191..84.191 rows=2 loops=1)

  • Sort Key: likes_1.user_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1340 read=1270
12. 7.317 84.274 ↑ 3.0 2 1

Gather (cost=3,744.60..6,930.98 rows=6 width=4) (actual time=84.047..84.274 rows=2 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=1340 read=1270
13. 10.211 76.957 ↑ 4.0 1 2

Parallel Hash Join (cost=2,744.60..5,930.38 rows=4 width=4) (actual time=70.258..76.957 rows=1 loops=2)

  • Hash Cond: (likes_1.video_id = likes_2.video_id)
  • Buffers: shared hit=1340 read=1270
14. 13.757 13.757 ↑ 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.011..13.757 rows=100,000 loops=2)

  • Filter: ((user_id = 1) IS NOT TRUE)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1274
15. 0.071 52.989 ↓ 0.0 0 2

Parallel Hash (cost=2,744.59..2,744.59 rows=1 width=4) (actual time=52.989..52.989 rows=0 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
  • Buffers: shared hit=4 read=1270
16. 52.918 52.918 ↓ 0.0 0 2

Parallel Seq Scan on likes likes_2 (cost=0.00..2,744.59 rows=1 width=4) (actual time=44.089..52.918 rows=0 loops=2)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 100000
  • Buffers: shared hit=4 read=1270
Planning time : 0.661 ms