Re: Speed up repetitive queries

Hi,

Thanx for your help.

I don't think the application is preparing the query, I think it just
runs the same query again and again as if it were completely different
each time, althoug it's not.

I paste some lines of the log file during the execution of the software:

2008-04-30 19:59:22.923 CEST admin CSIReport LOG: execute <unnamed>:
select connection0_.id as id35_5_, connection0_.pc_name as pc2_35_5_
, connection0_.gw_name as gw3_35_5_, connection0_.active as active35_5_,
connection0_.pc as pc35_5_, connection0_.gw as gw35_5_, connectio
n0_.carrier as carrier35_5_, connection0_.cic as cic35_5_,
pointcode1_.id as id45_0_, pointcode1_.value as value45_0_,
pointcode1_.ni as n
i45_0_, pointcode1_.active as active45_0_, gateway2_.id as id41_1_,
gateway2_.value as value41_1_, gateway2_.ni as ni41_1_, gateway2_.acti
ve as active41_1_, carrier3_.id as id33_2_, carrier3_.name as name33_2_,
carrier3_.active as active33_2_, cic4_.id as id34_3_, cic4_.low a
s low34_3_, cic4_.high as high34_3_, cic4_.active as active34_3_,
cic4_.producttype as productt5_34_3_, producttyp5_.id as id46_4_, produc
ttyp5_.name as name46_4_, producttyp5_.active as active46_4_ from
connection connection0_ left outer join pointcode pointcode1_ on connect
ion0_.pc=pointcode1_.id left outer join gateway gateway2_ on
connection0_.gw=gateway2_.id left outer join carrier carrier3_ on
connection0
_.carrier=carrier3_.id left outer join cic cic4_ on
connection0_.cic=cic4_.id left outer join producttype producttyp5_ on
cic4_.producttyp
e=producttyp5_.id where connection0_.id=$1
2008-04-30 19:59:22.923 CEST admin CSIReport DETAIL: parameters: $1 =
'50989'
2008-04-30 19:59:22.923 CEST admin CSIReport LOG: duration: 0.099 ms
2008-04-30 19:59:22.923 CEST admin CSIReport LOG: duration: 0.165 ms
2008-04-30 19:59:22.924 CEST admin CSIReport LOG: duration: 0.728 ms
2008-04-30 19:59:22.924 CEST admin CSIReport LOG: execute <unnamed>:
select connection0_.id as id35_5_, connection0_.pc_name as pc2_35_5_
, connection0_.gw_name as gw3_35_5_, connection0_.active as active35_5_,
connection0_.pc as pc35_5_, connection0_.gw as gw35_5_, connectio
n0_.carrier as carrier35_5_, connection0_.cic as cic35_5_,
pointcode1_.id as id45_0_, pointcode1_.value as value45_0_,
pointcode1_.ni as n
i45_0_, pointcode1_.active as active45_0_, gateway2_.id as id41_1_,
gateway2_.value as value41_1_, gateway2_.ni as ni41_1_, gateway2_.acti
ve as active41_1_, carrier3_.id as id33_2_, carrier3_.name as name33_2_,
carrier3_.active as active33_2_, cic4_.id as id34_3_, cic4_.low a
s low34_3_, cic4_.high as high34_3_, cic4_.active as active34_3_,
cic4_.producttype as productt5_34_3_, producttyp5_.id as id46_4_, produc
ttyp5_.name as name46_4_, producttyp5_.active as active46_4_ from
connection connection0_ left outer join pointcode pointcode1_ on connect
ion0_.pc=pointcode1_.id left outer join gateway gateway2_ on
connection0_.gw=gateway2_.id left outer join carrier carrier3_ on
connection0
_.carrier=carrier3_.id left outer join cic cic4_ on
connection0_.cic=cic4_.id left outer join producttype producttyp5_ on
cic4_.producttyp
e=producttyp5_.id where connection0_.id=$1
2008-04-30 19:59:22.924 CEST admin CSIReport DETAIL: parameters: $1 =
'251318'
2008-04-30 19:59:22.924 CEST admin CSIReport LOG: duration: 0.093 ms
2008-04-30 19:59:22.925 CEST admin CSIReport LOG: duration: 0.164 ms
2008-04-30 19:59:22.925 CEST admin CSIReport LOG: duration: 0.725 ms

I guess using PREPARE could speed things up a little bit, but that
probably has to be coded in the software, so for the moment I think I
cannot change that.

So, changing parameters of shared memory and postgresql.conf it's not
likely to make things faster for this sort of queries?

About the confusion of postgre version, yes, it's postgresql 8.2.7 64
bit.
I didn't know that there were several postgresql versions, with and
without the sql word in the name.

Thanks a lot for your help!
Javier

-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: Sunday, 04. May, 2008 06:20
To: Javier Olazaguirre
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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...