Re: Speed problems

>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.
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend

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"?

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

Is my use of indexes correct?

In the meantime, I will start using autovacuum or VACUUM.

Thanks for your help,