Оценка производительности полнотекстового поиска в PostgreSQL на примерах приближенных к реальным

Добрый день! Меня зовут Александр и я С# разработчик в компании Bimeister!

Данная статья предназначена для людей, применяющих или планирующих применять полнотекстовый поиск по объектам в БД PostgreSQL, а также для тех кто хотел бы оценить скорость поиска при использовании встроенных механизмов PostgreSQL для различных объемов данных.

Исследование и его цель

Часто возникает необходимость проверить, насколько встроенный инструмент соответствует потребностям и задачам, которые возникают в проекте. Такие вопросы обычно звучат в форме "Сколько времени займет выполнение определенного типа запроса при заданных условиях?".

В нашем случае мы хотим определить время, затрачиваемое на выборку сущностей при полнотекстовом поиске по 1, 3 или 6 текстовым полям.

Мы также решили оценить объемы и наборы данных, которые мы хотим получить. Мы выбрали два варианта:

1. Выборка 10-100 записей в результатах, с выводом первых 50.

2. Выборка 0.5-1% от общего объема записей, с выводом первых 50.

В нашем проекте используется постраничная выборка, поэтому мы всегда выбираем только первые 50 записей с предварительной сортировкой по одному из полей. Поиск будет осуществляться по 5-6 символам (числа, буквы, знаки препинания).

Подбор параметров и моделирование

Для проверки результатов подобрали данные, в которых сможем получить представленные ранее результаты для поиска по 1,3,6 полям, так чтобы записи на каждый из запросов не повторялись и были распределены примерно равномерно по каждому полю. Например, для строки "aBc-De" было найдено 30 записей, где было 10 совпадений по первому полю, 10 по второму и 10 по третьему.

Строка данных формировалась из части GUID, где в середину помещался искомый блок текста, а затем следовал номер строки и завершение из другой части GUID. В сумме такая строка составляла 40-45 символов. Например, для строки поиска "aBc-De" с номерами 333,444 сформировались бы строки формата: ‘b35f4b95-f0be-4aBc-De3331a72f00f-20f0-4’, ’ 436ca55d-7032-4aBc-De444da496f56-97b9-4’.

Критериями поиска и условиями работы с данными были:

• Максимальная скорость поиска

• Редкое обновление данных

• Большой объем данных

• Встроенные возможности PostgreSQL

• Возможность использования через c# код без серьезных костылей

Для определения подходящих индексов было рассмотрено несколько статей:

• https://www.postgresql.org/docs/10/textsearch.html

• https://habr.com/ru/companies/postgrespro/articles/337502/

• https://habr.com/ru/companies/postgrespro/articles/340978/

В результате был выбран GIN индекс как наиболее подходящий под заданные критерии.

Первичное тестирование SQL запросов и API на приближенных к реальным характеристиках

Моделирование

В ходе моделирования нас интересовала разница во времени выполнения запросов в зависимости от общего объема данных, а также скорость работы при использовании прямых SQL запросов и запросов через API.

Для измерения времени выполнения использовались встроенные средства PostgreSQL и библиотека Stopwatch для API. Все запросы включали сортировку по одному из полей и выборку первых 50 строк. В качестве индекса использовался GIN индекс, охватывающий 6 полей.

Пример запроса SQL для поиска по 6 полям:

SELECT * FROM "Objects"
WHERE "Code" ILIKE '%-4d7qF%'
   OR  "Name" ILIKE '%-4d7qF%'
   OR  "SearchData1" ILIKE '%-4d7qF%'
   OR  "SearchData2" ILIKE '%-4d7qF%'
   OR  "SearchData3" ILIKE '%-4d7qF%'
   OR  "SearchData4" ILIKE '%-4d7qF%'
ORDER  BY "Name"
    FETCH FIRST 50 ROWS ONLY;

Результаты запроса через SQL:

Важно отметить, что при анализе времени выполнения запросов была использована логарифмическая шкала, чтобы учесть нелинейный рост времени с увеличением объема данных. По результатам исследования было замечено, что после достижения примерно 10 миллионов записей происходит явная деградация скорости выполнения запросов.

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

В случае малых выборок данных, независимо от "прогретости" индексов и количества полей, по которым происходит поиск, время выполнения всегда колеблется в пределах 100-500 миллисекунд. Из-за этого, дальнейшее исследование малых выборок данных не проводилось.

Первичные подход к выполнению API запросов была таков, что они выполнялись после SQL запросов, что означает, что индексы были уже "прогретыми" и данные считались неточными для проведения полноценного сравнения и анализа.

Корректировки в эксперименте

На основе полученных результатов было решено, что требуется дополнительное исследование для API запросов на объеме 50 млн, при котором данные будут иметь нескольких различных запросов для каждого из исследуемых случаев. Индексы решили заменить на 6 одиночных индексов, по одному для каждого из полей.

Основная область проверки - запросы с 0.5-1% общих данных с сортировкой (как наихудший из возможных сценариев). Малые выборки проверялись, но больше для подтверждения результатов.

Уточненное тестирование API, но без прогрева индексов на приближенных к реальным характеристиках

Моделирование

В данном случае использовались только API запросы на объемах в 50 млн записей, по 2 запроса на каждый случай. Для проверки точности времени запроса индексы удалялись и перестраивались. В результате использовался усредненный результат. Дополнительно проводился мониторинг утилизации ресурсов стенда (характеристики стенда будут представлены в конце статьи).

Код запроса API

