這裡增加說明一個查詢的 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 章。

單個索引掃描只能使用將索引的欄位與其運算子類的運算子一起使用的查詢子句，並以 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 上的索引慢。最後一種方法是建立所有三個索引，但如果資料的搜尋頻率比更新頻率更高，並且所有三種類型的查詢都很常見，則這可能是合理的。如果其中一種類型的查詢比其他類型的查詢少得多，那麼您可能只想建立最符合常見類型的兩個索引。

索引欄位不必只是基礎資料表的一個欄位，而是可以是從資料表的一個欄位或多個欄位計算的函數或 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'，因此搜尋速度等同於任何其他簡單索引查詢。因此，當檢索速度比插入和更新速度更重要時，表示式上的索引就很有用。

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

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

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

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

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

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

PostgreSQL 中的所有索引都是二級索引，這意味著每個索引都與資料的主資料區域（在 PostgreSQL 術語中稱為資料表的 heap）分開儲存。這意味著在普通索引掃描中，每個資料列檢索都需要從索引和堆中獲取資料。此外，雖然與給予可索引 WHERE 條件匹配的索引項目通常在索引中靠近在一起，但它們引用的資料列可能在 heap 中的任何位置。因此，索引掃描 heap 部分涉及大量隨機存取，這可能很慢，特別是在傳統的磁碟媒體上。（如第 11.5 節所述，bitmap 掃描嘗試透過按排序循序執行 heap 存取來減輕此成本，但這只是到目前為止而已。）

為了解決這個效能問題，PostgreSQL 支援索引限定掃描，它可以單獨回答索引中的查詢而毋須任何 heap 存取。基本思維是直接從每個索引項目中回傳值，而不是查詢相關的 heap 項目。何時可以使用此方法有兩個基本限制：

1. 索引類型必須支援僅索引掃描。B-tree 索引能做到，GiST 和 SP-GiST 索引支援某些運算子類的僅索引掃描，但不支持其他運算子類。其他索引類型則沒有支援。 基本要求是索引必須物理儲存或能夠重建每個索引項目的原始資料值。作為一個反例，GIN 索引不支援索引限定掃描，因為每個索引項目通常只包含原始資料值的一部分。

2. 查詢必須僅引用儲存在索引中的欄位。例如，給予一個也有欄位 z 的資料表欄位 x 和 y 的索引，這些查詢可以使用索引限定掃描：

   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;

   （表示式索引和部分索引使此規則複雜化，如下所述。）

只要滿足這兩個基本要求，那麼查詢所需的所有資料值都可以從索引中獲得，因此就能只進行索引掃描。但是對 PostgreSQL 中的任何資料表掃描還有一個額外的要求：它必須驗證每個檢索到的資料列對查詢的 MVCC 快照是「可見的」，如第 13 章所述。可見性訊息不儲存在索引項目中，僅儲存在 heap 項目中；所以乍看之下似乎每個資料列檢索都需要存取 heap。如果資料列最近被修改，情況確實如此。但是，對於很少變化的資料，可以解決這個問題。對於資料表 heap 中的每個頁面，PostgreSQL 追踪儲存在該頁面中的所有資料表是否足夠大以使所有目前和未來的事務都可見。此訊息儲存在資料表的可見性映射表中。在找到候選索引項目之後，索引限定掃描會檢查相應 heap 頁面的可見性映射表。如果已設定，則該資料列已知可見，因此可以回傳資料而毋須進一步的作業。如果未設定，則必須存取 heap 項目以查明它是否可見，因此與標準索引掃描相比沒有效能優勢。即使在成功的情況下，這種方法也會對 heap 存取進行可見性映射表存取；但由於可見性圖比它描述的 heap 小四個數量級，因此存取它所需的物理 I/O 要少得多。在大多數情況下，可見性映射表始終在暫存在記憶體中。

簡而言之，雖然在給予兩個基本要求的情況下可以進行索引限定掃描，但只有當資料表的 heap 頁面的很大一部分設定了全部可見的映射位元時，才會獲勝。但是大部分資料列不變的資料通常足以使這種類型的掃描成立，這在實作中非常有用。

要有效使用索引限定掃描功能，您可以選擇建立僅在前導欄位中匹配 WHERE 子句的索引，而尾隨列包含要由查詢回傳的“payload”資料。例如，如果你經常執行像這樣的查詢

SELECT y FROM tab WHERE x = 'key';

