Django

Code

Ticket #3575: 3575-postgresql-performance.txt

File 3575-postgresql-performance.txt, 4.7 kB (added by Collin Grady <cgrady@the-magi.us>, 7 months ago)

example of performance boost on postgresql

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