Re: Help tuning postgres

On 10/13/05, Csaba Nagy <nagy@ecircle-ag.com> wrote:
> On Thu, 2005-10-13 at 14:40, Andrew Sullivan wrote:
> > On Thu, Oct 13, 2005 at 10:15:03AM +0200, Csaba Nagy wrote:
> > > And how would the analyze help in finding this out ? I thought it would
> > > only show me additionally the actual timings, not more detail in what
> > > was done...
> >
> > Yes, it shows the actual timings, and the actual number of rows.  But
> > if the estimates that the planner makes are wildly different than the
> > actual results, then you know your statistics are wrong, and that the
> > planner is going about things the wrong way.  ANALYSE is a big help.
> > There's also a verbose option to it, but it's usually less useful in
> > production situations.

This is the point I was trying to make. I've seen special instances
where people have posted an explain annalyze for a select/update to
the list and suggestions have arisen allowing major performance
improvements.

If this task is where your database is performing its worst then it is
the best place to start with optimizing, short of the obvious stuff,
which it sounds like you've covered.

Sometimes, and I think this has often been true for databases that are
either very large or very small, statistics can be tweaked to get
better performance. One good example is when a sequential scan is
being chosen when an index scan may be better; something like this
would definately peg your disk i/o.

Throwing more hardware at your problem will definately help, but I'm a
performance freak and I like to optimize everything to the max.
*Sometimes* you can get drastic improvements without adding any
hardware. I have seen some truly miraculus turn-arounds by tweaking
some non-obvious settings based on suggestions made on this list.
--
Matthew Nuzum
www.bearfruit.org