最佳使用PostgreSQL全文搜索排名的方法

Question

在这个答案的基础上，我想知道如果我想按排名排序，并仅限于匹配查询，使用PostgreSQL内置的全文搜索的最佳方法是什么。

假设有一个非常简单的表。

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

或者其他什么。现在我想搜索title字段。首先我创建了一个索引：

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

现在我想搜索“小狗”。这个可以运行：

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

但我真正想要的是这个：

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

或者采用以下方式（不起作用 - 无法在 WHERE 子句中使用 score）：

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

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

1. 如果我使用重复的 to_tsvector(...) 版本，它会调用两次吗？还是聪明到以某种方式缓存结果？
2. 是否有一种方法可以不重复使用 to_ts... 函数调用？
3. 是否有一种方法可以在 WHERE 子句中使用 score？
4. 如果有，是否更好地通过 score > 0 进行筛选，还是使用 @@？

- Timmmm

Answer 1

使用@@操作符将利用完整的文本GIN索引，而score > 0的测试则不会。

我按照问题中的方式创建了一个表，但添加了一个名为title_tsv的列：

CREATE TABLE test_pictures (
  id        BIGSERIAL,
  title     text,
  title_tsv tsvector
);

CREATE INDEX ix_pictures_title_tsv ON test_pictures 
    USING gin(title_tsv);

我使用一些测试数据填充了表格：

INSERT INTO test_pictures(title, title_tsv)
SELECT T.data, to_tsvector(T.data) 
FROM   some_table T;

然后我使用explain analyze运行之前接受的答案：

EXPLAIN ANALYZE 
SELECT  score, id, title
FROM (
    SELECT ts_rank_cd(P.title_tsv, to_tsquery('address & shipping')) AS score
        ,P.id        
        ,P.title
    FROM test_pictures as P
) S
WHERE score > 0
ORDER BY score DESC;

并获得了以下结果。请注意执行时间为5,015毫秒

QUERY PLAN                                                                                                                                    |
----------------------------------------------------------------------------------------------------------------------------------------------|
Gather Merge  (cost=274895.48..323298.03 rows=414850 width=60) (actual time=5010.844..5011.330 rows=1477 loops=1)                             |
  Workers Planned: 2                                                                                                                          |
  Workers Launched: 2                                                                                                                         |
  ->  Sort  (cost=273895.46..274414.02 rows=207425 width=60) (actual time=4994.539..4994.555 rows=492 loops=3)                                |
        Sort Key: (ts_rank_cd(p.title_tsv, to_tsquery('address & shipping'::text))) DESC                                                      |
        Sort Method: quicksort  Memory: 131kB                                                                                                 |
        ->  Parallel Seq Scan on test_pictures p  (cost=0.00..247776.02 rows=207425 width=60) (actual time=17.672..4993.997 rows=492 loops=3) |
              Filter: (ts_rank_cd(title_tsv, to_tsquery('address & shipping'::text)) > '0'::double precision)                                 |
              Rows Removed by Filter: 497296                                                                                                  |
Planning time: 0.159 ms                                                                                                                       |
Execution time: 5015.664 ms                                                                                                                   |

现在将其与@@运算符进行比较：

EXPLAIN ANALYZE
SELECT ts_rank_cd(to_tsvector(P.title), to_tsquery('address & shipping')) AS score
    ,P.id
    ,P.title
FROM    test_pictures as P
WHERE P.title_tsv @@ to_tsquery('address & shipping')
ORDER BY score DESC;

执行时间约为 29 毫秒，结果如下：

QUERY PLAN                                                                                                                                       |
-------------------------------------------------------------------------------------------------------------------------------------------------|
Gather Merge  (cost=13884.42..14288.35 rows=3462 width=60) (actual time=26.472..26.942 rows=1477 loops=1)                                        |
  Workers Planned: 2                                                                                                                             |
  Workers Launched: 2                                                                                                                            |
  ->  Sort  (cost=12884.40..12888.73 rows=1731 width=60) (actual time=17.507..17.524 rows=492 loops=3)                                           |
        Sort Key: (ts_rank_cd(to_tsvector(title), to_tsquery('address & shipping'::text))) DESC                                                  |
        Sort Method: quicksort  Memory: 171kB                                                                                                    |
        ->  Parallel Bitmap Heap Scan on test_pictures p  (cost=72.45..12791.29 rows=1731 width=60) (actual time=1.781..17.268 rows=492 loops=3) |
              Recheck Cond: (title_tsv @@ to_tsquery('address & shipping'::text))                                                                |
              Heap Blocks: exact=625                                                                                                             |
              ->  Bitmap Index Scan on ix_pictures_title_tsv  (cost=0.00..71.41 rows=4155 width=0) (actual time=3.765..3.765 rows=1477 loops=1)  |
                    Index Cond: (title_tsv @@ to_tsquery('address & shipping'::text))                                                            |
Planning time: 0.214 ms                                                                                                                          |
Execution time: 28.995 ms                                                                                                                        |

从执行计划中可以看出，在第二个查询中使用了索引ix_pictures_title_tsv，而在第一个查询中没有使用该索引，这使得使用@@运算符的查询快了整整172倍！

- isapir

Answer 2

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

- Clodoaldo Neto

---

此查询未使用索引。此外，to_tsquery('small dog') 会抛出一个错误。您可能想使用 to_tsquery('small & dog')。 - isapir

Answer 3

如果我使用重复的to_tsvector(...)版本，它会调用两次吗？还是聪明到某种程度可以缓存结果呢？

最好的方法是通过简单的解释来注意这些事情，尽管有时可能很难阅读。

长话短说，是的，PostgreSQL是足够聪明的，可以重复使用计算结果。

有没有一种方法可以在不重复调用to_ts...函数的情况下进行操作？

我通常做的是添加一个tsv列，它是文本搜索向量。如果你使用触发器使其自动更新，它立即为你提供了轻松访问的向量，但它也允许你通过使触发器选择性地更新搜索索引来进行选择性更新。

有办法在WHERE子句中使用分数吗？

是的，但不是用那个名字。 或者你可以创建一个子查询，但我个人会选择重复它。

如果有的话，使用score > 0过滤还是使用@@更好？

我能想到的最简单的版本是：

SELECT *
FROM pictures
WHERE 'small dog' @@ text_search_vector

text_search_vector可以显然被替换为类似于to_tsvector('english', pictures.title)的内容。

- Wolph

---

这个回答忽略了问题中似乎最重要的部分：“排名”。 - steviesh