Read about our upcoming Code of Conduct on this issue

  1. 22 Mar, 2018 1 commit
  2. 21 Mar, 2018 3 commits
  3. 16 Mar, 2018 3 commits
  4. 09 Mar, 2018 2 commits
  5. 08 Mar, 2018 2 commits
  6. 07 Mar, 2018 3 commits
  7. 06 Mar, 2018 9 commits
  8. 05 Mar, 2018 2 commits
  9. 03 Mar, 2018 1 commit
  10. 01 Mar, 2018 2 commits
  11. 28 Feb, 2018 1 commit
  12. 23 Feb, 2018 1 commit
  13. 22 Feb, 2018 1 commit
    • Yorick Peterse's avatar
      Optimise searching for users using short queries · 1c783267df9c
      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.
      1c783267df9c
  14. 20 Feb, 2018 2 commits
    • Grzegorz Bizon's avatar
      f66194e7fb91
    • Andreas Brandl's avatar
      Add partial index on projects for index-only scans. · 4212768b5345
      Andreas Brandl authored
      This helps with queries that get project ids based on the - comparably
      rare - visibility levels 10 and 20. For these, postgres can now leverage
      the partial index for a index-only scan to improve performance.
      
      Example queries:
      SELECT id FROM projects WHERE visibility_level IN (10,20)
      SELECT id FROM projects WHERE visibility_level IN (10)
      
      For MySQL, this results in a full index on id because MySQL omits the
      WHERE clause. That is, the index is a duplicate of the primary key
      basically.
      4212768b5345
  15. 16 Feb, 2018 2 commits
  16. 15 Feb, 2018 1 commit
  17. 12 Feb, 2018 2 commits
  18. 08 Feb, 2018 1 commit
    • Greg Stark's avatar
      Add indexes and change SQL for expired artifacts to deal with artifacts migration efficiently · d38400283976
      Greg Stark authored
      Artifacts are in the middle of being migrated from ci_builds to
      ci_job_artifacts. The expiration date is currently visible in both of
      these tables and the test for whether an expired artifact is present
      for a job is complex as it requires checking both the of the tables.
      
      Add two new indexes, one on ci_builds.artifacts_expire_at and one on
      ci_job_artifacts.expire_at to enable finding expired artifacts
      efficiently.
      
      And until the migration is finished, replace the SQL for finding
      expired and non-expired artifacts with a hand-crafted UNION ALL based
      query instead of using OR. This overcomes a database optimizer
      limitation that prevents it from using these indexes.
      
      When the migration is finished the next version should remove this
      query and replace it with a much simpler query on just
      ci_job_artifacts. See
      https://gitlab.com/gitlab-org/gitlab-ce/issues/42561 for followup.
      d38400283976
  19. 07 Feb, 2018 1 commit