112
技術社區[雲棲]
PostgreSQL SQL 語言：索引
本文檔為PostgreSQL 9.6.0文檔，本轉載已得到原譯者彭煜瑋授權。
假設我們有一個如下的表：
CREATE TABLE test1 (
id integer,
content varchar
);
而應用發出很多以下形式的查詢：
SELECT content FROM test1 WHERE id = constant;
在沒有事前準備的情況下，係統不得不掃描整個test1表，一行一行地去找到所有匹配的項。如果test1中有很多行但是隻有一小部分行（可能是0或者1）需要被該查詢返回，這顯然是一種低效的方式。但是如果係統被指示維護一個在id列上的索引，它就能使用一種更有效的方式來定位匹配行。例如，它可能僅僅需要遍曆一棵搜索樹的幾層而已。
類似的方法也被用於大部分非小說書籍中：經常被讀者查找的術語和概念被收集在一個字母序索引中放在書籍的末尾。感興趣的讀者可以相對快地掃描索引並跳到合適的頁而不需要閱讀整本書來尋找感興趣的材料。正如作者的任務是準備好讀者可能會查找的術語一樣，數據庫程序員也需要預見哪些索引會有用。
正如前麵討論的，下列命令可以用來在id列上創建一個索引：
CREATE INDEX test1_id_index ON test1 (id);
索引的名字test1_id_index可以自由選擇，但我們最好選擇一個能讓我們想起該索引用途的名字。
為了移除一個索引，可以使用DROP INDEX命令。索引可以隨時被創建或刪除。
一旦一個索引被創建，就不再需要進一步的幹預：係統會在表更新時更新索引，而且會在它覺得使用索引比順序掃描表效率更高時使用索引。但我們可能需要定期地運行ANALYZE命令來更新統計信息以便查詢規劃器能做出正確的決定。通過Chapter 14的信息可以了解如何找出一個索引是否被使用以及規劃器在何時以及為什麼會選擇不使用索引。
索引也會使帶有搜索條件的UPDATE和DELETE命令受益。此外索引還可以在連接搜索中使用。因此，一個定義在連接條件列上的索引可以顯著地提高連接查詢的速度。
在一個大表上創建一個索引會耗費很長的時間。默認情況下，PostgreSQL允許在索引創建時並行地進行讀（SELECT命令），但寫（INSERT、UPDATE和DELETE）則會被阻塞直到索引創建完成。在生產環境中這通常是不可接受的。在創建索引時允許並行的寫是可能的，但是有些警告需要注意，更多信息可以參考並發構建索引。
一個索引被創建後，係統必須保持它與表同步。這增加了數據操作的負擔。因此哪些很少或從不在查詢中使用的索引應該被移除。
PostgreSQL提供了多種索引類型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一種索引類型使用了 一種不同的算法來適應不同類型的查詢。默認情況下， CREATE INDEX命令創建適合於大部分情況的B-tree 索引。
B-tree可以在可排序數據上的處理等值和範圍查詢。特別地，PostgreSQL的查詢規劃器會在任何一種涉及到以下操作符的已索引列上考慮使用B-tree索引：
<
<=
=
>=
>
將這些操作符組合起來，例如BETWEEN和IN，也可以用B-tree索引搜索實現。同樣，在索引列上的IS NULL或IS NOT NULL條件也可以在B-tree索引中使用。
優化器也會將B-tree索引用於涉及到模式匹配操作符LIKE和~ 的查詢，前提是如果模式是一個常量且被固定在字符串的開頭—例如：col LIKE 'foo%'或者col ~ '^foo', 但在col LIKE '%bar'上則不會。但是，如果我們的數據庫沒有使用C區域設置，我們需要創建一個具有特殊操作符類的索引來支持模式匹配查詢，參見下麵的Section 11.9。同樣可以將B-tree索引用於ILIKE和~*，但僅當模式以非字母字符開始，即不受大小寫轉換影響的字符。
B-tree索引也可以用於檢索排序數據。這並不會總是比簡單掃描和排序更快，但是總是有用的。
Hash索引隻能處理簡單等值比較。不論何時當一個索引列涉及到一個使用了=操作符的比較時，查詢規劃器將考慮使用一個Hash索引。下麵的命令將創建一個Hash索引：
CREATE INDEX name ON table USING HASH (column);
Caution
Hash索引操作目前不被WAL記錄，因此存在未寫入修改，在數據庫崩潰後需要用REINDEX命令重建Hash索引。同樣，在完成初始的基礎備份後，對於Hash索引的改變也不會通過流式或基於文件的複製所複製，所以它們會對其後使用它們的查詢給出錯誤的答案。正因為這些原因，Hash索引已不再被建議使用。
GiST索引並不是一種單獨的索引，而是可以用於實現很多不同索引策略的基礎設施。相應地，可以使用一個GiST索引的特定操作符根據索引策略（操作符類）而變化。作為一個例子，PostgreSQL的標準捐獻包中包括了用於多種二維幾何數據類型的GiST操作符類，它用來支持使用下列操作符的索引化查詢：
<<
&<
&>
>>
<<|
&<|
|&>
|>>
@>
<@
~=
&&
Table 61-1中給出了標準發布中所包括的 GiST 操作符類。contrib集合中還包括了很多其他GiST操作符類。
GiST索引也有能力優化"最近鄰"搜索，例如：
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
它將找到離給定目標點最近的10個位置。能夠支持這種查詢的能力同樣取決於被使用的特定操作符類。 在Table 61-1中，"Ordering Operators"列中列出了可以在這種方法中使用的操作符。
和GiST相似，SP-GiST索引為支持多種搜索提供了一種基礎結構。SP-GiST 允許實現眾多不同的非平衡的基於磁盤的數據結構，例如四叉樹、k-d樹和radix樹。作為一個例 子，PostgreSQL的標準捐獻包中包含了一個用於二維點的SP-GiST操作符類，它用於支持使用下列操作符的索引化查詢：
<<
>>
~=
<@
<^
>^
Table 62-1中給出了標準發布中所包括的 SP-GiST 操作符類。
GIN 索引是"倒排索引"，它適合於包含多個組成值的數據值，例如數組。倒排索引中為每一個組成值都包含一個單獨的項，它可以高效地處理測試指定組成值是否存在的查詢。
與 GiST 和 SP-GiST相似， GIN可以支持多種不同的用戶定義的索引策略和特定操作符，通過它一個GIN索引可以被根據索引策略被使用。作為一個例子，PostgreSQL的標準捐獻包中包含了用於一維數組的GIN操作符類，它用於支持使用下列操作符的索引化查詢：
<@
@>
=
&&
Table 63-1中給出了標準發布中所包括的 GIN 操作符類。在contrib集合中還有更多其他GIN操作符類。
BRIN 索引（塊範圍索引的縮寫）存儲有關存放在一個表的連續物理塊範圍上的值摘要信息。與 GiST、SP-GiST 和 GIN 相似，BRIN可以支持很多種不同的索引策略，並且可以與一個 BRIN 索引配合使用的特定操作符取決於索引策略。對於具有線性排序順序的數據類型，被索引的數據對應於每個塊範圍的列中值的最小值和最大值，使用這些操作符來支持用到索引的查詢：
<
<=
=
>=
>
一個索引可以定義在表的多個列上。例如，我們有這樣一個表：
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個列（該限製可以在源代碼文件pg_config_manual.h中修改，但是修改後需要重新編譯PostgreSQL）。
一個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不同，GIN的搜索效率與查詢條件中使用哪些索引列無關。
多列 BRIN 索引可以被用於涉及該索引被索引列的任意子集的查詢條件。和 GIN 相似且不同於 B-樹 或者 GiST，索引搜索效率與查詢條件使用哪個索引列無關。在單個表上使用多個 BRIN 索引來取代一個多列 BRIN 索引的唯一原因是為了使用不同的pages_per_range存儲參數。
當然，要使索引其作用，查詢條件中的列必須要使用適合於索引類型的操作符，使用其他操作符的子句將不會被考慮使用索引。
多列索引應該較少地使用。在絕大多數情況下，單列索引就足夠了且能解決時間和空間。具有超過三個列的索引不太有用，除非該表的使用是極端程式化的。
除了簡單地查找查詢要返回的行外，一個索引可能還需要將它們以指定的順序傳遞。這使得查詢中的ORDER BY不需要獨立的排序步驟。在PostgreSQL當前支持的索引類型中，隻有B-tree可以產生排序後的輸出，其他索引類型會把行以一種沒有指定的且與實現相關的順序返回。
規劃器會考慮以兩種方式來滿足一個ORDER BY說明：掃描一個符合說明的可用索引，或者先以物理順序掃描表然後再顯式排序。對於一個需要掃描表的大部分的查詢，一個顯式的排序很可能比使用一個索引更快，因為其順序訪問模式使得它所需要的磁盤I/O更少。隻有在少數行需要被取出時，索引才會更有用。一種重要的特殊情況是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的默認情況）。
我們可以在創建B-tree索引時通過ASC、DESC、NULLS FIRST和NULLS LAST選項來改變索引的排序，例如：
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)就可以產生這種排序。
顯然，具有非默認排序的索引是相當專門的特性，但是有時它們會為特定查詢提供巨大的速度提升。是否值得維護這樣一個索引取決於我們會多頻繁地使用需要特殊排序的查詢。
隻有查詢子句中在索引列上使用了索引操作符類中的操作符並且通過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這樣的查詢的一種可能的實現方式就是分別使用兩個索引配合相應的條件，然後將結果“與”起來得到最後的結果行。
為了組合多個索引，係統掃描每一個所需的索引並在內存中準備一個位圖用於指示表中符合索引條件的行的位置。然後這些位圖會被根據查詢的需要“與”和“或”起來。最後，實際的表行將被訪問並返回。表行將被以物理順序訪問，因為位圖就是以這種順序布局的。這意味著原始索引中的任何排序都會被丟失，並且如果存在一個ORDER BY子句就需要一個單獨的排序步驟。由於這個原因以及每一個附加的索引都需要額外的時間，即使有額外的索引可用，規劃器有時也會選擇使用單一索引掃描。
在所有的應用（除了最簡單的應用）中，可能會有多種有用的索引組合，數據庫開發人員必須做出權衡以決定提供哪些索引。有時候多列索引最好，但是有時更好的選擇是創建單獨的索引並依賴於索引組合特性。例如，如果我們的查詢中有時隻涉及到列x，有時候隻涉及到列Y，還有時候會同時涉及到兩列，我們可以選擇在x和y上創建兩個獨立索引然後依賴索引組合來處理同時涉及到兩列的查詢。我們當然也可以創建一個(x, y)上的多列索引。當查詢同時涉及到兩列時，該索引會比組合索引效率更高，但是正如Section 11.3中討論的，它在隻涉及到y的查詢中幾乎完全無用，因此它不能是唯一的一個索引。一個多列索引和一個y上的獨立索引的組合將會工作得很好。多列索引可以用於那些隻涉及到x的查詢，盡管它比x上的獨立索引更大且更慢。最後一種選擇是創建所有三個索引，但是這種選擇最適合表經常被執行所有三種查詢但是很少被更新的情況。如果其中一種查詢要明顯少於其他類型的查詢，我們可能需要隻為常見類型的查詢創建兩個索引。
索引也可以被用來強製列值的唯一性，或者是多個列組合值的唯一性。
CREATE UNIQUE INDEX name ON table (column [, ...]);
當前，隻有B-tree能夠被聲明為唯一。
當一個索引被聲明為唯一時，索引中不允許多個表行具有相同的索引值。空值被視為不相同。一個多列唯一索引將會拒絕在所有索引列上具有相同組合值的表行。
PostgreSQL會自動為定義了一個唯一約束或主鍵的表創建一個唯一索引。該索引包含組成主鍵或唯一約束的所有列（可能是一個多列索引），它也是用於強製這些約束的機製。
Note:
不需要手工在唯一列上創建索引，如果那樣做也隻是重複了自動創建的索引而已。
一個索引列並不一定是底層表的一個列，也可以是從表的一列或多列計算而來的一個函數或者標量表達式。這種特性對於根據計算結果快速獲取表中內容是有用的。
例如，一種進行大小寫不敏感比較的常用方法是使用lower函數：
SELECT * FROM test1 WHERE lower(col1) = 'value';
這種查詢可以利用一個建立在lower(col1)函數結果之上的索引：
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
如果我們將該索引聲明為UNIQUE，它將阻止創建在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'，因此查詢的速度將等同於其他簡單索引查詢。因此，表達式索引對於檢索速度遠比插入和更新速度重要的情況非常有用。
一個部分索引是建立在表的一個子集上，而該子集則由一個條件表達式（被稱為部分索引的謂詞）定義。而索引中隻包含那些符合該謂詞的表行的項。部分索引是一種專門的特性，但在很多種情況下它們也很有用。
使用部分索引的一個主要原因是避免索引公值。由於搜索一個公值的查詢（一個在所有表行中占比查過一定百分比的值）不會使用索引，所以完全沒有理由將這些行保留在索引中。這可以減小索引的尺寸，同時也將加速使用索引的查詢。它也將加速很多表更新操作，因為這種索引並不需要在所有情況下都被更新。Example 11-1展示了一種可能的應用：
Example 11-1. 建立一個部分索引來排除公值
假設我們要在一個數據庫中保存網頁服務器訪問日誌。大部分訪問都來自於我們組織內的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';
可以看到部分索引查詢要求公值能被預知，因此部分索引最適合於數據分布不會改變的情況。當然索引也可以偶爾被重建來適應新的數據分布，但是這會增加維護負擔。
Example 11-2展示了部分索引的另一個可能的用途：從索引中排除那些查詢不感興趣的值。這導致了上述相同的好處，但它防止了通過索引來訪問"不感興趣的"值，即便在這種情況下一個索引掃描是有益的。顯然，為這種場景建立部分索引需要很多考慮和實驗。
Example 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可能在已上賬訂單或未上賬訂單中。
Example 11-2也顯示索引列和謂詞中使用的列並不需要匹配。PostgreSQL支持使用任意謂詞的部分索引，隻要其中涉及的隻有被索引表的列。然而，記住謂詞必須匹配在將要受益於索引的查詢中使用的條件。更準確地，隻有當係統能識別查詢的WHERE條件從數學上索引的謂詞時，一個部分索引才能被用於一個查詢。PostgreSQL並不能給出一個精致的定理證明器來識別寫成不同形式在數學上等價的表達式（一方麵創建這種證明器極端困難，另一方麵即便能創建出來對於實用也過慢）。係統可以識別簡單的不等蘊含，例如"x < 1"蘊含"x < 2"；否則謂詞條件必須準確匹配查詢的WHERE條件中的部分，或者索引將不會被識別為可用。匹配發生在查詢規劃期間而不是運行期間。因此，參數化查詢子句無法配合一個部分索引工作。例如，對於參數的所有可能值來說，一個具有參數"x < ?"的預備查詢絕不會蘊含"x < 2"。
部分索引的第三種可能的用途並不要求索引被用於查詢。其思想是在一個表的子集上創建一個唯一索引，如Example 11-3所示。這對那些滿足索引謂詞的行強製了唯一性，而對那些不滿足的行則沒有影響。
Example 11-3. 建立一個部分唯一索引
假設我們有一個描述測試結果的表。我們希望保證其中對於一個給定的主題和目標組合隻有一個"成功"項，但其中可能會有任意多個"不成功"項。實現它的方式是：
CREATE TABLE tests (
subject text,
target text,
success boolean,
...
);
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
WHERE success;
當有少數成功測試和很多不成功測試時這是一種特別有效的方法。
最後，一個部分索引也可以被用來重載係統的查詢規劃選擇。同樣，具有特殊分布的數據集可能導致係統在它並不需要索引的時候選擇使用索引。在此種情況下可以被建立，這樣它將不會被那些無關的查詢所用。通常，PostgreSQL會對索引使用做出合理的選擇（例如，它會在檢索公值時避開索引，這樣前麵的例子隻能節約索引尺寸，它並非是避免索引使用所必需的），非常不正確的規劃選擇則需要作為故障報告。
記住建立一個部分索引意味著我們知道的至少和查詢規劃器所知的一樣多，尤其是我們知道什麼時候一個索引會是有益的。構建這些知識需要經驗和對於PostgreSQL中索引工作方式的理解。在大部分情況下，一個部分索引相對於一個普通索引的優勢很小。
一個索引定義可以為索引中的每一列都指定一個操作符類。
CREATE INDEX name ON table (column opclass [sort options] [, ...]);
操作符類標識該列上索引要使用的操作符。例如，一個int4類型上的B樹索引會使用int4_ops類，這個操作符類包括用於int4類型值的比較函數。實際上列的數據類型的默認操作符類通常就足夠了。存在多個操作符類的原因是，對於某些數據類型可能會有多於一種的有意義的索引行為。例如，我們可能想要對一種複數數據類型按照絕對值排序或者按照實數部分排序。我們可以通過為該數據類型定義兩個操作符類來實現，並且在創建一個索引時選擇合適的類。操作符類會決定基本的排序順序（可以通過增加排序選項COLLATE、 ASC/DESC和/或 NULLS FIRST/NULLS LAST來修改）。
除了默認的操作符類，還有一些內建的操作符類：
操作符類text_pattern_ops、varchar_pattern_ops和 bpchar_pattern_ops分別支持類型text、varchar和 char上的B樹索引。它們與默認操作符類的區別是值的比較是嚴格按照字符進行而不是根據區域相關的排序規則。這使得這些操作符類適合於當一個數據庫沒有使用標準"C"區域時被使用在涉及模式匹配表達式（LIKE或POSIX正則表達式）的查詢中。一個例子是，你可以這樣索引一個varchar列：
CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
注意如果你希望涉及到<、<=、>或>=比較的查詢使用一個索引，你也應該創建一個使用默認操作符類的索引。這些查詢不能使用xxx_pattern_ops操作符類（但是普通的等值比較可以使用這些操作符類）。可以在同一個列上創建多個使用不同操作符類的索引。如果你正在使用C區域，你並不需要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 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");
PostgreSQL中的所有索引都是二級索引，表示每一個索引都被存儲在表的主數據區域（在PostgreSQL術語中被稱為該表的堆）之外。這意味著在一次普通索引掃描中，每次取一行需要從索引和堆中取得數據。此外，雖然滿足一個給定的可索引WHERE條件的索引項通常在索引中都靠攏在一起，但是它們所引用的表行可能分布在堆中的任何地方。因此一次索引掃描的堆訪問部分可能會涉及到很多對堆的隨機訪問，這可能會很慢，尤其在傳統的磁盤上。
為了解決這種性能問題，PostgreSQL支持隻用索引的掃描，這類掃描可以僅用一個索引來回答查詢而不產生任何堆訪問。其基本思想是直接從每一個索引項中直接返回值，而不是去參考相關的堆項。在使用這種方法時有兩個根本的限製：
索引類型必須支持隻用索引的掃描。B-樹索引總是支持隻用索引的掃描。GiST 和 SP-GiST 索引隻對某些操作符類支持隻用索引的掃描。其他索引類型不支持這種掃描。底層的要求是索引必須在物理上存儲或者可以重構出每一個索引項對應的原始數據值。GIN 索引是一個不支持隻用索引的掃描的反例，因為它的每一個索引項通常隻包含原始數據值的一部分。
查詢必須隻引用存儲在該索引中的列。例如，給定的索引建立在表的列x和y上，而該表還有一個列z，這些查詢可以使用隻用索引的掃描：
SELECT x, y FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND y < 42;
但是這些查詢不能使用隻用索引的查詢：
SELECT x, z FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND z < 42;
（如下麵所討論的，表達式索引和部分索引會讓這條規則更加複雜）。
如果符合這兩個根本要求，那麼該查詢所要求的所有數據值都可以從索引得到，因此才可能使用隻用索引的掃描。但是對PostgreSQL中的任何表掃描還有一個額外的要求：必須驗證每一個檢索到的行對該查詢的 MVCC 快照是"可見的"，如Chapter 13中討論的那樣。可見性信息並不存儲在索引項中，隻存儲在堆項中。因此，乍一看似乎每一次行檢索無論如何都會要求一次堆訪問。如果表行最近被修改過，確實是這樣。但是，對於很少更改的數據有一種方法可以解決這個問題。PostgreSQL為表堆中的每一個頁麵跟蹤是否其中所有的行的年齡都足夠大，以至於對所有當前以及未來的事務都可見。這個信息存儲在該表的可見性映射的一個位中。在找到一個候選索引項後，隻用索引的掃描會檢查對應堆頁麵的可見性映射位。如果該位被設置，那麼這一行就是可見的並且該數據庫可以直接被返回。如果該位沒有被設置，那麼就必須訪問堆項以確定這一行是否可見，這種情況下相對於標準索引掃描就沒有性能優勢。即便是在成功的情況下，這種方法也是把對堆的訪問換成了對可見性映射的訪問。不過由於可見性映射比它所描述的堆要小四個數量級，所以訪問可見性映射所需的物理 I/O 要少很多。在大部分情況下，可見性映射總是會被保留在內存中的緩衝中。
總之，雖然當兩個根本要求滿足時可以使用隻用索引的掃描，但是隻有該表的堆頁麵中有很大一部分的“所有都可見”映射位被設置時這種索引才有優勢。不過，有很大一部分行不被更改的表是很常見的，這也讓這一類掃描在實際中非常有用。
為了有效地使用隻用索引的掃描，可以選擇創建時隻讓前導列來匹配WHERE子句，而讓後麵的列保存"載荷"數據用以返回查詢所需的數據。例如，如果經常要運行這樣的查詢
SELECT y FROM tab WHERE x = 'key';
加速這類查詢的傳統方法是隻在x上創建一個索引。但是，在(x, y)上創建一個索引可以將這種查詢實現為隻用索引的掃描。正如之前討論的，這樣一個索引比隻在x上的索引更大並且因此開銷更大，因此隻有知道表幾乎是靜態時這種方法才具有吸引力。注意，很重要的一點是，該索引應該定義在(x, y)而不是(y, x)上。這是因為對於大部分索引類型（特別是 B-樹）來說，不限製前導索引列的搜索效率不高。
原則上，隻用索引的掃描可以被用於表達式索引。例如，給定一個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)的使用匹配到索引列。通常在上述那種簡單查詢中一切正常，但是涉及到連接的查詢中就不行了。這些不足將在未來的PostgreSQL版本中修正。
部分索引也和隻用索引的掃描之間有著有趣的關係。考慮Example 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 和以後的版本將會識別這種情況，並且允許生成隻用索引的掃描，但是舊版本無法這樣做。
Although indexes in 盡管PostgreSQL中的索引並不需要維護或調優，但是檢查真實的查詢負載實際使用了哪些索引仍然非常重要。檢查一個獨立查詢的索引使用情況可以使用EXPLAIN命令，它應用於這種目的的內容在Section 14.1中有介紹。也可以在一個運行中的服務器上收集有關索引使用的總體統計情況，如Section 28.2所述。
很難明確地表達決定創建哪些索引的通用過程。在之前的小節中的例子裏有一些典型的情況。通常需要大量的實驗才能決定應該創建哪些索引。本小節剩餘的部分將給出一些創建索引的提示：
- 總是先運行ANALYZE。這個命令會收集有關表中值分布情況的統計信息。估計一個查詢將要返回的行數需要這些信息，而結果行數則被規劃器用來為每一個可能的查詢計劃分配實際的代價。如果沒有任何真實的統計信息，將會假定一些默認值，這幾乎肯定是不準確的。在沒有運行的情況下檢查一個應用的索引使用情況是注定要失敗的。
- 使用真實數據進行實驗。使用測試數據來建立索引將會告訴你測試數據需要什麼樣的索引，但這並不代表真實數據的需要。
- 使用非常小的測試數據集是特別致命的。在從100000行中選出1000行時可能會用到索引，但是從100行裏選出1行是很難用到索引的，因為100行完全可能放入到一個磁盤頁麵中，而沒有任何計劃能夠比得上從一個磁盤頁麵順序獲取的計劃。
- 在創建測試數據時也要小心，特別是當應用還沒有產生時通常是不可避免的。值非常相似、完全隨機或以排好序的方式被插入都將是的統計信息傾斜於真實數據中的值分布。
- 如果索引沒有被用到，強製使用它們將會對測試非常有用。有一些運行時參數可以關閉多種計劃類型。例如，關閉順序掃描（enable_seqscan）以及嵌套循環連接（enable_nestloop）將強製係統使用一種不同的計劃。如果係統仍然選擇使用一個順序掃描或嵌套循環連接，則索引沒有被使用的原因可能更加根本，例如查詢條件不匹配索引（哪種查詢能夠使用哪種索引已經在前麵的小節中解釋過了）。
- 如果強製索引使用確實使用了索引，則有兩種可能性：係統是正確的並且索引確實不合適，或者查詢計劃的代價估計並沒有反映真實情況。因此你應該對用索引的查詢和不用索引的查詢計時。此時EXPLAIN ANALYZE命令就能發揮作用了。
- 如果發現代價估計是錯誤的，也分為兩種可能性。總代價是用每個計劃節點的每行代價乘以計劃節點的選擇度估計來計算的。計劃節點的代價估計可以通過運行時參數調整（如Section 19.7.2所述）。不準確的選擇度估計可能是由於缺乏統計信息，可以通過調節統計信息收集參數（見ALTER TABLE）來改進。
如果你不能成功地把代價調整得更合適，那麼你可能必須依靠顯式地強製索引使用。你也可能希望聯係PostgreSQL開發者來檢查該問題。
最後更新：2017-08-18 14:02:27