十月 9, 2024
摘要：在本教程中，您将学习如何在 PostgreSQL 中利用表达式索引。
目录
背景
假设我们有一个表，其中包含一系列时间戳（我们可以用 generate_series 函数来生成日期）：
CREATE TABLE t AS
SELECT d, repeat(md5(d::text), 10) AS padding
FROM generate_series(timestamp '1900-01-01',
timestamp '2100-01-01',
interval '1 day') s(d);
VACUUM ANALYZE t;
该表还包括一个填充列，以使其更大一些。现在，让我们执行一个简单的范围查询，从表中包含的 ~200 年中仅选择一个月。如果你对查询进行 explain，你将看到如下内容：
EXPLAIN SELECT * FROM t WHERE d BETWEEN '2001-01-01' AND '2001-02-01';
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on t (cost=0.00..4416.75 rows=32 width=332)
Filter: ((d >= '2001-01-01 00:00:00'::timestamp without time zone)
AND (d <= '2001-02-01 00:00:00'::timestamp without time zone))
(2 rows)
这里的运行时间为 ~20 毫秒。还不错，考虑到这必须遍历整个表 ~75k 行。
但是让我们在 timestamp 列上创建一个索引（这里的所有索引都是默认类型，即 btree，除非明确说明）：
CREATE INDEX idx_t_d ON t (d);
现在让我们尝试再次运行查询：
QUERY PLAN
------------------------------------------------------------------------
Index Scan using idx_t_d on t (cost=0.29..9.97 rows=34 width=332)
Index Cond: ((d >= '2001-01-01 00:00:00'::timestamp without time zone)
AND (d <= '2001-02-01 00:00:00'::timestamp without time zone))
(2 rows)
这次运行时间为 0.5 毫秒，因此大约快了 40 倍。这当然还是直接在列上创建的简单索引，而不是表达式索引。因此，假设我们需要从每个月的第 1 日选择数据，执行如下查询
SELECT * FROM t WHERE EXTRACT(day FROM d) = 1;
但是，它不能使用索引，因为它需要计算列上的表达式，而索引是构建在该列上的，如 EXPLAIN ANALYZE 所示：
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on t (cost=0.00..4416.75 rows=365 width=332)
(actual time=0.045..40.601 rows=2401 loops=1)
Filter: (date_part('day'::text, d) = '1'::double precision)
Rows Removed by Filter: 70649
Planning time: 0.209 ms
Execution time: 43.018 ms
(5 rows)
因此，这不仅必须执行顺序扫描，还必须进行计算，将查询持续时间增加到了 43 毫秒。
数据库无法使用索引的原因有多种。索引（至少是 btree 索引）依赖于查询由树状结构提供的排序数据，虽然范围查询可以从中受益，但第二个查询（带 extract 调用）则不能。
注意：另一个问题是索引支持的运算符集（即可以直接在索引上计算的运算符）非常有限。并且不支持 “extract” 函数，因此查询无法使用 Bitmap Index Scan 来解决排序问题。
理论上，数据库可能会尝试将条件转换为范围条件，但这非常困难，并且和特定表达式相关。在这种情况下，我们必须生成无限数量的此类 “每日” 范围，因为规划器并不真正知道表中的最小/最大时间戳。所以数据库甚至不会尝试。
但是，虽然数据库不知道如何转换条件，但开发人员经常知道。例如，对于像下面的条件
(column + 1) >= 1000
重写成下面这样并不难
column >= (1000 - 1)
这样就可以很好地利用索引了。
时间索引示例
但是，如果无法进行此类转换呢？例如这个示例查询
SELECT * FROM t WHERE EXTRACT(day FROM d) = 1;
在这种情况下，开发人员将不得不面对 d 列未知的 min/max 的相同问题，即使这样，它也会生成很多范围。
本文是关于表达式索引的，到目前为止，我们只使用了直接基于列构建的常规索引。因此，让我们创建第一个表达式索引：
CREATE INDEX idx_t_expr ON t ((extract(day FROM d)));
ANALYZE t;
然后会得到这样的 explain 计划
QUERY PLAN
------------------------------------------------------------------------
Bitmap Heap Scan on t (cost=47.35..3305.25 rows=2459 width=332)
(actual time=2.400..12.539 rows=2401 loops=1)
Recheck Cond: (date_part('day'::text, d) = '1'::double precision)
Heap Blocks: exact=2401
-> Bitmap Index Scan on idx_t_expr (cost=0.00..46.73 rows=2459 width=0)
(actual time=1.243..1.243 rows=2401 loops=1)
Index Cond: (date_part('day'::text, d) = '1'::double precision)
Planning time: 0.374 ms
Execution time: 17.136 ms
(7 rows)
因此，虽然这并没有给我们带来像第一个示例中的索引一样的 40 倍加速，但这是意料之中的，因为此查询返回的元组要多得多（2401 对比 32）。此外，这些元组分布在整个表中，而不是像第一个示例那样的局部化。因此，这是一个很好的 2 倍加速，在许多实际情况下，您会看到更大的提升。
但是，将索引用于具有复杂表达式的条件的能力，并不是这里最有趣的信息 – 这就是人们创建表达式索引的原因。但这并不是唯一的好处。
如果你看一下上面介绍的两个 explain 计划（没有和带有表达式索引时），你可能会注意到这一点：
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on t (cost=0.00..4416.75 rows=365 width=332)
(actual time=0.045..40.601 rows=2401 loops=1)
...
QUERY PLAN
------------------------------------------------------------------------
Bitmap Heap Scan on t (cost=47.35..3305.25 rows=2459 width=332)
(actual time=2.400..12.539 rows=2401 loops=1)
...
对的，创建表达式索引显著提高了估计值。如果没有索引，我们只有原始表列的统计数据（MCV + 直方图），因此数据库不知道如何估算表达式
EXTRACT(day FROM d) = 1
因此，它对于等于条件使用了默认估计值，即所有行的 0.5% – 由于该表有 73050 行，我们最终得到的估计值仅为 365 行。在实际应用程序中，经常会看到更严重的估算误差。
但是，使用索引时，数据库还会收集有关索引列的统计信息，在本例中，该列包含表达式的结果。在规划时，优化器会注意到这一点并生成更好的估计。
这会带来巨大的好处，还可能有助于修复一些由于估计不准确而导致的糟糕查询计划的情况。然而，大多数人并不知道这个方便的特性。
JSON 索引示例
随着 9.4 中 JSONB 数据类型的引入，该特性的实用性才有所增加，因为它是收集有关 JSONB 文档内容的统计数据的唯一方法。
在为 JSONB 文档建立索引时，存在两种基本的索引策略。您可以在整个文档上创建 GIN/GiST 索引，例如：
CREATE INDEX ON t USING GIN (jsonb_column);
它允许您查询 JSONB 列中的任意路径，使用包含运算符匹配子文档等。这很好，但您仍然只有基本的每列统计信息，这并不是很有用，因为文档被视为标量值（并且没有人会去匹配整个文档或使用文档的范围）。
表达式索引，比如，像这样创建：
CREATE INDEX ON t ((jsonb_column->'id'));
仅对特定表达式有用，即这个新创建的索引对下面查询有用
SELECT * FROM t WHERE jsonb_column ->> 'id' = 123;
但不适用于访问其他 JSON 键的查询，比如像 “value”
SELECT * FROM t WHERE jsonb_column ->> 'value' = 'xxxx';
这并不是说整个文档上的 GIN/GiST 索引没有用，但您必须选择。您可以创建特定的表达式索引，这在查询特定键时非常有用，并且具有表达式统计信息的额外优势。或者你可以在整个文档上创建一个 GIN/GiST 索引，能够处理对任意键的查询，但没有统计数据。
但是，在这种情况下，您也可以两者全要，因为您可以同时创建两个索引，并且数据库会选择将其中的哪个索引用于各个查询。有了表达式索引后，您也会获得准确的统计数据。
遗憾的是，你不能同时使用两者，因为表达式索引和 GIN/GiST 索引使用不同的条件
-- expression (btree)
SELECT * FROM t WHERE jsonb_column ->> 'id' = 123;
-- GIN/GiST
SELECT * FROM t WHERE jsonb_column @> '{"id" : 123}';
所以规划器不能同时使用它们 - 用表达式索引进行估算，再用 GIN/GiST 进行执行。