Search Postgresql Archives

Re: Problem Designing Index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



pgsql-general-owner@xxxxxxxxxxxxxx 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.


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux