Re: Performance tips - Mailing list pgsql-general

shared_buffers is set to 128 in postgresql.conf - is that what you
mean? There's enough RAM in this machine that I should be able to increase
it if that will help.

The query I was running last night didn't finish by morning (started at
midnight, I cancelled it at 8:30 am), so something's clearly wrong. Here's
my plan (critiques and suggestions welcome):

1 Create some more indices, specifically on the id fields used to join
tables
2 Increase shared_buffers as much as is practical
3 Try to rewrite the query without using in(select...) constructs
4 Move my IDE Zip drive to the secondary IDE controller to avoid IDE
slowdowns


If the above aren't satisfactory:
- Buy more RAM and repeat 2 above
- Buy a SCSI hard drive (there's already a good SCSI controller) and move
the database there


HOWEVER... what I'm hearing from most folks is that, even under these
conditions, the performance I'm experiencing is worse than
expected. If that's true, what should I do to diagnose that?

Thanks again.

----------------------------------------------------------------------
Andrew J Perrin - andrew_perrin@unc.edu - http://www.unc.edu/~aperrin
 Assistant Professor of Sociology, U of North Carolina, Chapel Hill
      269 Hamilton Hall, CB#3210, Chapel Hill, NC 27599-3210 USA


On Thu, 10 Jan 2002, Justin Clift wrote:

> Hi Andrew,
>
> What're your memory buffers set to?
>
> The only time I've had my CPU get anywhere near 80% is when I'm running
> on really low or default memory buffer settings.  Normally, postmaster
> doesn't go above 15% for me.
>
> ???
>
> Regards and best wishes,
>
> Justin Clift
>
>
> Andrew Perrin wrote:
> >
> > Never mind - while I was writing the last message the vacuum analyze
> > ended. No messages from vacuum analyze (just the VACUUM
> > acknowledgement). I'm recreating the one user-created index (the rest are
> > based on serials) now and will re-test queries.
> >
> > Thanks for everyone's help.
> >
> > ----------------------------------------------------------------------
> > Andrew J Perrin - andrew_perrin@unc.edu - http://www.unc.edu/~aperrin
> >  Assistant Professor of Sociology, U of North Carolina, Chapel Hill
> >       269 Hamilton Hall, CB#3210, Chapel Hill, NC 27599-3210 USA
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
>    - Indira Gandhi
>