PostgreSQL, полнотекстовый поиск, ранжирование, JSON, или MongoDB не нужна

PostgreSQL, полнотекстовый поиск, ранжирование, JSON, или MongoDB не нужна

2016 год на дворе. Разработчики чем только не обмажутся, лишь бы заставить на сайте поле «поиск» работать. Кто эластиксерчем, кто монгой. Кто вообще SaaS заюзает — не, я серьезно.

А проблема решается очень просто. Причем данные останутся в базе данных, а не в поделке сбоку. Их не надо будет обновлять руками, не будет никаких лагов и рассинхронов. Зато будет транзакционная целостность и прочие ништяки.

А еще я на это руководство JSON накатил. Чтобы любители скималесс-решений не чувствовали себя ущербными при виде колоночек.

JSON и полнотекстовыый поиск

Подтоговка системы

Давайте я вам сразу объясню, откуда брать постгрес свежее позапрошлогоднего. Вы идете на страницу https://www.postgresql.org/download/linux/, выбираете свой дистрибутив и следуете инструкциям.

В моем случае (Debian 8) мне надо набрать следующие волшебные слова:

# echo 'deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main' > /etc/apt/sources.list.d/pgdg.list
# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
# apt-get update
# apt-get install postgresql-9.6

Ничего сложного. Сложное начнется сейчас. На этом этапе слились тысячи сегодняшних фанатов NoSQL баз данных. Создаем юзера:

# su -c psql postgrespostgres=# CREATE ROLE komar WITH CREATEDB LOGIN;

Только вместо «komar» подставляем своего юниксового пользователя.

Теперь из-под него создаем базу данных:

$ createdb fts

Вот и все, можно развлекаться. Для доступа к базе данных надо набрать psql fts:

Создаем таблицу и заполняем данными

Создаем максимально скималесс-таблицу — с одной колонкой json типа jsonb:

$ psql fts


fts=> CREATE TABLE ads (json jsonb);

И чтобы нам было весело — заполняем ее 500 000 объявлений с авиты:

$ wget http://komar.in/files/ads.pgdump
$ pg_restore -d fts --data-only < ads.pgdump

Посмотреть, как выглядит случайное объявление, можно с помощью такой хитрой команды:

$ psql fts --no-align --tuples-only --record-separator=' ' -c 'SELECT json FROM ads OFFSET round(random() * 100) LIMIT 1' | jq --color-output '.' | less -R

Но она требует установленного jq. Наберите apt-get install jq, если у вас его до сих пор нет.

Можно развлекаться всякой бигдатой. Вот так, например, можно посмотреть, с каких номеров больше всего спама на авите:


fts=> SELECT json->>'phone', count(*) FROM ads GROUP BY json->>'phone' ORDER BY count DESC;

GIN-индекс

Какие-то калмыцкие астрономы сделали для постгреса индекс, пригодный для полнотекстового поиска. Но просто так применять его к тексту мы не будем.

Человеческие языки содержат окончания, предлоги и прочую хренотень, которая мешает жить программисту. Функция to_tsvector все это вырезает и превращает текст в набор токенов, по которому искать намного интереснее:


fts=> SELECT to_tsvector('russian', 'Съешь еще этих мягких французских булок, да выпей чаю');
                           to_tsvector
-----------------------------------------------------------------
 'булок':6 'вып':8 'мягк':4 'съеш':1 'французск':5 'ча':9 'эт':3

Аналогично работает функция plainto_tsquery, только она готовит запрос, а не текст. Затем с помощью оператора @@ можно поматчить одно с другим:


SELECT to_tsvector('russian', 'Съешь еще этих мягких французских булок, да выпей чаю') @@ plainto_tsquery('russian', 'съешь чаю');
 ?column?
----------
 t

Здесь t значит true, успех, все нашлось.

Разумно бы было сделать отдельную колонку и свалить туда эти токены, а по ним уже строить индекс. Но тогда понадобится при каждом обновлении исходного текста руками обновлять колонку с токенами, в общем, привет, эластиксерч. На хрен оно надо. Вместо этого сделаем функциональный индекс:


fts=> CREATE INDEX index_on_ads_title_using_gin
ON ads
USING gin
(to_tsvector('russian', json->>'title'));

