Re: Performance large tables.

Please start new threads to ask unrelated questions, rather than replying
to an existing thread. This makes the archives less useful, and may keep
people from seeing your question.

On Sat, Dec 10, 2005 at 15:37:01 -0800,
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;".

Another possibility is that foreign key checks may be slowing things down.
You may need to create some indexes on referencing tables if you are doing
deletes (or updates which change the key) in referenced tables.

Finding out more about which queries are slow and what plans they are using
is a good first step.