11.7. Индексы по выражениям
Индекс можно создать не только по столбцу нижележащей таблицы, но и по функции или скалярному выражению с одним или несколькими столбцами таблицы. Это позволяет быстро находить данные в таблице по результатам вычислений.
Например, для сравнений без учёта регистра символов часто используется функция lower:
SELECT * FROM test1 WHERE lower(col1) = 'value';
Этот запрос сможет использовать индекс, определённый для результата функции lower(col1) так:
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
Если мы объявим этот индекс уникальным (UNIQUE), он не даст добавить строки, в которых значения col1 различаются только регистром, как и те, в которых значения col1 действительно одинаковые. Таким образом, индексы по выражениям можно использовать ещё и для обеспечения ограничений, которые нельзя записать как простые ограничения уникальности.
Если же часто выполняются запросы вида:
SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
тогда, возможно, стоит создать такой индекс:
CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
Синтаксис команды CREATE INDEX обычно требует заключать индексные выражения в скобки, как показано во втором примере. Если же выражение представляет собой просто вызов функции, как в первом примере, дополнительные скобки можно опустить.
Поддержка индексируемых выражений обходится довольно дорого, так как эти выражения должны вычисляться при каждом добавлении строки и при каждом изменении без оптимизации HOT. Однако при поиске по индексу индексируемое выражение не вычисляется повторно, так как его результат уже сохранён в индексе. В рассмотренных выше случаях система видит запрос как WHERE столбец_индекса = 'константа', и поэтому поиск выполняется так же быстро, как и с простым индексом. Таким образом, индексы по выражениям могут быть полезны, когда скорость извлечения данных гораздо важнее скорости добавления и изменения.
11.7. Indexes on Expressions
An index column need not be just a column of the underlying table, but can be a function or scalar expression computed from one or more columns of the table. This feature is useful to obtain fast access to tables based on the results of computations.
For example, a common way to do case-insensitive comparisons is to use the lower function:
SELECT * FROM test1 WHERE lower(col1) = 'value';
This query can use an index if one has been defined on the result of the lower(col1) function:
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
If we were to declare this index UNIQUE, it would prevent creation of rows whose col1 values differ only in case, as well as rows whose col1 values are actually identical. Thus, indexes on expressions can be used to enforce constraints that are not definable as simple unique constraints.
As another example, if one often does queries like:
SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
then it might be worth creating an index like this:
CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
The syntax of the CREATE INDEX command normally requires writing parentheses around index expressions, as shown in the second example. The parentheses can be omitted when the expression is just a function call, as in the first example.
Index expressions are relatively expensive to maintain, because the derived expression(s) must be computed for each row insertion and non-HOT update. However, the index expressions are not recomputed during an indexed search, since they are already stored in the index. In both examples above, the system sees the query as just WHERE indexedcolumn = 'constant' and so the speed of the search is equivalent to any other simple index query. Thus, indexes on expressions are useful when retrieval speed is more important than insertion and update speed.