Теперь врубаем тайминги и развлекаемся:


fts=> \timing
Timing is on.
fts=> SELECT json->>'phone', json->>'title'
FROM ads
WHERE to_tsvector('russian', json->>'title') @@ plainto_tsquery('russian', 'щенка в добрые руки') LIMIT 3;
    ?column?     |              ?column?
-----------------+------------------------------------
 8 951 17X-XX-X8 | Отдам щенков в добрые руки
 8 950 11X-XX-X1 | В добрые руки щенки такса-спаниель
 8 905 85X-XX-X0 | Отдам щенка в добрые руки
(3 rows)

Time: 1.882 ms

Это прекрасно.

Без LIMIT будет тоже работать быстро — это я просто для листинга добавил.

Поиск по документу

В мире full text search есть такое понятие как «документ». Если мы пишем бложик, то документом, по которому проводится поиск, может быть сочетание заголовка, текста, тегов, комментариев, короче — нескольких полей.

Мы тоже так умеем. Для этого берем поля title и description, назначаем первому вес A, а второму — B, конкатенируем полученные токены при помощи оператора || и делаем на основе этого всего еще один функциональный индекс:


fts=> CREATE INDEX index_ads_on_json_document_using_gin
ON ads
USING gin
(
  (
    setweight(to_tsvector('russian', json->>'title'), 'A') ||
    setweight(to_tsvector('russian', coalesce(json->>'description', '')), 'B')
  )
);

Пробуем:


fts=> SELECT json->>'title', left(json->>'description', 40)
FROM ads
WHERE
  (
    setweight(to_tsvector('russian', json->>'title'), 'A') ||
    setweight(to_tsvector('russian', coalesce(json->>'description', '')), 'B')
  ) @@ plainto_tsquery('russian', 'отдам щенка')
LIMIT 3;
          ?column?           |                   left
-----------------------------+------------------------------------------
 Щенки                       | Отдам хороших крепких щенков овчарки на 
 В добрые руки               | отдам в добрые руки, щенка той терьера! 
 Отдам в хорошие руки щенков | Зимой 2017 года ожидаются щенки от суки 
(3 rows)

Time: 2.538 ms

Как видите, ищет теперь не только по заголовку, но и по тексту объявления.

Проблема

Только что мы назначили веса нашим колонкам. Очевидно, что сделали мы это для того, чтобы сортировать результаты по релевантности. Ну давайте попробуем:


fts=> SELECT
  ts_rank(
    (
      setweight(to_tsvector('russian', json->>'title'), 'A') ||
      setweight(to_tsvector('russian', coalesce(json->>'description', '')), 'B')
    ), plainto_tsquery('russian', 'отдам щенка')
  ),
  json->>'title',
  left(json->>'description', 50)
FROM ads
WHERE
  (
    setweight(to_tsvector('russian', json->>'title'), 'A') ||
    setweight(to_tsvector('russian', coalesce(json->>'description', '')), 'B')
  ) @@ plainto_tsquery('russian', 'отдам щенка')
ORDER BY ts_rank DESC
LIMIT 3;
 ts_rank  |   ?column?   |                        left
----------+--------------+----------------------------------------------------
 0.999898 | Отдам щенка  | Отдам щенка бесплатно. Порода метис, мама шар-пей,
 0.999745 | Отдам щенков | Отдам щенков в хорошие руки. Щенки от умных собак.
 0.999569 | Отдам щенков | Отдам щенков, мама умная преданная собака, хороший
(3 rows)

Time: 47.889 ms

Впечатляет? Да что-то не очень. 50 миллисекунд — это уже не «моментально». А если у нас результатов будет больше? Давайте поищем квартиры — ими вся авита с подачи риелторов засрана.


fts=> SELECT
  ts_rank(
    (
      setweight(to_tsvector('russian', json->>'title'), 'A') ||
      setweight(to_tsvector('russian', coalesce(json->>'description', '')), 'B')
    ), plainto_tsquery('russian', 'квартира')
  ),
  json->>'title',
  left(json->>'description', 35)
FROM ads
WHERE
  (
    setweight(to_tsvector('russian', json->>'title'), 'A') ||
    setweight(to_tsvector('russian', coalesce(json->>'description', '')), 'B')
  ) @@ plainto_tsquery('russian', 'квартира')
