JPA findBy...IgnoreCase and PostgreSQL indexing

Question

I have following question about findByUsernameIgnoreCase lookup and indexing. For example i have DB column username (unique index) in users table and i want case insensitive lookup. For now all the records are in UPPERCASE.

The question is findByUsernameIgnoreCase(...) will use the indexing or not?

In specification the outcome of IgnoreCase is where UPPER(username)=UPPER(?1) which confuses me in order of index.

Answer 1

For method findBy...IgnoreCase("someName") Spring Data JPA generate a like this SQL:

select ... from users u where upper(u.username) = upper('someName')

So in case of using it with index you should create an 'upper(username)' index, like this:

create index index_users_username_upper on users (upper(username));

Query plan example:

Bitmap Heap Scan on parents p  (cost=83.17..8791.50 rows=5000 width=346) (actual time=0.024..0.024 rows=0 loops=1)
  Filter: (upper(username) ~~ 'SOMENAME'::text)
  ->  Bitmap Index Scan on index_users_username_upper  (cost=0.00..81.92 rows=5000 width=0) (actual time=0.022..0.022 rows=0 loops=1)
        Index Cond: (upper(name) = 'SOMENAME'::text)
Planning time: 0.207 ms
Execution time: 0.051 ms

I think that storing username in uppercase is not necessarily in that case...

---

You can always create your own query method that will match your conditions, for example:

@Query("select u from User u where upper(u.username) = ?1")
List<User> findByUsernameIgnoreCase(string name);

In this case you can use simple index by username.