Though we usually see column names by themselves when defining an index, it is also possible to create an index with an expression.
Let’s say I have a
users table with an
create index email_idx on users (email);
If I always perform queries on the
function, like this
select * from users where lower(email) = lower('firstname.lastname@example.org');
then I will want to also create an index with that full expression —
I can do this with a statement like the following
create index lower_email_idx on users (lower(email));
Without an index that uses the full
statements like the one above will be forced to do full sequential scans
instead of indexed scans.