Junior dev asked me why their query was slow despite having an index.

Turns out they were doing WHERE LOWER(email) = 'user@example.com'

The index was on email, not LOWER(email).

Indexes are literal. They match exactly what you're querying for. Transform the data in your WHERE clause? You need an index on that transformation.

-- This won't use the index on email
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- This will
SELECT * FROM users WHERE email = 'john@example.com';

-- Or create a functional index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

Databases are fast, but they're not mind readers.