begriffs open source - ai-pg/blob - full-docs/txt/indexes-expressional.txt

   1
   2 11.7. Indexes on Expressions #
   3
   4    An index column need not be just a column of the underlying table, but
   5    can be a function or scalar expression computed from one or more
   6    columns of the table. This feature is useful to obtain fast access to
   7    tables based on the results of computations.
   8
   9    For example, a common way to do case-insensitive comparisons is to use
  10    the lower function:
  11 SELECT * FROM test1 WHERE lower(col1) = 'value';
  12
  13    This query can use an index if one has been defined on the result of
  14    the lower(col1) function:
  15 CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
  16
  17    If we were to declare this index UNIQUE, it would prevent creation of
  18    rows whose col1 values differ only in case, as well as rows whose col1
  19    values are actually identical. Thus, indexes on expressions can be used
  20    to enforce constraints that are not definable as simple unique
  21    constraints.
  22
  23    As another example, if one often does queries like:
  24 SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
  25
  26    then it might be worth creating an index like this:
  27 CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
  28
  29    The syntax of the CREATE INDEX command normally requires writing
  30    parentheses around index expressions, as shown in the second example.
  31    The parentheses can be omitted when the expression is just a function
  32    call, as in the first example.
  33
  34    Index expressions are relatively expensive to maintain, because the
  35    derived expression(s) must be computed for each row insertion and
  36    non-HOT update. However, the index expressions are not recomputed
  37    during an indexed search, since they are already stored in the index.
  38    In both examples above, the system sees the query as just WHERE
  39    indexedcolumn = 'constant' and so the speed of the search is equivalent
  40    to any other simple index query. Thus, indexes on expressions are
  41    useful when retrieval speed is more important than insertion and update
  42    speed.