$ ./guess_the_languageSQL & Databases
SQL & Databases · medium · #21

The query ignores the index. What fixes it?

CREATE INDEX idx_email ON users (email);

SELECT * FROM users
WHERE LOWER(email) = 'a@b.com';
  1. A hash index on email
  2. A UNIQUE index on email
  3. Run ANALYZE on users
  4. An index on LOWER(email)
Wrapping the column in a function hides it from a plain index; an expression index on LOWER(email) matches the predicate.
Play SQL & Databases ▸