1 | chesspark=# explain analyze select count(id) from game where white_player ilike 'trainer@chesspark.com/TrainingBot' or black_player ilike 'trainer@chesspark.com/TrainingBot';
|
---|
2 | QUERY PLAN
|
---|
3 | ----------------------------------------------------------------------------------------------------------------------------------------------------------------
|
---|
4 | Aggregate (cost=12173.96..12173.97 rows=1 width=4) (actual time=3870.045..3870.046 rows=1 loops=1)
|
---|
5 | -> Seq Scan on game (cost=0.00..12146.28 rows=11072 width=4) (actual time=0.050..3865.897 rows=11771 loops=1)
|
---|
6 | Filter: (((white_player)::text ~~* 'trainer@chesspark.com/TrainingBot'::text) OR ((black_player)::text ~~* 'trainer@chesspark.com/TrainingBot'::text))
|
---|
7 | Total runtime: 3870.100 ms
|
---|
8 | (4 rows)
|
---|
9 |
|
---|
10 | chesspark=# explain analyze select count(white_player) from game where lower(white_player) = lower('trainer@chesspark.com/TrainingBot') or lower(black_player) = lower('trainer@chesspark.com/TrainingBot');
|
---|
11 | QUERY PLAN
|
---|
12 | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
---|
13 | Aggregate (cost=13662.57..13662.58 rows=1 width=22) (actual time=1914.515..1914.515 rows=1 loops=1)
|
---|
14 | -> Seq Scan on game (cost=0.00..13655.04 rows=3010 width=22) (actual time=0.041..1910.418 rows=11771 loops=1)
|
---|
15 | Filter: ((lower((white_player)::text) = 'trainer@chesspark.com/trainingbot'::text) OR (lower((black_player)::text) = 'trainer@chesspark.com/trainingbot'::text))
|
---|
16 | Total runtime: 1914.569 ms
|
---|
17 | (4 rows)
|
---|
18 |
|
---|
19 | chesspark=# CREATE INDEX game_lower_white_player_idx ON game ((lower(white_player)));
|
---|
20 | CREATE INDEX
|
---|
21 | chesspark=# CREATE INDEX game_lower_black_player_idx ON game ((lower(black_player)));
|
---|
22 | CREATE INDEX
|
---|
23 | chesspark=# explain analyze select count(id) from game where white_player ilike 'trainer@chesspark.com/TrainingBot' or black_player ilike 'trainer@chesspark.com/TrainingBot';
|
---|
24 | QUERY PLAN
|
---|
25 | ----------------------------------------------------------------------------------------------------------------------------------------------------------------
|
---|
26 | Aggregate (cost=12166.26..12166.27 rows=1 width=4) (actual time=3908.237..3908.237 rows=1 loops=1)
|
---|
27 | -> Seq Scan on game (cost=0.00..12138.63 rows=11053 width=4) (actual time=0.036..3873.797 rows=11771 loops=1)
|
---|
28 | Filter: (((white_player)::text ~~* 'trainer@chesspark.com/TrainingBot'::text) OR ((black_player)::text ~~* 'trainer@chesspark.com/TrainingBot'::text))
|
---|
29 | Total runtime: 3908.290 ms
|
---|
30 | (4 rows)
|
---|
31 |
|
---|
32 | chesspark=# explain analyze select count(white_player) from game where lower(white_player) = lower('trainer@chesspark.com/TrainingBot') or lower(black_player) = lower('trainer@chesspark.com/TrainingBot');
|
---|
33 | QUERY PLAN
|
---|
34 | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
---|
35 | Aggregate (cost=5881.86..5881.87 rows=1 width=22) (actual time=108.183..108.183 rows=1 loops=1)
|
---|
36 | -> Bitmap Heap Scan on game (cost=88.72..5874.34 rows=3005 width=22) (actual time=4.734..103.221 rows=11771 loops=1)
|
---|
37 | Recheck Cond: ((lower((white_player)::text) = 'trainer@chesspark.com/trainingbot'::text) OR (lower((black_player)::text) = 'trainer@chesspark.com/trainingbot'::text))
|
---|
38 | -> BitmapOr (cost=88.72..88.72 rows=3012 width=0) (actual time=3.926..3.926 rows=0 loops=1)
|
---|
39 | -> Bitmap Index Scan on game_lower_white_player_idx (cost=0.00..43.61 rows=1506 width=0) (actual time=1.877..1.877 rows=4942 loops=1)
|
---|
40 | Index Cond: (lower((white_player)::text) = 'trainer@chesspark.com/trainingbot'::text)
|
---|
41 | -> Bitmap Index Scan on game_lower_black_player_idx (cost=0.00..43.61 rows=1506 width=0) (actual time=2.046..2.046 rows=6829 loops=1)
|
---|
42 | Index Cond: (lower((black_player)::text) = 'trainer@chesspark.com/trainingbot'::text)
|
---|
43 | Total runtime: 108.247 ms
|
---|
44 | (9 rows)
|
---|