PostgreSQL 部分索引

部分索引建立在表的一个子集上，该子集由条件表达式定义，这个条件称为索引的谓词。索引只包含满足谓词的行。它是一项专门功能，但在若干场景中很有价值。

主要用途之一，是不索引常见值。查询某个占表中超过几个百分点的常见值时，通常不会使用索引，因此没必要为这些行保存索引条目。这样可以缩小索引，加快真正使用索引的查询；很多更新也更快，因为不必每次更新索引。

示例 11.1：排除常见值

假设数据库保存 Web 访问日志，大多数访问来自组织内部 IP 范围，少部分来自外部，例如拨号连接的员工。如果按 IP 搜索主要用于查询外部访问，就可能不需要索引组织子网。

表结构如下：

CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    ...
);

创建相应部分索引：

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND
           client_ip < inet '192.168.100.255');

可以使用此索引的典型查询：

SELECT *
FROM access_log
WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

这里 IP 被部分索引覆盖。下面的 IP 被排除，因此不能使用该索引：

SELECT *
FROM access_log
WHERE url = '/index.html' AND client_ip = inet '192.168.100.23';

这种索引要求事先知道常见值，最适合数据分布稳定的情况。也可以偶尔重建，以适应新分布，但会增加维护成本。

另一个用途是排除典型工作负载不感兴趣的值。这样具有同样优势，但这些值无法通过该索引访问，即使某次查询本来可能从索引扫描获益。因此需要谨慎设计和实验。

示例 11.2：排除不感兴趣的值

假设订单表包含已开票和未开票订单，未开票只占很小比例，却最常被访问。仅索引未开票行可以改善性能：

CREATE INDEX orders_unbilled_index ON orders (order_nr)
    WHERE billed is not true;

可使用索引的查询：

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

查询甚至可以完全不涉及 order_nr：

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

这不如直接在 amount 上建立部分索引高效，因为需要扫描整个索引。但未开票订单很少时，仅用这个索引定位它们，仍可能有收益。

下面的查询不能使用此索引：

SELECT * FROM orders WHERE order_nr = 3501;

因为订单 3501 既可能已开票，也可能未开票。

这个例子也说明，索引列与谓词列不必相同。只要仅涉及被索引表的列，PostgreSQL 支持任意谓词。

但谓词必须与希望受益的查询条件匹配。准确地说，只有系统能够识别查询 WHERE 条件在数学上蕴含索引谓词，才能使用部分索引。

PostgreSQL 没有复杂定理证明器，无法识别所有写法不同但数学等价的表达式。这种通用证明器既难实现，也可能太慢。系统能识别简单不等式，例如 x < 1 蕴含 x < 2；除此之外，谓词必须与 WHERE 的某部分完全匹配，否则不会认为索引可用。

匹配发生在规划阶段，而不是运行时。因此参数化条件不能这样使用部分索引。例如准备语句的 x < ?，不可能对参数所有可能值都蕴含 x < 2。

示例 11.3：部分唯一索引

第三种用途并不要求查询使用索引，而是在表子集上建立唯一索引，仅对满足谓词的行强制唯一性，不限制其他行。

假设表记录测试结果，希望同一 subject 与 target 组合只有一条成功记录，而失败记录可以任意多：

CREATE TABLE tests (
    subject text,
    target text,
    success boolean,
    ...
);

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

成功少、失败多时，这种方式特别高效。也可以通过带 IS NULL 条件的部分唯一索引，让某列最多允许一个 null。

部分索引还可以干预系统的执行计划选择。特殊数据分布可能让系统在不该使用索引时选择索引，此时可以让该索引不适用于问题查询。

不过 PostgreSQL 通常能合理选择索引，例如查询常见值时会主动避开索引。前面的常见值例子主要是节省索引大小，并不是必须靠部分索引阻止使用。严重错误的计划选择应提交缺陷报告。

设计部分索引意味着你认为自己至少与规划器一样了解何时使用索引有益。这需要经验，以及对 PostgreSQL 索引机制的理解。多数情况下，它相对普通索引的优势有限，甚至可能适得其反。

示例 11.4：不要用部分索引代替分区

你可能想创建大量互不重叠的部分索引：

CREATE INDEX mytable_cat_1 ON mytable (data) WHERE category = 1;
CREATE INDEX mytable_cat_2 ON mytable (data) WHERE category = 2;
CREATE INDEX mytable_cat_3 ON mytable (data) WHERE category = 3;
...
CREATE INDEX mytable_cat_N ON mytable (data) WHERE category = N;

这不是好主意。几乎可以肯定，单个普通索引更合适：

CREATE INDEX mytable_cat_data ON mytable (category, data);

按照多列索引章节所述原因，应把 category 放在前面。

虽然在更大的索引中搜索，可能多下降几层树，但通常仍比规划器逐个挑选部分索引便宜。问题核心是系统不理解这些部分索引间的关系，只能逐个检查是否适用。

如果表大到单个索引确实不合适，应考虑分区。分区机制让系统明确知道各表与索引不重叠，因此能够获得更好的性能。

更多部分索引资料见原文参考文献 [ston89b]、[olson93] 与 [seshadri95]。

---

原文：11.8. Partial Indexes。作者／维护方：PostgreSQL 全球开发组及文档贡献者。本文为中文翻译，代码及命令保留原文。