为什么要在文本列上使用 text_pattern_ops 索引？

Question

为什么要在文本列上使用 text_pattern_ops 索引？

postgresqlindexcollationpattern-matching

31

今天七周七大数据库向我介绍了按操作员索引。

只要将值以小写形式索引，您就可以通过创建text_pattern_ops操作员类索引来为先前的查询创建模式匹配字符串的索引。

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

我们使用`text_pattern_ops`是因为标题的类型是文本。如果您需要对varchar，char或name进行索引，请使用相关的操作符：`varchar_pattern_ops`，`bpchar_pattern_ops`和`name_pattern_ops`。我觉得这个例子真的很令人困惑。为什么这样做有用呢？如果列的类型是文本，那么其他类型（varchar，char，name）在被用作搜索值之前是否会转换为文本？这种索引与使用默认操作符的索引有何不同？

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

- Iain Samuel McLean Elder

2这个相关的问题可能会有所帮助：http://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql/10696 - Erwin Brandstetter

谢谢，Erwin。你对那个问题的回答在我研究这本书中的观点时非常有帮助。 - Iain Samuel McLean Elder

An error occurred:

marked(): input parameter is undefined or null
Please report this to https://github.com/markedjs/marked.

- undefined

1个回答

- dezso · Accepted Answer

文档通常可以回答这类问题。就像在这个案例中一样：

The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar, and char respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard "C" locale. As an example, you might index a varchar column like this:
CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
Note that you should also create an index with the default operator class if you want queries involving ordinary <, <=, >, or >= comparisons to use an index. Such queries cannot use the xxx_pattern_ops operator classes. (Ordinary equality comparisons can use these operator classes, however.) It is possible to create multiple indexes on the same column with different operator classes.

文档继续说道：如果您使用C语言环境，则不需要xxx_pattern_ops操作符类，因为具有默认操作符类的索引可用于在C语言环境中进行模式匹配查询。您可以按以下方式检查您的语言环境（它很可能是UTF8而不是"C"）：

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