假設我們有一個類似於這樣的資料表：

CREATE TABLE test1 (
    id integer,
    content varchar
);

並且應用程序發出許多這樣形式的查詢：

SELECT content FROM test1 WHERE id = constant;

如果沒有提前準備，系統必須逐行掃描整個 test1 資料表，以查詢所有符合的項目。如果 test1 中有很多資料列，並且只有幾個資料列（可能是零個或一個）會被這樣的查詢回傳，這顯然是一種效率低下的方法。但是，如果系統已被指示在 id 欄位上維護索引，則可以使用更有效的方法來定位符合的資料列。例如，它可能只需要深入走幾層搜索樹就好。

在大多數非小說類書籍中使用了類似的方法：讀者經常查詢的術語和概念收集在本書末尾的字母索引中。感興趣的讀者可以相對快速地掃描索引並翻到適當的頁面，而不必閱讀整本書以找到感興趣的內容。正如作者的任務是預測讀者可能會查詢的項目一樣，資料庫管理員的任務是預測哪些索引有用。

可以使用以下指令在 id 欄位上建立索引，如下所示：

CREATE INDEX test1_id_index ON test1 (id);

可以自由選擇名稱 test1_id_index，但是您應該選擇能夠讓您以後記住索引的名字。

要移除索引，請使用 DROP INDEX 指令。 可以隨時向資料表中增加索引或從資料表中移除索引。

建立索引後，就不需要進一步操作：系統將在修改資料表時更新索引，並且當它認為這樣做比使用循序資料表掃描更有效時，它將在查詢中使用索引。但是，您可能必須定期執行 ANALYZE 指令以更新統計訊息，以允許查詢計劃程序做出明智的決策。有關如何確定是否使用索引以及計劃程序何時以及為何可以選擇不使用索引的訊息，請參閱第 14 章。

索引還可以使用搜索條件使 UPDATE 和 DELETE 指令受益。此外，索引可用於交叉查詢。因此，在作為交叉查詢條件一部分的欄位上定義的索引也可以顯著加快交叉查詢。

在大型資料表上建立索引可能需要很長時間。預設情況下，PostgreSQL 允許在索引建立的同時在資料表上進行讀取（SELECT 語句），但寫入（INSERT，UPDATE，DELETE）將被阻止，直到索引建構完成。在産品環境中，這通常是不可接受的。允許寫入與索引建立同時發生是可能的，但有幾點值得注意 - 有關更多訊息，請參閱同步建立索引。

建立索引後，系統必須使其與資料表保持同步。這增加了資料操作的開銷。因此，應移除在查詢中很少或從不使用的索引。

索引是增強資料庫效能的常用方法。索引允許資料庫伺服器比沒有索引時更快地查詢和檢索特定資料列。但索引也會增加整個資料庫系統的開銷，因此應該合理地使用它們。

這裡增加說明一個查詢的 ORDER BY 原則，毋須單獨的排序步驟即可獲得。目前 PostgreSQL 支援的索引類型中，只有 B-tree 可以產生有序輸出 - 其他索引類型以未指定的，依賴於實作上的順序回傳符合的資料列。

規劃程予將考慮透過掃描與語法符合的可用索引，或者透過按儲存循序掃描資料表並進行明確的排序來滿足 ORDER BY 語法。對於需要掃描資料表的大部分的查詢，明確的排序中一個重要的特殊情況是 ORDER BY 與 LIMIT n 結合使用：必須對所有資料完全排序，以取得前 n 筆資料，但如果存在與 ORDER BY 符合的索引，則可以直接檢索前 n 筆資料，而不掃描剩餘的資料列。

預設情況下，B-tree 索引按升羃儲存其項目，最後為空。這樣産生的索引是 x 欄位上索引的正向掃描，產生的輸出滿足 ORDER BY x（或者更詳細，ORDER BY x ASC NULLS LAST）。也可以向後掃描，產生滿足 ORDER BY x DESC 的輸出（或者更詳細地說，ORDER BY x DESC NULLS FIRST，因為 NULLS FIRST 是ORDER BY DESC 的預設值）。

您可以透過在建笠索引時包含選項 ASC，DESC，NULLS FIRST 或 NULLS LAST來調整 B-tree 索引的順序；例如：

CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

首先以空值以升羃儲存的索引可以滿足 ORDER BY x ASC NULLS FIRST 或 ORDER BY x DESC NULLS LAST，具體取決於掃描的方向。

