Re: Performance large tables.

On 12/10/05, Benjamin Arai <barai@cs.ucr.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 ;)