運算符“~<~”使用 varchar_pattern_ops 索引，而普通的 ORDER BY 子句不使用？

假設我有一個包含 1 000 000 條記錄的表。表結構為：

create table individual (     
 id serial primary key,
 surname varchar(128),
 "name" varchar(128),
 patronymic varchar(128),
 birth_dt date
)

我創建了一個複合索引。

索引 1

create index on individual using btree (upper(surname) varchar_pattern_ops
 , upper("name") varchar_pattern_ops, patronymic varchar_pattern_ops, birth_dt);

在查詢中使用或模式匹配varchar_pattern_ops時應應用的文件狀態。LIKE結論：這個索引不會在下面的查詢中使用，即使它從 1 000 000 只得到 10 行。

查詢 1

select * from individual
order by upper(surname), upper("name"), upper(patronymic), birth_dt limit 10;

甚至更多，文件建議不要創建索引varchar_pattern_ops。

索引 2

create index on individual using btree
(upper(surname), upper("name"), upper(patronimyc), birth_dt);

然後查詢 usingLIMIT將使用此索引。

我在 Postgres 使用者論壇上發現了一個欺騙來強制 Postgres 使用第一個索引。它是運營商~<~。

查詢 2

select * from individual
order by upper(surname) using ~<~
      , upper("name") using ~<~
      , upper(patronymic) using ~<~
      , birth_dt limit 100;

在這種情況下，即使INDEX 2不存在，也會使用**INDEX 1 。**我試圖調查以找出它發生的原因，但失敗了。

有一些系統表，例如pg_operator，（我認為）將運算符連結~<~到一些最有可能使用的函式LIKE或正則表達式。

我執行了QUERY 1和QUERY 2幾次並手動比較了結果。看起來運算符~<~給出了正確的結果，但我沒有冒任何風險，只是創建了一個正常的索引。

我仍然對 Postgres 計劃器如何決定使用哪個索引在它遇到QUERY 1~<~中的運算符的索引感興趣。

這圍繞**COLLATION規則（這是您的locale**的一部分）。根據文件：

和排序規則都指定“傳統 C”行為C，POSIX其中只有 ASCII 字母“ A”到“ Z”被視為字母，並且嚴格按字元程式碼字節值進行排序。

大膽強調我的。這些內部運算符：

~<~, ~<=~, ~>=~,~>~

是 operator classes 的成員，text_pattern_ops它們忽略目前的排序規則並預設為“傳統 C”行為。根據文件：varchar_pattern_ops``bpchar_pattern_ops

與預設運算符類的不同之處在於，這些值是嚴格逐個字元比較的，而不是根據特定於語言環境的排序規則。

這就是為什麼他們可以使用您的第一個索引，而他們的區域感知等價物

<, <=, >=,>

將需要第二個（使用預設運算符類）。

**ORDER BY**預設為目前排序規則。除非您碰巧使用“C”排序規則，否則第二組運算符用於排序，它只能使用具有匹配排序規則的索引（預設 btree 索引）。

值得注意的是，模式匹配通常依賴於“傳統 C”行為，這就是為什麼左錨LIKE匹配可以使用_patern_ops具有非 C 語言環境的索引，但不能使用預設索引。比較（最後一章）：

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

在 Postgres 9.1+ 中，您可以強制使用選定的排序順序COLLATE "locale_name"（區域設置必須在您的系統中可用！）。因此，如果您使用**COLLATE "C"**(這比evil hack using ~<~更好的選擇) 執行查詢，它可以使用_pattern_ops索引：

SELECT * FROM individual
ORDER  BY upper(surname) **COLLATE "C"**
       , upper("name") **COLLATE "C"**
       , upper(patronymic) **COLLATE "C"**
       , birth_dt
LIMIT  100;

結果不同。嘗試使用非 ASCII 字母，如ö, é等。這些通常根據您目前的排序規則進行排序，而字母a-z通常在所有語言環境中排序相同。

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