> 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:
>
> 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=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_.producttype=producttyp5_.id where
> connection0_.id=$1
>
>
> parameters: $1 = '141508'
>
> 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 there any way to speed up the execution of this 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.
> 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.
>
> Thanx!!
> Javier