您可能想知道為什麼還要提供所有四個選項，當兩個選項和後向掃描的可能性將覆蓋 ORDER BY 的所有變形時。在單欄位索引中，選項確實是多餘的，但在多欄位索引中它們可能很有用。 考慮 (x, y) 上的兩欄位索引：如果我們向前掃描，這可以滿足 ORDER BY x,y，如果我們向後掃描，則可以滿足 ORDER BY x DESC, y DESC。但可能是應用程序經常需要使用 ORDER BY x ASC, y DESC。無法從普通索引獲取該排序，但如果索引定義為 (x ASC, y DESC) 或 (x DESC, y ASC)，則可能。

顯然，具有非隱含排序順序的索引是一個相當專業的功能，但有時它們可以為某些查詢產生巨大的加速。是否值得維護這樣的索引取決於您使用需要特殊排序順序的查詢的頻率。

PostgreSQL 提供了幾種索引型別：B-tree，Hash，GiST，SP-GiST，GIN 和 BRIN。每種索引型別依適合類型的查詢使用不同的演算法。預設情況下， CREATE INDEX 指令建立適合最常見情況的 B-tree 索引。

B-tree 可以處理為某種排序的資料比較和範圍查詢。特別是，只要使用以下運算子之一進行比較時，PostgreSQL 查詢計劃程序就會考慮使用 B-tree 索引：

也可以使用 B-tree 索引搜尋來實作等同於這些運算子的組合的語法，例如 BETWEEN 和 IN。此外，索引欄位上的 IS NULL 或 IS NOT NULL 條件也可以與 B-tree 索引一起使用。

對於涉及樣式比對運算子 LIKE 和 ~ 的查詢，最佳化程序也可以使用 B-tree 索引，如果是樣式是常數並且放在字串的開頭的話 - 例如，col LIKE 'foo%' 或 col~' ^ foo'，但 col LIKE '%bar' 就不是。但是，如果您的資料庫不使用 C 語言環境，則需要使用特殊的運算子類建立索引，以支援樣式比對查詢的索引；詳見下面的第 11.9 節。也可以對 ILIKE 和 ~* 使用 B-tree 索引，但前提是樣式以非字母字元開頭，即不受大/小寫轉換影響的字元。

B-tree 索引也可用於按排序順序檢索資料。這並不一定會比簡單的掃描及排序更快，但通常會很有幫助。

Hash 索引只能處理簡單的相等比較。只要使用 = 運算子在比較中涉及索引欄位，查詢計劃程序就會考慮使用 Hash 索引。以下指令用於建立 Hash 索引：

CREATE INDEX name ON table USING HASH (column);

GiST 索引不是一種索引，而是一種可以實作許多不同索引策略的基礎結構。因此，可以使用 GiST 索引的特定運算子根據索引策略（運算子類）而變化。例如，PostgreSQL 的標準版本包括幾個二維幾何資料型別的 GiST 運算子類，它們支援使用這些運算子的索引查詢：

（有關這些運算子的含義，請參閱第 9.11 節。）標準版本中包含的 GiST 運算子類記錄在 Table 62.1 中。許多其他 GiST 運算子類在 contrib 套件中可用或作為單獨的專案支援。有關更多訊息，請參閱第 62 章。

GiST 索引還能夠最佳化「最近鄰居」搜尋，例如

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

找到最接近給予目標點的 10 個位置。執行此操作的能力再次取決於所使用的特定運算子類。在 Table 62.1 中，可以以這種方式使用的運算子列在「Ordering Operators」欄位中。

SP-GiST 索引（如 GiST 索引）提供支援各種搜尋的基礎結構。SP-GiST 允許實作各種不同的非平衡的磁碟資料結構，例如 quadtree，k-d tree 和 radix tree。 例如，PostgreSQL 的標準版本包括用於二維空間的 SP-GiST 運算子類，它支援使用這些運算子的索引查詢：

（有關這些運算子的含義，請參閱第 9.11 節。）標準版本中包含的 SP-GiST 運算子類記錄在 Table 63.1 中。有關更多訊息，請參閱第 63 章。

GIN 索引是「反向索引」，適用於包含多個值的組合的資料值，例如陣列。反向索引包含每個組合值的單獨項目，並且可以有效地處理測試特定組合值是否存在的查詢。

