UPD. Часть 2

Эта статья — первая из небольшой серии статей о том, как оптимально настроить полнотекстовый поиск в PostgreSQL. Мне пришлось недавно решать подобную задачу на работе — и я был очень удивлен отсутствию хоть сколько-нибудь вменяемых материалов по этому поводу. Мой опыт борьбы под катом.

Завязка

Я поддерживаю относительно большой проект, в котором есть публичный поиск по документам. В базе лежит ~500 тысяч документов общим объемом ~3,6 Гб. Суть поиска такова: пользователь заполняет форму, в которой есть и полнотекстовый запрос, и фильтрация по множеству полей в БД, в том числе и с join-ами.

Поиск работает (точнее, работал) через Sphinx, и работал не очень хорошо. Основные проблемы были такими:

1. Индексирование отъедало порядка 8 Гб оперативной памяти. На сервере с 8 Гб ОЗУ это проблема. Память свопилась, это приводило к ужасной производительности.
2. Индекс строился примерно 40 минут. Ни о какой консистентности поисковых результатов речи не шло, индексирование запускалось раз в день.
3. Поиск работал долго. Особенно долго осуществлялись запросы, которым соответствовало большое количество документов: огромное количестов id-шников приходилось передавать из сфинкса в базу, и сортировать по релевантности на бэкэнде.

Из-за этих проблем возникла задача — оптимизировать полнотекстовый поиск. У этой задачи есть два решения:

1. Подтюнить Sphinx: настроить realtime-индекс, хранить в индексе атрибуты для фильтрации.
2. Использовать встроенный FTS PostgreSQL.

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

Казалось бы, хорошее решение. Но проблемы поджидали впереди.

Начнем с самого начала.

Наивно используем полнотекстовый поиск

Как гласит документация, для полнотекстового поиска требуется использовать типы tsvector и tsquery. Первый хранит текст документа в оптимизированном для поиска виде, второй — хранит полнотекстовый запрос.

Для поиска в PostgreSQL есть функции to_tsvector, plainto_tsquery, to_tsquery. Для ранжирования результатов есть ts_rank. Их использование интуитивно понятно и они хорошо описаны в документации, поэтому на подробностях их использования останавливаться не будем.

Традиционный поисковый запрос с помощью них будет выглядеть так:

SELECT id, ts_rank(to_tsvector("document_text"), plainto_tsquery('запрос'))
FROM documents_document
WHERE to_tsvector("document_text") @@ plainto_tsquery('запрос')
ORDER BY ts_rank(to_tsvector("document_text"), plainto_tsquery('запрос')) DESC;

Мы вывели id-ы документов, в тексте которых есть слово "запрос", и отсортировали их по убыванию релевантности. Кажется, всё хорошо? Нет.

У подхода выше есть много недостатков:

1. Мы не используем индекс для поиска.
2. Функция ts_vector вызывается для каждой строки таблицы.
3. Функция ts_rank вызывается для каждой строки таблицы.

Это все приводит к тому, что поиск выполняется реально долго. Результаты EXPLAIN на боевой базе:

