On Tue, 2005-09-13 at 13:20, Warren Bell wrote: > I am having problems with performance. I think this is a simple question and > I am in the right place, if not, please redirect me. > > I have a table with 36 fields that slows down quite a bit after some light > use. There are only 5 clients connected to this DB and they are doing mostly > inserts and updates. There is no load on this server or db at all. This > table has had no more than 10,000 records and is being accesessd at the rate > of once per 5 seconds. It will slow down quite a bit. It will take 10 > seconds to do a `SELECT * FROM` query. I delete all records except one > perform a VACUUM and this will not speed it up. I drop the table and > recreate it and insert one record and it speeds right back up takeing only > 100 ms to do the query. This sounds like classic table / index bloat. Are you updating all 10,000 rows every 5 seconds? Good lord, that's a lot of updates. If so, then do a vacuum immediately after the update (or a delete), or change the system so it doesn't update every row every time. Next time, try a vacuum full instead of a drop and recreate and see if that helps. > > I am fairly new to Postgres. What do I need to do to keep this table from > slowing down? Vacuum this table more often. You might want to look at using the autovacuum daemon to do this for you. You might want to post a little more info on what, exactly, you're doing to see if we can spot any obvious problems. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
