在postgresql中，区域设置的哪一部分会导致LIKE操作出现问题

Question

网络上有很多关于C或POSIX以外的语言环境会导致postgresql中的性能问题的讨论。不过，我不清楚区域设置的哪些部分会导致问题以及原因。

在initdb（1）的手册页中，我们看到：

--locale=locale
 Sets the default locale for the database cluster. If this option is not specified, the locale is inherited from the environment that
 initdb runs in. Locale support is described in Section 22.1, u201cLocale Supportu201d, in the documentation.
--lc-collate=locale, --lc-ctype=locale, --lc-messages=locale, --lc-monetary=locale, --lc-numeric=locale, --lc-time=locale
  Like --locale, but only sets the locale in the specified category.

我们还看到：

   To alter the default collation order or character set classes, use the --lc-collate and --lc-ctype options. Collation orders other than C or
   POSIX also have a performance penalty. For these reasons it is important to choose the right locale when running initdb.

这是否意味着我可以使用--lc-collate POSIX --lc-ctype UTF-8并避免性能损失？或者是否涉及其他性能问题？

排序规则会影响排序性能，我对此并不感到惊讶，但不使用索引的LIKE比较也会出现同样的问题吗？有人能解释LIKE运算符的问题吗？

Answer 1

这听起来像是在谈论text_pattern_ops运算符类及其在C以外区域的数据库中的应用程序。

问题不在于编码，而在于排序规则。

b-tree索引要求所有东西都有一个单一的、稳定的排序顺序，遵循一些不变量，比如假设如果是a < b，那么是b > a。比较运算符用于在构建、维护和索引时对树进行排序。

对于文本字符串，当确定一个字符串是大于还是小于另一个字符串时，比较运算符会应用该语言的排序规则，以便就用户而言字符串排序"正确"。这些规则依赖于区域设置，可以执行诸如忽略标点符号和空白之类的操作。

LIKE操作员对区域设置不感兴趣。它只是想找到一个前缀字符串，而不能忽略标点符号。因此，它不能使用使用排序规则创建的b树索引，该排序规则可能会忽略标点符号/空白等。LIKE逐字符遍历索引树以查找匹配项，如果索引可能忽略字符，它就不能这样做。

这就是为什么，如果您的DB使用除"C"（POSIX）之外的区域设置，则必须创建不同的索引以用于LIKE。

本地化排序示例，比较：

regress=> WITH x(v) AS (VALUES ('10'),('1'),('1.'),('2.'),('.2'),('.1'),('1-1'),('11')
)
SELECT v FROM x ORDER BY v COLLATE "en_AU";
  v  
-----
 1
 .1
 1.
 10
 11
 1-1
 .2
 2.
(8 rows)
regress=> WITH x(v) AS (VALUES ('10'),('1'),('1.'),('2.'),('.2'),('.1'),('1-1'),('11')
)
SELECT v FROM x ORDER BY v COLLATE "C";
  v  
-----
 .1
 .2
 1
 1-1
 1.
 10
 11
 2.
(8 rows)

text_pattern_ops操作类满足了这一需求。在较新的PostgreSQL版本中，您可以在目标列上使用COLLATE "C"创建索引，以满足相同的需求，例如：

CREATE INDEX idx_c ON t2(x COLLATE "C");

LIKE将使用这样的索引，它也可以用于更快的排序，在这种情况下，您不关心给定操作的区域设置，例如

SELECT x FROM t2 ORDER BY x COLLATE "C";