explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jS3T

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 732.207 ↑ 1.0 30 1

Limit (cost=89,276.06..272,851.27 rows=30 width=160) (actual time=115.238..732.207 rows=30 loops=1)

2.          

CTE last_week_moves

3. 9.260 92.792 ↓ 109.5 35,261 1

WindowAgg (cost=87,093.71..87,100.96 rows=322 width=32) (actual time=82.161..92.792 rows=35,261 loops=1)

4. 9.764 83.532 ↓ 109.5 35,261 1

Sort (cost=87,093.71..87,094.52 rows=322 width=16) (actual time=82.157..83.532 rows=35,261 loops=1)

  • Sort Key: m_1.gid, m_1.played
  • Sort Method: quicksort Memory: 4291kB
5. 6.906 73.768 ↓ 109.5 35,261 1

Hash Join (cost=12,635.11..87,080.30 rows=322 width=16) (actual time=47.866..73.768 rows=35,261 loops=1)

  • Hash Cond: (m_1.gid = g.gid)
  • Join Filter: ((m_1.uid = g.player1) OR (m_1.uid = g.player2))
6. 41.295 41.295 ↓ 1.9 35,261 1

Index Scan using words_moves_gid_played_idx on words_moves m_1 (cost=0.43..74,397.39 rows=18,375 width=16) (actual time=22.157..41.295 rows=35,261 loops=1)

  • Index Cond: (played > (CURRENT_TIMESTAMP - '7 days'::interval))
7. 5.153 25.567 ↓ 1.0 50,144 1

Hash (cost=12,008.19..12,008.19 rows=50,119 width=12) (actual time=25.567..25.567 rows=50,144 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2667kB
8. 20.414 20.414 ↓ 1.0 50,144 1

Seq Scan on words_games g (cost=0.00..12,008.19 rows=50,119 width=12) (actual time=0.007..20.414 rows=50,144 loops=1)

9. 0.137 732.188 ↑ 6.9 30 1

Result (cost=2,175.11..1,274,963.19 rows=208 width=160) (actual time=115.238..732.188 rows=30 loops=1)

10. 0.043 103.971 ↑ 6.9 30 1

Sort (cost=2,175.11..2,175.63 rows=208 width=96) (actual time=103.954..103.971 rows=30 loops=1)

  • Sort Key: u.elo DESC
  • Sort Method: top-N heapsort Memory: 31kB
11. 0.044 103.928 ↑ 1.9 107 1

Nested Loop Left Join (cost=10.73..2,168.96 rows=208 width=96) (actual time=102.219..103.928 rows=107 loops=1)

12. 0.059 103.563 ↑ 1.9 107 1

Hash Semi Join (cost=10.44..437.88 rows=208 width=8) (actual time=102.193..103.563 rows=107 loops=1)

  • Hash Cond: (u.uid = m.uid)
13. 1.335 1.335 ↑ 1.0 417 1

Seq Scan on words_users u (cost=0.00..415.98 rows=417 width=8) (actual time=0.010..1.335 rows=417 loops=1)

  • Filter: (elo > 1500)
  • Rows Removed by Filter: 10142
14. 2.913 102.169 ↓ 105.0 33,613 1

Hash (cost=6.44..6.44 rows=320 width=4) (actual time=102.169..102.169 rows=33,613 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1694kB
15. 99.256 99.256 ↓ 105.0 33,613 1

CTE Scan on last_week_moves m (cost=0.00..6.44 rows=320 width=4) (actual time=82.166..99.256 rows=33,613 loops=1)

  • Filter: (diff IS NOT NULL)
  • Rows Removed by Filter: 1648
16. 0.000 0.321 ↑ 1.0 1 107

Limit (cost=0.29..8.30 rows=1 width=180) (actual time=0.003..0.003 rows=1 loops=107)

17. 0.321 0.321 ↑ 1.0 1 107

Index Scan using words_social_uid_stamp_idx on words_social s (cost=0.29..8.30 rows=1 width=180) (actual time=0.003..0.003 rows=1 loops=107)

  • Index Cond: (uid = u.uid)
18.          

SubPlan (forResult)

19. 1.980 58.050 ↑ 1.0 1 30

Aggregate (cost=7.25..7.27 rows=1 width=32) (actual time=1.935..1.935 rows=1 loops=30)

20. 56.070 56.070 ↓ 216.5 433 30

CTE Scan on last_week_moves (cost=0.00..7.25 rows=2 width=16) (actual time=0.235..1.869 rows=433 loops=30)

  • Filter: (uid = u.uid)
  • Rows Removed by Filter: 34828
21. 37.020 570.030 ↑ 1.0 1 30

Aggregate (cost=6,111.88..6,111.90 rows=1 width=32) (actual time=19.001..19.001 rows=1 loops=30)

22. 504.870 533.010 ↓ 8.3 14,555 30

Bitmap Heap Scan on words_moves (cost=34.01..6,107.50 rows=1,752 width=4) (actual time=1.612..17.767 rows=14,555 loops=30)

  • Recheck Cond: (uid = u.uid)
  • Heap Blocks: exact=211918
23. 28.140 28.140 ↓ 8.3 14,558 30

Bitmap Index Scan on words_moves_uid_idx (cost=0.00..33.57 rows=1,752 width=0) (actual time=0.938..0.938 rows=14,558 loops=30)

  • Index Cond: (uid = u.uid)