Thread: How to boost performance of ilike queries ?
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
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
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
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 >
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 >
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 > > > >
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 > > > > > > > >
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.