Re: Low Performance for big hospital server ..

On Wed,  5 Jan 2005 22:35:42 +0700, amrit@health2.moph.go.th
<amrit@health2.moph.go.th> wrote:
> Now I turn hyperthreading off and readjust the conf . I found the bulb query
> that was :
> update one flag of the table [8 million records which I think not too much]

Ahh, the huge update.  Below are my "hints" I've
found while trying to optimize such updates.

First of all, does this update really changes this 'flag'?
Say, you have update:
UPDATE foo SET flag = 4 WHERE [blah];
are you sure, that flag always is different than 4?
If not, then add:
UPDATE foo SET flag = 4 WHERE flag <> 4 AND [blah];
This makes sure only tuples which actually need the change will
receive it.  [ IIRC mySQL does this, while PgSQL will always perform
UPDATE, regardless if it changes or not ];

Divide the update, if possible.  This way query uses
less memory and you may call VACUUM inbetween
updates.  To do this, first SELECT INTO TEMPORARY
table the list of rows to update (their ids or something),
and then loop through it to update the values.

I guess the problem with huge updates is that
until the update is finished, the new tuples are
not visible, so the old cannot be freed...

   Regards,
      Dawid