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';  

That 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, rather than just a combination of fields:

    CREATE UNIQUE INDEX I ON lower(username);  

That 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. That makes the insert/update overhead of this sort of index higher than one that just uses a column list. Even more so than regular indexes, the index needs to give you something that's selective against your data for the overhead of building the index to be worthwhile.