Skip site navigation (1) Skip section navigation (2)

Re: How to boost performance of ilike queries ?

From: Russell Smith <mr-russ(at)pws(dot)com(dot)au>
To: Antony Paul <antonypaul24(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: How to boost performance of ilike queries ?
Date: 2005-01-24 09:58:54
Message-ID: 200501242058.54670.mr-russ@pws.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, 24 Jan 2005 08:18 pm, Antony Paul wrote:
> Hi,
>     I have a query which is executed using ilike. The query values are
> received from user and it is executed using PreparedStatement.
> Currently all queries are executed as it is using iilike irrespective
> of whether it have a pattern matching character or not. Can using =
> instead of ilike boot performance ?.  If creating index can help then
> how the index should be created on lower case or uppercase ?.
> 
It depends on the type of queries you are doing.

changing it to something like  lower(column) like lower('text%'), and
creating an index on lower(column) will give you much better performance.

If you have % in the middle of the query, it will still be slow, but I assume that is not
the general case.

I am not sure what the effect of it being prepared will be, however I've had much success
with the method above without the queries being prepared.  Others may be able to offer advice
about if prepare will effect it.

Regards

Russell Smith

In response to

Responses

pgsql-performance by date

Next:From: Chris MairDate: 2005-01-24 10:38:47
Subject: Re: poor performance of db?
Previous:From: Andrei ReinusDate: 2005-01-24 09:22:13
Subject: Re: poor performance of db?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group