explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PrF

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 842.446 ↑ 1.0 30 1

Limit (cost=89,502.34..272,686.22 rows=30 width=160) (actual time=215.796..842.446 rows=30 loops=1)

2.          

CTE last_week_moves

3. 9.254 193.613 ↓ 90.5 33,221 1

WindowAgg (cost=87,308.08..87,316.34 rows=367 width=32) (actual time=183.075..193.613 rows=33,221 loops=1)

4. 11.532 184.359 ↓ 90.5 33,221 1

Sort (cost=87,308.08..87,309.00 rows=367 width=16) (actual time=183.069..184.359 rows=33,221 loops=1)

  • Sort Key: m_1.gid, m_1.played
  • Sort Method: quicksort Memory: 4132kB
5. 4.151 172.827 ↓ 90.5 33,221 1

Gather (cost=13,632.94..87,292.45 rows=367 width=16) (actual time=37.204..172.827 rows=33,221 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 2.596 168.676 ↓ 72.4 11,074 3

Hash Join (cost=12,632.94..86,255.75 rows=153 width=16) (actual time=36.183..168.676 rows=11,074 loops=3)

  • Hash Cond: (m_1.gid = g.gid)
  • Join Filter: ((m_1.uid = g.player1) OR (m_1.uid = g.player2))
7. 130.844 130.844 ↓ 1.3 11,074 3

Parallel Seq Scan on words_moves m_1 (cost=0.00..73,600.05 rows=8,666 width=16) (actual time=0.761..130.844 rows=11,074 loops=3)

  • Filter: (played > (CURRENT_TIMESTAMP - '7 days'::interval))
  • Rows Removed by Filter: 492241
8. 6.255 35.236 ↓ 1.0 50,044 3

Hash (cost=12,007.42..12,007.42 rows=50,042 width=12) (actual time=35.236..35.236 rows=50,044 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 2663kB
9. 28.981 28.981 ↓ 1.0 50,044 3

Seq Scan on words_games g (cost=0.00..12,007.42 rows=50,042 width=12) (actual time=0.007..28.981 rows=50,044 loops=3)

10. 0.168 842.425 ↑ 7.0 30 1

Result (cost=2,186.01..1,278,367.01 rows=209 width=160) (actual time=215.795..842.425 rows=30 loops=1)

11. 0.047 205.087 ↑ 7.0 30 1

Sort (cost=2,186.01..2,186.53 rows=209 width=96) (actual time=205.069..205.087 rows=30 loops=1)

  • Sort Key: u.elo DESC
  • Sort Method: top-N heapsort Memory: 32kB
12. 0.039 205.040 ↑ 1.9 110 1

Nested Loop Left Join (cost=12.19..2,179.83 rows=209 width=96) (actual time=203.009..205.040 rows=110 loops=1)

13. 0.054 204.451 ↑ 1.9 110 1

Hash Semi Join (cost=11.90..440.43 rows=209 width=8) (actual time=202.984..204.451 rows=110 loops=1)

  • Hash Cond: (u.uid = m.uid)
14. 1.422 1.422 ↑ 1.0 418 1

Seq Scan on words_users u (cost=0.00..415.96 rows=418 width=8) (actual time=0.005..1.422 rows=418 loops=1)

  • Filter: (elo > 1500)
  • Rows Removed by Filter: 10139
15. 2.778 202.975 ↓ 86.4 31,549 1

Hash (cost=7.34..7.34 rows=365 width=4) (actual time=202.975..202.975 rows=31,549 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1366kB
16. 200.197 200.197 ↓ 86.4 31,549 1

CTE Scan on last_week_moves m (cost=0.00..7.34 rows=365 width=4) (actual time=183.080..200.197 rows=31,549 loops=1)

  • Filter: (diff IS NOT NULL)
  • Rows Removed by Filter: 1672
17. 0.000 0.550 ↑ 1.0 1 110

Limit (cost=0.29..8.30 rows=1 width=180) (actual time=0.005..0.005 rows=1 loops=110)

18. 0.550 0.550 ↑ 1.0 1 110

Index Scan using words_social_uid_stamp_idx on words_social s (cost=0.29..8.30 rows=1 width=180) (actual time=0.005..0.005 rows=1 loops=110)

  • Index Cond: (uid = u.uid)
19.          

SubPlan (forResult)

20. 1.890 55.140 ↑ 1.0 1 30

Aggregate (cost=8.27..8.28 rows=1 width=32) (actual time=1.838..1.838 rows=1 loops=30)

21. 53.250 53.250 ↓ 201.5 403 30

CTE Scan on last_week_moves (cost=0.00..8.26 rows=2 width=16) (actual time=0.176..1.775 rows=403 loops=30)

  • Filter: (uid = u.uid)
  • Rows Removed by Filter: 32818
22. 37.440 582.030 ↑ 1.0 1 30

Aggregate (cost=6,097.83..6,097.84 rows=1 width=32) (actual time=19.401..19.401 rows=1 loops=30)

23. 515.220 544.590 ↓ 8.6 15,011 30

Bitmap Heap Scan on words_moves (cost=33.97..6,093.45 rows=1,748 width=4) (actual time=1.680..18.153 rows=15,011 loops=30)

  • Recheck Cond: (uid = u.uid)
  • Heap Blocks: exact=216312
24. 29.370 29.370 ↓ 8.6 15,011 30

Bitmap Index Scan on words_moves_uid_idx (cost=0.00..33.54 rows=1,748 width=0) (actual time=0.979..0.979 rows=15,011 loops=30)

  • Index Cond: (uid = u.uid)