#	rows x	node
1.	↓ 0.0	→ Sort (cost=3044088.10..3044166.20 rows=31240 width=104) (actual time=.. rows= loops=) Sort Key: part.id_game
2.	↓ 0.0	→ HashAggregate (cost=3041443.46..3041755.86 rows=31240 width=104) (actual time=.. rows= loops=)
3.	↓ 0.0	→ Append (cost=1517714.92..3039803.36 rows=31240 width=104) (actual time=.. rows= loops=)
4.	↓ 0.0	→ Subquery Scan part (cost=1517714.92..1519745.48 rows=15620 width=104) (actual time=.. rows= loops=) Filter: (part.r <= 1000)
5.	↓ 0.0	→ WindowAgg (cost=1517714.92..1519120.69 rows=46859 width=94) (actual time=.. rows= loops=)
6.	↓ 0.0	→ Sort (cost=1517714.92..1517832.07 rows=46859 width=94) (actual time=.. rows= loops=) Sort Key: public.games.w_user, public.games.id_game
7.	↓ 0.0	→ Hash Join (cost=479014.57..1514079.59 rows=46859 width=94) (actual time=.. rows= loops=) Hash Cond: (public.games.b_user = u2.id_user)
8.	↓ 0.0	→ Hash Join (cost=474284.42..1508470.83 rows=46859 width=73) (actual time=.. rows= loops=) Hash Cond: (public.games.w_user = u1.id_user)
9.	↓ 0.0	→ Bitmap Heap Scan on games (cost=470600.70..1503498.49 rows=93718 width=52) (actual time=.. rows= loops=) Recheck Cond: ((total_time = 180::double precision) AND (increment = 0::double precision)) Filter: (stockfish_analysed AND stockfish_analysed AND (ply_count > 10))
10.	↓ 0.0	→ Bitmap Index Scan on index_total_increment (cost=0.00..470577.28 rows=16807988 width=0) (actual time=.. rows= loops=) Index Cond: ((total_time = 180::double precision) AND (increment = 0::double precision))
11.	↓ 0.0	→ Hash (cost=2637.29..2637.29 rows=83714 width=25) (actual time=.. rows= loops=)
12.	↓ 0.0	→ Seq Scan on users u1 (cost=0.00..2637.29 rows=83714 width=25) (actual time=.. rows= loops=) Filter: has_chronotype
13.	↓ 0.0	→ Hash (cost=2637.29..2637.29 rows=167429 width=25) (actual time=.. rows= loops=)
14.	↓ 0.0	→ Seq Scan on users u2 (cost=0.00..2637.29 rows=167429 width=25) (actual time=.. rows= loops=)
15.	↓ 0.0	→ Subquery Scan part (cost=1517714.92..1519745.48 rows=15620 width=104) (actual time=.. rows= loops=) Filter: (part.r <= 1000)
16.	↓ 0.0	→ WindowAgg (cost=1517714.92..1519120.69 rows=46859 width=94) (actual time=.. rows= loops=)
17.	↓ 0.0	→ Sort (cost=1517714.92..1517832.07 rows=46859 width=94) (actual time=.. rows= loops=) Sort Key: public.games.b_user, public.games.id_game
18.	↓ 0.0	→ Hash Join (cost=479014.57..1514079.59 rows=46859 width=94) (actual time=.. rows= loops=) Hash Cond: (public.games.w_user = u1.id_user)
19.	↓ 0.0	→ Hash Join (cost=474284.42..1508470.83 rows=46859 width=73) (actual time=.. rows= loops=) Hash Cond: (public.games.b_user = u2.id_user)
20.	↓ 0.0	→ Bitmap Heap Scan on games (cost=470600.70..1503498.49 rows=93718 width=52) (actual time=.. rows= loops=) Recheck Cond: ((total_time = 180::double precision) AND (increment = 0::double precision)) Filter: (stockfish_analysed AND stockfish_analysed AND (ply_count > 10))
21.	↓ 0.0	→ Bitmap Index Scan on index_total_increment (cost=0.00..470577.28 rows=16807988 width=0) (actual time=.. rows= loops=) Index Cond: ((total_time = 180::double precision) AND (increment = 0::double precision))
22.	↓ 0.0	→ Hash (cost=2637.29..2637.29 rows=83714 width=25) (actual time=.. rows= loops=)
23.	↓ 0.0	→ Seq Scan on users u2 (cost=0.00..2637.29 rows=83714 width=25) (actual time=.. rows= loops=) Filter: has_chronotype
24.	↓ 0.0	→ Hash (cost=2637.29..2637.29 rows=167429 width=25) (actual time=.. rows= loops=)
25.	↓ 0.0	→ Seq Scan on users u1 (cost=0.00..2637.29 rows=167429 width=25) (actual time=.. rows= loops=)

