postgresql - 如何在 PostgreSQL 中设置全文搜索查询

Question

我是 PostgreSQL 的新手，在实现全文搜索时遇到了一些问题。我目前正在使用以下设置:

CREATE DATABASE test;

CREATE TABLE data_table (
   id BIGSERIAL PRIMARY KEY,
   name VARCHAR(160) NOT NULL,
   description VARCHAR NOT NULL
);

CREATE INDEX data_table_idx ON data_table 
USING gin(to_tsvector('English', name || ' ' || description)); 

INSERT INTO data_table (name, description) VALUES 
    ('Penguin', 'This is the Linux penguin.'), 
    ('Gnu', 'This is the GNU gnu.'), 
    ('Elephant', 'This is the PHP elephant.'), 
    ('Elephant', 'This is the postgres elephant.'), 
    ('Duck', 'This is the duckduckgo duck.'), 
    ('Cat', 'This is the GitHub cat.'), 
    ('Bird', 'This is the Twitter bird.'), 
    ('Lion', 'This is the Leo lion.');

现在我尝试在表中搜索给定的用户输入并返回整个数据行和突出显示的匹配项，它看起来应该类似于以下内容:

WITH 
    q AS ( SELECT plainto_tsquery('English', 'elephants php') AS query ),
    d AS ( SELECT (name || ' ' || description) AS document FROM data_table ),
    t AS ( SELECT to_tsvector('English', d.document) AS textsearch FROM d ),
    r AS ( SELECT ts_rank_cd(t.textsearch, q.query) AS rank FROM t, q )
SELECT data_table.*, ts_headline('german', d.document, q.query) AS matches
FROM data_table, q, d, t , r
WHERE q.query @@ t.textsearch 
ORDER BY r.rank DESC 
LIMIT 10;

这给我留下了以下输出:

 id |   name   |          description           |              matches               
----+----------+--------------------------------+------------------------------------
  5 | duck     | This is the duckduckgo duck.   | Penguin This is the Linux penguin.
  2 | Gnu      | This is the GNU gnu.           | Gnu This is the GNU gnu.
  3 | Elephant | This is the PHP elephant.      | Penguin This is the Linux penguin.
  4 | elephant | This is the postgres elephant. | Penguin This is the Linux penguin.
  6 | Cat      | This is the GitHub cat.        | Penguin This is the Linux penguin.
  1 | Penguin  | This is the Linux penguin.     | Gnu This is the GNU gnu.
  1 | Penguin  | This is the Linux penguin.     | Penguin This is the Linux penguin.
  2 | Gnu      | This is the GNU gnu.           | Penguin This is the Linux penguin.
  4 | elephant | This is the postgres elephant. | Gnu This is the GNU gnu.
  3 | Elephant | This is the PHP elephant.      | Gnu This is the GNU gnu.
(10 rows)

所以查询确实返回了一些东西，但它没有按排名排序，每个文档都与名称/描述的每个组合组合在一起，唯一有效的是在文档中正确突出显示搜索结果。那么我做错了什么，我该如何解决？

Answer 1

我终于能够让它工作了。请在下面找到我的解决方案。我希望这会对某人有所帮助。如果有人知道更好的解决方案，索引更好/更快，我会很高兴知道。

查询:

WITH 
    q AS ( SELECT to_tsquery('german', 'elephant | php') AS query ),
    d AS ( SELECT id, (name || ' ' || description) AS doc FROM data_table ),
    t AS ( SELECT id, doc, to_tsvector('german', doc) AS vector FROM d ),
    r AS ( 
        SELECT id, doc, ts_rank_cd(vector, query) AS rank 
        FROM t, q
        WHERE q.query @@ vector
        ORDER BY rank DESC 
    )
SELECT id, ts_headline('german', doc, q.query) AS matches, rank
FROM r, q
ORDER BY r;

结果:

 id |                         matches                         | rank 
----+---------------------------------------------------------+------
  3 | <b>Elephant</b> This is the <b>PHP</b> <b>elephant</b>. |  0.3
  4 | <b>elephant</b> This is the postgres <b>elephant</b>.   |  0.2