Re: Speed problems - Mailing list pgsql-general

From Scott Marlowe
Subject Re: Speed problems
Date
Msg-id 1126637949.12728.49.camel@state.g2switchworks.com
Whole thread Raw
In response to Speed problems  ("Warren Bell" <warren@clarksnutrition.com>)
Responses Re: Speed problems
List pgsql-general
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.

pgsql-general by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Email Verfication Regular Expression
Next
From: Steve Atkins
Date:
Subject: Re: Email Verfication Regular Expression