explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cPZt

Settings
# exclusive inclusive rows x rows loops node
1. 0.101 533.018 ↑ 1.0 10 1

Limit (cost=72,059.26..72,059.28 rows=10 width=12) (actual time=532.916..533.018 rows=10 loops=1)

  • Buffers: shared hit=5138 read=3, temp read=2015 written=2021
2. 9.100 532.917 ↑ 7,007.4 10 1

Sort (cost=72,059.26..72,234.44 rows=70,074 width=12) (actual time=532.916..532.917 rows=10 loops=1)

  • Sort Key: (sum(COALESCE((log(((1 + COALESCE((count(l1.video_id)), '0'::bigint)))::double precision)), '0'::double precision))) DESC
  • Sort Method: top-N heapsort Memory: 25kB
  • Buffers: shared hit=4164 read=3, temp read=2015 written=2021
3. 30.839 523.817 ↓ 1.2 86,591 1

GroupAggregate (cost=68,344.24..70,544.98 rows=70,074 width=12) (actual time=470.798..523.817 rows=86,591 loops=1)

  • Group Key: likes.video_id
  • Buffers: shared hit=4161 read=3, temp read=2015 written=2021
4. 108.300 492.978 ↑ 1.0 200,000 1

Sort (cost=68,344.24..68,844.24 rows=200,000 width=12) (actual time=470.789..492.978 rows=200,000 loops=1)

  • Sort Key: likes.video_id
  • Sort Method: external merge Disk: 5104kB
  • Buffers: shared hit=4161 read=3, temp read=2015 written=2021
5. 105.139 384.678 ↑ 1.0 200,000 1

Hash Right Join (cost=39,321.31..47,315.10 rows=200,000 width=12) (actual time=209.137..384.678 rows=200,000 loops=1)

  • Hash Cond: (likes_1.user_id = likes.user_id)
  • Buffers: shared hit=4161 read=3, temp read=1377 written=1380
6. 19.954 206.271 ↓ 1.2 126,618 1

Merge Left Join (cost=32,765.31..36,420.36 rows=105,349 width=12) (actual time=135.014..206.271 rows=126,618 loops=1)

  • Merge Cond: (likes_1.user_id = l2.user_id)
  • Buffers: shared hit=2887 read=3, temp read=443 written=446
7. 28.364 158.592 ↓ 1.2 126,618 1

Group (cost=26,128.98..27,628.98 rows=105,349 width=8) (actual time=107.227..158.592 rows=126,618 loops=1)

  • Group Key: users.user_id, likes_1.user_id
  • Buffers: shared hit=1275 read=3, temp read=443 written=446
8. 88.419 130.228 ↑ 1.0 199,999 1

Sort (cost=26,128.98..26,628.98 rows=199,999 width=8) (actual time=107.223..130.228 rows=199,999 loops=1)

  • Sort Key: likes_1.user_id
  • Sort Method: external merge Disk: 3544kB
  • Buffers: shared hit=1275 read=3, temp read=443 written=446
9. 25.438 41.809 ↑ 1.0 199,999 1

Nested Loop Left Join (cost=0.42..5,782.44 rows=199,999 width=8) (actual time=0.082..41.809 rows=199,999 loops=1)

  • Join Filter: (likes_1.user_id <> users.user_id)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=1275 read=3
10. 0.072 0.072 ↑ 1.0 1 1

Index Only Scan using user_index on users (cost=0.42..8.44 rows=1 width=4) (actual time=0.070..0.072 rows=1 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 1
  • Buffers: shared hit=1 read=3
11. 16.299 16.299 ↑ 1.0 200,000 1

Seq Scan on likes likes_1 (cost=0.00..3,274.00 rows=200,000 width=4) (actual time=0.007..16.299 rows=200,000 loops=1)

  • Buffers: shared hit=1274
12. 0.005 27.725 ↑ 3.0 2 1

Materialize (cost=6,636.32..6,637.02 rows=6 width=12) (actual time=27.721..27.725 rows=2 loops=1)

  • Buffers: shared hit=1612
13. 0.000 27.720 ↑ 3.0 2 1

Finalize GroupAggregate (cost=6,636.32..6,636.94 rows=6 width=16) (actual time=27.716..27.720 rows=2 loops=1)

  • Group Key: l1.user_id, l2.user_id
  • Buffers: shared hit=1612
14. 0.000 27.804 ↑ 2.0 2 1

Gather Merge (cost=6,636.32..6,636.85 rows=4 width=16) (actual time=27.711..27.804 rows=2 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=2586
15. 0.022 50.928 ↑ 4.0 1 2

Partial GroupAggregate (cost=5,636.31..5,636.39 rows=4 width=16) (actual time=25.464..25.464 rows=1 loops=2)

  • Group Key: l1.user_id, l2.user_id
  • Buffers: shared hit=2586
16. 0.086 50.906 ↑ 4.0 1 2

Sort (cost=5,636.31..5,636.32 rows=4 width=12) (actual time=25.453..25.453 rows=1 loops=2)

  • Sort Key: l2.user_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2586
17. 18.912 50.820 ↑ 4.0 1 2

Parallel Hash Join (cost=2,744.60..5,636.27 rows=4 width=12) (actual time=15.563..25.410 rows=1 loops=2)

  • Hash Cond: (l2.video_id = l1.video_id)
  • Join Filter: (l1.user_id <> l2.user_id)
  • Rows Removed by Join Filter: 0
  • Buffers: shared hit=2559
18. 18.888 18.888 ↑ 1.2 100,000 2

Parallel Seq Scan on likes l2 (cost=0.00..2,450.47 rows=117,647 width=8) (actual time=0.007..9.444 rows=100,000 loops=2)

  • Buffers: shared hit=1274
19. 0.066 13.020 ↓ 0.0 0 2

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

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
  • Buffers: shared hit=1274
20. 12.954 12.954 ↓ 0.0 0 2

Parallel Seq Scan on likes l1 (cost=0.00..2,744.59 rows=1 width=8) (actual time=5.707..6.477 rows=0 loops=2)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 100000
  • Buffers: shared hit=1274
21. 42.425 73.268 ↑ 1.0 200,000 1

Hash (cost=3,274.00..3,274.00 rows=200,000 width=8) (actual time=73.267..73.268 rows=200,000 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2973kB
  • Buffers: shared hit=1274, temp written=512
22. 30.843 30.843 ↑ 1.0 200,000 1

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

  • Buffers: shared hit=1274