#	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)

 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
   ->  HashAggregate  (cost=3041443.46..3041755.86 rows=31240 width=104) (actual time=362443.737..362502.571 rows=123117 loops=1)
         ->  Append  (cost=1517714.92..3039803.36 rows=31240 width=104) (actual time=171519.344..362265.912 rows=124075 loops=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)
                     ->  WindowAgg  (cost=1517714.92..1519120.69 rows=46859 width=94) (actual time=171519.339..172018.693 rows=251827 loops=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
                                 ->  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)
                                       ->  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)
                                             ->  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))
                                                   ->  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))
                                             ->  Hash  (cost=2637.29..2637.29 rows=83714 width=25) (actual time=17.614..17.614 rows=70 loops=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
                                       ->  Hash  (cost=2637.29..2637.29 rows=167429 width=25) (actual time=143.379..143.379 rows=167255 loops=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)
               ->  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)
                     ->  WindowAgg  (cost=1517714.92..1519120.69 rows=46859 width=94) (actual time=189633.831..190141.971 rows=254125 loops=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
                                 ->  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)
                                       ->  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)
                                             ->  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))
                                                   ->  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))
                                             ->  Hash  (cost=2637.29..2637.29 rows=83714 width=25) (actual time=16.630..16.630 rows=70 loops=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
                                       ->  Hash  (cost=2637.29..2637.29 rows=167429 width=25) (actual time=143.801..143.801 rows=167255 loops=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)
 Total runtime: 362659.860 ms

Per node type stats

node type	count	sum of times	% of query
Append	1	15.751 ms	0.0 %
Bitmap Heap Scan	2	186588.815 ms	51.5 %
Bitmap Index Scan	2	171504.080 ms	47.3 %
Hash	4	80.298 ms	0.0 %
Hash Join	4	1660.483 ms	0.5 %
HashAggregate	1	236.659 ms	0.1 %
Seq Scan	4	241.126 ms	0.1 %
Sort	3	1254.791 ms	0.3 %
Subquery Scan part	2	89.497 ms	0.0 %
WindowAgg	2	963.591 ms	0.3 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
games	2	186588.815 ms	51.5 %
Bitmap Heap Scan	2	186588.815 ms	100.0 %
users	4	241.126 ms	0.1 %
Seq Scan	4	241.126 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: KVr

Colorize:

Columns visibility:

Per node type stats

Per table stats