From 24c8f42278844cf48cdd9871b8285445379623f0 Mon Sep 17 00:00:00 2001 From: Yorick Peterse Date: Thu, 29 Oct 2015 12:05:47 +0100 Subject: Use a UNION for User.find_by_any_email This is significantly faster than using a sub-query, at least when run on the GitLab.com production database. The benchmarks are a lot slower now with these changes, most likely due to PostgreSQL choosing a different (and less efficient) plan based on the amount of data present in the test database. Thanks to @dlemstra for suggesting the use of a UNION. --- app/models/user.rb | 15 ++++++++++++--- 1 file changed, 12 insertions(+), 3 deletions(-) diff --git a/app/models/user.rb b/app/models/user.rb index 924cb543fab..35f5ab56798 100644 --- a/app/models/user.rb +++ b/app/models/user.rb @@ -235,9 +235,18 @@ class User < ActiveRecord::Base # Find a User by their primary email or any associated secondary email def find_by_any_email(email) - User.reorder(nil). - where('id IN (SELECT user_id FROM emails WHERE email = :email) OR email = :email', email: email). - take + # Arel doesn't allow for chaining operations on union nodes, thus we have + # to write this query by hand. See the following issue for more info: + # https://github.com/rails/arel/issues/98. + sql = '(SELECT * FROM users WHERE email = :email + UNION + SELECT users.* + FROM emails + INNER JOIN users ON users.id = emails.user_id + WHERE emails.email = :email) + LIMIT 1;' + + User.find_by_sql([sql, { email: email }]).first end def filter(filter_name) -- cgit v1.2.1