Improve performance of Project.find_with_namespace
This cuts down the query time of Project.find_with_namespace from
roughly 250 ms to roughly 15 ms. For PostgreSQL an expression index is
added (also when setting up the database), for MySQL the code relies on
it being case-insensitive by default (meaning no special index is
required).
At the moment the code is using the method case_insensitive_where, but
I'd like some feedback on this name. It's a bit long and as
@jacobvosmaer stated it's not the same as ActiveRecord's where method
(since case_insensitive_where can only operate on a Hash). Some
alternative suggestions were iwhere and case_insensitive_match.
Personally I'm not a huge fan of case_insensitive_where since it's
quite long, though at least it makes its intent quite clear.
Merge request reports
Activity
mentioned in issue #2341 (closed)
Added 37 commits:
- e55cdb90...c670f073 - 32 commits from branch
master - 85c6a374 - Added methods for detecting MySQL/PostgreSQL
- 1190d0ab - Added concern for case-insensitive WHERE queries
- 95464320 - Revamp finding projects by namespaces
- 4a0deab1 - Added dedicated Rake task for setting up Postgres
- 61990470 - Added changelog for project namespace performance
Toggle commit list- e55cdb90...c670f073 - 32 commits from branch
Fixed some syntax errors (the used Hash syntax was apparently not available yet in Ruby 2.1) and rebased.
@dzaporozhets: do you have any suggestions for alternatives to
case_insensitive_where, or are you OK with that method name?
1 class NamespacesProjectsPathLowerIndexes < ActiveRecord::Migration 2 disable_ddl_transaction! 3 4 def up 5 return unless Gitlab::Database.postgresql? 6 7 execute 'CREATE INDEX CONCURRENTLY index_on_namespaces_lower_path ON namespaces (LOWER(path));' 8 execute 'CREATE INDEX CONCURRENTLY index_on_projects_lower_path ON projects (LOWER(path));' They should be when using something along the likes of
pg_dump(e.g. what we use for gitlab.com) or anything else that fully supports the various SQL features of PostgreSQL. For MySQL these aren't included, which is intended.Edited by yorickpeterse-staging
@yorickpeterse since we expect to reuse it in several places maybe something short would be oke. I think
iwhereorimatchis pretty ok.-
@yorickpeterse ping me when MR is ready for merge
@dzaporozhets Using
iwheresounds like a plan, I'll update the MR and ping you when done.@haynes "ci" is used as a prefix for Continuous Integration all over the place, so that's confusing.
-
@DouweM ah right. I didn't think about that.
@dzaporozhets Now that I fixed some Rubocop/spec problems this is good to go from my end.
Reassigned to @dzaporozhets
mentioned in merge request !1545 (merged)
mentioned in issue #3229 (closed)
