explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wMMV

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 5,133.424 ↑ 1.0 30 1

Limit (cost=88,273.29..2,532,117.76 rows=30 width=160) (actual time=365.656..5,133.424 rows=30 loops=1)

2.          

CTE last_week_moves

3. 9.180 187.623 ↓ 89.9 33,081 1

WindowAgg (cost=87,295.44..87,303.72 rows=368 width=32) (actual time=177.137..187.623 rows=33,081 loops=1)

4. 11.407 178.443 ↓ 89.9 33,081 1

Sort (cost=87,295.44..87,296.36 rows=368 width=16) (actual time=177.132..178.443 rows=33,081 loops=1)

  • Sort Key: m_1.gid, m_1.played
  • Sort Method: quicksort Memory: 4121kB
5. 3.779 167.036 ↓ 89.9 33,081 1

Gather (cost=13,632.94..87,279.76 rows=368 width=16) (actual time=30.055..167.036 rows=33,081 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 2.533 163.257 ↓ 72.1 11,027 3

Hash Join (cost=12,632.94..86,242.96 rows=153 width=16) (actual time=34.022..163.257 rows=11,027 loops=3)

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

Parallel Seq Scan on words_moves m_1 (cost=0.00..73,587.21 rows=8,688 width=16) (actual time=0.775..127.666 rows=11,027 loops=3)

  • Filter: (played > (CURRENT_TIMESTAMP - '7 days'::interval))
  • Rows Removed by Filter: 492219
8. 6.509 33.058 ↑ 1.0 50,042 3

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2663kB
9. 26.549 26.549 ↑ 1.0 50,042 3

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

10. 0.110 5,133.406 ↑ 4.7 30 1

Result (cost=969.56..11,405,577.11 rows=140 width=160) (actual time=365.655..5,133.406 rows=30 loops=1)

11. 0.040 200.246 ↑ 4.7 30 1

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

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

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

13. 0.053 200.013 ↑ 1.4 151 1

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

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

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

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

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

16. 0.042 1.115 ↑ 1.0 418 1

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

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

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

  • Filter: (elo > 1500)
  • Rows Removed by Filter: 10139
18. 2.695 197.009 ↓ 85.8 31,410 1

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

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1361kB
19. 194.314 194.314 ↓ 85.8 31,410 1

CTE Scan on last_week_moves m (cost=0.00..7.36 rows=366 width=4) (actual time=177.143..194.314 rows=31,410 loops=1)

  • Filter: (diff IS NOT NULL)
  • Rows Removed by Filter: 1671
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.890 52.770 ↑ 1.0 1 30

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

23. 50.880 50.880 ↓ 200.5 401 30

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

  • Filter: (uid = u.uid)
  • Rows Removed by Filter: 32680
24. 30.090 4,880.280 ↑ 1.0 1 30

Aggregate (cost=81,453.16..81,453.17 rows=1 width=32) (actual time=162.675..162.676 rows=1 loops=30)

25. 4,850.190 4,850.190 ↓ 8.6 15,009 30

Seq Scan on words_moves (cost=0.00..81,448.79 rows=1,747 width=4) (actual time=0.443..161.673 rows=15,009 loops=30)

  • Filter: (uid = u.uid)
  • Rows Removed by Filter: 1494728