将以前缀开头的短语与全文搜索相匹配

Question

我正在寻找一种方法来使用 PostgreSQL 中的 tsvector 来模拟类似 SELECT * FROM table WHERE attr LIKE '%text%' 的方法。

我在不使用字典的情况下创建了 tsvector 属性。现在，像 ... ... 这样的查询

SELECT title
FROM table
WHERE title_tsv @@ plainto_tsquery('ph:*');  

将返回所有标题，例如“Physics”、“PHP”等。但是我如何创建一个查询来返回标题以“Zend Fram”开头的所有记录（应该返回例如“Zend Framework”）？

当然，我可以使用类似：

SELECT title
FROM table
WHERE title_tsv @@ to_tsquery('zend')
AND   title_tsv @@ to_tsquery('fram:*');

但是，这似乎有点尴尬。

所以，问题是：有没有一种方法可以使用以下内容来制定上面给出的查询：

SELECT title
FROM table
WHERE title_tsv @@ to_tsquery('zend fram:*');

Answer 1

SELECT title
FROM table
WHERE title_tsv @@ to_tsquery('zend') and
title_tsv @@ to_tsquery('fram:*')  

相当于：

SELECT title
FROM table
WHERE title_tsv @@ to_tsquery('zend & fram:*')

但当然也发现“Zend 没有框架”。

当然，您可以在 tsquery 匹配之后表达与标题的正则表达式匹配，但是您必须使用解释分析来确保在 tsquery 之后而不是之前执行。

Answer 2

Postgres 9.6 引入了全文搜索的短语搜索功能。所以现在可以了：

SELECT title
FROM  tbl
WHERE title_tsv @@ to_tsquery('zend <-> fram:*');

<-> ; 是 FOLLOWED BY 运算符。

它找到 'foo Zend Framework bar' 或 'Zendframes'，但找不到 'foo Zend 没有框架 bar'。

引用 Postgres 9.6 的发行说明：

可以使用新的在 tsquery 输入中指定短语搜索查询
运算符 <-> 和 <N>。前者意味着和之前的词位
之后它必须按该顺序彼此相邻出现。后者
意味着它们必须完全N个词位分开。

为了获得最佳性能，支持使用 GIN 索引的查询：

CREATE INDEX tbl_title_tsv_idx ON tbl USING GIN (title_tsv);

或者根本不将 title_tsv 存储在表中（使其膨胀并使写入复杂化）。您可以改用表达式索引：

CREATE INDEX tbl_title_tsv_idx ON tbl USING GIN (to_tsvector('english', title));

您需要指定文本搜索配置（通常特定于语言）以使表达式不可变。并相应地调整查询：

...
WHERE to_tsvector('english', title) @@ to_tsquery('english', 'zend <-> fram:*');

Answer 3

这不是一个漂亮的解决方案，但它应该可以完成工作：

psql=# SELECT regexp_replace(cast(plainto_tsquery('Zend Fram') as text), E'(\'\\w+\')', E'\\1:*', 'g') ;
   regexp_replace    
---------------------
 'zend':* & 'fram':*
(1 row)

它可以像这样使用：

psql=# SELECT title FROM table WHERE title_tsv(title) @@ to_tsquery(regexp_replace(cast(plainto_tsquery('Zend Fram') as text), E'(\'\\w+\')', E'\\1:*', 'g'));

如何工作：

1. 将纯 tsquery 转换为字符串： cast(plainto_tsquery('Zend Fram') as text)
2. 使用正则表达式将 :* 前缀匹配器附加到每个搜索词：regexp_replace(..., E'(\'\\w+\')', E'\\1:*' , 'g')
3. 将其转换回非普通 tsquery。 to_tsquery(...)
4. 并在搜索表达式中使用它 SELECT title FROM table WHERE title_tsv(title) @@ ...

Answer 4

有一种方法可以在 Postgres 中使用 trigrams 和 Gin/Gist 索引来做到这一点。 Kristo Kaiv 的这篇文章中有一个简单的示例，但有一些粗糙的地方： 子字符串搜索。