PostgreSQL：查询优化与理解索引操作

PostgreSQL 索引优化：为什么你的索引会被忽略以及如何解决

创建索引并非总能保证 PostgreSQL 会使用它。开发者经常会遇到这样的情况：尽管存在索引，查询却运行缓慢，最终还是回退到全表扫描（Seq Scan）。理解 PostgreSQL 的查询规划器机制、解读 EXPLAIN ANALYZE 指标，以及了解影响执行策略选择的因素，对于数据库性能优化至关重要。本文将通过使用大型数据集的实际案例，深入探讨这些方面。

准备工作：400 万行数据用于实验

为了演示索引的工作原理，我们将创建一个名为 t_test 的测试表，它没有主键或任何索引，包含 400 万条记录。这将清晰地展示索引前后的性能差异。

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (id serial, name text);
INSERT INTO t_test (name) SELECT 'hans' FROM generate_series(1, 2000000);
INSERT INTO t_test (name) SELECT 'paul' FROM generate_series(1, 2000000);
SELECT name, count(*) FROM t_test GROUP BY name;

当执行一个没有索引的查询时，例如 EXPLAIN ANALYZE SELECT * FROM t_test WHERE id = 432332;，我们会观察到一个 Seq Scan，它会遍历所有 400 万行，大约耗时 126 毫秒。这是索引旨在解决的经典问题的一个例子。

理解 EXPLAIN 和 cost 指标

EXPLAIN ANALYZE 是分析 PostgreSQL 查询执行计划的主要工具。它提供了关于规划器如何执行查询的详细信息，包括选择的操作符（例如 Seq Scan、Index Scan）、执行时间，以及至关重要的 cost 指标。

让我们看看前面例子中 cost=0.00..71622.00 的输出。这个数字并非实际的毫秒时间，而是 PostgreSQL 用于比较不同执行计划的相对系数。可以将其视为“鹦鹉”——抽象的成本单位。为了更清晰的实验，禁用并行化是值得的：

SET max_parallel_workers_per_gather TO 0;

成本由几个组成部分得出，例如磁盘块的数量和处理每行的成本：

SELECT pg_relation_size('t_test') / 8192.0; -- ~21622 8KB blocks
SHOW cpu_tuple_cost;    -- 0.01 (cost of processing a row)
SHOW cpu_operator_cost; -- 0.0025 (cost of an operator/function)

我们 Seq Scan 的近似成本公式如下：

SELECT (pg_relation_size('t_test') / 8192.0) * 1
       + count(id) * 0.01
       + count(id) * 0.0025
FROM t_test;

结果将接近 71622。理解 cost 不考虑系统硬件特性至关重要，因此比较两个不同查询的 cost 来估算实际执行时间是不准确的。然而，在单个查询内部，它对于识别计划中最“昂贵”的部分很有用。

基本索引使用：BTree 及其优势

PostgreSQL 中最常见的索引类型是 BTree。它提供了高效的搜索、排序和高并发性。让我们在 id 列上创建一个 BTree 索引：

CREATE INDEX idx_id ON t_test (id);
EXPLAIN SELECT * FROM t_test WHERE id = 43242;

创建索引后，查询的 cost 显著降低（从 71,622 降至 8.45），检索时间降至毫秒级。BTree 索引对于排序操作和查找最小值/最大值也同样有效：

• 排序： PostgreSQL 可以通过简单地按所需方向遍历索引（DESC 为 Index Scan Backward），并在达到 LIMIT 后停止，从而使用索引执行 ORDER BY。

```sql

EXPLAIN SELECT * FROM t_test ORDER BY id DESC LIMIT 10;

```

• 最小值/最大值： 为了确定 min(id) 或 max(id)，规划器使用 Index Only Scan，从索引中读取第一个或最后一个条目。

```sql

EXPLAIN SELECT min(id), max(id) FROM t_test;

```

处理多条件查询：位图扫描

PostgreSQL 可以使用 位图扫描（Bitmap Scan） 有效处理单个索引上的多个 OR 条件查询。

EXPLAIN SELECT * FROM t_test WHERE id = 30 OR id = 50;

在这种情况下，PostgreSQL 会为每个条件执行一个 Bitmap Index Scan，然后使用 BitmapOr 将结果合并成一个位图，之后才访问主表（Bitmap Heap Scan）以检索完整的行。这避免了多次表扫描并优化了数据访问。

为什么规划器会忽略索引：选择性与统计信息

索引未被使用的最常见原因之一是查询条件的 选择性（selectivity） 较低。选择性是指表中符合给定条件的行所占的比例。如果一个条件影响了表的大部分数据，规划器可能会认为 Seq Scan 比 Index Scan 更高效。

让我们考虑一个例子。我们将在 name 字段上创建一个索引：

CREATE INDEX idx_name ON t_test (name);

如果我们搜索一个不存在的名称（EXPLAIN SELECT * FROM t_test WHERE name = 'hans2';），索引将被使用，并且 rows 将为 1，因为 PostgreSQL 总是期望至少有一行。然而，如果查询覆盖了表的大部分，例如 'hans' OR 'paul'（这构成了我们测试表的 100%）：

EXPLAIN SELECT * FROM t_test WHERE name = 'hans' OR name = 'paul';

在这种情况下，PostgreSQL 将执行 Seq Scan。原因很简单：扫描整个索引，然后为 400 万行中的每一行访问表，这比简单地顺序读取整个表要昂贵得多。规划器根据数据分布统计信息做出决策。如果这些统计信息过时（例如，在大量更改之后），决策可能会不理想。

