使用PostgreSQL全文搜索排名的最佳方法 [英] Best way to use PostgreSQL full text search ranking

问题描述

继续这个答案我想知道使用PostgreSQL的内置全文搜索的最佳方式是如果我想按排名排序，和限制为只匹配查询。

让我们假设一个非常简单的表。

  CREATE TABLE图片{
 id序列主键，
 title varchar（300），
 ... 
} 
  

或其他。现在我要搜索标题字段。首先我创建一个索引：

  CREATE INDEX pictures_title在图片上使用杜松子酒（to_tsvector（'english'，title））; 
  

现在我要搜索'small dog'。这样做：

  SELECT pictures.id，ts_rank_cd（to_tsvector（'english'，pictures.title），'small dog'） AS分数
来自图片
 ORDER BY得分DESC 
  

但是我真正想要的这是：

 选择图片.id，ts_rank_cd（to_tsvector（'english'，pictures.title），to_tsquery（'小狗'））AS分数
从图片WHERE to_tsvector（'english'，pictures.title）@@ to_tsquery（'small dog'）
 ORDER BY得分DESC 
  

或者，这可能不起作用 - 无法在<$ code>分数中使用 WHERE 子句）：

  SELECT pictures.id，ts_rank_cd（to_tsvector（'english '，pictures.title），to_tsquery（'小狗'））AS分数
从图片WHERE得分> 0 
 ORDER BY得分DESC 
  

最好的方法是什么？我的问题有很多：

1. 如果我使用重复 to_tsvector（...）它会调用两次，还是足够聪明，以缓存结果？


2. 有没有办法做到这一点，而不重复 to_ts ... 函数调用？


3. 有没有办法在<$ c $中使用 score c> WHERE 子句？


4. 如果存在，最好过滤 score> 0 或使用 @@ 的东西？

SELECT
pictures.id，
ts_rank_cd（to_tsvector（''））解决方案

  select *英语'，pictures.title），
 to_tsquery（'小狗'））AS分数
来自图片
）s 
 WHERE得分> 0 
 ORDER BY得分DESC 
  

Following on from this answer I want to know what the best way to use PostgreSQL's built-in full text search is if I want to sort by rank, and limit to only matching queries.

Let's assume a very simple table.

CREATE TABLE pictures {
  id SERIAL PRIMARY KEY,
  title varchar(300),
  ...
}

or whatever. Now I want to search the title field. First I create an index:

CREATE INDEX pictures_title ON pictures USING gin(to_tsvector('english', title));

Now I want to search for 'small dog'. This works:

SELECT pictures.id, ts_rank_cd(to_tsvector('english', pictures.title), 'small dog') AS score
FROM pictures
ORDER BY score DESC

But what I really want is this:

SELECT pictures.id, ts_rank_cd(to_tsvector('english', pictures.title), to_tsquery('small dog')) AS score
FROM pictures WHERE to_tsvector('english', pictures.title) @@ to_tsquery('small dog')
ORDER BY score DESC

Or alternatively this (which doesn't work - can't use score in the WHERE clause):

SELECT pictures.id, ts_rank_cd(to_tsvector('english', pictures.title), to_tsquery('small dog')) AS score
FROM pictures WHERE score > 0
ORDER BY score DESC

What's the best way to do this? My questions are many-fold:

1. If I use the version with repeated to_tsvector(...) will it call that twice, or is it smart enough to cache the results somehow?
2. Is there a way to do it without repeating the to_ts... function calls?
3. Is there a way to use score in the WHERE clause at all?
4. If there is, would it be better to filter by score > 0 or use the @@ thing?

解决方案

select *
from (
    SELECT
        pictures.id,
        ts_rank_cd(to_tsvector('english', pictures.title), 
        to_tsquery('small dog')) AS score
    FROM pictures
) s
WHERE score > 0
ORDER BY score DESC

这篇关于使用PostgreSQL全文搜索排名的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！