explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YCiA : Optimization for: plan #qChu

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.087 1,616.242 ↑ 10,372,759.0 8 1

Nested Loop Left Join (cost=106,054.37..38,538,688.95 rows=82,982,072 width=1,430) (actual time=1,135.135..1,616.242 rows=8 loops=1)

2. 0.033 1,614.139 ↑ 2,593,189.8 8 1

Nested Loop Left Join (cost=106,054.37..27,542,709.38 rows=20,745,518 width=1,776) (actual time=1,133.152..1,614.139 rows=8 loops=1)

3. 0.049 1,613.906 ↑ 168.8 8 1

Hash Left Join (cost=105,550.86..310,771.52 rows=1,350 width=1,774) (actual time=1,133.097..1,613.906 rows=8 loops=1)

  • Hash Cond: (CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END = u2.uid)
4. 0.010 1,611.468 ↑ 168.8 8 1

Nested Loop Left Join (cost=105,029.40..310,246.34 rows=1,350 width=1,763) (actual time=1,130.668..1,611.468 rows=8 loops=1)

5. 0.028 1,611.442 ↑ 168.8 8 1

Hash Left Join (cost=105,029.11..310,221.16 rows=1,350 width=1,752) (actual time=1,130.657..1,611.442 rows=8 loops=1)

  • Hash Cond: (CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END = s2.uid)
6. 3.270 1,604.453 ↑ 168.8 8 1

Hash Right Join (cost=103,862.76..309,025.47 rows=1,350 width=1,664) (actual time=1,123.679..1,604.453 rows=8 loops=1)

  • Hash Cond: (m.gid = g.gid)
7. 967.297 1,601.116 ↑ 20.2 49,842 1

Hash Anti Join (cost=103,598.52..306,123.21 rows=1,004,875 width=212) (actual time=389.860..1,601.116 rows=49,842 loops=1)

  • Hash Cond: (m.gid = m2.gid)
  • Join Filter: (m2.played > m.played)
  • Rows Removed by Join Filter: 4412558
8. 244.679 244.679 ↑ 1.0 1,503,243 1

Seq Scan on words_moves m (cost=0.00..77,397.12 rows=1,507,312 width=220) (actual time=0.003..244.679 rows=1,503,243 loops=1)

9. 167.311 389.140 ↑ 1.0 1,503,243 1

Hash (cost=77,397.12..77,397.12 rows=1,507,312 width=12) (actual time=389.140..389.140 rows=1,503,243 loops=1)

  • Buckets: 262144 Batches: 16 Memory Usage: 6716kB
10. 221.829 221.829 ↑ 1.0 1,503,243 1

Seq Scan on words_moves m2 (cost=0.00..77,397.12 rows=1,507,312 width=12) (actual time=0.003..221.829 rows=1,503,243 loops=1)

11. 0.012 0.067 ↑ 8.4 8 1

Hash (cost=263.40..263.40 rows=67 width=1,456) (actual time=0.067..0.067 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
12. 0.036 0.055 ↑ 8.4 8 1

Bitmap Heap Scan on words_games g (cost=9.29..263.40 rows=67 width=1,456) (actual time=0.024..0.055 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
13. 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)

14. 0.010 0.010 ↑ 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.010..0.010 rows=1 loops=1)

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

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

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

  • Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1419kB
17. 2.127 5.146 ↓ 1.5 10,555 1

Hash Anti Join (cost=533.87..1,077.52 rows=7,107 width=92) (actual time=2.252..5.146 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
18. 0.789 0.789 ↑ 1.0 10,661 1

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

19. 0.895 2.230 ↑ 1.0 10,661 1

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

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

21. 0.006 0.016 ↑ 1.0 1 8

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

22. 0.010 0.010 ↑ 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.008..0.010 rows=1 loops=1)

  • Index Cond: (uid = 5)
23. 1.057 2.389 ↑ 1.0 10,554 1

Hash (cost=389.54..389.54 rows=10,554 width=15) (actual time=2.389..2.389 rows=10,554 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 613kB
24. 1.332 1.332 ↑ 1.0 10,554 1

Seq Scan on words_users u2 (cost=0.00..389.54 rows=10,554 width=15) (actual time=0.004..1.332 rows=10,554 loops=1)

25. 0.040 0.200 ↑ 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.025..0.025 rows=1 loops=8)

  • Recheck Cond: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END <<= block)
  • Heap Blocks: exact=7
26. 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)
27. 0.005 2.016 ↑ 4.0 1 8

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

28. 0.004 2.011 ↑ 4.0 1 1

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

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

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

  • Filter: (uid = 5)
  • Rows Removed by Filter: 10655
30. 0.005 1.020 ↑ 2.0 3 6

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

31. 1.015 1.015 ↑ 1.0 6 1

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

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