var pattern = LikePatternsBuilder.Contains(str);
var query = _Objects
    .Where(Objects =>
        EF.Functions.ILike(Objects.Name, pattern, LikePatternsBuilder.EscapeCharacter) ||
        EF.Functions.ILike(Objects.Code, pattern, LikePatternsBuilder.EscapeCharacter) ||
        EF.Functions.ILike(Objects.SearchData1, pattern, LikePatternsBuilder.EscapeCharacter))
    .OrderBy(technicalObject => Objects.Name)
    .AsQueryable();

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

SELECT t."Id",
       t."Code",
       t."Name",
       t."SearchData1"
FROM "Objects" AS t
WHERE ((((t."Name" ILIKE @searchStr ESCAPE '\' 
         OR t."Code" ILIKE @searchStr ESCAPE '\') 
         OR t."SearchData1" ILIKE @searchStr ESCAPE '\') 
ORDER BY t."Name"
LIMIT 50 OFFSET 0

Результаты тестирования:

Наихудший результат переходил в таймаут запросов (отображается оранжевым столбцом на графике). При мониторинге ресурсов было замечено, что время выполнения в среднем составляло 90 секунд, хотя в одном случае было замечено время выполнения более 300 секунд.

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

Дополнительные данные

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

Анализ ресурсов показал, что основная нагрузка сейчас идет на диск. Изменений нагрузки на оперативную память или сеть зафиксировать не удалось. Нагрузка на процессор изменялась в пределах 10-20%, но на короткое время.

Также было проверено время вставки данных при включенных индексах. Скорость невысока, но позволяет вставлять и обновлять данные в объемах до нескольких тысяч записей (7 мс. на запись примерно).

 Тестирование проходило на стенде с представленными ниже характеристиками:

Выводы

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

Работа на данных технологиях из коробки (без дополнительных манипуляций) на объемах свыше 10 млн объектов скорее всего приведет к значительным просадкам производительности. Gin индексы хорошо подойдут как временное решение, но потребуется их удаление и повторное построение (либо выключение и перестроение) при импорте существенных объемов данных, так как идет замедление обновления и вставки данных при их наличии. (предварительная оценка в 20-30 раз)

В нашем случае, для работы на больших чем 10 млн объектов объемах они не подойдут, так что будем искать другие варианты решения этой задачи. Планировали посмотреть на Solr и Elastic, но пока нет окончательного решения.

Комментарии и Дополнительная информация

1) Были выполнены проверки быстродействия поиска как через API так и путем прямого SQL запроса. GIN индексы выдают достаточно хорошую производительность на объемах около 10 Млн записей (худший сценарий в пределах 2 сек). Малые объемы результатов поиска выдаются быстро при всех исследованных объемах.

При росте количества объектов видна быстрая деградация, которая проявляется в росте времени выполнения от 10 до 50 раз.

2) По представленному ниже плану выполнения можно понять, что построенные индексы активно используются (План представлен для аналогичных запросов, но на меньших объемах данных)

Limit  (cost=5021.21..5021.33 rows=50 width=811)
  ->  Sort  (cost=5021.21..5024.23 rows=1208 width=811)
        Sort Key: "Name"
        ->  Bitmap Heap Scan on "TechnicalObjects"  (cost=602.87..4981.08 rows=1208 width=811)
              Recheck Cond: ((("Code")::text ~~* '%Cz2-2%'::text) OR (("Name")::text ~~* '%Cz2-2%'::text) OR ((searchdata1)::text ~~* '%Cz2-2%'::text) OR ((searchdata2)::text ~~* '%Cz2-2%'::text) OR ((searchdata3)::text ~~* '%Cz2-2%'::text) OR ((searchdata4)::text ~~* '%Cz2-2%'::text))
              ->  BitmapOr  (cost=602.87..602.87 rows=1208 width=0)
                    ->  Bitmap Index Scan on idx_testtable_search_2  (cost=0.00..97.51 rows=201 width=0)
                          Index Cond: ((""Code"")::text ~~* '%Cz2-2%'::text)
                    ->  Bitmap Index Scan on idx_testtable_search_1  (cost=0.00..97.51 rows=201 width=0)
                          Index Cond: ((""Name"")::text ~~* '%Cz2-2%'::text)
                    ->  Bitmap Index Scan on idx_testtable_search_3  (cost=0.00..101.51 rows=201 width=0)
                          Index Cond: ((searchdata1)::text ~~* '%Cz2-2%'::text)
                    ->  Bitmap Index Scan on idx_testtable_search_4  (cost=0.00..101.51 rows=201 width=0)
                          Index Cond: ((searchdata2)::text ~~* '%Cz2-2%'::text)
                    ->  Bitmap Index Scan on idx_testtable_search_5  (cost=0.00..101.51 rows=201 width=0)
                          Index Cond: ((searchdata3)::text ~~* '%Cz2-2%'::text)
                    ->  Bitmap Index Scan on idx_testtable_search_6  (cost=0.00..101.51 rows=201 width=0)
                          Index Cond: ((searchdata4)::text ~~* '%Cz2-2%'::text)

План представлен для одного из запросов поисковых запроса по 2 000 000 записей с тем же распределением данных что и в основных запросах. Как можно заметить основные затраты времени идут на сортировку и подготовку данных.

 3) Также хотелось бы добавить, что в документации можно встретить рекомендации по использованию tsvector и tsquery, однако согласно документации они ориентированы на поиск по документам на естественном языке, что плохо укладывается в заданные критерии поиска, где основными поисковыми запросами будут число-буквенные комбинации которые плохо подходят для разбиения на лексемы.

Вырезка из документации: PostgreSQL provides two data types that are designed to support full text search, which is the activity of searching through a collection of natural-language documents to locate those that best match a query.