Re: Can anyone explain this: duplicate dbs. - Mailing list pgsql-performance

From SpaceBallOne
Subject Re: Can anyone explain this: duplicate dbs.
Date
Msg-id BAY14-DAV16F2B796085B6591A93AFACC0E0@phx.gbl
Whole thread Raw
In response to Can anyone explain this: duplicate dbs.  ("SpaceBallOne" <space_ball_one@hotmail.com>)
Responses Re: Can anyone explain this: duplicate dbs.  (Christopher Kings-Lynne <chriskl@familyhealth.com.au>)
Re: Can anyone explain this: duplicate dbs.  (John A Meinel <john@arbash-meinel.com>)
List pgsql-performance
> What version of postgres?

8.0.2 ... but I think I've seen this before on 7.3 ...

> There are a few possibilities. If you are having a lot of updates to the
> table, you can get index bloat. And vacuum doesn't fix indexes. You have
> to "REINDEX" to do that. Though REINDEX has the same lock that VACUUM
> FULL has, so you need to be a little careful with it.

> Probably better is to do CLUSTER, as it does a REINDEX and a sort, so
> your table ends up nicer when you are done.

Thanks, will try those next time this problem crops up (i just deleted /
recreated the database to speed things for its users in the office ...
probably should have held off to see if I could find a solution first!).

Yes, the database / table-in-question does have a lot of updates, deletes,
and new rows (relatively speaking for a small business).

Would CLUSTER / REINDEX still have an effect if our queries were done via
sequential scan? This is a old database (as in built by me when i was just
starting to learn unix / postgres) so the database design is pretty horrible
(little normalisation, no indexes).

Have taken Chris's advice onboard too and setup cron to do a vacuumdb hourly
instead of my weekly vacuum.

Cheers,

Dave.




pgsql-performance by date:

Previous
From: John A Meinel
Date:
Subject: Re: Can anyone explain this: duplicate dbs.
Next
From: Christopher Kings-Lynne
Date:
Subject: Re: Can anyone explain this: duplicate dbs.