User.by_login is really slow on gitlab.com

I tried the following patch on top of 7.14.0-ee:

$ cat /opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/auth.rb
module Gitlab
  class Auth
    def find(login, password)
start = Time.now
      user = User.by_login(login)
printf("User.by_login %.3f\n", Time.now - start)
  
      if Devise.omniauth_providers.include?(:kerberos)
        kerberos_user = Gitlab::Kerberos::Authentication.login(login, password)
        return kerberos_user if kerberos_user
      end

      # If no user is found, or it's an LDAP server, try LDAP.
      #   LDAP users are only authenticated via LDAP
      if user.nil? || user.ldap_user?
        # Second chance - try LDAP authentication
        return nil unless Gitlab::LDAP::Config.enabled?

        Gitlab::LDAP::Authentication.login(login, password)
      else
        user if user.valid_password?(password)
      end
ensure
printf("Gitlab::Auth#find %.3f\n", Time.now - start)
    end
  end
end

It show output like this in unicorn_stdout.log:

User.by_login 0.237
Gitlab::Auth#find 0.301
User.by_login 0.455
Gitlab::Auth#find 0.527
User.by_login 0.168
Gitlab::Auth#find 0.241
User.by_login 0.274
Gitlab::Auth#find 0.339
User.by_login 0.338
Gitlab::Auth#find 0.403
User.by_login 0.280
Gitlab::Auth#find 0.344
User.by_login 0.401
Gitlab::Auth#find 0.467
User.by_login 0.317
Gitlab::Auth#find 0.404
User.by_login 0.445
Gitlab::Auth#find 0.517
User.by_login 0.055
Gitlab::Auth#find 0.119
User.by_login 0.234
Gitlab::Auth#find 0.300
User.by_login 0.313
Gitlab::Auth#find 0.378
User.by_login 0.282
Gitlab::Auth#find 0.369

User.by_login does this query:

      where('lower(username) = :value OR lower(email) = :value',
            value: login.to_s.downcase).first

I suspect that the problem here is that although we have indexes on 'username' and 'email', we do NOT have indexes on 'lower(username)' or 'lower(email)'. Postgres supports adding such indexes, MySQL does not. But with MySQL we could probably drop the 'lower()' part because of case-insensitivity.

cc @dzaporozhets