Лучшие альтернативы для быстрого поиска по большой таблице в базе данных Postgres?
У нас есть Express API, который подключается к базе данных Postgres с помощью Sequelize.
Одна из наших конечных точек возвращает список продаж, который принимает различные параметры запроса, такие как страница, порядок столбцов, фильтры и поисковый запрос.
Эта конечная точка выполняет два запроса: один для получения одной страницы результатов с заданными параметрами, а другой для получения количества всех элементов с одинаковыми параметрами. Оба запроса имеют одинаковые предложения from иwhere.
Большая часть запроса представляет собой точные совпадения (столбец = значение), но когда указан поисковый запрос, к запросу добавляются 10 условий:
8 из них — сравнение столбцов и поискового запроса (
ilike).Остальные 2 более сложны:
Чтобы получить полное имя клиента, используется concat:
name ilike %test name%И еще один, который использует
concat(Client.first_name, ' ', Client.last_name) ILIKE '%Test Name%'для получения аббревиатуры и позволяет пользователю выполнять поиск по названию местоположения в качестве аббревиатуры:regexp_replace
Мы тестируем клиент, который имеет наибольшее количество данных (примерно 100 тыс. строк), и когда эти regexp_replace(Location.name, '([a-z ])+', '', 'g') ILIKE '%ABCD%' отсутствуют в запросе, конечная точка обычно занимает от 1 до 2 секунд, что приемлемо. Но если они есть в запросе, это занимает около 6 секунд. Тот факт, что мы выполняем 2 запроса, делает разницу еще больше.
Необработанный запрос занимает около 600 мс без ilike и около 3 секунд с ними. Если я поставлю ilike на запрос, это покажет, что лайки — это то, что занимает большую часть времени, особенно тот, который содержит конкатенацию (имя и фамилия клиента).
Есть несколько простых вещей, которые мы можем сделать, например, уменьшить количество столбцов, по которым выполняется поиск (мы оцениваем, все ли они необходимы), перестать использовать аббревиатуры и сделать что-нибудь еще вместо этого объединения для полного имени. как || оператор (который я пытаюсь сделать в Sequelize). Также нам следует использовать индексы.
Но что бы вы порекомендовали сделать помимо этого? Я рассматриваю возможность использования Elasticsearch, но это может быть излишним и требует времени для настройки и обслуживания.
Я также читал о полнотекстовом поиске в Postgres, который ускорит поиск и предоставит нам более продвинутые функции поиска (аналогичные Elasticsearch). Порекомендуете ли вы это? Я думаю, вы бы связали это с индексами gist или gin, верно?
Есть еще какие-нибудь рекомендации?
Я сказал: «Когда выполняется поиск», если API не получает поисковый запрос, все эти 10 условий лайков удаляются из запроса, а вызов API занимает от 1 до 2 секунд, не имеет значения, что еще я сделать в запросе. Но когда запрос имеет эти 10 лайков, это всегда занимает около 6 секунд. Итак, очевидно, что проблема заключается в этих 10 лайках, а не в остальной части запроса или остальной части реализации API. Я думал, что это ясно, но я редактирую вопрос, чтобы он был более ясным, я также включу результаты EXPLAIN ANALYZE.
Да, FTS в Postgres может быть достаточно мощным для вашего случая использования, и у меня он работает хорошо. Теперь этот вопрос либо требует рекомендаций, либо основан на мнении, и то и другое является близкими причинами. Если вы хотите спросить, как применить возможности полнотекстового поиска к вашей таблице в Postgres, удалите все запросы рекомендаций и вместо этого предоставьте минимальный воспроизводимый пример . Обязательно к прочтению: Meta.stackoverflow.com/questions/271055. Расскажите нам о своей цели, примере запроса со значениями, структуре таблицы, существующих индексах, ограничениях, версии pg, реализации. ограничения и т. д. Сделайте это подробным и целенаправленным.
Кроме того, применение regex_replace к 100 тысячам строк бесполезно, никакой индекс в этом не поможет. Обычно это можно смягчить, нормализуя ваши данные таким образом, чтобы столбец представлял собой отдельный объект, а не что-то объединенное (в некоторых случаях это может быть сгенерированный столбец, в других случаях будет создана «полная версия» и т. д.). То же самое и с «ILIKE», если только вы не выполняете поиск по префиксу (шаблон не начинается с %), но в этом может помочь полнотекстовый поиск (tsvector, tsquery) (postgresql.org/docs/current/functions-textsearch.html).
Ответы 1
Решая эту проблему, я бы, вероятно, начал с оценки того, какая из операций занимает больше всего времени. Возможно, предложения конкатенации и ILIKE занимают время, или возможно, ваше хранилище медленно загружает данные в ОЗУ. Это очень сложно сказать по описанию запроса.
С этой целью я настоятельно рекомендую использовать инструменты анализа плана запроса Postgres. (https://www.postgresql.org/docs/current/sql-explain.html)
EXPLAIN ANALYZE предоставит вам план запроса (шаги внутреннего выполнения, которые выполняет postgres), который вы затем сможете скопировать и вставить в визуализатор плана запроса, например dalibo (https://explain.dalibo.com/).
Сделав это, вы сможете четко видеть, какая именно часть вашего запроса занимает больше всего времени.
Тем не менее, если вы не проиндексировали столбцы, по которым ищете, это шаг 0. Все, что происходит после индексации, является второстепенным. Правильная индексация сэкономит вам более 90 % времени при выполнении многих запросов. Все остальное порядка 5-10%.
Обратите внимание, что обычный индекс postgres b-дерева для строкового столбца фактически будет использоваться планировщиком запросов postgres только в том случае, если выполняются определенные условия. Самое главное, что он будет использоваться только при поиске подстрок, находящихся в начале всей строки.
Чтобы обойти эту проблему, вам нужно будет использовать индекс полнотекстового поиска Postgres. Выбор индексов GIN или GIST очень незначителен по сравнению с экономией времени при простом наличии индекса.
Спасибо, я выполнил EXPLAIN ANALYZE (кстати, я отредактировал вопрос), и да, условия ilike - это часть запроса, занимающая большую часть времени, и внутри этих условий вызовы regexp_replace и concat занимают больше всего времени. Итак, следующие шаги — убедиться, что у нас есть индексы для всего, что нам нужно, возможно, удалив поиск по акронимам, а затем оценив использование postgres FTS. Я спрошу свою команду, достаточно ли поиска подстрок в начале строки. Спасибо!
Пожалуйста, включите несколько примеров. Все, что я могу понять из вашей прозы, это то, что когда вы ничего не делаете, на то, чтобы ничего не сделать, уходит 1–2 секунды, но когда вы что-то делаете, это занимает больше времени.