explain.depesz.com

PostgreSQL's explain analyze made readable

Result: O7yS

Settings
# exclusive inclusive rows x rows loops node
1. 0.217 227.358 ↑ 35.5 47 1

Subquery Scan on t (cost=26,701.96..26,851.96 rows=1,667 width=12) (actual time=224.590..227.358 rows=47 loops=1)

  • Filter: (t.rk <= 10)
  • Rows Removed by Filter: 4953
2. 2.396 227.141 ↑ 1.0 5,000 1

WindowAgg (cost=26,701.96..26,789.46 rows=5,000 width=20) (actual time=224.589..227.141 rows=5,000 loops=1)

3. 1.073 224.745 ↑ 1.0 5,000 1

Sort (cost=26,701.96..26,714.46 rows=5,000 width=12) (actual time=224.569..224.745 rows=5,000 loops=1)

  • Sort Key: (COALESCE((count(l.video_id)), '0'::bigint)) DESC
  • Sort Method: quicksort Memory: 427kB
4. 1.579 223.672 ↑ 1.0 5,000 1

Hash Right Join (cost=25,299.81..26,394.76 rows=5,000 width=12) (actual time=220.313..223.672 rows=5,000 loops=1)

  • Hash Cond: (l.video_id = v.video_id)
5. 0.000 220.803 ↑ 2.9 1,718 1

Finalize GroupAggregate (cost=25,154.31..26,186.15 rows=4,998 width=12) (actual time=218.988..220.803 rows=1,718 loops=1)

  • Group Key: l.video_id
6. 0.000 236.806 ↑ 3.8 1,971 1

Gather Merge (cost=25,154.31..26,098.53 rows=7,528 width=12) (actual time=218.981..236.806 rows=1,971 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 0.996 448.518 ↑ 5.7 657 3

Partial GroupAggregate (cost=24,154.29..24,229.59 rows=3,764 width=12) (actual time=149.023..149.506 rows=657 loops=3)

  • Group Key: l.video_id
8. 0.285 447.522 ↑ 5.3 705 3

Merge Anti Join (cost=24,154.29..24,173.13 rows=3,764 width=4) (actual time=149.009..149.174 rows=705 loops=3)

  • Merge Cond: (l.video_id = w.video_id)
9. 1.149 443.583 ↑ 5.3 705 3

Sort (cost=24,059.78..24,069.19 rows=3,765 width=4) (actual time=147.789..147.861 rows=705 loops=3)

  • Sort Key: l.video_id
  • Sort Method: quicksort Memory: 58kB
  • Worker 0: Sort Method: quicksort Memory: 60kB
  • Worker 1: Sort Method: quicksort Memory: 54kB
10. 0.756 442.434 ↑ 5.3 705 3

Parallel Hash Anti Join (cost=17,931.35..23,836.17 rows=3,765 width=4) (actual time=107.595..147.478 rows=705 loops=3)

  • Hash Cond: (l.video_id = l_user_1.video_id)
11. 70.374 335.031 ↑ 4.5 842 3

Parallel Hash Semi Join (cost=12,142.15..17,999.38 rows=3,777 width=4) (actual time=71.966..111.677 rows=842 loops=3)

  • Hash Cond: (l.user_id = l_other_user.user_id)
12. 49.080 49.080 ↑ 1.2 166,667 3

Parallel Seq Scan on likes l (cost=0.00..5,268.33 rows=208,333 width=8) (actual time=0.006..16.360 rows=166,667 loops=3)

13. 0.324 215.577 ↑ 5.0 137 3

Parallel Hash (cost=12,133.65..12,133.65 rows=680 width=4) (actual time=71.859..71.859 rows=137 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 112kB
14. 62.739 215.253 ↑ 5.0 137 3

Parallel Hash Semi Join (cost=5,789.19..12,133.65 rows=680 width=4) (actual time=24.036..71.751 rows=137 loops=3)

  • Hash Cond: (l_other_user.video_id = l_user.video_id)
15. 82.911 82.911 ↑ 1.2 166,665 3

Parallel Seq Scan on likes l_other_user (cost=0.00..5,789.17 rows=208,331 width=8) (actual time=0.010..27.637 rows=166,665 loops=3)

  • Filter: (user_id <> 1)
  • Rows Removed by Filter: 1
16. 0.102 69.603 ↑ 2.0 1 3

Parallel Hash (cost=5,789.17..5,789.17 rows=2 width=4) (actual time=23.201..23.201 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 72kB
17. 69.501 69.501 ↑ 2.0 1 3

Parallel Seq Scan on likes l_user (cost=0.00..5,789.17 rows=2 width=4) (actual time=17.712..23.167 rows=1 loops=3)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 166665
18. 0.185 106.647 ↑ 2.0 1 3

Parallel Hash (cost=5,789.17..5,789.17 rows=2 width=4) (actual time=35.549..35.549 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
19. 106.462 106.462 ↑ 1.0 2 2

Parallel Seq Scan on likes l_user_1 (cost=0.00..5,789.17 rows=2 width=4) (actual time=35.722..53.231 rows=2 loops=2)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 249998
20. 0.054 3.654 ↓ 0.0 0 3

Sort (cost=94.51..94.52 rows=1 width=4) (actual time=1.218..1.218 rows=0 loops=3)

  • Sort Key: w.video_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
21. 3.600 3.600 ↓ 0.0 0 3

Seq Scan on watch w (cost=0.00..94.50 rows=1 width=4) (actual time=1.200..1.200 rows=0 loops=3)

  • Filter: (user_id = 1)
  • Rows Removed by Filter: 5000
22. 0.673 1.290 ↑ 1.0 5,000 1

Hash (cost=83.00..83.00 rows=5,000 width=4) (actual time=1.289..1.290 rows=5,000 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 240kB
23. 0.617 0.617 ↑ 1.0 5,000 1

Seq Scan on video v (cost=0.00..83.00 rows=5,000 width=4) (actual time=0.017..0.617 rows=5,000 loops=1)

Planning time : 2.776 ms
Execution time : 244.725 ms