explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VDfr

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 14.829 ↑ 128,699.0 8 1

Nested Loop Left Join (cost=1,395.00..908,801,363.25 rows=1,029,592 width=1,430) (actual time=2.215..14.829 rows=8 loops=1)

2. 0.001 7.426 ↑ 128,699.0 8 1

Nested Loop Left Join (cost=941.08..441,294,523.81 rows=1,029,592 width=1,776) (actual time=1.256..7.426 rows=8 loops=1)

3. 0.008 0.289 ↑ 128,699.0 8 1

Nested Loop Left Join (cost=513.80..1,352,436.19 rows=1,029,592 width=1,692) (actual time=0.132..0.289 rows=8 loops=1)

4. 0.006 0.153 ↑ 8.4 8 1

Nested Loop Left Join (cost=10.29..925.20 rows=67 width=1,690) (actual time=0.077..0.153 rows=8 loops=1)

5. 0.001 0.131 ↑ 8.4 8 1

Nested Loop Left Join (cost=10.01..476.76 rows=67 width=1,679) (actual time=0.074..0.131 rows=8 loops=1)

6. 0.011 0.122 ↑ 8.4 8 1

Nested Loop Left Join (cost=9.72..467.62 rows=67 width=1,664) (actual time=0.069..0.122 rows=8 loops=1)

7. 0.024 0.063 ↑ 8.4 8 1

Bitmap Heap Scan on words_games g (cost=9.29..263.40 rows=67 width=1,456) (actual time=0.046..0.063 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.039 ↓ 0.0 0 1

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

9. 0.018 0.018 ↑ 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.018..0.018 rows=1 loops=1)

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

  • Index Cond: ((5 = player2) AND (COALESCE(finished, 'infinity'::timestamp with time zone) > (CURRENT_TIMESTAMP - '1 day'::interval)))
11. 0.000 0.048 ↑ 1.0 1 8

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

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

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

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

14. 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=15) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (uid = 5)
15. 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)
16. 0.024 0.128 ↑ 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.016..0.016 rows=1 loops=8)

  • Recheck Cond: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END <<= block)
  • Heap Blocks: exact=7
17. 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)
18. 0.008 7.136 ↑ 1.0 1 8

Limit (cost=427.27..427.28 rows=1 width=180) (actual time=0.892..0.892 rows=1 loops=8)

19. 0.024 7.128 ↑ 1.0 1 8

Sort (cost=427.27..427.28 rows=1 width=180) (actual time=0.891..0.891 rows=1 loops=8)

  • Sort Key: s1.stamp DESC
  • Sort Method: top-N heapsort Memory: 25kB
20. 7.104 7.104 ↓ 6.0 6 8

Seq Scan on words_social s1 (cost=0.00..427.26 rows=1 width=180) (actual time=0.094..0.888 rows=6 loops=8)

  • Filter: (uid = u1.uid)
  • Rows Removed by Filter: 10655
21. 0.008 7.376 ↑ 1.0 1 8

Limit (cost=453.92..453.93 rows=1 width=180) (actual time=0.921..0.922 rows=1 loops=8)

22. 0.016 7.368 ↑ 1.0 1 8

Sort (cost=453.92..453.93 rows=1 width=180) (actual time=0.921..0.921 rows=1 loops=8)

  • Sort Key: s2.stamp DESC
  • Sort Method: top-N heapsort Memory: 25kB
23. 7.352 7.352 ↓ 2.0 2 8

Seq Scan on words_social s2 (cost=0.00..453.91 rows=1 width=180) (actual time=0.706..0.919 rows=2 loops=8)

  • Filter: (uid = CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END)
  • Rows Removed by Filter: 10659