Re: index on ILIKE/LIKE - PostgreSQL 9.2

[Lucas Possamai <drum.lucas@xxxxxxxxx>]

Hi there,

The problem was solved by using lowercase

New index:

CREATE INDEX CONCURRENTLY  ON public.ja_jobs (clientid, lower(title) varchar_pattern_ops, time_job);

New query:

SELECT DISTINCT title
                FROM public.ja_jobs WHERE lower(title) LIKE lower('RYAN WER')
                AND clientid = 31239 
                AND time_job > 1457826264
                order BY title
limit 10;

- Improvement of 1400%

Thanks

Lucas