PostgreSQL 支持表达式索引或者称之为函数索引，即索引列不是表的一个或多个字段，而是表的一个或多个字段上计算的函数或者表达式。这个特性可用于 where 条件中的字段包含函数或表达式的场景。

1. PostgreSQL 函数索引示例

举个例子，对于大小写敏感的字符串比较，通常会将其全部转成小写后进行比较，如下：

select * from t where lower(name)='tang';

由于 name 字段上使用了函数 lower()，使得字段 name 上的索引无法使用，因此需要创建函数索引，如下：

create index t_lower_name on t(lower(name));

注意这里的表达式索引最好不要定义为 unique，否则对于仅大小写不一样，其他都一样的字符串，会报唯一键冲突，如下：

postgres=# create unique index uk_t_lower_name on t (lower(name));
CREATE INDEX
postgres=# insert into t values (1, 'tang');
INSERT 0 1
postgres=# insert into t values (2, 'Tang');
ERROR:  duplicate key value violates unique constraint "uk_t_lower_name"
DETAIL:  Key (lower(name))=(tang) already exists.

2. PostgreSQL 表达式索引示例

另外一个例子关于表达式索引，如下：

create table t(id int, first_name text, last_name text);
select * from t where (first_name || ' ' || last_name) = 'tang';

上述两个字段通过 || 表达式进行字符串连接操作，普通的索引无法使用，需要创建表达式索引，如下：

create index t_names on t((first_name || ' ' || last_name));

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

表达式索引的维护成本相对较高，因为必须为插入的每一行和非 HOT 更新的值计算表达式，因此对写入性能有所影响，但是索引表达式在索引搜索期间不会重新计算，因为它们已经存储在索引中，对查询不会有性能影响，反而对有表达式或函数查询的场景有较大的性能提升。综合来看，当查询速度比插入和更新速度更重要时，表达式上的索引就显得很有用。

文章评论