Re: Speed problems - Mailing list pgsql-general

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.