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