物理数据布局的影响：相关性与 CLUSTER

索引的有效性在很大程度上取决于数据在磁盘上的物理排列。如果索引访问的数据分布广泛，会显著降低检索速度。让我们创建一个表的副本，但行顺序是随机的：

CREATE TABLE t_random AS SELECT * FROM t_test ORDER BY random();
CREATE INDEX idx_random ON t_random (id);
VACUUM ANALYZE t_random;

让我们比较一下检索原始（有序）表和随机化表的头 10,000 条记录的查询性能：

• 原始表（有序数据）：

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_test WHERE id < 10000;

```

在这里，我们将观察到较低的缓冲区访问次数（例如，Buffers: shared hit=3 read=82），这表明数据是顺序读取的。

• 随机化表（分散数据）：

```sql

EXPLAIN (analyze true, buffers true) SELECT * FROM t_random WHERE id < 10000;

```

在这种情况下，缓冲区访问次数将显著更高（例如，Buffers: shared hit=801 read=7210）。发生这种情况是因为数据是分散的，迫使 PostgreSQL 执行许多随机磁盘读取，这大大增加了执行时间。规划器甚至可能切换到 Bitmap Heap Scan。

相关性（Correlation）

PostgreSQL 使用 相关性（correlation） 指标来跟踪数据的有序程度，该指标可在 pg_stats 中找到：

SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename IN ('t_test', 't_random') AND attname = 'id'
ORDER BY 1, 2;

• correlation ~ 1：数据物理上有序，允许顺序读取磁盘块。
• correlation ~ 0：数据随机分散，导致每行需要多次单独的磁盘访问。

CLUSTER

CLUSTER 命令允许您根据指定的索引物理排序表中的数据：

CLUSTER t_random USING idx_random;
VACUUM ANALYZE t_random;

在 CLUSTER 之后，检索将再次变得快速。然而，CLUSTER 存在显著的缺点：

• 表锁定： 该操作会在其持续期间锁定整个表，包括 SELECT 语句。
• 局限性： 它只能与单个索引一起使用。
• 无自动维护： 数据顺序不会自动维护；在新的插入或更新之后，数据可能会再次变得无序。

通过 Index Only Scan 和 INCLUDE 进行优化

当查询只访问完全包含在索引中的列时，PostgreSQL 可以执行 仅索引扫描（Index Only Scan）。这避免了访问主表（heap），显著加快了查询执行速度。

EXPLAIN SELECT id FROM t_test WHERE id = 34234;

在这里，id 已经存在于 idx_id 索引中，因此可以进行 Index Only Scan。然而，如果您查询所有列，包括不在 idx_id 中的 name：

EXPLAIN SELECT * FROM t_test WHERE id = 34234;

PostgreSQL 将执行常规的 Index Scan，因为它需要访问表以获取 name 列。为了即使对于 SELECT * 也能启用 Index Only Scan，您可以使用带有 INCLUDE 子句的 覆盖索引（covering index）：

CREATE INDEX idx_random_cover ON t_random (id) INCLUDE (name);
EXPLAIN SELECT * FROM t_random WHERE id = 34234;

现在，name 已包含在索引中，并且再次执行 Index Only Scan，从而最大限度地减少了磁盘访问。

高级索引技术：函数索引和部分索引

除了标准的 BTree 索引，PostgreSQL 还提供了更专业的解决方案：

• 函数索引（Functional Indexes）： 这些索引允许您对函数的结果进行索引。唯一的要求是该函数必须是确定性的（对于相同的输入总是返回相同的结果）。

```sql

CREATE INDEX idx_cos ON t_random (cos(id));

EXPLAIN SELECT * FROM t_random WHERE cos(id) = 10;

```

一个典型的用例是为不区分大小写的搜索索引 lower(email)。

• 部分索引（Partial Indexes）： 这些索引只覆盖满足特定 WHERE 条件的表行子集。

```sql

CREATE INDEX idx_name ON t_test (name) WHERE name NOT IN ('hans', 'paul');

```

这样的索引会显著更小，并且更新频率更低，当大部分数据很少涉及搜索查询时，这会很有益。

其他索引类型：GiST 和 pg_trgm

PostgreSQL 支持各种索引类型，每种都针对特定任务进行了优化。除了 BTree，还有 GIN、GiST、SP-GiST、BRIN 和 Bloom。例如，GiST 索引通常用于地理空间数据、全文搜索和其他复杂数据类型。

pg_trgm 扩展通过将字符串分解为三元组并计算它们之间的距离来实现模糊字符串匹配。这对于容错搜索或部分字符串匹配非常有用。

CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT 'abcde' <-> 'abdeacb'; -- a number between 0 and 1
SELECT show_trgm('abcdef');

关键要点

• EXPLAIN ANALYZE 是你最好的朋友： 使用它来理解查询计划并识别瓶颈。cost 是一个相对指标，用于比较单个计划的不同部分。
• 选择性决定选择： 索引对于高选择性查询（少量行）有效。对于低选择性（大量行），PostgreSQL 可能会倾向于 Seq Scan。
• 物理数据布局很重要： 逻辑数据和物理数据顺序之间的高度相关性可以提高 Index Scan 性能。CLUSTER 有帮助但有显著缺点。
• Index Only Scan 和 INCLUDE： 当所有必需的列都已存在于索引中时，使用这些机制可以最大限度地减少对主表的访问。
• 高级索引： 函数索引和部分索引允许您为特定场景创建更专业和高效的结构。

— Editorial Team