Как сделать запрос на поиск 6 значений в одной таблице и 1 значения в другой, имеется не оптимизированный запрос с ILIKE?

В таблице имеется более 13 миллионов записей. Этот запрос отрабатывает за 80 секунд. Что очень долго. Так как используется ILIKE индексирование не работает.
Как можно оптимизировать запрос? Может нужно создать FULL TEXT INDEX для всех полей?

select *, "user_apps"."id" as "uaId", "messages"."id" as "messageId"
from "messages" 
left join "user_apps" on "user_apps"."id" = "messages"."user_app_id" 
where "user_apps"."unique_id" ilike '%12346789%'
or "sender" ilike '%12346789%' 
or "message" ilike '%12346789%' 
or "receiver" ilike '%12346789%' 
or "messages"."country" ilike '%12346789%' 
or CAST(message.id as VARCHAR(255)) ilike '%12346789%' 
or "sim" ilike '%12346789%' 
and "type" = 'message'
order by "messageId" desc 
limit 51 offset 0
  • Вопрос задан
  • 625 просмотров
Пригласить эксперта
Ответы на вопрос 2
@Everything_is_bad
Пробуй pg_trgm, там побочный эффект часто помогает для like (но не факт, что тут поможет), а так, подобный треш, только внешним индексатором ускорять. Хотя конечно стоит описать глобальную проблему, может это по другому решается.
Ответ написан
Комментировать
mayton2019
@mayton2019
Bigdata Engineer
Непонятно зачем ты конструируешь ILIKE когда у тебя идет поиск цифр и нет
case-insensitive алгоритмов. Упрости, чтоб было системе проще.

Про триграммы тут уже напихали. Нечего добавить.

Есть идея попробовать следующее. Можно конкатенировать все искомые поля в одно большое
виртуальное поле и построить по нему реальный триграммный индекс. Здесь мы сэкономим на количестве.
Вместо 5 индексов сделаем один и результат будет тот-же самый. Нам по сути безразлично
в каком поле найдена строка. Важно что просто была дизьюнкция предикатов.

CREATE TABLE messages(
    .......
    all_fields text GENERATED ALWAYS AS (CONCAT(sender,' ',message,' ',receiver,' ',sim))
);

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX messages_tr_idx ON messages USING GIN (all_fields gin_trgm_ops);


Точно так-же поступить с табличкой user_apps.

По поводу этого ограничителя.

....
order by "messageId" desc 
limit 51 offset 0

Судя по всему - это дизайн запроса для показа на UI. Обычно так делают чтобы
показать на формочке веба или в приложении.

Limit и order - это плохая комбинация. Если смысл сортировки по messageId - это показать
последние (оперативные) сообщения то я-бы заменил это на поиск по партишену сегодняшнего
дня
(или опер-дня) если можно так сказать.

Разделение данных на оперативные и исторические ускорит поиск многократно. И если
допустии опер-день занимает 500 тыс строк а исторические данные - 13 млн. То вы
сразу получаете вместо 80 секунд ускорение ... эээ в сколько раз? 500 000/13 000 000 = x/80
это будет примерно 3 секунды. Вот. Это если предполагать линейную зависимость от объема.
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы