如何使子字符串匹配查询在大表上快速运行？
我有一个带有文本字段的大表，并且想要使用 ILIKE 查询该表以查找包含给定子字符串的记录。它在小桌子上完美运行，但就我而言，这是一个相当耗时的操作，而且我需要它快速运行，因为我在网站的实时搜索字段中使用它。任何想法将不胜感激...
I have a large table with a text field, and want to make queries to this table, to find records that contain a given substring, using ILIKE. It works perfectly on small tables, but in my case it is a rather time-consuming operation, and I need it work fast, because I use it in a live-search field in my website. Any ideas would be appreciated...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
检查 等待 9.1 –更快的 LIKE/ILIKE 博客文章，来自 depesz 使用的解决方案八卦。
为此，您需要使用尚未发布的 Postgresql 9.1。然后你的写入速度会慢得多，因为三元组索引很大。
建议全文搜索 by user12861 仅当您搜索单词而不是子字符串时才会有帮助。
Check Waiting for 9.1 – Faster LIKE/ILIKE blog post from depesz for a solution using trigrams.
You'd need to use yet unreleased Postgresql 9.1 for this. And your writes would be much slower then, as trigram indexes are huge.
Full text search suggested by user12861 would help only if you're searching for words, not substrings.
您可能想研究全文索引。这有点复杂，也许其他人可以给出更好的描述，或者您可以尝试一些链接，例如：
http://wiki.postgresql.org/wiki/Full_Text_Indexing_with_PostgreSQL
You probably want to look into full text indexing. It's a bit complicated, maybe someone else can give a better description, or you might try some links, like this one for example:
http://wiki.postgresql.org/wiki/Full_Text_Indexing_with_PostgreSQL