Postgresql 中文操作指南

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.

例如，执行不区分大小写的比较的一种常见方法是使用 lower 函数：

For example, a common way to do case-insensitive comparisons is to use the lower function:

SELECT * FROM test1 WHERE lower(col1) = 'value';

如果在 lower(col1) 函数的结果中定义了索引，则此查询可以使用索引：

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

如果我们在此声明该索引 UNIQUE，它将阻止创建行，它们的 col1 值仅在大小写上有差异，以及行，其 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));

CREATE INDEX 命令的语法通常需要在索引表达式周围写括号，如第二个示例所示。当表达式只是函数调用时，括号可以省略，如第一个示例所示。

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.

索引表达式维护的成本相对较高，因为必须为每一行插入和 non-HOT update.计算派生表达式，然而，索引表达式在索引搜索期间 not_重新计算，因为它们已存储在索引中。在上述两个示例中，系统将查询视为仅 _WHERE indexedcolumn = 'constant'，因此搜索速度相当于任何其他简单索引查询。因此，当检索速度比插入和更新速度更重要时，对表达式的索引是有用的。

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.