PosgreSQL 目录 COLLATE 设置不当导致查询优化无法使用索引

PosgreSQL 索引 COLLATE 设置不当导致查询优化无法使用索引

最近在维护一个大型PostgreSQL数据库的时候，遇到了一个问题，表的某一字段明明有索引，但是执行查询的时候优化器不去使用，VACUUM、REINDEX均无效，简单的=条件，也会导致频繁的seq scan。

无奈之下，查看索引的定义，发现使用了 COLLATE "C" 选项，才记起以前看到文档里说，每个Index只支持一种Collate，这个文档原文：

An index can support only one collation per index column. If multiple collations are of interest, multiple indexes may be needed.

一个索引仅能支持一种字符集（COLLATE），如果需要支持多类，则需要为每一类建一个索引。

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

索引不指定COLLATE，则用默认那一列的COLLATE来创建，因此，下面的查询

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,

能用这个索引，缘于比较运算默认使用的是表格列的COLLATE。但是，如果你用另外的COLLATE来参与查询，索引就失效了，比如下面的查询

SELECT * FROM test1c WHERE content > constant COLLATE "y";

are also of interest, an additional index could be created that supports the "y" collation, like this:

要让上面这句话可以使用索引优化查询，必须为“y”COLLATE也建立一个索引。

CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y");

看到这个东西，知道了原因。创建数据库的表默认的是"zh-cn"，然而索引是“C”，导致默认情况下，zh-cn无索引可用。问到原因，原来这个数据库是从别处迁移来的，以前表用的是数据库的默认 COLLATE “C”，用pgAdmin创建索引时，又制定了“C”，导出后，创建表的COLLATE不含有“C"的指定，而索引含有，迁移过来后，表用了新数据库的”zhcn“，而索引不变。

知道了原因，就立刻修改所有的索引。这里有上百个索引，不可能一个个敲，又不想修改存储过程，全部人工加上 content COLLATE 转换，那就有个小招数。

select * from pg_indexes

可以返回所有索引的create 语句、名称等东西

在Excel里贴进去，单元格里依次拍上

"DROP INDEX" , 索引名， ";" 索引创建语句 , ";"

而后，把 ’COLLETE "C"‘ 查找替换成空，最后，全部复制到记事本，就已经是可以使用的脚本啦！

PosgreSQL 目录 COLLATE 设置不当导致查询优化无法使用索引

相关推荐