sql - unaccent() 防止在 Postgres 中使用索引

Question

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

我的查询如下所示:

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'));

一旦我从查询中删除 unaccent，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 使用索引？在我看来，这没有意义，因为在执行实际查询之前应该已经完全知道 unaccent 的结果(删除变音符号等)。所以 Postgres 应该可以使用索引。使用unaccent时如何避免seq扫描？

Answer 1

unaccent() 的不可变变体|

澄清 currently accepted, incorrect answer 中的错误信息:
表达式索引只允许 IMMUTABLE函数(出于显而易见的原因)和 unaccent()只有STABLE . solution you suggested in the the comment也是有问题的。详细解释和适当的解决方案为此:

• Does PostgreSQL support "accent insensitive" collations?

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

• PostgreSQL accent + case insensitive search

实际问题/解决方案

运营商LIKE在您的查询中有微妙的错误(很可能)。您不想要将“Weststrasse”解释为搜索模式，您想要按原样匹配(规范化)字符串。替换为 = 运算符，您将看到使用当前索引的(位图)索引扫描，不考虑 unaccent() 的函数波动:

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

为什么？

LIKE 的右操作数是一个模式。 Postgres 不能使用普通的 btree 索引进行模式匹配 ( exceptions apply )。 LIKE使用纯字符串作为模式(无特殊字符)可以通过对 btree 索引进行相等性检查来优化。但是如果字符串中有特殊字符，这个索引就out了。

如果有 IMMUTABLE LIKE 右边的函数，可以立即对其进行评估，并且上述优化仍然是可能的。每documentation on Function Volatility Categories :

IMMUTABLE ...
This category allows the optimizer to pre-evaluate the function when a query calls it with constant arguments.

对于较小的函数波动性(STABLE 或 VOLATILE)，这是不可能的。这就是为什么你伪造 IMMUTABLE unaccent() 的“解决方案”似乎有效，但它真的是在给 pig 涂口红。

重申一下:

• 如果您想与 LIKE 一起工作和模式，使用 trigram index .
• 如果您不想使用 LIKE和模式，使用相等运算符=