PostgreSQL 部分索引

假设我们要在一个数据库中保存网页服务器访问日志。大部分访问都来自于我们组织内的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';

可以看到部分索引查询要求公值能被预知，因此部分索引最适合于数据分布不会改变的情况。这样的索引也可以偶尔被重建来适应新的数据分布，但是这会增加维护负担。

如果我们有一个表包含已上账和未上账的订单，其中未上账的订单在整个表中占据一小部分且它们是最经常被访问的行。我们可以通过只在未上账的行上创建一个索引来提高性能。创建索引的命令如下：

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可能在已上账订单或未上账订单中。

假设我们有一个描述测试结果的表。我们希望保证其中对于一个给定的主题和目标组合只有一个“成功”项，但其中可能会有任意多个“不成功”项。实现它的方式是：

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

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

当有少数成功测试和很多不成功测试时这是一种特别有效的方法。通过创建具有IS NULL限制的惟一部分索引，也可以允许列中仅有一个空。

你可能想尝试创建一组巨大的、不重叠的部分索引，例如

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 节)。 通过这种机制，系统理解表和索引是不重叠的，就此而言可以获得更好的性能。