Re: [GENERAL] index on ILIKE/LIKE - PostgreSQL 9.2

[Lucas Possamai]

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