與 GiST 和 SP-GiST 一樣，GIN 可以支援許多不同的使用者定義的索引策略，並且可以使用 GIN 索引的特定運算子根據索引策略而有所不同。例如，PostgreSQL 的標準發行版包括一個陣列的 GIN 運算子類，它支援使用這些運算子的索引查詢：

（有關這些運算子的含義，請參閱第 9.18 節。）標準版本中包含的 GIN 運算子類記錄在 Table 64.1 中。許多其他 GIN 運算子類在 contrib 套件中可用或作為單獨的專案支援。有關更多訊息，請參閱第 64 章。

BRIN 索引（Block Range Indexes 的簡寫）儲存關於儲存在資料表的連續物理區塊範圍中值的摘要。與 GiST，SP-GiST 和 GIN 一樣，BRIN 可以支援許多不同的索引策略，並且可以使用 BRIN 索引的特定運算子根據索引策略而變化。對於具有線性排序順序的資料類型，索引數據對應於每個區塊範圍的欄位中值的最小值和最大值。這支援使用這些運算子的索引查詢：

Table 65.1 中記錄了標準發行版中包含的 BRIN 運算子類。有關更多訊息，請參閱第 65 章。

部分索引（partial index）是在資料表的子集上建構的索引；子集由條件表示式（所以稱作為部分索引）定義。索引僅包含滿足條件的資料列。部分索引是一個特別的功能，有幾種情況下很有用。

使用部分索引的一個主要原因是避免索引常見值。由於搜尋公共值（佔所有資料表的某個比例以上的值）的查詢無論如何都不會使用索引，因此根本不應該將這些資料列保留在索引中。這可以減少索引的大小，將加速那些使用索引的查詢。它還將加速許多資料表的更新操作，因為索引不需要在所有情況下都更新。範例 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';

無法使用此索引的查詢是：

SELECT *
FROM access_log
WHERE 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;

由於系統必須掃描整個索引，因此這不如部分索引有效。然而，如果有相對較少的未開票訂單，使用此部分索引只是為了找到未開票的訂單可能是一個使用的理由。

請注意，此查詢無法使用此索引：

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. 設定部分唯一索引

假設我們有一個描述測試結果的資料表。 我們希望確保給定 subject 和 target 組合只有一個「successful」項目，但可能存在任意數量的「unsuccessful」項目。這是一種方法：

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

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

當幾乎沒有成功的測試和許多不成功的測試時，這是一種特別有效的方法。

最後，部分索引也可用於覆蓋系統的查詢計劃選擇。此外，具有特殊分佈的資料集可能會導致系統在不應該使用索引時使用索引。在這種情況下，可以設定索引，使其不可用於違規查詢。通常，PostgreSQL對索引使用做出了合理的選擇（例如，它在檢索常見值時避免使用它們，因此前面的範例實際上只保存索引大小，不需要避免索引使用），並且嚴重錯誤的計劃選擇是導致錯誤的回報。

請記住，設定部分索引表示您至少知道查詢計劃程序知道的內容，特別是您知道索引何時可能有利可圖。形成這些知識需要經驗和對 PostgreSQL 中索引如何工作的理解。在大多數情況下，部分索引優於常規索引的優勢會是很小的。

有關部分索引的更多訊息可以在[ston89b]，[olson93]和[seshadri95]中找到。

索引欄位不必只是基礎資料表的一個欄位，而是可以是從資料表的一個欄位或多個欄位計算的函數或 scalar 表示式。此功能對於根據計算結果來快速存取資料表非常有用。

例如，進行不區分大小寫的比較的常用方法是使用 lower 函數：

SELECT * FROM test1 WHERE lower(col1) = 'value';

如果已在 lower(col1) 函數的結果上定義了一個索引，則此查詢可以使用索引：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

如果我們要宣告這個索引 UNIQUE，它將阻止建立 col1 值僅在大小寫情況下不同的資料，以及 col1 值實際上相同的資料。因此，表示式上的索引可用於強制執行不能作為簡單唯一性定義的限制條件。

另一個例子，如果經常進行如下查詢：

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

那麼可能值得建立這樣的索引：

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

CREATE INDEX 指令的語法通常需要在索引表示式使用括號，如第二個範例所示。當表示式只是函數呼叫時，可以省略括號，如第一個範例中所示。

索引表示式的維護成本相對較高，因為必須在插入時和每次更新時為每一行計算衍生表示式。但是，索引表示式在索引搜尋期間不會重新計算，因為它們已儲存在索引中。在上面的兩個範例中，系統將查詢視為 WHERE indexedcolumn ='constant'，因此搜尋速度等同於任何其他簡單索引查詢。因此，當檢索速度比插入和更新速度更重要時，表示式上的索引就很有用。

