> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx > [mailto:pgsql-general-owner@xxxxxxxxxxxxxx]On Behalf Of Scott Marlowe > Sent: Wednesday, September 14, 2005 8:24 AM > To: Warren Bell > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: Speed problems > > > On Tue, 2005-09-13 at 21:01, Warren Bell wrote: > > >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 > > >> 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. > > > > > > > I have three indexes on this table. One index is a 1 column, > one index is a > > 5 column multi and one is a 2 column multi. I have run EXPLAIN > ANALYZE on > > all of my queries and they seem to be taking advantage of these indexes. > > > > Would three indexes of this sort be considered "index bloat"? > > No, index bloat is a different problem. In the days of yore, postgresql > had a tendency to grow its indexes over time without reclaiming lost > space in them, which lead to bloated indexes (back in the day, I once > had a 100k table with an 80 meg index after a while... Now that is > bloat) > > Today, index bloat is generally not a problem, as vacuum can reclaim > much more space in an index than it once could. I'm guessing you're > suffering from a bloating of tables and indexes caused by not vacuuming > enough. Use a vacuum full once to clear up the bloated tables and > indexes, and then regularly scheduled plain vacuums to keep them at a > reasonable size. > > > I am updating no more than 200 records at a time. Here are some > examples of > > my queries: > > > > UPDATE table SET boolean_col_1 = true WHERE boolean_col_2 = false > > > > UPDATE table SET (several columns = something) WHERE char_col_1 > = 'blah' AND > > int4_col_1 = 11 > > > > UPDATE table SET boolean_col_1 = true WHERE boolean_col_2 = false AND > > boolean_col_3 = false AND boolean_col_4 = false AND > boolean_col_5 = false > > AND boolean_col_6 = false > > OK. But how many are you updating between regular vacuums? That's the > real issue. If your regular vacuums aren't often enough, postgresql > starts lengthening the tables instead of reusing the space in them that > was freed by the last updates / deletes. > > Keep in mind, that in postgresql, all updates are really insert / delete > pairs, as far as storage is concerned. So, updates create dead tuples > just like deletes would. > > > Is my use of indexes correct? > > Seems good to me. > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > I have installed pg_autovacuum and also did a VACUUM FULL on the tables. Speed has improved quite a bit. Are there any set rules on what the pg_autovacuum -v and -V arguments should be set to? I went with the defaults Thanks for your help, ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
