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
