4 ms·

Postgres Indexes – Expression/Functional Indexing

• willlll 13y ago
  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 13y ago
    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 13y ago
      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 13y ago
        well, beside optimization, a function marked immutable is important because indexing on function return values makes only sense if the function is idempotent.
        • victorNicollet 13y ago
          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 13y ago
            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".
  • Someone 13y ago
    IMMUTABLE also implies "does not change the database". http://www.postgresql.org/docs/9.2/static/sql-createfunction.html http://www.postgresql.org/docs/9.2/static/sql-createfunction...: 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]
• jeffdavis 13y ago
  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-createtable.html#SQL-CREATETABLE-EXCLUDE http://www.postgresql.org/docs/current/static/sql-createtabl...
• buro9 13y ago
  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 13y ago
    Yeah doesn't resolve for me either
    • craigkerstiens 13y ago
      Sorry was an issue in mucking with my SSL cert, should be all back now.