Re: Help needed on performance

Hi!

The only thing that comes immediately to mind is to be sure that you're 
vacuuming regularly. It seems to me that you're inserting and deleting rows 
quite a bit, and without regular vacuuming, the indexes will become pretty 
useless. I had similar problems with a table that had only a few dozen rows, 
but a couple hundred thousand deleted rows. Performance was horrible until I 
remembered to vacuum, which I now do daily with a cron job. I don't think 
Pypgsql has any bearing on the problem, however.

HTH.

.... Bob

On Friday 13 June 2003 02:04, Dave Strickler wrote:
> If there's a good FAQ on this, please just point me there....
>
> Background
> ------------
> I have a database that has 3 tables in it. One of the tables has about
> 15-20 fields in it, a number of them are strings, and so I have defined
> them as TEXT. The others are mostly INT4.
> I have indexes on 4 of the TEXT fields, and on one other INT4 fields.
>
> The table has 500k to 1m (size fluxuates) rows in it and is run on a
> Dual Processor 2.4 Xenon, with 2GB of RAM and a single IDE drive. Its
> running RH8, and has Apache running as well as Postgresql.
>
>
>
> Problem
> ----------
> The crux of the problem is performance. At somewhere around 500k
> records in the table, the speed of SELECTs and DELETEs gets really poor.
> 'Poor' as in 2-3 minutes to do a DELETE of 1k rows. Under this number,
> the speed is excellent.
>
> Any ideas on this?
>
> TIA,
>
>
>
> Dave Strickler
> MailWise, LLC
> (617) 848-5942
> ------------------------------------
> Want to get rid of SPAM, Viruses, and unwanted content?
> MailWise Filter - nothing to install, maintain or upgrade.
> "Intelligent Email Protection(tm)"
> Info at  http://www.mailwise.com



-------------------------------------------------------
This SF.NET email is sponsored by: eBay
Great deals on office technology -- on eBay now! Click here:
http://adfarm.mediaplex.com/ad/ck/711-11697-6916-5