Postgresql 中文操作指南
11.7. Indexes on Expressions #
索引列不必只是底层表的一列,但可以是从表中的一个或多个列计算出的函数或标量表达式。此功能对于根据计算结果快速访问表很有用。
例如,执行不区分大小写的比较的一种常见方法是使用 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 命令的语法通常需要在索引表达式周围写括号,如第二个示例所示。当表达式只是函数调用时,括号可以省略,如第一个示例所示。
索引表达式维护的成本相对较高,因为必须为每一行插入和 non-HOT update.计算派生表达式,然而,索引表达式在索引搜索期间 not_重新计算,因为它们已存储在索引中。在上述两个示例中,系统将查询视为仅 _WHERE indexedcolumn = 'constant',因此搜索速度相当于任何其他简单索引查询。因此,当检索速度比插入和更新速度更重要时,对表达式的索引是有用的。