This is a multi-part message in MIME format.
------_=_NextPart_001_01C8AC67.59227174
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
> I have an application developped by a third party which takes very
> long to process all the queries.
>=20
> I use Red Hat 4 and Postgre 8.2.7 on a 64 bit machine.
>=20
> Checking the log files created by postgre I see that the program is
> running always the same query:
>=20
> 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_, connection0_.carrier as carrier35_5_,
> connection0_.cic as cic35_5_, pointcode1_.id as id45_0_,
> pointcode1_.value as value45_0_, pointcode1_.ni as ni45_0_,
> pointcode1_.active as active45_0_, gateway2_.id as id41_1_,
> gateway2_.value as value41_1_, gateway2_.ni as ni41_1_,
> gateway2_.active 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 as low34_3_, cic4_.high as high34_3_,
> cic4_.active as active34_3_, cic4_.producttype as productt5_34_3_,
> producttyp5_.id as id46_4_, producttyp5_.name as name46_4_,
> producttyp5_.active as active46_4_ from connection connection0_ left
> outer join pointcode pointcode1_ on connection0_.pc=3Dpointcode1_.id
> left outer join gateway gateway2_ on connection0_.gw=3Dgateway2_.id =
left
> outer join carrier carrier3_ on connection0_.carrier=3Dcarrier3_.id =
left
> outer join cic cic4_ on connection0_.cic=3Dcic4.id left outer join
> producttype producttyp5_ on cic4_.producttype=3Dproducttyp5_.id where
> connection0_.id=3D$1
>=20
>=20
> parameters: $1 =3D '141508'
>=20
> 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.
>=20
> Is there any way to speed up the execution of this query?
>=20
> 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.
> I already tried changing shared buffers and other parameters in
> postgresql.conf, but performance is still the same.
> 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). 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.
>=20
> Thanx!!
> Javier
------_=_NextPart_001_01C8AC67.59227174
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7652.24">
<TITLE>Speed up repetitive queries</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<P><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 FACE=3D"Arial">I have an =
application developped by a third party which takes very long to =
process all the queries.</FONT></SPAN>
</P>
<P><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 FACE=3D"Arial">I use Red Hat 4 =
and Postgre 8.2.7 on a 64 bit machine.</FONT></SPAN>
</P>
<P><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 FACE=3D"Arial">Checking the log =
files created by postgre I see that the program is running always the =
same query:</FONT></SPAN>
</P>
<P><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 FACE=3D"Arial">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_, =
connection0_.carrier as carrier35_5_, connection0_.cic as cic35_5_, =
pointcode1_.id as id45_0_, pointcode1_.value as value45_0_, =
pointcode1_.ni as ni45_0_, pointcode1_.active as active45_0_, =
gateway2_.id as id41_1_, gateway2_.value as value41_1_, gateway2_.ni as =
ni41_1_, gateway2_.active 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 as low34_3_, cic4_.high as high34_3_, cic4_.active =
as active34_3_, cic4_.producttype as productt5_34_3_, producttyp5_.id as =
id46_4_, producttyp5_.name as name46_4_, producttyp5_.active as =
active46_4_ from connection connection0_ left outer join pointcode =
pointcode1_ on connection0_.pc=3Dpointcode1_.id left outer join gateway =
gateway2_ on connection0_.gw=3Dgateway2_.id left outer join carrier =
carrier3_ on connection0_.carrier=3Dcarrier3_.id left outer join cic =
cic4_ on connection0_.cic=3Dcic4.id left outer join producttype =
producttyp5_ on cic4_.producttype=3Dproducttyp5_.id where =
connection0_.id=3D$1</FONT></SPAN></P>
<BR>
<P><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 FACE=3D"Arial">parameters: $1 =3D =
'141508'</FONT></SPAN>
</P>
<P><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 FACE=3D"Arial">The only thing =
that changes is the parameter at the end ($1).</FONT></SPAN>
<BR><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 FACE=3D"Arial">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.</FONT></SPAN></P>
<P><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 FACE=3D"Arial">Is there any way =
to speed up the execution of this query?</FONT></SPAN>
</P>
<P><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 FACE=3D"Arial">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.</FONT></SPAN></P>
<P><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 FACE=3D"Arial">I already tried =
changing shared buffers and other parameters in postgresql.conf, but =
performance is still the same.</FONT></SPAN>
<BR><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 FACE=3D"Arial">When I run a =
Explain statement with the select I see indices are being used by all =
subqueries.</FONT></SPAN>
<BR><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 FACE=3D"Arial">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). 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.</FONT></SPAN></P>
<P><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 =
FACE=3D"Arial">Thanx!!</FONT></SPAN>
<BR><SPAN LANG=3D"de-ch"><FONT SIZE=3D2 =
FACE=3D"Arial">Javier</FONT></SPAN><SPAN LANG=3D"en-us"></SPAN>
</P>
</BODY>
</HTML>
------_=_NextPart_001_01C8AC67.59227174--
|
