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 секунд. Непростительно долго!


Результаты EXPLAIN
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 секунд, это всё ещё непростительно долго!


Результаты EXPLAIN
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

Метрики без join

Запрос:


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 и сравним результаты.


Без 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 сразу не стоит. У него есть минусы, плюсы и границы применения — об этом я напишу в следующей статье.

Комментарии (10)


  1. AntonCtrannik
    12.03.2019 10:24

    Хорошая статья, хочу лишь добавить что в случае когда тексты написаны на разных языках, нужно добавить колонку типа REGCONFIG в которой будет храниться язык например:
    ALTER TABLE documents_document ADD COLUMN lang REGCONFIG NOT NULL DEFAULT 'russian'::regconfig;
    индекс:
    CREATE INDEX idx_gin_document
    ON documents_document
    USING gin (to_tsvector( lang, «document_text»));

    или
    добавить колонку lang типа VARCHAR:
    ALTER TABLE documents_document ADD COLUMN lang VARCHAR NOT NULL DEFAULT 'russian';
    индекс:
    CREATE INDEX idx_gin_document
    ON documents_document
    USING gin (to_tsvector( lang::REGCONFIG, «document_text»));


  1. apapacy
    12.03.2019 16:53

    Насколько актуальны по Вашему мнению полнотекстовые индексы без нечеткого поиска («с опечатками»)? Их по-прежнему имеет смысл использовать например в веб-приложениях?


    1. geoolekom Автор
      12.03.2019 17:40

      Считаю, что да, вполне стоит. В них есть поиск по словоформам, этого для большинства сайтов достаточно.
      Всё-таки поисковый движок – не самая важная часть сайта.


  1. mgremlin
    12.03.2019 18:30

    Спасибо, интересно.
    Ждем продолжения.


    1. geoolekom Автор
      12.03.2019 18:57

  1. Exponent
    12.03.2019 20:04

    Пробовал использовать полнотекстовый поиск на postgresql, но затем оказалось удачнее подключить Lucene к JPA, все культурно подключается и индексируется, поиск работает быстро.


  1. Maxchagin
    12.03.2019 22:49

    Спасибо за статью!
    Кстати, если создать индекс с конфигурацией russian

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

    И выполнять запрос без
    to_tsvector("document_text"), plainto_tsquery('запрос')

    То индекс использоваться не будет


    1. geoolekom Автор
      12.03.2019 23:22

      Да, конечно. Вы правы.
      Наверное, стоило написать об этом явно. Но в выводе EXPLAIN для функционального индекса можно увидеть, что я делаю запросы с Russian.


  1. immaculate
    13.03.2019 09:29

    Странно, я очень давно пользовался FTS в PostgreSQL, но в моем случае, после миграции с PostgresSQL FTS на SphinxSearch, который индексировал Postgres, скорость поиска выросла значительно, а нагрузка на базу данных существенно упала.


    Из всех деталей помню только, что тогда переход на SphinxSearch позволил спать спокойно как минимум несколько следующих месяцев.


    Правда это было в стародавние времена (едва ли не 10 лет назад, даже не помню, какая тогда версия Postgres была, наверное 8), и наверное, большое влияние оказывает размер документов и характер доступа к ним.


    1. geoolekom Автор
      13.03.2019 18:35

      У меня (да и на большей части сайтов тоже) полнотекстовый поиск выполняет довольно формальный характер. В таких случаях настроить постгрес, имхо, намного более логично, чем тащить лишнюю сущность в виде сфинкса.