explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EWfH

Settings
# exclusive inclusive rows x rows loops node
1. 0.078 1,244.265 ↑ 514,796.0 8 1

Nested Loop Left Join (cost=82,849.25..7,338,297.75 rows=4,118,368 width=1,430) (actual time=163.727..1,244.265 rows=8 loops=1)

2. 0.033 1,242.235 ↑ 128,699.0 8 1

Nested Loop Left Join (cost=82,849.25..6,791,758.96 rows=1,029,592 width=1,776) (actual time=161.805..1,242.235 rows=8 loops=1)

3. 0.012 1,242.010 ↑ 8.4 8 1

Nested Loop Left Join (cost=82,345.74..5,440,247.97 rows=67 width=1,774) (actual time=161.725..1,242.010 rows=8 loops=1)

4. 0.012 1,241.974 ↑ 8.4 8 1

Nested Loop Left Join (cost=82,345.45..5,440,238.83 rows=67 width=1,763) (actual time=161.702..1,241.974 rows=8 loops=1)

5. 0.016 6.890 ↑ 8.4 8 1

Nested Loop Left Join (cost=1,175.93..1,879.65 rows=67 width=1,555) (actual time=6.767..6.890 rows=8 loops=1)

6. 0.027 6.826 ↑ 8.4 8 1

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

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

Bitmap Heap Scan on words_games g (cost=9.29..263.40 rows=67 width=1,456) (actual time=0.031..0.080 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.007 0.007 ↑ 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.007..0.007 rows=1 loops=1)

  • Index Cond: ((5 = player1) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval)))
10. 0.012 0.012 ↑ 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.011..0.012 rows=7 loops=1)

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

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

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

Hash Anti Join (cost=533.87..1,077.52 rows=7,107 width=92) (actual time=2.205..4.941 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.689 0.689 ↑ 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.689 rows=10,661 loops=1)

14. 0.845 2.194 ↑ 1.0 10,661 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 545kB
15. 1.349 1.349 ↑ 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.349 rows=10,661 loops=1)

16. 0.048 0.048 ↑ 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.006..0.006 rows=1 loops=8)

  • Index Cond: (uid = CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END)
17. 0.016 1,235.072 ↑ 1.0 1 8

Limit (cost=81,169.52..81,169.52 rows=1 width=216) (actual time=154.383..154.384 rows=1 loops=8)

18. 0.112 1,235.056 ↑ 41.0 1 8

Sort (cost=81,169.52..81,169.62 rows=41 width=216) (actual time=154.382..154.382 rows=1 loops=8)

  • Sort Key: m.played DESC
  • Sort Method: top-N heapsort Memory: 25kB
19. 1,234.944 1,234.944 ↑ 1.3 31 8

Seq Scan on words_moves m (cost=0.00..81,169.31 rows=41 width=216) (actual time=43.592..154.368 rows=31 loops=8)

  • Filter: (gid = g.gid)
  • Rows Removed by Filter: 1503269
20. 0.003 0.024 ↑ 1.0 1 8

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

21. 0.021 0.021 ↑ 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.020..0.021 rows=1 loops=1)

  • Index Cond: (uid = 5)
22. 0.032 0.192 ↑ 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.024..0.024 rows=1 loops=8)

  • Recheck Cond: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END <<= block)
  • Heap Blocks: exact=7
23. 0.160 0.160 ↑ 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.020..0.020 rows=1 loops=8)

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

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

25. 0.003 1.946 ↑ 4.0 1 1

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

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

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

  • Filter: (uid = 5)
  • Rows Removed by Filter: 10655
27. 0.003 0.984 ↑ 2.0 3 6

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

28. 0.981 0.981 ↑ 1.0 6 1

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

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