單個索引掃描只能使用將索引的欄位與其運算子類的運算子一起使用的查詢子句，並以 AND 連接。例如，給予 (a, b) 上的索引，如 WHERE a = 5 AND b = 6 的查詢條件可以使用索引，但是像 WHERE a = 5 OR b = 6 這樣的查詢就無法直接使用索引。

幸運的是，PostgreSQL 能夠組合多個索引（包括同一索引的多次使用）來處理單個索引掃描無法對應的情況。系統可以在多個索引掃描中形成 AND 和 OR 條件。例如，像 WHERE x = 42 OR x = 47 OR x = 53 OR x = 99 這樣的查詢可以分解為x 上索引的四個單獨掃描，每次掃描使用一個查詢子句。然後將這些掃描的結果進行「或」運算以產生結果。另一個例子是，如果我們在 x 和 y 上有單獨的索引，那麼像 WHERE x = 5 AND y = 6 這樣的查詢的一種可能情況是使用具有適當查詢子句的每個索引，然後將索引結果和 AND 一起識別結果資料列。

為了組合多個索引，系統掃描每個所需的索引會在記憶體中準備一個 bitmap，給予回報與索引條件符合的資料表中資料列的位置。然後根據查詢的需要對 bitmap 進行 AND 運算和 OR 運算。最後，存取並回傳實際資料表中的資料列。資料列按磁碟循序存取，因為這是 bitmap 的檔案結構；這意味著原始索引的任何排序都會遺失，因此如果查詢具有 ORDER BY 子句，則需要單獨的排序步驟。由於這個原因，並且因為每次額外的索引掃描都會增加額外的時間，所以計劃程序有時會選擇使用簡單的索引掃描，即使可以使用其他索引也是如此。

除了最簡單的應用程序之外，還有各種可能有用的索引組合，資料庫開發人員必須進行權衡以決定提供哪些索引。有時多欄位索引是最好的，但有時最好建立單獨的索引並依賴索引組合功能。例如，如果您的工作負載包含有時只涉及欄位 x 的查詢混合，有時只涉及欄位 y，有時只有兩個欄位，您可以選擇在 x 和 y 上建立兩個單獨的索引，依靠索引組合來處理查詢使用兩個欄位。您還可以在 (x, y) 上建立多個欄位索引。對於涉及兩個欄位的查詢，此索引通常比索引組合更有效，但如所述，對於僅涉及 y 的查詢，它幾乎無用，因此它不應該是唯一的索引。多欄位索引和 y 上單獨索引的組合可以很好地發揮作用。對於僅涉及 x 的查詢，可以使用多列索引，儘管它會更大，因此比單獨 x 上的索引慢。最後一種方法是建立所有三個索引，但如果資料的搜尋頻率比更新頻率更高，並且所有三種類型的查詢都很常見，則這可能是合理的。如果其中一種類型的查詢比其他類型的查詢少得多，那麼您可能只想建立最符合常見類型的兩個索引。

索引還可用於強制欄位值的唯一性，或多個欄位的組合值唯一性。

CREATE UNIQUE INDEX name ON table (column [, ...]);

目前，只能將 B-tree 索引宣告為唯一。

當索引宣告為唯一時，不允許具有相等索引值有多筆資料。但空值不被視為相等， 多欄位唯一索引僅拒絕所有索引欄位在多筆資料中相等的情況。

當為資料表定義唯一限制條件或主鍵時，PostgreSQL 會自動建立唯一索引。索引涵蓋構成主鍵或唯一限制條件的欄位（如果適用，則為多欄位索引），並且是強制執行限制條件的機制。

注意 毋須在宣告唯一性的欄位上手動建立索引；這樣做只會複製自動建立的索引。

PostgreSQL 中的所有索引都是次要索引 (Secondary Index)，這意味著每個索引都與資料表的主要資料區（在 PostgreSQL 術語中稱為資料表的 heap）分開儲存。也就是說，在一般的索引掃描中，每筆資料檢索都需要從索引和 heap 中取得資料。此外，雖然與給定可索引 WHERE 條件匹配的索引項目通常在索引中會放在一起，但它們連結的資料列可能在 heap 中的任何位置。因此，索引掃描的 heap 存取部分涉及對 heap 的大量隨機存取，這可能會很慢，尤其是在傳統的儲存媒體上。 （如第 11.5 節中所述，bitmap 掃描嘗試透過按排序順序進行 heap 存取來降低這個成本，但這也只是到目前為止唯一所能做的事。）

