Re: Problem Designing Index - Mailing list pgsql-general

From Alan J Batsford
Subject Re: Problem Designing Index
Date
Msg-id OFBB856885.6D626CE3-ON85257336.00487DB2-85257336.004AF209@notes.uss.com
Whole thread Raw
In response to Re: Problem Designing Index  (Bill Moran <wmoran@potentialtech.com>)
Responses Re: Problem Designing Index
List pgsql-general
pgsql-general-owner@postgresql.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.


pgsql-general by date:

Previous
From: "John Coulthard"
Date:
Subject: Re: Unable to connect to PostgreSQL server via PHP
Next
From: Tom Lane
Date:
Subject: Re: "Out of memory" errors..