PostgreSQL 中的部分索引

Source: Dev.to

概览

部分索引是针对特定访问模式的精细索引。它们不是对表中的每一行都建立索引，而只对满足条件的行建立索引——因此更小、更快，并且在合适的使用场景下更高效。

部分索引的工作原理

部分索引最适用于以下查询：

• 先过滤后扫描多行
• 针对有意义的数据子集
• 否则可能触及索引阈值，导致规划器完全忽略索引

使用示例数据库进行演示

示例使用已转换为 PostgreSQL 的 MySQL 示例数据库。salaries 表约有 300 万行。

CREATE TABLE "employees"."salaries" (
    "id" int4 GENERATED ALWAYS AS IDENTITY,
    "emp_no" int4,
    "salary" int4,
    "from_date" date,
    "to_date" date,
    PRIMARY KEY ("id")
);

标准索引查询

SELECT COUNT(*)
FROM salaries
WHERE salary > 100000
  AND to_date = '9999-01-01';

在该数据集中，9999-01-01 表示在职薪资。该查询大约耗时 140 ms（冷） 和 40 ms（热）。

添加部分索引

在添加索引之前，先考虑会过滤掉多少行。此例中，只有 247 000 行是当前（在职）的，约占 300 万行的很小比例，削减幅度明显。

CREATE INDEX idx_salaries_salary_todate_partial
ON salaries (salary)
WHERE to_date = '9999-01-01';

使用部分索引后，同样的查询耗时约 ≈16 ms（冷、热均相同）。

部分索引发挥优势的场景

大结果集与“30 % 规则”

当结果集超过总行数约 30 % 时，规划器可能会忽略范围查询的索引。例如：

SELECT COUNT(*)
FROM salaries
WHERE salary > 50000
  AND to_date = '9999-01-01';

• salary > 100000 返回 17 000 行 → 使用索引。
• salary > 50000 返回 215 000 行（≈87 % 的在职行） → 标准索引被忽略，改为表扫描，热时间约 120 ms。

部分索引仍然有用，因为它减少了起始行数，使查询时间即使在更宽的过滤条件下也保持在 40 ms 左右。

对子集的重复查询

典型场景是带有 is_processed 布尔字段的队列：

CREATE INDEX idx_unprocessed_queue
ON queue (created_at)
WHERE is_processed = false;

如果未处理的行只占总量的一小部分，索引保持小且快速。

索引大小的优势

• 完整薪资索引：58 MB
• 部分薪资索引：7 MB

更小的索引更容易装入缓冲缓存，提升其驻留在内存中的概率。

效益有限的情况

如果某列已经有唯一索引，在额外条件上再建部分索引可能提升不大：

SELECT id
FROM users
WHERE email = 'test@test.com' AND is_active = true;

由于 email 已唯一，规划器会使用唯一索引而不考虑 is_active。不过在非常大的表上，部分索引仍能减轻索引膨胀。

总结

部分索引是降低特定访问模式查询时间的强大工具。它们在以下情况下最为有效：

• 查询始终在相同条件上进行过滤
• 该条件排除掉了大量行
• 生成的索引明显小于完整索引

首先分析最常见的查询并估算可以过滤掉多少数据。如果结果看起来有前景，部分索引可能是值得添加的改进。