為了解決此效能問題，PostgreSQL 支援了 Index-only 掃描，此種掃描表示只需要從索引中回覆查詢，而不涉及任何 heap 的存取。基本思想是直接從每個索引項目中回傳值，而不需要查詢關聯的 heap 內容。何時可以使用此方法有兩個基本限制：

1. 索引類型必須支持 Index-only 掃描。B-tree 索引的所有操作都能適用。 GiST 和 SP-GiST 索引支援了某些運算子類別的 Index-only 掃描，但並沒有支援其他的運算子類別。除了上述以外的索引目前不支援此功能。 基本要求是索引必須實體儲存或能夠回傳每個索引項目的原始資料值。作為反例，GIN 索引不能支援 Index-only 掃描，因為每個索引項目通常僅保留原始資料值的一部分。

2. 該查詢必須僅引用儲存在索引中的欄位。例如，在某三個欄位 (x, y, z) 的資料表宣告了 x 和 y 欄位上的索引，這些查詢就可以使用 Index-only 掃描：

   Copy

   SELECT x, y FROM tab WHERE x = 'key';
   SELECT x FROM tab WHERE x = 'key' AND y < 42;

   但是這些查詢就不能使用：

   Copy

   SELECT x, z FROM tab WHERE x = 'key';
   SELECT x FROM tab WHERE x = 'key' AND z < 42;

   （後續所述的表示式索引和部分索引使會此規則複雜化。）

如果滿足這兩個基本要求，那麼查詢所需的所有資料都可以從索引中獲得，因此實際執行面來說，Index-only 掃描是可能的。但是 PostgreSQL 中的任何資料表掃描還有一個額外的要求：它必須驗證對查詢的 MVCC 快照「可見」每筆檢索到的資料列，如第 13 章所述。可見性資訊並不儲存在索引之中，而僅儲存在 heap 之中；因此乍看之下似乎每筆資料檢索都將需要存取 heap。如果資料表最近被修改了，則確實如此。但是，對於很少修改資料的情況，有一種解決此問題的方法。PostgreSQL 針對資料表中的每個頁面追踪該頁面中儲存的所有資料列版本是否足夠舊，以至於所有目前和將來的事務均是可見的。此資訊儲存在資料表的可見性檢視表中。 Index-only 掃描在找到候選索引項目之後，檢查相應 heap 頁面的可見性映射位元。如果已設置，則該筆資料是可見的，因此不需要進一步的工作即可回傳資料。如果未設定，則必須存取 heap 以確認它是否為可見，因此與標準的索引掃描相比，不見得能得到效能優勢。即使在成功的情況下，這種方法也將可見性檢視存取再切換成 heap 存取。但是由於可見性檢視表比它所記錄的 heap 小四個數量級，因此存取它所需的實體 I/O 顯然會少得多。在大多數情況下，可見性檢視表會一直保持快取在記憶體之中。

簡而言之，儘管在滿足兩個基本要求的情況下可以進行 Index-only 掃描，但是只有在資料表的 heap 頁面很大一部分都設定了其所有可見性映射位元的情況下，它才會是可用的。但是，由於普遍上大部分資料列不變的資料表是普遍的情況，所以在實務上中這種掃描會非常有用。

To make effective use of the index-only scan feature, you might choose to create a covering index, which is an index specifically designed to include the columns needed by a particular type of query that you run frequently. Since queries typically need to retrieve more columns than just the ones they search on, PostgreSQL allows you to create an index in which some columns are just “payload” and are not part of the search key. This is done by adding an INCLUDE clause listing the extra columns. For example, if you commonly run queries like

SELECT y FROM tab WHERE x = 'key';

the traditional approach to speeding up such queries would be to create an index on x only. However, an index defined as

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

could handle these queries as index-only scans, because y can be obtained from the index without visiting the heap.

Because column y is not part of the index's search key, it does not have to be of a data type that the index can handle; it's merely stored in the index and is not interpreted by the index machinery. Also, if the index is a unique index, that is

CREATE UNIQUE INDEX tab_x_y ON tab(x) INCLUDE (y);

the uniqueness condition applies to just column x, not to the combination of x and y. (An INCLUDE clause can also be written in UNIQUE and PRIMARY KEY constraints, providing alternative syntax for setting up an index like this.)

