表达式索引上的统计信息_postgresql introduction and concepts

作者：瀚高PG实验室 （Highgo PG Lab）- 瀚高大李

众所周知PostgreSQL 允许在表达式上创建索引。

一个索引列不一定必须为底层表（underlying table）中的一列，也可以为由表里的一列或多列计算得出的函数或者标量表达式（scalar expression）。这个特征是非常有用的，可以基于计算结果获得对于表的快速访问。

例1

一个常用的做大小写不敏感比较（case-insensitive comparisons）的方式是使用lower函数：

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

这个查询可以使用一个已经定义好的，在lower(col1)函数的结果上的索引：

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

如果我们把这个索引声明为唯一索引，它会阻止创建 col1 列的值只是大小写不同的行以及 col1 列的值完全相同的行。因此，表达式索引可以被用于加强一些约束，这些约束无法被定义为简单的唯一约束。

创建生成交叉列（cross-columns）统计信息的表达式索引也是可以实现的。

例2

如果我们经常使用类似如下的查询：

SELECT * FROM people WHERE （first_name || ' ' || last_name）= 'John Smith';

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

CREATE INDEX people_names_idx ON people ((first_name || ' ' || last_name));

通常情况下CREATE INDEX命令的语法要求在索引表达式的周围写圆括号（如例2所示），但当表达式只是一个函数调用的时候圆括号也可以被省略（如例1所示）。

索引表达式的维护相对昂贵，因为各行插入和每次更新时必须计算衍生的表达式（derived expression）。然而索引表达式在一次索引搜索的过程中不会被重新计算，因为它们已经被存入到了索引之中。

因此，表达式上的索引在索引速度比插入和更新的速度更重要的时候非常有效。

优化器统计信息

然而表达式索引还有另一个好处，那就是优化器统计信息（optimizer statistics）。表达式索引不仅允许对于匹配表达式的快速查询，而且还提供了优化器统计信息，优化器统计信息能提升行估计（row estimates）和提高查询规划。

例3

图1

从图1的结果我们可以看出，优化器并不了解取模运算符（modulus operator）的选择度，所以最初它只返回两行。一旦一个表达式索引被创建，生成了分析统计信息，优化器就可以准确的知道有多少行被返回了。过程如图2所示：

图2

从图2的结果来看，优化器已经可以知道有100行被返回。而且有趣的是，优化器会使用表达式索引统计信息，即使表达式索引本身未被使用。在上面的例子中，仅仅选用取模运算还不足以使索引生效，但是使用表达式统计信息对于一些更复杂的查询会很有帮助。

参考文献:

1、PostgreSQL 9.6.7 Documentation

2、PostgreSQL Introduction and Concepts