WIP: Case-Insensitive Name Sorting

What does this MR do?

Ignores the case of letters when comparing names during sorting operations.

Are there points in the code the reviewer needs to double check?

• Can you test the performance impact of LOWER on these queries?

Why was this MR needed?

2. Sorting does not ignore case of project name (that's irritating)

@ujr #27769 (moved)

2 . - This seems like a bug!

@markglenfletcher https://gitlab.com/gitlab-org/gitlab-ce/issues/27769#note_22917862

Screenshots (if relevant)

Does this MR meet the acceptance criteria?

• Changelog entry added
• Documentation created/updated
• API support added
• Tests
  • Added for this feature/bug
  • All builds are passing
• Conform by the merge request performance guides
• Conform by the style guides
• Branch has no merge conflicts with master (if it does - rebase it please)
• Squashed related commits together

What are the relevant issue numbers?

Fixes #27769 (moved) (the other 2 points are duplicates)

changed title from Case-Insensitive Name Sort to Case-Insensitive Name Sorting

added 1 commit

• 90ddbee1 - Add changelog entry

Compare with previous version

marked as a Work In Progress

I am going to set this to work in progress until it has tests.

added ~164274 ~480950 labels

added 71 commits

• 90ddbee1...11dd2348 - 69 commits from branch gitlab-org:master
• 6bddd61c - Ignore case when comparing names
• 1545ab90 - Add changelog entry

Compare with previous version

It might be necessary to index LOWER(name). Expression indexes were added to rails in 5.0, but can be enabled using schema_plus_pg_indexes.

added 1 commit

• 6bf45a42 - Provide the table name to match ActiveRecord table disambiguation

Compare with previous version

The specs were failing, because the column name needed to be prefixed with the table name to match the way ActiveRecord expands symbols. Some of the queries contain joins which have to be disambiguated. Fixed.

This still needs specs, a performance benchmark, and probably an index on the lowercase name expression.

added 1 commit

• c0cceb71 - Add test for sorting projects by name ignoring case

Compare with previous version

@yorickpeterse @ayufan Do you have any input on creating an index for LOWER(name)?

@deckar01 We use that in different places, so we definitely have exp with that :) Lets wait for Yorick, or you can just look in source :)

# trigram indexes are case-insensitive so we can just index the column
# instead of indexing lower(column)

db/migrate/20160226114608_add_trigram_indexes_for_searching.rb#L17-18

The name columns do index with gin_trgm_ops, but since they aren't being ordered case-insensitive it makes me suspect that the order operation is bypassing the existing index.

@deckar01 @ayufan We have done it in the past but it's quite a mess. The migration has to be PostgreSQL only, and we need some extra code to ensure the indexes are in place for newly set up databases. We do this in some Rake task somewhere, but I forgot which one. To be perfectly honest I'd rather not go through that trouble.

Ok, no new index. The gin_trgm_ops index should provide case-insensitive indexing, so I need to determine what the performance impact is.

added 1 commit

• d1b8dd69 - Dynamically quote the table and column for MySQL compatability

Compare with previous version

added 1 commit

• 42c48d69 - Dynamically quote the table and column for MySQL compatability

Compare with previous version

Here is my initial attempt at benchmarking the projects query:

Before: ~0.97ms

After: ~1.035ms (+7%)

https://gitlab.com/deckar01/gitlab-ce/snippets/1639234

I suspect the low number of projects (10) and limited resources of the server (2011 MBA) create a pretty high margin of error. Any suggestions on how to perform a more controlled performance test would be appreciated.