It's wise to be conservative about adding non-key payload columns to an index, especially wide columns. If an index tuple exceeds the maximum size allowed for the index type, data insertion will fail. In any case, non-key columns duplicate data from the index's table and bloat the size of the index, thus potentially slowing searches. And remember that there is little point in including payload columns in an index unless the table changes slowly enough that an index-only scan is likely to not need to access the heap. If the heap tuple must be visited anyway, it costs nothing more to get the column's value from there. Other restrictions are that expressions are not currently supported as included columns, and that only B-tree and GiST indexes currently support included columns.

Before PostgreSQL had the INCLUDE feature, people sometimes made covering indexes by writing the payload columns as ordinary index columns, that is writing

CREATE INDEX tab_x_y ON tab(x, y);

even though they had no intention of ever using y as part of a WHERE clause. This works fine as long as the extra columns are trailing columns; making them be leading columns is unwise for the reasons explained in Section 11.3. However, this method doesn't support the case where you want the index to enforce uniqueness on the key column(s).

Suffix truncation always removes non-key columns from upper B-Tree levels. As payload columns, they are never used to guide index scans. The truncation process also removes one or more trailing key column(s) when the remaining prefix of key column(s) happens to be sufficient to describe tuples on the lowest B-Tree level. In practice, covering indexes without an INCLUDE clause often avoid storing columns that are effectively payload in the upper levels. However, explicitly defining payload columns as non-key columns reliably keeps the tuples in upper levels small.

In principle, index-only scans can be used with expression indexes. For example, given an index on f(x) where x is a table column, it should be possible to execute

SELECT f(x) FROM tab WHERE f(x) < 1;

as an index-only scan; and this is very attractive if f() is an expensive-to-compute function. However, PostgreSQL's planner is currently not very smart about such cases. It considers a query to be potentially executable by index-only scan only when all columns needed by the query are available from the index. In this example, x is not needed except in the context f(x), but the planner does not notice that and concludes that an index-only scan is not possible. If an index-only scan seems sufficiently worthwhile, this can be worked around by adding x as an included column, for example

CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);

An additional caveat, if the goal is to avoid recalculating f(x), is that the planner won't necessarily match uses of f(x) that aren't in indexable WHERE clauses to the index column. It will usually get this right in simple queries such as shown above, but not in queries that involve joins. These deficiencies may be remedied in future versions of PostgreSQL.

Partial indexes also have interesting interactions with index-only scans. Consider the partial index shown in Example 11.3:

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

In principle, we could do an index-only scan on this index to satisfy a query like

SELECT target FROM tests WHERE subject = 'some-subject' AND success;

But there's a problem: the WHERE clause refers to success which is not available as a result column of the index. Nonetheless, an index-only scan is possible because the plan does not need to recheck that part of the WHERE clause at run time: all entries found in the index necessarily have success = true so this need not be explicitly checked in the plan. PostgreSQL versions 9.6 and later will recognize such cases and allow index-only scans to be generated, but older versions will not.\

可以在資料表的多個欄位上定義索引。例如，如果您有此形式的資料表：

CREATE TABLE test2 (
  major int,
  minor int,
  name varchar
);

（比如，你將 /dev 目錄內容儲存在資料庫中......）並經常發出如下查詢：

SELECT name FROM test2 WHERE major = constant AND minor = constant;

那麼在 major 和 minor 欄位上定義一個索引可能是合適的，例如：

CREATE INDEX test2_mm_idx ON test2 (major, minor);

目前，只有 B-tree、GiST、GIN 和 BRIN 索引型別支援多欄位索引。最多可以指定 32 個欄位。（編譯 PostgreSQL 時可以變更此限制；請參閱檔案 pg_config_manual.h。）

多欄位 B-tree 索引可以與涉及索引欄位的任何子集的查詢條件一起使用，但是當前導（最左側）欄位存在限制條件時，索引最有效。確切的規則是對前導欄位的等式限制條件以及第一欄位上沒有等式限制條件的任何不等式限制條件將用於索引的條件掃描。在索引中檢查對這些欄位右側的欄位限制條件，因此它們可以儲存對資料表的正確存取，而不會減少必須掃描的索引部分。例如，給定 (a, b, c) 上的索引和查詢條件 WHERE a = 5 AND b >= 42 AND c < 77，必須從第一個項目掃描索引，其中 a = 5 且 b = 42，直到最後一個項目 a = 5。將跳過 c >= 77 的索引條目，但仍然需要掃描它們。該索引原則上可以用於對 b 或 c 有限制條件，但對 a 沒有限制條件的查詢 - 只是必須掃描整個索引，因此在大多數情況下，查詢規劃程序更喜歡使用索引進行循序資料表掃描。

