postgresql - 如何制作快速的 pg_trgm DESC(降序)？

Question

我在一个表中有一个包含 100.000 个句子的列表，使用 pg_trgm 我可以使用 GIN/GIST 非常快速地获得与我的字符串最接近的"super cool"指数。看官方例子:

https://www.postgresql.org/docs/11/pgtrgm.html

可悲的是，我想要相反的，我想要最最不同的一个，但是DESC时不使用GIN/GIST索引，所以很慢.

SELECT t, 'super cool' <-> t AS dist
  FROM test_trgm
  ORDER BY dist DESC LIMIT 10;

我该怎么做？从源代码重建 pg_trgm ?怎么样？

Answer 1

我认为这根本无法优化，除非事先知道“t”或者您可以缓存一些东西。即使您尝试更改 Postgres 源代码，您也很可能看不到任何好处。

在文档中，<-> 运算符是 similarity(t1, t2) 的简写。如果两个术语都已知，您可以为这些分数编制索引，例如，您可以为任何 t1、t2 组合“创建索引”这个函数，它会起作用。这将是一个标准的 BTree 索引，您可以执行小于、大于或任何您想要的检查或排序。

但是 t2 未知，因此，您无法为任何可能的字符串创建索引。 (或者如果数量合理，您可以在表格中伪造所有可能的字符串组合)

如果您不知道另一个术语，排序是如何进行的？好吧，因为你可以得到你的单词 t1，提取所有的三元组，并得到哪些行(tids)至少出现了 X 次。这很快，因为您只需检查原始单词的 N 个三元组、检索桶中的元组 ID、计数和排序。

现在试着反过来做:你需要所有完全没有共同八卦的单词。因此，您必须扫描检索到的三元组，获取元组 ID，然后获取整个表，过滤掉您之前获得的元组 ID。然后继续那些只有 1 个卦的，然后是 2 个，依此类推。这听起来效率很低，就像扫描整个表和索引一两次。

主要问题依赖于检索具有零巧合的匹配项。不管你怎么做，你都需要扫描整个表。

如果您至少可以跳过那些零重合的搜索，那么您可以加快搜索速度。为此，您可以使用 set_limit(0.0001) 并使用“%”运算符将它们过滤掉。 (但听起来这不是你想要的)

即使将八卦提取到数组或子表中似乎也无济于事。你的问题看起来像一个布隆过滤器，但反过来了，我仍然不确定是否有可能创建这样的索引。

也许如果您添加更多关于您想要完成什么的信息，我们可以找到不使用三元组的不同方法。