17

If I change a regular unique index on users.email to a case insensitive one by creating this index:

CREATE UNIQUE INDEX user_email_ci_idx ON users ((lower(email)));

Any reason not to drop the prior case sensitive index?

I would guess that Postgres will switch to the new index, and performance would be equivalent?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
justingordon
  • 12,553
  • 12
  • 72
  • 116

1 Answers1

13

The new index can only be used with conditions based on the index expression:

...
WHERE lower(email)  = 'abc@foo.org'  -- search string in lower case.

As long as you remember that, you can drop the old case sensitive index.

Or you have a look at trigram indexes which work case insensitive to begin with.

See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • trigram will only help for like and ilike and partial string search. If my use case is to look for case insensitive exact match. Will trigram index still be better than the lower/upper index? – Andrew James Ramirez Sep 23 '20 at 08:21
  • 1
    @AndrewJamesRamirez: For only case insensitive *equality* conditions, chose the displayed btree index on the expression over a trigram index. Typically more efficient - typically much smaller than a trigram GIN or GiST index for starters. Even more so with [deduplication](https://www.postgresql.org/docs/13/btree-implementation.html#BTREE-DEDUPLICATION) in Postgres 13 (currently RC). – Erwin Brandstetter Sep 23 '20 at 11:17