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 обычно требует заключать индексные выражения в скобки, как показано во втором примере. Если же выражение представляет собой просто вызов функции, как в первом примере, дополнительные скобки можно опустить.

Поддержка индексируемых выражений обходится довольно дорого, так как эти выражения должны вычисляться при добавлении каждой строки и при каждом последующем изменении. Однако при поиске по индексу индексируемое выражение не вычисляется повторно, так как его результат уже сохранён в индексе. В рассмотренных выше случаях система видит запрос как WHERE колонка_индекса = 'константа' и поэтому поиск выполняется так же быстро, как и с простым индексом. Таким образом, индексы по выражениям могут быть полезны, когда скорость извлечения данных гораздо важнее скорости добавления и изменения.