Как настроить запрос полнотекстового поиска в 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('german', 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('german', 'elephants php') AS query ),
    d AS ( SELECT (name || ' ' || description) AS document FROM data_table ),
    t AS ( SELECT to_tsvector('german', 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