多欄位 GiST 索引可以與涉及索引欄位的任何子集的查詢條件一起使用。其他欄位的條件限制索引回傳的項目，但第一欄位的條件是確定需要掃描多少索引的最重要條件。如果 GiST 索引的第一欄位只有幾個不同的值，即使其他欄位中有許多不同的值，它也會相對無效。

多欄位 GIN 索引可以與涉及索引欄位的任何子集的查詢條件一起使用。與 B-tree 或 GiST 不同，無論查詢條件使用哪個索引欄位，索引搜尋的有效性都是相同的。

多欄位 BRIN 索引可以與涉及索引欄位的任何子集的查詢條件一起使用。與 GIN 類似，與 B-tree 或 GiST 不同，無論查詢條件使用哪個索引欄位，索引搜尋有效性都是相同的。在單個資料表上具有多個 BRIN 索引而不是一個多欄位 BRIN 索引的唯一原因是具有不同的 pages_per_range 儲存參數。

當然，每個欄位必須與適合索引類型的運算子一起使用；涉及其他運算子的子句將不予考慮。

應謹慎使用多欄位索引。在大多數情況下，單個欄位上的索引就足夠了，節省了空間和時間。除非資料表的使用非常特殊，否則具有三個欄位以上的索引不太可能有用。有關不同索引配置的優點的一些討論，另請參閱第 11.5 節和第 11.9 節。

索引定義可以為索引的每個欄位指定運算子類。

CREATE INDEX name ON table (column opclass [sort options] [, ...]);

運算子類標示該欄位的索引所要使用的運算子。例如，int4 型別的 B-tree 索引將使用 int4_ops 類；此運算子類包含 int4 型別的比較函數。實際上，欄位的資料型別的預設運算子類通常就足夠了。指定運算子類的主要原因是對於某些資料型別，可能存在多個有意義的索引行為。例如，我們可能希望按絕對值或複數的實部資料型別進行排序。我們可以透過為資料型別定義兩個運算子類，然後在建立索引時選擇適當的子類來實現。運算子類決定基本排序順序（然後可以通過增加排序選項 COLLATE，ASC / DESC 和 NULLS FIRST / NULLS LAST 來變更）。

除了預設的子類之外，還有一些內建的運算子類：

• 運算子類 text_pattern_ops，varchar_pattern_ops 和 bpchar_pattern_ops 分別支援型別 text，varchar 和 char 上的 B-tree 索引。與預設運算子類的不同之處在於，這些值嚴格按字元進行比較，而不是根據特定於語言環境的排序規則進行比較。這使得當資料庫不使用標準“C”語言環境時，這些運算子類適合於涉及樣式匹配表示式（LIKE 或 POSIX 正規表示式）的查詢。 例如，您可以像這樣索引 varchar 欄位：

  Copy

  CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

  請注意，如果希望涉及普通 <，<=，> 或 >= 比較的查詢使用索引，還應使用預設運算子類建立索引。此類查詢不能使用 xxx_pattern_ops 運算子類。（但是，普通的相等比較可以使用這些運算子類。）可以使用不同的運算子類在同一欄位上建立多個索引。如果確實使用了「C語言」（C locale）環境，則不需要 xxx_pattern_ops 運算子類，因為具有預設運算子類的索引可用於「C」語言環境中的樣式匹配查詢。

以下查詢顯示所有已定義的運算子類：

SELECT am.amname AS index_method,
       opc.opcname AS opclass_name,
       opc.opcintype::regtype AS indexed_type,
       opc.opcdefault AS is_default
    FROM pg_am am, pg_opclass opc
    WHERE opc.opcmethod = am.oid
    ORDER BY index_method, opclass_name;

運算子類實際上只是一個稱為運算子族的較大結構的子集。 在多個資料型別具有相似行為的情況下，定義跨資料型別運算子並允許它們使用索引通常很有用。為此，必須將每種型別的運算子類分組到同一運算子族中。跨型別運算子是該族的成員，但不與該族中的任何單個子類相關聯。

上一個查詢的延伸版本顯示了每個運算子類所屬的運算子族：

