Re: How to boost performance of ilike queries ?

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