#	exclusive	inclusive	rows x	rows	loops	node
1.	133.461	417762.731	↑ 3.4	123117	1	→ Sort (cost=3412912.35..3413954.57 rows=416890 width=104) (actual time=417743.945..417762.731 rows=123117 loops=1) Sort Key: part.id_game Sort Method: quicksort Memory: 20386kB
2.	220.188	417629.270	↑ 3.4	123117	1	→ HashAggregate (cost=3347029.21..3351198.11 rows=416890 width=104) (actual time=417568.976..417629.270 rows=123117 loops=1)
3.	15.112	417409.082	↑ 3.4	124075	1	→ Append (cost=1633388.90..3325142.49 rows=416890 width=104) (actual time=212172.763..417409.082 rows=124075 loops=1)
4.	45.117	212712.161	↑ 3.4	61939	1	→ Subquery Scan part (cost=1633388.90..1660486.79 rows=208445 width=104) (actual time=212172.761..212712.161 rows=61939 loops=1) Filter: (part.r <= 1000)
5.	462.759	212667.044	↑ 2.5	251827	1	→ WindowAgg (cost=1633388.90..1652148.98 rows=625336 width=94) (actual time=212172.756..212667.044 rows=251827 loops=1)
6.	561.799	212204.285	↑ 2.5	251827	1	→ Sort (cost=1633388.90..1634952.24 rows=625336 width=94) (actual time=212172.715..212204.285 rows=251827 loops=1) Sort Key: public.games.w_user, public.games.id_game Sort Method: quicksort Memory: 41558kB
7.	318.361	211642.486	↑ 2.5	251827	1	→ Hash Join (cost=479303.81..1541123.45 rows=625336 width=94) (actual time=101610.220..211642.486 rows=251827 loops=1) Hash Cond: (public.games.b_user = u2.id_user)
8.	514.464	211151.189	↑ 2.5	251827	1	→ Hash Join (cost=474573.66..1524668.25 rows=625336 width=73) (actual time=101437.144..211151.189 rows=251827 loops=1) Hash Cond: (public.games.w_user = u1.id_user)
9.	109399.420	210618.888	↓ 1.7	2091568	1	→ Bitmap Heap Scan on games (cost=470889.95..1503787.74 rows=1250679 width=52) (actual time=101419.182..210618.888 rows=2091568 loops=1) Recheck Cond: ((total_time = 180::double precision) AND (increment = 0::double precision)) Filter: (stockfish_analysed AND (ply_count > 10))
10.	101219.468	101219.468	↓ 1.2	19861183	1	→ Bitmap Index Scan on index_total_increment (cost=0.00..470577.28 rows=16807988 width=0) (actual time=101219.468..101219.468 rows=19861183 loops=1) Index Cond: ((total_time = 180::double precision) AND (increment = 0::double precision))
11.	0.043	17.837	↑ 1195.9	70	1	→ Hash (cost=2637.29..2637.29 rows=83714 width=25) (actual time=17.837..17.837 rows=70 loops=1)
12.	17.794	17.794	↑ 1195.9	70	1	→ Seq Scan on users u1 (cost=0.00..2637.29 rows=83714 width=25) (actual time=16.632..17.794 rows=70 loops=1) Filter: has_chronotype
13.	40.209	172.936	↑ 1.0	167255	1	→ Hash (cost=2637.29..2637.29 rows=167429 width=25) (actual time=172.936..172.936 rows=167255 loops=1)
14.	132.727	132.727	↑ 1.0	167255	1	→ Seq Scan on users u2 (cost=0.00..2637.29 rows=167429 width=25) (actual time=47.588..132.727 rows=167255 loops=1)
15.	45.156	204681.809	↑ 3.4	62136	1	→ Subquery Scan part (cost=1633388.90..1660486.79 rows=208445 width=104) (actual time=204131.731..204681.809 rows=62136 loops=1) Filter: (part.r <= 1000)
16.	472.304	204636.653	↑ 2.5	254125	1	→ WindowAgg (cost=1633388.90..1652148.98 rows=625336 width=94) (actual time=204131.728..204636.653 rows=254125 loops=1)
17.	560.190	204164.349	↑ 2.5	254125	1	→ Sort (cost=1633388.90..1634952.24 rows=625336 width=94) (actual time=204131.703..204164.349 rows=254125 loops=1) Sort Key: public.games.b_user, public.games.id_game Sort Method: quicksort Memory: 41881kB
18.	314.143	203604.159	↑ 2.5	254125	1	→ Hash Join (cost=479303.81..1541123.45 rows=625336 width=94) (actual time=96089.697..203604.159 rows=254125 loops=1) Hash Cond: (public.games.w_user = u1.id_user)
19.	511.749	203137.689	↑ 2.5	254125	1	→ Hash Join (cost=474573.66..1524668.25 rows=625336 width=73) (actual time=95937.255..203137.689 rows=254125 loops=1) Hash Cond: (public.games.b_user = u2.id_user)
20.	107750.221	202609.286	↓ 1.7	2091568	1	→ Bitmap Heap Scan on games (cost=470889.95..1503787.74 rows=1250679 width=52) (actual time=95053.682..202609.286 rows=2091568 loops=1) Recheck Cond: ((total_time = 180::double precision) AND (increment = 0::double precision)) Filter: (stockfish_analysed AND (ply_count > 10))
21.	94859.065	94859.065	↓ 1.2	19861183	1	→ Bitmap Index Scan on index_total_increment (cost=0.00..470577.28 rows=16807988 width=0) (actual time=94859.065..94859.065 rows=19861183 loops=1) Index Cond: ((total_time = 180::double precision) AND (increment = 0::double precision))
22.	0.042	16.654	↑ 1195.9	70	1	→ Hash (cost=2637.29..2637.29 rows=83714 width=25) (actual time=16.654..16.654 rows=70 loops=1)
23.	16.612	16.612	↑ 1195.9	70	1	→ Seq Scan on users u2 (cost=0.00..2637.29 rows=83714 width=25) (actual time=15.524..16.612 rows=70 loops=1) Filter: has_chronotype
24.	40.802	152.327	↑ 1.0	167255	1	→ Hash (cost=2637.29..2637.29 rows=167429 width=25) (actual time=152.327..152.327 rows=167255 loops=1)
25.	111.525	111.525	↑ 1.0	167255	1	→ Seq Scan on users u1 (cost=0.00..2637.29 rows=167429 width=25) (actual time=15.872..111.525 rows=167255 loops=1)

 Sort  (cost=3412912.35..3413954.57 rows=416890 width=104) (actual time=417743.945..417762.731 rows=123117 loops=1)
   Sort Key: part.id_game
   Sort Method:  quicksort  Memory: 20386kB
   ->  HashAggregate  (cost=3347029.21..3351198.11 rows=416890 width=104) (actual time=417568.976..417629.270 rows=123117 loops=1)
         ->  Append  (cost=1633388.90..3325142.49 rows=416890 width=104) (actual time=212172.763..417409.082 rows=124075 loops=1)
               ->  Subquery Scan part  (cost=1633388.90..1660486.79 rows=208445 width=104) (actual time=212172.761..212712.161 rows=61939 loops=1)
                     Filter: (part.r <= 1000)
                     ->  WindowAgg  (cost=1633388.90..1652148.98 rows=625336 width=94) (actual time=212172.756..212667.044 rows=251827 loops=1)
                           ->  Sort  (cost=1633388.90..1634952.24 rows=625336 width=94) (actual time=212172.715..212204.285 rows=251827 loops=1)
                                 Sort Key: public.games.w_user, public.games.id_game
                                 Sort Method:  quicksort  Memory: 41558kB
                                 ->  Hash Join  (cost=479303.81..1541123.45 rows=625336 width=94) (actual time=101610.220..211642.486 rows=251827 loops=1)
                                       Hash Cond: (public.games.b_user = u2.id_user)
                                       ->  Hash Join  (cost=474573.66..1524668.25 rows=625336 width=73) (actual time=101437.144..211151.189 rows=251827 loops=1)
                                             Hash Cond: (public.games.w_user = u1.id_user)
                                             ->  Bitmap Heap Scan on games  (cost=470889.95..1503787.74 rows=1250679 width=52) (actual time=101419.182..210618.888 rows=2091568 loops=1)
                                                   Recheck Cond: ((total_time = 180::double precision) AND (increment = 0::double precision))
                                                   Filter: (stockfish_analysed AND (ply_count > 10))
                                                   ->  Bitmap Index Scan on index_total_increment  (cost=0.00..470577.28 rows=16807988 width=0) (actual time=101219.468..101219.468 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.837..17.837 rows=70 loops=1)
                                                   ->  Seq Scan on users u1  (cost=0.00..2637.29 rows=83714 width=25) (actual time=16.632..17.794 rows=70 loops=1)
                                                         Filter: has_chronotype
                                       ->  Hash  (cost=2637.29..2637.29 rows=167429 width=25) (actual time=172.936..172.936 rows=167255 loops=1)
                                             ->  Seq Scan on users u2  (cost=0.00..2637.29 rows=167429 width=25) (actual time=47.588..132.727 rows=167255 loops=1)
               ->  Subquery Scan part  (cost=1633388.90..1660486.79 rows=208445 width=104) (actual time=204131.731..204681.809 rows=62136 loops=1)
                     Filter: (part.r <= 1000)
                     ->  WindowAgg  (cost=1633388.90..1652148.98 rows=625336 width=94) (actual time=204131.728..204636.653 rows=254125 loops=1)
                           ->  Sort  (cost=1633388.90..1634952.24 rows=625336 width=94) (actual time=204131.703..204164.349 rows=254125 loops=1)
                                 Sort Key: public.games.b_user, public.games.id_game

                                 Sort Method:  quicksort  Memory: 41881kB
                                 ->  Hash Join  (cost=479303.81..1541123.45 rows=625336 width=94) (actual time=96089.697..203604.159 rows=254125 loops=1)
                                       Hash Cond: (public.games.w_user = u1.id_user)
                                       ->  Hash Join  (cost=474573.66..1524668.25 rows=625336 width=73) (actual time=95937.255..203137.689 rows=254125 loops=1)
                                             Hash Cond: (public.games.b_user = u2.id_user)
                                             ->  Bitmap Heap Scan on games  (cost=470889.95..1503787.74 rows=1250679 width=52) (actual time=95053.682..202609.286 rows=2091568 loops=1)
                                                   Recheck Cond: ((total_time = 180::double precision) AND (increment = 0::double precision))
                                                   Filter: (stockfish_analysed AND (ply_count > 10))
                                                   ->  Bitmap Index Scan on index_total_increment  (cost=0.00..470577.28 rows=16807988 width=0) (actual time=94859.065..94859.065 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.654..16.654 rows=70 loops=1)
                                                   ->  Seq Scan on users u2  (cost=0.00..2637.29 rows=83714 width=25) (actual time=15.524..16.612 rows=70 loops=1)
                                                         Filter: has_chronotype
                                       ->  Hash  (cost=2637.29..2637.29 rows=167429 width=25) (actual time=152.327..152.327 rows=167255 loops=1)
                                             ->  Seq Scan on users u1  (cost=0.00..2637.29 rows=167429 width=25) (actual time=15.872..111.525 rows=167255 loops=1)
 Total runtime: 417789.514 ms

Per node type stats

node type	count	sum of times	% of query
Append	1	15.112 ms	0.0 %
Bitmap Heap Scan	2	217149.641 ms	52.0 %
Bitmap Index Scan	2	196078.533 ms	46.9 %
Hash	4	81.096 ms	0.0 %
Hash Join	4	1658.717 ms	0.4 %
HashAggregate	1	220.188 ms	0.1 %
Seq Scan	4	278.658 ms	0.1 %
Sort	3	1255.450 ms	0.3 %
Subquery Scan part	2	90.273 ms	0.0 %
WindowAgg	2	935.063 ms	0.2 %

Per table stats

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

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: CXA

Colorize:

Columns visibility:

Per node type stats

Per table stats