11.8 部分索引#

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

使用部分索引的一个主要原因是为了避免对常见值进行索引。由于搜索常见值（占所有表行的百分之几）的查询无论如何都不会使用索引，因此完全没有必要将那些行保留在索引中。这会减小索引的大小，从而会加快使用索引的那些查询。它还会加快许多表更新操作，因为在所有情况下都不需要更新索引。示例 11.1展示了此理念的一个可能的应用。

示例 11.1 设置部分索引以排除常见值

假设您正在数据库中存储 Web 服务器访问日志。大多数访问都源自您组织的 IP 地址范围，但有些访问来自其他地方（例如，拨号连接的员工）。如果您的 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中进行了展示。这会产生与上面列出的相同优势，但它可以防止通过该索引访问““无意义””值，即使在这种情况下索引扫描可能有利可图。显然，针对此类方案设置部分索引需要非常小心并进行大量试验。

示例 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 可能在已计费或未计费订单中。

示例 11.2还说明了索引列和谓词中使用的列不必匹配。PostgreSQL支持具有任意谓词的部分索引，只要涉及的只是正在编制索引的表的列。但是，请记住，谓词必须与应从索引中受益的查询中使用的条件相匹配。确切地说，只有当系统能够识别查询的WHERE条件在数学上暗示索引的谓词时，才能在查询中使用部分索引。PostgreSQL没有一个复杂的定理证明器，它可以识别以不同形式编写的数学等价表达式。（不仅这种通用定理证明器极其难以创建，而且它可能太慢而无法真正使用。）系统可以识别简单的不等式含义，例如“x < 1”暗示“x < 2”；否则，谓词条件必须与查询的WHERE条件的一部分完全匹配，否则该索引将不被识别为可用。匹配在查询计划时发生，而不是在运行时发生。因此，参数化查询子句不适用于部分索引。例如，带有参数的准备查询可能指定“x < ?”，对于参数的所有可能值，它永远不会暗示“x < 2”。

部分索引的第三个可能用途根本不需要在查询中使用索引。此处的想法是在表的子集上创建一个唯一索引，如示例 11.3中所示。这强制满足索引谓词的行之间的唯一性，而不约束不满足索引谓词的行。

示例 11.3. 设置部分唯一索引

假设我们有一个描述测试结果的表。我们希望确保对于给定的主题和目标组合，只有一个“成功”条目，但可能有多个“不成功”条目。以下是一种实现方法

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

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

当成功测试较少且不成功测试较多时，这是特别有效的方法。还可以通过使用带有IS NULL限制的唯一部分索引来允许一列中只有一个空值。

最后，部分索引还可以用于覆盖系统的查询计划选择。此外，分布奇特的数据集可能导致系统在实际不应该使用索引时使用索引。在这种情况下，可以设置索引，使其对违规查询不可用。通常情况下，PostgreSQL会对索引使用做出合理的判断（例如，在检索常见值时避免使用索引，因此前面的示例实际上只节省了索引大小，而不需要避免使用索引），而严重错误的计划选择是提交错误报告的原因。

请记住，设置部分索引表明您至少了解查询计划程序所了解的内容，特别是您知道何时索引可能有利可图。形成这种知识需要经验和对PostgreSQL中索引工作原理的理解。在大多数情况下，部分索引相对于常规索引的优势将很小。在某些情况下，它们会产生适得其反的效果，如示例 11.4中所示。

示例 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);

（出于第 11.3 节中描述的原因，请将类别列放在第一位。）虽然在此较大索引中进行搜索可能比在较小索引中进行搜索时需要下降更多树级别，但这几乎肯定比计划程序为选择适当的部分索引而付出的努力要便宜。问题的核心在于，系统不了解部分索引之间的关系，并且会费力地测试每个部分索引以查看它是否适用于当前查询。

如果您的表足够大，以至于单个索引确实是一个糟糕的主意，则应该考虑使用分区（请参阅第 5.11 节）。使用该机制，系统确实理解这些表和索引是不重叠的，因此可以实现更好的性能。

有关部分索引的更多信息，请参阅[ston89b]、[olson93]和[seshadri95]。

11.8 部分索引# ​

11.8 部分索引#