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

Question

[Александр]

В таблице имеется более 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

Comments

[Дмитрий]

Как уменьшить время выполнения запроса like '%uri%' в Postgresql?

[Александр]

Дмитрий, а как создать триграмму для нескольких полей, при этом одно поле будет из другой таблице добавленное JOIN?

[Everything_is_bad]

Александр, так на каждое поле индекс

[Дмитрий]

Александр, https://postgrespro.ru/docs/postgresql/16/indexes-...

Answer 1

[Everything_is_bad]

Пробуй pg_trgm, там побочный эффект часто помогает для like (но не факт, что тут поможет), а так, подобный треш, только внешним индексатором ускорять. Хотя конечно стоит описать глобальную проблему, может это по другому решается.

Answer 2

[mayton2019]

Непонятно зачем ты конструируешь 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 секунды. Вот. Это если предполагать линейную зависимость от объема.