explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qYz3

Settings
# exclusive inclusive rows x rows loops node
1. 3.769 410.969 ↑ 1.0 10 1

Limit (cost=33,949.92..33,949.95 rows=10 width=12) (actual time=407.199..410.969 rows=10 loops=1)

  • Buffers: shared hit=290 read=2194, temp read=443 written=445
2. 10.515 407.200 ↑ 10,000.0 10 1

Sort (cost=33,949.92..34,199.92 rows=100,000 width=12) (actual time=407.199..407.200 rows=10 loops=1)

  • Sort Key: (COALESCE(count(likes.video_id), '0'::bigint)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=126 read=1148, temp read=221 written=222
3. 19.552 396.685 ↑ 1.2 86,591 1

Finalize GroupAggregate (cost=17,156.61..31,788.96 rows=100,000 width=12) (actual time=329.103..396.685 rows=86,591 loops=1)

  • Group Key: video.video_id
  • Buffers: shared hit=123 read=1148, temp read=221 written=222
4. 0.000 377.133 ↓ 1.3 126,360 1

Gather Merge (cost=17,156.61..30,288.96 rows=100,000 width=12) (actual time=329.096..377.133 rows=126,360 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=287 read=2194, temp read=443 written=445
5. 35.836 703.738 ↑ 1.6 63,180 2

Partial GroupAggregate (cost=16,156.60..18,038.95 rows=100,000 width=12) (actual time=324.341..351.869 rows=63,180 loops=2)

  • Group Key: video.video_id
  • Buffers: shared hit=287 read=2194, temp read=443 written=445
6. 148.380 667.902 ↑ 1.2 99,999 2

Sort (cost=16,156.60..16,450.72 rows=117,646 width=8) (actual time=324.330..333.951 rows=99,999 loops=2)

  • Sort Key: video.video_id
  • Sort Method: external merge Disk: 1768kB
  • Worker 0: Sort Method: external merge Disk: 1776kB
  • Buffers: shared hit=287 read=2194, temp read=443 written=445
7. 91.286 519.522 ↑ 1.2 99,999 2

Parallel Hash Join (cost=3,194.98..6,248.40 rows=117,646 width=8) (actual time=65.033..259.761 rows=99,999 loops=2)

  • Hash Cond: (likes.video_id = video.video_id)
  • Buffers: shared hit=260 read=2194
8. 299.428 299.428 ↑ 1.2 99,999 2

Parallel Seq Scan on likes (cost=0.00..2,744.59 rows=117,646 width=4) (actual time=0.256..149.714 rows=99,999 loops=2)

  • Filter: (user_id <> 10)
  • Rows Removed by Filter: 1
  • Buffers: shared read=1274
9. 25.116 128.808 ↓ 1.2 50,000 2

Parallel Hash (cost=2,674.14..2,674.14 rows=41,667 width=4) (actual time=64.404..64.404 rows=50,000 loops=2)

  • Buckets: 131072 Batches: 1 Memory Usage: 4992kB
  • Buffers: shared hit=249 read=920
10. 103.692 103.692 ↓ 1.2 50,000 2

Parallel Index Only Scan using video_pkey on video (cost=0.29..2,674.14 rows=41,667 width=4) (actual time=0.053..51.846 rows=50,000 loops=2)

  • Heap Fetches: 100000
  • Buffers: shared hit=249 read=920