explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xwfS

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 2,081.591 ↑ 1.0 30 1

Limit (cost=256,086.04..439,965.41 rows=30 width=176) (actual time=1,467.999..2,081.591 rows=30 loops=1)

2.          

CTE last_week_moves

3. 9.487 91.465 ↓ 119.6 36,112 1

WindowAgg (cost=84,908.84..84,915.63 rows=302 width=32) (actual time=80.620..91.465 rows=36,112 loops=1)

4. 10.062 81.978 ↓ 119.6 36,112 1

Sort (cost=84,908.84..84,909.59 rows=302 width=16) (actual time=80.616..81.978 rows=36,112 loops=1)

  • Sort Key: m_1.gid, m_1.played
  • Sort Method: quicksort Memory: 4358kB
5. 6.790 71.916 ↓ 119.6 36,112 1

Hash Join (cost=12,636.57..84,896.40 rows=302 width=16) (actual time=46.335..71.916 rows=36,112 loops=1)

  • Hash Cond: (m_1.gid = g.gid)
  • Join Filter: ((m_1.uid = g.player1) OR (m_1.uid = g.player2))
6. 40.790 40.790 ↓ 2.1 36,112 1

Index Scan using words_moves_gid_played_idx on words_moves m_1 (cost=0.43..72,215.16 rows=17,182 width=16) (actual time=21.909..40.790 rows=36,112 loops=1)

  • Index Cond: (played > (CURRENT_TIMESTAMP - '7 days'::interval))
7. 5.443 24.336 ↑ 1.0 50,184 1

Hash (cost=12,008.84..12,008.84 rows=50,184 width=12) (actual time=24.336..24.336 rows=50,184 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2669kB
8. 18.893 18.893 ↑ 1.0 50,184 1

Seq Scan on words_games g (cost=0.00..12,008.84 rows=50,184 width=12) (actual time=0.008..18.893 rows=50,184 loops=1)

9. 0.133 2,081.571 ↑ 7.0 30 1

Result (cost=171,170.41..1,458,326.00 rows=210 width=176) (actual time=1,467.998..2,081.571 rows=30 loops=1)

10. 0.059 1,456.838 ↑ 7.0 30 1

Sort (cost=171,170.41..171,170.93 rows=210 width=112) (actual time=1,456.820..1,456.838 rows=30 loops=1)

  • Sort Key: u.elo DESC
  • Sort Method: top-N heapsort Memory: 32kB
11. 0.040 1,456.779 ↑ 1.9 108 1

Nested Loop Left Join (cost=0.70..171,164.20 rows=210 width=112) (actual time=81.056..1,456.779 rows=108 loops=1)

12. 0.042 1,455.551 ↑ 1.9 108 1

Nested Loop Left Join (cost=0.29..4,995.02 rows=210 width=103) (actual time=81.039..1,455.551 rows=108 loops=1)

13. 418.841 1,455.185 ↑ 1.9 108 1

Nested Loop Semi Join (cost=0.00..3,247.29 rows=210 width=15) (actual time=81.029..1,455.185 rows=108 loops=1)

  • Join Filter: (u.uid = m.uid)
  • Rows Removed by Join Filter: 11328535
14. 1.414 1.414 ↑ 1.0 419 1

Seq Scan on words_users u (cost=0.00..416.00 rows=419 width=15) (actual time=0.005..1.414 rows=419 loops=1)

  • Filter: (elo > 1500)
  • Rows Removed by Filter: 10141
15. 1,034.930 1,034.930 ↓ 90.1 27,037 419

CTE Scan on last_week_moves m (cost=0.00..6.04 rows=300 width=4) (actual time=0.193..2.470 rows=27,037 loops=419)

  • Filter: (diff IS NOT NULL)
  • Rows Removed by Filter: 1233
16. 0.000 0.324 ↑ 1.0 1 108

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

17. 0.324 0.324 ↑ 1.0 1 108

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=108)

  • Index Cond: (uid = u.uid)
18. 1.188 1.188 ↑ 15,367.0 1 108

Index Scan using words_geoip_block_idx on words_geoip i (cost=0.41..637.61 rows=15,367 width=23) (actual time=0.011..0.011 rows=1 loops=108)

  • Index Cond: (u.ip <<= block)
19.          

SubPlan (forResult)

20. 2.040 59.910 ↑ 1.0 1 30

Aggregate (cost=6.80..6.82 rows=1 width=32) (actual time=1.997..1.997 rows=1 loops=30)

21. 57.870 57.870 ↓ 221.5 443 30

CTE Scan on last_week_moves (cost=0.00..6.79 rows=2 width=16) (actual time=0.206..1.929 rows=443 loops=30)

  • Filter: (uid = u.uid)
  • Rows Removed by Filter: 35669
22. 36.960 564.690 ↑ 1.0 1 30

Aggregate (cost=6,122.47..6,122.48 rows=1 width=32) (actual time=18.823..18.823 rows=1 loops=30)

23. 499.020 527.730 ↓ 8.2 14,419 30

Bitmap Heap Scan on words_moves (cost=34.03..6,118.08 rows=1,755 width=4) (actual time=1.638..17.591 rows=14,419 loops=30)

  • Recheck Cond: (uid = u.uid)
  • Heap Blocks: exact=210194
24. 28.710 28.710 ↓ 8.2 14,424 30

Bitmap Index Scan on words_moves_uid_idx (cost=0.00..33.59 rows=1,755 width=0) (actual time=0.957..0.957 rows=14,424 loops=30)

  • Index Cond: (uid = u.uid)