Re: Speed up repetitive queries

On Fri, May 2, 2008 at 9:13 AM, Javier Olazaguirre
<javier(dot)olazaguirre(at)nexustelecom(dot)com> wrote:
>
> I have an application developped by a third party which takes very long to
> process all the queries.
>
> I use Red Hat 4 and Postgre 8.2.7 on a 64 bit machine.
>
> Checking the log files created by postgre I see that the program is running
> always the same query:

> The only thing that changes is the parameter at the end ($1).
> This query is executed at least a million times (sometimes several million
> times) just changing the value of the $1 parameter. Each query takes between
> 1 and 2 milliseconds to execute in my system. So running a million queries
> takes quite a lot of minutes.

Is the application preparing and re-executing the same query, or
repreparing each time it executes it? Preparation might be a pretty
significant amount of overhead here.

> Is there any way to speed up the execution of this query?

1-2 milliseconds is pretty fast for an individual query.

> I cannot change the code of the application, I already got it compiled, so
> as far as I can think of, all I can do is tune the system, change parameters
> in postgre, etc.

Yeah, we've all been there. Sometimes you can come up with a workaround.

> I already tried changing shared buffers and other parameters in
> postgresql.conf, but performance is still the same.

Yeah, I can't imagine there's a lot of low hanging fruit for tuning
the db for such a simple query.

> When I run a Explain statement with the select I see indices are being used
> by all subqueries.
> I see my cpu is at 100%, so I believe my bottleneck is not IO or memory
> (8GB, and in "top" I see almost all of it is free).

Yeah, I can't see using more memory helping with this operation. It's
a single small bit at a time. In fact, using more memory would just
mean more to keep track of, likely slowing things down.

> My problem is that of
> all the cores of my processors, postgre is just using one, but I guess this
> can only be fixed changing the code of the application running the queries
> on postgre, so this is a different story.

Of course pgsql is using just one. You're only giving it one thing to
do at a time. (btw, it's PostgreSQL, postgres, pgsql, or pg. Postgre
is generally not preferred. No big. and no matter how you spell it,
it's pronounced "Post-Gres-Q-L" :) )

What you might look at doing is having the application run in multiple
instances each instance across a specific data range. This will
likely move your heads all over the place. OTOH, if the app could be
rewritten to send >1 query at a time through multiple connections, it
could likely get faster.

However, running multiple update queries will very quickly saturate
your I/O and you'll suddenly be I/O bound. That can be worked on with
more discs, RAID-10, battery backed RAID controllers, etc...