Postgresql 中文操作指南
11.11. Indexes and Collations #
一个索引只能支持每个索引列一种校对规则。如果有多个校对规则,则可能需要多个索引。
An index can support only one collation per index column. If multiple collations are of interest, multiple indexes may be needed.
考虑以下陈述:
Consider these statements:
CREATE TABLE test1c (
id integer,
content varchar COLLATE "x"
);
CREATE INDEX test1c_content_index ON test1c (content);
索引会自动使用基础列的校对规则。因此,如下形式的查询
The index automatically uses the collation of the underlying column. So a query of the form
SELECT * FROM test1c WHERE content > constant;
可以使用索引,因为比较在默认情况下会使用列的校对规则。但是,此索引无法加速涉及其他校对规则的查询。因此,如果如下形式的查询
could use the index, because the comparison will by default use the collation of the column. However, this index cannot accelerate queries that involve some other collation. So if queries of the form, say,
SELECT * FROM test1c WHERE content > constant COLLATE "y";
也很相关,则可以创建一个支持 "y" 校对规则的额外索引,如下所示:
are also of interest, an additional index could be created that supports the "y" collation, like this:
CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y");