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