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

  1. PostgreSQL, полнотекстовый поиск, ранжирование, JSON, или MongoDB не нужна
    1. JSON и полнотекстовыый поиск
      1. Подтоговка системы
      2. Создаем таблицу и заполняем данными
      3. GIN-индекс
    2. Поиск по документу
      1. Проблема
    3. rum, или ранжированный полнотекстовый поиск за миллисекунды
      1. Подготовка системы
    4. Если вам и этого мало
    5. Отдельное спасибо

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

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

А еще я на это руководство 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 postgres
postgres=# 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 звезд на гитхабе (девятнадцатая — моя), в репозитариях вы его не найдете. Придется руками собирать. Это несложно. Сначала ставим хидеры:

# 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, чтобы особо криворукие пользователи тоже могли пользоваться поиском на вашем сайтике.

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