pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to boost performance of ilike queries ?
8+ messages / 4 participants
[nested] [flat]
* How to boost performance of ilike queries ?
@ 2005-01-24 09:18 Antony Paul <antonypaul24@gmail.com>
2005-01-24 09:58 ` Re: How to boost performance of ilike queries ? Russell Smith <mr-russ@pws.com.au>
0 siblings, 1 reply; 8+ messages in thread
From: Antony Paul @ 2005-01-24 09:18 UTC (permalink / raw)
To: pgsql-performance
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 ?.
rgds
Antony Paul
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to boost performance of ilike queries ?
2005-01-24 09:18 How to boost performance of ilike queries ? Antony Paul <antonypaul24@gmail.com>
@ 2005-01-24 09:58 ` Russell Smith <mr-russ@pws.com.au>
2005-01-25 08:23 ` Re: How to boost performance of ilike queries ? Antony Paul <antonypaul24@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Russell Smith @ 2005-01-24 09:58 UTC (permalink / raw)
To: Antony Paul <antonypaul24@gmail.com>; +Cc: 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to boost performance of ilike queries ?
2005-01-24 09:18 How to boost performance of ilike queries ? Antony Paul <antonypaul24@gmail.com>
2005-01-24 09:58 ` Re: How to boost performance of ilike queries ? Russell Smith <mr-russ@pws.com.au>
@ 2005-01-25 08:23 ` Antony Paul <antonypaul24@gmail.com>
2005-01-25 08:49 ` Re: How to boost performance of ilike queries ? Russell Smith <mr-russ@pws.com.au>
2005-01-25 14:01 ` Re: How to boost performance of ilike queries ? Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 8+ messages in thread
From: Antony Paul @ 2005-01-25 08:23 UTC (permalink / raw)
To: Russell Smith <mr-russ@pws.com.au>; +Cc: pgsql-performance
Creating an index and using lower(column) does not change the explain
plan estimates.
It seems that it is not using index for like or ilike queries
irrespective of whether it have a pattern matching character in it or
not. (using PostgreSQL 7.3.3)
On googling I found this thread
http://archives.postgresql.org/pgsql-sql/2004-11/msg00285.php
It says that index is not used if the search string begins with a % symbol.
rgds
Antony Paul
On Mon, 24 Jan 2005 20:58:54 +1100, Russell Smith <mr-russ@pws.com.au> wrote:
> 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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to boost performance of ilike queries ?
2005-01-24 09:18 How to boost performance of ilike queries ? Antony Paul <antonypaul24@gmail.com>
2005-01-24 09:58 ` Re: How to boost performance of ilike queries ? Russell Smith <mr-russ@pws.com.au>
2005-01-25 08:23 ` Re: How to boost performance of ilike queries ? Antony Paul <antonypaul24@gmail.com>
@ 2005-01-25 08:49 ` Russell Smith <mr-russ@pws.com.au>
2005-01-25 10:09 ` Re: How to boost performance of ilike queries ? Antony Paul <antonypaul24@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Russell Smith @ 2005-01-25 08:49 UTC (permalink / raw)
To: Antony Paul <antonypaul24@gmail.com>; +Cc: pgsql-performance
On Tue, 25 Jan 2005 07:23 pm, Antony Paul wrote:
> Creating an index and using lower(column) does not change the explain
> plan estimates.
> It seems that it is not using index for like or ilike queries
> irrespective of whether it have a pattern matching character in it or
> not. (using PostgreSQL 7.3.3)
>
> On googling I found this thread
>
> http://archives.postgresql.org/pgsql-sql/2004-11/msg00285.php
>
> It says that index is not used if the search string begins with a % symbol.
What exactly are the type of like queries you are going? there is a solution
for having the % at the start, but you can win everyway.
>
> rgds
> Antony Paul
>
> On Mon, 24 Jan 2005 20:58:54 +1100, Russell Smith <mr-russ@pws.com.au> wrote:
> > 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
> >
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to boost performance of ilike queries ?
2005-01-24 09:18 How to boost performance of ilike queries ? Antony Paul <antonypaul24@gmail.com>
2005-01-24 09:58 ` Re: How to boost performance of ilike queries ? Russell Smith <mr-russ@pws.com.au>
2005-01-25 08:23 ` Re: How to boost performance of ilike queries ? Antony Paul <antonypaul24@gmail.com>
2005-01-25 08:49 ` Re: How to boost performance of ilike queries ? Russell Smith <mr-russ@pws.com.au>
@ 2005-01-25 10:09 ` Antony Paul <antonypaul24@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Antony Paul @ 2005-01-25 10:09 UTC (permalink / raw)
To: Russell Smith <mr-russ@pws.com.au>; +Cc: pgsql-performance
Actually the query is created like this.
User enters the query in a user interface. User can type any character
in the query criteria. ie. % and _ can be at any place. User have the
freedom to choose query columns as well. The query is agianst a single
table .
rgds
Antony Paul
On Tue, 25 Jan 2005 19:49:12 +1100, Russell Smith <mr-russ@pws.com.au> wrote:
> On Tue, 25 Jan 2005 07:23 pm, Antony Paul wrote:
> > Creating an index and using lower(column) does not change the explain
> > plan estimates.
> > It seems that it is not using index for like or ilike queries
> > irrespective of whether it have a pattern matching character in it or
> > not. (using PostgreSQL 7.3.3)
> >
> > On googling I found this thread
> >
> > http://archives.postgresql.org/pgsql-sql/2004-11/msg00285.php
> >
> > It says that index is not used if the search string begins with a % symbol.
>
> What exactly are the type of like queries you are going? there is a solution
> for having the % at the start, but you can win everyway.
>
> >
> > rgds
> > Antony Paul
> >
> > On Mon, 24 Jan 2005 20:58:54 +1100, Russell Smith <mr-russ@pws.com.au> wrote:
> > > 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
> > >
> >
> >
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to boost performance of ilike queries ?
2005-01-24 09:18 How to boost performance of ilike queries ? Antony Paul <antonypaul24@gmail.com>
2005-01-24 09:58 ` Re: How to boost performance of ilike queries ? Russell Smith <mr-russ@pws.com.au>
2005-01-25 08:23 ` Re: How to boost performance of ilike queries ? Antony Paul <antonypaul24@gmail.com>
@ 2005-01-25 14:01 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2005-01-25 14:01 UTC (permalink / raw)
To: Antony Paul <antonypaul24@gmail.com>; +Cc: Russell Smith <mr-russ@pws.com.au>; pgsql-performance
On Tue, 25 Jan 2005, Antony Paul wrote:
> Creating an index and using lower(column) does not change the explain
> plan estimates.
> It seems that it is not using index for like or ilike queries
> irrespective of whether it have a pattern matching character in it or
> not. (using PostgreSQL 7.3.3)
I believe in 7.3.x an index is only considered for like in "C" locale, I
think the *_pattern_op opclasses were added in 7.4 for which you can make
indexes that are considered for non wildcard starting search strings in
non "C" locales. And it may have trouble doing estimates before 8.0 on the
functional index because of lack of statistics. You may want to consider
an upgrade once 8.0 shakes out a bit.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to boost performance of ilike queries ?
@ 2005-01-24 14:01 Merlin Moncure <merlin.moncure@rcsonline.com>
2005-01-24 14:14 ` Re: How to boost performance of ilike queries ? Antony Paul <antonypaul24@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Merlin Moncure @ 2005-01-24 14:01 UTC (permalink / raw)
To: Russell Smith <mr-russ@pws.com.au>; +Cc: pgsql-performance
Russell wrote:
> 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.
>
There are two general cases I tend to use prepared queries. First case
is when there is an extremely complex plan generation step that you want
to skip. IMO, this is fairly rare in the normal course of doing things.
Second case is when you have a relatively simple query that gets
executed very, very frequently, such as select a,b,c from t where k.
Even though the query plan is simple, using a prepared query can shave
5-15% off your query time depending on various factors (on a low latency
network). If you fire off the statement a lot, this adds up. Not
generally worthwhile to go this route if you are executing over a high
latency network like the internet.
If your application behavior can benefit from the second case, it can
probably benefit from using parse/bind as well...use ExecPrepared, etc.
libpq interface functions.
The cumulative savings of using ExecPrepared() vs. using vanilla
PQExec() (for simple queries over a high latency network) can be 50% or
better. This is both from client's perspective and in server CPU load
(especially when data is read from cache). This is most interesting to
driver and middleware writers who broker data exchange between the
application and the data. The performance minded application developer
(who can make calls to the connection object) can take advantage of this
however.
Merlin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: How to boost performance of ilike queries ?
2005-01-24 14:01 Re: How to boost performance of ilike queries ? Merlin Moncure <merlin.moncure@rcsonline.com>
@ 2005-01-24 14:14 ` Antony Paul <antonypaul24@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Antony Paul @ 2005-01-24 14:14 UTC (permalink / raw)
To: Merlin Moncure <merlin.moncure@rcsonline.com>; +Cc: Russell Smith <mr-russ@pws.com.au>; pgsql-performance
I used PreparedStatements to avoid SQL injection attack and it is the
best way to do in JDBC.
rgds
Antony Paul
On Mon, 24 Jan 2005 09:01:49 -0500, Merlin Moncure
<merlin.moncure@rcsonline.com> wrote:
> Russell wrote:
> > 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.
> >
> There are two general cases I tend to use prepared queries. First case
> is when there is an extremely complex plan generation step that you want
> to skip. IMO, this is fairly rare in the normal course of doing things.
>
> Second case is when you have a relatively simple query that gets
> executed very, very frequently, such as select a,b,c from t where k.
> Even though the query plan is simple, using a prepared query can shave
> 5-15% off your query time depending on various factors (on a low latency
> network). If you fire off the statement a lot, this adds up. Not
> generally worthwhile to go this route if you are executing over a high
> latency network like the internet.
>
> If your application behavior can benefit from the second case, it can
> probably benefit from using parse/bind as well...use ExecPrepared, etc.
> libpq interface functions.
>
> The cumulative savings of using ExecPrepared() vs. using vanilla
> PQExec() (for simple queries over a high latency network) can be 50% or
> better. This is both from client's perspective and in server CPU load
> (especially when data is read from cache). This is most interesting to
> driver and middleware writers who broker data exchange between the
> application and the data. The performance minded application developer
> (who can make calls to the connection object) can take advantage of this
> however.
>
> Merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-01-25 14:01 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-24 09:18 How to boost performance of ilike queries ? Antony Paul <antonypaul24@gmail.com>
2005-01-24 09:58 ` Russell Smith <mr-russ@pws.com.au>
2005-01-25 08:23 ` Antony Paul <antonypaul24@gmail.com>
2005-01-25 08:49 ` Russell Smith <mr-russ@pws.com.au>
2005-01-25 10:09 ` Antony Paul <antonypaul24@gmail.com>
2005-01-25 14:01 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-01-24 14:01 Re: How to boost performance of ilike queries ? Merlin Moncure <merlin.moncure@rcsonline.com>
2005-01-24 14:14 ` Antony Paul <antonypaul24@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox