explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MJGz

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 8.371 ↑ 514,796.0 8 1

Nested Loop Left Join (cost=1,680.16..1,900,142.78 rows=4,118,368 width=1,430) (actual time=8.151..8.371 rows=8 loops=1)

2. 0.010 6.674 ↑ 128,699.0 8 1

Nested Loop Left Join (cost=1,680.16..1,353,603.99 rows=1,029,592 width=1,776) (actual time=6.514..6.674 rows=8 loops=1)

3. 0.004 6.552 ↑ 8.4 8 1

Nested Loop Left Join (cost=1,176.65..2,093.00 rows=67 width=1,774) (actual time=6.470..6.552 rows=8 loops=1)

4. 0.000 6.532 ↑ 8.4 8 1

Nested Loop Left Join (cost=1,176.36..1,644.57 rows=67 width=1,763) (actual time=6.468..6.532 rows=8 loops=1)

5. 0.002 6.524 ↑ 8.4 8 1

Nested Loop Left Join (cost=1,176.07..1,635.43 rows=67 width=1,752) (actual time=6.463..6.524 rows=8 loops=1)

6. 0.012 6.482 ↑ 8.4 8 1

Hash Left Join (cost=1,175.65..1,431.21 rows=67 width=1,544) (actual time=6.453..6.482 rows=8 loops=1)

  • Hash Cond: (CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END = s2.uid)
7. 0.028 0.047 ↑ 8.4 8 1

Bitmap Heap Scan on words_games g (cost=9.29..263.40 rows=67 width=1,456) (actual time=0.023..0.047 rows=8 loops=1)

  • Recheck Cond: (((5 = player1) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval))) OR ((5 = player2) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval))))
  • Filter: (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval))
  • Heap Blocks: exact=8
8. 0.000 0.019 ↓ 0.0 0 1

BitmapOr (cost=9.29..9.29 rows=67 width=0) (actual time=0.019..0.019 rows=0 loops=1)

9. 0.013 0.013 ↑ 4.0 1 1

Bitmap Index Scan on words_games_player1_coalesce_idx (cost=0.00..4.33 rows=4 width=0) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: ((5 = player1) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval)))
10. 0.006 0.006 ↑ 9.0 7 1

Bitmap Index Scan on words_games_player2_coalesce_idx (cost=0.00..4.92 rows=63 width=0) (actual time=0.006..0.006 rows=7 loops=1)

  • Index Cond: ((5 = player2) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval)))
11. 1.434 6.423 ↓ 1.5 10,555 1

Hash (cost=1,077.52..1,077.52 rows=7,107 width=92) (actual time=6.423..6.423 rows=10,555 loops=1)

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1419kB
12. 2.070 4.989 ↓ 1.5 10,555 1

Hash Anti Join (cost=533.87..1,077.52 rows=7,107 width=92) (actual time=2.319..4.989 rows=10,555 loops=1)

  • Hash Cond: (s2.uid = s_1.uid)
  • Join Filter: (s_1.stamp > s2.stamp)
  • Rows Removed by Join Filter: 10714
13. 0.611 0.611 ↑ 1.0 10,661 1

Seq Scan on words_social s2 (cost=0.00..400.61 rows=10,661 width=96) (actual time=0.003..0.611 rows=10,661 loops=1)

14. 0.850 2.308 ↑ 1.0 10,661 1

Hash (cost=400.61..400.61 rows=10,661 width=8) (actual time=2.308..2.308 rows=10,661 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 545kB
15. 1.458 1.458 ↑ 1.0 10,661 1

Seq Scan on words_social s_1 (cost=0.00..400.61 rows=10,661 width=8) (actual time=0.001..1.458 rows=10,661 loops=1)

16. 0.008 0.040 ↑ 1.0 1 8

Limit (cost=0.43..3.03 rows=1 width=216) (actual time=0.005..0.005 rows=1 loops=8)

17. 0.032 0.032 ↑ 41.0 1 8

Index Scan using words_moves_gid_played_idx on words_moves m (cost=0.43..107.05 rows=41 width=216) (actual time=0.004..0.004 rows=1 loops=8)

  • Index Cond: (gid = g.gid)
18. 0.004 0.008 ↑ 1.0 1 8

Materialize (cost=0.29..8.31 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=8)

19. 0.004 0.004 ↑ 1.0 1 1

Index Scan using words_users_pkey on words_users u1 (cost=0.29..8.30 rows=1 width=11) (actual time=0.003..0.004 rows=1 loops=1)

  • Index Cond: (uid = 5)
20. 0.016 0.016 ↑ 1.0 1 8

Index Scan using words_users_pkey on words_users u2 (cost=0.29..6.69 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=8)

  • Index Cond: (uid = CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END)
21. 0.008 0.112 ↑ 15,367.0 1 8

Bitmap Heap Scan on words_geoip i2 (cost=503.51..20,018.14 rows=15,367 width=23) (actual time=0.014..0.014 rows=1 loops=8)

  • Recheck Cond: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END <<= block)
  • Heap Blocks: exact=7
22. 0.104 0.104 ↑ 15,367.0 1 8

Bitmap Index Scan on words_geoip_block_idx (cost=0.00..499.67 rows=15,367 width=0) (actual time=0.013..0.013 rows=1 loops=8)

  • Index Cond: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END <<= block)
23. 0.001 1.672 ↑ 4.0 1 8

Materialize (cost=0.00..855.04 rows=4 width=88) (actual time=0.204..0.209 rows=1 loops=8)

24. 0.002 1.671 ↑ 4.0 1 1

Nested Loop Anti Join (cost=0.00..855.02 rows=4 width=88) (actual time=1.628..1.671 rows=1 loops=1)

  • Join Filter: ((s.stamp > s1.stamp) AND (s1.uid = s.uid))
  • Rows Removed by Join Filter: 11
25. 0.847 0.847 ↑ 1.0 6 1

Seq Scan on words_social s1 (cost=0.00..427.26 rows=6 width=96) (actual time=0.088..0.847 rows=6 loops=1)

  • Filter: (uid = 5)
  • Rows Removed by Filter: 10655
26. 0.002 0.822 ↑ 2.0 3 6

Materialize (cost=0.00..427.29 rows=6 width=8) (actual time=0.014..0.137 rows=3 loops=6)

27. 0.820 0.820 ↑ 1.0 6 1

Seq Scan on words_social s (cost=0.00..427.26 rows=6 width=8) (actual time=0.085..0.820 rows=6 loops=1)

  • Filter: (uid = 5)
  • Rows Removed by Filter: 10655