explain.depesz.com

A tool for finding a real cause for slow queries.

Result: KVr

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 132.520 362635.091 ↓ 3.9 123117 1

Sort (cost=3044088.10..3044166.20 rows=31240 width=104) (actual time=362616.368..362635.091 rows=123117 loops=1)

  • Sort Key: part.id_game
  • Sort Method: quicksort Memory: 20386kB
2. 236.659 362502.571 ↓ 3.9 123117 1

HashAggregate (cost=3041443.46..3041755.86 rows=31240 width=104) (actual time=362443.737..362502.571 rows=123117 loops=1)

3. 15.751 362265.912 ↓ 4.0 124075 1

Append (cost=1517714.92..3039803.36 rows=31240 width=104) (actual time=171519.344..362265.912 rows=124075 loops=1)

4. 44.720 172063.413 ↓ 4.0 61939 1

Subquery Scan part (cost=1517714.92..1519745.48 rows=15620 width=104) (actual time=171519.343..172063.413 rows=61939 loops=1)

  • Filter: (part.r <= 1000)
5. 488.650 172018.693 ↓ 5.4 251827 1

WindowAgg (cost=1517714.92..1519120.69 rows=46859 width=94) (actual time=171519.339..172018.693 rows=251827 loops=1)

6. 562.955 171530.043 ↓ 5.4 251827 1

Sort (cost=1517714.92..1517832.07 rows=46859 width=94) (actual time=171497.852..171530.043 rows=251827 loops=1)

  • Sort Key: public.games.w_user, public.games.id_game
  • Sort Method: quicksort Memory: 41558kB
7. 316.627 170967.088 ↓ 5.4 251827 1

Hash Join (cost=479014.57..1514079.59 rows=46859 width=94) (actual time=84313.951..170967.088 rows=251827 loops=1)

  • Hash Cond: (public.games.b_user = u2.id_user)
8. 517.354 170507.082 ↓ 5.4 251827 1

Hash Join (cost=474284.42..1508470.83 rows=46859 width=73) (actual time=84170.453..170507.082 rows=251827 loops=1)

  • Hash Cond: (public.games.w_user = u1.id_user)
9. 86015.679 169972.114 ↓ 22.3 2091568 1

Bitmap Heap Scan on games (cost=470600.70..1503498.49 rows=93718 width=52) (actual time=84152.722..169972.114 rows=2091568 loops=1)

  • Recheck Cond: ((total_time = 180::double precision) AND (increment = 0::double precision))
  • Filter: (stockfish_analysed AND stockfish_analysed AND (ply_count > 10))
10. 83956.435 83956.435 ↓ 1.2 19861183 1

Bitmap Index Scan on index_total_increment (cost=0.00..470577.28 rows=16807988 width=0) (actual time=83956.435..83956.435 rows=19861183 loops=1)

  • Index Cond: ((total_time = 180::double precision) AND (increment = 0::double precision))
11. 0.045 17.614 ↑ 1195.9 70 1

Hash (cost=2637.29..2637.29 rows=83714 width=25) (actual time=17.614..17.614 rows=70 loops=1)

12. 17.569 17.569 ↑ 1195.9 70 1

Seq Scan on users u1 (cost=0.00..2637.29 rows=83714 width=25) (actual time=16.420..17.569 rows=70 loops=1)

  • Filter: has_chronotype
13. 40.479 143.379 ↑ 1.0 167255 1

Hash (cost=2637.29..2637.29 rows=167429 width=25) (actual time=143.379..143.379 rows=167255 loops=1)

14. 102.900 102.900 ↑ 1.0 167255 1

Seq Scan on users u2 (cost=0.00..2637.29 rows=167429 width=25) (actual time=18.048..102.900 rows=167255 loops=1)

15. 44.777 190186.748 ↓ 4.0 62136 1

Subquery Scan part (cost=1517714.92..1519745.48 rows=15620 width=104) (actual time=189633.835..190186.748 rows=62136 loops=1)

  • Filter: (part.r <= 1000)
16. 474.941 190141.971 ↓ 5.4 254125 1

WindowAgg (cost=1517714.92..1519120.69 rows=46859 width=94) (actual time=189633.831..190141.971 rows=254125 loops=1)

17. 559.316 189667.030 ↓ 5.4 254125 1

Sort (cost=1517714.92..1517832.07 rows=46859 width=94) (actual time=189633.805..189667.030 rows=254125 loops=1)

  • Sort Key: public.games.b_user, public.games.id_game
  • Sort Method: quicksort Memory: 41881kB
18. 319.564 189107.714 ↓ 5.4 254125 1

Hash Join (cost=479014.57..1514079.59 rows=46859 width=94) (actual time=88681.153..189107.714 rows=254125 loops=1)

  • Hash Cond: (public.games.w_user = u1.id_user)
19. 506.938 188644.349 ↓ 5.4 254125 1

Hash Join (cost=474284.42..1508470.83 rows=46859 width=73) (actual time=88537.238..188644.349 rows=254125 loops=1)

  • Hash Cond: (public.games.b_user = u2.id_user)
20. 100573.136 188120.781 ↓ 22.3 2091568 1

Bitmap Heap Scan on games (cost=470600.70..1503498.49 rows=93718 width=52) (actual time=87742.574..188120.781 rows=2091568 loops=1)

  • Recheck Cond: ((total_time = 180::double precision) AND (increment = 0::double precision))
  • Filter: (stockfish_analysed AND stockfish_analysed AND (ply_count > 10))
21. 87547.645 87547.645 ↓ 1.2 19861183 1

Bitmap Index Scan on index_total_increment (cost=0.00..470577.28 rows=16807988 width=0) (actual time=87547.645..87547.645 rows=19861183 loops=1)

  • Index Cond: ((total_time = 180::double precision) AND (increment = 0::double precision))
22. 0.036 16.630 ↑ 1195.9 70 1

Hash (cost=2637.29..2637.29 rows=83714 width=25) (actual time=16.630..16.630 rows=70 loops=1)

23. 16.594 16.594 ↑ 1195.9 70 1

Seq Scan on users u2 (cost=0.00..2637.29 rows=83714 width=25) (actual time=15.505..16.594 rows=70 loops=1)

  • Filter: has_chronotype
24. 39.738 143.801 ↑ 1.0 167255 1

Hash (cost=2637.29..2637.29 rows=167429 width=25) (actual time=143.801..143.801 rows=167255 loops=1)

25. 104.063 104.063 ↑ 1.0 167255 1

Seq Scan on users u1 (cost=0.00..2637.29 rows=167429 width=25) (actual time=18.455..104.063 rows=167255 loops=1)