加速此類查詢的傳統方法是僅在 x 上建立索引。但是，(x, y) 上的索引將提供將此查詢實作為索引限定掃描的可能性。 如前所述，這樣的索引會更大，因此比單獨使用 x 的索引更昂貴，所以只有在知道該資表大部分是靜態的情況下才有吸引力。 請注意，在 (x, y) 而不是 (y, x) 上宣告索引很重要，因為大多數索引類型（特別是B-tree）不限制前導索引欄位的搜尋效率不高。

原則上，索引限定掃描可以與表示式索引一起使用。例如，給予 f(x) 上的索引，其中 x 是資料表欄位，應該可以執行

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

作為索引限定掃描；如果 f() 是一個昂貴的計算函數，這是非常有吸引力的。但是，PostgreSQL 的規劃程序目前對這種情況並不十分聰明。只有當查詢所需的所有欄位都可以從索引獲得時，它才會將查詢視為可能透過索引限定進行掃描。在此範例中，除了裡面的 f(x) 之外，不需要 x，但是規劃程序沒有注意到這一點，並得出結論進行索引限定掃描。如果索引限定掃描似乎足夠值得，可以通過宣告索引打開 (f(x), x) 來解決這個問題，其中第二欄位預計不會在實作中使用，而只是說服了規劃程序可以進行索引限定掃描。如果目標是避免重新計算 f(x)，則另一個警告是規劃程序不一定要匹配不在索引欄位的可索引 WHERE 子句中的 f(x) 使用。它通常會在如上所示的簡單查詢中得到正確的結果，但在涉及 JOIN 的查詢中則不會。這些缺陷也許會在 PostgreSQL 的未來版本中得到改善。

部分索引還與索引限定掃描具有有趣的交互運作。考慮範例 11.3 中顯示的部分索引：

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

原則上，我們可以對此索引執行索引限定掃描就能滿足查詢

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

但是存在一個問題：WHERE 子句參考的 success，它不能作為索引的結果欄位。 儘管如此，仍能進行索引限定掃描，因為計劃不需要在執行時重新檢查 WHERE 子句的那一部分：索引中找到的所有項目必須具有 success = true，因此毋須在計劃中明確檢查。 PostgreSQL 版本 9.6 及更高版本能識別此類情況並允許産生索引限定掃描，但舊版本不會。

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

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）將被阻止，直到索引建構完成。在産品環境中，這通常是不可接受的。允許寫入與索引建立同時發生是可能的，但有幾點值得注意 - 有關更多訊息，請參閱同步建立索引。

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

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

看看以下語法：

CREATE TABLE test1c (
    id integer,
    content varchar COLLATE "x"
);

CREATE INDEX test1c_content_index ON test1c (content);

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

SELECT * FROM test1c WHERE content > constant;

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

SELECT * FROM test1c WHERE content > constant COLLATE "y";

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

CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y");

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

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;

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

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.11 節。

雖然 PostgreSQL 中的索引不需要維護或調教，但檢查查詢工作負載實際使用哪些索引仍然很重要。以 EXPLAIN 指令檢查單個查詢的索引使用情況；第 14.1 節說明了其應用。如第 28.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 開發人員來檢查問題。

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

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

範例 11.1. 設定部分索引以排除常見值

假設您將 Web 伺服器存取日誌儲存在資料庫中。大多數存取源自組織的 IP 位址範圍，但有些來自其他地方（例如，撥號連接上的員工）。如果按 IP 搜尋主要用於外部存取，則可能不需要索引與組織的子網對應的 IP 範圍。

假設一個像這樣的資料表：

要建立適合我們範例的部分索引，請使用如下的指令：

可以使用此索引的典型查詢是：

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

觀察到這種部分索引要求預定常見值，因此這種部分索引最好用於靜態的資料分佈。也可以偶爾重新建立索引以調整新的資料分佈，但這會增加維護的工作量。

範例 11.2. 設定部分索引以排除不感興趣的值

如果您的資料表包含已開票和未開票的訂單，其中未開票的訂單佔據資料表的一小部分，但這些是最常存取的資料列，您可以透過僅在未開票的資料上建立索引來提高效能。建立索引的指令如下所示：

使用此索引的可能查詢是：

但是，索引也可以用於完全不涉及 order_nr 的查詢中，例如：

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

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

訂單 3501 可能屬於開票或未開票的訂單。

範例 11.3. 設定部分唯一索引

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

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

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

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

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