postgresql - 当名称可以是任何语言时，如何按名称索引 postgres 表？

Question

我有一个很大的 postgres 位置表(商店、地标等)，用户可以通过各种方式进行搜索。当用户想要搜索地名时，系统当前会执行(假设搜索是在咖啡馆):

lower(location_name) LIKE '%cafe%'

作为查询的一部分。这是非常低效的。禁止如此。我必须让它更快。我试过在

上索引表

gin(to_tsvector('simple', location_name))

和搜索

(to_tsvector('simple',location_name) @@ to_tsquery('simple','cafe'))

效果很好，搜索时间减少了几个数量级。

但是，位置名称可以使用任何语言，包括中文等不以空格分隔的语言。这个新系统无法找到任何中文位置，除非我搜索确切的名称，而旧系统可以找到部分名称的匹配项。

所以，我的问题是:我能否让它同时适用于所有语言，还是我走错了路？

Answer 1

如果你想优化任意子字符串匹配，一种选择是使用 the pg_tgrm module .添加索引:

CREATE INDEX table_location_name_trigrams_key ON table
  USING gin (location_name gin_trgm_ops);

这会将“Simple Cafe”分解为“sim”、“imp”、“mpl”等，并为每行中的每个 trigam 添加一个条目到索引中。然后查询规划器可以自动使用该索引进行子字符串模式匹配，包括:

SELECT * FROM table WHERE location_name ILIKE '%cafe%';

此查询将在索引中查找“caf”和“afe”，找到交集，获取这些行，然后根据您的模式检查每一行。 (最后一项检查是必要的，因为“caf”和“afe”的交集同时匹配“simple cafe”和“unsafe scaffolding”，而“%cafe%”应该只匹配一个)。随着输入模式变长，索引变得更加有效，因为它可以排除更多的行，但它仍然不如索引整个单词那么有效，所以不要期望性能比 to_tsvector 有所提高。

要注意的是，三元组对于三个字符以下的模式根本不起作用。这可能会也可能不会破坏您的应用程序。

---

编辑:我最初将此添加为评论。

昨晚我几乎睡着了，我又有了一个想法。创建一个 cjk_chars 函数，它接受一个输入字符串，regexp_matches 整个 CJK Unicode 范围，并返回任何此类字符的数组，如果没有则返回 NULL .在 cjk_chars(location_name) 上添加 GIN 索引。然后查询:

WHERE CASE
  WHEN cjk_chars('query') IS NOT NULL THEN
    cjk_chars(location_name) @> cjk_chars('query')
    AND location_name LIKE '%query%'
  ELSE
    <tsvector/trigrams>
  END

Ta-da，unigrams!