Code

Ticket #3575: 3575-postgresql-performance.txt

File 3575-postgresql-performance.txt, 4.7 KB (added by Collin Grady <cgrady@…>, 6 years ago)

example of performance boost on postgresql

Line 
1chesspark=# 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
10chesspark=# 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
19chesspark=# CREATE INDEX game_lower_white_player_idx ON game ((lower(white_player)));
20CREATE INDEX
21chesspark=# CREATE INDEX game_lower_black_player_idx ON game ((lower(black_player)));
22CREATE INDEX
23chesspark=# 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
32chesspark=# 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)