Re: Problem Designing Index

pgsql-general-owner(at)postgresql(dot)org wrote on 08/13/2007 08:36:23 AM:

> While it's difficult to be sure, I'm guessing you have either a hardware
> problem, or a tuning problem -- but I don't think your indexes are a
problem.
>
> Keep in mind that once PostgreSQL has determined which rows to return, it
> has to actually read all those rows off disk and send them to the client
> application. In my opinion, 8 seconds to read in over 100,000 rows isn't
> unreasonable (especially if those rows are wide).
>
> If 8 seconds is an unacceptable time, then you're liable to need hardware
to
> fix it: more RAM to cache those rows, or faster disks or both.
>
> However, this is just speculation. You didn't provide analyze output,
table
> schema, hardware details, or configuration information ... so it's
entirely
> possible that there is something else wrong. I'm just making an educated
> guess.

Thanks for the help, after your email I went to capture some analyze output
for you and when I did I figured to bump up the statistics on the two
columns of interest from 100 to 1000. Now all statements return close to
instantly.

I originally thought it was the index because I could make an index that
yielded great performance for each type of select I was doing, but never
for all of them at once. To answer your question about hardware the CPU is
a xeon with 3GB of ram. I am unsure of the exact speed of the HDD but I'm
certain its high performance.

Is this analyze tool something I need to run periodically to keep
performance up? If so how often should I run it.