Uh oh!
There was an error while loading.Please reload this page.
- Notifications
You must be signed in to change notification settings - Fork14
Open
Description
Describe the bug
An index is created on theemail field:
https://github.com/fastapi-users/fastapi-users-db-sqlalchemy/blob/main/fastapi_users_db_sqlalchemy/__init__.py#L61
but the index can't be used by theget_by_email() method because the filtering uses a function on the SQL field.
To Reproduce
explain analyzeSELECTauth_user.hashed_password,auth_user.is_active,auth_user.is_superuser,auth_user.is_verified,auth_user.id,auth_user.email,auth_user.first_name,auth_user.last_nameFROM auth_userWHERElower(auth_user.email)=lower('foo@bar.com');
Seq Scan on auth_user (cost=0.00..10.90 rows=1 width=1245) (actual time=0.030..0.030 rows=0 loops=1) Filter: (lower((email)::text) = 'foo@bar.com'::text)Sequential scan is used instead of index scan.
Expected behavior
explain analyzeSELECTauth_user.hashed_password,auth_user.is_active,auth_user.is_superuser,auth_user.is_verified,auth_user.id,auth_user.email,auth_user.first_name,auth_user.last_nameFROM auth_userWHEREauth_user.email=lower('foo@bar.com');
Index Scan using ix_auth_user_email on auth_user (cost=0.14..2.36 rows=1 width=1245) (actual time=0.044..0.045 rows=0 loops=1) Index Cond: ((email)::text = 'foo@bar.com'::text)There is several ways to fix the issue:
- use CIText extension but it's not standard in SQLAlchemy (https://pypi.org/project/sqlalchemy-citext/).
- create a functional index:
CREATE INDEX ix_auth_user ON auth_user (lower(email) text_pattern_ops); - store email in lower case (my favorite even though on rare occasions, an outdated server or program might not interpret the capitalization correctly).
I can provide a PR when we'll agree on a solution.
Configuration
- FastAPI Users version : 0.7