explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xgv1

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 854.069 ↑ 1.0 30 1

Limit (cost=88,277.89..271,362.73 rows=30 width=160) (actual time=213.377..854.069 rows=30 loops=1)

2.          

CTE last_week_moves

3. 9.108 189.409 ↓ 90.0 33,118 1

WindowAgg (cost=87,300.05..87,308.33 rows=368 width=32) (actual time=178.986..189.409 rows=33,118 loops=1)

4. 10.619 180.301 ↓ 90.0 33,118 1

Sort (cost=87,300.05..87,300.97 rows=368 width=16) (actual time=178.981..180.301 rows=33,118 loops=1)

  • Sort Key: m_1.gid, m_1.played
  • Sort Method: quicksort Memory: 4124kB
5. 4.608 169.682 ↓ 90.0 33,118 1

Gather (cost=13,632.94..87,284.37 rows=368 width=16) (actual time=27.440..169.682 rows=33,118 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 2.587 165.074 ↓ 72.2 11,039 3

Hash Join (cost=12,632.94..86,247.57 rows=153 width=16) (actual time=35.476..165.074 rows=11,039 loops=3)

  • Hash Cond: (m_1.gid = g.gid)
  • Join Filter: ((m_1.uid = g.player1) OR (m_1.uid = g.player2))
7. 127.981 127.981 ↓ 1.3 11,039 3

Parallel Seq Scan on words_moves m_1 (cost=0.00..73,591.82 rows=8,686 width=16) (actual time=0.806..127.981 rows=11,039 loops=3)

  • Filter: (played > (CURRENT_TIMESTAMP - '7 days'::interval))
  • Rows Removed by Filter: 492221
8. 6.336 34.506 ↓ 1.0 50,044 3

Hash (cost=12,007.42..12,007.42 rows=50,042 width=12) (actual time=34.506..34.506 rows=50,044 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 2663kB
9. 28.170 28.170 ↓ 1.0 50,044 3

Seq Scan on words_games g (cost=0.00..12,007.42 rows=50,042 width=12) (actual time=0.008..28.170 rows=50,044 loops=3)

10. 0.158 854.048 ↑ 4.7 30 1

Result (cost=969.56..855,365.45 rows=140 width=160) (actual time=213.376..854.048 rows=30 loops=1)

11. 0.043 201.630 ↑ 4.7 30 1

Sort (cost=969.56..969.91 rows=140 width=96) (actual time=201.613..201.630 rows=30 loops=1)

  • Sort Key: u.elo DESC
  • Sort Method: top-N heapsort Memory: 32kB
12. 0.041 201.587 ↑ 1.3 110 1

Nested Loop Anti Join (cost=433.41..965.43 rows=140 width=96) (actual time=199.884..201.587 rows=110 loops=1)

13. 0.056 201.395 ↑ 1.4 151 1

Hash Semi Join (cost=433.12..874.72 rows=211 width=104) (actual time=199.765..201.395 rows=151 loops=1)

  • Hash Cond: (u.uid = m.uid)
14. 0.930 2.841 ↓ 1.1 473 1

Hash Join (cost=421.19..849.83 rows=422 width=104) (actual time=1.095..2.841 rows=473 loops=1)

  • Hash Cond: (s.uid = u.uid)
15. 0.840 0.840 ↑ 1.0 10,664 1

Seq Scan on words_social s (cost=0.00..400.64 rows=10,664 width=96) (actual time=0.009..0.840 rows=10,664 loops=1)

16. 0.042 1.071 ↑ 1.0 418 1

Hash (cost=415.96..415.96 rows=418 width=8) (actual time=1.071..1.071 rows=418 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
17. 1.029 1.029 ↑ 1.0 418 1

Seq Scan on words_users u (cost=0.00..415.96 rows=418 width=8) (actual time=0.004..1.029 rows=418 loops=1)

  • Filter: (elo > 1500)
  • Rows Removed by Filter: 10139
18. 2.776 198.498 ↓ 85.9 31,445 1

Hash (cost=7.36..7.36 rows=366 width=4) (actual time=198.498..198.498 rows=31,445 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1362kB
19. 195.722 195.722 ↓ 85.9 31,445 1

CTE Scan on last_week_moves m (cost=0.00..7.36 rows=366 width=4) (actual time=178.992..195.722 rows=31,445 loops=1)

  • Filter: (diff IS NOT NULL)
  • Rows Removed by Filter: 1673
20. 0.151 0.151 ↓ 0.0 0 151

Index Only Scan using words_social_uid_stamp_idx on words_social x (cost=0.29..0.43 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=151)

  • Index Cond: ((uid = s.uid) AND (stamp > s.stamp))
  • Heap Fetches: 41
21.          

SubPlan (forResult)

22. 1.860 55.860 ↑ 1.0 1 30

Aggregate (cost=8.29..8.30 rows=1 width=32) (actual time=1.862..1.862 rows=1 loops=30)

23. 54.000 54.000 ↓ 200.5 401 30

CTE Scan on last_week_moves (cost=0.00..8.28 rows=2 width=16) (actual time=0.179..1.800 rows=401 loops=30)

  • Filter: (uid = u.uid)
  • Rows Removed by Filter: 32717
24. 37.980 596.400 ↑ 1.0 1 30

Aggregate (cost=6,094.50..6,094.52 rows=1 width=32) (actual time=19.880..19.880 rows=1 loops=30)

25. 528.360 558.420 ↓ 8.6 15,009 30

Bitmap Heap Scan on words_moves (cost=33.97..6,090.13 rows=1,747 width=4) (actual time=1.714..18.614 rows=15,009 loops=30)

  • Recheck Cond: (uid = u.uid)
  • Heap Blocks: exact=216285
26. 30.060 30.060 ↓ 8.6 15,009 30

Bitmap Index Scan on words_moves_uid_idx (cost=0.00..33.53 rows=1,747 width=0) (actual time=1.002..1.002 rows=15,009 loops=30)

  • Index Cond: (uid = u.uid)