CREATE INDEX — 定義一個新索引
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ]name] ON [ ONLY ]table_name[ USINGmethod] ( {column_name| (expression) } [ COLLATEcollation] [opclass[ (opclass_parameter=value[, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ INCLUDE (column_name[, ...] ) ] [ NULLS [ NOT ] DISTINCT ] [ WITH (storage_parameter[=value] [, ... ] ) ] [ TABLESPACEtablespace_name] [ WHEREpredicate]
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 子句中使用使用者定義的函式，請記住在建立函式時將其標記為不可變。
UNIQUE當建立索引時（如果已有資料）以及每次新增資料時，都會導致系統檢查表中是否存在重複值。嘗試插入或更新會導致重複條目的資料將生成錯誤。
當唯一索引應用於分割槽表時，還有其他限制；請參閱 CREATE TABLE。
CONCURRENTLY使用此選項時，PostgreSQL 將在不獲取任何阻止表上併發插入、更新或刪除的鎖的情況下構建索引；而標準的索引構建會鎖定表直到完成（但允許讀取）。使用此選項時有幾個需要注意的注意事項——請參閱下面的 併發構建索引。
對於臨時表，CREATE INDEX 始終是非併發的，因為沒有其他會話可以訪問它們，並且非併發索引建立成本更低。
IF NOT EXISTS如果同名的關聯已存在，則不報錯。在這種情況下會發出通知。請注意，不能保證現有索引與將要建立的索引有任何相似之處。當指定 IF NOT EXISTS 時，需要索引名。
INCLUDE可選的 INCLUDE 子句指定了一個列列表，這些列將被包含在索引中作為 非鍵 列。非鍵列不能用於索引掃描的查詢條件，並且對於索引強制執行的任何唯一性或排除約束，它都會被忽略。但是，僅索引掃描可以在不訪問索引表的情況下返回非鍵列的內容，因為它們可以直接從索引條目中獲取。因此，新增非鍵列可以使本來無法使用索引的查詢能夠使用僅索引掃描。
在向索引新增非鍵列（尤其是寬列）時，應謹慎。如果索引元組超過了索引型別允許的最大大小，資料插入將失敗。無論如何，非鍵列會複製索引表中的資料並導致索引大小膨脹，從而可能減慢搜尋速度。此外，B-tree 的重複資料刪除功能從未使用過帶有非鍵列的索引。
INCLUDE 子句中列出的列不需要相應的運算子類；該子句可以包含資料型別沒有為給定訪問方法定義運算子類的列。
由於表示式不能用於僅索引掃描，因此它們不支援作為包含的列。
目前，B-tree、GiST 和 SP-GiST 索引訪問方法支援此功能。在這些索引中，INCLUDE 子句中列出的列的值包含在對應於堆元組的葉元組中，但未包含在用於樹導航的上層索引條目中。
name要建立的索引的名稱。此處不能包含模式名；索引始終在其父表所在的同一模式中建立。索引名稱必須與該模式中任何其他關聯（表、序列、索引、檢視、物化檢視或外部表）的名稱不同。如果省略名稱，PostgreSQL 將根據父表名和索引列名選擇一個合適的名稱。
ONLY如果表已分割槽，則指示不要遞迴建立分割槽的索引。預設是遞迴的。
table_name要索引的表的名稱（可能是帶模式限定的）。
method要使用的索引方法的名稱。選項包括 btree、hash、gist、spgist、gin、brin，或使用者安裝的訪問方法，如 bloom。預設方法是 btree。
column_name表的列名。
expression基於表的一列或多列的表示式。通常，表示式必須用括號括起來，如語法所示。但是，如果表示式是函式呼叫形式，則可以省略括號。
collation用於索引的排序規則的名稱。預設情況下，索引使用為要索引的列宣告的排序規則或要索引的表示式的結果排序規則。帶有非預設排序規則的索引可能有助於處理涉及使用非預設排序規則的表示式的查詢。
opclass運算子類的名稱。有關詳細資訊，請參閱下文。
opclass_parameter運算子類引數的名稱。有關詳細資訊，請參閱下文。
ASC指定升序（這是預設值）。
DESC指定降序。
NULLS FIRST指定 NULL 值排在非 NULL 值之前。當指定 DESC 時，這是預設值。
NULLS LAST指定 NULL 值排在非 NULL 值之後。當未指定 DESC 時，這是預設值。
NULLS DISTINCTNULLS NOT DISTINCT指定對於唯一索引，NULL 值是否被視為不同（不相等）。預設情況下，它們是不同的，因此唯一索引可以在列中包含多個 NULL 值。
storage_parameter特定於索引方法的儲存引數的名稱。有關詳細資訊，請參閱下面的 索引儲存引數。
tablespace_name用於建立索引的表空間。如果未指定，則會查詢 default_tablespace，對於臨時表的索引，則查詢 temp_tablespaces。
predicate部分索引的約束表示式。
可選的 WITH 子句指定了索引的 儲存引數。每個索引方法都有自己的一組允許的儲存引數。
B-tree、hash、GiST 和 SP-GiST 索引方法都接受此引數
fillfactor (integer) #控制索引方法在填充索引頁時嘗試填充的程度。對於 B-trees，葉子頁在初始索引構建期間以及在右側擴充套件索引（新增新的最大鍵值）時會填充到此百分比。如果頁面隨後完全填滿，它們將被分割，導致磁碟上的索引結構碎片化。B-trees 使用預設的 fillfactor 90，但可以選擇 10 到 100 之間的任何整數值。
對於預計會有大量插入和/或更新的表上的 B-tree 索引，在 CREATE INDEX 時（在將資料批次載入到表中後）使用較低的 fillfactor 設定可能是有益的。50 - 90 的值可以在 B-tree 索引早期生命週期中有效地“平滑”頁面分割的 “速率”（較低的 fillfactor 甚至可能降低頁面分割的絕對數量，儘管這種效果高度依賴於工作負載）。B-tree 自底向上刪除技術（在 第 65.1.4.2 節 中描述）依賴於在頁面上具有一些 “額外” 空間來儲存 “額外” 的元組版本，因此可能會受到 fillfactor 的影響（儘管影響通常不顯著）。
在其他特定情況下，可以在 CREATE INDEX 時將 fillfactor 提高到 100，以最大化空間利用率。只有當您完全確定表是靜態的（即，它永遠不會受到插入或更新的影響）時，才應考慮這一點。否則，100 的 fillfactor 設定可能會 損害 效能：即使是少量更新或插入也會導致頁面分割突然激增。
其他索引方法使用 fillfactor 的方式不同但大致相似；預設 fillfactor 在不同方法之間有所不同。
B-tree 索引另外接受此引數
deduplicate_items (boolean) #控制 第 65.1.4.3 節 中描述的 B-tree 去重技術的用法。設定為 ON 或 OFF 來啟用或停用最佳化。（如 第 19.1 節 中所述，允許使用 ON 和 OFF 的替代拼寫）。預設值為 ON。
透過 ALTER INDEX 關閉 deduplicate_items 可防止未來的插入觸發去重，但本身並不會使現有的釋出列表元組使用標準的元組表示。
GiST 索引另外接受此引數
buffering (enum) #控制是否使用 第 65.2.4.1 節 中描述的緩衝構建技術來構建索引。設定為 OFF 時停用緩衝，設定為 ON 時啟用緩衝，設定為 AUTO 時初始停用緩衝，但一旦索引大小達到 effective_cache_size 就會動態啟用。預設值為 AUTO。請注意，如果可能使用排序構建，它將代替緩衝構建，除非指定了 buffering=ON。
GIN 索引接受這些引數
fastupdate (boolean) #控制 第 65.4.4.1 節 中描述的快速更新技術的用法。ON 啟用快速更新，OFF 停用它。預設值為 ON。
透過 ALTER INDEX 關閉 fastupdate 可防止未來的插入進入待處理索引條目列表，但本身並不會重新整理現有的條目。您可能需要 VACUUM 表或稍後呼叫 gin_clean_pending_list 函式以確保待處理列表被清空。
gin_pending_list_limit (integer) #覆蓋此索引的全域性設定 gin_pending_list_limit。此值以千位元組為單位。
BRIN索引接受這些引數
pages_per_range (integer) #定義構成一個索引條目的一個塊範圍的表塊數（有關更多詳細資訊，請參閱 第 65.5.1 節）。預設值為 128。BRIN索引（參見 第 65.5.1 節）的每個條目所構成的塊範圍的數量。
autosummarize (boolean) #定義當檢測到下一個頁面範圍發生插入時，是否為前一個頁面範圍排隊一個摘要執行（有關更多詳細資訊，請參閱 第 65.5.1.1 節）。預設值為 off。
建立索引可能會干擾資料庫的常規操作。通常，PostgreSQL 會鎖定要索引的表以防止寫入，並透過一次掃描表來完成整個索引構建。其他事務仍然可以讀取表，但如果它們嘗試插入、更新或刪除表中的行，它們將被阻塞直到索引構建完成。這可能會對即時生產資料庫產生嚴重影響。非常大的表可能需要數小時才能建立索引，即使對於較小的表，索引構建也可能在對生產系統來說過長的時間內阻止寫入者。這可能會對生產系統產生嚴重影響。
PostgreSQL 支援在不阻止寫入的情況下構建索引。透過指定 CREATE INDEX 的 CONCURRENTLY 選項來呼叫此方法。使用此選項時，PostgreSQL 必須對錶進行兩次掃描，並且此外，它必須等待所有可能修改或使用索引的現有事務終止。因此，此方法比標準索引構建需要更多的工作，並且完成時間也更長。然而，由於它允許在索引構建期間繼續正常操作，因此此方法對於在生產環境中新增新索引非常有用。當然，索引建立帶來的額外 CPU 和 I/O 負載可能會減慢其他操作。
在併發索引構建中，索引實際上在一個事務中被錄入系統目錄作為 “無效” 索引，然後另外兩個事務進行兩次表掃描。在每次表掃描之前，索引構建必須等待已修改表的現有事務終止。第二次掃描後，索引構建必須等待所有具有早於第二次掃描的快照（參見 第 13 章）的事務終止，包括其他表上任何併發索引構建階段使用的事務（如果涉及的索引是部分索引或包含非簡單列引用的列）。然後，索引最終可以被標記為 “有效” 並可用於查詢，CREATE INDEX 命令也隨之終止。即使如此，索引也可能無法立即用於查詢：在最壞的情況下，只要存在早於索引構建開始的事務，它就不能被使用。
如果在掃描表時出現問題，例如死鎖或唯一索引中的唯一性衝突，CREATE INDEX 命令將失敗，但會留下一個 “無效” 的索引。此索引將被查詢忽略，因為它可能不完整；但是它仍然會消耗更新開銷。psql 的 \d 命令會將此索引報告為 INVALID。
postgres=# \d tab
Table "public.tab"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
col | integer | | |
Indexes:
"idx" btree (col) INVALID
在這種情況下，推薦的恢復方法是刪除索引，然後再次嘗試執行 CREATE INDEX CONCURRENTLY。（另一種選擇是使用 REINDEX INDEX CONCURRENTLY 來重建索引）。
併發構建唯一索引的另一個注意事項是，在第二次表掃描開始時，唯一性約束已經對其他事務強制執行。這意味著在索引可用於查詢之前，甚至在索引構建最終失敗的情況下，其他查詢也可能報告約束衝突。此外，如果在第二次掃描中發生失敗，“無效” 索引在此之後仍將強制執行其唯一性約束。
表示式索引和部分索引的併發構建是支援的。在評估這些表示式時出現的錯誤可能導致行為類似於上面描述的唯一約束衝突。
常規索引構建允許在同一表上同時進行其他常規索引構建，但一個表一次只能進行一個併發索引構建。在任何一種情況下，在索引構建期間都不允許對錶進行模式修改。另一個不同之處在於，常規的 CREATE INDEX 命令可以在事務塊內執行，但 CREATE INDEX CONCURRENTLY 不能。
目前不支援對分割槽表進行併發索引構建。但是，您可以單獨併發地在每個分割槽上構建索引，然後在最後非併發地建立分割槽索引，以減少分割槽表寫入被鎖定的時間。在這種情況下，構建分割槽索引是一個僅元資料操作。
有關何時使用索引、何時不使用索引以及在哪些特定情況下索引可能有用，請參閱 第 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 選項在您需要支援查詢（依賴索引避免排序步驟）的 “nulls sort low” 行為，而不是預設的 “nulls sort high” 時很有用。
系統定期收集表中所有列的統計資訊。新建立的非表示式索引可以立即使用這些統計資訊來確定索引的有用性。對於新的表示式索引，需要執行 ANALYZE 或等待 autovacuum 守護程序 分析表以生成這些索引的統計資訊。
在 CREATE INDEX 執行時，search_path 會臨時更改為 pg_catalog, pg_temp。
對於大多數索引方法，建立索引的速度取決於 maintenance_work_mem 的設定。較大的值將減少索引建立所需的時間，只要您不將其設定得大於實際可用的記憶體量，否則會導致機器發生交換。
PostgreSQL 可以利用多個 CPU 來構建索引，以更快地處理表行。此功能稱為 並行索引構建。對於支援並行構建索引的索引方法（目前是 B-tree、GIN 和 BRIN），maintenance_work_mem 指定每個索引構建操作作為一個整體可以使用的最大記憶體量，無論啟動了多少工作程序。通常，成本模型會自動確定請求多少工作程序（如果有）。
並行索引構建可能受益於增加 maintenance_work_mem，而等效的序列索引構建幾乎沒有或根本沒有益處。請注意，maintenance_work_mem 可能會影響請求的工作程序數量，因為並行工作程序必須擁有總 maintenance_work_mem 預算至少 32MB 的份額。領導程序還必須有剩餘的 32MB 份額。增加 max_parallel_maintenance_workers 可能會允許使用更多工作程序，這將減少索引建立所需的時間，只要索引構建還沒有達到 I/O 瓶頸。當然，也應該有足夠的 CPU 容量，否則這些容量將閒置。
透過 ALTER TABLE 設定 parallel_workers 的值直接控制 CREATE INDEX 對該表請求的並行工作程序數量。這完全繞過了成本模型，並且不會影響 maintenance_work_mem 對請求的並行工作程序數量的影響。透過 ALTER TABLE 將 parallel_workers 設定為 0 將在所有情況下停用該表的並行索引構建。
您可能希望在設定 parallel_workers 後將其重置，作為調整索引構建的一部分。這可以避免對查詢計劃造成意外更改，因為 parallel_workers 會影響 所有 並行表掃描。
雖然帶 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 列上建立唯一的 B-tree 索引，幷包含 director 和 rating 列
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 標準中沒有為索引提供任何規定。
如果您在文件中看到任何不正確、與您對特定功能的體驗不符或需要進一步澄清的內容，請使用 此表單 來報告文件問題。