描述

CREATE INDEX 在指定關聯的指定欄位上建立索引，該關聯可以是表格或實體化檢視表。索引主要用於提升資料庫效能（儘管不當使用可能會導致效能降低）。

索引的索引鍵欄位指定為欄位名稱，或者指定為括號中撰寫的運算式。如果索引方法支援多欄位索引，則可以指定多個欄位。

索引欄位可以是從表格列的一或多個欄位值計算而來的運算式。此功能可用於根據基本資料的某種轉換快速存取資料。例如，在 upper(col) 上計算的索引將允許子句 WHERE upper(col) = 'JIM' 使用索引。

PostgreSQL 提供 B-tree、hash、GiST、SP-GiST、GIN 和 BRIN 等索引方法。使用者也可以定義自己的索引方法，但這相當複雜。

當存在 WHERE 子句時，會建立部分索引。部分索引是一種僅包含表格一部分項目的索引，通常是比表格其餘部分更適合建立索引的部分。例如，如果您有一個包含已計費和未計費訂單的表格，其中未計費訂單僅佔總表格的一小部分，但卻是經常使用的部分，則可以透過僅在該部分上建立索引來提高效能。另一個可能的應用是將 WHERE 與 UNIQUE 結合使用，以強制表格子集的唯一性。請參閱第 11.8 節以取得更多討論。

WHERE 子句中使用的運算式只能參考基礎表格的欄位，但它可以使用所有欄位，而不僅僅是被建立索引的欄位。目前，子查詢和聚合運算式在 WHERE 中也是禁止的。相同的限制適用於作為運算式的索引欄位。

索引定義中使用的所有函式和運算子都必須是「不可變的」，也就是說，它們的結果必須僅取決於它們的引數，而絕不能取決於任何外部影響（例如另一個表格的內容或目前時間）。此限制確保索引的行為良好定義。若要在索引運算式或 WHERE 子句中使用使用者定義的函式，請記住在建立函式時將其標記為不可變。

參數

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col    | integer |           |          |
Indexes:
    "idx" btree (col) INVALID

注意事項

請參閱第 11 章，以瞭解何時可以使用索引、何時不使用索引，以及在哪些特定情況下索引可能有用。

目前，只有 B-tree、GiST、GIN 和 BRIN 索引方法支援多鍵值欄位的索引。是否可以有多個鍵值欄位與是否可以將 INCLUDE 欄位新增到索引無關。索引最多可以有 32 個欄位，包括 INCLUDE 欄位。（此限制可以在建置 PostgreSQL 時變更。）目前只有 B-tree 支援唯一索引。

可以為索引的每個欄位指定具有可選參數的運算子類別。運算子類別識別索引要用於該欄位的運算子。例如，四位元整數的 B-tree 索引將使用 int4_ops 類別；此運算子類別包含四位元整數的比較函數。實際上，欄位資料類型的預設運算子類別通常就足夠了。使用運算子類別的主要原因是，對於某些資料類型，可能有多個有意義的排序。例如，我們可能希望按絕對值或實部對複數資料類型進行排序。我們可以透過為資料類型定義兩個運算子類別，然後在建立索引時選擇正確的類別來做到這一點。有關運算子類別的更多資訊，請參閱第 11.10 節和第 36.16 節。

當在分割資料表上呼叫 CREATE INDEX 時，預設行為是遞迴到所有分割區，以確保它們都具有相符的索引。首先檢查每個分割區，以確定是否已存在等效的索引，如果存在，則該索引將作為分割區索引附加到正在建立的索引，該索引將成為其父索引。如果不存在相符的索引，則將建立一個新的索引並自動附加；每個分割區中新索引的名稱將按照好像指令中未指定索引名稱的方式確定。如果指定了 ONLY 選項，則不會執行遞迴，並且索引將被標記為無效。（ALTER INDEX ... ATTACH PARTITION 會在所有分割區取得相符的索引後，將索引標記為有效。）但是請注意，將來使用 CREATE TABLE ... PARTITION OF 建立的任何分割區將自動具有相符的索引，無論是否指定了 ONLY。

對於支援排序掃描的索引方法（目前僅 B-tree），可以指定可選的子句 ASC、DESC、NULLS FIRST 和/或 NULLS LAST，以修改索引的排序順序。由於可以向前或向後掃描排序的索引，因此建立單欄位 DESC 索引通常沒有用 — 使用常規索引已經可以使用該排序順序。這些選項的價值在於，可以建立多欄位索引，以符合混合排序查詢所請求的排序順序，例如 SELECT ... ORDER BY x ASC, y DESC。NULLS 選項在需要支援 「空值排序低」 行為，而不是預設的 「空值排序高」，並且查詢依賴索引來避免排序步驟時非常有用。

