當需要具有運算符類的索引時，唯一索引是否優於唯一約束

Postgres 文件說：

使用索引來強制執行唯一約束可以被認為是不應該直接訪問的實現細節。然而，應該注意的是，沒有必要在唯一列上手動創建索引。這樣做只會複製自動創建的索引。

基於此，如果我想要一個列上的表達式索引並且還希望該列是唯一的，那麼case 2下面會更好，因為它可以通過單個索引完成上述操作。然而case 1會因為唯一約束而自動創建一個索引，而另一個因為我需要一個小寫索引而自動創建？

正如@Colin’tHart 指出的那樣，這兩種情況並不相同。我應該在不使用lower()表達的情況下發布這個問題。在這種情況下，我的理解是 aCREATE UNIQUE INDEX會比唯一約束和簡單索引更好。

基於此，如果我想要一個text_pattern_ops列上帶有運算符類（例如）的索引並且還希望該列是唯一的，那麼case 2下面會更好，因為它可以通過單個索引完成上述操作。而case 1由於唯一約束和另一個因為我需要不同的運算符類而會自動創建一個索引？

情況1：

CREATE TABLE book (
  id SERIAL PRIMARY KEY,
  name text NOT NULL,
  CONSTRAINT book_name_key UNIQUE (name)
);

CREATE INDEX book_name_like ON book (name text_pattern_ops);

案例二：

CREATE TABLE book (
  id SERIAL PRIMARY KEY,
  name text NOT NULL
);

CREATE UNIQUE INDEX book_name_like ON book (name text_pattern_ops);

text_pattern_ops有那麼一刻，我想 USING INDEX在添加UNIQUE CONSTRAINT. 但這失敗了，因為：

ERROR: index "book2_name_like" does not have default sorting behavior

手冊：

索引不能有表達式列，也不能是部分索引。此外，它必須是具有預設排序順序的 b 樹索引。這些限制確保索引等同於由正常ADD PRIMARY KEY或ADD UNIQUE命令建構的索引。

例如，像這樣的唯一索引將允許 FK 約束引用它，但性能很差，因為它不支持標準運算符。

手冊：

請注意，如果您希望涉及普通<、<=、>或>=比較的查詢使用索引，則還應該使用預設運算符類創建索引。

所以回答這個問題：

如果您需要一個UNIQUE CONSTRAINT（除其他原因外：使用 FK 引用它），您的第一個帶有約束和索引的變體是唯一的選擇。此外，由約束創建的索引的預設運算符類支持更多操作（如按預設排序順序排序）。

如果您不需要任何這些，請使用第二個變體，因為很明顯，維護一個索引更便宜：只需一個UNIQUE text_pattern_ops索引。

索引和約束的區別：

• PostgreSQL 如何強制執行 UNIQUE 約束/它使用什麼類型的索引？

與 COLLATE “C” 替代

除了創建兩個索引之外，還有另一種可能更可取的索引替代方法。手冊：xxx_pattern_ops

與預設運算符類的不同之處在於，這些值是嚴格逐個字元比較的，而不是根據特定於語言環境的排序規則。LIKE當數據庫不使用標準的“C”語言環境時，這使得這些運算符類適用於涉及模式匹配表達式（或 POSIX 正則表達式）的查詢。

和：

索引自動使用基礎列的排序規則。

您可以創建沒有排序規則的列（使用COLLATE "C"）。然後預設操作符類的行為方式與其他操作符相同text_pattern_ops——此外，索引可以與所有標準操作符一起使用。

CREATE TABLE book2 (
  book_id serial PRIMARY KEY,
  book    text NOT NULL **COLLATE "C"** UNIQUE  -- that's all!
);

現在，LIKE可以使用索引：

SELECT * FROM book2 WHERE book LIKE 'foo%';

但ILIKE仍然不能：

SELECT * FROM book2 WHERE book ILIKE 'foo%';

db<>fiddle here 舊的 sqlfddle

考慮使用附加模組 pg_trgm 的三元組索引以獲得更通用的解決方案：

• 在 PostgreSQL 中使用 LIKE、SIMILAR TO 或正則表達式進行模式匹配

引用自：https://dba.stackexchange.com/questions/90722