英文:
Pattern index not getting used in my postgres query
问题
你的查询是：
SELECT *
FROM all_persons
WHERE ((("email") ilike ('abc.com%')))
ORDER BY "lastOrderAt" DESC
OFFSET 0 LIMIT 20
我已经在表上的 email 列上添加了索引 emailvarchar_pattern_ops。
使用 explain 我发现索引未被使用。有人可以指导一下是索引创建错误了还是如何加快查询速度？
英文:
My query is
SELECT *
FROM all_persons
WHERE ((("email") ilike ('abc.com%')))
ORDER BY "lastOrderAt" DESC
OFFSET 0 LIMIT 20
I have already added indexes on table on column emailvarchar_pattern_ops.
Using explain I found that index is not used. Can someone guide that is the index wrongly created or how can I speed up the query?
答案1
得分: 1
varchar_pattern_ops区分大小写，因此不支持ILIKE。
您可以确保列以全小写存储，确保模式也全小写，然后只使用LIKE而不是ILIKE。或者您可以在运行时将它们转换为小写，并让它使用一个函数索引。
或者，您可以使用三元组索引（https://www.postgresql.org/docs/current/pgtrgm.html），它支持ILIKE，但如果通配符始终在模式的末尾，效率将低于btree索引。
英文:
varchar_pattern_ops is case sensitive. So it doesn't support ILIKE.
You can make sure the column is stored in all lower case, make sure the pattern is also all lower case, and then just use LIKE rather than ILIKE. Or you can convert them to lower case on fly, and have it use an index which is a functional index.
Or you can use a trigram index (https://www.postgresql.org/docs/current/pgtrgm.html), which does support ILIKE, but will be less efficient than a btree if the wildcard is always at the end of the pattern.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论