From: | amrit(at)health2(dot)moph(dot)go(dot)th |
---|---|
To: | Dawid Kuroczko <qnex42(at)gmail(dot)com> |
Cc: | PGsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Low Performance for big hospital server .. |
Date: | 2005-01-06 16:34:43 |
Message-ID: | 1105029283.41dd68a3ead99@webmail.moph.go.th |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> 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...
Yes, very good point I must try this and I will give you the result , thanks a
lot.
Amrit
Thailand
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2005-01-06 16:57:56 | Re: Benchmark two separate SELECTs versus one LEFT JOIN |
Previous Message | Frank Wiles | 2005-01-06 15:52:19 | Re: first postgrreSQL tunning |