為什麼要在文本列上索引 text_pattern_ops？

今天，七週內的七個數據庫向我介紹了每個操作員的索引。

您可以通過創建text_pattern_ops運算符類索引來索引字元串以匹配先前查詢的模式，只要這些值以小寫形式索引即可。

CREATE INDEX moves_title_pattern ON movies (
   (lower(title) text_pattern_ops);

我們使用了，text_pattern_ops因為標題是文本類型。如果您需要索引 varchars、chars 或名稱，請使用相關操作：varchar_pattern_ops、bpchar_pattern_ops和name_pattern_ops.

我發現這個例子真的很混亂。為什麼這樣做有用？

如果列是文本類型，在用作搜尋值之前，其他類型（varchar、char、name）是否會轉換為文本？

該索引與使用預設運算符的索引有何不同？

CREATE INDEX moves_title_pattern ON movies (lower(title));

文件通常會為您提供此類問題的答案。就像在這種情況下一樣：

運算符類 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 語言環境中的模式匹配查詢。

您可以按如下方式檢查您的語言環境（可能是 UTF8 而不是“C”）：

postgres=> show lc_collate;
lc_collate
-------------
en_GB.UTF-8

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