
Postgres Indexes – Expression/Functional Indexing - craigkerstiens
http://www.craigkerstiens.com/2013/05/29/postgres-indexes-expression-or-functional-indexes/
======
jeffdavis
Something really cool you can do is functional _unique_ indexes:

    
    
        CREATE UNIQUE INDEX foo_fidx ON foo (lower(a));
    

Will reject values of "a" that have the same lower-case value as something
that already exists, even if the values of "a" are different. Exclusion
Constraints[1] can make use of this, as well.

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;
    

Exclusion constraints also accept a WHERE clause.

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...](http://www.postgresql.org/docs/current/static/sql-
createtable.html#SQL-CREATETABLE-EXCLUDE)

------
willlll
IMMUTABLE STRICT is used properly in all of these examples. However, it is
important to not lie to postgres and declare your functions are IMMUTABLE
(always same output given same input) and STRICT (no side-effects) when
they're not, or you'll get wrong answers.

~~~
jpitz
Devil's Advocate: It is important to get them _right_, because postgres will
cache the tar out of a IMMUTABLE function result, and <mumbling>something
something optimize STRICT calls, maybe?</mumbling>

~~~
fdr
An immutable mark will allow the optimizer to constant-fold an expression that
contains only IMMUTABLE operations. It basically has to believe you on this
one, and being wrong is not good at all.

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.

~~~
ithkuil
well, beside optimization, a function marked immutable is important because
indexing on function return values makes only sense if the function is
idempotent.

~~~
victorNicollet
Idempotent is probably not what you meant.

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).

~~~
ithkuil
wow, there seems to be a degree of confusion about this term, depending on the
context.

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".

------
buro9
Did anyone get a cached copy of this article? Seems like there is a DNS
failure with Heruko, either that or Craig nuked his blog for a day.

~~~
chrisfarms
Yeah doesn't resolve for me either

~~~
craigkerstiens
Sorry was an issue in mucking with my SSL cert, should be all back now.

