There are more than 13 million records in the table. This request is processed in 80 seconds. Which is a very long time. Since ILIKE is used, indexing does not work. How can I optimize my query? Maybe I need to create a FULL TEXT INDEX for all fields?
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