Re: Performance large tables.

On Saturday 10 December 2005 19:28, Jaime Casanova wrote:
> 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...

Once you've updated a majority of the rows in a large tables, if your still
doing further work on that table that involves complex conditionals, you'll
probably notice a slow down, so you might even think about doing a vacuum
durring the updates if you can swing it.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL