Postgresql 索引小写（列）无法按预期工作？
以下是我在搜索“amadeus”子字符串时可以获得的数据示例：
db=# SELECT entidade FROM gma WHERE entidade ILIKE '%amadeus%';
entidade
---------------------------------
Hairdresser Amadeus
Snack-Bar Amadeus
Restaurant Amadeus
Restaurant Amadeus
Restaurant Amadeus
Amadeus - Musical Instruments
(6 rows)
但是我希望能够将 ILIKE 替换为 LIKE。因此，我尝试仅使用小写字母来索引 entidade：
db=# CREATE INDEX idx_gma_entidade ON gma USING btree
db-# ( lower(entidade) );
CREATE INDEX
现在我期望使用 LIKE 访问完全相同的数据：
db=# SELECT entidade FROM gma WHERE entidade LIKE '%amadeus%';
entidade
----------
(0 rows)
但是，如您所见，结果不是我所期望的...... 有人可以解释一下原因吗？而且，如果可能的话，我怎样才能实现预期的行为？
The following is an example of the data I can get when searching for "amadeus" substring:
db=# SELECT entidade FROM gma WHERE entidade ILIKE '%amadeus%';
entidade
---------------------------------
Hairdresser Amadeus
Snack-Bar Amadeus
Restaurant Amadeus
Restaurant Amadeus
Restaurant Amadeus
Amadeus - Musical Instruments
(6 rows)
However I want to be able replace ILIKE by LIKE. So I tried to index entidade with only lower-case letters:
db=# CREATE INDEX idx_gma_entidade ON gma USING btree
db-# ( lower(entidade) );
CREATE INDEX
By now I was expecting to access exactly the same data using LIKE:
db=# SELECT entidade FROM gma WHERE entidade LIKE '%amadeus%';
entidade
----------
(0 rows)
But, as you can see, the result is not what I expected...
Can somebody care to explain why? And, if possible, how can I achieve the expected behaviour?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
运行选择时，您需要在列上使用 lower() 函数：
SELECT entidade FROM gma WHERE lower(entidade) LIKE '%amadeus%';但是因为前面有一个通配符，无论如何，查询永远不会使用索引。所以创建一个是没有意义的
You need to use the lower() function on the column when running your select:
SELECT entidade FROM gma WHERE lower(entidade) LIKE '%amadeus%';But because you have a wildcard at the front, the query will never use an index anyway. So there is no point in creating one
您不能为此使用 btree 索引。在搜索字符串的开头使用通配符会使索引变得无用。
使用全文搜索或查看 wildspeed。
You can't use btree indexes for this. Using a wildcard at the beginning of the search string makes an index useless.
Use full text search or take a look at wildspeed.
创建索引永远不应该改变查询的结果，而只能改变查询的实现方式。您必须指定 ILIKE 或诸如
lower(column) LIKE '...'之类的构造，以便与文本列进行不区分大小写的匹配。您可以使用 citext contrib 模块创建一个默认情况下不区分大小写匹配的 citext 类型，这似乎就是您想要的。Creating an index should never change the result of a query, only how it is implemented. You must specify ILIKE or a construct such as
lower(column) LIKE '...'for a case-insensitive match against a text column. You can use thecitextcontrib module to create a citext type that will match case-insensitively by default, which seems to be what you want.正如每个人都指出的那样，查询没有使用您的索引。您可以在查询前面使用解释分析关键字来查找是否使用了索引。
As everyone pointed out the query was not using your index. You can use explain analyse keywords in front of your query to find whether index was used or not.