unaccent() 阻止 Postgres 中的索引使用

我想从导入到 PostgreSQL 9.3.5 的 OpenStreetMap 数据库中检索给定名称的路线，操作系统是 Win7 64 位。为了具有一定的容错能力，我使用了 Postgres 的非重音扩展。

我的查询如下所示：

SELECT * FROM germany.ways
WHERE lower(tags->'name') like lower(unaccent('unaccent','Weststrasse'))

查询计划：

Seq Scan on ways  (cost=0.00..2958579.31 rows=122 width=465)
  Filter: (lower((tags -> 'name'::text)) ~~ lower(unaccent('unaccent'::regdictionary, 'Weststrasse'::text)))

奇怪的是，这个查询对方法使用顺序扫描，尽管索引存在于lower(tags->'name'):

CREATE INDEX ways_tags_name ON germany.ways (lower(tags -> 'name'));

一旦我从查询中删除非重音符号，Postgres 就会使用索引：

SELECT * FROM germany.ways
WHERE lower(tags->'name') like lower('Weststrasse')

查询计划：

Index Scan using ways_tags_name on ways  (cost=0.57..495.43 rows=122 width=465)
  Index Cond: (lower((tags -> 'name'::text)) = 'weststrasse'::text)
  Filter: (lower((tags -> 'name'::text)) ~~ 'weststrasse'::text)

为什么 unaccent 会阻止 Postgres 使用索引？在我看来，这是没有意义的，因为在执行实际查询之前，应该已经完全知道非重音符号（变音符号删除等）的结果。所以Postgres应该能够使用索引。使用unaccent时如何避免seq扫描？

IMMUTABLE的变体unaccent()

为了澄清其中的错误信息已接受，现已删除，答案不正确:
表达式索引仅允许IMMUTABLE功能（出于明显的原因）和unaccent()只是STABLE. The 您在评论中建议的解决方案也是有问题的。详细解释和正确的解决方案 for that:

• PostgreSQL 是否支持“重音不敏感”排序规则？

取决于内容tags->name添加可能有用unaccent()到表达式索引，但这与为什么不使用索引的问题正交：

• PostgreSQL 重音+不区分大小写的搜索

实际问题/解决方案

运营商LIKE在你的查询中是微妙的wrong（最有可能的）。你做not想要将“Weststrasse”解释为搜索模式，您希望按原样匹配（规范化）字符串。替换为=运算符，您将看到当前索引的（位图）索引扫描，不管的函数波动性unaccent():

SELECT * FROM germany.ways
WHERE lower(tags->'name') = lower(unaccent('unaccent','Weststrasse'))

Why?

的右操作数LIKE is a pattern。 Postgres 不能使用普通的 btree 索引进行模式匹配（例外情况适用). A LIKE使用纯字符串作为模式（无特殊字符）可以通过对 btree 索引进行相等检查来优化。但如果字符串中有特殊字符，this索引已出。

如果有一个IMMUTABLE函数的右边LIKE，可以立即评估，并且上述优化仍然是可能的。引用手册上函数波动类别:

IMMUTABLE ...
此类别允许优化器在以下情况下预先评估函数： 查询使用常量参数调用它。

如果函数波动性较小（STABLE or VOLATILE）。这就是为什么你的“解决方案”是伪造IMMUTABLE unaccent()看似有效，但实际上是给猪涂口红。

重申一下：

• 如果您想与LIKE和模式，使用三元组GIN索引.
• 如果您不想与LIKE和模式，使用相等运算符=（以及表达式上的 B 树索引lower(tags->'name').