部分索引指的是在表的一个子集上构建的索引；该子集由一个条件表达式（称为部分索引的谓词）定义。索引仅包含那些满足谓词的表行的条目。部分索引是一种专门的功能，但在多种情况下都非常有用。

使用部分索引的主要原因之一是避免索引常见的值。由于查询搜索常见值（占表行总数的百分之几以上）时无论如何都不会使用索引，因此完全没有必要将这些行保留在索引中。这可以减小索引的大小，从而加快那些确实使用索引的查询的速度。它还可以加快许多表更新操作的速度，因为并非所有情况下都需要更新索引。

部分索引也可以用于覆盖系统的查询计划选择。此外，数据集的特殊分布可能会导致系统在真正不应该使用索引时使用索引。在这种情况下，可以设置索引，使其不可用于有问题的查询。通常情况下，PostgreSQL 会对索引使用做出合理的决策（例如，它在检索常见值时会避免使用索引，因此前面的例子实际上只是节省了索引大小，并不是必须避免使用索引），而明显的错误计划选择应报告为错误。

一、设置部分索引以排除常见值

假设你正在数据库中存储 Web 服务器访问日志。大多数访问来自你组织的 IP 地址范围，但也有一些来自其他地方（例如，通过拨号连接的员工）。如果你主要通过 IP 地址搜索外部访问，那么你可能不需要索引对应于你组织子网的 IP 地址范围。

假设有一个这样的表：

1
2
3
4
5
CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    ...
);

要创建一个适合此示例的部分索引，可以使用以下命令：

1
2
3
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');

一个可以使用此索引的典型查询如下：

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

这里查询的 IP 地址被部分索引覆盖。以下查询无法使用部分索引，因为它使用了一个被索引排除的 IP 地址：

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

请注意，这种部分索引要求常见值预先确定，因此对于数据分布不经常变化的情况，部分索引最适合。可以定期重建部分索引以适应新的数据分布，但这会增加维护工作量。

二、设置部分索引以排除不感兴趣的值

部分索引的另一个可能用途是排除典型查询工作负载不感兴趣的值。这带来了与上述相同的优势，但它防止“不感兴趣”的值通过该索引被访问，即使在这种情况下索引扫描可能是有利的。显然，为此类场景设置部分索引需要大量的小心和实验。

假设你有一个表，其中包含已开票和未开票的订单，未开票的订单占总表的一小部分，但这些是最常访问的行。你可以通过创建仅包含未开票行的索引来提高性能。创建索引的命令如下：

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

一个可以使用此索引的查询如下：

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

然而，即使不涉及 order_nr 列的查询也可以使用此索引，例如：

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

这不如在 amount 列上创建部分索引高效，因为系统需要扫描整个索引。然而，如果未开票的订单相对较少，仅为了找到未开票的订单而使用此部分索引可能是有利的。

请注意，以下查询无法使用此索引：

1
SELECT * FROM orders WHERE order_nr = 3501;

订单 3501 可能是已开票或未开票的订单之一。

上述示例说明了索引列和谓词中使用的列不需要匹配。PostgreSQL 支持任意谓词的部分索引，只要涉及的列是被索引的表中的列即可。然而，需要注意的是，谓词必须与查询中预期受益的 WHERE 条件匹配。具体来说，只有当系统可以识别查询的 WHERE 条件在数学上隐含索引的谓词时，部分索引才能在查询中使用。PostgreSQL 没有复杂的定理证明器来识别以不同形式编写的数学等价表达式。（不仅创建这样的通用定理证明器极其困难，而且它可能会太慢而无法实际使用。）系统可以识别简单的不等式隐含关系，例如“x < 1”隐含“x < 2”；否则，谓词条件必须与查询的 WHERE 条件部分完全匹配，否则索引将不被视为可用。匹配发生在查询计划阶段，而不是运行时。因此，参数化的查询子句无法与部分索引配合使用。例如，带有参数的预准备查询可能指定“x < ?”，这永远不会隐含“x < 2”适用于所有参数值。

三、设置部分唯一索引

还有可能的用途是部分索引根本不需要在查询中使用。这里的思路是在表的一个子集上创建唯一索引。这确保了满足索引谓词的行是唯一的，而不约束那些不满足谓词的行。

假设你有一个描述测试结果的表。你希望确保给定主题和目标组合只有一个“成功”的条目，但可能有任意数量的“不成功”条目。这里是一个方法：

1
2
3
4
5
6
7
8
9
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 中索引工作原理的理解。在大多数情况下，部分索引相对于常规索引的优势微乎其微。在某些情况下，它们甚至会适得其反。

你可能会被诱惑创建一组不重叠的部分索引，例如：

1
2
3
4
5
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;

这是一个糟糕的想法！几乎可以肯定，使用一个非部分索引会更好，声明如下：

1
CREATE INDEX mytable_cat_data ON mytable (category, data);

虽然在这个更大的索引中搜索可能需要通过更多的树层，但这几乎肯定会比选择适当的部分索引所需的规划器努力更便宜。问题的核心在于系统不了解部分索引之间的关系，因此会费力地测试每个索引以查看其是否适用于当前查询。

如果你的表足够大，以至于单个索引真的不是一个好主意，你应该考虑使用分区。通过这种方法，系统理解这些表和索引是不重叠的，因此可以实现更好的性能。

关于部分索引的更多信息可以在如下文章中找到：

相关链接

PostgreSQL: Documentation: 16: 11.8. Partial Indexes

PostgreSQL: Documentation: 16: 5.11. Table Partitioning

PostgreSQL: Documentation: 16: Bibliography STON89B

PostgreSQL: Documentation: 16: Bibliography OLSON93

PostgreSQL: Documentation: 16: Bibliography SESHADRI95

OB tags

#PostgreSQL