1. 22 Feb, 2018 1 commit
    • Yorick Peterse's avatar
      Optimise searching for users using short queries · 41bfe82b
      Yorick Peterse authored
      This optimises searching for users when using queries consisting out of
      one or two characters such as "ab". We optimise such cases by searching
      for `LOWER(name)` and `LOWER(username)` instead of using `ILIKE`. Using
      `LOWER` produces a _much_ better performing query.
      
      For example, when searching for all users matching the term "a" we'd
      produce the following plan:
      
           Limit  (cost=637.69..637.74 rows=20 width=805) (actual time=41.983..41.995 rows=20 loops=1)
             Buffers: shared hit=8330
             ->  Sort  (cost=637.69..638.61 rows=368 width=805) (actual time=41.982..41.990 rows=20 loops=1)
                   Sort Key: (CASE WHEN ((name)::text = 'a'::text) THEN 0 WHEN ((username)::text = 'a'::text) THEN 1 WHEN ((email)::text = 'a'::text) THEN 2 ELSE 3 END), name
                   Sort Method: top-N heapsort  Memory: 35kB
                   Buffers: shared hit=8330
                   ->  Bitmap Heap Scan on users  (cost=75.47..627.89 rows=368 width=805) (actual time=9.452..41.305 rows=277 loops=1)
                         Recheck Cond: (((name)::text ~~* 'a'::text) OR ((username)::text ~~* 'a'::text) OR ((email)::text = 'a'::text))
                         Rows Removed by Index Recheck: 7601
                         Heap Blocks: exact=7636
                         Buffers: shared hit=8327
                         ->  BitmapOr  (cost=75.47..75.47 rows=368 width=0) (actual time=8.290..8.290 rows=0 loops=1)
                               Buffers: shared hit=691
                               ->  Bitmap Index Scan on index_users_on_name_trigram  (cost=0.00..38.85 rows=180 width=0) (actual time=4.369..4.369 rows=4071 loops=1)
                                     Index Cond: ((name)::text ~~* 'a'::text)
                                     Buffers: shared hit=360
                               ->  Bitmap Index Scan on index_users_on_username_trigram  (cost=0.00..34.41 rows=188 width=0) (actual time=3.896..3.896 rows=4140 loops=1)
                                     Index Cond: ((username)::text ~~* 'a'::text)
                                     Buffers: shared hit=328
                               ->  Bitmap Index Scan on users_email_key  (cost=0.00..1.94 rows=1 width=0) (actual time=0.022..0.022 rows=0 loops=1)
                                     Index Cond: ((email)::text = 'a'::text)
                                     Buffers: shared hit=3
           Planning time: 3.912 ms
           Execution time: 42.171 ms
      
      With the changes in this commit we now produce the following plan
      instead:
      
           Limit  (cost=13257.48..13257.53 rows=20 width=805) (actual time=1.567..1.579 rows=20 loops=1)
             Buffers: shared hit=287
             ->  Sort  (cost=13257.48..13280.93 rows=9379 width=805) (actual time=1.567..1.572 rows=20 loops=1)
                   Sort Key: (CASE WHEN ((name)::text = 'a'::text) THEN 0 WHEN ((username)::text = 'a'::text) THEN 1 WHEN ((email)::text = 'a'::text) THEN 2 ELSE 3 END), name
                   Sort Method: top-N heapsort  Memory: 35kB
                   Buffers: shared hit=287
                   ->  Bitmap Heap Scan on users  (cost=135.66..13007.91 rows=9379 width=805) (actual time=0.194..1.107 rows=277 loops=1)
                         Recheck Cond: ((lower((name)::text) = 'a'::text) OR (lower((username)::text) = 'a'::text) OR ((email)::text = 'a'::text))
                         Heap Blocks: exact=277
                         Buffers: shared hit=287
                         ->  BitmapOr  (cost=135.66..135.66 rows=9379 width=0) (actual time=0.152..0.152 rows=0 loops=1)
                               Buffers: shared hit=10
                               ->  Bitmap Index Scan on yorick_test_users  (cost=0.00..124.75 rows=9377 width=0) (actual time=0.101..0.101 rows=277 loops=1)
                                     Index Cond: (lower((name)::text) = 'a'::text)
                                     Buffers: shared hit=4
                               ->  Bitmap Index Scan on index_on_users_lower_username  (cost=0.00..1.94 rows=1 width=0) (actual time=0.035..0.035 rows=1 loops=1)
                                     Index Cond: (lower((username)::text) = 'a'::text)
                                     Buffers: shared hit=3
                               ->  Bitmap Index Scan on users_email_key  (cost=0.00..1.94 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=1)
                                     Index Cond: ((email)::text = 'a'::text)
                                     Buffers: shared hit=3
           Planning time: 0.303 ms
           Execution time: 1.687 ms
      
      Here we can see the new query is 25 times faster compared to the old
      query.
      41bfe82b
  2. 25 May, 2017 1 commit
  3. 21 Mar, 2017 2 commits