postgresql - 如何在 postgres 中结合全文搜索和三元组

Question

我正在为 git 提交数据库开发一个搜索系统。我目前正在使用全文搜索，使用户能够按作者、提交日期、日志消息和提交哈希进行搜索。目前，提交哈希仅在用户提供整个提交哈希时才有用，整个提交哈希又长又难记，但对于指定单个提交很有用。

查询数据库的query本质上是这样的:

SELECT
    cid,
    (ts_rank(tsv, q) + ts_rank_cd(tsv, q)) AS rank
FROM
    search,
    plainto_tsquery(%(query)s) AS q
WHERE
    (tsv @@ q);

其中 cid 是提交哈希，tsv 是每次提交的相关信息的文本搜索向量。

我的目标是允许用户在他们的查询中只提供一部分提交散列，并提供基本上来自他们输入的所有提交。

我已经研究过 trigrams，它看起来最有前途，但我不完全确定如何将它们集成到这个查询中。

Answer 1

1:创建 tsvectors 的列/ View /物化 View 。

CREATE MATERIALIZED VIEW unique_lexeme AS
SELECT word FROM ts_stat(
'SELECT to_tsvector('simple', post.title) || 
    to_tsvector('simple', post.content) ||
    to_tsvector('simple', author.name) ||
    to_tsvector('simple', coalesce(string_agg(tag.name, ' ')))
FROM post
JOIN author ON author.id = post.author_id
JOIN posts_tags ON posts_tags.post_id = posts_tags.tag_id
JOIN tag ON tag.id = posts_tags.tag_id
GROUP BY post.id, author.id');

2:使用三元组从该列中选择

SELECT word
FROM unique_lexeme
WHERE similarity(word, 'samething') > 0.5 
ORDER BY word <-> 'samething';

(在此站点中搜索:拼写错误 http://rachbelaid.com/postgres-full-text-search-is-good-enough/ )

3:找到单词后，使用它们对结果进行排名。使用子查询:

选择单词 WHERE 相似度(单词，'samething')> 0.5 按单词排序 <-> 'samething';

或者，您可以只创建一个子查询来检查相似性。

添加:

索引 tsvector 列。

同时刷新物化 View ( http://www.postgresqltutorial.com/postgresql-materialized-views/ )。

使用触发器更新列 ( https://www.postgresql.org/docs/9.0/textsearch-features.html )