explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9uUG

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 25.808 ↑ 1.0 10 1

Limit (cost=10,838.57..10,838.59 rows=10 width=219) (actual time=25.805..25.808 rows=10 loops=1)

2. 0.831 25.805 ↑ 182.9 10 1

Sort (cost=10,838.57..10,843.14 rows=1,829 width=219) (actual time=25.804..25.805 rows=10 loops=1)

  • Sort Key: (length(s.word)) DESC
  • Sort Method: top-N heapsort Memory: 29kB
3. 1.316 24.974 ↓ 2.0 3,705 1

Nested Loop (cost=537.30..10,799.04 rows=1,829 width=219) (actual time=2.541..24.974 rows=3,705 loops=1)

4. 2.261 23.658 ↓ 8.1 3,705 1

Nested Loop (cost=536.44..10,709.46 rows=457 width=127) (actual time=2.519..23.658 rows=3,705 loops=1)

5. 0.000 17.692 ↓ 8.1 3,705 1

Nested Loop Anti Join (cost=536.01..6,990.09 rows=457 width=131) (actual time=2.512..17.692 rows=3,705 loops=1)

6. 1.124 13.063 ↓ 7.9 5,389 1

Nested Loop (cost=535.73..6,695.13 rows=686 width=139) (actual time=2.507..13.063 rows=5,389 loops=1)

7. 0.836 8.237 ↓ 5.5 3,702 1

Hash Join (cost=535.44..6,399.05 rows=679 width=47) (actual time=2.501..8.237 rows=3,702 loops=1)

  • Hash Cond: (CASE WHEN (g.player1 = s.uid) THEN g.player2 ELSE g.player1 END = u2.uid)
8. 0.357 5.116 ↓ 5.5 3,702 1

Nested Loop (cost=13.98..5,875.72 rows=679 width=39) (actual time=0.179..5.116 rows=3,702 loops=1)

9. 0.912 1.057 ↓ 5.5 3,702 1

Bitmap Heap Scan on words_scores s (cost=13.69..2,270.92 rows=679 width=23) (actual time=0.174..1.057 rows=3,702 loops=1)

  • Recheck Cond: (uid = 5)
  • Heap Blocks: exact=419
10. 0.145 0.145 ↓ 5.5 3,702 1

Bitmap Index Scan on words_scores_uid_idx (cost=0.00..13.52 rows=679 width=0) (actual time=0.145..0.145 rows=3,702 loops=1)

  • Index Cond: (uid = 5)
11. 3.702 3.702 ↑ 1.0 1 3,702

Index Scan using words_games_pkey on words_games g (cost=0.29..5.31 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=3,702)

  • Index Cond: (gid = s.gid)
12. 0.958 2.285 ↓ 1.0 10,555 1

Hash (cost=389.54..389.54 rows=10,554 width=8) (actual time=2.285..2.285 rows=10,555 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 541kB
13. 1.327 1.327 ↓ 1.0 10,555 1

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

14. 3.702 3.702 ↑ 1.0 1 3,702

Index Scan using words_social_uid_stamp_idx on words_social s2 (cost=0.29..0.43 rows=1 width=96) (actual time=0.001..0.001 rows=1 loops=3,702)

  • Index Cond: (uid = u2.uid)
15. 5.389 5.389 ↓ 0.0 0 5,389

Index Only Scan using words_social_uid_stamp_idx on words_social x_1 (cost=0.29..0.43 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=5,389)

  • Index Cond: ((uid = s2.uid) AND (stamp > s2.stamp))
  • Heap Fetches: 1684
16. 3.705 3.705 ↑ 1.0 1 3,705

Index Scan using words_moves_pkey on words_moves m (cost=0.43..8.14 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=3,705)

  • Index Cond: (mid = s.mid)
17. 0.000 0.000 ↑ 4.0 1 3,705

Materialize (cost=0.86..43.88 rows=4 width=100) (actual time=0.000..0.000 rows=1 loops=3,705)

18. 0.000 0.022 ↑ 4.0 1 1

Nested Loop (cost=0.86..43.86 rows=4 width=100) (actual time=0.019..0.022 rows=1 loops=1)

19. 0.005 0.005 ↑ 1.0 1 1

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

  • Index Cond: (uid = 5)
20. 0.000 0.017 ↑ 4.0 1 1

Nested Loop Anti Join (cost=0.57..35.52 rows=4 width=92) (actual time=0.014..0.017 rows=1 loops=1)

  • Join Filter: ((x.stamp > s1.stamp) AND (s1.uid = x.uid))
  • Rows Removed by Join Filter: 6
21. 0.005 0.005 ↑ 1.0 6 1

Index Scan using words_social_uid_stamp_idx on words_social s1 (cost=0.29..17.51 rows=6 width=96) (actual time=0.003..0.005 rows=6 loops=1)

  • Index Cond: (uid = 5)
22. 0.005 0.012 ↑ 3.0 2 6

Materialize (cost=0.29..17.54 rows=6 width=8) (actual time=0.001..0.002 rows=2 loops=6)

23. 0.007 0.007 ↑ 1.0 6 1

Index Only Scan using words_social_uid_stamp_idx on words_social x (cost=0.29..17.51 rows=6 width=8) (actual time=0.004..0.007 rows=6 loops=1)

  • Index Cond: (uid = 5)
  • Heap Fetches: 6