Re: Speed up repetitive queries

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Javier Olazaguirre" <javier(dot)olazaguirre(at)nexustelecom(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Speed up repetitive queries
Date: 2008-05-04 04:19:33
Message-ID: dcc563d10805032119u373dcd95i4aff318fd1c3f67d@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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...

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Patrick TJ McPhee 2008-05-04 04:29:11 Re: Unloading a table consistently
Previous Message Nathan Thatcher 2008-05-04 03:39:04 Custom C function - is palloc broken?