У нас есть 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, верно?
Есть еще какие-нибудь рекомендации?
🤔 А знаете ли вы, что...
Node.js имеет огромное количество библиотек и модулей, доступных через npm (Node Package Manager).
Решая эту проблему, я бы, вероятно, начал с оценки того, какая из операций занимает больше всего времени. Возможно, предложения конкатенации и 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 очень незначителен по сравнению с экономией времени при простом наличии индекса.