ORDER BY ts_rank DESC
LIMIT 3;
 ts_rank  |            ?column?             |                left
----------+---------------------------------+-------------------------------------
 0.760803 | 1-к квартира, 45.9 м², 9/20 эт. | ..  Примечание:  квартира расположе
  0.75441 | 2-к квартира, 80 м², 15/19 эт.  | Эксклюзивно в компании Винсент-Недв
  0.75441 | 1-к квартира, 32 м², 2/10 эт.   | Приглашаем вас поселиться у нас в н
(3 rows)

Time: 8645.818 ms

Пиздец.

Все дело в том, что LIMIT здесь не помогает. Постгрес не знает rank’а заранее: он выбирает все результаты (их 28 199) и для каждого считает этот rank, а уже потом сортирует.

Мы получим прирост в производительности, если перестанем выпендриваться и сделаем отдельную колонку для tsvector’а. Но это ведь не интересно...

rum, или ранжированный полнотекстовый поиск за миллисекунды

Специально для такого случая калмыцкие астрономы сняли офис на Автозаводской с чудовищно низкими потолками, наняли туда сишников и выкатили rum. Это еще один индекс, как он работает — никто не знает, но он такой, реальный.

Подготовка системы

Так как у этого экстеншона всего 19 звезд на гитхабе (девятнадцатая — моя), в репозитариях вы его не найдете. Придется руками собирать.

RUM теперь есть в репах:

# apt-get install postgresql-9.6-rum

Но можно и руками собрать. Это несложно. Сначала ставим хидеры:

# apt-get install postgresql-server-dev-9.6

Затем сделаем по инструкции из README.md:

$ git clone https://github.com/postgrespro/rum
$ cd rum
$ make USE_PGXS=1
$ sudo make USE_PGXS=1 install

При попытке подключить этот экстеншон от обычного пользователя постгрес пошлет вас куда подальше, поэтому подключаем от имени postgres:

$ sudo -u postgres psql fts -c 'CREATE EXTENSION rum;'

Теперь делаем все то же самое, только вместо gin подставляем rum, да еще и дописываем rum_tsvector_ops:


fts=> CREATE INDEX index_ads_on_json_document_using_rum
  ON ads
  USING rum
  (
    (
      setweight(to_tsvector('russian', json->>'title'), 'A') ||
      setweight(to_tsvector('russian', coalesce(json->>'description', '')), 'B')
    )
    rum_tsvector_ops
  );

И вместо ORDER BY ts_rank используем хитрый оператор <=>:


fts=> SELECT
  (
    setweight(to_tsvector('russian', json->>'title'), 'A') ||
    setweight(to_tsvector('russian', coalesce(json->>'description', '')), 'B')
  ) <=> to_tsquery('russian', 'квартира') AS rank,
  json->>'title',
  left(json->>'description',35)
FROM ads
WHERE
  (
    setweight(to_tsvector('russian', json->>'title'), 'A') ||
    setweight(to_tsvector('russian', coalesce(json->>'description', '')), 'B')
  ) @@ to_tsquery('russian', 'квартира')
ORDER BY rank
LIMIT 3;
 ts_rank  |            ?column?             |                left
----------+---------------------------------+-------------------------------------
 0.760803 | 1-к квартира, 45.9 м², 9/20 эт. | ..  Примечание:  квартира расположе
  0.75441 | 2-к квартира, 80 м², 15/19 эт.  | Эксклюзивно в компании Винсент-Недв
  0.75441 | 1-к квартира, 32 м², 2/10 эт.   | Приглашаем вас поселиться у нас в н
(3 rows)

Time: 46.810 ms

Живем. Теперь можно гугол свой открывать.

Нет, для гугла этого, конечно, не хватит. Хотя кто-то пытался. Но на средний стартапик хватит с головой.

Если вам и этого мало

Калмыцкие астрономы также сделали расширение pg_trgm, чтобы особо криворукие пользователи тоже могли пользоваться поиском на вашем сайтике.

Отдельное спасибо

Неладному

Смотрите также

Быстрое агрегирование в PostgreSQL