explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S4a

Settings
# exclusive inclusive rows x rows loops node
1. 0.104 538.470 ↑ 1.0 10 1

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

  • Buffers: shared hit=3863 read=1278, temp read=2015 written=2021
2. 9.282 538.366 ↑ 7,007.4 10 1

Sort (cost=72,059.26..72,234.44 rows=70,074 width=12) (actual time=538.365..538.366 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=3227 read=1278, temp read=2015 written=2021
3. 31.781 529.084 ↓ 1.2 86,591 1

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

  • Group Key: likes.video_id
  • Buffers: shared hit=3224 read=1278, temp read=2015 written=2021
4. 97.846 497.303 ↑ 1.0 200,000 1

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

  • Sort Key: likes.video_id
  • Sort Method: external merge Disk: 5104kB
  • Buffers: shared hit=3224 read=1278, temp read=2015 written=2021
5. 93.707 399.457 ↑ 1.0 200,000 1

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

  • Hash Cond: (likes_1.user_id = likes.user_id)
  • Buffers: shared hit=3224 read=1278, temp read=1377 written=1380
6. 19.415 207.825 ↓ 1.2 126,618 1

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

  • Merge Cond: (likes_1.user_id = l2.user_id)
  • Buffers: shared hit=3224 read=4, temp read=443 written=446
7. 26.600 159.318 ↓ 1.2 126,618 1

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

  • Group Key: users.user_id, likes_1.user_id
  • Buffers: shared hit=1274 read=4, temp read=443 written=446
8. 89.712 132.718 ↑ 1.0 199,999 1

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

  • Sort Key: likes_1.user_id
  • Sort Method: external merge Disk: 3544kB
  • Buffers: shared hit=1274 read=4, temp read=443 written=446
9. 25.513 43.006 ↑ 1.0 199,999 1

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

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

Index Only Scan using users_pkey on users (cost=0.42..8.44 rows=1 width=4) (actual time=1.625..1.627 rows=1 loops=1)

  • Index Cond: (user_id = 1)
  • Heap Fetches: 1
  • Buffers: shared read=4
11. 15.866 15.866 ↑ 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.013..15.866 rows=200,000 loops=1)

  • Buffers: shared hit=1274
12. 0.006 29.092 ↑ 3.0 2 1

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

  • Buffers: shared hit=1950
13. 0.000 29.086 ↑ 3.0 2 1

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

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

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

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

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

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

Sort (cost=5,636.31..5,636.32 rows=4 width=12) (actual time=23.197..23.198 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. 16.496 46.312 ↑ 4.0 1 2

Parallel Hash Join (cost=2,744.60..5,636.27 rows=4 width=12) (actual time=17.887..23.156 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. 17.926 17.926 ↑ 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.006..8.963 rows=100,000 loops=2)

  • Buffers: shared hit=1274
19. 0.032 11.890 ↓ 0.0 0 2

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

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

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

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

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

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

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

  • Buffers: shared read=1274