Thanks all for your valuable comments, as I gather, what I need to do is to check the queries that are slow and do a vacuum analyze and share the results along with postgresql.conf being used.
I will work on that. Thanks again, Saurabh On Tue, Jul 7, 2009 at 4:46 AM, justin <jus...@emproshunts.com> wrote: > Saurabh Dave wrote: > > >No offense intended - but have you looked at the documentation for > postgresql.conf? > > >If you are going to include PostgreSQL in your application, I'd highly > recommend you >understand what you are including. :-) > > I had a look into the documentation of postgres.conf, and tried a lot with > changing paramters I thought would improve the performance, but in vain. > Autovaccum is enabled by default in 8.3.7 , but i reduced the nap time so > that it happens more frequently. > > As others have pointed tuning is not a caned answer hence all the config > options to start with. But to change the configuration to something a bench > mark must be made. The only way to do that is identify the common SQL > commands sent to the server then run explain analyze so you know what the > server is doing. Then post the the results along with Config file and we > can make suggestions > > There is http://wiki.postgresql.org/wiki/Performance_Optimization > > Greg Smith is working on a tuner > http://notemagnet.blogspot.com/2008/11/automating-initial-postgresqlconf.html > > But thats a monumental undertaking as one configuration setting for one > type of work load can be ruinousness to another work load. > > The one common theme is know the workload so the configuration matches. > > > My personal opinion is that certain parameters in postgres.conf are simply > too technical in nature for a application developer like me, it becomes more > of a trial and error kind of frustrating process. > > This boils down to know the work load. > different kinds of work loads: > A: more writing with very few reads. > B: more reads that are simple queries and few complex quiers with very > few writes. There is a ratio to look at in my case 10000 reads occur before > next write So we have lots of indexes aimed at those common queries. > C: Complex queries taking minutes to hours to run on data warehouse > covering millions of records. > D: equal work load between writes and reads. > > There are many kinds of workloads requiring different configurations. > > > If there a utility that understands the system specification on which > postgres is going to run and change the paramters accordingly, that would > help. > > Thanks, > Saurabh > > <snip> > >