八月 15, 2024
摘要：在本教程中，您将学习如何使用表达式索引来优化查询的选择率估计。
目录
介绍
大多数人都知道 Postgres 允许创建表达式索引。如果您需要对 WHERE 子句中使用的表达式进行索引查找，这将非常有用。
但是，表达式索引还有另一个好处，那就是优化器统计信息。表达式索引不仅允许对匹配表达式进行快速查找，而且还能提供优化器统计信息，以改进行数估计值，从而改进查询计划。
使用表达式索引改进行数估计值
下面是一个示例：
CREATE TABLE test (x INTEGER);
INSERT INTO test SELECT x FROM generate_series(1, 100) AS t(x);
ANALYZE test;
SELECT COUNT(*) FROM test WHERE x % 2 = 1;
count
-------
50
EXPLAIN SELECT * FROM test WHERE x % 2 = 1;
QUERY PLAN
----------------------------------------------------
Seq Scan on test (cost=0.00..2.50 rows=1 width=4)
Filter: ((x % 2) = 1)
优化器不知道取模运算符的选择率，因此它最初假设只返回一行。一旦创建了表达式索引，并分析生成了统计信息，优化器就会确切地知道将返回多少行：
CREATE INDEX i_test ON test ((x % 2));
ANALYZE test;
EXPLAIN SELECT * FROM test WHERE x % 2 = 1;
QUERY PLAN
-----------------------------------------------------
Seq Scan on test (cost=0.00..2.50 rows=50 width=4)
Filter: ((x % 2) = 1)
有趣的是，优化器使用了表达式索引的统计信息，即使没有使用到表达式索引本身。在上面的示例中，取模运算符的选择率不足以使用到索引，但表达式统计信息对于更复杂的查询（例如，使用到连接）很有用。此方法还可用于创建基于函数的统计信息。
还可以创建表达式索引，来生成多列的统计信息。例如，下面的表达式索引将为州/城市的组合提供准确的统计信息，但查询需要使用到一样的字符串联接形式：
CREATE INDEX i_customer_state_city ON customer ((state || '|' || city));
如果有一种方法，可以在不产生创建和维护索引的开销的情况下，创建表达式统计信息，那就太好了。
使用表达式索引改进查询计划
如上所示，在表达式索引上生成的统计信息，可用于产生更准确的行数估计值，并可能提供更好的计划。接下来，让我们看看更准确的行数估计值，会如何改变查询计划。
首先，设置数据表：
CREATE TABLE test1 AS
SELECT * FROM generate_series(1, 100000) AS f(x);
CREATE TABLE test2 AS
SELECT * FROM generate_series(1, 2) AS f(x);
ANALYZE test1;
ANALYZE test2;
然后是使用取模的连接查询，不带表达式索引：
EXPLAIN SELECT test1.x
FROM test1, test2
WHERE test1.x % 2 = 1 AND test1.x = test2.x;
QUERY PLAN
-----------------------------------------------------------------
Nested Loop (cost=0.00..1959.02 rows=1 width=4)
Join Filter: (test1.x = test2.x)
-> Seq Scan on test1 (cost=0.00..1943.00 rows=500 width=4)
Filter: ((x % 2) = 1)
-> Materialize (cost=0.00..1.03 rows=2 width=4)
-> Seq Scan on test2 (cost=0.00..1.02 rows=2 width=4)
使用了嵌套循环连接，这是次优的，因为 test1 的行数估计值比实际值小了一百倍。
通过对 test1.x 上的取模运算产生适当的统计信息，可以使用到更高效的哈希连接：
CREATE INDEX i_test1 ON test1((x % 2));
ANALYZE test1;
ANALYZE test2;
EXPLAIN SELECT test1.x
FROM test1, test2
WHERE test1.x % 2 = 1 AND test1.x = test2.x;
QUERY PLAN
------------------------------------------------------------------
Hash Join (cost=1.04..2132.29 rows=1 width=4)
Hash Cond: (test1.x = test2.x)
-> Seq Scan on test1 (cost=0.00..1943.00 rows=50197 width=4)
Filter: ((x % 2) = 1)
-> Hash (cost=1.02..1.02 rows=2 width=4)
-> Seq Scan on test2 (cost=0.00..1.02 rows=2 width=4)
请注意，test1 的行数估计值现在更准确了，并且分析基础表时也会分析表达式索引。现在的总成本略高（2132.29 对比 1959.02），但这并不是因为哈希连接的成本更高。相反，这是因为嵌套循环错误地估计了它需要处理的行数，因为它不知道取模运算的选择率。
这个例子也说明了一件事，优化器有多么“喜欢”哈希连接。如果 test2 有三行或更多行，或者如果 test1 有多 10 倍的行数，并启用了并行查询，那么即使没有表达式索引的统计信息，也会使用哈希连接。尽管存在误估，但哈希连接仍然非常可靠，因此它们受到优化器的青睐。结论是，只有当测试表明表达式索引实际上改进了查询计划时，才建议创建用于统计目的的表达式索引，也就是说，仅改进 explain 的行数估计值几乎没有什么好处。