Re: Performance large tables.

On 12/10/05, Benjamin Arai <barai(at)cs(dot)ucr(dot)edu> wrote:
> To be more specific, there are two types of commands that are run on
> the system. There are application commands that do all different types
> of joins and etc but for the most part are fast enough to meet user
> expectations. On the other hand there is a weekly update (This is the
> problem) that updates all of the modified records for a bunch of
> finacial data such as closes and etc. For the most part they are
> records of the type name,date,value. The update currently takes almost
> two days. The update does deletions, insertion, and updates depending
> on what has happened from the previous week.
>
> For the most part the updates are simple one liners. I currently commit
> in large batch to increase performance but it still takes a while as
> stated above. From evaluating the computers performance during an
> update, the system is thrashing both memory and disk. I am currently
> using Postgresql 8.0.3.
>
> Example command "UPDATE data where name=x and date=y;".
>

Try using VACUUM or VACUUM FULL after those weekly updates...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)