Postgres Indexes – Expression/Functional Indexing
craigkerstiens.com
craigkerstiens.com
CREATE UNIQUE INDEX foo_fidx ON foo (lower(a));
Also, you can do partial indexes, which have a WHERE clause. You can combine those with unique indexes, too:
CREATE UNIQUE INDEX foo_idx ON foo (lower(a))
WHERE b <> 0;
Additionally, if you do a partial and/or functional index, then postgres will collect stats specific to the function and predicate, which improves plans even if the index isn't used.
[1] http://www.postgresql.org/docs/current/static/sql-createtabl...
IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.
STRICT does not mean 'no side effects'; it means 'returns NULL when given a NULL argument'. From the same page:
CALLED ON NULL INPUT (the default) indicates that the function will be called normally when some of its arguments are null. It is then the function author's responsibility to check for null values if necessary and respond appropriately.
RETURNS NULL ON NULL INPUT or STRICT indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically.
[looked that up because I couldn't think of a function that was STRICT but not IMMUTABLE according to the definition given]
STRICT is less advisory: if specified, the function will not be evaluated if it any NULL is seen in the argument list (NULL will be yielded immediately), without ever passing control to the code in the function.
It makes sense to index on, say, `reverse(name)` (to perform suffix searches), and that function is immutable and deterministic, but it is not idempotent (except on palindromes).
Mathematically speaking it means that you can apply it multiple times and get the same result, i.e. reverse(reverse(name)) == reverse(name)"
However, I heard it using it many times where the correct term would be "pure function" (one whose output is defined solely by its input). But this is clearly misleading.
I guess that the confusion stems from the use of "applying multiple times". Formally multiple function application means f(f(x)), and not simply repeating function evaluation f(x); f(x).
Furthermore I saw people distinguishing "side effect idempotence" (observed by all pure functions) and "return value idempotence".
To add more confusion to that, some some HTTP (GET/...) operations are called idempotent, even though it's clearly not in functional sense (if you take the output of the GET and feed it as argument to another GET, you won't get the same result again), but only meaning "side effect free".