PostgreSQL 部分索引

假設(shè)我們要在一個(gè)數(shù)據(jù)庫(kù)中保存網(wǎng)頁服務(wù)器訪問日志。大部分訪問都來自于我們組織內(nèi)的IP地址，但是有些來自于其他地方（如使用撥號(hào)連接的員工）。如果我們主要通過IP搜索來自于外部的訪問，我們就沒有必要索引對(duì)應(yīng)于我們組織內(nèi)網(wǎng)的IP范圍。

假設(shè)有這樣一個(gè)表：

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

用以下命令可以創(chuàng)建適用于我們的部分索引：

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');

一個(gè)使用該索引的典型查詢是：

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

此處查詢的IP地址由部分索引覆蓋。以下查詢無法使用部分索引，因?yàn)樗褂脧乃饕信懦?IP 地址：

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

可以看到部分索引查詢要求公值能被預(yù)知，因此部分索引最適合于數(shù)據(jù)分布不會(huì)改變的情況。這樣的索引也可以偶爾被重建來適應(yīng)新的數(shù)據(jù)分布，但是這會(huì)增加維護(hù)負(fù)擔(dān)。

如果我們有一個(gè)表包含已上賬和未上賬的訂單，其中未上賬的訂單在整個(gè)表中占據(jù)一小部分且它們是最經(jīng)常被訪問的行。我們可以通過只在未上賬的行上創(chuàng)建一個(gè)索引來提高性能。創(chuàng)建索引的命令如下：

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

使用該索引的一個(gè)可能查詢是：

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列上部分索引有效，因?yàn)橄到y(tǒng)必須掃描整個(gè)索引。然而，如果有相對(duì)較少的未上賬訂單，使用這個(gè)部分索引來查找未上賬訂單將會(huì)更好。

注意這個(gè)查詢將不會(huì)使用該索引：

SELECT * FROM orders WHERE order_nr = 3501;

訂單3501可能在已上賬訂單或未上賬訂單中。

假設(shè)我們有一個(gè)描述測(cè)試結(jié)果的表。我們希望保證其中對(duì)于一個(gè)給定的主題和目標(biāo)組合只有一個(gè)“成功”項(xiàng)，但其中可能會(huì)有任意多個(gè)“不成功”項(xiàng)。實(shí)現(xiàn)它的方式是：

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

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

當(dāng)有少數(shù)成功測(cè)試和很多不成功測(cè)試時(shí)這是一種特別有效的方法。通過創(chuàng)建具有IS NULL限制的惟一部分索引，也可以允許列中僅有一個(gè)空。

你可能想嘗試創(chuàng)建一組巨大的、不重疊的部分索引，例如

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;

這是個(gè)個(gè)壞主意!幾乎可以肯定，使用一個(gè)非部分索引會(huì)更好一些，聲明如

CREATE INDEX mytable_cat_data ON mytable (category, data);

(將類別列放在前面，基于第 11.3 節(jié)所述的原因。) 雖然在這個(gè)更大的索引中進(jìn)行搜索可能比在更小的索引中進(jìn)行搜索要下降兩倍以上的樹級(jí)別， 但這幾乎肯定會(huì)比選擇適當(dāng)?shù)牟糠炙饕械囊粋€(gè)所需的規(guī)劃器的開銷更便宜。 問題的核心是系統(tǒng)不理解部分索引之間的關(guān)系，并將費(fèi)力地測(cè)試每個(gè)索引，以確定它是否適用于當(dāng)前查詢。

如果你的表足夠大，單個(gè)索引確實(shí)是一個(gè)壞主意，你應(yīng)該考慮使用分區(qū)代替(參見第 5.11 節(jié))。 通過這種機(jī)制，系統(tǒng)理解表和索引是不重疊的，就此而言可以獲得更好的性能。