Expression-based indexes

The classic example for an expression-based index is when you typically search based on a name only after converting it to a particular case:

CREATE INDEX i_lower_idx ON t (lower(name));
SELECT * FROM t WHERE lower(name) = 'x';

This query will run using that expression index, which turns it into a single value indexed lookup in the table. An index based on the username without converting to lower case first will not be useful in that context.

One non-obvious thing you can implement using partial indexes is forcing uniqueness for an expression, instead of just a combination of fields:

CREATE UNIQUE INDEX I ON lower(username);

This would only allow one row to exist at any time with that value for the expression. You might want to handle this as a constraint instead though.

Note that every time you insert or update an entry in a table with this sort of index, the function used needs to be called to compute an appropriate value for the index. This...