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
