PostgreSQL 13 第 11 章 索引 11.7. 表达式索引

一个索引列并不一定是底层表的一个列，也可以是从表的一列或多列计算而来的一个函数或者标量表达式。这种特性对于根据计算结果快速获取表中内容是有用的。

例如，一种进行大小写不敏感比较的常用方法是使用lower函数：

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

这种查询可以利用一个建立在lower(col1)函数结果之上的索引：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
复制

如果我们将该索引声明为UNIQUE，它将阻止创建在col1值上只有大小写不同的行。

另外一个例子，如果我们经常进行如下的查询：

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
复制

那么值得创建一个这样的索引：

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
复制

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

索引表达式的维护代价较为昂贵，因为在每一个行被插入或更新时都得为它重新计算相应的表达式。然而，索引表达式在进行索引搜索时却不需要重新计算，因为它们的结果已经被存储在索引中了。在上面两个例子中，系统将会发现查询的条件是WHERE indexedcolumn = 'constant'，因此查询的速度将等同于其他简单索引查询。因此，表达式索引对于检索速度远比插入和更新速度重要的情况非常有用。