我有一个 PostgreSQL 数据库,其中的表中包含一堆字符串列,并使用 pg_trgm 对其建立了三元索引。它们的查询方式如下
SELECT [...] FROM mytable WHERE mycolumn ILIKE '%searchterm%';
对于搜索条件非常具体的查询,这种方法非常有效。该表包含几十万行数据,其中一些列可能包含大量文本(尽管大多数都是相当短的字符串)。
问题是,当搜索词很常见且出现在许多行中时,这种方法效果不佳。对于具有特定搜索词的搜索查询,此查询在几毫秒内完成,对于匹配所有行的约 80% 的词,则需要数十秒。实际上,三元组索引几乎用于所有查询,即使是慢速查询。
我知道这可能是索引工作方式固有的问题,而且不容易修复,但我主要在寻找比等待查询超时更好的处理这些情况的方法。无论如何,搜索这些术语并不是很有用,它不会过滤掉足够多的行。但我无法阻止用户输入这些术语,所以如果可能的话,我希望比查询超时更优雅地失败。
首先,有什么方法可以提高这种查询的性能?是否有更好的索引类型或其他方法来调整索引的性能?我故意在这里使用三元组索引而不是 Postgres 全文搜索,因为词干提取在这里会适得其反。搜索词通常不是英文单词,在我的第一次实验中,词干提取对某些查询是有害的,因为它会删除特定领域的缩写或术语的部分内容。
有没有办法检测出搜索词过于不具体的情况?如果无法使这些查询足够快,那么下一个最好的办法就是不运行它们并超时,而是尽早向用户显示有用的反馈,表明他们的搜索词有问题。
您可以在实际运行查询之前对该查询执行“解释”,然后如果结果行估计值过高而导致查询没有意义,则向用户返回错误。
您可以自动向查询添加 LIMIT。当搜索被认为不具选择性时,这将促使切换到顺序扫描而不是位图扫描,然后 LIMIT 将启动并减少顺序扫描的运行时间(但即使它继续使用位图扫描,只要大部分时间用于位图堆扫描而不是位图索引扫描,它也应该更快。)
您可以尝试设置 gin_fuzzy_search_limit,但根据我的经验,这是不可靠的,而且似乎有缺陷。
你可以试着弄清楚为什么它这么慢。返回几十万行的 80% 不会很快,但可能也不应该花 10 秒的时间。
您可以将超时时间降低到当出现错误输入时在合理时间段内超时的程度。
你可以耐心一点,依靠用户(自我)教育。他们可能会发现某些类型的搜索不值得运行,因为它们耗时太长或返回的行数太多而无用。特别是如果你帮助他们了解这一点。