Gather Merge (actual time=420289.477..420313.969 rows=58742 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Sort (actual time=420266.150..420267.935 rows=19581 loops=3)
        Sort Key: (ts_rank(to_tsvector(document_text), plainto_tsquery('запрос'::text))) DESC
        Sort Method: quicksort  Memory: 2278kB
        ->  Parallel Seq Scan on documents_document (actual time=65.454..420235.446 rows=19581 loops=3)
              Filter: (to_tsvector(document_text) @@ plainto_tsquery('запрос'::text))
              Rows Removed by Filter: 140636
Planning time: 3.706 ms
Execution time: 420315.895 ms

420 секунд! На один запрос!

Ещё база генерит множество ворнингов вида [54000] word is too long to be indexed. В этом ничего страшного нет. Причина в том, что в моей базе лежат документы, созданные в WYSIWYG-редакторе. Он вставляет множество   всюду, где можно, и их бывает по 54 тысячи штук подряд. Postgres слова такой длины игнорирует и пишет ворнинг, который нельзя отключить.

Попробуем исправить все замеченные проблемы и ускорить поиск.

Наивно оптимизируем поиск

Играться с боевой базой мы не будем, конечно — создадим тестовую базу. В ней ~12 тысяч документов. Запрос из примера там выполняется ~35 секунд. Непростительно долго!

Sort (actual time=35431.874..35432.208 rows=3593 loops=1)
  Sort Key: (ts_rank(to_tsvector(document_text), plainto_tsquery('запрос'::text))) DESC
  Sort Method: quicksort  Memory: 377kB
  ->  Seq Scan on documents_document (actual time=8.470..35429.261 rows=3593 loops=1)
        Filter: (to_tsvector(document_text) @@ plainto_tsquery('запрос'::text))
        Rows Removed by Filter: 9190
Planning time: 0.200 ms
Execution time: 35432.294 ms

Индекс

В первую очередь, конечно, надо добавить индекс. Самый простой способ: функциональный индекс.

CREATE INDEX idx_gin_document 
ON documents_document 
USING gin (to_tsvector('russian', "document_text"));

Создаваться такой индекс будет долго — на тестовой базе ему понадобилось ~26 секунд. Ему надо пройтись по базе и вызвать функцию to_tsvector для каждой записи. Хотя поиск он всё же ускоряет до 12 секунд, это всё ещё непростительно долго!

Sort (actual time=12213.943..12214.327 rows=3593 loops=1)
  Sort Key: (ts_rank(to_tsvector('russian'::regconfig, document_text), plainto_tsquery('запрос'::text))) DESC
  Sort Method: quicksort  Memory: 377kB
  ->  Bitmap Heap Scan on documents_document (actual time=3.849..12212.248 rows=3593 loops=1)
        Recheck Cond: (to_tsvector('russian'::regconfig, document_text) @@ plainto_tsquery('запрос'::text))
        Heap Blocks: exact=946
        ->  Bitmap Index Scan on idx_gin_document (actual time=0.427..0.427 rows=3593 loops=1)
              Index Cond: (to_tsvector('russian'::regconfig, document_text) @@ plainto_tsquery('запрос'::text))
Planning time: 0.109 ms
Execution time: 12214.452 ms

Многократный вызов to_tsvector

Для решения этой проблемы нужно хранить tsvector в базе. При изменении данных в таблице с документами, конечно, надо обновлять его — через триггеры в БД, с помощью бэкэнда.

Сделать это можно двумя способами:

1. Добавить колонку типа tsvector в таблицу с документами.
2. Создать отдельную таблицу с one-to-one связью с таблицей документов, и хранить там вектора.

Плюсы первого подхода: отсутствие join-ов при поиске.
Плюсы второго подхода: отсутствие лишних данных в таблице с документами, она остается такого же размера, как и раньше. При бэкапе не придется тратить время и место на tsvector, которые бэкапить вообще не нужно.

Оба похода ведут к тому, что данных на диске становится вдвое больше: хранятся тексты документов и их вектора.

Я для себя выбрал второй подход, его преимущества для меня весомей.

CREATE INDEX idx_gin_document 
ON documents_documentvector 
USING gin ("document_text");

SELECT documents_document.id, ts_rank("text", plainto_tsquery('запрос'))
FROM documents_document
  LEFT JOIN documents_documentvector ON documents_document.id = documents_documentvector.document_id
WHERE "text" @@ plainto_tsquery('запрос')
ORDER BY ts_rank("text", plainto_tsquery('запрос')) DESC;

Добавим данные в связанную таблицу и создадим индекс. Добавление данных заняло 24 секунды на тестовой базе, а создание индекса — всего 2,7 секунды. Обновление индекса и данных, как видим, существенно не ускорилось, но сам индекс теперь можно обновить очень быстро.

А во сколько раз ускорился сам поиск?

Sort (actual time=48.147..48.432 rows=3593 loops=1)
  Sort Key: (ts_rank(documents_documentvector.text, plainto_tsquery('запрос'::text))) DESC
  Sort Method: quicksort  Memory: 377kB
  ->  Hash Join (actual time=2.281..47.389 rows=3593 loops=1)
        Hash Cond: (documents_document.id = documents_documentvector.document_id)
        ->  Seq Scan on documents_document (actual time=0.003..2.190 rows=12783 loops=1)
        ->  Hash (actual time=2.252..2.252 rows=3593 loops=1)
              Buckets: 4096  Batches: 1  Memory Usage: 543kB
              ->  Bitmap Heap Scan on documents_documentvector (actual time=0.465..1.641 rows=3593 loops=1)
                    Recheck Cond: (text @@ plainto_tsquery('запрос'::text))
                    Heap Blocks: exact=577
                    ->  Bitmap Index Scan on idx_gin_document (actual time=0.404..0.404 rows=3593 loops=1)
                          Index Cond: (text @@ plainto_tsquery('запрос'::text))
Planning time: 0.410 ms
Execution time: 48.573 ms

SELECT id, ts_rank("text", plainto_tsquery('запрос')) AS rank
FROM documents_documentvector
WHERE "text" @@ plainto_tsquery('запрос')
ORDER BY rank;

Sort (actual time=44.339..44.487 rows=3593 loops=1)
  Sort Key: (ts_rank(text, plainto_tsquery('запрос'::text)))
  Sort Method: quicksort  Memory: 265kB
  ->  Bitmap Heap Scan on documents_documentvector (actual time=0.692..43.682 rows=3593 loops=1)
        Recheck Cond: (text @@ plainto_tsquery('запрос'::text))
        Heap Blocks: exact=577
        ->  Bitmap Index Scan on idx_gin_document (actual time=0.577..0.577 rows=3593 loops=1)
              Index Cond: (text @@ plainto_tsquery('запрос'::text))
Planning time: 0.182 ms
Execution time: 44.610 ms

Невероятно! И это несмотря на join и ts_rank. Уже вполне приемлемый результат, большую часть времени отнимет не поиск, а вычисление ts_rank для каждой из строк.

Многократный вызов ts_rank

Кажется, мы успешно решили все наши проблемы, кроме этой. 44 миллисекунды — вполне достойное время выполнения. Кажется, хэппи-энд близок? Не тут-то было!

Запустим тот же самый запрос без ts_rank и сравним результаты.

SELECT document_id, 1 AS rank
FROM documents_documentvector
WHERE "text" @@ plainto_tsquery('запрос')
ORDER BY rank;

Bitmap Heap Scan on documents_documentvector (actual time=0.503..1.609 rows=3593 loops=1)
  Recheck Cond: (text @@ plainto_tsquery('запрос'::text))
  Heap Blocks: exact=577
  ->  Bitmap Index Scan on idx_gin_document (actual time=0.439..0.439 rows=3593 loops=1)
        Index Cond: (text @@ plainto_tsquery('запрос'::text))
Planning time: 0.147 ms
Execution time: 1.715 ms

1,7 мс! В тридцать раз быстрее! Для боевой базы результаты ~150 мс и 1,5 секунды. Разница в любом случае на порядок, и 1,5 секунды — не то время, которое хочется ждать ответа от базы. Что же делать?

Выключить сортировку по релевантности нельзя, сократить количество строк для подсчета — нельзя (база должна вычислить ts_rank для всех совпавших документов, иначе их нельзя отсортировать).

Кое-где в интернете рекомендуют кэшировать наиболее частые запросы (и, соответственно, вызов ts_rank). Но мне подобный подход не нравится: правильно отобрать нужные запросы довольно сложно, и поиск все равно будет тормозить на запросах неправильных.

Очень хотелось бы, чтобы после прохода по индексу данные приходили в уже отсортированном виде, как это делает тот же Sphinx. К сожалению, из коробки в PostgreSQL ничего такого сделать не получится.

Но нам повезло — так умеет делать индекс RUM. Подробно о нём можно почитать, например, в презентации его авторов. Он хранит дополнительную информацию о запросе, которая позволяет прямо в индексе оценивать т.н. "расстояние" между tsvector и tsquery и выдавать сортированный результат сразу после сканирования индекса.

Но выкидывать GIN и устанавливать RUM сразу не стоит. У него есть минусы, плюсы и границы применения — об этом я напишу в следующей статье.