Re: index on ILIKE/LIKE - PostgreSQL 9.2

From: Lucas Possamai <drum(dot)lucas(at)gmail(dot)com>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Re: index on ILIKE/LIKE - PostgreSQL 9.2
Date: 2016-05-23 21:37:43
Message-ID: CAE_gQfWE+yfS2gfcOMwBhfkkRTrLLq+Q9Ovbq1_bKw69KGOQQA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Lucas Possamai 2016-05-23 21:40:15 Re: Function PostgreSQL 9.2
Previous Message Lucas Possamai 2016-05-23 21:34:23 Re: Connections - Postgres 9.2