Sort  (cost=3044088.10..3044166.20 rows=31240 width=104)
   Sort Key: part.id_game
   ->  HashAggregate  (cost=3041443.46..3041755.86 rows=31240 width=104)
         ->  Append  (cost=1517714.92..3039803.36 rows=31240 width=104)
               ->  Subquery Scan part  (cost=1517714.92..1519745.48 rows=15620 width=104)
                     Filter: (part.r <= 1000)
                     ->  WindowAgg  (cost=1517714.92..1519120.69 rows=46859 width=94)
                           ->  Sort  (cost=1517714.92..1517832.07 rows=46859 width=94)
                                 Sort Key: public.games.w_user, public.games.id_game
                                 ->  Hash Join  (cost=479014.57..1514079.59 rows=46859 width=94)
                                       Hash Cond: (public.games.b_user = u2.id_user)
                                       ->  Hash Join  (cost=474284.42..1508470.83 rows=46859 width=73)
                                             Hash Cond: (public.games.w_user = u1.id_user)
                                             ->  Bitmap Heap Scan on games  (cost=470600.70..1503498.49 rows=93718 width=52)
                                                   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)
                                                         Index Cond: ((total_time = 180::double precision) AND (increment = 0::double precision))
                                             ->  Hash  (cost=2637.29..2637.29 rows=83714 width=25)
                                                   ->  Seq Scan on users u1  (cost=0.00..2637.29 rows=83714 width=25)
                                                         Filter: has_chronotype
                                       ->  Hash  (cost=2637.29..2637.29 rows=167429 width=25)
                                             ->  Seq Scan on users u2  (cost=0.00..2637.29 rows=167429 width=25)
               ->  Subquery Scan part  (cost=1517714.92..1519745.48 rows=15620 width=104)
                     Filter: (part.r <= 1000)
                     ->  WindowAgg  (cost=1517714.92..1519120.69 rows=46859 width=94)
                           ->  Sort  (cost=1517714.92..1517832.07 rows=46859 width=94)
                                 Sort Key: public.games.b_user, public.games.id_game
                                 ->  Hash Join  (cost=479014.57..1514079.59 rows=46859 width=94)
                                       Hash Cond: (public.games.w_user = u1.id_user)
                                       ->  Hash Join  (cost=474284.42..1508470.83 rows=46859 width=73)
                                             Hash Cond: (public.games.b_user = u2.id_user)
                                             ->  Bitmap Heap Scan on games  (cost=470600.70..1503498.49 rows=93718 width=52)
                                                   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)
                                                         Index Cond: ((total_time = 180::double precision) AND (increment = 0::double precision))
                                             ->  Hash  (cost=2637.29..2637.29 rows=83714 width=25)

                                                ->  Seq Scan on users u2  (cost=0.00..2637.29 rows=83714 width=25)
                                                         Filter: has_chronotype
                                       ->  Hash  (cost=2637.29..2637.29 rows=167429 width=25)
                                             ->  Seq Scan on users u1  (cost=0.00..2637.29 rows=167429 width=25)

Per node type stats

node type	count	sum of times	% of query
Append	1	0.000 ms	0.0 %
Bitmap Heap Scan	2	0.000 ms	0.0 %
Bitmap Index Scan	2	0.000 ms	0.0 %
Hash	4	0.000 ms	0.0 %
Hash Join	4	0.000 ms	0.0 %
HashAggregate	1	0.000 ms	0.0 %
Seq Scan	4	0.000 ms	0.0 %
Sort	3	0.000 ms	0.0 %
Subquery Scan part	2	0.000 ms	0.0 %
WindowAgg	2	0.000 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
games	2	0.000 ms	0.0 %
Bitmap Heap Scan	2	0.000 ms	0.0 %
users	4	0.000 ms	0.0 %
Seq Scan	4	0.000 ms	0.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: mRY

Colorize:

Columns visibility:

Per node type stats

Per table stats