我有一个 PostgreSQL 数据库,其中的表中包含一堆字符串列,并使用 pg_trgm 对其建立了三元索引。它们的查询方式如下
SELECT [...] FROM mytable WHERE mycolumn ILIKE '%searchterm%';
对于搜索条件非常具体的查询,这种方法非常有效。该表包含几十万行数据,其中一些列可能包含大量文本(尽管大多数都是相当短的字符串)。
问题是,当搜索词很常见且出现在许多行中时,这种方法效果不佳。对于具有特定搜索词的搜索查询,此查询在几毫秒内完成,对于匹配所有行的约 80% 的词,则需要数十秒。实际上,三元组索引几乎用于所有查询,即使是慢速查询。
我知道这可能是索引工作方式固有的问题,而且不容易修复,但我主要在寻找比等待查询超时更好的处理这些情况的方法。无论如何,搜索这些术语并不是很有用,它不会过滤掉足够多的行。但我无法阻止用户输入这些术语,所以如果可能的话,我希望比查询超时更优雅地失败。
首先,有什么方法可以提高这种查询的性能?是否有更好的索引类型或其他方法来调整索引的性能?我故意在这里使用三元组索引而不是 Postgres 全文搜索,因为词干提取在这里会适得其反。搜索词通常不是英文单词,在我的第一次实验中,词干提取对某些查询是有害的,因为它会删除特定领域的缩写或术语的部分内容。
有没有办法检测出搜索词过于不具体的情况?如果无法使这些查询足够快,那么下一个最好的办法就是不运行它们并超时,而是尽早向用户显示有用的反馈,表明他们的搜索词有问题。