User.find_by_any_email takes over 500 ms to find matching rows
As per New Relic (https://rpm.newrelic.com/accounts/543063/applications/4968123/databases#id=478712187&sort_by=slowest) the method User.find_by_any_email
generates a SQL query that takes over half a second to find a matching row. The generated query is roughly as following:
select users.*
from users
left join emails on emails.user_id = users.id
where (users.email = X or emails.email = X)
The JOIN here is the primary reason for the slowdown, we can see this in the following query plan:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=16656.61..18887.25 rows=32 width=26) (actual time=92.866..122.898 rows=1 loops=1)
Hash Cond: (emails.user_id = users.id)
Filter: ((users.email = 'yorickpeterse@gmail.com'::text) OR ((emails.email)::text = 'yorickpeterse@gmail.com'::text))
Rows Removed by Filter: 258953
-> Seq Scan on emails (cost=0.00..164.89 rows=8389 width=26) (actual time=0.002..0.857 rows=8389 loops=1)
-> Hash (cost=11676.38..11676.38 rows=257538 width=26) (actual time=76.131..76.131 rows=257538 loops=1)
Buckets: 8192 Batches: 8 Memory Usage: 1863kB
-> Seq Scan on users (cost=0.00..11676.38 rows=257538 width=26) (actual time=0.004..37.086 rows=257538 loops=1)
Planning time: 0.192 ms
Execution time: 123.044 ms
Simply querying the users or emails table by only the Email field (e.g. select * from users where email = X
) is blazing fast.
I'm currently looking into this to see if there's a faster solution for this method.