SELECT am.amname AS index_method,
       opc.opcname AS opclass_name,
       opf.opfname AS opfamily_name,
       opc.opcintype::regtype AS indexed_type,
       opc.opcdefault AS is_default
    FROM pg_am am, pg_opclass opc, pg_opfamily opf
    WHERE opc.opcmethod = am.oid AND
          opc.opcfamily = opf.oid
    ORDER BY index_method, opclass_name;

此查詢顯示所有已定義的運算子系列以及每個系列中包含的所有運算子：

SELECT am.amname AS index_method,
       opf.opfname AS opfamily_name,
       amop.amopopr::regoperator AS opfamily_operator
    FROM pg_am am, pg_opfamily opf, pg_amop amop
    WHERE opf.opfmethod = am.oid AND
          amop.amopfamily = opf.oid
    ORDER BY index_method, opfamily_name, opfamily_operator;

每個索引欄位只能支援一個排序規則（Collation）。 如果感興趣多個排序規則，則可能需要多個索引。

看看以下語法：

索引自動使用基礎欄位的排序規則。所以這樣的查詢形式

會使用這個索引，因為預設情況下比較將使用欄位的排序規則。但是，此索引無法加速涉及其他一些排序規則的查詢。所以，如果是像這樣的查詢，比方說，

也是有意義的，可以建立一個支援「y」排序規則的附加索引，如下所示：

雖然 PostgreSQL 中的索引不需要維護或調教，但檢查查詢工作負載實際使用哪些索引仍然很重要。以 EXPLAIN 指令檢查單個查詢的索引使用情況；第 14.1 節說明了其應用。如第 27.2 節所述，還可以在正在執行的伺服器中收集有關索引使用情況的整體統計訊息。

決定要建立哪些索引的一般過程是很難確定的。在前面幾節的範例中已經顯示了許多典型案例。通常需要進行大量的實驗。本節的剩餘部分提供了一些提示：

• 始終先執行 ANALYZE。此指令收集有關資料表中內容的分佈的統計訊息。需要此訊息來估計查詢回傳的資料列數量，計劃程序需要為每個可能的查詢計劃分配實際成本。在沒有任何實際統計資料的情況下，會假設某些預設值，但這幾乎肯定是不準確的。因此，在沒有執行 ANALYZE 的情況下檢查應用程序的索引使用情況是失敗的。有關更多訊息，請參閱第 24.1.3 節和第 24.1.6 節。

• 使用真實資料進行實驗。使用測試資料設定索引將告訴您測試資料需要哪些索引，但這就是全部。使用非常小的測試資料集尤其致命。雖然選擇 100000 筆資料中的 1000 個可能是索引的候選者，但選擇 100 筆資料中的 1 個就幾乎不會，因為 100 筆資料可能適合單個磁碟頁面，並且沒有計劃可以比循序讀取 1 個磁碟頁面更好。在編輯測試資料時要小心，這在應用尚未投入産品階段時通常是不可避免的。非常相似，完全隨機或按排序順序插入的值會使統計資料偏離實際資料所具有的分佈。

• 當不使用索引時，它可以用於測試以強制使用它們。有些執行時的參數可以關閉各種計劃類型（參閱第 19.7.1 節）。例如，關閉循序掃描（enable_seqscan）和巢狀循環掃描（enable_nestloop）這些是最基本的計劃，將迫使系統使用不同的計劃。如果系統仍然選擇循序掃描或巢狀循環掃描，則可能存在更為根本的原因，即不使用索引；例如，查詢條件與索引無法搭配。（什麼樣的查詢可以使用前面幾節中解釋的索引類型。）

• 如果強制索引使用也確實使用索引，那麼有兩種可能性：系統是正確的並且使用索引確實不合適，或者查詢計劃的成本估算未反映現實。因此，您應該使用和不使用索引來查詢查詢。EXPLAIN ANALYZE 指令在這裡很有用。

• 如果事實證明成本估算是錯誤的，那麼又有兩種可能性。 總成本是根據每個計劃節點的每行成本乘以計劃節點的可能性估計來計算的。可以透過執行時參數調整計劃節點的估計成本（在第 19.7.2 節中描述）。可能性估計不準確是由於統計資訊不足。可以透過調整統計訊息收集參數來改進這一點（參閱 ALTER TABLE）。

如果您對查詢成本沒有更好的調整方案了，那麼您可能不得不明確強制使用索引。也許你還需要聯繫 PostgreSQL 開發人員來協同檢查問題。