Как сделать более весомее колонки в PostreSQL при поиске?

Question

[rraderio]

В Elasticsearch я могу сделать так

GET /_search
{
  "query": {
    "multi_match" : {
      "query" : "this is a test",
      "fields" : [ "subject^3", "message" ] 
    }
  }
}

Мы ищем "this is a test" в 2-х полях/колонках: subject, message. Поле subject в три раза более весомее чем поле message.

Как добиться того же с PostgreSQL?

Answer 1

[Dmitry MiksIr]

Вы, надеюсь, про FTS в PostgreSQL? Тогда, скорее всего ответ содержится тут https://www.postgresql.org/docs/9.5/static/textsea...

Создание индекса

UPDATE  table SET ftsindex = 
setweight(to_tsvector(coalesce(subject,'')), 'A') ||
setweight(to_tsvector(coalesce(message,'')), 'D');

При создания индекса разным полям можно назначить условный вес, от A до D.
При запросе, условному весу можно указать реальный

SELECT id, ts_rank_cd({1.0, 1.0, 1.0, 3.0}, ftsindex, query) AS rank
FROM table, to_tsquery('this is a test') query
WHERE query @@ ftsindex
ORDER BY rank DESC
LIMIT 10;

Comments

[un1t]

Может и содержится, но только ничего не понятно. Можешь пример привести?

[Dmitry MiksIr]

У вас один FTS индекс на два поля или отдельные FTS индексы на каждое поле? Вес нужен постоянно (можно задать при создании индекса) или в разных запросах разный вес?

[un1t]

Dmitry MiksIr: ну вообще я не топикстартер. Однако раз тут сравнение с эластиком, в эластике можно указывать вес под каждый запрос. Хотя если запрос от этого сильно упростится, можно предположить, что вес постоянный. Про один или несколько индексов - вероятно, что индексов сейчас никаких нет, как лучше?

[Dmitry MiksIr]

Меня вот тоже смутило отутствие упоминания индексов про postgresql, по-этому гадать не стал. Вес можно задавать при запросе, это я что-то ступил. Сейчас дам пример в ответе.

[un1t]

"UPDATE table SET ftsindex". Т.е. надо создать у таблицы специальное поле типа tsvector и засунуть туда значение полученное как указано выше? А на это поле потом надо еще индекс создать? И получается в отличии от простых индексов это все надо вручную обновлять при добавлении/изменении записей?

[Dmitry MiksIr]

Можно обойтись без отдельной колонки создав expression index. Такой вариант проще, сам обновляется, занимает меньше места на диске, но при запросах на поиск нужно указывать полностью выражение создания tsvector, чтобы этот индекс был найден. Тут вот расписано достаточно понятно https://www.postgresql.org/docs/9.5/static/textsea...

Answer 2

[freeExec]

SELECT * FROM
(SELECT id, 3 AS rank FROM mail WHERE subject like '%query%'
UNION ALL
SELECT id, 1 AS rank FROM mail WHERE message like '%query%') AS t
ORDER BY rank DESC

Comments

[un1t]

like для поиска? вы серьезно?

[freeExec]

un1t: А что like не для поиска?

[un1t]

freeExec: ну вопрос очевидно про полнотестовый поиск. Like с этой задачей справляется чуть хуже чем никак.