Users table index on recovery_token is not being used in queries

[travis-humata]

Bug report

The following query, which should be nearly instant, accounts for 8% of our DB usage and 1500 ms per query:

SELECT (a bunch of columns) 
FROM users AS users 
WHERE recovery_token = $1 and is_sso_user = $2 
LIMIT $3

On investigation this query is running a seq_scan instead of using the index on recovery_token:

This is the index in question:

CREATE UNIQUE INDEX recovery_token_idx ON auth.users USING btree (recovery_token) WHERE ((recovery_token)::text !~ '^[0-9 ]*$'::text)

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

A clear and concise description of what the bug is.

To Reproduce

Run:

explain analyze SELECT *
FROM auth.users
WHERE recovery_token = 'abc';

Expected behavior

Should be nearly instant and use index in query plan

[J0]

closing as this has been deployed - let us know if there are still issues though

[nthouliss]

Hi,

I'm still seeing this issue on my project. Is the index supposed to one provisioned by supabase or should we be creating this index ourselves? I'd just like to verify this because I can see that no index is created for the column on my project.

Edit: Quick correction, there is an index on my project for recovery_token on auth.users but it isn't being used.