On Fri, May 2, 2008 at 9:13 AM, Javier Olazaguirre <javier.olazaguirre@xxxxxxxxxxxxxxxx> 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...
