如何指数名称一个Postgres表，当名称可以是任何语言？(How to index a postg

我的用户可以以各种方式搜索位置（商店，地标等）的大型Postgres的表。 当用户想要做一个地方的名称进行搜索，目前还系统（假定搜索上网吧）：

lower(location_name) LIKE '%cafe%'

作为查询的一部分。 这是效率非常低。 令人望而却步。 重要的是我有这样的速度更快。 我试过在索引表

gin(to_tsvector('simple', location_name))

与搜索

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

其精美作品，并通过几个数量级的减少了搜索时间。

但是，位置名称可以是任何语言，包括像中国的语言，这是不空格分隔。 这个新系统是无法找到任何中国的位置，除非我要寻找的确切名称，而旧系统能找到部分名称匹配就好了。

所以，我的问题是：我能得到这个一次为所有语言，还是我在错误的轨道上？

Answer 1:

如果你想优化任意字符串匹配项，一个选择是使用的pg_tgrm模块 。 添加一个索引：

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

这将打破“简单的咖啡馆”到“SIM卡”，“小鬼”，“MPL”等，并添加到索引的条目为每行中的每个trigam。 对于子模式匹配，包括查询规划可以将自动使用这个索引：

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

这个查询将查找索引中的“咖啡馆”和“AFE”，找到交叉点，获取这些行，然后检查每一行对你的格局。 （这最后的检查是必要的，因为相交“咖啡馆”和“AFE”既“简单的咖啡馆”和“不安全的脚手架”匹配，而“网吧％％”应该只匹配一个）。 作为输入模式变得更长，因为它可以排除更多的行，但它仍然不是索引整个单词一样高效，所以不要指望在提高性能的指标变得更有效to_tsvector 。

美中不足的是，八卦不要在所有的模式，下三个字符工作。 这可能是也可能不是一个交易断路器为您的应用程序。

---

编辑：我最初加入这个注释。

我有另外的想法，昨晚，当我主要是睡着了。 使一个cjk_chars函数，它的输入字符串， regexp_matches整个CJK的Unicode范围，并返回任何这样的字符的数组或NULL如果没有。 添加在GIN指数cjk_chars(location_name) 然后查询：

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

当当，对unigram！

Answer 2:

对于在多语言环境下全文检索你需要存储的语言每个数据是沿侧文本自身。 然后，您可以使用的功能TSEARCH语言指定的口味，以获得适当的制止，等

例如，给出：

CREATE TABLE location(
    location_name text, 
    location_name_language text
);

...加上任何适当的约束，你可能会这样写：

CREATE INDEX location_name_ts_idx
USING gin(to_tsvector(location_name_language, location_name));

和搜索：

SELECT to_tsvector(location_name_language,location_name) @@ to_tsquery('english','cafe');

跨语言搜索将是有问题的，不管你做什么。 在实践中我会使用多个匹配策略：我会搜索词比较给tsvector的location_name在simple配置和文本存储的语言。 我想可能还使用了基于三元类似的做法表明willglynn，那么我会统一的结果显示，在寻找常用术语。

这是可能的，你可能会发现PG的全文搜索太有限，在这种情况下，你可能想看看像卢塞恩 / Solr的 。

见：* 控制全文搜索 。 * TSEARCH字典

Answer 3:

类似于@willglynn已经发布，我会考虑pg_trgm模块。 但优选与要旨指数：

CREATE INDEX tbl_location_name_trgm_idx
USING gist(location_name gist_trgm_ops);

该gist_trgm_ops操作类忽略大小写一般，和ILIKE是一样快LIKE 。 引用的源代码：

注意：IGNORECASE宏意味着卦是不区分大小写。

我用COLLATE "C"在这里-这实际上是没有什么特别的核对（字节顺序代替），因为你显然有不同的排序规则在你列的组合。 整理相关订货或范围，一个基本的相似性搜索，你也离不开它。 我会考虑设置COLLATE "C"为你列开始。

该指数将给予支持，查询你的第一个，简单的形式：

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

• 非常快。
• 保留能力找到部分匹配。
• 增加能力模糊搜索。
  退房%运营商和set_limit()
• GiST的指数也与查询非常快LIMIT n选择N“最好”的比赛。 您可以添加到上面的查询：

ORDER BY location_name <-> 'cafe'
LIMIT 20

了解更多关于“距离”操作符<-> 这里的手册中 。

甚至：

SELECT *
FROM   tbl
WHERE  location_name ILIKE '%cafe%'        -- exact partial match
OR     location_name %     'cafe'          -- fuzzy match
ORDER  BY 
       (location_name ILIKE 'cafe%') DESC  -- exact beginning first
      ,(location_name ILIKE '%cafe%') DESC -- exact partial match next
      ,(location_name <->   'cafe')        -- then "best" matches
      ,location_name                       -- break remaining ties (collation!)
LIMIT  20;

我用这样的事情在几个应用程序（我）满意的结果。 当然，它会在组合使用多种功能，慢一点。 寻找你的甜蜜点......

你可以走一步，并创建一个单独的部分索引的每一种语言，并使用每个匹配的归类：

CREATE INDEX location_name_trgm_idx
USING gist(location_name COLLATE "de_DE" gist_trgm_ops)
WHERE location_name_language = 'German';

-- repeat for each language

这只会是有用的， 如果你只是想每个查询特定语言的结果，并会在这种情况下，速度非常快。

文章来源: How to index a postgres table by name, when the name can be in any language?