系統定期收集資料表所有欄位的統計資訊。新建立的非運算式索引可以立即使用這些統計資訊來確定索引的效用。對於新的運算式索引，有必要執行 ANALYZE 或等待自動清理精靈分析資料表，以產生這些索引的統計資訊。

在 CREATE INDEX 執行時，search_path 暫時變更為 pg_catalog, pg_temp。

對於大多數索引方法，建立索引的速度取決於 maintenance_work_mem 的設定。較大的值將減少建立索引所需的時間，只要您不要使其大於實際可用的記憶體量，否則會將機器驅動到交換。

PostgreSQL 可以在利用多個 CPU 的同時建立索引，以便更快地處理資料表列。此功能稱為並行索引建立。對於支援並行建立索引的索引方法（目前僅 B-tree），maintenance_work_mem 指定每個索引建立操作可以使用的最大記憶體量，無論啟動了多少個工作程序。通常，成本模型會自動確定應該請求多少個工作程序（如果有的話）。

在等效的循序索引建立幾乎看不到或沒有任何好處的情況下，並行索引建立可能會受益於增加 maintenance_work_mem。請注意，maintenance_work_mem 可能會影響請求的工作程序數量，因為並行工作程序必須至少擁有 32MB 的總 maintenance_work_mem 預算份額。領導程序還必須剩餘 32MB 份額。增加 max_parallel_maintenance_workers 可能會允許使用更多的工作程序，這將減少建立索引所需的時間，只要索引建立尚未受 I/O 限制。當然，也應該有足夠的 CPU 容量，否則這些容量將閒置。

透過 ALTER TABLE 直接設定 parallel_workers 的值，可以直接控制 CREATE INDEX 針對該表所請求的平行工作者處理序數量。這完全繞過了成本模型，並且防止 maintenance_work_mem 影響請求的平行工作者數量。透過 ALTER TABLE 將 parallel_workers 設定為 0 將在所有情況下停用該表的平行索引建立。

雖然帶有 CONCURRENTLY 選項的 CREATE INDEX 支援無特殊限制的平行建立，但實際上只有第一次表掃描會以平行方式執行。

使用 DROP INDEX 來移除索引。

如同任何長時間執行的交易，表上的 CREATE INDEX 可能會影響並行 VACUUM 在任何其他表上可以移除哪些元組。

PostgreSQL 的先前版本也具有 R-tree 索引方法。 此方法已被移除，因為它相較於 GiST 方法沒有顯著優勢。 如果指定 USING rtree，CREATE INDEX 會將其解譯為 USING gist，以簡化將舊資料庫轉換為 GiST 的過程。

執行 CREATE INDEX 的每個後端都會在 pg_stat_progress_create_index 視圖中報告其進度。 有關詳細資訊，請參閱 第 27.4.4 節。

範例

在表 films 中的欄位 title 上建立唯一的 B-tree 索引

CREATE UNIQUE INDEX title_idx ON films (title);

在表 films 中的欄位 title 上建立具有包含欄位 director 和 rating 的唯一 B-tree 索引

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating);

建立停用重複資料刪除的 B-Tree 索引

CREATE INDEX title_idx ON films (title) WITH (deduplicate_items = off);

在表達式 lower(title) 上建立索引，允許有效的區分大小寫搜尋

CREATE INDEX ON films ((lower(title)));

（在本範例中，我們選擇省略索引名稱，因此系統會選擇一個名稱，通常是 films_lower_idx。）

建立具有非預設定序的索引

CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");

建立具有非預設 NULL 排序的索引

CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);

建立具有非預設填滿因子的索引

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

建立一個GIN索引，並且停用快速更新

CREATE INDEX gin_idx ON documents_table USING GIN (locations) WITH (fastupdate = off);

在表 films 的欄位 code 上建立索引，並將索引放置在表空間 indexspace 中

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;

在點屬性上建立 GiST 索引，以便我們可以有效地在轉換函式的結果上使用方框運算子

CREATE INDEX pointloc
    ON points USING gist (box(location,location));
SELECT * FROM points
    WHERE box(location,location) && '(0,0),(1,1)'::box;

建立索引而不鎖定對表的寫入

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);

相容性

CREATE INDEX 是 PostgreSQL 語言的擴充功能。 SQL 標準中沒有關於索引的規定。

另請參閱