Лучший способ использования полнотекстового поиска PostgreSQL

Следуя этому ответу, я хочу знать, как лучше всего использовать встроенный полнотекстовый поиск PostgreSQL, если я хочу сортировать по рангу и ограничивать только соответствующие запросы.

Предположим, что очень простая таблица.

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

или что-то еще. Теперь я хочу найти поле title . Сначала я создаю индекс:

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

Теперь я хочу найти 'small dog' . Это работает:

 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 

Или, альтернативно, это (что не работает – не может использовать score в WHERE ):

 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... функции to_ts... ?
  3. Есть ли способ использовать score в WHERE ?
  4. Если есть, было бы лучше фильтровать по score > 0 или использовать @@ вещь?

 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 

Если я использую версию с повторяющимся to_tsvector (…), она будет вызывать это дважды, или она достаточно умна, чтобы каким-то образом кэшировать результаты?

Лучший способ заметить эти вещи – это просто объяснить, хотя их трудно читать.

Короче говоря, да, PostgreSQL достаточно умен, чтобы повторно использовать вычисленные результаты.

Есть ли способ сделать это, не повторяя вызовы функции to_ts …?

Обычно я добавляю столбец tsv который является текстовым поисковым вектором. Если вы сделаете это автоматическое обновление с помощью триггеров, он сразу же даст вам вектор, который легко доступен, но также позволяет выборочно обновлять индекс поиска, делая триггер выборочным.

Есть ли способ использовать оценку в предложении WHERE?

Да, но не с этим именем. В качестве альтернативы вы можете создать подзапрос, но я бы просто повторил его.

Если есть, было бы лучше фильтровать по счету> 0 или использовать @ @ вещь?

Простейшая версия, о которой я могу думать, такова:

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

Очевидно, что to_tsvector('english', pictures.title) можно заменить чем-то вроде to_tsvector('english', pictures.title)